imagina imagina - 1 year ago 61
SQL Question

PDO prepared statement isn't working

I'm finding that my PDO MySQL insertion isn't working

The basic format of it is:

INSERT INTO `my_table` (id, email_hash, dob, 1, 6, 10) VALUES (?, ?, ?, ?, ?, ?)

It actually comes like this:

$InsertQuery = $db->prepare("INSERT INTO `my_table` (id, email_hash, dob, $NumbersString) VALUES (?, ?, ?, $QuestionMarkString)");
$InsertQuery->execute(array("$ID, $hashed_email, $dob, $YesNoString"));

The variable
fills in (correctly) the number of question mark placeholders.

The variable
is a string of "1"s of appropriate length to act as markers in the database.

So even when I can see that the first part of the query successfully becomes formed as:

INSERT INTO `my_table` (id, email_hash, dob, 1, 6, 10) VALUES (?, ?, ?, ?, ?, ?)

... and the content of the execute array successfully becomes:

52, $2y$10$h9yXWUd8edQVMTSwZrX7T.pJ/C1pLDE9b081OtGmG6nbAtXr7lASK, 29062016, 1, 1, 1

.. the insert still doesn't happen. I get a PHP error saying:

PHP Warning: PDO::prepare(): 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 '1, 6, 10) VALUES (?, ?, ?, ?, ?, ?)' at line [etc]

Answer Source

It should be:

$InsertQuery->execute(array($ID, $hashed_email, $dob, $YesNoString));

Also, you cannot have columns that only contain digits:

Identifiers may begin with a digit but unless quoted may not consist solely of digits.

Therefore you need to wrap your columns in backticks `