Recipe Name:
Injection: Avoid SQL Injection: Use Parameterized Queries (PreparedStatement)
Description:
Could lead to SQL Injection
Level:
error
Language:
  • java
Tags:
  • security
  • SEI CERT
  • basic protection set
  • injection
  • SQL
  • OWASP Top 10
Documentation

Concatenation of untrusted input directly into SQL queries can lead to injection attacks.

Untrusted data needs to be handled with caution. In the case of SQL queries, this can be done by using prepared statements. Instead of concatenating the parameters into the query itself, a prepared statement puts placeholders (often '?' is used as placeholder) in the query where the parameters need to go. After defining the structure of the query in this way, we then tell the statement exactly what the type of the parameters is, using e.g. setString. This prevents attacks in multiple ways. Firstly, as the prepared statement knows what part of the query is meant to be a parameter, it will not attempt to interpret the parameter values as part of the SQL query. It will only consider them to be values. Secondly, by providing the type of the parameter we ensure it can also be checked before using the value in the SQL query.

Before 
String sqlString = "SELECT * FROM db_user WHERE username = '"
    + username + "' AND password = '" + pwd + "'";
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(sqlString);
After 
String sqlString = "SELECT * FROM db_user WHERE username = ?"
    + AND password = ?";
PreparedStatement stmt = connection.prepareStatement(sqlString);
stmt.setString(1, username);
stmt.setString(2, hashPassword(password));
ResultSet rs = stmt.executeQuery();
References
Recipe 
id: scw:sqli:connection-preparestatement
version: 10
metadata:
  name: 'Injection: Avoid SQL Injection: Use Parameterized Queries (PreparedStatement)'
  shortDescription: Could lead to SQL Injection
  level: error
  language: java
  newCodeOnly: false
  scwCategory: injection:sql
  cweCategory: 89
  enabled: true
  comment: ""
  descriptionFile: descriptions/java_use_parameterized_queries.html
  tags: security;SEI CERT;basic protection set;injection;SQL;OWASP Top 10
search:
  methodcall:
    args:
      1:
        type: java.lang.String
        value:
          containsUntrustedInput: true
    name: prepareStatement
    type: java.sql.Connection
availableFixes:
- name: Use parameterized queries
  actions:
  - parameterize:
      placeholderFormat: '?'
      extractUntrustedInput:
        methodsOnObject:
          methods:
          - type: java.lang.String
            methodName: setString
            args:
              "1": '{{{ index }}}'
              "2": '{{{ . }}}'
          - type: java.lang.Integer
            methodName: setInt
            args:
              "1": '{{{ index }}}'
              "2": '{{{ . }}}'
          - type: int
            methodName: setInt
            args:
              "1": '{{{ index }}}'
              "2": '{{{ . }}}'
          - type: double
            methodName: setDouble
            args:
              "1": '{{{ index }}}'
              "2": '{{{ . }}}'
          - type: java.lang.Double
            methodName: setDouble
            args:
              "1": '{{{ index }}}'
              "2": '{{{ . }}}'
          - type: float
            methodName: setFloat
            args:
              "1": '{{{ index }}}'
              "2": '{{{ . }}}'
          - type: java.lang.Float
            methodName: setFloat
            args:
              "1": '{{{ index }}}'
              "2": '{{{ . }}}'
          - type: boolean
            methodName: setBoolean
            args:
              "1": '{{{ index }}}'
              "2": '{{{ . }}}'
          - type: java.lang.Boolean
            methodName: setBoolean
            args:
              "1": '{{{ index }}}'
              "2": '{{{ . }}}'
          - type: java.lang.Short
            methodName: setShort
            args:
              "1": '{{{ index }}}'
              "2": '{{{ . }}}'
          - type: short
            methodName: setShort
            args:
              "1": '{{{ index }}}'
              "2": '{{{ . }}}'
          - type: java.sql.Time
            methodName: setTime
            args:
              "1": '{{{ index }}}'
              "2": '{{{ . }}}'
          - type: java.sql.TimeStamp
            methodName: setTimeStamp
            args:
              "1": '{{{ index }}}'
              "2": '{{{ . }}}'
          - type: java.net.URL
            methodName: setURL
            args:
              "1": '{{{ index }}}'
              "2": '{{{ . }}}'
          target:
            returnValue: {}