sql 命令为什么显示错误? [英] 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),'%')";

某些页面运行正常，即使没有相关项目.但是当 $products_name 是 A Bug's Life "Multi Pak" Special 2003 Collectors Edition 时.它显示一个错误:

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?

推荐答案

$products_name 的示例值包含 ' 符号，因此 sql 查询混乱.

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) ...

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

请注意，g 和 s 之间的 ' 会结束"第一个撇号，而 $products_name 的其余部分在执行的查询中不是字符串值.

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.

您应该改用准备好的语句.您还应该针对当前代码的漏洞在 google 上搜索sql 注入攻击".

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

你可以使用任何一个

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

我个人更喜欢 PDO.原因参见 mysqli 或 PDO - 优缺点是什么?.

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

这篇关于sql 命令为什么显示错误?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！