REPLACE INTO sometimes fails

[tonyl09]

I have a PHP/mysql form that occasionally fails. The particular piece that fails is:

// Existing email address - update record with latest info and save prior data in history field.
	$result = mysql_query("REPLACE INTO $userstable VALUES('$ID','$cur_date','$download','$email','$assist','$emailcontactname','$emailcontactphone','$group_size','$zipcode','$how_find', '$specialty','$comments','$userinfo','$history', '$countdownload', '$countday', '$autocountemail')",$db) or die ("<center><h3>Unable to update the data in row. Please try again in a bit.</h3></center>");

We sometimes get the "or die" response, but not often. How can I track this issue down?

Thanks -- Tony

[Pro2000]

You may be facing this problem because of the single quote ' or double quote " mark inside some posted field.. Anyway, in order to know what's going wrong, please instead of:

or die ("<center><h3>Unable to update the data in row. Please try again in a bit.</h3></center>");

type:

or die(mysql_error());

Then tell us what occurs.

[tonyl09]

The error message said:

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 't believe what this product can do., , Mozilla/4.0 (compatible; MSIE 8.0; Window' at line 1

That point is where two fields were being updated:

1: a longtext comments field containing (not changed from previous form use)

I can't believe what this product can do.

and
2: a longtext userinfo field containing (not changed from previous form use)

, Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.0; Trident/4.0; SLCC1; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; OfficeLiveConnector.1.5; OfficeLivePatch.1.3; .NET4.0C)

Thank you -- Tony

[Pro2000]

Apparently, you have to prevent the users from entering the single quote directly.. That is, you have to use the htmlspecialchars() function.
For example, if the posted text is named $NEW, the code becomes:

$NEW = htmlspecialchars($NEW, ENT_QUOTES);

Where ENT_QUITES means that you want to prevent the use of the single quotation mark.
This function will also prevent the use of HTML tags by users so that you guarantee that no post will be put in your page to spoil the layout, put some advertisements, spam, etc...
For more information on this function:
http://www.php.net/manual/en/function.htmlspecialchars.php

[ddymacek]

1) I can't believe what this product can do.
Is breaking on the ' mark in can't. your query is seeing this and breaking, nothing would be seen in your query after that ' in cant, and the rest would be dropped resulting in an error.
To fix this you can use addslashes($comments);

2)'userinfo string'.
I'm guessing that you may have the same issue on the userinfo field. Is there more data in that userinfo string than just the error message produces?
I can only guess that there is something before the first , Mozilla/4.0....
What does your original userinfo data look like?
as a quick fix, I would just try to add the addslashes($userinfo); as well.

[Pro2000]

Yes, you can also use addslashes($New) which is better for database queries because this function

Returns a string with backslashes before characters that need to be quoted in database queries etc.

http://www.php.net/manual/en/function.addslashes.php

[tonyl09]

For this situation, I went with htmlspecialchars, cleaning both old and new data, and that worked well.

Thank you -- Tony