How Secure is mysql_real_escape_string?
Moderator: General Moderators
How Secure is mysql_real_escape_string?
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?
- Maugrim_The_Reaper
- DevNet Master
- Posts: 2704
- Joined: Tue Nov 02, 2004 5:43 am
- Location: Ireland
Re: How Secure is mysql_real_escape_string?
Actually, the question this spawned from was NOT limited to mysql injection attacks.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?
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.
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
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.
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
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
Code: Select all
SELECT * FROM `books` WHERE `title` LIKE '%'.mysql_real_escape_string($_POST['book']).'%'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
Last edited by josh on Tue Feb 28, 2006 10:15 pm, edited 2 times in total.