CODEWITHSUNDEEP
mysqlsql
dreamchaser
dreamchaser

Reputation: 157

a sql command why it shows an error?

$categories_query ="SELECT p.products_id 
,  p.products_quantity
, p.products_price 
, p.products_status 
,p.products_image
, pd.products_name

FROM products_description AS pd
INNER JOIN products AS p
ON p.products_id = pd.products_id
WHERE  p.products_status = 1 and p.products_id <> $pid and pd.products_name LIKE CONCAT(LEFT('$products_name',10),'%')";

some pages are running ok, even if there is no relative items. but when the $products_name is A Bug's Life "Multi Pak" Special 2003 Collectors Edition. it shows an 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 's Life "Multi Pak" Special 2003 Collectors Edition',10),'%')' at line 11
in:
[SELECT p.products_id...

why? and how to correct it?

Upvotes: 1

Views: 235

Answers (2)

Avo Murom&#228;gi
Avo Murom&#228;gi

Reputation: 1593

Example value of $products_name contains ' symbol, so sql query is messed up.

At the moment part of your query would be something like that

... (LEFT('Bug's Life "Multi Pak" Special 2003 Collectors Edition',10) ...

Note that an ' between g and s "ends" the first apostrophe and the rest of the $products_name is not a string value in executed query.

You should use prepared statements instead. You should also google "sql injection attack" for the vulnerabilities of your current code.

EDIT: you could use either

I'd personally prefer PDO. For reasons see mysqli or PDO - what are the pros and cons?.

Upvotes: 1

Milos911
Milos911

Reputation: 433

You need to use mysql_real_escape_string() on $product name. the " ' " is reason for that error, and mysql_real_escape_string() will fix that. You should always filter all variables before sending them to mysql, otherwise you are risking mysql injection trough malicious sql codel. mysql_real_escape_string() on php.net

Upvotes: 2

Related Questions