带有预准备语句的原始插入查询 [英] Raw insert query with prepared statements
问题描述
我在 app / Lib
中有一个需要存储日志的帮助程序库,因此我正在尝试 DboSource :: rawQuery()
。文档非常模糊:
I have a helper library in app/Lib
that needs to store a log so I'm trying DboSource::rawQuery()
. Docs are pretty vague:
/**
* Executes given SQL statement.
*
* @param string $sql SQL statement
* @param array $params Additional options for the query.
* @return bool
*/
public function rawQuery($sql, $params = array()) {
$this->took = $this->numRows = false;
return $this->execute($sql, $params);
}
...以及我在网上发现的任何示例都假装不存在SQL注入。无论我尝试使用哪种语法:
... and any example I find online pretends that SQL injection does not exist. No matter what syntax I try:
public function log (DataSource $db) {
$sql = 'INSERT INTO log (foo, bar)
VALUES (:foo, :bar)';
$params = array(
'foo' => 'One',
'bar' => 'Two',
);
$db->rawQuery($sql, $params);
}
...我总是会收到这样的数据库错误:
... I always get database errors like this:
SQLSTATE [42000]:[Microsoft] [SQL Server的ODBC驱动程序11] [SQL Server]':'附近的语法不正确。
SQLSTATE[42000]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near ':'.
我也尝试过位置占位符(?
)甚至是类似PDO的参数( PDO :: PARAM_STR
以及所有内容)。
I've also tried positional place-holders (?
) and even PDO-like params (with PDO::PARAM_STR
and all the stuff).
在CakePHP /中运行参数化原始查询的语法是什么2.5.5针对SQL Server?
What's the syntax to run a parametrised raw query in CakePHP/2.5.5 against SQL Server?
推荐答案
答案(显然)是:无。
在 Sqlserver :: _ execute()
的源代码,我们可以阅读:
In the source code for Sqlserver::_execute()
we can read:
/**
* Executes given SQL statement.
*
* @param string $sql SQL statement
* @param array $params list of params to be bound to query (supported only in select)
* @param array $prepareOptions Options to be used in the prepare statement
* @return mixed PDOStatement if query executes with no problem, true as the result of a successful, false on error
* query returning no rows, such as a CREATE statement, false otherwise
* @throws PDOException
*/
所以您只能在阅读时使用准备好的陈述,而不能在写作中使用:-!
您需要回到2000年代早期并转义:
You need to get back to the early 2000s and escape:
public function log (DataSource $db) {
$sql = sprintf('INSERT INTO log (foo, bar)
VALUES (%s, %s)',
$db->value('foo'),
$db->value('bar')
);
$db->rawQuery($sql);
}
这篇关于带有预准备语句的原始插入查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!