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?

possible duplicate of [Escaping column names in PDO statements](http://stackoverflow.com/questions/1542627/escaping-column-names-in-pdo-statements) — hakre, Mar 03 '14 at 10:12

Bill Karwin · Accepted Answer · 2022-10-12T16:49:36.533

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.

I agree, whitelisting is the way to go. – wesside Nov 19 '12 at 07:16 — wesside, Nov 19 '12 at 07:16
`in_array` with `true` for strict comparison – Bell Feb 03 '17 at 16:15 — Bell, Feb 03 '17 at 16:15

score 6 · Answer 2 · edited Nov 19 '12 at 06:05

6

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();

edited Nov 19 '12 at 06:05

NullPoiиteя

56,591
22
125
143

answered Nov 19 '12 at 05:45

wesside

5,622
5
30
35

1

Well it's better than interpolating any arbitrary $info, but it's an incomplete solution. For example, if my column name is `order` (an SQL reserved word), the query breaks. – Bill Karwin Nov 19 '12 at 06:25
1

@BillKarwin bill, I agree, though I was just making a point. Like you said above, whitelisting is the way to go, it's how I prefer to handle these things myself. I said the same thing here: http://stackoverflow.com/questions/13424009/updating-sql-column/13424037#13424037 Just went through your Injection slideshow, good stuff. – wesside Nov 19 '12 at 07:18
Well, add quotes around it SELECT "$info" FROM... That should also allow query to be used as column – Torge Apr 23 '19 at 14:32

score -1 · Answer 3 · answered Dec 16 '18 at 02:27

-1

What about using quote and substr.

$sql = 'SELECT * FROM table WHERE `' . substr($db->quote($field), 1, -1) . '` = :id';

This will remove the quotes surrounding the escaped field.

answered Dec 16 '18 at 02:27

SequenceDigitale.com

4,038
1
24
23

1

Dangerous! If `$field` contains a Backtick, this fails horribly and allows SQL-Injections. – Matteo B. Oct 06 '21 at 09:01

escaping column name with PDO

3 Answers3

Linked