php.lang.security.injection.tainted-sql-string.tainted-sql-string

profile photo of semgrepsemgrep
Author
unknown
Download Count*
LGPL Commons Clause
License

User data flows into this manually-constructed SQL string. User data can be safely inserted into SQL strings using prepared statements or an object-relational mapper (ORM). Manually-constructed SQL strings is a possible indicator of SQL injection, which could let an attacker steal or manipulate data from the database. Instead, use prepared statements ($mysqli->prepare("INSERT INTO test(id, label) VALUES (?, ?)");) or a safe library.

Run Locally

Run in CI

Semgrep CI docs

Defintion

Edit RuleView Source
Open in Playground 
rules:
  - id: tainted-sql-string
    languages:
      - php
    severity: ERROR
    message: User data flows into this manually-constructed SQL string. User data
      can be safely inserted into SQL strings using prepared statements or an
      object-relational mapper (ORM). Manually-constructed SQL strings is a
      possible indicator of SQL injection, which could let an attacker steal or
      manipulate data from the database. Instead, use prepared statements
      (`$mysqli->prepare("INSERT INTO test(id, label) VALUES (?, ?)");`) or a
      safe library.
    metadata:
      cwe:
        - "CWE-89: Improper Neutralization of Special Elements used in an SQL
          Command ('SQL Injection')"
      owasp:
        - A01:2017 - Injection
        - A03:2021 - Injection
      references:
        - https://owasp.org/www-community/attacks/SQL_Injection
      category: security
      technology:
        - php
      cwe2022-top25: true
      cwe2021-top25: true
      subcategory:
        - vuln
      likelihood: HIGH
      impact: MEDIUM
      confidence: MEDIUM
      license: Commons Clause License Condition v1.0[LGPL-2.1-only]
      vulnerability_class:
        - SQL Injection
    mode: taint
    pattern-sanitizers:
      - pattern-either:
          - pattern: mysqli_real_escape_string(...)
          - pattern: real_escape_string(...)
          - pattern: $MYSQLI->real_escape_string(...)
    pattern-sources:
      - patterns:
          - pattern-either:
              - pattern: $_GET
              - pattern: $_POST
              - pattern: $_COOKIE
              - pattern: $_REQUEST
    pattern-sinks:
      - pattern-either:
          - patterns:
              - pattern: |
                  sprintf($SQLSTR, ...)
              - metavariable-regex:
                  metavariable: $SQLSTR
                  regex: .*\b(?i)(select|delete|insert|create|update|alter|drop)\b.*
          - patterns:
              - pattern: |
                  "...$EXPR..."
              - metavariable-regex:
                  metavariable: $EXPR
                  regex: .*\b(?i)(select|delete|insert|create|update|alter|drop)\b.*
          - patterns:
              - pattern: |
                  "$SQLSTR".$EXPR
              - metavariable-regex:
                  metavariable: $SQLSTR
                  regex: .*\b(?i)(select|delete|insert|create|update|alter|drop)\b.*

Examples

tainted-sql-string.php

<?php

// True Positives

function test1() {
    // ruleid: tainted-sql-string
    $query = "SELECT * FROM table WHERE Id = '".$_GET['url']."'";
    $info = mysql_query($query);
    return $info;
}

function test2() {
    $part = $_POST['url'];
    // ruleid: tainted-sql-string
    $query = "SELECT * FROM table WHERE Id = '$part'";
    $info = mysql_query($query);
    return $info;
}

function test3() {
    // ruleid: tainted-sql-string
    $query = "SELECT * FROM table WHERE Id = '{$_REQUEST['url']}'";
    $info = mysql_query($query);
    return $info;
}

function test4() {
    // ruleid: tainted-sql-string
    $query = sprintf("SELECT * FROM table WHERE Id = '%s'", $_COOKIE['foo']);
    $info = mysql_query($query);
    return $info;
}

// True Negatives

function test1() {
    // ok: tainted-sql-string
    $query = 'SELECT * FROM table WHERE Id = 1';
    $info = mysql_query($query);
    return $info;
}

function test2() {
    $value = 1;
    // ok: tainted-sql-string
    $query = "SELECT * FROM table WHERE Id = '".$value."'";
    $info = mysql_query($query);
    return $info;
}

function test3() {
    // ok: tainted-sql-string
    $query = "SELECT * FROM table WHERE Id = '{$foobar() ? 1 : 2}'";
    $info = mysql_query($query);
    return $info;
}

function test4() {
    $value = 1;
    // ok: tainted-sql-string
    $query = sprintf("SELECT * FROM table WHERE Id = '%s'", $value);
    $info = mysql_query($query);
    return $info;
}

function test5() {
    $part = $_POST['url'];
    $part = mysqli_real_escape_string($part);
    // ok: tainted-sql-string
    $query = sprintf("SELECT * FROM table WHERE Id = '" . $part . "'");
    $info = mysql_query($query);
    return $info;
}

function ok_test6() {
    // ok: tainted-sql-string
    $this->delete("id:".$_GET['id']);
}

function ok_test7() {
    // ok: tainted-sql-string
    $select = "Foobar: = '{$_REQUEST['url']}'";
    $info = do_smth($select);
    return $info;
}