[RESOLVED] php auto-escapes mysql queries?

[asterix299]

I'm setting up a website right now and I was testing for SQL injection with no protection against it. (like before using mysql_real_escape_string) Basically the way I set it up was as follows:

<?php

$blah = $_POST['blah'];
$user = $_COOKIE['user'];

$query = "UPDATE table SET blah='$blah' WHERE username='$user'";

mysql_query($query);

?>

What confused me was that, even though I put no instructions to do so, PHP escapes all mysql characters automatically. I even tried using mysql_real_escape_string() with my queries and it actually inserted the backslashes into the table (i.e. it was being escaped twice)

So what's going on here? Did I miss the memo where PHP started auto-escaping mysql queries?

[penagate]

No; you missed the memo where it started auto-escaping HTTP parameters ($_GET, $_POST, $_COOKIE). This is a 'feature' known as magic quotes: it automagically escapes quote characters on the assumption that you might want to use them in a SQL query, and it is tremendously irritating. If you run your server, you should disable magic quotes by editing php.ini. If you don't, you're going to have to live with it by adding a check into your code that reverses the effect:

if (@get_magic_quotes_gpc()) {
  $f = create_function('$x', 'return stripslashes($x);');
  array_walk_recursive($_GET, $f);
  array_walk_recursive($_POST, $f);
  array_walk_recursive($_COOKIE, $f);
} 

Once you've done that, you'll no longer have auto-escaped strings.

Next you should throw away lib_mysql and use a better data access abstraction layer. On PHP 4, try MDB2; on PHP 5, try PDO (for multiple sources) or mysqli (for MySQL sources only). These will allow you to use parameterised queries. When you use a parameterised query, you are not concatenating any variables into the query string; instead, you write it using placeholders, and pass the variable data in as function arguments. This avoids the possibility of SQL injection altogether and is thus a far more solid solution than escaping characters. It also makes your SQL easier to read and thus increases maintainability.

As a bonus, these libraries also allow you to use prepared statements, which increase performance slightly. Parameterised prepared statements are how SQL should always be run from any language.

[asterix299]

ok I do run my own server so i'll kill that right now.

Do you have an article about these parameterized strings? I saw another thread where you mentioned them but I didn't fully understand.

[visualAd]

Have a look at PDO: http://phpro.org/tutorials/Introduction-to-PHP-PDO.html

[asterix299]

I would rather use mysqli since I already have it installed. Can you direct me toward a good tutorial on parameterized queries using it?

[asterix299]

Ok I learned the basics of mysqli and I made a quick test script:

<?php

include("dbinfo.inc.php");

$mysqli = mysqli_connect("localhost", $dbUser, $dbPassword, $dbName);

if (mysqli_connect_errno()) {
    die("connect failed");
} 

$username = 'hello';
$blah = 'hello';
$blah2 = 'hello';

if ($stmt = mysqli_prepare($mysqli, "INSERT INTO blah VALUES (?, ?, ?)")) {

    mysqli_stmt_bind_param($stmt, 'sss', $username, $blah, $blah2);
    mysqli_stmt_execute($stmt);
    mysqli_stmt_close($stmt);

}

mysqli_close($mysqli);

?>

So with this I'm completely invulnerable to SQL injection? Quite a simple remedy! Do you have any suggestions on improving this test script before I implement it in my actual site?

[asterix299]

Guys... I still have a question here ^

I always get my first question answered but then when I ask a second, no one ever seems to bother reading it!

[penagate]

I'm sorry but sometimes some of us have to go and work and sleep and such things and we don't always get around to replying as soon as we should.


As for your code, it looks fine. If you prefer, mysqli also provides an object-oriented syntax which you can use instead of the procedural one.

if ($stmt = $mysqli->prepare('INSERT INTO blah VALUES (?, ?, ?)')) {
    $stmt->bind_param('sss', $username, $blah, $blah2);
    $stmt->execute();
    $stmt->close();
}

$mysqli->close(); 

Have a look at the MySQL Improved Extension Manual for all the documentation as it covers both the procedural and object-oriented methods.

[asterix299]

sorry it's just I see all the others getting replied to and mine seem to get skipped over once the main question is answered.

Nah... I like procedural better than OO with PHP. That's just how I roll.

Thanks btw.