Do I need to use mysql_real_escape_string()?

partypete

Hello,

Dreamweaver just got an update so it's generated sql queries won't be open to sql injection attacks. The new code uses this function: mysql_real_escape_string()

My server is set up with magic_quotes_gpc() enabled. I thought that this alone was enough to prevent sql injection attacks... that it escaped all the needed characters that could cause problems in user submitted data such as ' and ".

What makes mysql_real_escape_string() better than using magic_quotes_gpc(), and do I need it when I'm already using magic_quotes_gpc()/addslashes().

PHP.net has this note:
"Note: If this function is not used to escape data, the query is vulnerable to SQL Injection Attacks." Scary.

Thanks for any help!

Peter

MarkR

What makes using mysql_real_escape_string better than magic_quotes_gpc, is that you will only call mysql_real_escape_string on things which are going to be put into SQL commands.

Magic_quotes_gpc will trash things even if they are NOT destined for the database, causing immense amounts of inconvenience.

It's because magic_quotes_gpc DOES escape stuff, but it escapes stuf AT THE WRONG LEVEL. You should be doing it in your database layer, not your web form layer.

Mark

partypete

Thanks Mark!

So although magic_quotes_gpc is at the wrong level or a little overkill, using it does protect my sql queries as well as mysql_real_escape_string correct? I'm not open to sql injection attacks right?

Now that I'm aware of mysql_real_escape_string, I'll use it when I do a code update later. Just want to make sure things are safe with magic quotes until then.

Thanks again,
Peter

MarkR

using magic_quotes_gpc will only protect you if you DO NOT use mysql_real_escape_string. Using both will result in data corruption as things are escaped more than once.

Therefore, if you are confident that throughout your application you're using SQL safely, escaping everything appropriately, you can (and indeed MUST to avoid double-escaping) disable magic_quotes_gpc.

The problem with magic_quotes_gpc is that it will escape things when they come in from the request, even if they aren't going into the database. For example, things sent by email, stored in a file or session etc, may get escaped.

Moreover, under some circumstances (say, storing the value in a hidden field), the same value might logically pass through $_POST (or something else escaped by "magic" quotes) more than once, resulting in it becoming double-escaped (or more).

This is why magic_quotes_gpc is quite flawed.

Also, if you want to do something like validate an email address or some other string, you'll have to do stripslashes() on it, validate it, then add them back in. This is error prone and therefore makes for unmaintainable code, as I'm sure you can imagine.

Mark