PDO + comment + ? = Error‼

Question

^{(Side-note: Blocking ! in question titles doesn't stop smart-arses like me putting U+203C Double Exclamation Mark instead :p)}

After a quick round of debugging, I found this:

$query = <<<END
    SELECT
        `column1`, `column2`,
        SOME_FUNCTION(`column3`) -- process in PHP instead?
    FROM `tablename`
    WHERE `condition` BETWEEN ? AND ?
END;
$stmt = $pdo->prepare($query);
$stmt->execute(array(1,10));

And got this:

Uncaught Exception » RuntimeException » PDOException:
SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

Do you see the problem?

The question mark in the comment -- process in PHP instead? is interpreted as a token to bind a parameter to! PDO then expects three parameters instead of the two that were passed.

Now, obviously the simple solution was to just rewrite the comment, but this feels like I'm avoiding what may be a bigger issue.

Is there perhaps something wrong with PDO, or is there an option I can set to have it understand MySQL comments?

score 2 · Accepted Answer · answered Oct 29 '14 at 15:02

2

You can have comments, but not those with ? in them or other things like :x that would be interpreted as placeholders.

PDO does not understand SQL syntax. It's treating all that text as something that needs to be examined for placeholders. When emulating prepared statements this is what will happen.

answered Oct 29 '14 at 15:02

tadman

208,517
23
234
262

Ah, I see, this is a consequence of prepared statement emulation. Okay. Does the mysql driver support prepared statements without emulation? – Niet the Dark Absol Oct 29 '14 at 15:08
Looks like PDO [can do it, but not for MySQL](http://stackoverflow.com/questions/10617057/does-pdo-always-use-emulated-prepared-statements-by-default). – tadman Oct 29 '14 at 15:09
1

I just now tried disabling `ATTR_EMULATE_PREPARES` and it's executing my prepared statements perfectly - even better, it no longer has a problem with my question-comment. – Niet the Dark Absol Oct 29 '14 at 15:15

PDO + comment + ? = Error‼

1 Answers1