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

Question

I understand that prepared statements are the best way to find protection against SQL injection. However, they provide coverage in a limited manner; for example, in cases where I allow the user to decide how the order should work (i.e. is it ASC or DESC? etc.), I do not get any coverage with prepared operations there.

I understand that for this I can map the user's entry to a predefined whitelist. But this is only possible when the whitelist can be created or guessed in advance in advance.

For example, in the cases that I mentioned above (ASC or DESC), this can be easily compared and checked using a list of accepted values. But isn’t there a situation where part of the SQL statement cannot be checked against the whitelist?

If this situation exists, then what is the recommended approach?

If I were to avoid user_input using the built-in utility to run the database (such as mysqL_real_escape_string for mysql) all over the board, where would I fail?

I ask this question with the assumption that I always create my SQL expressions with quoted values ​​- even for integers ...

Let's look at the following example and look at it.

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

Suppose all the vars are user-provided.

If I were mysql_real_escape_string with all the variables in the aforementioned SQL (as opposed to using prepared statements that cover me only halfway, forcing me to whitelist the other half, that can't help), is it equally safe (and easier to code )? If not, in which the login script output utility error failed?

 $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} 

Answer 1

You always need to use whitelists for things like table or column names , regardless of whether you use prepared statements or mysql escape functions.

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

Consider the table name my_table; DELETE * FROM mysql; SELECT * FROM my_table my_table; DELETE * FROM mysql; SELECT * FROM my_table my_table; DELETE * FROM mysql; SELECT * FROM my_table . Nothing on this line will be escaped by mysql escape functions, but it is definitely the line you want to check against whitelisting.

In addition, mysql escape functions have a problem with character sets that can make them useless, so you are always better prepared statements.

Answer 2

You can use PDO and your life will be 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 do not need to worry about quotes or SQL injections. You can use a simple whitelist, as you mentioned, to get a variable in your query.