How to prevent MySQL injection attacks?

[avillanu]

I've fully read

 

http://us.php.net/manual/en/function.mysql-real-escape-string.php

 

and I'm still confused. Everyone has their own "best practice" code and they're all different. I've done extensive googling and it's the same. This is a huge headache as everyone is acting like an authority..

 

This is what I'm planning to do for my script. Please let me know of any shortcomings.

 

1) [Example 1442 in link] - add mysql_real_escape_string to ALL MySQL queries. Would this be enough? It would seem this would catch anything.

2) $id = intval($_GET['id']) instead of a simple $_GET['id']. Would this be necessary if I do the previous prevention method (1) and is there some code that be used if the variable passed is a string?

 

Also, how are these methods affected if magic_quotes_gpc is onor off?

[freakstyle]

HI There,

just a quick note for you, a very easy and secure way to protect from injection attack is not so much what you are escaping but really what you are querying.

 

Lets say you want to log in a user, so they have entered their user name and password.

now inside your form handler you need to query the db to see if this information is right.

well, instead of looking for the password, try just obtaining the email, if you get that... then check the returned record's password and your done.

 

you could just as easily select more data then loop over an array to find the data that you are looking for. The basic idea here is to keep as many doors closed to your db.

 

and above all, before you use any user input...Validate it!! that is the single most important step. if you need to use their email address for something, make sure its well formed, if you need a zip code make sure its numeric. and so on and so forth.

 

best of luck to you

 

[hackerkts]

Just a short tip, before you do the mysql_query(), you just have to ensure the query that send to mysql doesn't contain funny characters (eg: quotation marks).

[chocopi]

The best way I found when I didn't know much was to use as many as possible, but obviously this can be very pointless to keep doing this.

 

You should be fine with mysql_real_escape_string though

 

Everyone has their own "best practice" code

 

Just keep testing and find your own "best practice" that way you can improve whenever needed.

 

Well thats my input anyways,

 

Good Luck,

 

~ Chocopi

[avillanu]

Would it be okay to put it in the query or should it be before it as suggested by hackerkts?

[chocopi]

yea you should always validate anything before you put it through a query