I couldn't find an SQL injectiong tread in DP so I think a good discussion of this would be helpfull for a lot of people . Ok I'm a security consious noob in PHP/MYSQL, I have created a script that passes parameters in the url and other function that inserts the user input on a form in a database. My url parameters are either strings or integers and the form input is all text. From what I have found to protect my script I should do these things: 1) if magic quotes is on run strip slashes. 2) if its an integer convert to integer type. 3) if its a string replace <space>AND<space>, <space>OR<space, and all other sql commands with and empty string ( I think this would be good since my input shouln't contain these ). 4) Remove special characters not needed like = and ; (any others?) 5) use the sscape function for mysql. 6) quote variables in the query string. Is all this a good solution? anything that I should eliminate because is redundant or not needed? Any other php, mysql security tips are welcome.
It makes no sense to try to catch all possible SQL combinations - there's just too many. Use regular expressions to validate your input (in some cases you can just use numeric conversions, just as you describe). Search for quotes in the input - most types of input are not supposed to have any. If you expect some, quote them properly (you can use e/preg_replace for this). If you have a choice (I don't think MySQL/PHP supports this), use parameter binding. That is, your SQL statement will look kind of like this "insert into some_table values (?, ?, ?)". Then you provide actual values at run time. This is most effective protection agains SQL injection with those client-side drivers that support it. As for quoting query strings - don't quote them too early or otherwise you'll end up with strings you can't use (e.g. if you insert slashes and then use string comparison on such string, it may not match what you expect, yet be perfectly valid). J.D.
Instead of removing stuff that shouln't be in the input, only allow stuff that should be. (There is a big difference there). If the input is first and last name, only allow [a-zA-Z' ] (That's a to z in either case, an apostrophe and a space. Then use add slashes before sending it to MySql. For int imput, use ctype_digit() to check for 0-9 if you get anything else, trash it. There's also is_numeric() but it allows other stuff like a comma and period etc. Simply define what is allowable input and only allow that. That's my opinion.
One of my inputs is a Big block of text ( a user review), so I was thinking of leaving everything in except dangerous stuff. If the user types SELECT or LIKE its either somebody with the caps lock on or somebody trying something bad.
That's one way of looking at it, but I would simply allow a to z and the punctuation marks. Alternatively, a review could potentially contain sample SQL (Just as this post does), so just escape all quotes and send it on in $properly_escaped_text = mysql_real_escape_string($input); $query = "INSERT INTO `table` (`ip`,`review`) VALUES ('$ip','$properly_escaped_text')"; Code (markup): [edit]Also, read this[/edit]
Thanks I'll read that after dinner. I have found that everyone have a different opinion about the subject making it hard to get to the right conclusion and solution.
Look at code you know works that has a large user base and is proven for methods that are short and to the point that work.
Here's a pretty good read how a SQL injection attack can compromize the entire network. http://www.microsoft.com/technet/technetmag/issues/2005/01/AnatomyofaHack/default.aspx More info on SQL injection (SPI Dynamics produces a suite of website vulnerability detection tools): http://www.spidynamics.com/whitepapers/WhitepaperSQLInjection.pdf J.D.
I have found for SQL Inection protection for ASP from the below link. http://codegroups.com/blog/index.php/sql-injection-protection/ Even you can clean up your database from the below link Cleaning Up SQL Injection Attack : b.js http://codegroups.com/blog/index.php/cleaning-up-sql-injection-attack-bjs/