PDO ATTR_EMULATE_PREPARES = false, SHOW TABLES LIKE and syntax error

Question

Here is such a mistake

SQLSTATE [42000]: Syntax error or access violation: 1064 SQL syntax; check the syntax to use mySQL? at line 1

occurs when trying to prepare a query of the form

SHOW TABLES LIKE :1

when disabling the emulation of prepared requests in the code

 $conn = new PDO('mysql:dbname=testdb;host=127.0.0.1;charset=utf8', 'root', 'root', [ PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, ]); $sth = $conn->prepare('SHOW TABLES LIKE :1'); <-- тут ошибка $sth->execute([':1' => 'test']); var_dump($sth->fetchAll());

PHP 7.0.4 and 5.6.13
MySQL 5.6.20
Windows 7 :)

Question: What to do? Is this my local glitch or PDO globally curved?

UPD With a view request

 SHOW COLUMNS FROM `test` LIKE ?

similar situation.

Generally a strange idea as a named parameter to use a number.
, and ': table', the error does not disappear until I delete PDO::ATTR_EMULATE_PREPARES => false,
PDO gives you a fundamental opportunity to fulfill a request in which IOs are not supported, and you give it for it.

Small small 14.4k one ten 35 · Accepted Answer · 2017-03-05T14:41:52

mysql> prepare stmt1 from 'show tables like?';
ERROR 1064 (42000): You have an error in your SQL syntax; check the syntax to use mySQL? at line 1

It seems to me that the prepared statements in mysql basically can not process show tables requests.

Exactly , this is mentioned in the manual. In mysql it is allowed to prepare only a specific list of expressions, SHOW TABLES and SHOW COLUMNS are not included in the list of supported expressions. And emulation of prepared expressions, respectively, successfully hides this restriction.

Use queries to the pseudo-tables of the INFORMATION_SCHEMA service database:

 SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = DATABASE() AND table_name LIKE ?;

It seems to me that the prepared statements in mysql basically can not process show tables requests.

PDO ATTR_EMULATE_PREPARES = false, SHOW TABLES LIKE and syntax error

1 answer 1

More articles: