Avoid SQL injection

Docs > コード分析 > Static Analysis Rules > Avoid SQL injection

このページは日本語には対応しておりません。随時翻訳に取り組んでいます。
翻訳に関してご質問やご意見ございましたら、お気軽にご連絡ください。

Metadata

ID: ruby-security/sql-injection

Language: Ruby

Severity: Error

Category: Security

CWE: 89

Description

This rule pertains to avoiding SQL Injection, which is a serious security vulnerability that can allow attackers to manipulate or control your database. SQL Injection occurs when untrusted data is inserted into a database query without proper sanitization or parameterization.

In the provided non-compliant code, the SQL queries are constructed by string concatenation with user-provided input. This is a dangerous practice as it allows an attacker to inject arbitrary SQL code into the query. For instance, if an attacker provides an ‘id’ value of “1; DROP TABLE analysis_results;”, it could lead to the deletion of an entire table.

To avoid SQL Injection, always use parameterized queries or prepared statements, which ensure that user-provided input is always treated as literal values, not executable code. In Ruby, you can use the ‘quote’ and ‘sanitize’ methods provided by ActiveRecord, or use ‘?’ placeholders in your SQL queries to safely include user-provided input. For example, you could write: ActiveRecord::Base.connection.execute("UPDATE analysis_results SET running_time_sec = ? WHERE id = ?", time, id). This ensures that the ’time’ and ‘id’ values are properly escaped, preventing SQL Injection.

Non-Compliant Code Examples

stmt = "UPDATE analysis_results SET running_time_sec=" + time + " WHERE id="+id
stmt = "update analysis_results SET running_time_sec=#{time} WHERE id=#{id}"
stmt = "UPDATE analysis_results SET running_time_sec=%{time} WHERE id=%{id}" % {id: 42, time: 51}


stmt = "UPDATE analysis_results SET running_time_sec=" + time + " WHERE id="+id
stmt = "UPDATE analysis_results SET running_time_sec=#{time} WHERE id=#{id}"
stmt = "UPDATE analysis_results SET running_time_sec=%{time} WHERE id=%{id}" % {id: 42, time: 51}


stmt = "SELECT foo,bar FROM myTable WHERE id="+id
stmt = "SELECT * from mytable WHERE id=#{id}"
stmt = "SELECT plop.foo from my_table WHERE id=%{id}" % {id: 42, time: 51}

stmt = "DELETE FROM myTable WHERE id="+id
stmt = "delete from mytable WHERE id=#{id}"
stmt = "DELETE from my_table WHERE id=%{id}" % {id: 42, time: 51}


stmt = "INSERT INTO myTable VALUES("+id+");"
stmt = "insert into mytable(field1, field2) VALUES (#{field1}, #{field2}})"  
stmt = "insert INTO my_table(field1, field2) VALUES (%{field1},%{field2}) " % {field1: 42, field2: 51}