pg_prepare（）准备好的语句（不是PDO）会阻止SQL注入吗？ [英] Does pg_prepare() prepared statement (not PDO) prevent SQL-Injection?
问题描述
我正在使用的目标系统中不支持PDO ，尽管我寻求在上使用 PHP 5.1.x 防止SQL注入的解决方案PostGres-DB 8.2 + 。目前没有机会切换到PDO。
PDO ist not supported in target system I'm working on and though I seek a solution for preventing SQL-Injection using PHP 5.1.x on a PostGres-DB 8.2+. There is at the moment no chance of switching to PDO.
目前，我的解决方案是pg_prepare编写的语句：
My solution at the moment is pg_prepare-prepared statement:
// Trying to prevent SQL-Injection
$query = 'SELECT * FROM user WHERE login=$1 and password=md5($2)';
$result = pg_prepare($dbconn, "", $query);
$result = pg_execute($dbconn, "", array($_POST["user"], $_POST["password"]));
if (pg_num_rows($result) < 1) {
die ("failure");
}
但是pg_prepare文档缺少重要信息：
它说明以后的使用情况
pg_prepare（）创建一个准备好的语句，供以后使用
pg_execute（）或pg_send_execute（）来执行。[...]
pg_prepare() creates a prepared statement for later execution with pg_execute() or pg_send_execute().[...]
它讲述命名/匿名语句
该函数从以下位置创建一个名为stmtname的准备好的语句
查询字符串，该字符串必须包含一个SQL命令。 stmtname可能是
以创建未命名的语句，在这种情况下，任何现有的
未命名的语句都会被自动替换； [...]
The function creates a prepared statement named stmtname from the query string, which must contain a single SQL command. stmtname may be "" to create an unnamed statement, in which case any pre-existing unnamed statement is automatically replaced;[...]
它讲述了类型转换
与pg_prepare（）也可以通过执行SQL PREPARE语句的
创建。 （但是pg_prepare（）更灵活
，因为它不需要预先指定参数类型。）此外，
尽管没有PHP函数可删除已准备好的语句，但
是SQL可以将DEALLOCATE语句用于该目的。
Prepared statements for use with pg_prepare() can also be created by executing SQL PREPARE statements. (But pg_prepare() is more flexible since it does not require parameter types to be pre-specified.) Also, although there is no PHP function for deleting a prepared statement, the SQL DEALLOCATE statement can be used for that purpose.
但它不能告诉您，这种准备好的语句的实现是否安全SQL注入
*几乎所有此安全问题所涉及的注释均涉及PDO解决方案，在文档中注意到驱动程序阻止了SQL注入。但是，如果一个简单的解决方案是pg_prepare，那么我现在将使用pg_prepare。*
*Nearly all comments by this security question refers to the PDO-solution, where in documentation is noticed that the driver prevents SQL-injection. But if an easy solution may be pg_prepare, I would use pg_prepare at the moment.*
感谢您提供的最佳实践解决方案的重要信息。
Thanks for this important information of maybe a best practice solution.
编辑（标记为解决方案之后）：
感谢您的启发！
EDIT (after marked as solution): Thanks for very enlightening answers!
- 我将Frank Heikens的解决方案标记为最佳答案，因为它解释了SQL注入中的重要点。程序员可能会使用准备好的状态表，但是错误地可能仍然存在SQL注入不足！
- 除了弗兰克·海肯斯（Frank Heikens）的回答外，hoppa还显示了使用pg_prepare /阻止了SQL注入pg_query_params。
- 现在将对
pg_query_params使用经过优化的代码（感谢Milen A. Radev） - 和
pg_escape_string（）作为替代（感谢混蛋）
- I marked the solution of Frank Heikens as best answer, cause it explains an important point in SQL-injection. A programmer may use prepared statemtents, but the SQL-injection-lack may still be there by mistake!
- Aside from Frank Heikens answer, hoppa shows that the SQL-injection is prevented using pg_prepare/pg_query_params. Thanks though.
- Will now use an optimized code with
pg_query_params(thanks to Milen A. Radev) - And
pg_escape_string()as alternative when it comes to it (thanks to halfer)
所有答案都很有帮助：）
All answers are helpfully :)
// Trying to prevent SQL-Injection (**updated**)
$sql_query = 'SELECT * FROM user WHERE login=$1 and password=md5($2);';
$result = pg_query_params($dbconn_login, $sql_query, array($_POST["user"], $_POST["password"]));
if (pg_num_rows($result) < 1) {
die('failure');
}
推荐答案
预先准备好的语句对SQL注入，因为没有人可以在准备好之后更改查询计划。但是，如果您的语句已经被破坏，您仍然会遭受SQL注入：
A prepared statement is safe from SQL injection because nobody can change the queryplan after it's prepared. But, if your statement is already compromised, you still suffer from SQL injection:
<?php
// how NOT to construct your SQL....
$query = 'SELECT * FROM user WHERE login=$1 and password=md5($2) LIMIT '. $_POST['limit']; -- injection!
$result = pg_prepare($dbconn, "", $query);
$result = pg_execute($dbconn, "", array($_POST["user"], $_POST["password"]));
if (pg_num_rows($result) < 1) {
die ("failure");
}
?>
这篇关于pg_prepare（）准备好的语句（不是PDO）会阻止SQL注入吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
