1

I have an error when inserting into the database.

Code:

dbquery("INSERT INTO site_news_comments (articleid,title,short,comment,timestamp,userid,main,type,topstory) VALUES ($article,'".clean($commentss['title'])."','','".mysql_real_escape_string($_POST['comment'])."',current_timestamp,'".USER_ID."','0','".$commentss['type']."','')");

Ignore the dbquery, works exactly as mysql_query.

The error I am receiving 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 ''title','short','

No idea why this error is being thrown!

Improve this question
2
  • why dont u put all the data into variable first. much more easier to INSERT later. e.g: $title = clean($commentss['title']) Commented Oct 4, 2012 at 3:21
  • can you print just the query and see how it is built before executing Commented Oct 4, 2012 at 3:23

4 Answers 4

Reset to default
2

Teaching a man how to fish.

If a query fails, the first thing you should do is to echo the query you're about to send:

$sql = "INSERT INTO site_news_comments (articleid,title,short,comment,timestamp,userid,main,type,topstory) VALUES ($article,'".clean($commentss['title'])."','','".mysql_real_escape_string($_POST['comment'])."',current_timestamp,'".USER_ID."','0','".$commentss['type']."','')";

echo $sql;

It's usually pretty obvious what's wrong with the final query; pay particular attention to the dynamic stuff in your query and generally around the area where MySQL complains about.

If that still looks okay, then you look for words that might need escaping, such as the reserved words.

Conclusion

Having looked at the code mysql, I would have to conclude that the problem lies with $article and it causes problems in your query. You should probably escape it as well, just in case :)

Recommendation

You should learn about PDO / mysqli and using prepared statements:

// PDO example
$stmt = $db->prepare('INSERT INTO site_news_comments (articleid, title, short, comment, timestamp, userid, main, type, topstory) VALUES (:article, :title, :short, :comment, CURRENT_TIMESTAMP, :user, :main, :type, :topstory)');
$stmt->execute(array(
    ':article' => $article,
    ':title' => $commentss['title'],
    ':short' => '',
    ':comment' => $_POST['comment'],
    ':user' => USER_ID,
    ':main' => 0,
    ':type' => $commentss['type'],
    ':topstory' => '',
));
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Article is not empty, as it is defined in the URL, and the other stuff to do with the article show.
@zuc0001 read the rest of the answer too, echo the query and look at it
Ok, I did a query. It came out ok, except for one error for $_POST['comment'] (but thats because I didn't submit the form), but there was an error at the $article, instead of the articleid being '1', it had '1/'. Any ideas?
@zuc0001 add the echoed query to your question so that we all can take a look ... but obviously, 1/ would cause an issue :)
Thankyou though, without echoing the SQL query, I would not have been able to find the problem! Thankyou so much!
|
2

EDIT
I read too quickly the first time around; The error does not appear to be in the column list, it looks like it's in the value list. The only place the query can have a syntax error is if $article is empty (or un-sanitized data, such as non-numeric). Try adding quotes around it in the query and/or verifying it has at least a default value:

$article = (empty($article) || !is_numeric($article)) ? 0 : $article;
dbquery("... VALUES ('".$article."', '".clean($commentss['title'])."', '', '".mysql_real_escape_string($_POST['comment'])."', current_timestamp, '".USER_ID."', '0', '".$commentss['type']."', '')");

Original Answer

There is a list of reserved words used by MySQL that, if you use them for column names, you have to escape them with backticks.

Try updating all of them to fix:

dbquery("INSERT INTO site_news_comments (`articleid`, `title`, `short`, `comment`, `timestamp`, `userid`, `main`, `type`, `topstory`) VALUES ...
Improve this answer

3 Comments

Thank for that. Although I am still getting the exactly same error. Its being used on a "news" system for my website. When you post a comment from the "frontpage" of the article, it inserts ok. But when you insert it from another page of comments, such as page 2, it throws the error. I do not know why though.
-1. Although it's good advice to escape column names, it's not the root cause.
@Jack Yeah, I actually went through all of his column-names compared to the reserved-words list and none of them matched (except timestamp, but that's an "okay" one); I updated my answer, but it actually looks like yours beat me to the punch =P
0

Thanks for the help guys! But I fixed the problem!

It seems that the cause of the problem was of the "URL". The URL was

news/1/&page=2

So when I inserted the $article, it came as '1/', this was because it thought that the ID was 1/ , not 1 because of the URL.

So I've just changed it to

news/1&page=2

Thanks!

Improve this answer

Comments

0 
//change this line :
dbquery("INSERT INTO site_news_comments (articleid,title,short,comment,timestamp,userid,main,type,topstory) VALUES ($article,'".clean($commentss['title'])."','','".mysql_real_escape_string($_POST['comment'])."',current_timestamp,'".USER_ID."','0','".$commentss['type']."','')");

//to this : (surround $articleid with single quote)
dbquery("INSERT INTO site_news_comments (articleid,title,short,comment,timestamp,userid,main,type,topstory) VALUES ('".$article."','".clean($commentss['title'])."','','".mysql_real_escape_string($_POST['comment'])."',current_timestamp,'".USER_ID."','0','".$commentss['type']."','')");
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.