due to popular feedback this tip has been updated to compensate for some bad advice I was promoting. Sorry folks 😉
In summary, to ensure your database cannot be compromised by data from web forms:
- (obviously) never pass unverified data from a webform to your database
- if you’re using MySQL use either mysql_escape_string or mysql_real_escape_string to escape a string for use in mysql_query, the latter takes a connection handler and escapes the string according to the current character set
- if you’re using the PEAR library use DB::quote() to clean your data
Also take a look at the following guidelines from the MySQL manual, general advice can be loosely applied to other databases but watch out for the exceptions (see comments)
When running MySQL, follow these guidelines whenever possible:
- Do not trust any data entered by your users. They can try to trick your code by entering special or escaped character sequences in web forms, URLs, or whatever application you have built. Be sure that your application remains secure if a user enters something like “; DROP DATABASE mysql;”. This is an extreme example, but large security leaks and data loss may occur as a result of hackers using similar techniques, if you do not prepare for them. Also remember to check numeric data. A common mistake is to protect only strings. Sometimes people think that if a database contains only publicly available data that it need not be protected. This is incorrect. At least denial-of-service type attacks can be performed on such databases. The simplest way to protect from this type of attack is to use apostrophes around the numeric constants: SELECT * FROM table WHERE ID=’234′ rather than SELECT * FROM table WHERE ID=234. MySQL automatically converts this string to a number and strips all non-numeric symbols from it. Checklist:
- All web applications:
- Try to enter ‘ and “ in all your web forms. If you get any kind of MySQL error, investigate the problem right away.
- Try to modify any dynamic URLs by adding %22 (“), %23 (#), and %27 (‘) in the URL.
- Try to modify datatypes in dynamic URLs from numeric ones to character ones containing characters from previous examples. Your application should be safe against this and similar attacks.
- Try to enter characters, spaces, and special symbols instead of numbers in numeric fields. Your application should remove them before passing them to MySQL or your application should generate an error. Passing unchecked values to MySQL is very dangerous!
- Check data sizes before passing them to MySQL.
- Consider having your application connect to the database using a different user name than the one you use for administrative purposes. Do not give your applications any more access privileges than they need.
- Users of PHP:
- Check out the addslashes() function. As of PHP 4.0.3, a mysql_escape_string() function is available that is based on the function of the same name in the MySQL C API.