[RESOLVED] mysql insert single quote problem

[cafrow]

I am having a problem that for some reason I am unable to get this insert to work. I have a string that contains single quotes ' and its killing my insert statement.


if(!mysql_query("INSERT INTO `" . TABLE_PRODUCTS_DESCRIPTION_TEMP . "` (`products_name`, `products_description`, `language_id`, `products_id`) VALUES ('" . get_product_name($products[$new_products[$i]]['item_number']) . "', '" . smart($products[$new_products[$i]]['description']) . "', '1', '" . $insert_product_id . "')"))

function smart($value)
{
// Stripslashes
if (get_magic_quotes_gpc())
{
$value = stripslashes($value);
}
// Quote if not integer
if (!is_numeric($value))
{
$value = mysql_real_escape_string($value);
}

return $value;
}


I have tried the HTMLentities and addslashes but neither one of those worked. What would be the best way for me to insert a string that might contain special characters without breaking my query and still being safe from mysql hacking. Thanks for any input, this is part of an OSCommerce store so I need the info that is stored in the database to be able to pulled out without calling any special cleanup functions when display the data in the store.

Thanks for any help with this, I cannot believe that something so simple is messing me up so bad.

[chazzy]

change your code to something like this to help detect errors:


mysql_query("INSERT INTO `" . TABLE_PRODUCTS_DESCRIPTION_TEMP . "` (`products_name`, `products_description`, `language_id`, `products_id`) VALUES ('" . get_product_name($products[$new_products[$i]]['item_number']) . "', '" . smart($products[$new_products[$i]]['description']) . "', '1', '" . $insert_product_id . "')") or die("Unable to run insert: ".mysql_error());

[NogDog]

Maybe it's just a copy-and-paste error/confusion, but in the code sample you provided the smart() function would only get defined if the query failed in the first place. In any case, here's a pattern that should at least provide some debug info:

function smart($value)
{
// Stripslashes
if (get_magic_quotes_gpc())
{
$value = stripslashes($value);
}
// Quote if not integer
if (!is_numeric($value))
{
$value = mysql_real_escape_string($value);
}

return $value;
}

$query = "INSERT INTO `" . TABLE_PRODUCTS_DESCRIPTION_TEMP . "` (`products_name`, `products_description`, `language_id`, `products_id`) VALUES ('" . get_product_name($products[$new_products[$i]]['item_number']) . "', '" . smart($products[$new_products[$i]]['description']) . "', '1', '" . $insert_product_id . "')";
$result = @mysql_query($query);
if(!$result)
{ # get some debug info:
echo "<p class='error'>Query failed: $query - " . mysql_error() . "</p>\n";
exit;
}
if(mysql_affected_rows() != 1)
{
echo "<p class='error'>Error: " . mysql_affected_rows() . " rows inserted. Query: $query</p>\n";
exit;
}
# looks like everthing's OK, so continue with any other processing....

[cafrow]

I have altered the code to give a better error reporting for this troubleshooting. Here is what the code looks like now


$sql = "UPDATE `" . TABLE_PRODUCTS_DESCRIPTION_TEMP . "` SET
`products_description` = '" . $bnf_products[$i]['description'] . "',
`products_name` = '" . get_product_name($bnf_products[$i]['item_number']) . "'
WHERE products_id = '" . $prod_id['products_id'] . "'";
if(!@mysql_query($sql))
{
$output .= "Error Updating product model: " . $bnf_products[$i]['item_number'] . "<br>";
$output .= "SQL query: " . $sql . "<br>";
$output .= mysql_error() . "<br>";
}
function smart($value)
{
// Stripslashes
if (get_magic_quotes_gpc())
{
$value = stripslashes($value);
}
// Quote if not integer
if (!is_numeric($value))
{
$value = mysql_real_escape_string($value);
}

return $value;
}


Here is the output error that I am getting


Error Updating product model: GFCLMJ2X

SQL query: UPDATE `products_test_description_temp` SET `products_description` = 'Diamond Plate Men's Belted Solid Leather Motorcycle Jacket. This motorcycle jacket features silver hardware zipup front multiple zippered and snap pockets inside breast pocket adjustable zippered cuffs and a belt across the front of the waist. Size 2X. 8 lbs each. 11200PT 222.95 Item #GFCLMJ2X', `products_name` = 'MOTORCYCLE JKT ZIP, 2X' WHERE products_id = '34249'

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 's Belted Solid Leather Motorcycle Jacket. This motorcycle jacket features silver' at line 2

[cafrow]

Well I went back and rewrote the insert the way I normally do it, which is with sprintf, I have heard from people that they hate sprintf and other say they like it, personally i love it. here is what i made it look like, no error this time.


$sql = sprintf("UPDATE `" . TABLE_PRODUCTS_DESCRIPTION_TEMP . "` SET
`products_description` = %s,
`products_name` = %s
WHERE products_id = %s",
smart2($bnf_products[$i]['description']),
smart2(get_product_name($bnf_products[$i]['item_number'])),
smart2($prod_id['products_id']));
if(!@mysql_query($sql))
{
$output .= "Error Updating product model: " . $bnf_products[$i]['item_number'] . "<br>";
$output .= "SQL query: " . $sql . "<br>";
$output .= mysql_error() . "<br>";
}

function smart2($value)
{
// Stripslashes
if (get_magic_quotes_gpc())
{
$value = stripslashes($value);
}
// Quote if not integer
if (!is_numeric($value))
{
$value = "'" . mysql_real_escape_string($value) . "'";
}

return $value;
}