escaping column name with PDO

Question

I have a function that's like

function getInfoById($id, $info) {

}

the idea is to have a query be "SELECT $info FROM table WHERE id = $id"

This doesn't work with PDO because you can't escape column names. I also don't really want to use "SELECT *" because doesn't that return a bigger result set and use more memory?

Answer 1

Yes, PDO does not have a builtin function for delimiting identifiers like table names and column names. The PDO::quote() function is only for string literals and date literals.

For what it's worth, when I worked on Zend Framework, I implemented a quoteIdentifier() function.

You're right that SELECT * fetches all columns, likely using more memory and spoiling the benefit of covering indexes.

My recommendation is to create an allowlist column names. That is, make sure $info actually names a column of table. Then you don't need to worry about the column name not existing, or containing a strange character, or anything. You get to control the set of columns that are legitimate to put in the query.

You should also delimit the column name anyway. Delimited identifiers are necessary if the column name contains punctuation, whitespace, international characters, or matches an SQL reserved word. See Do different databases use different name quote?

function getInfoById($id, $info) {
    // you can make this a literal list, or query it from DESC or INFORMATION_SCHEMA
    $cols = array('col1', 'col2', 'col3');

    if (array_search($info, $cols) === false) {
      return false;
    }
    $sql = "SELECT `$info` FROM table WHERE id = :id";
    $stmt = $pdo->prepare($sql);
    if ($stmt === false) {
      return false;
    }
    . . .
}

I show more examples of allowlisting in my presentation SQL Injection Myths and Fallacies or my book SQL Antipatterns, Volume 1: Avoiding the Pitfalls of Database Programming.

Answer 2

I would just filter it out with some regex. Keep it simple.

Also, you should bind $id and have it be :id

$info = preg_replace('/[^A-Za-z0-9_]+/', '', $info);

$stmt = $pdo->prepare('SELECT $info FROM table WHERE id = :id'); 
$stmt->bindParam(':id', $id);
$stmt->execute();