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?

I think you're going about this all wrong! Not only is it good practice to escape user input but also validate the input they are entering. Example, if you have a field for first name you should validate for the first name. Maybe only allowing A-Za-z -. Or maybe the field is numeric only, so 0-9 would be the validation — Phill Pafford, Jun 15 '11 at 13:25
I agree with you 100% I'm just exploring the concept of sql injections for learning purpose and it seems that as long as magic_quote is enabled there's no need to clean data... Is it possible to still perform an sql injection with magic_quote enabled seems to be my real question. — Erik, Jun 15 '11 at 13:28
Even with magic_quote disabled this will still execute: $_POST['foo'] = "\' OR 1=1 " as you're not validating the data. There is not setting to disable to protect from injection! — Phill Pafford, Jun 15 '11 at 13:31
with magic quote enabled $_POST['foo'] = " OR 1=1 " would just set the query to "SELECT * FROM bar WHERE foo=' OR 1=1'" which really isn't doing much to the query at all. — Erik, Jun 15 '11 at 13:34
typo, sorry fixed it. The second example you're escaping the single quote so it should execute — Phill Pafford, Jun 15 '11 at 13:36
Actualy it just escapes both the \ and the ' automatically so it still doesnt execute... You would get SELECT * FROM bar WHERE foo='\\\' OR 1=1' which again doesnt really do much — Erik, Jun 15 '11 at 13:54

score 8 · Accepted Answer · answered Jun 15 '11 at 13:27

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/

I agree with you completely, prepared queries is definitively the way to go! But what type of sql injection would still work on a input where magic quotes is enabled? — Erik, Jun 15 '11 at 13:32
http://stackoverflow.com/questions/1086918/what-characters-have-to-be-escaped-to-prevent-mysql-injections — Brad, Jun 15 '11 at 13:33
http://stackoverflow.com/questions/110575/do-htmlspecialchars-and-mysql-real-escape-string-keep-my-php-code-safe-from-injec — Brad, Jun 15 '11 at 13:34
Why the downvote? Who downvotes without explaining? Even if you disagree with me, it would certainly be to Erik's benefit if you could explain yourself. — Brad, Jun 15 '11 at 13:46
Hmm I'm still not able to get any sql injections to work. I understand there's fundamental flaws with magic quotes but I would like to see a real world example of an sql injection that will bypass the magic quote security. So far it seems like magic quote does protect against any sql injection... — Erik, Jun 15 '11 at 13:52

score 6 · Answer 2 · edited Apr 16 '12 at 11:42

6

Disable magic_quote in php.ini and use PDO.

edited Apr 16 '12 at 11:42

answered Jun 15 '11 at 13:19

dynamic

46,985
55
154
231

So is it still possible to perform an sql injection? – Erik Jun 15 '11 at 13:29
@Erik, absolutely. See my post. – Brad Jun 15 '11 at 13:30

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

2 Answers2

Linked

Related