CODEWITHSUNDEEP
mysqlcinsertsyntax-error
Nick
Nick

Reputation: 6025

MySQL syntax error for INSERT

This precise code was working before on my old machine, but I'm getting this error spat back at me (on Mac OSX 10.7).

The C code is: 

printf("%s\n",query);

if (mysql_query(conn,query)){
    fprintf(stderr, "%s\n", mysql_error(conn)); 
}

The output is:

INSERT INTO comment (unum,cat_subject,cat_major,cat_minor,unmod) 
 VALUES (1,1,1,0,'The cat was lazy.') 

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 ','The cat was lazy.')' at line 1

The original printf statements creates two lines of text, but I've wrapped them for readability.

It all looks fine to me! The following works in PHP for the same table (with the correct bindParam statements):

$STH=$DBH->prepare("INSERT INTO comment (unum,cat_subject,cat_major,cat_minor,unmod) VALUES (:u,:s,:ma,:mi,:t)");

What's wrong?

(BTW: I tried it with " instead of ' but that made no difference.)

Upvotes: 0

Views: 268

Answers (1)

eggyal
eggyal

Reputation: 125835

(Upgrading to an answer)

It appears that you may have non-printable characters in query, probably after the 0 character. Try hexdumping the string's bytes to see if anything is suspicious:

char* c;
for (c=query; *c; c++) printf("%02x",*c);

Should you be concatenating into your SQL the unknown contents of variables, I'd strongly recommend using MySQL's prepared statement API to pass your variables to MySQL as parameters in order to avoid SQL injection; this will also perform basic type-conversion of such parameters to the destination column types - if you need more control over the cleansing of your variables, you will need to perform such in your application prior to passing the parameters to MySQL.

Upvotes: 2

Related Questions