ZF2清理数据库查询的变量 [英] ZF2 sanitize variables for DB queries

问题描述

在Zend Framework 2中进行数据库查询时，应该如何清除用户提交的值?例如，以下SQL中的$ id

In making database queries in Zend Framework 2, how should I be sanitizing user submitted values? For example, $id in the following SQL

$this->tableGateway->adapter->query(
  "UPDATE comments SET spam_votes = spam_votes + 1 WHERE comment_id = '$id'",
  \Zend\Db\Adapter\Adapter::QUERY_MODE_EXECUTE
);

推荐答案

您可以在执行时传递参数.

You can pass parameters when you execute..

 $statement = $this->getAdapter()->query("Select * from test WHERE id = ?");
 $result = $statement->execute(array(99));

 $resultSet = new ResultSet;
 $resultSet->initialize($result);

您还可以将它们直接传递给查询方法

You can also pass them directly to the query method

 $statement = $this->getAdapter()->query(
    "Select * from test WHERE id = ?", 
    array(99)
 );
 $result = $statement->execute();

 $resultSet = new ResultSet;
 $resultSet->initialize($result);

两者都将生成查询从测试WHERE id ='99'中选择*"

Both will produce the query "Select * from test WHERE id = '99'"

如果要使用命名参数:

$statement = $this->getAdapter()->query("Select * from test WHERE id = :id");
$result = $statement->execute(array(
    ':id' => 99
));

$resultSet = new ResultSet;
$resultSet->initialize($result);

如果要引用表/字段名称等

If you want to quote your table/field names etc:

$tablename = $adapter->platform->quoteIdentifier('tablename');

$statement = $this->getAdapter()->query("Select * from {$tablename} WHERE id = :id");
$result = $statement->execute(array(
    ':id' => 99
));

这篇关于ZF2清理数据库查询的变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！