与预准备语句和LIKE一起使用时，SHOW COLUMNS返回错误

Question

I’m having troubles with the MySQL/MariaDB query below and I can’t figure out why.

$field = 'some_db_field';
$field = '%' . $field . '%';

$query = $db->prepare(
    " SHOW COLUMNS "
  . "         FROM table"
  . "         LIKE :fieldName"
);

$query->bindParam(':fieldName', $field, PDO::PARAM_STR);
$query->execute();

I get the following error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?' at line 1' in script.php:

What am I doing wrong?

Answer 1

I have experienced this issue as well.

You simply can't use PDO bindParam function on a SHOW COLUMN query.

I don't know why. I have searched the doc and the net for hours and I can't find any explanation.

An alternative is to use PDO::getColumnMeta

Or to query the information_schema

$field = 'some_db_field';
$field = '%' . $field . '%';

$query = $db->prepare( 
    " SELECT * "
  . " FROM INFORMATION_SCHEMA.COLUMNS "
  . " WHERE table_name = 'your_table' AND column_name LIKE :fieldName "
);

$query->bindParam(':fieldName', $field, PDO::PARAM_STR);
$query->execute();
    return $output; 
}

There's an advantage with this one over SHOW COLUMN: you have much more column information returned. Example :

| TABLE_CATALOG | TABLE_SCHEMA |  TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME |  COLLATION_NAME | COLUMN_TYPE | COLUMN_KEY | EXTRA |                      PRIVILEGES | COLUMN_COMMENT |
|---------------|--------------|-------------|-------------|------------------|----------------|-------------|-----------|--------------------------|------------------------|-------------------|---------------|--------------------|--------------------|-----------------|-------------|------------|-------|---------------------------------|----------------|
|           def |  db_9_1e4841 | input_table | Agency_CODE |                1 |         (null) |         YES |   varchar |                        3 |                      9 |            (null) |        (null) |             (null) |               utf8 | utf8_general_ci |  varchar(3) |            |       | select,insert,update,references |                |