CODEWITHSUNDEEP
mysqlperldbi
Kamrul Khan
Kamrul Khan

Reputation: 3350

Perl dbi prepare is putting wrong quote

My code is similar to the below:

$sth = $dbh->prepare("CREATE TABLE IF NOT EXISTS ? (`id` bigint(100) unsigned NOT NULL AUTO_INCREMENT");
$sth->execute('test');

I end up with the error:

ERROR 1064 (42000): 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 ''test'

From the trace I found perl DBI put single quote around the table name, which mysql doesnt like. How to tell DBI not to put any quote or to put (`) instead of single quote?

Upvotes: 1

Views: 226

Answers (2)

Jens
Jens

Reputation: 69440

You can not use a placeholder for a table or column name. PreparedStatement will interpret this value as a string and make sinlge quotes around it.

Upvotes: 1

ikegami
ikegami

Reputation: 385546

It's just doing what you asked. When given a string, ? is equivalent to a string literal. So

SELECT * FROM Table WHERE field = ?

means

SELECT * FROM Table WHERE field = 'test'

and

SELECT * FROM ?

means

SELECT * FROM 'test'

You need to use

$dbh->prepare("
   CREATE TABLE IF NOT EXISTS ".( $dbh->quote_identifier('test') )." (
             `id` bigint(100) unsigned NOT NULL AUTO_INCREMENT
          )
");

Upvotes: 5

Related Questions