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

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]    => abc@gmail.com 
        [:to]      => lala@me.com 
        [: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 ('abc@gmail.com', 'lala@me.com' at line 1

No idea how to fix this. any ideas?

I had the same problem I just added $stmt->bindParam(1,$var_name); and my problem solved — PHPFan, Oct 08 '22 at 19:37

Jan Krüger · Accepted Answer · 2016-12-03T16:39:18.360

137

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.

edited Dec 03 '16 at 16:39

answered Dec 28 '10 at 06:18

Jan Krüger

17,870
3
59
51

5

"order" is also reserved. Doh! Thanks for the post :-) – Mike Jul 24 '12 at 23:07
"UNLOCK" is also reserved (https://dev.mysql.com/doc/refman/5.5/en/keywords.html) :-) thanks, had run into this same problem. – Aditya M P Mar 07 '16 at 13:41
I found this Q&A through a search for another question similar to this one. They (the OP) did tick the `from`, it's the `to` that wasn't ticked afterwards. Look at the error here they posted after their edit: `right syntax to use near 'to`, edit being http://stackoverflow.com/posts/4544051/revisions. The answer should be edited in regards to this. The OP also edited their question with the ticked `from`, and I have rolled the question back to its original state. – Funk Forty Niner Dec 01 '16 at 19:00
the reserved word link has been changed https://dev.mysql.com/doc/refman/5.7/en/keywords.html – sansknwoledge Mar 06 '17 at 11:56

score 41 · Answer 2 · edited Jul 19 '18 at 09:53

41

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);

edited Jul 19 '18 at 09:53

Brane

3,257
2
42
53

answered Mar 20 '13 at 13:52

uKolka

36,422
4
33
44

10

turning off emulation [also helps](http://stackoverflow.com/tags/pdo/info) – Your Common Sense Mar 20 '13 at 14:01
1

@Your Common Sense: Why does it help? I had same limit values binding problem and turning off emulation did help :) But why? – CoR Nov 12 '13 at 13:46
2

@CoR It's explained pretty good in the wiki that he linked to. PDO treats placeholder parameters as strings by default (with emulation on). Limits become "... LIMIT '10' OFFSET '1';" and that's not what you want. If you turn emulation off MySQL handles the placeholder values according to their type. – uKolka Nov 12 '13 at 14:39

score 2 · Answer 3 · edited Oct 05 '16 at 11:05

2

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.

edited Oct 05 '16 at 11:05

Petter Friberg

21,252
9
60
109

answered Oct 05 '16 at 11:03

Kazim Noorani

263
3
6

thats very nice – adib16 Oct 18 '20 at 21:30

score 1 · Answer 4 · answered Jul 24 '16 at 11:25

Same pdo error in sql query while trying to insert into database value from multidimential array:

$sql = "UPDATE test SET field=arr[$s][a] WHERE id = $id";
$sth = $db->prepare($sql);    
$sth->execute();

Extracting array arr[$s][a] from sql query, using instead variable containing it fixes the problem.

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

4 Answers4

Linked

Related