sql - Are single quotes escaped automatically in PHP? Then what's the need for cleaning?

Question

I'm reading up on web security and one obvious topic to cover is SQL injections. I'm trying to set up a basic PHP page where I can execute an SQL injection (it's a local server). However, it seems my code (or server) automatically escapes single quotes. Is this a new standard or is there a setting that's activated on my server that I don't know about? Is there a need to clean input any more?

Here's an example of my server side code:

$foo = $_POST['foo'];
$sql = "SELECT * FROM bar WHERE foo='" . $foo . "'";

connectoTo("database");
query($sql);

Where connectTo() connects to the database server and selects the database and query() is the usual procedure used when executing a query. No cleaning what so ever. However, when I'm sending

$_POST['foo'] = "' OR 1=1 #" 

the PHP page receives this as

$_POST['foo'] = "' OR 1=1 #"

So is foo already escaped? This is the same with $_GET.

Any thoughts? Do we not need to clean user input any more?

Answer 1

There is a dead "feature" in PHP that would automatically escape POST/GET data called Magic Quotes. The idea was to keep common types of SQL injection from happening.

In reality, you get jumbled data, and SQL injection was still very possible, depending on the implementation. The developers of PHP quickly realized this and deprecated the feature, and discouraged its use.

In a proper PHP installation, this should absolutely be disabled! If you do not have access to PHP.ini to set magic_quotes_gpc off, then you can put this at the top of your code:

if (get_magic_quotes_gpc()) {
    $process = array(&$_GET, &$_POST, &$_COOKIE, &$_REQUEST);
    while (list($key, $val) = each($process)) {
        foreach ($val as $k => $v) {
            unset($process[$key][$k]);
            if (is_array($v)) {
                $process[$key][stripslashes($k)] = $v;
                $process[] = &$process[$key][stripslashes($k)];
            } else {
                $process[$key][stripslashes($k)] = stripslashes($v);
            }
        }
    }
    unset($process);
}

Taken from: http://www.php.net/manual/en/security.magicquotes.disabling.php

Now, on to your SQL injection problem. You see, there are far more things to worry about than just quotes. You don't specify which database you are using, but it doesn't matter. The best way to avoid injection issues is by using prepared/paramterized queries.

Prepared queries are queries sent to the server with parameters, whose values are sent later.

INSERT INTO someTable (field1, field2) VALUES (:field1, :field2);

Note the :field1 and :field2, as they are parameters. When I execute this statement, those will be replaced with the proper values. Since the server is doing it, no escaping is necessary (and/or it happens in the background for you, depending on the DB layer you are using).

The easiest way to implement this in PHP is by utilizing PDO. How to use PDO is too long for this box, so I will point you in the direction of a tutorial:

http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/