Syntax error in simple Insert Query

Question

I'm trying to create some basic insert query in PHP and it keeps trowing me an error but i really don't know why , can any you guys please see if there is anything wrong ?

    mysql_query("
INSERT INTO itens (nome, data, cliente, link, desc, img) VALUES ($nome,$data,$cliente,$link,$desc,$img)
") or die(mysql_error());

Update

Pulled from deleted answer of the OP, the code is now:

mysql_query("INSERT INTO itens (nome, data, cliente, link, `desc`, img) 
VALUES ($nome,$data,$cliente,$link,$desc,$img)") or die(mysql_error());

And the error is:

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 'kl,j)' at line 2

The kl and j are the last two things i insert in the form.

Never say "It gives an error". Always say "It gives *this* error" and then paste the actual error. — Andy Lester, Dec 18 '12 at 15:59
Sorry the error is : 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, img) VALUES (s,2012-12-18,(nome, data, cliente, link, desc, img),http://ww' at line 1 — SaraVieira, Dec 18 '12 at 16:00
I know , after i put this to work i will secure it more, thanks — SaraVieira, Dec 18 '12 at 16:07
@user1555009 - Have you tried `("$nome","$data","$cliente","$link","$desc","$img")` instead of `($nome,$data,$cliente,$link,$desc,$img)`?? — Pankit Kapadia, Dec 18 '12 at 16:17
Your variables are strings, but you're not putting quotes around each value; see my answer as well. — Ja͢ck, Dec 18 '12 at 16:34

score 7 · Answer 1 · edited May 23 '17 at 12:09

7

DESC is a MySQL Reserved Keyword, You should escaped it with backtick, ex

INSERT INTO itens (nome, data, cliente, link, `desc`, img) 
VALUES ($nome,$data,$cliente,$link,$desc,$img)

MySQL Reserved Keyword

you're query is vulnerable with SQL Injection, please take time to read the article below on how to prevent from it,

How can I prevent SQL injection in PHP?

edited May 23 '17 at 12:09

Community

1
1

answered Dec 18 '12 at 15:59

John Woo

258,903
69
498
492

Is it common practice to escape all column names? – Sharlike Dec 18 '12 at 16:01
@Sharlike nope, only those who are Reserved keywords must be escaped. – John Woo Dec 18 '12 at 16:02
I changed the name of the column and now it shows me 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 'name,2012-12-25,Client,link,desc,link)' at line 1 – SaraVieira Dec 18 '12 at 16:07
is `name` present in your table. can you please post the result of the statement in your question `DESC itens`? – John Woo Dec 18 '12 at 16:09
name is just what i wrote in the form – SaraVieira Dec 18 '12 at 16:33

score 2 · Answer 2 · answered Dec 18 '12 at 16:05

2

First and foremost - Escape, Escape, Escape OR Learn PDO / mysqli and prepared statements.

Second - know what the reserved keywords are that can't be used for column names; those have to be escaped using backticks.

$sql = sprintf("INSERT INTO itens (nome, data, cliente, link, `desc`, img) VALUES ('%s', '%s', '%s', '%s', '%s', '%s');", 
    mysql_real_escape_string($nome),
    mysql_real_escape_string($data),
    mysql_real_escape_string($cliente),
    mysql_real_escape_string($link),
    mysql_real_escape_string($desc),
    mysql_real_escape_string($img)
);

mysql_query($sql);

Third - I think you made a typo in the table name (itens vs items).

answered Dec 18 '12 at 16:05

Ja͢ck

170,779
38
263
309

1

hey jack, `mysql_real_escape_string` doesn't fully protect from `SQL Injection`. [SQL injection that gets around mysql_real_escape_string()](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string/5741264#5741264) – John Woo Dec 18 '12 at 16:12
@JW. Hence the quotes around each escaped string. Please read properly. – Ja͢ck Dec 18 '12 at 16:17
how about number? will it protect from injection. I read it properly. – John Woo Dec 18 '12 at 16:19
@Jw I don't see a number anywhere in the query. – Ja͢ck Dec 18 '12 at 16:20
1

yes, but that's not the point. The point is `mysql_real_escape_string` has a limitation on protecting you from the injection like numbers (*which is on the other case*). – John Woo Dec 18 '12 at 16:26
@JW. Yes, *if* you don't put quotes around the argument after escaping it. Basically, even if the column is an `INT` I will still write `'123'` (if I were to use `mysql_query()` of course). Maybe I would use `%d` format specifier in `sprintf()` as an alternative. – Ja͢ck Dec 18 '12 at 16:32
Using `mysql_real_escape_string()` safely **requires** that one first correctly set the connection character set with `mysql_set_charset()`. Furthermore, one must be careful to ensure either that the SQL mode does not include `NO_BACKSLASH_ESCAPES` or that literals are not quoted using in double-quote `"` characters. See http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string for more info. – eggyal Apr 25 '14 at 14:58

Syntax error in simple Insert Query

2 Answers2