What's wrong with this? [MYSQL]
Community Forums/General Help/What's wrong with this? [MYSQL]
| ||
$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. |
| ||
One posibility could be that it could be the datetime field in the skin table isnt compatbile becourse datetime is also a value type. |
| ||
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 |
| ||
I use HeidiSQL to test my SQL agains a MySQL database. Its very helpfull. |
| ||
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 |
| ||
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! |
| ||
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 |
| ||
Did it really let you create a table with a column called 'desc' ? |
| ||
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! |
| ||
yep, desc is also a command (for sorting in descending order), never spotted that one. |
| ||
You can use desc (or anything you like) if you wrap it in []s (at least you can in MSSql) Last edited 2011 |
| ||
MySQL <> MSSql ... thankfully :-) |
| ||
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 |
| ||
@brucey, yeah i know ;P but can you not box your fields/records to stop them being confused with keywords as well? |
| ||
In MySQL you can also do `desc` if you have a column named the same as a command. |
| ||
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 |
| ||
Common sense dictates you don't name anything after keywords :-) Unless laziness trumps common sense? |
| ||
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. |