0

I always use PDO when making MySQL connections from PHP and it works as expected when my execution variables are parts of the WHERE statements in the query. An example of this could be this:

$stmt = $dbh->prepare("SELECT name FROM users WHERE id = :id");
$stmt->execute(['id' => 1]);

Let's say I want to fetch a dynamic column name in the query above. I would expect the following query to work:

$stmt = $dbh->prepare("SELECT name, :dynamic_column FROM users WHERE id = :id");
$stmt->execute(['dynamic_column' => 'age', 'id' => 1]);

...but it doesn't. The only way I can add dynamic column names or table names to a query is by adding php-variables directly to the query like this:

$stmt = $dbh->prepare("SELECT name, $dynamic_column FROM users WHERE id = :id");
$stmt->execute(['id' => 1]);

... which will work. But by going with this solution, the whole point of PDO is gone.

My question is now; what is the best practice when the variable I want to add to my query is a table name or a column name?

Simon Degn
  • 901
  • 1
  • 12
  • 40
  • 2
    I'm not sure about best practice, but I would recommend creating arrays of acceptable values, and check the input against that. https://stackoverflow.com/questions/182287/can-php-pdo-statements-accept-the-table-or-column-name-as-parameter has some options – aynber Sep 29 '21 at 12:17
  • 1
    To elaborate on what aynber said, make a list of allowed values and check against that first. Then concatenate the field or table name into the query. You cannot use prepared statements to sanitize them. – Machavity Sep 29 '21 at 12:19

0 Answers0