mysql real escape string

greg's picture

He has: 1,581 posts

Joined: Nov 2005

what characters does it escape? According to php.net it only escapes
"\x00, \n, \r, \, ', " and \x1a."

I know it does slashes, but why does php.net not list them?
I couldn't find a comprehensive list of all escaped chars.

Also, what about mysql field lengths? If you create a row with say varchar(18) and are expecting at most 18, and real_escape adds slashes, I presume it counts in the total chars allowed. So am I to create field lengths with potential additional chars for the slashes from real_escape?

Confused

JeevesBond's picture

He has: 3,956 posts

Joined: Jun 2002

I know it does slashes, but why does php.net not list them?

It does. You just need to look carefully at the list to see it. Smiling

Also, what about mysql field lengths? If you create a row with say varchar(18) and are expecting at most 18, and real_escape adds slashes, I presume it counts in the total chars allowed. So am I to create field lengths with potential additional chars for the slashes from real_escape?

That's an interesting point. Although I think this isn't a big issue, usually fields of restricted size don't require double or single quotes, null characters etc. Think you're right though, and this could happen, doesn't seem too many people are worried about it though!

They should include it in the PHP documentation though, that would be handy.

a Padded Cell our articles site!

pr0gr4mm3r's picture

He has: 1,502 posts

Joined: Sep 2006

The backslash shouldn't add length to the string because the backslash isn't a character. From my understanding, it just tells PHP to take the next character literally. Unfortunately, the following code did not support that theory:

<?php
    $str
= "Here's is a test string with some '''.";
   
$strlen1 = strlen($str);
   
$strlen2 = strlen(mysql_real_escape_string($str));
   
    echo
$strlen1 . '<br />';    // returns 38
   
echo $strlen2 . '<br />';    // returns 42!
?>

Even though PHP sees it at a different length, I tried it in a test db to see if MySQL sees it as a different length as well. I made a varchar field with a length of 5, and put in an escaped string of "ab'cd". It was all there and not truncated to "ac\'d".

So from what I did, escaping a string will make it larger as far as PHP is concerned, but it will not make the string too large for the DB fields.

JeevesBond's picture

He has: 3,956 posts

Joined: Jun 2002

The backslash shouldn't add length to the string because the backslash isn't a character. From my understanding, it just tells PHP to take the next character literally.

That makes a lot of sense. Maybe we're missing something though: the backslash doesn't tell PHP to take the next character literally, it tell MySQL to take the next character literally.

We'd need to test against inserting the field into a MySQL record, SELECT it out again, then test the length. As you saw it didn't truncate in the database. Good test. Smiling

a Padded Cell our articles site!

pr0gr4mm3r's picture

He has: 1,502 posts

Joined: Sep 2006

JeevesBond wrote: We'd need to test against inserting the field into a MySQL record, SELECT it out again, then test the length. As you saw it didn't truncate in the database. Good test. Smiling

That's probably a good idea. I've been looking in phpMyAdmin, and that might be taking out the slashes automatically. I ran some more queries, but this time selected the data myself. See the attached screenshot. In the top section, I show the length of the string, and the length of the escaped string. Then, once that it inserted into the DB, I select it again showing the length and the string. This shows that the backslash is not stored in the DB, thus not making that an issue with the limited field lengths. Smiling

Edit: Forgot to mention that the field is a varchar type with a max length of 15.

AttachmentSize
_1203351887663.png 36.5 KB
greg's picture

He has: 1,581 posts

Joined: Nov 2005

Right.
So the real_escape backslash simply tells mysql that the apostrophe (or slash or quotes etc)is simply a character being entered into the DB as text, and not to use it as whatever the apostrophe would be used for within a PHP/MYSQL query. I.E. as part of PHP syntax.

And so that is why it's not actually entered into the DB, it doesn't need to be as the only injection issues are at the time of query excecution.
The real_esacpe takes out the problem of that by using the chars as text only rather than allowing them to change the structure of a query and thus not needed to be stored in the DB

Interesting test too pr0gr4mm3r!
Thanks to you both!

EDIT
pr0gr4mm3r, when I try the above code you did with strlen, the first one outputs 38 but the second one after the real_escape outputs 0
Why is that?

EDIT II
I tried with mysql_escape_string instead and it works fine. So the server I am using doesn't have PHP version that has real_escape?
According to my admin CP, it's running version 5.2.5
Confused

pr0gr4mm3r's picture

He has: 1,502 posts

Joined: Sep 2006

greg wrote:

EDIT
pr0gr4mm3r, when I try the above code you did with strlen, the first one outputs 38 but the second one after the real_escape outputs 0
Why is that?

EDIT II
I tried with mysql_escape_string instead and it works fine. So the server I am using doesn't have PHP version that has real_escape?
According to my admin CP, it's running version 5.2.5
Confused

You have to initiate a connection to the database before you run mysql_real_escape_string() because it escapes the string according to the character set of the database. I took out the lines that connected to the database before I posted the code because it has the password, hostname, etc. Sorry, I probably should have mentioned that. The mysql_escape_string() does not require an active connection to a database, so that's probably why it worked for you while the real_escape one didn't.

This was a good question - something I never thought of.

greg's picture

He has: 1,581 posts

Joined: Nov 2005

hmm, that's interesting.

I see all tutorials on the net (decent sites too) that use the real_escape first, then make a query to the DB.
So they run the variable through real_escape, then use the var in the query as normal.

$value = mysql_real_escape_string($value);

Yet on php.net they use the real_escape in the query line, and that is the ONLY site I have seen do it that way

$query = sprintf("INSERT INTO products (`name`) VALUES ('%s', '%s', %d)",
mysql_real_escape_string($product_name));

So is one way better than the other?

JeevesBond's picture

He has: 3,956 posts

Joined: Jun 2002

<?php
$query
= sprintf("INSERT INTO products (`name`) VALUES ('%s', '%s', %d)",
mysql_real_escape_string($product_name));
?>

Drupal does it this way. Don't think it matters though to be honest, just use whichever method you're most likely to remember to implement. In fact the best method is the Drupal way: use a thin database abstraction layer to do it all for you. Have a look at the db_query() function. If you implement something like that and run all queries through one function it will be nearly impossible to introduce a SQL injection vulnerability.

Tutorials on the Web are fine, but they rarely teach good architecture (way beyond their scope).

a Padded Cell our articles site!

pr0gr4mm3r's picture

He has: 1,502 posts

Joined: Sep 2006

greg wrote: hmm, that's interesting.

I see all tutorials on the net (decent sites too) that use the real_escape first, then make a query to the DB.
So they run the variable through real_escape, then use the var in the query as normal.

$value = mysql_real_escape_string($value);

That's how I do it. It's not after you query the database. You just have to be connected to the database.

For example, it's something like this:

<?php
    mysql_connect
('localhost', 'user', 'pass');
   
mysql_select_db('db_name');
   
   
/* now that we have a db selected, we can use mysql_real_escape_string() */
   
$user = mysql_real_escape_string($_POST['user']);
   
$password = mysql_real_escape_string($_POST['password']);
   
   
$query = "SELECT * FROM Users WHERE user = '$user' && password = '$password' LIMIT 1";
   
$result = mysql_query($query);
   
   
/* do some results processing here */
?>

greg's picture

He has: 1,581 posts

Joined: Nov 2005

That's cleared a lot up, thanks!

JeevesBond wrote: Tutorials on the Web are fine, but they rarely teach good architecture (way beyond their scope).

That's why I come here Wink

Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.