How does PDO handle escape of Field Names and table names in SQL statements?

For example, if an order field exists in a data table and is used for sorting, my statement may need to write {code...} like this ...} however, because order itself is a keyword, the syntax error will be reported during execution. I know that the field in pdo can be dynamic with: column_name... for example, a data table has a field named orderIs used for sorting, and my statements may need to be written in this way.

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

But becauseorderIt is a keyword, so the syntax error will be reported during execution. I know that the field can be used in pdo.:column_nameIt automatically handles quotation marks and other issues. You can also use$pdo->quoteTo escape the string, but there is no way to escape the field name and table name.

This escape is the same in different database systems, for example, in mysql

SELECT * FROM table_name ORDER BY `order` ASC

In sqlite

SELECT * FROM table_name ORDER BY "order" ASC

Is there a general and easy way to solve this problem?

Update

According to the answer, a general escape method is summarized.

$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, a data table has a field namedorderIs used for sorting, and my statements may need to be written in this way.

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

But becauseorderIt is a keyword, so the syntax error will be reported during execution. I know that the field can be used in pdo.:column_nameIt automatically handles quotation marks and other issues. You can also use$pdo->quoteTo escape the string, but there is no way to escape the field name and table name.

This escape is the same in different database systems, for example, in mysql

SELECT * FROM table_name ORDER BY `order` ASC

In sqlite

SELECT * FROM table_name ORDER BY "order" ASC

Is there a general and easy way to solve this problem?

Update

According to the answer, a general escape method is summarized.

$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 problem. In this case, you may only have to use the method of judging the driver to achieve your 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;    }}

Then use him to prepare statement:

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

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

//... $ Driver = $ db-> getAttributes (PDO: ATTR_DRIVER_NAME); echo $ driver; // such as mysql

However, I think the more constructive question is: "How to avoid using Reserved Words", because it should not facilitate the existence of illegal identifiers:

• First, it has nothing to do with technology: in some cases, "reserved words are used" will become the phone handle, right? I have seen a lot of such situations. Since we all know that we should not use reserved words, why?
• DBAs or other users who are used to using command lines and writing a large number of SQL statements should pay attention to field escaping from time to time.
• The tool set of each person is not completely unified. There is always such a backup or analysis tool that is not automatically escaped, which may lead to a waste of time and energy.
• The version of RMDBS (the SQL standard is always changed) cannot be lifted by anyone, so the major ORM decided not to automatically escape table names and fields after discussion.

It is easy to avoid reserved words:

• The table name must have a prefix, for exampleGfw _And so on, who will use this word as a reserved word for such a defect?
• The field name should be confirmed by a design document, or at least everyone has a common place to mark it. problems are found before many codes are coded and gradually accumulated into specifications.

1. Chris Yue's answer is to see the recruitment and removal actions to help you solve historical issues.
2. Modi's answer is a good design rule that helps you avoid such problems in new projects. In contrast, I agree with this answer, such fields and table names in historical projects, it is best to eliminate it through refactoring.
3. PDO does not support many other features. For example, SELECT * FROM table_name WHERE id IN (: id_lists) does not support this feature. Therefore, I did not use the PDO preprocessing feature IN lotusphp, use your PHP method to process SQL statements as string templates. This is against the original intention of the pre-processing statement and is a helpless choice. Since you have chosen PHP and PDO, it is best to use its most reliable features, as modi says, to avoid these troubles during the design phase.
4. MySQL Prepared Statements supports using placeholders for table names, as shown in the following figure:

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