How Secure is mysql_real_escape_string?

Discussions of secure PHP coding. Security in software is important, so don't be afraid to ask. And when answering: be anal. Nitpick. No security vulnerability is too small.

Moderator: General Moderators

Post Reply
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

How Secure is mysql_real_escape_string?

Post 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?
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post 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..
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Oh ok thank you for clarifying that.
User avatar
Maugrim_The_Reaper
DevNet Master
Posts: 2704
Joined: Tue Nov 02, 2004 5:43 am
Location: Ireland

Post 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.
Roja
Tutorials Group
Posts: 2692
Joined: Sun Jan 04, 2004 10:30 pm

Re: How Secure is mysql_real_escape_string?

Post 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. :)
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post 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
Last edited by josh on Tue Feb 28, 2006 10:15 pm, edited 2 times in total.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Thank you that is a very good example. I will be sure to watch out for things like that.
Post Reply