Quotes in mysql queries – security issue

Remember to check numeric data as well. If an application generates a query such as SELECT * FROM table WHERE ID=234 when a user enters the value 234,the user can enter the value 234 OR 1=1 to cause the application to generate the query SELECT * FROM table WHERE ID=234 OR 1=1.As a result, the server retrieves every row in the table. This exposes every row and causes excessive server load. The simplest way to protect from this type of attack is to use single quotes around the numeric constants: SELECT * FROM table WHERE ID='234'.  If the user enters extra information, it all becomes part of the string. In a numeric context, MySQL automatically converts this string to a number and strips any trailing nonnumeric characters from it. It means that if the user enters 234myname the value remains 234. Another option is to do a check before the mysql query if the value is numeric.

Source http://dev.mysql.com/doc/refman/5.0/en/security-guidelines.html

Leave a Reply