title: PHP: using prepared statements and protecting against SQL injection vs escape
body: <p>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.  </p>
  
  <p>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. </p>
  
  <p>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? </p>
  
  <p>If such a situation exists, then what's the recommended approach? </p>
  
  <p>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? </p>
  
  <p>I'm asking this question with the assumption that I always construct my sql statements with quoted values - even for integers... </p>
  
  <p>Let's take a look at the following example and reflect upon it..</p>
  
  <pre><code>select {$fields} from {$table} where Age='{$age}' order by {$orderby_pref}
  </code></pre>
  
  <p>Assume all vars are user supplied. </p>
  
  <p>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?</p>
  
  <pre><code>$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}
  </code></pre>