Totally Zenned
- Join Date:
- Sep 2004
- Posts:
- 1,237
- Plugin Contributions:
- 1
[Done v1.3.8] Apostrophes in product names
Warning: This bug and associated fixes are pretty in-depth. Not for the faint-of-heart. :flex:
We recently had an authorize.net credit card charge without an accompanying Zen Cart order. I put authorize.net into test mode and recreated the customer's order. When I submitted the order for final processing, I got this nasty SQL error:
1064 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 'Fun: Ultimate Improv(qty: 1) [Date] => July 17, 2007, 9:20 in:
[insert into authorizenet (id, customer_id,order_id, response_code,
response_text, authorization_type, transaction_id, sent, received, time,
session_id) values ('', '1', '12451', '1', '(TESTMODE) This transaction has been
approved.', 'auth_capture', '0', 'Array ( [x_login] => ******* [x_tran_key] =>
******* [x_relay_response] => FALSE [x_delim_data] => TRUE [x_version]
=> 3.1 [x_type] => AUTH_CAPTURE [x_method] => CC [x_amount] => 70.00
[x_card_num] => *******0015 [x_exp_date] => 0110 [x_card_code] =>
******* [x_email_customer] => FALSE [x_email_merchant] => TRUE
[x_cust_id] => 1 [x_invoice_num] => TEST-12451 [x_first_name] => Frank
[x_last_name] => Smith [x_company] => Personal [x_address] => 123 My
Street [x_city] => Town [x_state] => New Jersey [x_zip] => 00000 [x_country]
=> United States [x_phone] => 111-222-3333 [x_email] =>
[email protected] [x_ship_to_first_name] => Frank [x_ship_to_last_name]
=> Smith [x_ship_to_address] => 123 My Street [x_ship_to_city] => Town
[x_ship_to_state] => New Jersey [x_ship_to_zip] => 00000 [x_ship_to_country]
=> United States [x_description] => Bucket O' Fun: Ultimate Improv(qty: 1)
[Date] => July 17, 2007, 9:20 am [IP] => 68.37.226.107 [Session] =>
REMOVED [x_test_request] => TRUE [url] =>
https://secure.authorize.net/gateway/transact.dll ) ', 'REMOVED', 'July 17, 2007,
9:20 am', 'REMOVED')]
In case the issue is not jumping out at you, the problem rests with the value in the [x_description] key, namely that apostrophe in "Bucket O' Fun."
I backtracked this value to see how and where it's created, ultimately ending up in the catalog's order class and the value placed in $order->products[$i]['name']. However, everywhere I looked this value was coming directly from the DB, so it should already have been clean.
So I went further, and looked directly at the DB entry for this product. I drew up a small script that I then ran in my admin:
$apos_check = $db->Execute("select * from products_description where products_id = 349");
echo 'Name is: ' . $apos_check->fields['products_name'] . '<br/>--END';
exit;
Note that I am not doing any scrubbing of the value. I am also not using $db->bindVars(), so nothing is being scrubbed by the $db class directly. Here's the output:
Name is: Bucket O' Fun: Ultimate Improv
--END
It appears that there's absolutely no initial scrub-in for the product name. This means that the admin pages where products are edited/created are at least not prepping the input of the product name. Off to the Admin!
This product uses the Zen Cart default product type, so I didn't make the mess (good news), but everyone else is vulnerable too (bad news). I went through the entire process of creating and editing a product, and found that the product name is not being correctly scrubbed in.
Here's the offending line, 117 in admin/includes/modules/update_product.php:
$sql_data_array = array('products_name' => zen_db_prepare_input($_POST['products_name'][$language_id]),
Looks okay, right? Unfortunately, zen_db_prepare_input() does not actually prep input for safe database inserts, it only cleans it up. For proper inserts, you also need to use zen_db_input(). Here's those two functions for reference:
function zen_db_input($string) {
return addslashes($string);
}
function zen_db_prepare_input($string) {
if (is_string($string)) {
return trim(stripslashes($string));
} elseif (is_array($string)) {
reset($string);
while (list($key, $value) = each($string)) {
$string[$key] = zen_db_prepare_input($value);
}
return $string;
} else {
return $string;
}
}
addslashes() is only present in zen_db_input().
I could simply add zen_db_input here, however given the number of places that a product name is called, there's no guarantee that they all properly scrub the name out, meaning it could display with slashes. It could even create the compound slash effect, where slashes are added to slash out existing slashes.
So, for the time being at least, I decided to nip this problem at the final source of contention. I opened includes/modules/payment/authorizenet_aim.php and found line 305:
$description .= $order->products[$i]['name'] . '(qty: ' . $order->products[$i]['qty'] . ') + ';
...and edited to appear as follows:
$description .= [B]addslashes(stripslashes([/B]$order->products[$i]['name'][B]))[/B] . '(qty: ' . $order->products[$i]['qty'] . ') + ';
The stripslashes() call ensures that we don't get compounding slashes as described above.
I actually believe that, even if the source of the problem is fixed, this correction should stay in place, since it's a reasonable failsafe without any negative consequences.
Frank Koehl
"Cleverly Disguised as a Responsible Adult"
frankkoehl.com
