Skip to content

Resolve vulnerability: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection')

Mirko Brandner requested to merge remediate/improper-neutralization-of-special-elements-used-in-an-sql-comm-D20240402T110424 into main

AI GENERATED PATCH

The suggested code changes were generated by GitLab Duo Vulnerability Resolution, an AI feature. Use this feature with caution. Before you apply the code changes, carefully review and test them, to ensure that they solve the vulnerability, don't harm the functional behavior of your application or introduce new vulnerabilities.

The large language model that generated the suggested code changes was only provided with the affected lines of code, and the vulnerability in that code. It is not aware of any functionality outside of this context.

Please see our documentation for more information about this feature. We'd love to hear your feedback so we can improve on this feature as we work to bring it to general availability.

Description:

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 PreparedStatement queries:

// Some userInput
String userInput = "someUserInput";
// Your connection string
String url = "...";
// Get a connection from the DB via the DriverManager
Connection conn = DriverManager.getConnection(url);
// Create a prepared statement
PreparedStatement st = conn.prepareStatement("SELECT name FROM table where name=?");
// Set each parameters value by the index (starting from 1)
st.setString(1, userInput);
// Execute query and get the result set
ResultSet rs = st.executeQuery();
// Iterate over results
while (rs.next()) {
    // Get result for this row at the provided column number (starting from 1)
    String result = rs.getString(1);
    // ...
}
// Close the ResultSet
rs.close();
// Close the PreparedStatement
st.close();

For more information on SQL Injection see OWASP: https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html

Identifiers:

  • Find Security Bugs-SQL_INJECTION_JDBC
  • Find Security Bugs-SQL_NONCONSTANT_STRING_PASSED_TO_EXECUTE
  • CWE-89
  • Find Security Bugs-SQL_PREPARED_STATEMENT_GENERATED_FROM_NONCONSTANT_STRING
  • find_sec_bugs.SQL_INJECTION_SPRING_JDBC-1.SQL_INJECTION_JPA-1.SQL_INJECTION_JDO-1.SQL_INJECTION_JDBC-1.SQL_NONCONSTANT_STRING_PASSED_TO_EXECUTE-1.SQL_INJECTION-1.SQL_INJECTION_HIBERNATE-1.SQL_INJECTION_VERTX-1.SQL_PREPARED_STATEMENT_GENERATED_FROM_NONCONSTANT_STRING-1
  • Find Security Bugs-SQL_INJECTION_JPA
  • Find Security Bugs-SQL_INJECTION_HIBERNATE
  • Find Security Bugs-SQL_INJECTION_JDO
  • Find Security Bugs-SQL_INJECTION
  • Find Security Bugs-SQL_INJECTION_SPRING_JDBC
  • Find Security Bugs-SQL_INJECTION_VERTX
  • A1:2017 - Injection
  • A03:2021 - Injection

Merge request reports