mysql real escape string

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.

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.

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:

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.

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.

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

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?

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

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:

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