Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Problem with special characters and in particular "


PupStar

Recommended Posts

Posted

Hi Guys,

 

I have been pulling my hair out for a couple of days now so its time I threw this open to the community.

 

I am running the Year Make Model contribution and the problem I am having is that when I insert a " into the name of the 'Part' then it produces this 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 ') and p.products_status = '1' and p.products_id = p2c.products_id and pd.produc' at line 1

 

select count(p.products_id) as total from products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id left join specials s on p.products_id = s.products_id, products_to_categories p2c where p.products_id in () and p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1'

 

[TEP STOP]

 

If I remove the " then the query works perfectly. I have posted this problem in the contribution thread but no response as yet and I could do with getting it sorted.

 

This is the query from index.php

 

if($current_category_id) {
 $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where " . (YMM_FILTER_PRODUCT_LISTING == 'Yes' ? $YMM_where : '') . " p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";
}else{
 $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where " . (YMM_FILTER_PRODUCT_LISTING == 'Yes' ? $YMM_where : '') . " p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "'";
}

 

I can not for the life of me see if there is a problem with this.

 

Any ideas would be appreciated.

 

Thanks

 

Mark

Posted

where p.products_id in () 

There needs to be one or more numbers in the ( ) list. I don't recall the solution off the top of my head, but I know this kind of thing has been discussed many times before.

Posted

where p.products_id in () 

There needs to be one or more numbers in the ( ) list. I don't recall the solution off the top of my head, but I know this kind of thing has been discussed many times before.

 

Thanks Phil,

 

I have tried it with more than 1 number in the list and the " still produces the error.

 

I have searched but can not seem to find anything that really relates to this problem.

 

Regards

 

Mark

Posted

Just a quick answer at first glance (I don't know the YMM-contrib):

IMHO, the " is causing the PHP code to break the string. Try to use \" in order to escape the " character and see if this helps. if not, you could also try the entity code " (if the text is only shown on web pages).

 

Hope it helps...

Posted

Just a quick answer at first glance (I don't know the YMM-contrib):

IMHO, the " is causing the PHP code to break the string. Try to use \" in order to escape the " character and see if this helps. if not, you could also try the entity code " (if the text is only shown on web pages).

 

Hope it helps...

 

Hi Dirk,

 

Thanks for the reply but the entity code did not work and neither does escaping the character with \ because it is part of the text in the database record.

 

It somehow needs to be escaped when called in the query.

 

Mark

Posted

I thought I responded to this earlier today, but I don't see my post.

 

I don't see a " in the error listing, and the PHP code you gave doesn't appear to have anything to do with the error listing. If the add-on is poorly written and doesn't escape various quotes and other special characters, that could be a problem. Also, are you sure you're talking about a real " and not an MS Smart Quote you copy and pasted in from Word or Outlook? A Smart Quote is not a valid Latin-1 or UTF-8 character and could give the database a headache trying to process it (as a control code).

Posted

I thought I responded to this earlier today, but I don't see my post.

 

I don't see a " in the error listing, and the PHP code you gave doesn't appear to have anything to do with the error listing. If the add-on is poorly written and doesn't escape various quotes and other special characters, that could be a problem. Also, are you sure you're talking about a real " and not an MS Smart Quote you copy and pasted in from Word or Outlook? A Smart Quote is not a valid Latin-1 or UTF-8 character and could give the database a headache trying to process it (as a control code).

 

no they are not cut and pasted from anywhere.

 

The field in the database is set as VARCHAR so not really sure why it does not work.

 

So if it works for the product name then surely it should work for this urgh

 

Mark

Posted

The error message you gave in your first post does not have a " anywhere, and does not appear to have a "name" field. You say you remove the " (from what?) and it works. It does have an ID list which is empty: (). You don't just randomly stick numbers in it and hope for the best... the PHP code building the query is supposed to supply a list of one or more ID numbers, and isn't for some reason. Perhaps a " in a name is breaking the code and not allowing the associated product ID to get into the ID list? This (an empty list) I'm sure has been discussed many times before, and it would be worth searching the forum for it (it might give a lead to where to find the "-in-name causing the problem). Finally, the PHP code you gave after the error message does not appear to have anything to do with the indicated query.

Posted

The error message you gave in your first post does not have a " anywhere, and does not appear to have a "name" field. You say you remove the " (from what?) and it works. It does have an ID list which is empty: (). You don't just randomly stick numbers in it and hope for the best... the PHP code building the query is supposed to supply a list of one or more ID numbers, and isn't for some reason. Perhaps a " in a name is breaking the code and not allowing the associated product ID to get into the ID list? This (an empty list) I'm sure has been discussed many times before, and it would be worth searching the forum for it (it might give a lead to where to find the "-in-name causing the problem). Finally, the PHP code you gave after the error message does not appear to have anything to do with the indicated query.

 

I am not really sure how to explain it to make it any clearer. So here are some images

 

Image 1 shows the 'products_car_part' field to contain no special characters i.e. "

 

Image 2 shows the 'products_car_part' field to contain the special character "

Posted

If you have a " in a name, then you have to use ' to surround it. And vice versa.

 

"Don't"

 

'10" width'

 

if that is the case then how are " handled in say the products name when the field type is set to the same VARCHAR???

 

Maybe I am totally missing the point and it is more simple than I think :thought_balloon:

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...