Hi what is the best way to protect your site from SQL injection? If you have a name field, someone can insert ' OR Name = ' to ruin your data. we can always invalidate name fields with apostraphes However sometimes we do need field with apostraphes, so what is the best limitation to a field that will block all potential SQL injections but will allow the widest range of input?
I've always used a simple mysql_real_escape_string() which adds slashes (for those apostrophes). My husband is introducing me to bind variables, which creates a drop-in idea. Whatever is put in the variable location can only be a single string, replacing the issue of escaping a string and injecting malicious code. This article gives a good overview, probably better than I can explain it.
you can also make user only input alphanumeric character only. This way, the user can't input char like ! = and '.
It depends on what sql you are using. If you use mysql,then you should escape data with mysql_real_escape_string(). For Sqlite try sqlite_escape_string(). Always check for magic quotes being on/off to avoid storing data with "\'". To avoid this try stripslashes().
htmlentities() and htmlspecialchars() and really good functions if you do not need single quote and double quotes as it is in the database.
suppose i just add a \ in front of every single quote or double quote depending on which we use, will this block every possible sql injections so far as protecting our database, neglecting scripts possibilities.
damn why didn't i learn about htmlentities sooner. so this is how other social networks enable html mode in the about me section. i filled all my codes with pregmatch already. thanks mwasif. btw: what's the difference between htmlentities() and htmlspecialchars()?
' or 1=1-- is a better injection. To secure against this you have to do two things - SELECT * FROM `Table` WHERE `Column` = '$input' rather than this SELECT * FROM `Table` WHERE `Column` = $input (notice the apostrophes) and Escape characters in the input. If the input is "you're" once escaped it will look like "you\'re". This is useful to stop people breaking out of your apostrophes. SELECT * FROM `Table` WHERE `Column` = '\' or 1=1--' rather than this SELECT * FROM `Table` WHERE `Column` = '' or 1=1--'
Validating the input is a great suggestion. Another basic step you can take is to parameterize your input statement. There's a great video about securing your SQL code here - http://www.microsoft.com/hellosecureworld7