0

I'm using CakePHP 2.9.9.

I want to use prepared statement in query method, but got syntax error.

code is below

$query = 'CREATE TABLE IF NOT EXISTS ? (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY)';
$this->User->query($query, array('dynamic_table_name'));

error message is this.

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 ''dynamic_table_name' (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY)' at line 1

Why escape by single quote like ''dynamic_table_name' ?

How to fix it?

chris85
  • 23,846
  • 7
  • 34
  • 51
dmnlk
  • 2,995
  • 2
  • 25
  • 30
  • 1
    Possible duplicate of [Can PHP PDO Statements accept the table or column name as parameter?](https://stackoverflow.com/questions/182287/can-php-pdo-statements-accept-the-table-or-column-name-as-parameter) – Mike Jul 03 '17 at 16:31
  • I'd guess you cant bind a table with cakephp, I'm not familiar with cakephp though. Reading more `Underneath the covers, the query builder uses PDO prepared statements` – chris85 Jul 03 '17 at 16:31
  • @chris85 It has nothing to do with cakephp. It's a limitation on probably all RDBMS that support prepared statements. – Mike Jul 03 '17 at 16:34
  • @Mike The framework could have (it doesn't per the docs) had an intermediary step. – chris85 Jul 03 '17 at 17:11
  • @chris85 True, it could. However to quote [here](https://stackoverflow.com/a/15990488/811240) "This is not something an abstraction library like PDO can or should work around, either, since it would defeat the 2 key purposes of prepared statements: 1) to allow the database to decide in advance how a query will be run, and use the same plan multiple times; and 2) to prevent security issues by separating the logic of the query from the variable input." – Mike Jul 03 '17 at 17:13

0 Answers0