0

Recently I started to work with someone's library that makes use of some modern PHP5 features, and have been introduced to this strange new PDO language, where apparently you can't trouble-shoot the SQL in the usual way.

Here is a generated SQL statement which is being rejected:

INSERT INTO "eve"."utilRegisterKey" ("activeAPIMask","isActive","keyID","vCode") VALUES (?,?,?,?) ON DUPLICATE KEY UPDATE "activeAPIMask"=VALUES("activeAPIMask"),"isActive"=VALUES("isActive"),"keyID"=VALUES("keyID"),"vCode"=VALUES("vCode")

Normally I would assume that the quotation marks are what is killing this statement, but for all I know this PDO system is "cool" with that.

Here is the error:

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 '"eve"."utilRegisterKey" ("activeAPIMask","isActive","keyID","vCode") VALUES ('26' at line 1)

I have no reason to think the library is at fault, it appears to be written by an expert. So what could be going wrong?

1Up
  • 994
  • 2
  • 12
  • 24

1 Answers1

4

You cannot wrap your table name or column identifiers in quotes. Either use ticks or nothing at all.

INSERT INTO eve.utilRegisterKey (activeAPIMask,isActive,keyID,vCode)

or

INSERT INTO `eve`.`utilRegisterKey` (`activeAPIMask`,`isActive`,`keyID`,`vCode`)
John Conde
  • 217,595
  • 99
  • 455
  • 496
  • WOW so it IS the library! Thats just crazy. – 1Up Sep 10 '14 at 01:44
  • 2
    @1Up It's not the library, that is MySQL. Identifiers are quoted with backticks or unquoted. Never single or double quoted. – Michael Berkowski Sep 10 '14 at 01:46
  • What I mean (and I could be wrong) is that the library I'm using appears to wrap the tables in quotes like so: `'INSERT INTO "%1$s"."%2$s%3$s" ("%4$s") VALUES %5$s ON DUPLICATE KEY UPDATE %6$s',` – 1Up Sep 10 '14 at 01:54
  • Looks like it is either supposed to remove those quotes when generating the query or it is written incorrectly. I suspect the latter. – John Conde Sep 10 '14 at 01:55
  • Actually you are all incorrect in your answers. I'm the writer of the library he is using and I use ANSI mode in MySQL which is needed to have MySQL obey ANSI SQL rules for quoting or at least come close. The class he was using I missed setting the mode causing the issue. You can find more about MySQL modes at http://dev.mysql.com/doc/refman/5.5/en/sql-mode.html – Dragonaire Sep 10 '14 at 20:16