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

Question

I have looked through all over the Internet 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]    => abc@gmail.com 
        [:to]      => lala@me.com 
        [:name]    => abc 
        [:subject] => abc 
        [:message] => abc )

Which is expected (none are null).

But it outputs the below 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

I had got this exact error, but in my case, I did bind the values for the LIMIT clause without specifying the type. Without defining the type LIMIT :limit OFFSET :offset; resulted in LIMIT '10' OFFSET '1'; instead of LIMIT 10 OFFSET 1;. What benefits to correct that is the following:

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

If you want to learn more about SQL, Check out this SQL Certification by Intellipaat.

For more information visit :