NewsForumsCodeLogsGallerySpecsSearch

What's wrong with this? [MYSQL]

Community Forums/General Help/What's wrong with this? [MYSQL]

Yahfree(Posted 2011) [#1]
$sql="INSERT INTO skin (ID, name, desc, authname, dl, datetime) VALUES (NULL, '$_POST[name]', '$_POST[desc]', '$_POST[authname]', '0', '$datetime');";
$result=mysql_query($sql);


I'm getting a nice fat:

"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, authname, dl, datetime) VALUES (NULL, 'sdfsdf', 'Enter your Description...' at line 1 "

What's going on? it must be something stupidly simple.


Space_guy(Posted 2011) [#2]
One posibility could be that it could be the datetime field in the skin table isnt compatbile becourse datetime is also a value type.


Perturbatio(Posted 2011) [#3]
yep, try quoting the datetime column.

*EDIT*
Better yet, rename the column to something descriptive. what does it represent (i.e. last_login, last_downloaded, last_modified, date_created)

Last edited 2011


Tri|Ga|De(Posted 2011) [#4]
I use HeidiSQL to test my SQL agains a MySQL database.
Its very helpfull.


ima747(Posted 2011) [#5]
If it's not the date time name check that you're sanitizing your input values. Looks like you're taking the raw post value, if it contains control or restricted characters (such as apostrophes, quotes, etc.) you will have big input handling problems as well as massive security holes (a properly formatted description field could for example wipe your whole database...)

SQL tends, by default, though it can be configured otherwise, to auto escape input (which has it's own problems) nothing else does. And it's auto escape isn't as good as a normal SQL escape call... One thing I've had to do is actually strip the escapes and then re escape them to get proper safety checks, and even that has a few holes (though generally they just cause errors with intentionally malformed content, not full security holes...)

Last edited 2011


Brucey(Posted 2011) [#6]
Always use prepared statements... then you don't need to worry about the content of the data you are inserting.

Things like this : '$_POST[name]'
can allow arbitrary access to all kinds of things you really don't want to know about!


Yahfree(Posted 2011) [#7]
That was left over from me trying to simplify things as much as possible to find the bug. Here's the updated version; I changed datetime to posttime in the database, but it's still spitting errors out:

$name = mysql_real_escape_string($_POST['name']);
$desc = mysql_real_escape_string($_POST['desc']);
$authname = mysql_real_escape_string($_POST['authname']);
$datetime=date("Y-m-d H:i:s"); //create date time
$sql="INSERT INTO skin (name, desc, authname, dl, posttime) VALUES ('$name', '$desc', '$authname', '0', '$datetime')";
$result=mysql_query($sql);


Here are the datatypes:
ID - PRIMARY, AUTO INC, int(11)
name - varchar(255)
desc - varchar(255)
authname - varchar(255)
dl - int(11)
posttime - datetime

Last edited 2011

Last edited 2011


Brucey(Posted 2011) [#8]
Did it really let you create a table with a column called 'desc' ?


Yahfree(Posted 2011) [#9]
Wow! That was the problem, can't believe i missed that. It was shorthand for "description" :o

Thanks Brucey, I knew it was something stupid!


Perturbatio(Posted 2011) [#10]
yep, desc is also a command (for sorting in descending order), never spotted that one.


D4NM4N(Posted 2011) [#11]
You can use desc (or anything you like) if you wrap it in []s (at least you can in MSSql)

Last edited 2011


Brucey(Posted 2011) [#12]
MySQL <> MSSql

... thankfully :-)


TartanTangerine (was Indiepath)(Posted 2011) [#13]
Grab a copy of SQLyog. Btw, try & catch are your friends.

Be really careful when using names that are reserved by MYSQL : http://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-reservedwords-5-5.html

Last edited 2011


D4NM4N(Posted 2011) [#14]
@brucey, yeah i know ;P but can you not box your fields/records to stop them being confused with keywords as well?


Htbaa(Posted 2011) [#15]
In MySQL you can also do `desc` if you have a column named the same as a command.


Perturbatio(Posted 2011) [#16]
You can quote them, but really, you shouldn't name your columns after keywords. For the same reason you shouldn't name variables or functions after existing keywords


Brucey(Posted 2011) [#17]
Common sense dictates you don't name anything after keywords :-)

Unless laziness trumps common sense?


Perturbatio(Posted 2011) [#18]
I have been guilty of it myself, but it's due to ignorance rather than laziness. And it got corrected as soon as I found out.