Semgrep Registry - gitlab.security_code

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

Defintion

Open in Playground

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

gitlab.security_code_scan.SCS0002-1

Run Locally

Run in CI

Defintion