gitlab.security_code_scan.SCS0002-1
SQL Injection is a critical vulnerability that can lead to data or system compromise. By dynamically generating SQL query strings, user input may be able to influence the logic of the SQL statement. This could lead to an adversary accessing information they should not have access to, or in some circumstances, being able to execute OS functionality or code.
Replace all dynamically generated SQL queries with parameterized queries. In situations where dynamic queries must be created, never use direct user input, but instead use a map or dictionary of valid values and resolve them using a user supplied key.
For example, some database drivers do not allow parameterized queries for >
or <
comparison
operators. In these cases, do not use a user supplied >
or <
value, but rather have the
user
supply a gt
or lt
value. The alphabetical values are then used to look up the >
and <
values to be used in the construction of the dynamic query. The same goes for other queries
where
column or table names are required but cannot be parameterized.
Example using parameterized queries with SqlCommand
:
string userInput = "someUserInput";
string connectionString = ...;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
String sql = "SELECT name, value FROM table where name=@Name";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.Add("@Name", System.Data.SqlDbType.NVarChar);
command.Parameters["@Name"].Value = userInput;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine("{0} {1}", reader.GetString(0), reader.GetString(1));
}
}
}
}
For more information on SQL Injection see OWASP: https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html
Run Locally
Run in CI
Defintion
rules:
- id: security_code_scan.SCS0002-1
languages:
- csharp
patterns:
- pattern-either:
- patterns:
- metavariable-regex:
metavariable: $FUNC
regex: ^(SqlQuery|ExecuteSqlCommand|ExecuteSqlCommandAsync|ExecuteSqlRaw|ExecuteSqlRawAsync|FromSqlRaw|FromSql|GetSqlStringCommand|ExecuteDataSet|ExecuteReader|ExecuteNonQuery|ExecuteScalar|CreateSQLQuery)$
- pattern: $DB.$FUNC($ARG, ...);
- pattern-not: $DB.$FUNC("...", ...);
- patterns:
- metavariable-regex:
metavariable: $FUNC
regex: ^(ExecuteQuery|ExecuteCommand)$
- pattern-inside: |
using System.Data.Linq;
...
- pattern: (DataContext $CTX).$FUNC($ARG, ...)
- patterns:
- metavariable-regex:
metavariable: $IMPL
regex: ^(SqlCommand|OracleCommand|NpgsqlCommand|MySqlCommand|EntityCommand|OdbcCommand|OleDbCommand|SqliteCommand)$
- pattern-either:
- patterns:
- pattern: new $IMPL($ARG, ...);
- pattern-not: new $IMPL("...", ...);
- patterns:
- pattern: ($IMPL $CMD).CommandText = <...$ARG...>;
- pattern-not: ($IMPL $CMD).CommandText = "...";
- patterns:
- metavariable-regex:
metavariable: $FUNC
regex: ^(ExecuteDataRow|ExecuteDataRowAsync|ExecuteDataset|ExecuteDatasetAsync|ExecuteNonQuery|ExecuteNonQueryAsync|ExecuteReader|ExecuteReaderAsync|ExecuteScalar|ExecuteScalarAsync|UpdateDataSet|UpdateDataSetAsync)$
- pattern-inside: |
using MySql.Data.MySqlClient;
...
- pattern: MySqlHelper.$FUNC("...", $ARG, ...);
- patterns:
- pattern-inside: |
using Cassandra;
...
- pattern: (Session $SESS).Execute($ARG, ...);
- pattern-not: (Session $SESS).Execute("...", ...);
message: >
SQL Injection is a critical vulnerability that can lead to data or system
compromise. By
dynamically generating SQL query strings, user input may be able to influence the logic of
the SQL statement. This could lead to an adversary accessing information they should
not have access to, or in some circumstances, being able to execute OS functionality or code.
Replace all dynamically generated SQL queries with parameterized queries. In situations where
dynamic queries must be created, never use direct user input, but instead use a map or
dictionary of valid values and resolve them using a user supplied key.
For example, some database drivers do not allow parameterized queries for `>` or `<` comparison
operators. In these cases, do not use a user supplied `>` or `<` value, but rather have the
user
supply a `gt` or `lt` value. The alphabetical values are then used to look up the `>` and `<`
values to be used in the construction of the dynamic query. The same goes for other queries
where
column or table names are required but cannot be parameterized.
Example using parameterized queries with `SqlCommand`:
```
string userInput = "someUserInput";
string connectionString = ...;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
String sql = "SELECT name, value FROM table where name=@Name";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.Add("@Name", System.Data.SqlDbType.NVarChar);
command.Parameters["@Name"].Value = userInput;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine("{0} {1}", reader.GetString(0), reader.GetString(1));
}
}
}
}
```
For more information on SQL Injection see OWASP:
https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html
severity: ERROR
metadata:
shortDescription: Improper Neutralization of Special Elements used in an SQL
Command ('SQL Injection')
category: security
cwe: CWE-89
primary_identifier: security_code_scan.SCS0002-1
secondary_identifiers:
- name: SCS0002
type: security_code_scan_rule_id
value: SCS0002
license: MIT
vulnerability_class:
- Other