mysql_real_escape_string() Different behavior with single quote

Question

I'm trying to prevent sql injections.
For this purpose I use mysql_real_escape_string().

On my local server (phpversion 5.3.2 ):

$string="a'b"
$newstring=mysql_real_escape_string($string);

query("INSERT INTO .. ..field1='$newstring'");

Inserting $newstring into table puts "a'b".
On another server (phpversion 5.2.10)it puts "a\'b" into table.

How can I allow inserting "a'b" and avoid injections?
I don't want to make changes to INI file and magic_quotes as it can affect other queries.
I can't use add_slashes as I will have to look for all the usages of getting the value to remove the slashes.

but magic_quotes are off on a server and off on the other? – Narcis Radu Dec 08 '11 at 10:59 — Narcis Radu, Dec 08 '11 at 10:59

Johan · Answer 1 · 2011-12-08T11:05:47.667

The most likely issue is that you have magic_quotes enabled on one server, but not the other. Either disable magic_quotes or change to the code:

$example = stripslashes($_POST['example']);      //undoes the magic_quotes
$escaped_string = mysql_real_escape_string($example); //escapes it properly.

A less likely scenario may occur if you have 2 connections to 2 different database server

mysql_real_escape_string works different depending on the connection that you have open.
If you have multiple servers and thus multiple connections, you need to run the escaping function once per connection.

Example

$example = "a'b";
$connectionA = mysql_connect('localhost', 'mysql_user', 'mysql_password');
$connectionB = mysql_connect('remotehost', 'mysql_user', 'mysql_password');
$escapedstringA = mysql_real_escape_string($example, $connectionA);
$escapedstringB = mysql_real_escape_string($example, $connectionB);

If you leave out the connection parameter in the call to mysql_real_escape_string both escapedstrings will be escaped using the default encoding of remotehost, which is incorrect.

This code may work incorrect:

$example = "a'b";
$connectionA = mysql_connect('localhost', 'mysql_user', 'mysql_password');
$connectionB = mysql_connect('remotehost', 'mysql_user', 'mysql_password');
$escapedstringA = mysql_real_escape_string($example);  //uses remotehost's encoding
$escapedstringB = mysql_real_escape_string($example);

Wut? If `a'b` turns into `a\'b` in the table, that has nothing to do with `mysql_real_escape_string()`. — Pekka, Dec 08 '11 at 11:01
@Pekka, I do believe it can **if** you're reusing an escaped value in a wrong encoding on a server with a very different encoding, rereading the question, I agree that it much more likely that it's the awful `magic_quotes` — Johan, Dec 08 '11 at 11:07

score 0 · Answer 2 · answered Dec 08 '11 at 11:00

This has probably nothing to do with mysql_real_escape_string(), but your magic_quotes setting.

The preferable thing would be to turn magic quotes off, as even the PHP manual itself recommends.

If that is really not possible, here is an example how to "disable" magic quotes from inside PHP code.

score 0 · Answer 3 · answered Dec 08 '11 at 11:01

It is indeed magic quotes thats the problem. The only thing you can do is detect magic qoutes and strip the slashes. Theres a few scripts you can just drop in place that will work globally

check the comments here

http://php.net/manual/en/security.magicquotes.disabling.php

If you dont want it globally,, use the if condition and the strip slashes just on that variable

score 0 · Accepted Answer · answered Dec 08 '11 at 11:01

0

If magic_quotes_gpc is enabled, first apply stripslashes() to the data. Using this function on data which has already been escaped will escape the data twice.

if(get_magic_quotes_gpc()) {
    $newstring = stripslashes($newstring);
}

answered Dec 08 '11 at 11:01

Emir Akaydın

5,708
1
29
57

The *very best* thing, however, would be to turn off magic quotes altogether. It's a thing of the past. – Pekka Dec 08 '11 at 11:04
What if he doesn't have the rights to change php configuration of the server? – Emir Akaydın Dec 08 '11 at 11:05
worked great for me, now the question is: "Is it safe to use this and mysql_real_escape_string?" – lvil Dec 08 '11 at 11:11
And I rather not change the server configs – lvil Dec 08 '11 at 11:12
@lvil yes, if you apply `mysql_real_escape_string()` to it afterwards, this is safe. – Pekka Dec 08 '11 at 11:14

score 0 · Answer 5 · edited May 23 '17 at 11:55

I'm trying to prevent sql injections.
For this purpose I use mysql_real_escape_string().

Look at this question, it might be interesting for you: Apparently there is an SQL injection bug in my PHP code

In short, "use mysql_real_escape_string" is not sufficient to "prevent sql injections". It is useful for the strings only, but for the other parts you need different approach. Refer to my earlier answer on the matter for the full details: https://stackoverflow.com/a/2995163/285587

mysql_real_escape_string() Different behavior with single quote

5 Answers5