Errors in log file coming from usage of quote characters

I have been struggling over this for a while now.

A cart needs to place and use the ' for feet and " for inches. First I had problems with EP4. which was solved. Now I am getting the following in the error logs. I can't figure out what to do about them.

[21-May-2013 04:12:34 America/Denver] PHP Fatal 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 '18" x 12"", GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, " ", ""), pov.' at line 7 :: SELECT DISTINCT p.products_id, p.products_type, p.master_categories_id,
p.manufacturers_id, p.products_price, p.products_tax_class_id, pd.products_description, IF(s.status = 1,
s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status =1, s.specials_new_products_price,
p.products_price) as final_price, p.products_sort_order, p.product_is_call, p.product_is_always_free_shipping,
p.products_qty_box_status FROM zen_products p LEFT JOIN zen_specials s on p.products_id = s.products_id LEFT JOIN zen_products_description pd on p.products_id = pd.products_id JOIN zen_products_to_categories p2c on p.products_id = p2c.products_id JOIN zen_products_attributes p2a on p.products_id = p2a.products_id JOIN zen_products_options po on p2a.options_id = po.products_options_id JOIN zen_products_options_values pov on p2a.options_values_id = pov.products_options_values_id WHERE p.products_status = 1
and pd.language_id = '1'
and p2c.categories_id = '12' GROUP BY p.products_id HAVING ( FIND_IN_SET("Size:18" x 18" x 12"", GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, " ", ""), pov.products_options_values_name)))) in /MyPath/includes/classes/db/mysql/query_factory.php on line 120

[21-May-2013 04:26:31 America/Denver] PHP Fatal 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 '", ""), pov.products_options_values_name))))' at line 1 :: select distinct * FROM zen_products p left join zen_manufacturers m on (p.manufacturers_id = m.manufacturers_id) left join zen_products_to_categories p2c on p.products_id = p2c.products_id left join zen_products_description pd on p.products_id = pd.products_id join zen_products_attributes p2a on p.products_id = p2a.products_id join zen_products_options po on p2a.options_id = po.products_options_id join zen_products_options_values pov on p2a.options_values_id = pov.products_options_values_id WHERE p.products_status = 1 AND pd.language_id = 1 and p.products_date_added >=20130507 GROUP BY p.products_id HAVING ( FIND_IN_SET("Size:2' x 3'-3"", GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, " ", ""), pov.products_options_values_name)))) in /MyPath/includes/classes/db/mysql/query_factory.php on line 120

[21-May-2013 04:26:45 America/Denver] PHP Fatal 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 '7'-6" Size", GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, " ", ""), p' at line 1 :: select distinct * FROM zen_products p left join zen_manufacturers m on (p.manufacturers_id = m.manufacturers_id) left join zen_products_to_categories p2c on p.products_id = p2c.products_id left join zen_products_description pd on p.products_id = pd.products_id join zen_products_attributes p2a on p.products_id = p2a.products_id join zen_products_options po on p2a.options_id = po.products_options_id join zen_products_options_values pov on p2a.options_values_id = pov.products_options_values_id WHERE p.products_status = 1 AND pd.language_id = 1 and p.products_date_added >=20130507 GROUP BY p.products_id HAVING ( FIND_IN_SET("Size:2'-3" x 7'-6" Size", GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, " ", ""), pov.products_options_values_name)))) in /MyPath/includes/classes/db/mysql/query_factory.php on line 120

and the latest:

[21-May-2013 07:08:52 America/Denver] PHP Warning: Variable passed to each() is not an array or object in /MyPath/includes/classes/db/mysql/query_factory.php on line 153

First, despite its mention in the error messages, query_factory.php is only mentioned because it is the part of the code that sends the query to the server. It is only the messenger.

You need to find whatever part of your custom PHP code is building those queries, and fix what it's doing wrong.

It's probably completely ignoring the fact that queries, as with any string of text, cannot mix and match ' or " marks inside the string if the string starts and ends with the same symbol, unless the symbol is escaped correctly.

I just did an online search on escaping quotes in PHP and these articles came up, which speak to the concept:
http://en.kioskea.net/faq/30229-programming-quotation-marks-apostrophes-and-strings
http://www.untwistedvortex.com/php-programming-beginners-quotation-marks/

Who wrote the code that's doing the replacements you mention?

Kevin205:

I have been struggling over this for a while now.
A cart needs to place and use the ' for feet and " for inches. First I had problems with EP4. which was solved. Now I am getting the following in the error logs. I can't figure out what to do about them.

These are always troublesome characters, and there are only two safe ways to use them:

1. Escape them with backslashes before inserting into the database.
2. Replace them with their HTML equivalents (see: http://www.degraeve.com/reference/specialcharacters.php)

In either case you'll find you'll still have issues if/when trying to search for these strings.

Personally, I try to avoid using these characters at all where possible. "in" and "ft" are just as effective descriptors, without the pitfalls of using quotes.

I agree, its not a perfect solution, but it is simple, effective and practical, and if I can avoid these potential problems by avoiding these characters I consider it a good solution.

Cheers
Rod

DrByte:

Who wrote the code that's doing the replacements you mention?

I was getting errors while using EP4 and when importing (Attrib-Basic-EP) for placing attributes named Size: under v_products_options_name_1 and values similar to 1'6" x 3'2" under v_products_options_values_name_1 to table.

The errors where fixed by replacing the easypopulate_4_attrib.php file recommended by Chad. This post.

RodG:

These are always troublesome characters, and there are only two safe ways to use them:

1. Escape them with backslashes before inserting into the database.

Thanks for the reply.

I am trying to avoid using **in **and ft, but it's giving me a lot of headaches!

Can ' and " characters both be escaped as follow?
' and "

Kevin205:

Can ' and " characters both be escaped as follow?
' and "
If your string starts and ends with double-quotes, then you must escape all double-quotes within that string, but leave the single-quotes alone.
And vice-versa if your string starts and ends with single-quotes.

Kevin205:

I am trying to avoid using **in **and ft, but it's giving me a lot of headaches!

Which is why I avoid the ' & " .

Either way, its still a problem.

Kevin205:

Can ' and " characters both be escaped as follow?
' and "

If your string starts and ends with double-quotes, then you must escape all double-quotes within that string, but leave the single-quotes alone.
And vice-versa if your string starts and ends with single-quotes.

Couldn't have said it better.

Cheers
Rod

This is crazy. I can’t seem to get it correct.

In this article it mentions "A simple technique to avoid errors is to count the number of quotation marks or apostrophes, If it is odd, then it will certainly generate an error."; Which in my case the result is clearly not odd set of quotation marks as they are even (6 quotation marks).

Data originally generated in Excel (Original.xls) as follow for size of a product:
Col 1 Row 1, Col2 Row1
Size: , 1’-4” X 2’-6”
Col 1 Row 2, Col2 Row2
Size: , 1’-4\” X 2’-6\”

Case: Saved through **Excel **as CSV (MS-DOS) (*.csv) Original.csv
Original.csv results in: (viewed in Notepad++)
Size:,"1'-8"" x 3'-7"""
Size:,"1'-8"" x 3'-7"""

Case: File Original.xls opened Open Office and Saved as Text CSV (.csv) (*.csv)
Saved with the following parameters:
Character set: Unicode (UTF-8)
Field delimiter: ,
Text delimiter: "

Original_OO.csv results in: (viewed in Notepad++)
Size:,"1'-8"" x 3'-7"""
Size:,"1'-8"" x 3'-7"""

I noticed

• The resulted .csv files from both Excel and Open Office are identical!
• Both applications, while generating the .csv place the " before "" (""") for the second escape, where I think it should be like (""")

How would one solve this issue?

And still getting the following error:

[24-May-2013 06:36:52 America/Denver] PHP Fatal 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 '3'-7"", GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, " ", ""), pov.pro' at line 7 :: SELECT DISTINCT p.products_id, p.products_type, p.master_categories_id,
p.manufacturers_id, p.products_price, p.products_tax_class_id, pd.products_description, IF(s.status = 1,
s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status =1, s.specials_new_products_price,
p.products_price) as final_price, p.products_sort_order, p.product_is_call, p.product_is_always_free_shipping,
p.products_qty_box_status FROM zen_products p LEFT JOIN zen_specials s on p.products_id = s.products_id LEFT JOIN zen_products_description pd on p.products_id = pd.products_id JOIN zen_products_to_categories p2c on p.products_id = p2c.products_id JOIN zen_products_attributes p2a on p.products_id = p2a.products_id JOIN zen_products_options po on p2a.options_id = po.products_options_id JOIN zen_products_options_values pov on p2a.options_values_id = pov.products_options_values_id WHERE p.products_status = 1
and pd.language_id = '1'
and p2c.categories_id = '36' GROUP BY p.products_id HAVING ( FIND_IN_SET("Size:1'-8"x 3'-7"", GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name, " ", ""), pov.products_options_values_name)))) in /MyPath/includes/classes/db/mysql/query_factory.php on line 120

I tried placing the escape " directly in, through admin’s Attribute Controller > Option Name and Option Value as follow:
**Size: 231'-6" x 1'-6" **

Once inserted it looked liked the following with no escape character

This is what it looks like in the SQL products_options_values table

So where and how are you suppose to use the escape character for this type of Option Values, which it is commonly used for dimensions?

I tried placing the escape " directly in, through admin’s Attribute Controller > Option Names and Option Values as follow:
**Size: 231'-6" x 1'-6" **

Once inserted it looked liked the following with no escape character

This is what it looks like in the SQL products_options_values table

So how are you suppose to use the escape character for this type of Option Values, which are commonly used to display dimensions?

Kevin205:

In this article it mentions "A simple technique to avoid errors is to count the number of quotation marks or apostrophes, If it is odd, then it will certainly generate an error.";

This is somewhat misleading advise. It neglects to mention that if the quotation marks *or apostrophes *have been escaped then they shouldn't be counted. Nor does it mention that an even number of quotation marks and apostrophes is no assurance that there isn't a mismatch, especially if they've been mixed.

Kevin205:

Which in my case the result is clearly not odd set of quotation marks as they are even (6 quotation marks).

I take my comments back. It isn't just misleading advise, it is bad advice, best ignored.

Kevin205:

How would one solve this issue?

These are always troublesome characters, and there are only two safe ways to use them:

1. Escape them with backslashes before inserting into the database.
2. Replace them with their HTML equivalents (see: http://www.degraeve.com/reference/specialcharacters.php)

Showing us the data of your CSV exports, etc is of no use to us whatsoever. It doesn't change what you've been told. Strings must start and end with either a quote or apostrophe. and any other special characters within the string needs to be "escaped".
Also, you shouldn't forget, that if you add the escape slashes when entering the data into the database, you'll need to remove them again after your read the data back from the database (before doing else with it).

When dealing with CSV files its up to you to ensure the proper escaping has taken place. If the data is being entered via a script, the strings can be escaped somewhat automatically via the PHP addslashes() function (and removed again via the stripslashes() function.

I will once again re-repeat my stance on this though. It is simply best to simply avoid using these characters, at least until you've gained a good understanding of why the problem exists in the first place, because until you actually understand the reasons for the problem you don't have any means of avoiding them.

If when you do understand the reasons you won't need to ask us how to solve them. You'll 'instinctively' know when/where and what characters need to be escaped (and which ones don't).

It isn't difficult to understand, but what we are telling you about the need to 'escape' certain characters needs to be more than just 'words'. Hopefully I'm making some kind of sense here?

Cheers
Rod

Rod – Before anything, I wanted to thank you for spending your precious time explaining the situation to me. I am trying to make sense out of this!

What the error message does not tell me is that whether the error is getting generated from double quotation mark (") or the apostrophe (').

Would you please tell me if you think I should place & #34; and & #39 or & #34; is enough? (space between & and # is placed by me so it can display here)

I wish I knew how to implement this so it would stop the errors from getting generated.

You are using:

FIND_IN_SET("Size:1'-8"x 3'-7"",

As I explained previously, when you use double-quotes to bound something, the opening double-quote must match the closing double-quote.
Same if you were to start and end with single-quotes.

AND then BETWEEN those opening-and-ending quotes, ALL QUOTES OF THE SAME STYLE MUST BE ESCAPED.

So, thus, where you open and close with double-quotes, then ALL double-quotes BETWEEN the opening and ending double-quotes must be escaped.

Example:

FIND_IN_SET("Size:1'-8\"x 3'-7\"",

AND FURTHER, since your "FIND_IN_SET" clause is already inside another BIG LONG STRING, which must open and end with a set of matching quotes, and yet you're also mixing both double and single quotes inside that same big long string. So, while my notes for fixing your FIND_IN_SET syntax are correct, you will also need to fix things to accommodate whatever quote style you're opening and closing your entire SQL string within.

I just narrowed it down, and found out that it's the Dynamic Filter v1.0 that's causing the error.

Kevin205:

I just narrowed it down, and found out that it's the Dynamic Filter v1.0 that's causing the error.

Swiiisssshhhhhh.... That's the sound I just heard as you flushed all we have been trying to tell you down the toilet.

Oh well, win some, lose some.

Cheers
Rod

Hello Rod – Your help and efforts of DrByte did absolutely not go down the drain!

You made me understand the main cause of the problem (double-quotes and single-quotes in PHP), and that my friend resulted in me finding the trigger point; what initiated the errors (Dynamic Filter).

If Dynamic filter is not used to filter for Size attribute the error does not get generated, and carts work perfectly with dimension used as (1’-3” x 2’-2”); without the use of any escape or special HTML character codes.

Furthermore, by your help, now customers and robots browsing in 2 different carts (sites) are not getting the following error message on their browser by going through the cart and trying to filter for size attribute (I excluded the attribute size to appear among filters, through Dynamic Filter Config).

WARNING: An Error occurred, please refresh the page and try again.

To me, it was a winner.

Thank you again for all your efforts. -- Kevin

PS. I and the shop owners still need the size attribute filtered, and with information gained here, I am trying to resolve this issue.

Kevin205:

You made me understand the main cause of the problem (double-quotes and single-quotes in PHP),

I just wish to clarify that this isn't a PHP issue. The same problem exists with virtually all computer languages. It is a fundamental issue in the way all computers deal with text strings (he says cautiously).

Cheers
Rod

I have posted a solution in the Dynamic filter thread