Page 1 of 1
How Secure is mysql_real_escape_string?
Posted: Mon Feb 27, 2006 9:38 pm
by Benjamin
I have seen some of you write that mysql_real_escape_string should be used at the very least to protect against MySQL injection attacks but that a whitelist approach would be better. What is an example of something that could make it past mysql_real_escape_string and harm the database?
Posted: Mon Feb 27, 2006 10:25 pm
by josh
Not harm the database per se, but for example if you are expecting a valid integer and you got some other datatype, another part of your app could behave unexpectedly..
Posted: Mon Feb 27, 2006 11:21 pm
by Benjamin
Oh ok thank you for clarifying that.
Posted: Tue Feb 28, 2006 7:34 am
by Maugrim_The_Reaper
Should be noted initial filtering, and html/database escaping are two separate things. One is not an alternative to another - except in the most simplistic cases.
Re: How Secure is mysql_real_escape_string?
Posted: Tue Feb 28, 2006 6:36 pm
by Roja
agtlewis wrote:I have seen some of you write that mysql_real_escape_string should be used at the very least to protect against MySQL injection attacks but that a whitelist approach would be better. What is an example of something that could make it past mysql_real_escape_string and harm the database?
Actually, the question this spawned from was NOT limited to mysql injection attacks.
The whitelist is a more secure solution to the overall problem of handling input (especially if its intended for db storage). Use mysql_real_escape_string to prevent the injection attacks. But before that, use a whitelist to limit the input to a sane range of characters, to reduce your headaches for step three: Outputting that content.

Posted: Tue Feb 28, 2006 6:55 pm
by John Cartwright
but that a whitelist approach would be better
What is an object in your whitelist had a ' ? or what about in the case of a user, such as O'Brian trying to enter his username?
Do yourself a favor and play it safe when running queries by using mysql_real_escape_string() at minimum.
Posted: Tue Feb 28, 2006 8:28 pm
by Benjamin
I always validate user input before it goes to queries so what was discussed in the other forums doesn't really apply to me. It just assumed that people already did that. It sounded to me that in addition to that, and mysql_real_escape_string, something else needed to be done to the data to ensure proper security.
Posted: Tue Feb 28, 2006 9:16 pm
by josh
Here's an example.. lets say that you are letting the user ask for all books with a certan string in the name, and your code looked like
Code: Select all
SELECT * FROM `books` WHERE `title` LIKE '%'.mysql_real_escape_string($_POST['book']).'%'
The user could send a '%' and get every record.. Unintended behavior. Escaping is just what it implies, a form of escaping... Even if your whitelist accepts \' a use could send \\'.
Run everything through as many filters as you can, then escape it last. As an alternative to escaping you can cast it to an integer or float if you expect numeric values.
~ Fixed missing closing parenthesis
Posted: Tue Feb 28, 2006 9:45 pm
by Benjamin
Thank you that is a very good example. I will be sure to watch out for things like that.