[SOLVED] MySQL choking on string?

[Galab Juman]

I wrote a php script that reads a JSON feed and populates a db with the data. It has been working fine but is now erroring out on a certain entry.

 

I get this error "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 '..."

 

Since it has worked for all other entries I assume its an issue with the string itself. I am using "mysql_real_escape_string" on every string before I put into DB. Are there other ways to "clean" a string of unaccepted chars? Especially if I don't know what those chars may be ? ( I am not making the JSON feed)

 

Thanks for any insight!

 

[gijew]

Do you have an example of what a bad string would look like? Try adding htmlentities() and stripslashes() before bringing the feed into your database.

[Galab Juman]

I did implement those calls just to be safe but I think I may have miss diagnosed the issue. But I have narrowed it down to my update

 

This works

$myquery = "INSERT INTO games (nameid, name, `desc`, time, width, height, cat,instructions, keywords,  active, `type`, authorsite, authorname, updated)

VALUES ('$nameid', '$name', '$desc', UNIX_TIMESTAMP(), '$width', '$height', '$cat','$instructions', '$keywords', 'Yes', 'SWF','$authorsite', '$authorname','$updated')";

$result = mysql_query($myquery) or die(mysql_error());

 

This doesn't:

$myquery = "UPDATE games SET nameid='$nameid', name='$name', `desc`='$desc', time=UNIX_TIMESTAMP(), width='$width', height='$height', cat='$cat',instructions='$instructions', keywords = '$keywords', authorsite='$authorsite', authorname='$authorname', updated='$updated'  WHERE nameid = '$nameid'";

$result = mysql_query($myquery) or die(mysql_error());

 

Any thoughts?

And COULD it have to do with the data itself?

[J.Daniels]

At a quick glance it appears that the query is ok.  Try echoing $myquery to see what is being sent to MySQL.

[Galab Juman]

OK so it looks like it IS because of single quotes (apostrophes) inside a string inside my query

 

So I need to escape these... addslashes() seems to work, as does htmlspecialchars()

 

But how do I restore the formayting upon HTML display of this data?

 

 

[gijew]

stripslashes() after the data is processed by mysql

[Galab Juman]

stripSlashes does NOT restore the formatting. It just removes the slashes.

 

For example:

 

addSlashes turns a carriage return into \r\n

 

stripSlashes turns that to rn

 

I want to turn it back into a carriage return

[Galab Juman]

RESOLVED ( I hope)

 

When entering into DB:

htmlspecialchars ($myString);

 

When displaying:

html_entity_decode($myString)