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
– Narcis Radu

2011-12-08 10:59:55 +00:00
Commented Dec 8, 2011 at 10:59 — Narcis Radu
– Narcis Radu, Commented Dec 8, 2011 at 10:59

Emir Akaydın · Accepted Answer · 2011-12-08 11:01:14Z

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 8, 2011 at 11:01

Emir Akaydın

5,8481 gold badge32 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Pekka Over a year ago

The very best thing, however, would be to turn off magic quotes altogether. It's a thing of the past.

Emir Akaydın Over a year ago

What if he doesn't have the rights to change php configuration of the server?

lvil Over a year ago

worked great for me, now the question is: "Is it safe to use this and mysql_real_escape_string?"

lvil Over a year ago

And I rather not change the server configs

Pekka Over a year ago

@lvil yes, if you apply mysql_real_escape_string() to it afterwards, this is safe.

Pekka · Accepted Answer · 2011-12-08 11:00:29Z

0

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.

answered Dec 8, 2011 at 11:00

Pekka

451k150 gold badges989 silver badges1.1k bronze badges

Comments

Lee · Accepted Answer · 2011-12-08 11:01:01Z

0

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

answered Dec 8, 2011 at 11:01

Lee

10.6k4 gold badges39 silver badges45 bronze badges

Comments

Johan · Accepted Answer · 2011-12-08 11:05:47Z

0

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);

edited Dec 8, 2011 at 11:05

answered Dec 8, 2011 at 11:00

Johan

77.4k28 gold badges204 silver badges346 bronze badges

2 Comments

Pekka Over a year ago

Wut? If a'b turns into a\'b in the table, that has nothing to do with mysql_real_escape_string().

Johan Over a year ago

@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

Community · Accepted Answer · 2017-05-23 11:55:31Z

0

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

edited May 23, 2017 at 11:55

CommunityBot

11 silver badge

answered Dec 8, 2011 at 12:28

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

Collectives™ on Stack Overflow

mysql_real_escape_string() Different behavior with single quote

5 Answers 5

5 Comments

Comments

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

5 Comments

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related