Can I use a PDO prepared statement to bind an identifier (a table or field name) or a syntax keyword?

by

Unfortunately, placeholder can represent a data literal only. So, a very common pitfall is a query like this:

$opt = "id";
$sql = "SELECT :option FROM t";
$stm  = $pdo->prepare($sql);
$stm->execute([':option' => $opt]);
$data = $stm->fetchAll();

This statement will return just a literal string 'id' in the fieldset, not the value of the column named id.

So, a developer must take care of identifiers oneself – PDO offers no help for this matter.

To make a dynamical identifier safe, one has to follow 2 strict rules:

  • to format identifiers properly
  • to verify it against a hardcoded whitelist.

To format an identifier, one has to apply these 2 rules:

  • Enclose the identifier in backticks.
  • Escape backticks inside by doubling them.

After such formatting, it is safe to insert the $table variable into query. So, the code would be:

$field = "`".str_replace("`","``",$field)."`";
$sql   = "SELECT * FROM t ORDER BY $field";

However, although such formatting would be enough for the cases like ORDER BY, for most other cases there is a possibility for a different sort of injection: letting a user choose a table or a field they can see, we may reveal some sensitive information, like a password or other personal data. So, it’s always better to check dynamical identifiers against a list of allowed values. Here is a brief example:

$allowed = array("name","price","qty");
$key     = array_search($_GET['field'], $allowed);
$field   = $allowed[$key];
$query   = "SELECT $field FROM t"; //value is safe

For keywords, the rules are same, but of course there is no formatting available – thus, only whitelisting is possible and ought to be used:

$dir = $_GET['dir'] == 'DESC' ? 'DESC' : 'ASC'; 
$sql = "SELECT * FROM t ORDER BY field $dir"; //value is safe

More Related Contents:

  1. PHP – Using PDO with IN clause array
  2. pdo prepared statements with wildcards
  3. Insert multiple rows with PDO prepared statements
  4. Replacing mysql_* functions with PDO and prepared statements
  5. PDOstatement (MySQL): inserting value 0 into a bit(1) field results in 1 written in table
  6. Calling stored procedure with Out parameter using PDO
  7. When *not* to use prepared statements?
  8. PDO error: SQLSTATE[HY000]: General error: 2031
  9. Use of PDO in classes [duplicate]
  10. Error while using PDO prepared statements and LIMIT in query [duplicate]
  11. Insert form in MySQL with PDO [duplicate]
  12. Undefined index $_POST
  13. Getting raw SQL query string from PDO prepared statements
  14. How do I set ORDER BY params using prepared PDO statement?
  15. “Invalid parameter number: parameter was not defined” Inserting data
  16. MySQL Prepared statements with a variable size variable list
  17. Having issue with matching rows in the database using PDO
  18. How can I properly use a PDO object for a parameterized SELECT query
  19. How do I return integer and numeric columns from MySQL as integers and numerics in PHP?
  20. mysqli_stmt::bind_param(): Number of elements in type definition string doesn’t match number of bind variables
  21. PHP PDO vs normal mysql_connect
  22. Can I create a database using PDO in PHP?
  23. PHP PDO and MySQLi [duplicate]
  24. PDO fetch one column from table into 1-dimensional array
  25. Getting a PHP PDO connection from a mysql_connect()?
  26. Having issue with matching rows in the database
  27. PHP Laravel: No connection could be made because the target machine actively refused it
  28. PDO bindParam into one statement?
  29. What is the PDO equivalent of function mysql_real_escape_string?
  30. Installing PDO driver on MySQL Linux server

Leave a Comment