java.lang.security.audit.formatted-sql-string.formatted-sql-string
Community Favorite
semgrep
Author
73,592
Download Count*
License
Detected a formatted string in a SQL statement. This could lead to SQL injection if variables in the SQL statement are not properly sanitized. Use a prepared statements (java.sql.PreparedStatement) instead. You can obtain a PreparedStatement using 'connection.prepareStatement'.
Run Locally
Run in CI
Defintion
rules:
- id: formatted-sql-string
metadata:
cwe:
- "CWE-89: Improper Neutralization of Special Elements used in an SQL
Command ('SQL Injection')"
owasp:
- A01:2017 - Injection
- A03:2021 - Injection
source-rule-url: https://find-sec-bugs.github.io/bugs.htm#SQL_INJECTION
asvs:
section: "V5: Validation, Sanitization and Encoding Verification Requirements"
control_id: 5.3.5 Injection
control_url: https://github.com/OWASP/ASVS/blob/master/4.0/en/0x13-V5-Validation-Sanitization-Encoding.md#v53-output-encoding-and-injection-prevention-requirements
version: "4"
references:
- https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html
- https://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html#create_ps
- https://software-security.sans.org/developer-how-to/fix-sql-injection-in-java-using-prepared-callable-statement
category: security
technology:
- java
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
options:
taint_assume_safe_numbers: true
taint_assume_safe_booleans: true
message: Detected a formatted string in a SQL statement. This could lead to SQL
injection if variables in the SQL statement are not properly sanitized.
Use a prepared statements (java.sql.PreparedStatement) instead. You can
obtain a PreparedStatement using 'connection.prepareStatement'.
mode: taint
pattern-sources:
- patterns:
- pattern-either:
- pattern: |
(HttpServletRequest $REQ)
- patterns:
- pattern-inside: |
$ANNOT $FUNC (..., $INPUT, ...) {
...
}
- pattern: (String $INPUT)
- focus-metavariable: $INPUT
label: INPUT
- patterns:
- pattern-either:
- pattern: $X + $INPUT
- pattern: $X += $INPUT
- pattern: $STRB.append($INPUT)
- pattern: String.format(..., $INPUT, ...)
- pattern: String.join(..., $INPUT, ...)
- pattern: (String $STR).concat($INPUT)
- pattern: $INPUT.concat(...)
- pattern: new $STRB(..., $INPUT, ...)
label: CONCAT
requires: INPUT
pattern-propagators:
- pattern: (StringBuffer $S).append($X)
from: $X
to: $S
- pattern: (StringBuilder $S).append($X)
from: $X
to: $S
pattern-sinks:
- patterns:
- pattern-not: $S.$SQLFUNC(<... "=~/.*TABLE *$/" ...>)
- pattern-not: $S.$SQLFUNC(<... "=~/.*TABLE %s$/" ...>)
- pattern-either:
- pattern: (Statement $S).$SQLFUNC(...)
- pattern: (PreparedStatement $P).$SQLFUNC(...)
- pattern: (Connection $C).createStatement(...).$SQLFUNC(...)
- pattern: (Connection $C).prepareStatement(...).$SQLFUNC(...)
- pattern: (EntityManager $EM).$SQLFUNC(...)
- metavariable-regex:
metavariable: $SQLFUNC
regex: execute|executeQuery|createQuery|query|addBatch|nativeSQL|create|prepare
requires: CONCAT
pattern-sanitizers:
- patterns:
- pattern: (CriteriaBuilder $CB).$ANY(...)
severity: ERROR
languages:
- java
Examples
formatted-sql-string.java
// cf. https://www.baeldung.com/sql-injection
package sql.injection;
import com.biz.org.AccountDTO;
import com.biz.org.DB;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.Query;
import javax.persistence.criteria.CriteriaBuilder;
public class SqlExample {
public void staticQuery() throws SQLException {
Connection c = DB.getConnection();
// ok:formatted-sql-string
ResultSet rs = c.createStatement().executeQuery("SELECT * FROM happy_messages");
}
public void getAllFields(String tableName) throws SQLException {
Connection c = DB.getConnection();
// ruleid:formatted-sql-string
ResultSet rs = c.createStatement().executeQuery("SELECT * FROM " + tableName);
}
public void findAccountsById(String id) throws SQLException {
String sql = "SELECT * "
+ "FROM accounts WHERE id = '"
+ id
+ "'";
Connection c = DB.getConnection();
// ruleid:formatted-sql-string
ResultSet rs = c.createStatement().executeQuery(sql);
}
public void findAccountsById(String id, String field) throws SQLException {
String sql = "SELECT ";
sql += field;
sql += " FROM accounts WHERE id = '";
sql += id;
sql += "'";
Connection c = DB.getConnection();
// ruleid:formatted-sql-string
ResultSet rs = c.createStatement().executeQuery(sql);
}
}
public class SqlExample2 {
public void getAllFields(String tableName) throws SQLException {
Connection c = db.getConnection();
// ruleid:formatted-sql-string
ResultSet rs = c.createStatement().execute("SELECT * FROM " + tableName);
}
public void findAccountsById(String id) throws SQLException {
String sql = "SELECT * "
+ "FROM accounts WHERE id = '"
+ id
+ "'";
Connection c = db.getConnection();
// ruleid:formatted-sql-string
ResultSet rs = c.createStatement().execute(sql);
}
public List<AccountDTO> findAccountsById(String id) {
String jql = "from Account where id = '" + id + "'";
EntityManager em = emfactory.createEntityManager();
// ruleid:formatted-sql-string
TypedQuery<Account> q = em.createQuery(jql, Account.class);
return q.getResultList()
.stream()
.map(this::toAccountDTO)
.collect(Collectors.toList());
}
}
public class SQLExample3 {
public void getAllFields(String tableName) throws SQLException {
Connection c = db.getConnection();
// ruleid:formatted-sql-string
ResultSet rs = c.createStatement().execute(String.format("SELECT * FROM %s", tableName));
}
public void findAccountsById(String id) throws SQLException {
String sql = String.format("SELECT * FROM accounts WHERE id = '%s'", id);
Connection c = db.getConnection();
// ruleid:formatted-sql-string
ResultSet rs = c.createStatement().execute(sql);
}
public List<AccountDTO> findAccountsById(String id) {
String jql = String.format("from Account where id = '%s'", id);
EntityManager em = emfactory.createEntityManager();
// ruleid: formatted-sql-string
TypedQuery<Account> q = em.createQuery(jql, Account.class);
return q.getResultList()
.stream()
.map(this::toAccountDTO)
.collect(Collectors.toList());
}
public void findAccountsByIdOk() throws SQLException {
String id = "const";
String sql = String.format("SELECT * FROM accounts WHERE id = '%s'", id);
Connection c = db.getConnection();
// ok:formatted-sql-string
ResultSet rs = c.createStatement().execute(sql);
}
}
public class tableConcatStatements {
public void tableConcat() {
// ok:formatted-sql-string
stmt.execute("DROP TABLE " + tableName);
stmt.execute(String.format("CREATE TABLE %s", tableName));
}
}
// This whole operation has nothing to do with SQL
public class FalsePositiveCase {
private ApiClient apiClient; // imagine an ApiClient class that contains a method named execute
public void test(String parameter) throws ApiException {
com.squareup.okhttp.Call call = constructHttpCall(parameter); // Create OKHttp call using parameter from outside
// ok: formatted-sql-string
apiClient.execute(call);
// ok: formatted-sql-string
apiClient.execute(call);
apiClient.run(call); // proof that 'execute' name is causing the false-positive
}
public List<Student> addWhere(String name, CriteriaQuery Query)
{
EntityManager em = emfactory.createEntityManager();
CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
// ok: formatted-sql-string
List<Student> students = em.createQuery(Query.where(criteriaBuilder.equal(studentRoot.get("name"), name ))).getResultList();
return students;
}
}
Short Link: https://sg.run/OPXp