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?

-------------Problems Reply------------

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

  • mysqli: http://php.net/manual/en/mysqli.prepare.php
  • PDO: http://php.net/manual/en/pdo.prepared-statements.php

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

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

Category:mysql Views:0 Time:2011-10-31
Tags: mysql sql

Related post

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.139 (s). 11 q(s)