Why can't I insert this into MySQL?

Question

I probably made a stupid error on my syntax for the query, but I can't seam to fix it. Here is the query my program tries to execute:

INSERT INTO filez (
  filename,
  uploadedby,
  dateuploaded,
  public,
  FileSize,
  FileTransferSize,
  key,
  bytes
)
VALUES(
  'tacct/tesABCscdsdasdasdD.testtest',
  'tacct',
  '%27 %December %2012, %7:%32:%15%AM',
  1,
  7,
  7,
  '`',
  'TestDoc'
)

And here's the mysql_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 'key, bytes) VALUES('tacct/tesABCscdsdasdasdD.testtest', 'tacct', '%27 %D' at line 1

I did mysql_real_escape_string() on EVERYTHING except the FileSize and FileTransferSize in the query. Could you tell me what I am doing wrong? Thanks!

I wonder if the keyword `KEY` is confusing it. Try wrapping `key` in backquotes? — halfer, Dec 27 '12 at 13:44
@Tommy3244: Should you also tag the question `keyboard`, as you used a keyboard to type the query? And `cheese-sandwich` to ensure we know where you got the energy to move your fingers? — Lightness Races in Orbit, Dec 27 '12 at 13:47
(Aside: a good way to check for this problem is to modify your query to insert the smallest number of columns possible, then add new columns until the error re-appears). — halfer, Dec 27 '12 at 13:48
@LightnessRacesinOrbit - the PHP tag may be justifiable in this case, since `mysql_real_escape_string` is explicitly mentioned. Please consider being kinder to new members - this is a perfectly acceptable question. — halfer, Dec 27 '12 at 13:49
@halfer: But it didn't need to be - we are given a MySQL query, not a PHP statement. This is a perfect _MySQL-only_ question with no need to cloud it with other, irrelevant technologies. And please do not mistake my attempts to teach SO newcomers how to write better questions for "unkindness" simply because I did not use some childish emoticon: :) — Lightness Races in Orbit, Dec 27 '12 at 13:50
@halfer: (And, strictly speaking, this question is far _too localised_ for SO, but I answered it because I'm feeling _kind_.) — Lightness Races in Orbit, Dec 27 '12 at 15:23

score 13 · Accepted Answer · answered Dec 27 '12 at 13:43

13

The error tells you that the error happens at the text key, and it's quite correct: key is a reserved word in MySQL.

Where you use it as a field name, you must enclose it in backticks (`); it's a good general practice anyway.

So:

INSERT INTO `filez` (
  `filename`,
  `uploadedby`,
  `dateuploaded`,
  `public`,
  `FileSize`,
  `FileTransferSize`,
  `key`,
  `bytes`
)
VALUES(
  'tommy3244/tesABCscdsdasdasdD.testtest',
  'tommy3244',
  '%27 %December %2012, %7:%32:%15%AM',
  1,
  7,
  7,
  '`',
  'TestDoc'
)

answered Dec 27 '12 at 13:43

Lightness Races in Orbit

378,754
76
643
1,055

@Tommy3244: Preferably, enclose _all_ field names in backticks (_not_ "quotations"). That gives you consistency and prevents you from running into the problem again. It also aids syntax highlighting and probably makes parsing marginally faster. – Lightness Races in Orbit Dec 27 '12 at 13:47

score 4 · Answer 2 · edited May 23 '17 at 10:24

4

try using following query

INSERT INTO filez (filename, uploadedby, dateuploaded, public, FileSize, FileTransferSize, `key`, bytes) VALUES
('tommy3244/tesABCscdsdasdasdD.testtest', 'tommy3244', '%27 %December %2012, %7:%32:%15%AM', 
1, 7, 7, '`', 'TestDoc')

key is keyword so you have to use backticks as i used above. For more info check following question too Select a column with a keyword name

edited May 23 '17 at 10:24

Community

1
1

answered Dec 27 '12 at 13:44

Fathah Rehman P

8,401
4
40
42

1

A good answer _explains_ the problem. Also your suggested query is highly inconsistent. – Lightness Races in Orbit Dec 27 '12 at 13:46

score 4 · Answer 3 · answered Dec 27 '12 at 13:44

key is a reserved word and you can not use it in column name without using backtics. try this

INSERT INTO filez (`filename`, `uploadedby`, `dateuploaded`, `public`, `FileSize`, `FileTransferSize`, `key`, `bytes`) VALUES('tommy3244/tesABCscdsdasdasdD.testtest', 'tommy3244', '%27 %December %2012, %7:%32:%15%AM', 1, 7, 7, '`', 'TestDoc')

score 3 · Answer 4 · answered Dec 27 '12 at 13:45

 INSERT INTO `filez` (`filename`, `uploadedby`, `dateuploaded`, 
                    `public`, `FileSize`, `FileTransferSize`, 
                     `key`, `bytes`) 
        VALUES('tommy3244/tesABCscdsdasdasdD.testtest',   'tommy3244', 
                '%27 %December %2012, %7:%32:%15%AM', 1, 7, 7, '`', 'TestDoc')

ok,

syntax to use near 'key, bytes) both are reserve words, Good practice is always use ` sign for user defined identifiers.

score 0 · Answer 5 · answered Dec 27 '12 at 13:53

0

KEY is reserved key word for mysql

try other column name or make backticks like that ``

answered Dec 27 '12 at 13:53

echo_Me

37,078
5
58
78

score 0 · Answer 6 · edited Dec 28 '12 at 12:55

0

The issue is key is the reserved keyword of mysql , you can't use it as column name, if you really want then enclose it inside backtick character

key

edited Dec 28 '12 at 12:55

Lightness Races in Orbit

378,754
76
643
1,055

answered Dec 27 '12 at 13:55

Manish Goyal

700
1
7
17

Why can't I insert this into MySQL?

6 Answers6