escaping column name with PDO

by

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.

More Related Contents:

  1. PHP PDO and Mysql [closed]
  2. PDO Fetch statement only retrieves first column
  3. How to properly set up a PDO connection
  4. PHP PDO prepared statements
  5. PDO MySQL: Use PDO::ATTR_EMULATE_PREPARES or not?
  6. PDO Prepared Inserts multiple rows in single query
  7. Why I am getting Cannot pass parameter 2 by reference error when I am using bindParam with a constant value?
  8. Where to put password_verify in login script?
  9. mysql PDO how to bind LIKE
  10. Connect PHP to MSSQL via PDO ODBC
  11. PDO closing connection
  12. How to get the total number of rows of a GROUP BY query?
  13. How do I use pdo’s prepared statement for order by and limit clauses?
  14. What is PDO & why should I use it?
  15. How to install pdo driver in php docker image?
  16. MySQL retrieve variable from Stored Procedure in PHP PDO
  17. Understanding pdo mysql transactions
  18. In PHP with PDO, how to check the final SQL parametrized query? [duplicate]
  19. Error while using PDO prepared statements and LIMIT in query [duplicate]
  20. PDO prepared statement fetch() returning double results
  21. SQLSTATE[HY093]: Invalid parameter number: parameter was not defined
  22. PDO::rowCount VS COUNT(*)
  23. PHP Warning: PHP Startup: Unable to load dynamic library ‘pdo_mysql.so’
  24. Fatal error: Call to a member function bindParam() [duplicate]
  25. How to escape single-quote (apostrophe) in string using php
  26. php, mysql – Too many connections to database error
  27. Work-around for PHP5’s PDO rowCount MySQL issue
  28. Insert value list does not match column list: 1136 Column count doesn’t match value count
  29. PHP Validating the File Upload [duplicate]
  30. PDO and MySQL ‘between’

Leave a Comment