PDO Exception when DESCRIBE table

Question

I am getting the error:

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 "mytable" at line 1

When I call the below function using

$fields = Table::getFieldsForTable('mytable');

If I hard-code :t to my table name, then the code executes fine.

 public static function getFieldsForTable ($table ) {
            $sql = 'DESCRIBE :t';

            try {
                /**
                 * @var $db \PDO
                 */
                $db = static::getDB();
                $stmt = $db->prepare($sql);

                $stmt->bindValue(':t', $table, PDO::PARAM_STR);

                $stmt->execute();

                return $stmt->fetchAll(PDO::FETCH_ASSOC);
            } catch (\PDOException $e){
                echo "PDO ERROR" . $e->getMessage();
            }
        }

I have used the same code snippet over and over in other parts of the project, but I am failing to see what I have done wrong here.

Any help?

fooquency · Accepted Answer · 2018-09-19T18:15:20.870

1

Simply because table or column names cannot be replaced by parameters in PDO - it's just a fundamental restriction in the way it works.

See answers to duplicate question: Can PHP PDO Statements accept the table or column name as parameter?

https://stackoverflow.com/a/15990488/180733 is an excellent explanation.

If you are concerned about the security of accepting an arbitrary table name, consider an up-front fetch of all table names using SHOW TABLES, and then validate the proposed table name against that list, using in_array ($table, $tables).

edited Sep 19 '18 at 18:15

answered Sep 19 '18 at 18:09

fooquency

1,575
3
16
29

Thanks. I didn't know that. I'll just add just my $sql line to compensate. – user-44651 Sep 19 '18 at 18:14

score 0 · Answer 2 · answered Sep 19 '18 at 18:11

bindValue with PDO::FETCH_ASSOC quotes the string as if it's a value you'd use for insert or select etc. Just concat the string

$sql="DESCRIBE ".$table;.

For security develop a regex that detects only valid table names, e.g. something like this

preg_match('/^[a-zA-Z]{1}[a-zA-Z_]{1,18}$/',$table);

Or match against a whitelist, e.g. array of accepted tables

PDO Exception when DESCRIBE table

2 Answers2