Why can't I bind a variable to a 'SHOW VARIABLE LIKE' query with PDO/MySQL?-CodePudding

I've been digging over the PHP PDO documentation and I can't figure out why this query is failing.

Here's the query:

$var = 'information_schema_stats_expiry';
$stmt = $pdo->prepare('SHOW VARIABLES LIKE :var');
$stmt->execute([':var' => $var]);

When executed I get this error:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1

I know I can work around it with slightly altered queries. These are a few I've tested that do work.

$var = 'information_schema_stats_expiry';
$stmt = $this->pdo->prepare('SHOW VARIABLES LIKE "' . $var . '"');
$stmt->execute();

Or,

$var = 'information_schema_stats_expiry';
$stmt = $this->pdo->prepare('SHOW VARIABLES WHERE variable_name=:var');
$stmt->execute([':var' => $var]);

Or, the same as above, but with

SHOW VARIABLES WHERE variable_name LIKE :var

However, I'm trying to understand why the first query doesn't work. It reminds me of the issues where people try to bind a variable for a LIMIT clause, but it fails because the number gets quoted; but, in this case, the variable name needs to be quoted, so I would think the query would work fine. Is this a bug, or is there a documented reason why this query would be failing?

To sum up, this query works fine when I run it directly through a MySQL client, or as a static query in PHP:

SHOW VARIABLES LIKE "information_schema_stats_expiry"

However, if I try to make a prepared statement with the exact same syntax, it it will fail:

SHOW VARIABLES LIKE :var

Is there any obvious reason this isn't working?

This is running on PHP 8 with MySQL 8.

CodePudding user response：

It's a MySQL limitation. Not all statements can be prepared. (There is a PREPARE syntax which sets up parameter binding, which we don't usually see in PHP/PDO. You ought to be getting a warning of sorts.)

SHOW VARIABLES is definitely exempt:
https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html#prepared-statements-permitted