Escaping Column Name with Pdo

escaping column name with PDO

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.

Escaping column names in PDO statements

The ANSI standard way of doing a delimited identifier is:

SELECT "field1" ...

and if there's a " in the name, double it:

SELECT "some""thing" ...

Unfortunately this doesn't work in MySQL with the default settings, because MySQL prefers to think double quotes are an alternative to single quotes for string literals. In this case you have to use backticks (as outlined by Björn) and backslash-escaping.

To do backslash escaping correctly, you would need mysql_real_escape_string, because it's character-set-dependent. But the point is moot, because neither mysql_real_escape_string nor addslashes escape the backquote character. If you can be sure there will never be non-ASCII characters in the column names you can get away with just manually backslash-escaping the ` and \ characters.

Either way, this isn't compatible with other databases. You can tell MySQL to allow the ANSI syntax by setting the config option ANSI_QUOTES. Similarly, SQL Server also chokes on double quotes by default; it uses yet another syntax, namely square brackets. Again, you can configure it to support the ANSI syntax with the ‘quoted_identifier’ option.

Summary: if you only need MySQL compatibility:

a. use backquotes and disallow the backquote, backslash and nul character in names because escaping them is unreliable

If you need cross-DBMS compatibility, either:

b. use double quotes and require MySQL/SQL-Server users to change the configuration appropriately. Disallow double-quote characters in the name (as Oracle can't handle them even escaped). Or,

c. have a setting for MySQL vs SQL Server vs Others, and produce either the backquote, square bracket, or double-quote syntax depending on that. Disallow both double-quotes and backslash/backquote/nul.

This is something you'd hope the data access layer would have a function for, but PDO doesn't.

Summary of the summary: arbitrary column names are a problem, best avoided if you can help it.

Summary of the summary of the summary: gnnnnnnnnnnnh.

Escape a preset column name in PHP PDO

Identifiers such as columnNames and tableNames shouldn't be wrap with single quotes, but instead with backtick. Wrapping with single quotes converts the identifier to become string literals.

INSERT INTO myTbl.uploads(id, name, `group`) VALUES (:id,:user,:group)

• When to use single quotes, double quotes, and backticks?

Can PHP PDO Statements accept the table or column name as parameter?

Table and Column names CANNOT be replaced by parameters in PDO.

In that case you will simply want to filter and sanitize the data manually. One way to do this is to pass in shorthand parameters to the function that will execute the query dynamically and then use a switch() statement to create a white list of valid values to be used for the table name or column name. That way no user input ever goes directly into the query. So for example:

function buildQuery( $get_var ) 
{
    switch($get_var)
    {
        case 1:
            $tbl = 'users';
            break;
    }

    $sql = "SELECT * FROM $tbl";
}

By leaving no default case or using a default case that returns an error message you ensure that only values that you want used get used.

safely escaping table names/column names

You can use a dynamic white list by asking the database what columns are valid for a given database table. It's an additional sql query, but safety is good.

select COLUMN_NAME 
from INFORMATION_SCHEMA.COLUMNS 
where TABLE_SCHEMA = :databaseName
  and TABLE_NAME = :tableName

Fetch the results of that and then just make sure all the dynamic column names are in the result set.

I believe views are included in INFORMATION_SCHEMA.COLUMNS, so it should all just plain work.

Then just use backticks around the validated column names when assembling the dynamic sql(I assume you use purely ascii column names, otherwise you potentially have additional considerations).

Escape a pdo query, is that necessary?

The parts of your query that are dynamic are the table name and column names. You can't use bind functions for these parts of the query. Bind functions can be used only for the parts of the query that would otherwise be a simple value in an SQL query. Like a numeric constant, or a quoted string or quoted date literal.

To avoid SQL injection from dynamic table names or column names, you have the following choices:

• Use values that are predefined in your class, or otherwise certain to be safe. Don't use external content from users or any other source.
• Use escaping. Note that the function PDO::quote() doesn't do the kind of escaping you need for table names or column names.
• Create a "allowlist" of known table names and the column names for the respective table, and compare the dynamic input to the allowlist. If it doesn't match the allowlist, raise an error.

how to write column name with space in pdo prepared statement

The escape character in MySQL is the back quote (or back tick). Here is an example of what it looks like:

UPDATE table1 
    SET `COL 1` = :c1
     where table1_id = :id

---

Related Topics