防止将查询生成器与 DB::raw() 组合在一起的查询进行 SQL 注入 [英] Prevent SQL injection for queries that combine the query builder with DB::raw()

问题描述

在 Laravel 4 中，我想保护一些复杂的数据库查询免受 SQL 注入.这些查询使用查询生成器和 DB::raw() 的组合.这是一个简化的例子:

In Laravel 4, I want to protect some complex database queries from SQL injection. These queries use a combination of the query builder and DB::raw(). Here is a simplified example:

$field = 'email';
$user = DB::table('users')->select(DB::raw("$field as foo"))->whereId(1)->get();

我已阅读 Chris Fidao 的教程，可以将绑定数组传递给select() 方法，因此可以通过使用准备好的语句正确地防止 SQL 注入.例如:

I've read Chris Fidao's tutorial that it is possible to pass an array of bindings to the select() method, and therefore prevent SQL injection correctly, by using prepared statements. For example:

$results = DB::select(DB::raw("SELECT :field FROM users WHERE id=1"), 
               ['field' => $field]
           ));

这可行，但该示例将整个查询放入原始语句中.它没有将查询生成器与 DB::raw() 结合起来.当我使用第一个示例尝试类似的事情时:

This works, but the example puts the entire query into a raw statement. It doesn't combine the query builder with DB::raw(). When I try something similar using the first example:

$field = 'email';
$user = DB::table('users')->select(DB::raw("$field as foo"), ['field' => $field])
             ->whereId(1)->get();

... 然后我得到一个错误:strtolower() 期望参数 1 是字符串，给定数组

... then I get an error: strtolower() expects parameter 1 to be string, array given

对于将查询构建器与 DB::raw() 结合起来的查询，防止 SQL 注入的正确方法是什么?

推荐答案

我发现查询生成器有一个名为 setBindings() 的方法，在这种情况下很有用:

I discovered the query builder has a method called setBindings() that can be useful in this instance:

$field = 'email';
$id = 1;
$user = DB::table('users')->select(DB::raw(":field as foo"))
        ->addSelect('email')
        ->whereId(DB::raw(":id"))
        ->setBindings(['field' => $field, 'id' => $id])
        ->get();

这篇关于防止将查询生成器与 DB::raw() 组合在一起的查询进行 SQL 注入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！