How PDO handles the escape of field names and table names in SQL statements

For example, in a data table there is a field called orderis specifically used for sorting, and my statement may have to be written like this.

$sth = $pdo->prepare (' SELECT * FROM table_name ORDER by order ASC '); $sth->execute ();

But because order itself is a keyword, then in the execution of the time will be reported syntax error, I know in PDO in the field can be used :column_name to dynamically bind, it will automatically handle the quotation marks and other problems, but also can be used $pdo->quote to escape the string, but there is no method to escape the field name and table name.

This kind of escape is the same in different database systems, such as in MySQL.

SELECT * FROM table_name ORDER BY ' order ' ASC

And in SQLite.

SELECT * FROM table_name ORDER BY "order" ASC

Is there a common and easy way to deal with this problem?

Update

Finally, a general escape method is summed up according to the answer.

$escapes = Array (' mysql ' = = Array (' '    , ', '),    ' mssql ' = = '    array (' [', '] ') '); $driver = $ Db->getattributes (pdo::attr_driver_name); $escape = Isset ($escapes [$driver])? $escapes [$driver]: Array (' "', '" '); $field = $escape [0]. $field. $escape [1];

Reply content:

For example, in a data table there is a field called order , is specifically used to do the sorting, my statement may be so written

$sth = $pdo->prepare (' SELECT * FROM table_name ORDER by order ASC '); $sth->execute ();

But because order itself is a keyword, then in the execution of the time will be reported syntax error, I know in PDO in the field can be used :column_name to dynamically bind, it will automatically handle the quotation marks and other problems, but also can be used $pdo->quote to escape the string, but there is no method to escape the field name and table name.

This kind of escape is the same in different database systems, such as in MySQL.

SELECT * FROM table_name ORDER BY ' order ' ASC

And in SQLite.

SELECT * FROM table_name ORDER BY "order" ASC

Is there a common and easy way to deal with this problem?

Update

Finally, a general escape method is summed up according to the answer.

$escapes = Array (' mysql ' = = Array (' '    , ', '),    ' mssql ' = = '    array (' [', '] ') '); $driver = $ Db->getattributes (pdo::attr_driver_name); $escape = Isset ($escapes [$driver])? $escapes [$driver]: Array (' "', '" '); $field = $escape [0]. $field. $escape [1];

As far as my experience is concerned, there is no such thing as, perhaps you can only use the method of judging driver to achieve the goal. You can write a function or method specifically for this:

function Add_quote_identifier ($field, $driver = ' mysql ') {    switch ($driver) {case        ' mysql ':            return sprintf ('%s ', $field);        Case ' SQLite ':            return sprintf (' "%s" ', $field);        Default:            return $field;    }}

And then use him to prepare statement:

$stmt = $dbh->prepare (sprintf (' SELECT * FROM table_name ORDER by%s ASC ', Add_quote_identifier (' order '));

Combined with @Chris Yue scheme, you can obtain directly from the connection instance what driver is:

... $driver = $db->getattributes (pdo::attr_driver_name); Echo $driver; such as MySQL

But I think the more constructive question is: "How to avoid the use of reserved words", because the existence of illegal identities should not be facilitated:

• First of all, there is a technology-independent aspect: At some point, "actually use the reserved word" will become gossip, right? I've seen a lot of this kind of situation. Since we all know not to use reserved words, why bother?
• DBAs or other people who are accustomed to command line, write large amounts of SQL, and occasionally have to pay attention to the field escape, very annoying.
• Each person's toolset will not be completely unified, there is always such a backup or analysis tool is not automatically escaped, may lead to waste of time and energy.
• Rmdbs version (SQL standard also always change) who is poor can not lift, so the major ORM after discussion decided not to do table name and field automatic escape

Avoiding reserved words is simple:

• Table name plus a prefix is finished, such as Gfw_ , who is so wicked will use this as a reserved word?
• Field names should be confirmed by a design document, or at least a common place to annotate, to identify problems before code is many, and to accumulate into canonical

1. Chris Yue's answer is to see a recruit to help you solve the legacy of history.
2. Modi's answer is good design rules that can help you avoid this kind of problem in new projects, by contrast, I agree with the answer that this type of field and table name in a history project is best done by refactoring.
3. There are many features that PDO does not support, such as SELECT * FROM table_name where ID in (: id_lists) is not supported, so later I do not use PDO preprocessing in lotusphp, Instead of using your own PHP method to treat SQL statements as string templates, this is a helpless choice, contrary to the original intention of the preprocessing statement. Now that you've chosen PHP and PDO, it's best to use its most reliable features, which, as Modi says, avoids the hassle in the design phase.
4. MySQL Prepared statements actually supports table names with placeholders, like this:

SELECT * from @table; set @table = ' sf_questions ';

