[Resolved] Database converts hyphens to question marks

[bsandall]

I've noticed that any of my product codes that contain hyphens, e.g. 'ABC-1', are stored in the database instead as 'ABC?1'. They do display correctly in the store, so somewhere in CubeCart they are converted back and forth, but I haven't been able to find out where yet.

This becomes an issue (albeit one that can easily be worked around) when trying to match product codes using direct SQL queries or, for example, reading in a CSV file to automatically update prices.

Anyway, since hyphens don't have any particular special meaning in SQL when included in a string, what is the reason for the conversion?

Edited December 16, 2015 by bsandall
Resolved

[bsmither]

We would like to know what utility you are using to view the product_code column in the CubeCart_inventory table.

A questionmark is typically used by some applications that do not convert properly between certain Code Pages, but may have success at UTF-8.

Using any other utility you may have available, is there still a questionmark? Can the utility you are using show the fields contents in binary?

I am thinking the product_code value may actually contain an -en-dash- or an -em-dash- or something other than 0x2D that is getting displayed correctly (somehow) with the combination of CubeCart, PHP, the web server and the web browser.

FWIW, my product_codes that have a dash show as a dash (hex 2D).

[bsandall]

That was using MySQL console to query the database (it's a local copy) - using the local PHPMyAdmin, however, does correctly show hyphens.

CubeCart tables are all encoded using UTF-8, though, and I've never seen hyphens fail to be encoded properly like this before using UTF-8 encoding. Is it possible that the CC admin pages are using a different encoding?

[bsmither]

No, not a different encoding. And regardless of encoding a x2D character is the same in any encoding because it is within the universally standard set of ASCII characters between x20 and x7F.

Were you able to have phpMyAdmin view the contents of the field in hex mode?

[bsandall]

I'm not sure if this is what you meant, but I cast the product codes to binary in my SQL query and some of them are correctly encoded as '2d' for the hyphen - these display correctly in all cases - and some do not appear to be encoded the same - instead they have 'e28090' for the code, which is the UTF-8 encoded UNICODE hyphen...

So, I think what happened is I copied and pasted some of these codes in from online and/or a spreadsheet file, and the source must have encoded the hyphen in the above manner. Oh, the joy of working with character sets with invisible differences.

[Dirty Butter]

I changed all our product codes a long time ago to use an x between numerical sections. Back then the customer could not search for a product code that had a hyphen in it. I think that's since been corrected. But another issue I DO know still exists is a hidden one. If you accidentally add an extra space while writing a description, the non-breaking space code sticks to the word, so that word is not searchable. I developed the habit of checking the source code of the description before going on to the next tab.

[bsandall]

Yikes, that sounds like a nightmare depending on how complex your descriptions are.

For my particular scenario, I was able to resolve it simply by replacing the hexadecimal version of the character with the one I want:

UPDATE `cubecart_option_matrix` 
    SET product_code=REPLACE(product_code, CHAR(0xe28090), '-') 
    WHERE product_code LIKE CONCAT('%',CHAR(0xe28090),'%');

Thanks @bhsmither for pointing me in the right direction - I wouldn't have thought to check the hex value of the strings otherwise.