Error with MySql query

Question

I am having problems with this MySql query:

INSERT INTO groups (NAME, DESC, TIME, OWNER) VALUES ('$GNAME', '$DESC', '$TIME', '$UID')

Essentially, the script adds the Group Name, Decription, Time and the Username of the person who registered the Group, into the mysql database.

Full script:

<?php
include_once('include/session.php');
$GNAME = $_POST['groupname'];
$DESC = $_POST['desc'];
$SPAM = $_POST['spam'];
$UID = $_POST['UID'];
$TIME = date('Y-m-d H:i:s');
if($SPAM == "queuee"){


$query ="INSERT INTO groups (NAME, DESC, TIME, OWNER) VALUES ('$GNAME','$DESC','$TIME', '$UID')";

$result = mysql_query($query) or die("There as been an Error! <hr>Error:<hr>".mysql_error() ."<br><hr><a href=\"../index.php\">Go Back</a>");
header("Location: ../group.php?id=$GNAME");
}else{
?>
The Security Question was wrong. Try Again.

<?
}
?>

I ran the Query directly into MySql itself using Phpmyadmin. However it still threw an error. The error is:

#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 'DESC, TIME, OWNER) VALUES ('$GNAME', '$DESC', '$TIME', '$UID')' at line 1

Any help at all is much appreciated and perhaps I am being a complete fool and not noticing an obvious mistake. If you need any more information just ask!

Many Thanks.

STOP CODING RIGHT AWAY! Do some research on SQL injection and counter measures such as database wrapper APIs and prepared statements. — Salman A, Sep 09 '12 at 15:20

score 6 · Answer 1 · answered Sep 06 '12 at 19:55

6

"DESC" is a keyword. Put it in backquotes.

..., `DESC`, ...

answered Sep 06 '12 at 19:55

Ignacio Vazquez-Abrams

776,304
153
1,341
1,358

Thanks a lot! I knew it would be something simple! – Queuee Sep 06 '12 at 19:56

score 3 · Answer 2 · answered Sep 06 '12 at 19:55

3

DESC is a reserved keyword. If you must use it for a column name, wrap it in backticks:

`DESC`

answered Sep 06 '12 at 19:55

Josh

12,448
10
74
118

andrewsi · Accepted Answer · 2012-09-06T20:02:27.763

2

Try:

INSERT INTO groups (`NAME`, `DESC`, `TIME`, `OWNER`) VALUES ('$GNAME','$DESC','$TIME', '$UID')";

You're using reserved words as column names - PHP gets terribly confused in that case.

I should also add that your code is open to SQL injection, and you should look at moving away from using the mysql_* functions.

The quickstart guide for mysqli is at http://www.php.net/manual/en/mysqli.quickstart.php

PDO is another option; the information on prepared statements is at http://www.php.net/manual/en/pdo.prepared-statements.php

edited Sep 06 '12 at 20:02

answered Sep 06 '12 at 19:55

andrewsi

10,807
132
35
51

Could you possibly modify the code to prevent this as one has very little experience in dealing with SQL injection. I have heard about mysqli (Mysql improved) but have no idea whether or not it works in the same way as the 'old' version, if you like. Thanks :) – Queuee Sep 06 '12 at 19:59
@Queuee - mysqli works in pretty much the same way as mysql_* - I've added a link to the quickstart guide, which should help you figure out what needs to change. – andrewsi Sep 06 '12 at 20:03
Thanks a lot! You have helped with two problems, one with the DESC and the other with SQL Injections! I assume that mysqli protects against SQL Injections? – Queuee Sep 06 '12 at 20:05
@Queuee - it will go a long way to protecting you from SQL injections, but it's still possible to be affected by them (though to be fair, you really do have to work at making your code vulnerable - in normal use you'll be protected fully) – andrewsi Sep 06 '12 at 20:07
Thanks again! You have drastically improved my knowledge on this matter! I will change all the mysql_ 'commands' - if you like - to the mysqli_ 'commands'. I shall also Use ` whenever inserting into a database (or merely selecting the table column to be inserted into) to be on the safe side! Thanks again - twice! – Queuee Sep 06 '12 at 20:10

score 1 · Answer 4 · answered Sep 06 '12 at 19:56

1

DESC is SQL keyword, use `` to escape column names:

$query ="INSERT INTO `groups` (`NAME`, `DESC`, `TIME`, `OWNER`) VALUES ('$GNAME','$DESC','$TIME', '$UID')";

answered Sep 06 '12 at 19:56

Ivan Hušnjak

3,493
3
20
30

score 1 · Answer 5 · answered Sep 06 '12 at 19:56

1

According to MySQL Reserved Words, the word `DESC can't be used as a field name, unless you enclose it with backticks.

answered Sep 06 '12 at 19:56

Jocelyn

11,209
10
43
60

score 0 · Answer 6 · answered Sep 06 '12 at 19:56

0

Put single (or double maybe) quotes around DESC in the "INSERT INTO groups" part. DESC is a reserved word and must be quoted.

answered Sep 06 '12 at 19:56

virtuexru

858
1
6
16

1

Identifiers used in metadata must be backquoted. – Ignacio Vazquez-Abrams Sep 06 '12 at 19:57

score 0 · Answer 7 · answered Sep 06 '12 at 19:58

0

use

$DESCRIPTION = $_POST['desc'];

inseted of

$DESC = $_POST['desc'];

$DESCRIPTION variable use in you query

$query ="INSERT INTO groups (NAME, DESC, TIME, OWNER) VALUES ('$GNAME','$DESCRIPTION','$TIME', '$UID')";

answered Sep 06 '12 at 19:58

Abid Hussain

7,724
3
35
53

Error with MySql query

7 Answers7