直接接受查询字符串中未经清理的输入 参数化查询 SQL 注入

水货CTO入职不到半年犯下低级错误，将公司拖入无底深渊 https://mp.weixin.qq.com/s/wPg3QOe1nKKBE_u4odhBfw

刘志勇、Tina InfoQ 2021-03-03

防止 SQL 注入，在 Rails 文档中有明确的示例说明（https://guides.rubyonrails.org/security.html#sql-injection），甚至示例与该提交中所讨论的代码完全一样，所以这个技术在 Rails 里并不是什么新鲜玩意儿。在 2021 年还能让黑客利用到 SQL 注入漏洞，这很让人不可置信，一位网友在 Reddit 上评论道，“代码将要部署到生产环境中，在合并代码之前难道没有让同行审查吗？如果首席技术官审查了开发人员的代码后还犯这种错误，那么要么 CTO 和工程师都是白痴，要么工程师们在故意欺骗这位白痴。”

而且除了这是一种糟糕的实践之外，现有的每一个代码静态分析工具都会告诉你，这样编写 SQL 是一个非常糟糕的做法。CI 管道甚至会直接拒绝代码，拒绝合并代码。也就是说，即使我们的某个开发人员试图忽略了这个明显的安全漏洞，系统本身也能阻止它。因此，Gab 要么根本没有任何 SAST 工具，要么故意选择忽略他们的反馈。

7.2 SQL Injection

Thanks to clever methods, this is hardly a problem in most Rails applications. However, this is a very devastating and common attack in web applications, so it is important to understand the problem.

7.2.1 Introduction

SQL injection attacks aim at influencing database queries by manipulating web application parameters. A popular goal of SQL injection attacks is to bypass authorization. Another goal is to carry out data manipulation or reading arbitrary data. Here is an example of how not to use user input data in a query:

Project.where("name = '#{params[:name]}'")

This could be in a search action and the user may enter a project's name that they want to find. If a malicious user enters ' OR 1 --, the resulting SQL query will be:

SELECT * FROM projects WHERE name = '' OR 1 --'

The two dashes start a comment ignoring everything after it. So the query returns all records from the projects table including those blind to the user. This is because the condition is true for all records.

7.2.2 Bypassing Authorization

Usually a web application includes access control. The user enters their login credentials and the web application tries to find the matching record in the users table. The application grants access when it finds a record. However, an attacker may possibly bypass this check with SQL injection. The following shows a typical database query in Rails to find the first record in the users table which matches the login credentials parameters supplied by the user.

User.find_by("login = '#{params[:name]}' AND password = '#{params[:password]}'")

If an attacker enters ' OR '1'='1 as the name, and ' OR '2'>'1 as the password, the resulting SQL query will be:

SELECT * FROM users WHERE login = '' OR '1'='1' AND password = '' OR '2'>'1' LIMIT 1

This will simply find the first record in the database, and grants access to this user.

7.2.3 Unauthorized Reading

The UNION statement connects two SQL queries and returns the data in one set. An attacker can use it to read arbitrary data from the database. Let's take the example from above:

Project.where("name = '#{params[:name]}'")

And now let's inject another query using the UNION statement:

') UNION SELECT id,login AS name,password AS description,1,1,1 FROM users --

This will result in the following SQL query:

SELECT * FROM projects WHERE (name = '') UNION
  SELECT id,login AS name,password AS description,1,1,1 FROM users --'

The result won't be a list of projects (because there is no project with an empty name), but a list of user names and their password. So hopefully you encrypted the passwords in the database! The only problem for the attacker is, that the number of columns has to be the same in both queries. That's why the second query includes a list of ones (1), which will be always the value 1, in order to match the number of columns in the first query.

Also, the second query renames some columns with the AS statement so that the web application displays the values from the user table. Be sure to update your Rails to at least 2.1.1.

7.2.4 Countermeasures

Ruby on Rails has a built-in filter for special SQL characters, which will escape ' , " , NULL character, and line breaks. Using Model.find(id) or Model.find_by_some thing(something) automatically applies this countermeasure. But in SQL fragments, especially in conditions fragments (where("...")), the connection.execute() or Model.find_by_sql() methods, it has to be applied manually.

Instead of passing a string to the conditions option, you can pass an array to sanitize tainted strings like this:

Model.where("login = ? AND password = ?", entered_user_name, entered_password).first

As you can see, the first part of the array is an SQL fragment with question marks. The sanitized versions of the variables in the second part of the array replace the question marks. Or you can pass a hash for the same result:

Model.where(login: entered_user_name, password: entered_password).first

The array or hash form is only available in model instances. You can try sanitize_sql() elsewhere. Make it a habit to think about the security consequences when using an external string in SQL.