带有预准备语句的原始插入查询 [英] 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屋！