laravel 防止将查询构建器与 DB::raw() 结合的查询的 SQL 注入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26670933/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Prevent SQL injection for queries that combine the query builder with DB::raw()
提问by mtmacdonald
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:
在 Laravel 4 中，我想保护一些复杂的数据库查询免受 SQL 注入。这些查询使用查询构建器和 DB::raw() 的组合。这是一个简化的示例：
$field = 'email';
$user = DB::table('users')->select(DB::raw("$field as foo"))->whereId(1)->get();
I've read Chris Fidao's tutorialthat 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:
我已经阅读了Chris Fidao 的教程，它可以通过使用准备好的语句将绑定数组传递给 select() 方法，从而正确防止 SQL 注入。例如：
$results = DB::select(DB::raw("SELECT :field FROM users WHERE id=1"),
['field' => $field]
));
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:
这有效，但该示例将整个查询放入原始语句中。它不会将查询构建器与 DB::raw() 结合起来。当我使用第一个示例尝试类似的操作时：
$field = 'email';
$user = DB::table('users')->select(DB::raw("$field as foo"), ['field' => $field])
->whereId(1)->get();
... then I get an error: strtolower() expects parameter 1 to be string, array given
...然后我得到一个错误：strtolower() 期望参数 1 是字符串，数组给定
What is the correct way to prevent SQL injection for queries that combine the query builder with DB::raw()?
对于将查询构建器与 DB::raw() 相结合的查询，防止 SQL 注入的正确方法是什么？
采纳答案by mtmacdonald
I discovered the query builder has a method called setBindings()that can be useful in this instance:
我发现查询构建器有一个名为setBindings()的方法，在这个实例中很有用：
$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();
回答by fideloper
Eloquent uses PDO under the hood to sanitize items. It won't sanitize items added to SELECT statements.
Eloquent 在幕后使用 PDO 来消毒项目。它不会清理添加到 SELECT 语句的项目。
The mysqli_real_escape_stringmethod is still useful for sanitizing SQL strings, however.
然而，mysqli_real_escape_string方法对于清理SQL 字符串仍然很有用。
Consider also (or instead) keeping an array of valid field names from the users table and checking against that to ensure there isn't an invalid value being used.
还考虑（或替代）从 users 表中保留一组有效字段名称并检查它以确保没有使用无效值。
$allowedFields = ['username', 'created_at'];
if( ! in_array($field, $allowedFields) )
{
throw new \Exception('Given field not allowed or invalid');
}
$user = DB::table('users')
->select(DB::raw("$field as foo"))
->whereId(1)->get();
