PHP: using prepared statements and protecting against SQL injection vs escape

Question

I do understand that the prepared statements is the ultimate way to seek protection against the SQL injection. However, they provide coverage in a limited fashion; for example, in cases where I let the user to decide how the order by operation to be ( i.e, is it ASC or DESC? etc ), I get no coverage there with the prepared statements.

I understand that I can map the user input to a pre-defined white list for that. But, this is only possible when a whitelist can be created or guessed thoroughly beforehand.

For example, in the cases I mention above ( the ASC, or DESC ), this can easily be mapped and verified against a list of accepted values. But isn't there a situation where the portion of the SQL statement cannot be verified against a white list?

If such a situation exists, then what's the recommended approach?

If I were to escape the user_input using the underlying database's built-in escape utility (such as mysqL_real_escape_string for mysql) across the board, where would I fail?

I'm asking this question with the assumption that I always construct my sql statements with quoted values - even for integers...

Let's take a look at the following example and reflect upon it..

select {$fields} from {$table} where Age='{$age}' order by {$orderby_pref}

Assume all vars are user supplied.

If I were to mysql_real_escape_string all the variables in the above SQL ( as opposed to using prepared statements which covers me only half-way forcing me to come up whitelists for the other half that it cannot help), wouldn't it be equally safe (and easier to code)? If not, in which input scenario escape utility would fail?

$fields       = mysql_escape($fields);
$table        = mysql_escape($table);
$age          = mysql_escape($age);
$orderby_pref = mysql_escape($orderby_pref);

select {$fields} from {$table} where Age='{$age}' order by {$orderby_pref}

Chabot

Upvoted because you know and care about sql injections (as opposed to most people asking questions here in the PHP tag) – Maziemazlack

There's a difference between dynamic sql and adding values into queries. Both bound parameters or string concatenation (plus escaping) could be used for adding values. Dynamic SQL requires whitelisting acceptable syntax. So either use a regex, a map, or switch statement for adding DESC or ORDER and other qualifiers. Stored procedures would be a long-winded alternative though. – Chiller

Answer 1

You always need to use white-lists for stuff like table- or column names, whether you use prepared statements or the mysql escape functions.

The problem is that table names and column names are not quoted in single or double quotes, so if you use a function that specifically quotes these characters (and some more of course...), it will do nothing for your table name.

Consider the table name my_table; DELETE * FROM mysql; SELECT * FROM my_table. Nothing in this string will get escaped by mysql's escape functions but it is definitely a string you would want to check against a white-list.

Apart from that the mysql escape functions have a problem with character sets that can render them useless, so you are always better off with prepared statements.

Downey

YOUR EXAMPLE OF my_table; DELETE * FROM mysql; SELECT * FROM my_table DOES NAIL IT DOWN VERY WELL AS TO WHY THE MYSQL_ESCAPE APPROACH WOULD FAIL.... So, in plain and simple talk, the only alternative for the SQL injection protection (for the portions where the prepared statements or the db's escape utility does not help), is to use the whitelist approach. Is this conclusion correct? – Chabot

Answer 2

You could use PDO and your life will get easier ... :

    #   Order
    switch(strtoupper($Order)){
        default:
        case 'ASC':
            $Order = 'ASC';
            break;

        case 'DESC':
            $Order = 'DESC';
            break;
    }

    #   ID
    $ID = 39;
    $Username = 'David';

    #   Query
    $Query = $this->DB->Main->prepare('SELECT * FROM Table WHERE ID = :ID AND Username = :Username ORDER BY HellBob '.$Order);
    $Query->bindValue(':ID', $ID, PDO::PARAM_INT);
    $Query->bindValue(':Username', $Username, PDO::PARAM_STR);

    #   All good ?
    if(!$Query->execute()){
        exit('Error');
    }

    // Results
    $Row = $Query->fetch(PDO::FETCH_ASSOC);

You don't have to worry about quotes or SQL injections. You can use simple "white list" as you mention to get variable into your query.

Trunnion

you forgot the ordering field ;-) – Sumbawa

This doesn't really answer the question as you have hard-coded the table name, column names and sort-order column in the query. – Downey

Then, you could just use white list to write table name. There's no safe way except white list. – Shred