php 如何在 pdo->query 中添加变量值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9949425/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to add variable values inside pdo->query
提问by BobRock
I want to upgrade my current code which is constantly sql injected with PDO.
我想升级我当前的代码，该代码不断注入 PDO sql。
Currently I'm stuck with using a variable inside a PDO query.
目前我坚持在 PDO 查询中使用变量。
If I have two arguments like this
如果我有两个这样的论点
$rowsPerPage = 3;
// by default we show first page
$pageNum = 1;
if (isset($_GET['page'])) {
$pageNum = mysql_real_escape_string($_GET['page']);
}
$offset = ($pageNum - 1) * $rowsPerPage;
And I have query like this
我有这样的查询
$STH = $DBH->query("SELECT News.ID, LEFT(NewsText,650), Title, AID, Date, imgID," .
"DATE_FORMAT(Date, '%d.%m.%Y.') as formated_date " .
"FROM News, Categories, NewsCheck WHERE Name LIKE '%News - Block%' AND CID=Categories.ID AND JID=News.ID ".
"ORDER BY `Date` DESC LIMIT $offset, $rowsPerPage");
PDO reports an error in last line of the query ORDER BY
When I replace these line with
"ORDER BY Date DESC LIMIT3,3");everything work.
PDO 在查询 ORDER BY 的最后一行报告错误，当我将这些行替换为
"ORDER BY Date DESC LIMIT3,3");一切正常时。
So how to add variable values inside PDO::query ?
那么如何在 PDO::query 中添加变量值呢？
Updated: Thanks to answer bellow I have updated my code like this
更新：感谢下面的回答我已经更新了我的代码
$STH = $DBH->prepare("SELECT News.ID, LEFT(NewsText,650), Title, AID, Date, imgID," .
"DATE_FORMAT(Date, '%d.%m.%Y.') as formated_date " .
"FROM News, Categories, NewsCheck WHERE Name LIKE '%News - Block%' AND CID=Categories.ID AND JID=News.ID ".
"ORDER BY `Date` DESC LIMIT :offset, :rowsPerPage;");
$STH->bindParam(':offset', $offset, PDO::PARAM_STR);
$STH->bindParam(':rowsPerPage', $rowsPerPage, PDO::PARAM_STR);
$STH->execute();
But error occured:
但是出现了错误：
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 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', '3'' at line 1' in /pdo/test.php:42 Stack trace: #0 /pdo/test.php(42): PDOStatement->execute() #1 {main} thrown in /pdo/test..
致命错误：未捕获的异常 'PDOException' 带有消息 'SQLSTATE[42000]：语法错误或访问冲突：1064 您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以获取在 /pdo/test.php:42 Stack trace: #0 /pdo/test 中第 1 行的“-3”、“3”附近使用的正确语法。 php(42): PDOStatement->execute() #1 {main} 被抛出到 /pdo/test..
Second UpdateChanged from PARAM_STR TO PARAM_INT like this
第二次更新从 PARAM_STR 更改为 PARAM_INT 像这样
$STH->bindParam(':offset', $offset, PDO::PARAM_INT);
$STH->bindParam(':rowsPerPage', $rowsPerPage, PDO::PARAM_INT);
Everything works.
一切正常。
回答by Pierre-Olivier
You want to use prepared statementsand query parameterslike the following:
您想使用准备好的语句和查询参数，如下所示：
$sth = $dbh->prepare('SELECT your_column FROM your_table WHERE column < :parameter');
$sth->bindParam(':parameter', $your_variable, PDO::PARAM_STR);
$sth->execute();
Using variables directly in your query will not protect you from SQL injections, even if you are using PDO. Parameters are the only good way to prevent them.
直接在查询中使用变量不会保护您免受 SQL 注入，即使您使用的是 PDO。参数是防止它们的唯一好方法。
