Simple mysql_real_escape_string question

[webref.eu]

Could someone please clarify.  Let's say I have magic quotes switched off.  If I then do an INSERT into MySQL with: 

 

mysql_real_escape_string($Username, $connection)

 

let us say with $Username having a value of:

 

Harry's

 

How would you expect this to be visible in the database when using MySQL Query Browser? 

 

I am seeing: 

 

Harry's

 

but I would have expected to see: 

 

Harry\'s

 

because my understanding is that mysql_real_escape_string would have escaped the ' with a backslash.  I am confused, can anyone help? 

 

Thanks All.

 

 

[PFMaBiSmAd]

The escape characters \ are NOT inserted into the database. They are present in the data in the query string and the \' is converted to the literal ' when the query is executed.

[webref.eu]

OK, thanks.  So, to summarise, mysql_real_escape_string will guard against SQL injection attacks but the data will not look any different because of it when viewed in the database? 

 

Rgds

[webref.eu]

Hi All

 

I'd still like some further advice on how mysql_real_escape_string works.  This is what I think happens:

 

Imagine magic quotes is OFF. 

 

Field input into txtPassword is:

 

test"test

 

Then:

 

$Password=$_POST['txtPassword'];

 

Now do:

 

echo mysql_real_escape_string($Password, $connection)

 

You'll see:

 

test\"test

 

i.e. backslash escaped as you would imagine.

 

Now insert into database with something like:

 

//database query

$query = "INSERT INTO Users (Username, Password, Email, SubscribeToNewsletter) VALUES ('" .

mysql_real_escape_string($Username, $connection) . "', '" .

mysql_real_escape_string($Password, $connection) . "', '" .

mysql_real_escape_string($Email, $connection) . "', '" .

mysql_real_escape_string($SubscribeToNewsletter, $connection) . "')";

 

Now look at database field containing the Password with MySQL Query Browser, I see: 

 

test"test

 

i.e. no longer backslash escaped, even though mysql_real_escape_string was applied.  I think however, the database field contents is as it should be, but please could someone explain to me why this is the case in as much detail as possible.  I appreciate I have had a brief explanation above but I really need to have a thorough understanding of this and would appreciate some more input. 

 

Many thanks all. 

 

 

[GingerRobot]

I guess the only way to explain is by analogy. You know (i would assume) that you may have to escape quotes when echoing something. For example, to echo harry's (when using single quotes around the string), you would have to do this:

 

echo 'harry\'s';

 

The backslash effectively tells PHP to ignore any special meaning that the ' character has. In this case, it prevents the single qoute from terminating the string. The backslash is not echoed.

 

In the same way, using mysql_real_escape_string() tells MySQL to ignore any special meaning that the characters have. It does not store them; just prevents them from having significance. Perhaps it would be helpful if you understood that mysql_real_escape_string() actually calls the MySQL function of the same name. Hence why a connection is required.