[Done v1.3.8] Apostrophes in product names

[BlindSide]

Warning: This bug and associated fixes are pretty in-depth. Not for the faint-of-heart.

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] => 
joeblow###################### [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 .= addslashes(stripslashes($order->products[$i]['name'])) . '(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.

[Kim]

Thanks Frank!

[paulm]

Nice catch

Some notes though:

I think the $sql_data_array is being fed to the database using zen_db_perform() in admin/includes/modules/update_product.php And I am quite sure zen_db_perform() uses the zen_db_input() function. If not an error would show when entering products with apostrophes in the admin.

Also it's logical that the added slashes won't show after:

$apos_check = $db->Execute("select * from products_description where products_id = 349");
echo 'Name is: ' . $apos_check->fields['products_name'] . '<br/>--END';
exit;

because the slashes are not (and should not) be added to the database. But it's very possible I just don't understand what you were trying to do here.

hth

[DrByte]

In terms of authorize.net ... While the addslashes(stripslashes()) approach may suffice, there are some other characters to protect against as well ... namely & and & etc.

I had been contemplating using this instead:

'x_description' => str_replace(array('"',"'",'&','&'), '', $description),

... which just removes any " or ' or & or &
While missing those in product names etc might be a little less friendly in appearance, it's probably the safest.

The entire string being submitted is then url_encoded already ... so any other characters shouldn't be an issue. The only one I was contemplating bothering about would be %

Probably should do the str_replace on the entire string though ... because customer and company name/address info could contain similarly unacceptable characters, which might cause similar problems.


The updated module posted here should help work around these issues, as it does the data-type conversion before writing to the database now: http://www.zen-cart.com/forum/showth...897#post354897

[AlexT]

I am not sure if the suggested fix also works on customer's email addresses, but a single quote in a email address generates similar 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 '[email protected]'' at line 1 in:
[select customers_email_format from zen_customers where customers_email_address= 'xxxxxxx.o'[email protected]']
If you were entering information, press the BACK button in your browser and re-check the information you had entered to be sure you left no blank fields.

This happens when trying to update an order with notify customer box ticked. ZC 1.3.7

[JoeOB]

Frank,

Joe O'Brien here. Is it possible that other characters are also causing the same issue? We had a credit card approved only to have the order be dropped. On the AuthNet report, the order had two items. The first one looks ok. The second item had a * symbol and a # symbol. Thanks.

[DrByte]

Certain characters/symbols are stripped out of the data sent to Authorize.net, to protect against transmission errors. Some are considered invalid as part of transaction submission.

&   |  *  "  '  &  =

NOTE: Latest version for v1.3.7.x posted here: http://www.zen-cart.com/forum/showth...023#post441023

Or, better yet, upgrade to v1.3.8a or newer for even more improvements.