[Done 1.3.8] Apostrophes in any order data w/ authorize.net database logging on

[bjornarl]

I have experienced other errors related to this bug (http://www.zen-cart.com/forum/showthread.php?p=404423) while using 1.3.7 (and just finished upgrading to 1.3.8a which doesn't appear to fix it). Some of the discussion has centered around what is sent to authorize.net, but the other issue is invalid database update statements due to unmatched apostrophes.

If any field in the order data contains an apostrophe -- whether in product names, customer name, customer address, etc -- and if "Enable Database Storage" is set to TRUE in the AIM module configuration, then the customer will receive a hard SQL error message when attempting to complete their order.

This is the fix that has worked for me (recently updated for 1.3.8a):

In authorizenet_aim.php, replace line 619:

$sql = $db->bindVars($sql, ':sentData', print_r($this->reportable_submit_data, true), 'string');

with:

$reportable_submit_data_text = print_r($this->reportable_submit_data, true);
$reportable_submit_data_text = str_replace("'", "''", $reportable_submit_data_text);
$sql = $db->bindVars($sql, ':sentData', $reportable_submit_data_text, 'string');

This effectively replaces single ' characters in the string to be saved with '' which works correctly. However, I am new to PHP, so there is probably a more elegant way to handle this.

Bjornar

[DrByte]

This effectively replaces single ' characters in the string to be saved with '' which works correctly. However, I am new to PHP, so there is probably a more elegant way to handle this.

The 'string' parameter in the bindVars() function also transforms single ' characters with an appropriate character to allow insertion, and considers which database engine is in use to ensure it selects the appropriate character-escaping method to be used.

If that's not working on your server, then, while I have no idea what your server specs and PHP configuration, version, etc details are, I'm inclined to suspect that you have something odd with magic_quotes or magic_quotes_sybase settings.

[bjornarl]

Ah, 1.3.7 didn't have the bindVars() call and this bug was definitely a problem then (at least on my server). I must have failed to test this properly in 1.3.8 (and when I looked at the 1.3.8 code I did not spot the transformation you mention).

Sorry for the false report but thanks for the detailed response!

Bjornar

[standpipe]

Any way to fix this in 1.3.7 ? I'm not looking forward to doing an upgrade at this point, as I simply don't have the time or enough bugs to justify it yet.

[DrByte]

http://www.zen-cart.com/forum/showth...023#post441023

[bjornarl]

Any way to fix this in 1.3.7 ? I'm not looking forward to doing an upgrade at this point, as I simply don't have the time or enough bugs to justify it yet.

Looking in my version history, here's what I did to fix mine in 1.3.7:

In authorizenet_aim.php, replaced:

      // Insert the data into the database
      $db->Execute("insert into " . TABLE_AUTHORIZENET . "  (id, customer_id,order_id, response_code, response_text, authorization_type, transaction_id, sent, received, time, session_id) values ('', '" . $_SESSION['customer_id'] . "', '" . $new_order_id . "', '" . $db_response_code . "', '" . $db_response_text . "', '" . $db_authorization_type . "', '" . $db_transaction_id . "', '" . print_r($reportable_submit_data, true) . "', '" . $response_list . "', '" . $order_time . "', '" . $db_session_id . "')");

with

      // Insert the data into the database
	  // BUGFIX: Added escaping of apostrophes used in addresses and other info
      $reportable_submit_data_text = print_r($reportable_submit_data, true);
      $reportable_submit_data_text = str_replace("'", "''", $reportable_submit_data_text);

      $db->Execute("insert into " . TABLE_AUTHORIZENET . "  (id, customer_id,order_id, response_code, response_text, authorization_type, transaction_id, sent, received, time, session_id) values ('', '" . $_SESSION['customer_id'] . "', '" . $new_order_id . "', '" . $db_response_code . "', '" . $db_response_text . "', '" . $db_authorization_type . "', '" . $db_transaction_id . "', '" . $reportable_submit_data_text . "', '" . $response_list . "', '" . $order_time . "', '" . $db_session_id . "')");

Use at your own risk :)

[DogTags]

Looking in my version history, here's what I did to fix mine in 1.3.7:

In authorizenet_aim.php, replaced:

      // Insert the data into the database
      $db->Execute("insert into " . TABLE_AUTHORIZENET . "  (id, customer_id,order_id, response_code, response_text, authorization_type, transaction_id, sent, received, time, session_id) values ('', '" . $_SESSION['customer_id'] . "', '" . $new_order_id . "', '" . $db_response_code . "', '" . $db_response_text . "', '" . $db_authorization_type . "', '" . $db_transaction_id . "', '" . print_r($reportable_submit_data, true) . "', '" . $response_list . "', '" . $order_time . "', '" . $db_session_id . "')");

with

      // Insert the data into the database
	  // BUGFIX: Added escaping of apostrophes used in addresses and other info
      $reportable_submit_data_text = print_r($reportable_submit_data, true);
      $reportable_submit_data_text = str_replace("'", "''", $reportable_submit_data_text);

      $db->Execute("insert into " . TABLE_AUTHORIZENET . "  (id, customer_id,order_id, response_code, response_text, authorization_type, transaction_id, sent, received, time, session_id) values ('', '" . $_SESSION['customer_id'] . "', '" . $new_order_id . "', '" . $db_response_code . "', '" . $db_response_text . "', '" . $db_authorization_type . "', '" . $db_transaction_id . "', '" . $reportable_submit_data_text . "', '" . $response_list . "', '" . $order_time . "', '" . $db_session_id . "')");

Use at your own risk :)

What if someone uses a double quote in their account info?

Is there a way to add the double quote character to the list of escaped characters in the mini fix above?

Many thanks

[bjornarl]

What if someone uses a double quote in their account info?

Is there a way to add the double quote character to the list of escaped characters in the mini fix above?

Many thanks

I wouldn't have thought double quotes (") would cause a problem, so without knowing what the problem is I'm not sure what you would want to replace them with. But you could certainly add a second str_replace line to do additional replacements...

[DogTags]

I'd want to just strip the double quotes (and other unallowed characters) totally

[bjornarl]

Well I don't think anything other than apostrophes (') are "unallowed" in the database SQL that gets used. (It's possible I'm wrong).

However if you really want to strip out double quotes you can probably just add another str_replace line along these lines:

$reportable_submit_data_text = str_replace("\"", "", $reportable_submit_data_text);

I'm not sure whether that will have unintended consequences though so you'd have to test well.