That's cleared a lot up,

greg — Wed, 20 Feb 2008 17:06:17 +0000

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

greg wrote: hmm, that's

pr0gr4mm3r — Tue, 19 Feb 2008 19:19:31 +0000

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 */
?>

JeevesBond — Tue, 19 Feb 2008 18:37:37 +0000

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

hmm, that's interesting. I

greg — Tue, 19 Feb 2008 05:31:12 +0000

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?

greg

pr0gr4mm3r — Tue, 19 Feb 2008 04:52:25 +0000

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

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.

Right. So the real_escape

greg — Tue, 19 Feb 2008 03:22:00 +0000

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

JeevesBond wrote: We'd need

pr0gr4mm3r — Mon, 18 Feb 2008 20:04:27 +0000

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.

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.

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

The backslash shouldn't add

JeevesBond — Mon, 18 Feb 2008 13:39:42 +0000

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.

The backslash shouldn't add

pr0gr4mm3r — Mon, 18 Feb 2008 01:56:11 +0000

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.

I know it does slashes, but

JeevesBond — Sun, 17 Feb 2008 12:43:08 +0000

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.