Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

The manual seems to suggest that using quotes around numbers is sufficient to protect from SQL injection.

According to section 5.3.1. General Security Guidelines of the MySQL 5.1 Reference Manual:

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 quotation marks 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.

Does that mean that the user is protected if the user enters the value 234' OR 1=1 # ? (i.e. to generate the query SELECT * FROM table WHERE ID='234' OR 1=1 #')

share|improve this question
3  
If that's the kind of stuff in the MySQL reference manual it makes sense that the MySQL.com site was broken into via SQL Injection. –  mrdenny Sep 9 '11 at 11:57
    
@mrdenny - theregister.co.uk/2011/03/28/mysql_hack –  Nick Chammas Sep 12 '11 at 17:17
add comment

4 Answers 4

up vote 14 down vote accepted

Short answer, no. The quoting trick is easily defeated by including your own closing quote and then a comment symbol to eliminate the final concatenated quote, precisely as in your example.

To protect yourself from SQL injection you must use bind variables. Changing your example to SELECT * FROM table WHERE ID = :X and then binding the user's input to X solves the problem instantly and completely. It is impossible to over-emphasize how important this practice is!

share|improve this answer
add comment

The excerpt provided above will not prevent SQL injections. Even with single quotes the input can be modified to manipulate the quotes. With quotes the query will be:

SELECT * FROM table WHERE ID='234', where the input is 234

Now consider the injected input: 234'; select * from passwords;select * from table where id='234. This will create a query block of:

SELECT * FROM table WHERE ID='234'; select * from passwords;select * from table where id='234'

Depending on the front end language used, in my case PHP, there are built in functions like mysql_real_escape_string that can be used to reduce SQL injections. The only comment I can say about the documentation (apart from @mrdenny's humorous one) is that without the quotes, there are more injection possible.

share|improve this answer
    
Have you had experience with this ??? HA HA, just kidding. +1 !!! –  RolandoMySQLDBA Sep 13 '11 at 0:31
1  
@Rolando So far so good, but I am still paranoid. –  StanleyJohns Sep 13 '11 at 10:15
add comment

User just has to enter 234' OR 1=1 -- (with the trailing space) to break this.

share|improve this answer
add comment

Generally, it's not such a bad "guideline" if you are using some kind of magic_quotes mechanism (which is bypassable only if you are using GBK / Big5 or familiar character set(s) at the backend DBMS). It should properly filter/preformat all occurrences of character ' which can easily enter the SQL injection into that statement (e.g. with payload you've stated 234' OR 1=1 #).

But, to be 100% safe you should definitely use ONLY "parameterized" SQL statements!

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.