What is wrong with my SQL/PHP syntax here?

Question

So I have a database of downloads on my site set up that enables me to track the number of downloads, and I'm trying to set up a front-end for me and my compatriots to insert new downloads into the database. I'm setting up the front-end with primarily PHP.

The way my paging is set up removes the possibility of my forms simply posting, so instead I have JS serializing the data and reloading the page, then I unserialize the data in PHP, stick the values into a mysql query, and try to run it.

Here's what my SQL code looks like inside of PHP:

$sql  = "INSERT INTO dl (id, file, desc) VALUES ('$idd', '$file', '$desc')";

Which turns into this string:

INSERT INTO dl (id, file, desc) VALUES ('a56', 'test.zip', 'cake')

But when the page tries to run it, I get this 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 'desc) VALUES ('a56', 'test.zip', 'cake')' at line 1

And the weirdness of that is compounded by the fact that the line of code running the query is not on line 1. It's on line 28.

Any help is appreciated :)

try changing the names of the columns, file and desc may be reserved — ılǝ, Jul 11 '14 at 05:39
Yes Desc is a mysql predefined keyword. We use as sorting parameter — Naveed Ramzan, Jul 11 '14 at 05:54

score 6 · Accepted Answer · edited May 23 '17 at 12:01

6

desc is a reserved keyword in MySQL.

The recommended workaround is to use backticks. From MySQL manual:

If an identifier contains special characters or is a reserved word, you must quote it whenever you refer to it.

If renaming the table or column isn't possible, wrap the offending identifier in backticks (`):

$sql  = "INSERT INTO dl (id, `file`, `desc`) VALUES ('$idd', '$file', '$desc')";

Take a look at this question too: When to use single quotes, double quotes, and backticks in MySQL

edited May 23 '17 at 12:01

Community

1
1

answered Jul 11 '14 at 05:43

Tushar Gupta

15,504
1
29
47

2

You could go the extra mile and backtick all the field and table names. – Fabrício Matté Jul 11 '14 at 05:43
Would that benefit me at all, or just provide protection against this in the future? – zbee Jul 11 '14 at 05:46
It is mandatory that you should not use reserved keywords but still if you insist use it with backticks simple – Tushar Gupta Jul 11 '14 at 05:48
What do you mean by 'mandatory,' other than it makes my code break? – zbee Jul 11 '14 at 05:53

score 1 · Answer 2 · edited Jul 11 '14 at 05:45

1

desc is a reserved word in SQL.

It is (together with ASC) used to determine the sorting order of the results.

edited Jul 11 '14 at 05:45

padarom

3,529
5
33
57

answered Jul 11 '14 at 05:40

Baalthasarr

377
1
13

Is there a work-around I can utilize in this case? EDIT: Nevermind, Tushar's answer worked :) – zbee Jul 11 '14 at 05:42
1

@Zbee you have to use backticks arround the column names. – Jens Jul 11 '14 at 05:43

score 1 · Answer 3 · answered Jul 11 '14 at 05:44

1

Please change the column name of desc, its reserved by MYSQL. for more details please see list of reserved words on MYSQL Reserved Words

http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

answered Jul 11 '14 at 05:44

sameerali

102
3
12

score 1 · Answer 4 · answered Jul 11 '14 at 05:50

1

Please try this.

$sql  = "INSERT INTO dl (`id`, `file`, `desc`) VALUES ('".$idd."', '".$file."', '".$desc."')";

hope this is your useful.

answered Jul 11 '14 at 05:50

The K

59
7

What is wrong with my SQL/PHP syntax here?

4 Answers4