[SOLVED] php mysql bug

[HektoR]

hello.

i wrote script and in search box when i type "-1" or '  i give mysql error, how can i solve this ?

thank you

[ratcateme]

can we see your script?

to better understand you problem

 

Scott.

 

[HektoR]

i'm not at home.

it is simply search box.

[RussellReal]

use mysql_real_escape_string

 

when you're doing

 

SELECT * FROM `tableName` WHERE `whatever` LIKE '%$whatever%'

 

if $whatever contains an apostraphy (?) " ' "

 

the query will ultimately look like:

SELECT * FROM `tableName` WHERE `whatever` LIKE '% whatever lol ' o%'

 

which will terminate the ' too early, leaving extra characters outside of the ' which is causing you the error, this is what is known as "mysql injection" so you should ALWAYS secure your mysql queries with

 

mysql_real_escape_string()

[HektoR]

thank you very much for reply.

 

and where i must insert mysql_real_escape_strig()  ?

[RussellReal]

note: mysql_real_escape_string() is weird, it only works if you connect to a mysql database, so make sure you have a database connection going..

 

THEN, before you do your little query thing

 

$query = "SELECT * FROM `tableName` WHERE `whatever` LIKE '%$whatever%'";

 

you wanna do

 

$whatever = mysql_real_escape_string($whatever);

$query = "SELECT * FROM `tableName` WHERE `whatever` LIKE '%$whatever%'";

 

you should apply mysql_real_escape_string in this fashion to EVERY variable which you are going to pass to a query, this way you avoid any errors in the future

[HektoR]

thank you very much

[RussellReal]

anytime bro

[HektoR]

and one question again

this example is for search and for:

index.php?id='

is same?

[RussellReal]

well yes, that would mess you up aswell.. you should ALWAYS use mysql_real_escape_string

 

any POST or GET variable which your end user can manipulate and send back to your server, which gets put into a sql query, should always be escaped to ensure security aswell as avoid errors..

[HektoR]

understand

thank you again

[Adam]

mysql_real_escape_string() isn't always the best choice depending upon the data you're expecting. There's a whole range of functions you can use to filter your inputs...

 

Just never leave an input variable unsecured!

 

A

[HektoR]

can you write alternatives of mysql_real_escape_string() ??

 

[Mchl]

Depends what data you expect user to put in.

If you expect a date in YYYY-MM-DD format, then you can have custom function that checks it. If you're expecting an email addy, then there's filter_var with FILTER_VALIDATE_EMAIL filter (there are also filters for other datatypes).

 

mysql_real_escape_string is however probably the best solution (second to using prepared statements) to use, when user can input just any string.

[PFMaBiSmAd]

 

 

All external data your script receives must be validate by your script to insure it exists and contains expected values. If you received query errors  for -1 or an empty value, it means your code is not validating what it received.

 

If the query is expecting a numeric field value, mysql_real_escape_string() won't prevent a hacker from injecting sql after the numeric value.

 

If your code is expecting a positive number in $_GET['id'], you should be checking if it is empty or not and then cast it as an integer number and then check if it is a positive number before ever putting it into your query.