Rails SQL 注入:这段代码有多脆弱? [英] Rails SQL Injection: How vulnerable is this code?

问题描述

我正在尝试了解 SQL 注入.似乎人们可以变得非常有创意.这让我想知道我正在制作的基于搜索的 Rails 网络应用程序.

I'm trying to understand SQL Injection. It seems like people can get pretty creative. Which gets me wondering about my search-based rails webapp I'm making.

假设我只是将用户输入的信息直接输入到我的 SQL 查询的where"语句中.允许这样做会对我的数据库造成多大的损害?

Suppose I just fed user-entered information directly into the "where" statement of my SQL query. How much damage could be done to my database by allowing this?

def self.search(search)
  if search
    includes(:hobbies, :addresses).where(search)
  else
    self.all
  end

所以基本上，用户在主页上的搜索栏中输入的任何内容都会直接输入到where"语句中.

So basically, whatever the user types into the search bar on the home page gets fed straight into that 'where' statement.

有效搜索"的一个例子是:

An example of a valid 'search' would be:

"hobby LIKE ? OR (gender LIKE ? AND hobby LIKE ?)", "golf", "male", "polo"

它仅限于where"语句的上下文这一事实是否提供任何形式的辩护?他们还能以某种方式执行删除或创建操作吗?

Does the fact that it's limited to the context of a 'where' statement provide any sort of defense? Could they still somehow perform delete or create operations?

当我查看本教程时，我没有看到执行删除或创建的直接方法where 子句之外的动作.如果我的数据库不包含我不愿意从有效搜索结果中显示的信息，并且没有用户帐户或管理员权限之类的东西，那么这里的真正危险是什么?

When I look at this tutorial, I don't see a straightforward way to perform a deletion or creation action out of the where clause. If my database contains no information that I'm not willing to display from a valid search result, and there's no such thing as user accounts or admin privileges, what's really the danger here?

推荐答案

我从这里的另一篇文章中摘取了这个:在 rails 中清理用户输入的最佳方法

I took this from another post here: Best way to go about sanitizing user input in rails

TL;博士关于用户输入和查询:确保始终使用活动记录查询方法(如.where)，避免使用字符串插值传递参数；将它们作为散列参数值或作为参数化语句传递.

TL;DR Regarding user input and queries: Make sure to always use the active record query methods (such as .where), and avoid passing parameters using string interpolation; pass them as hash parameter values, or as parameterized statements.

关于呈现可能不安全的用户生成的 html/javascript 内容:从 Rails 3 开始，html/javascript 文本会自动正确转义，以便在页面上显示为纯文本，而不是解释为 html/javascript，因此您不必' 不需要明确清理(或使用 <%= h(potentially_unsafe_user_generated_content)%>

Regarding rendering potentially unsafe user-generated html / javascript content: As of Rails 3, html/javascript text is automatically properly escaped so that it appears as plain text on the page, rather than interpreted as html/javascript, so you don't need to explicitly sanitize (or use <%= h(potentially_unsafe_user_generated_content)%>

如果我理解正确，只要您正确使用活动记录查询方法，您就无需担心以这种方式清理数据.例如:

If I understand you correctly, you don't need to worry about sanitizing data in this manner, as long as you use the active record query methods correctly. For example:

假设我们的参数映射如下所示，因为恶意用户将以下字符串输入到 user_name 字段中:

Lets say our parameter map looks like this, as a result of a malicious user inputting the following string into the user_name field:

:user_name => "(从用户限制 1 中选择 user_name)"不好的方式(不要这样做):

:user_name => "(select user_name from users limit 1)" The bad way (don't do this):

Users.where("user_name = #{params[:id}") # 字符串插值在这里不好结果查询将如下所示:

Users.where("user_name = #{params[:id}") # string interpolation is bad here The resulting query would look like:

SELECT users.* FROM users WHERE (user_name = (select user_name from users limit 1))以这种方式直接插入字符串会将带有键 :user_name 的参数值的文字内容放入查询中而无需清理.您可能知道，恶意用户的输入被视为普通的ol SQL"，其危险性非常明显.

SELECT users.* FROM users WHERE (user_name = (select user_name from users limit 1)) Direct string interpolation in this manner will place the literal contents of the parameter value with key :user_name into the query without sanitization. As you probably know, the malicious user's input is treated as plain 'ol SQL, and the danger is pretty clear.

好方法(这样做):

Users.where(id: params[:id]) # 散列参数或

Users.where(id: params[:id]) # hash parameters OR

Users.where("id = ?", params[:id]) # 参数化语句结果查询将如下所示:

Users.where("id = ?", params[:id]) # parameterized statement The resulting query would look like:

SELECT users.* FROM users WHERE user_name = '(select user_name from users limit 1)'如您所见，Rails 实际上会为您清理它，只要您将参数作为散列或方法参数(取决于您使用的查询方法)传入.

SELECT users.* FROM users WHERE user_name = '(select user_name from users limit 1)' So as you can see, Rails in fact sanitizes it for you, so long as you pass the parameter in as a hash, or method parameter (depending on which query method you're using).

在创建新模型记录时清理数据的情况并不真正适用，因为 new 或 create 方法需要值的散列.即使您尝试将不安全的 SQL 代码注入散列，散列的值也会被视为纯字符串，例如:

The case for sanitization of data on creating new model records doesn't really apply, as the new or create methods are expecting a hash of values. Even if you attempt to inject unsafe SQL code into the hash, the values of the hash are treated as plain strings, for example:

User.create(:user_name=>"bobby 表); 删除表用户;")查询结果:

User.create(:user_name=>"bobby tables); drop table users;") Results in the query:

INSERT INTO users (user_name) VALUES ('bobby tables);删除表用户;')所以，和上面的情况一样.

INSERT INTO users (user_name) VALUES ('bobby tables); drop table users;') So, same situation as above.

希望有帮助.如果我遗漏或误解了什么，请告诉我.

I hope that helps. Let me know if I've missed or misunderstood anything.

编辑关于转义 html 和 javascript，简短版本是 ERB 为您转义"您的字符串内容，以便将其视为纯文本.如果您真的需要，您可以通过执行 your_string_content.html_safe 将其视为 html.

Edit Regarding escaping html and javascript, the short version is that ERB "escapes" your string content for you so that it is treated as plain text. You can have it treated like html if you really want, by doing your_string_content.html_safe.

然而，简单地做类似 <%= your_string_content %> 的事情是完全安全的.内容被视为页面上的字符串.事实上，如果您使用 Chrome Developer Tools 或 Firebug 检查 DOM，您实际上应该看到该字符串周围的引号.

However, simply doing something like <%= your_string_content %> is perfectly safe. The content is treated as a string on the page. In fact, if you examine the DOM using Chrome Developer Tools or Firebug, you should in fact see quotes around that string.

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