SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax — PHP — PDO [duplicate]

Question

I've looked through all the other StackOverflow (and google) posts with the same problem, but none seemed to address my problem.

I am using PDO and PHP.

My code:

$vals = array(
   ':from'    => $email,
   ':to'      => $recipient,
   ':name'    => $name,
   ':subject' => $subject,
   ':message' = >$message
);
print_r($vals);
try {
   $pdo = new PDOConfig();
   $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
   $sql = "SELECT * FROM messages WHERE `message` LIKE :message";
   $q = $pdo->prepare($sql);
   $q->execute(array(':message' => $vals[':message']));
   $resp = $q->fetchAll();

   foreach ($resp as $row) {
      throw new Exception('Please do not post the same message twice!');
   }

   $sql = "INSERT INTO messages (from, to, name, subject, message) VALUES (:from, :to, :name, :subject, :message)";
   $q = $pdo->prepare($sql);
   $q->execute($vals);
} 
catch(PDOException $e) {
   echo $e->getMessage();
}

and the first print_r gives

Array ( [:from]    => [email protected] 
        [:to]      => [email protected] 
        [:name]    => abc 
        [:subject] => abc 
        [:message] => abc )

which is expected (none are null)

but it outputs the 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 'from, to, name, subject, message) VALUES ('[email protected]', '[email protected]' at line 1

No idea how to fix this. any ideas?

Answer 1

from is a keyword in SQL. You may not used it as a column name without quoting it. In MySQL, things like column names are quoted using backticks, i.e. `from`.

Personally, I wouldn't bother; I'd just rename the column.

PS. as pointed out in the comments, to is another SQL keyword so it needs to be quoted, too. Conveniently, the folks at drupal.org maintain a list of reserved words in SQL.

Answer 2

I've got this exact error, but in my case I was binding values for the LIMIT clause without specifying the type. I'm just dropping this here in case somebody gets this error for the same reason. Without specifying the type LIMIT :limit OFFSET :offset; resulted in LIMIT '10' OFFSET '1'; instead of LIMIT 10 OFFSET 1;. What helps to correct that is the following:

$stmt->bindParam(':limit', intval($limit, 10), \PDO::PARAM_INT);
$stmt->bindParam(':offset', intval($offset, 10), \PDO::PARAM_INT);

Answer 3

ALTER TABLE `{$installer->getTable('sales/quote_payment')}`
ADD `custom_field_one` VARCHAR( 255 ) NOT NULL,
    ADD `custom_field_two` VARCHAR( 255 ) NOT NULL;

Add backtick i.e. " ` " properly. Write your getTable name and column name between backtick.