INJ00-G: Prevent SQL Injection

Introduction

Structured Query Language (SQL) injection vulnerabilities occur when unvalidated user input is added to SQL statements, which can lead to manipulation of the statement and compromise the system's security. This may result in leaks of sensitive information or breaches of data integrity. Preventative measures such as using parameterized queries or implementing input validation can help mitigate the risk of SQL injection attacks.

This rule outlines best practices for handling SQL queries for self-managed environments, but JDBC is not supported on Guidewire Cloud.

Non-compliant Code Example

This non-compliant code example authenticates a username/password by executing an SQL statement to retrieve a username and password from a SQL-compliant database. The password is passed as a char array, the database connection is created, and the passwords are hashed. The function returns true if the username and password hash match a record in the database.

Unfortunately, this code example permits a SQL injection attack by incorporating the tainted input argument username into the SQL command, which allows an attacker to manipulate the query to gain unauthorized access:

// Noncompliant Code Example

function authenticate(String username, char[] password) : boolean {
     var pwd = hashPassword(password)
     using (var conn = _dataSource.getConnection()
          var stmt = conn.createStatement(),  
          var rs = stmt.executeQuery(
            "SELECT * FROM db_user WHERE username = '" 
            + username + "' AND password = '" + pwd + "'")) { 
          if (rs.next()) return true 
          return false
     } // end using
} // end function authenticate     

Compliant Solution

This compliant solution uses a parametric query with a ? (question mark) character as a placeholder for the argument.

// Compliant Solution

function authenticate(String username, char[] password) : boolean {
     var pwd = hashPassword(password)
     using (var conn = _dataSource.getConnection()) {
          var sqlString = "select * from db_user where username=? and password=?"
          var stmt = conn.prepareStatement(sqlString)
          stmt.setString(1, username)
          stmt.setString(2, pwd)
          var rs = stmt.executeQuery()
          if (rs.next()) return true
          return false
     } // end using
} // end function authenticate

Use the set*() methods of the PreparedStatement class to enforce strong type checking. This technique mitigates the SQL injection vulnerability because the input is properly escaped.

Risk Assessment

Failure to sanitize tainted user input before incorporating the data into an SQL command can result in SQL injection attacks. This rule is applicable in self-managed environments where calls to the execute method are allowed but not in the Cloud environment where they are prohibited.

Rule	Severity	Likelihood	Remediation Cost	Priority	Level
INJ00-G	High	Likely	Medium	L12	L1

Additional Resources

Was this page helpful?

Introduction​

Non-compliant Code Example​

Compliant Solution​

Risk Assessment​

Additional Resources​

Introduction

Non-compliant Code Example

Compliant Solution

Risk Assessment

Additional Resources