Escape Column Name with PDO

Question

I have a function that I like

function getInfoById($id, $info) { } 

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

This does not work with PDO because you cannot escape column names. I also do not want to use "SELECT *" because it does not return a larger set of results and uses more memory?

Answer 1

Yes, PDO does not have a built-in function for delimiting identifiers such as table names and column names. The PDO::quote() function is intended only for string literals and date literals.

What is it worth when I worked on the Zend Framework, I implemented the quoteIdentifier() function.

You are correct that SELECT * fetches all columns, most likely using more memory and ruining the advantage of covering indexes.

My recommendation is whitelist column names. That is, make sure that $ info actually names the table column. Then you don’t need to worry that the column name does not exist or contains a strange character or something else. You can manage a set of columns that may be legal for a query.

You must also delimit the column name. Separate identifiers are required if the column name contains punctuation, spaces, international characters, or matches a reserved SQL word. See Do different databases use different quotes with names?

 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 whitelists in my presentation of SQL Injection Myths and Fallacies .

Answer 2

I would just filter it with some regular expression. Keep it simple.

In addition, you must bind $id and have it :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();