How come this vulnerable PDO works instead of prepared statement?

Question

There is only one way my PDO query returns result I want, but proper prepared statement gives out only column name.

This returns column name instead of queried row:

$queryPrice = "SELECT :zone FROM express WHERE kg >= :kg LIMIT 1";
$stmt = $conn->prepare($queryPrice);
$stmt->bindParam(':zone', $zone, PDO::PARAM_STR);
$stmt->bindParam(':kg', $_SESSION['weight'], PDO::PARAM_STR);
$stmt->execute();
$price = $stmt->fetchColumn();

This works, but is vulnerable to injection:

$queryPrice = "SELECT $zone FROM express WHERE kg >= :kg LIMIT 1";
$stmt = $conn->prepare($queryPrice);
$stmt->bindParam(':kg', $_SESSION['weight'], PDO::PARAM_STR);
$stmt->execute();
$price = $stmt->fetchColumn();

Why doesn't prepared statement for column name work?

Is there a safe way to achieve desired results?

Table and Column names cannot be replaced by parameters in PDO. Check this [link](http://stackoverflow.com/questions/182287/can-php-pdo-statements-accept-the-table-name-as-parameter) — Saty, Aug 28 '15 at 08:21
String interpolation and SQL injection aren't necessarily the same. Depends on where the column value originates from, if it was preset, or whitelisted user input. — mario, Aug 28 '15 at 08:29
To safely inject a table name, use string concatenation with a "safe list" of allowed values. — halfer, Aug 28 '15 at 08:33
You could achieve this with a stored procedure - to which you could pass the relevant colmn name or table name as parameters in your pdo prepared statement — Professor Abronsius, Aug 28 '15 at 08:35
`if($zone === 'Europe' || $zone ==== 'America'){ $safeZone = $zone }` `else { echo 'error'; }` is valid whitelist? — JessicaGonzalez, Aug 28 '15 at 08:38

Your Common Sense · Answer 1 · 2015-08-28T08:51:02.883

On the second thought your question appears to be caused by bad database design.

Instead of having zones as columns in the table, you have to have them as data in the single column in another table. And it is clearly proven by the fact that you are trying to address a column name the way only data have to be addressed.

You have reorganize your table, leaving only one column for zones, name for example. And you'll be able to select your zone with a query like this

SELECT name FROM zones WHERE kg > :kg

How come this vulnerable PDO works instead of prepared statement?

1 Answers1