Rails 5 SQL注入 [英] Rails 5 SQL Injection

问题描述

我已经在各种SO线程，指南等上阅读了一段时间，但是所有答案都是矛盾和矛盾的。

似乎

• 消毒
  
  
  
  
  • 消毒 c $ c>
  
  
  • sanitize_conditions
  
  
  • sanitize_sql
  
  
  • sanitize_sql_array
  
  
  • sanitize_sql_for_assignment
  
  
  • sanitize_sql_for_conditions
  
  
  • sanitize_sql_hash
  
  
  • sanitize_sql_hash_for_assignment
  
  
  • sanitize_sql_hash_for_conditions
  
  
  • sanitize_sql_like
  
  
  
  
  

  我正在尝试编写一个原始查询适配器，该适配器可让我运行原始的Postgres查询，但允许我插入来自危险用户输入的自己的参数。

  
  
  

  我不能在这少数情况下使用AR，因为因为我正在执行复杂的经/纬度计算，聚合函数，复杂的子查询等。

  
  
  

  到目前为止，我已经尝试了两种方法：

  
  
  

  方法1

  
  
  

  对于这种方法，我不知道是否可以消毒是上述方法中的最佳选择，或者在100％的情况下都可以使用...（我仅使用Postgres）

    class RawQuery 
   
   def exec（prepared，* params）
   prepare = query.dup 
   params.flatten.each_with_index do | p，i | 
   prepare.gsub！（ $＃{i + 1}，ActiveRecord :: Base.sanitize（p））
  结束
   ActiveRecord :: Base.connection.exec_query（已准备）
  结束
   
  结束
    

  常用用法示例（通常不会当然是这样简单，或者我只是使用AR）：

  
  
  

  RawQuery.new.exec（'SELECT * FROM users WHERE name = $ 1'，params [：name]）

  
  
  

  此外，似乎消毒了代表到 quote 。但是根据此SO帖子，它说仅用单引号将内容包装起来并不安全...所以我不知道。

  
  
  

  方法2

  
  
  

  我不确定这是否与安全，但似乎使用了实际的PG准备好的功能（我认为它是100％安全的）。唯一的问题是Rails不会将其打印到控制台，也不会包含SQL执行时间（这会破坏我的分析工具）。

    class RawQuery 
   
   def prepare（query，* params）
   name = raw_query _＃{SecureRandom.uuid.gsub（'-'，``）}} 
   connection = ActiveRecord :: Base.connection.raw_connection 
   connection.prepare（名称，查询）
   connection.exec_prepared（名称，参数）
  结束
   
  结束
    

  使用相同的方式：

  
  
  

  RawQuery.new.prepare（'SELECT * FROM users WHERE name = $ 1'，params [：name]）

  
  
  

  ---

  
  
  

  一种方法比另一种方法更安全吗？两者都是100％安全吗？

  
  
  

  我的应用程序总是超出Rails的SQL功能范围，我需要一个很好的lib，我可以将其包含在所有项目中我知道这是完全安全的。

  解决方案

  使用 quote 是安全的。我在链接到的页面上阅读了答案，我看不到有人说报价是不安全的。我看到您有关使用引号的问题。是的，如果您只在字符串两边加上引号，那是不安全的，例如：

    q = SELECT * FROM users where email ='＃{params [：email]}' 
    

  但是使用 quote （该方法）很好：

    q =选择*来自电子邮件地址为＃{ connection.quote（params [：email]）} 
    

  您可以在控制台中试一下尽最大努力打破它，但我认为您将无法做到：

    2.3.3：003> ActiveRecord :: Base.connection.quote（ f’oo）
   => 'f''oo' 
    

  如果成功，我相信Rails团队想知道（私下）！但是正如您所看到的， quote 方法的作用不只是在开头和结尾加上引号。

  
  
  

  也，因为您说的是正在寻找权威引用，所以源代码本身的注释表明，引用用户输入是这些功能的预期目的：

  
  
  

  https://github.com/ rails / rails / blob / 2471e6391dfe71cfbb8621bdf573729d961d3209 / activerecord / lib / active_record / connection_adapters / abstract / quoting.rb＃L6-L13

   ＃引用该列的值以帮助防止
  ＃{SQL注入攻击} [http://en.wikipedia.org/wiki/SQL_injection]。 
   def quote（value）
    

  https://github.com/rails/rails/blob/0f1d0b1b100ab105d /lib/active_record/connection_adapters/postgresql/quoting.rb#L17-L20

   ＃引用用于SQL输入。 
   def quote_string（s）＃：nodoc：
    

  （请注意，我显示的是 quote_string 作为注释，但是您可能应该使用 quote ，它试图找出数据类型并做适当的事情。）

  
  
  

  顺便说一句，这是一个与您类似的问题，我在2014年给出了答案，还有一些替代方法：如何在rails中执行带有动态绑定的原始更新

  
  

  I've read about this for some time now on various SO threads, guides, etc... but all the answers are conflicting and contradictory.

  It seems there's many similar methods, and a lot of the answers say to use a different one.

  • sanitize
  • sanitize_conditions
  • sanitize_sql
  • sanitize_sql_array
  • sanitize_sql_for_assignment
  • sanitize_sql_for_conditions
  • sanitize_sql_hash
  • sanitize_sql_hash_for_assignment
  • sanitize_sql_hash_for_conditions
  • sanitize_sql_like

  I'm trying to write a 'raw query' adapter that lets me run raw Postgres queries, but allowing me to insert my own parameters that come from dangerous user input.

  I can't use AR in these few instances because I'm doing complex lat/long calculations, aggregate functions, complex subqueries, etc.

  So far I have tried 2 approaches:

  Method 1

  For this method, I don't know if sanitize is the best option of the above, or if it will work in 100% of cases... (I'm using Postgres only)

  class RawQuery
  
    def exec(prepared, *params)
      prepared = query.dup
      params.flatten.each_with_index do |p, i|
        prepared.gsub!("$#{i + 1}", ActiveRecord::Base.sanitize(p))
      end
      ActiveRecord::Base.connection.exec_query(prepared)
    end
  
  end
  

  Trivial usage example (normally it wouldn't be this simple of course, or I would just use AR):

  RawQuery.new.exec('SELECT * FROM users WHERE name = $1', params[:name])

  Furthermore it seems that sanitize delegates to quote. But according to this SO post it says simply wrapping things with single quotes isn't secure... so I have no idea.

  Method 2

  I'm not sure if this is just as secure, but it seems to use an actual PG prepared function (which I assume is 100% secure). The only problem is rails doesn't print it out to the console, nor include the SQL execution time (which breaks my profiling tools).

  class RawQuery
  
    def prepare(query, *params)
      name = "raw_query_#{SecureRandom.uuid.gsub('-', '')}"
      connection = ActiveRecord::Base.connection.raw_connection
      connection.prepare(name, query)
      connection.exec_prepared(name, params)
    end
  
  end
  

  Used the same way:

  RawQuery.new.prepare('SELECT * FROM users WHERE name = $1', params[:name])

  ---

  Is one method more secure over another? Are both 100% secure?

  My apps always extend far outside of what Rails is capable of SQL-wise and I need a good lib I can include on all my projects which I know is completely safe.

  解决方案

  Using quote is safe. I read the answers on the page you linked to, and I don't see anyone saying that quote is insecure. I see your question about using "quotes". Yes, if you just put quotes around a string, that is insecure, e.g.:

  q = "SELECT * FROM users where email = '#{params[:email]}'"
  

  But using quote (the method) is fine:

  q = "SELECT * FROM users where email = #{connection.quote(params[:email])}"
  

  You could play around in the console and try your best to break it, but I don't think you'll be able to:

  2.3.3 :003 > ActiveRecord::Base.connection.quote("f''oo")                                                                              
   => "'f''''oo'"
  

  If you succeed, I'm sure the Rails team would like to know (privately)! But as you can see, the quote method does more than stick a quote at the beginning and end.

  Also, since you say you are looking for an authoritative citation, the comments in the source code itself suggest that quoting user inputs is the intended purpose of these functions:

  https://github.com/rails/rails/blob/2471e6391dfe71cfbb8621bdf573729d961d3209/activerecord/lib/active_record/connection_adapters/abstract/quoting.rb#L6-L13

  # Quotes the column value to help prevent
  # {SQL injection attacks}[http://en.wikipedia.org/wiki/SQL_injection].
  def quote(value)
  

  https://github.com/rails/rails/blob/0f1d0b1b5254e3678abaabbebb3362a100c10262/activerecord/lib/active_record/connection_adapters/postgresql/quoting.rb#L17-L20

  # Quotes strings for use in SQL input.
  def quote_string(s) #:nodoc:
  

  (Note I am showing quote_string for the comment, but you should probably use quote, which tries to figure out the data type and do something appropriate.)

  By the way, here is a similar question to yours, with an answer from me in 2014, and some alternatives too: How to execute a raw update sql with dynamic binding in rails

  这篇关于Rails 5 SQL注入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！