教义原始 sql 和准备好的语句 [英] Doctrine raw sql and prepared statements

问题描述

我有一个使用准备好的语句的 Doctrine_RawSql 查询.但是，在生成 SQL 查询时，它们似乎被忽略了.但是如果我省略了标记值，我会得到一个关于绑定变量数量不匹配的异常(所以它至少试图将它们分入).

I've got a Doctrine_RawSql query using prepared statements. However, they seem to get ignored when the SQL query is generated. But If I leave out the token values, I get an exception about number of bound variables not matching (so it's at least trying to sub them in).

如果我在内联包含这些值，Doctrine 是否在幕后做任何事情来防止 SQL 注入?

If I include these values inline, is Doctrine doing anything behind the scenes to prevent SQL injection?

这是我的代码:

public function sortedPhotogsByLocation($location)
{
    $q = new Doctrine_RawSql();
    $result = $q->select('{p.*}')
            ->from('photographers p')
            ->addComponent('p', 'Photographer')
            ->where('p.city_id = ?', $location->id)
            ->orderBy('CASE WHEN p.lname < "?%" THEN 1 ELSE 0 END, p.lname ASC', $location->photographer_sort)
            ->execute();
    return $result;
}

这提供了以下 SQL 输出:

This provides the following SQL output:

  SELECT *  
  FROM photographers p 
  WHERE p.city_id = ? 
  ORDER BY 
    CASE WHEN p.lname < "?%" THEN 1 ELSE 0 END, p.lname 
  ASC

$location 上的属性设置正确.如果我对参数进行硬编码:

The properties on $location are being set properly. If I hardcode the parameters:

->where('p.city_id = ?', 5)

我遇到了同样的问题，令牌没有被替换.

I encounter the same problem with the tokens not being replaced.

推荐答案

我对Doctrine_RawSql并不完全熟悉，但是占位符应该是单独的，而不是?％"，只是?并在您传递的变量上添加 % .看看 示例 #6.

I'm not entirely familiar with Doctrine_RawSql, but a placeholder should be by itself, not "?%", just ? and add the % on the variable you are passing. Take a look at example #6.

这篇关于教义原始 sql 和准备好的语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！