PHP PDO::prepare() - SQL Error with placeholders

Question

My problem is that I can't get placeholders to work in my SQL statements. Specifically, in the below code, when I replace the placeholder ':tripsid' with a value like 'abcdefg' the TABLE isn't being created as intended.

SQL Error is:

PDO::errorInfo(): Array ( [0] => 42000 [1] => 1064 [2] => 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 *''sdfghjk' ( id INT NOT NULL, stop_start SMALLINT NOT NULL, stop_end SMA'* at line 1 )

And the code:

// My method to create a table with PDO and placeholders
public function routes_table()  {

    $this->connect();

    $STH = $this->DBH->prepare('CREATE TABLE IF NOT EXISTS :tripsid (
        id INT NOT NULL,
        stop_start SMALLINT NOT NULL,
        stop_end SMALLINT NOT NULL,
        distance SMALLINT NOT NULL,
        duration TINYINT NOT NULL,
        medium TINYTEXT NOT NULL,
        CONSTRAINT pk_routes PRIMARY KEY ( id )
        )');


    $tripsid = "sdfghjk";
    $STH->bindParam(':tripsid', $tripsid, PDO::PARAM_STR, 7);

    $STH->execute(); 

    // SQL Errors
    if (!$STH->execute($input)) {
        echo "\nPDO::errorInfo():\n";
        print_r($STH->errorInfo());
    }

    $this->disconnect();

}

I have tried everything I could think of. Does anyone see the mistake?

score 1 · Answer 1 · edited May 23 '17 at 11:43

I see this question is over a year old, but I've just stumbled across it and would like to highlight something: this has nothing to do with the quotation marks.

When an error occurs and you are given the snippet of SQL relating to the syntax in question, it is automatically enclosed around a pair of single quotation marks. This can often make it look as though there's a different issue, namely that of a mixture of double and single quotes.

In this example -

PDO::errorInfo(): Array ( [0] => 42000 1 => 1064 [2] => 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 ''sdfghjk' ( id INT NOT NULL, stop_start SMALLINT NOT NULL, stop_end SMA' at line 1 )

It's referring to:

'sdfghjk' ( id INT NOT NULL, stop_start SMALLINT NOT NULL, stop_end SMA

The outermost quotation marks are just specific to the error output; like me - I fear you noticed this and thought your error was about incorrect quotation marks. This is a red herring! Remove the outermost single quotes to actually see the SQL which was incorrect.

As for your real problem: You CANNOT specify the table name (or a column name) as a parameter; this is common to prepared statements on most platforms and APIs. This goes for any query - select/insert/update etc etc - none of them can accept the tablename as a parameter. For more information - see this question here.

So with this in mind, I would use sprintf() to achieve something like this...

$STH = $this->DBH->prepare(
    sprintf('CREATE TABLE IF NOT EXISTS %s (
        id INT NOT NULL,
        stop_start SMALLINT NOT NULL,
        stop_end SMALLINT NOT NULL,
        distance SMALLINT NOT NULL,
        duration TINYINT NOT NULL,
        medium TINYTEXT NOT NULL,
        CONSTRAINT pk_routes PRIMARY KEY ( id )
        )', $tripsid) 
);

$STH->execute();

Yes, it rather takes the beauty out of PDO when you still have to manipulate strings - unfortunately, this is the case for using table names as parameters! Also, remember this means 'old stylee' SQL Injection risks could still apply as you are manipulating the query directly.

I don't expect this will help the OP a year later, but I hope it'll help someone else now! :)

score 0 · Answer 2 · edited Dec 26 '11 at 18:06

ok now I know that the quotation marks bound to the parameter automatically by PDO::bindParam() is causing the problem. Does anybody have an idea how to remove the quotation marks from the table name so my code will be...

$STH = $this->DBH->prepare('CREATE TABLE IF NOT EXISTS tablename (
            id INT NOT NULL,...

instead of

$STH = $this->DBH->prepare('CREATE TABLE IF NOT EXISTS 'tablename' (
            id INT NOT NULL,

PHP PDO::prepare() - SQL Error with placeholders

2 Answers2