Rails Brakeman警告SQL注入 [英] Rails brakeman warning of sql injection
问题描述
我的模型中有一个示波器:
I've got a scope in my model :
scope :assigned_to_user, ->(user) {
task_table = UserTask.table_name
joins("INNER JOIN #{task_table}
ON #{task_table}.user_id = #{user.id}
AND (#{task_table}.type_id = #{table_name}.type_id)
AND (#{task_table}.manager_id = #{table_name}.manager_id)
")
}
因此,在运行刹车员报告后,我会收到此警告:
So after running brakeman report I get this warning :
assigned_to_user | SQL Injection | Possible
所以我尝试了以下方法:
So I tried the following :
scope :assigned_to_user, ->(user) {
task_table = UserTask.table_name
joins(ActiveRecord::Base::sanitize("INNER JOIN #{task_table}
ON #{task_table}.user_id = #{user.id}
AND (#{task_table}.type_id = #{table_name}.type_id)
AND (#{task_table}.manager_id = #{table_name}.manager_id)
"))
}
这对我不起作用,因为它将'
(撇号)添加到sql的前面和后面.因此,当我将其用作返回一些结果的查询的一部分并且应用此范围时,它将生成错误的sql.
This doesn't work for me because it adds '
(apostrophe) to the front and back of the sql. So when I use this as a part of query which returns some results and I apply this scope it generates the incorrect sql.
我也尝试过:
scope :assigned_to_user, ->(user) {
task_table = UserTask.table_name
joins("INNER JOIN #{task_table}
ON #{task_table}.user_id = ?
AND (#{task_table}.type_id = #{table_name}.type_id)
AND (#{task_table}.manager_id = #{table_name}.manager_id)
", user.id)
}
甚至不构建该语句.并尝试了其他一些不起作用,甚至不值得一提的东西.有人知道如何解决这个问题吗?
Doesn't even build the statement. And tried couple of other stuff which didn't work and not even worth mentioning. Does anybody have idea how to fix this?
推荐答案
经过某种类型的研究,我将使用这里的内容.
有一种称为sanitize_sql_array
的方法( ref ),您可以使用它来通过向其传递sql字符串和替换值来转义语句:
After some kind of research here is what I would use.
There is a method called sanitize_sql_array
(ref), you can use it to escape statements by passing an sql string and replacement values to it like:
sanitize_sql_array(['user_id = :user_id', user_id: 5])
# => "user_id = 5"
如果我们将表名传递给此方法,它也会对其进行转义,但会将ActiveRecord::Base.connection
对象的quote
方法应用于一个值,该值用于转义变量,但不转义表名.也许有时它可以工作,但是当我使用PostrgreSQL时,它对我失败了,因为quote
方法使用单引号,但是PostgreSQL需要对表名使用双引号.
If we'd pass a table name to this method it will also escape it, but will apply a quote
method of ActiveRecord::Base.connection
object on a value, which is used to escape variables, but not table names. Maybe sometimes it will work, but it failed for me when I was using PostrgreSQL, because quote
method uses single quotes, but PostgreSQL requires double-quotation for table names.
sanitize_sql_array([
'INNER JOIN :table_name ON :table_name.user_id = :user_id',
{ table_name: 'users', user_id: 5 }
])
# => "INNER JOIN 'users' ON 'users'.user_id = 5"
connection
对象还具有方法quote_table_name
,可以将其单独应用于表名,以确保它们被转义+使用sanitize_sql_array
作为用户ID.
connection
object also has a method quote_table_name
, which could be separately applied on table names, to make sure that they are escaped + use sanitize_sql_array
for user id.
scope :assigned_to_user, -> (user) {
task_table = connection.quote_table_name(UserTask.table_name)
current_table = connection.quote_table_name(table_name)
sanitized_sql = sanitize_sql_array([
"INNER JOIN #{task_table}
ON #{task_table}.user_id = :user_id
AND (#{task_table}.type_id = #{current_table}.type_id)
AND (#{task_table}.manager_id = #{current_table}.manager_id)",
{ user_id: user.id }
])
joins(sanitized_sql)
}
或者您实际上可以在user.id
上使用sanitize
而不是将所有内容包装在sanitize_sql_array
方法调用(#{sanitize(user.id)}
)中.
Or you could actually just use sanitize
on user.id
instead of wrapping everything in sanitize_sql_array
method call (#{sanitize(user.id)}
).
顺便说一句,Brakeman不会显示任何警告,因为查询已移至变量. Brakeman照原样解析您的代码,并且它不知道变量及其内容.因此,所有这些事情只是为了确保自己能够逃避一切.
By the way, Brakeman won't already show any warnings, because query has been moved to a variable. Brakeman literally parses your code as is and it does not know about a variable and it's content. So all this thing is just to make yourself sure that everything is being escaped.
仅需关闭Brakeman,您就可以将查询移至变量:
Just to shut up Brakeman you could just move a query to a variable:
scope :assigned_to_user, -> (user) {
task_table = UserTask.table_name
query = "INNER JOIN #{task_table}
ON #{task_table}.user_id = #{user.id}
AND (#{task_table}.type_id = #{table_name}.type_id)
AND (#{task_table}.manager_id = #{table_name}.manager_id)"
joins(query)
}
这篇关于Rails Brakeman警告SQL注入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!