Doctrine raw sql和准备语句 [英] Doctrine raw sql and prepared statements

问题描述

我有一个Doctrine_RawSql查询使用准备的语句。但是，当生成SQL查询时，它们似乎被忽略。但是，如果我省略了令牌值，我会得到一个关于不匹配的绑定变量的数量的异常（所以至少要试图分配它们）。

如果我包括这些值是内联的，Doctrine在幕后做任何事情以防止SQL注入？

这是我的代码：

  public function sortedPhotogsByLocation（$ location）
 {
 $ q = new Doctrine_RawSql（）; 
 $ result = $ q-> select（'{p。*}'）
  - > from（'photographers p'）
  - > addComponent（'p'摄影师）
  - > where（'p.city_id =？'，$ location-> id）
  - > orderBy（'CASE WHEN p.lname<？％ ELSE 0 END，p.lname ASC'，$ location-> photographer_sort）
  - > execute（）; 
 return $ result; 
} 
  

这提供了以下SQL输出：

  SELECT * 
 FROM摄影师p 
 WHERE p.city_id =？ 
 ORDER BY 
 CASE WHEN p.lname< ？％THEN 1 ELSE 0 END，p.lname 
 ASC 
  

编辑：正在设置 $ location 上的属性。如果我硬编码参数：

   - > where（'p.city_id =？'，5）
  

我遇到与令牌不被替换相同的问题。

解决方案

我不完全熟悉Doctrine_RawSql，但占位符本身应该是？，而不是？并将％添加到您要传递的变量上。看看示例＃6 。

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).

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

Here's my code:

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;
}

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

EDIT: 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.

解决方案

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.

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