Insecure Use of SQL Queries

Fixing Insecure Use of SQL Queries

About SQL Injection (SQLi)

What is SQL injection (SQLi)?

SQL injection (SQLi) is a type of security vulnerability that allows attackers to exploit an application's interaction with a SQL database.

This occurs when an application fails to properly sanitize user input and includes untrusted data in an SQL query, allowing attackers to inject malicious code or modify the intended behavior of the query.

SQL injection can affect various types of applications that interact with SQL databases, such as web applications, content management systems, and e-commerce platforms.

Attackers can use various techniques, such as crafted queries or filter values, to inject malicious code or entities into the application.

Check out this video for a high-level explanation:

What is the impact of SQL injections?

SQL injection can lead to various security threats and risks, such as:

Information disclosure: SQL injection can expose sensitive information, such as user data, login credentials, or other types of confidential information, to unauthorized parties.
Unauthorized access: SQL injection can allow attackers to gain unauthorized access to databases, perform unauthorized actions, or modify data.
Denial-of-service: SQL injection can cause denial-of-service (DoS) attacks by injecting malicious queries or values that consume system resources, such as CPU or memory, leading to system crashes or slowdowns.
Arbitrary code execution: SQL injection can execute arbitrary code on the database server by injecting malicious queries or values that exploit vulnerabilities in the application or the underlying operating system.

SQL injections are very common and have been the cause of many high-profile breaches.

How to prevent SQL injections?

To prevent SQL injection, it is important to follow security best practices and implement appropriate security measures, such as:

Use parameterized queries: Use parameterized queries and other secure coding practices to ensure that user input is properly sanitized and not used to construct SQL queries directly.
Use input validation: Validate user input to ensure that it is safe and does not contain malicious code or characters that can be used for injection attacks.
Limit user access: Limit user access to databases to authorized users and restrict access to sensitive information and actions.
Use secure coding practices: Follow secure coding practices, such as code reviews, vulnerability scanning and testing, and threat modeling, to ensure that the source code is free of vulnerabilities and that SQL injection vulnerabilities are addressed.
Use a web application firewall: Use a web application firewall (WAF) that can help prevent SQL injection by filtering and blocking malicious traffic.

References

Taxonomies

Explanation & Prevention

CVE Details: CWE-89

Training

Secure Code Warrior: SQL Injection

Option A: Use Prepared Statements Securely

Go through the issues that GuardRails identified in the PR/MR

Look for insecure patterns like this:

// Persistence Manager
PersistenceManager pm = getPM();
Query q = pm.newQuery("select * from Users where name = " + input);
q.execute();

or:

// Entity Manager
EntityManager pm = getEM();
TypedQuery<UserEntity> q = em.createQuery(
    String.format("select * from Users where name = %s", username),
    UserEntity.class);
UserEntity res = q.getSingleResult();

or:

//JDBC Template
JdbcTemplate jdbc = new JdbcTemplate();
int count = jdbc.queryForObject("select count(*) from Users where name = '"+paramName+"'", Integer.class);

or:

// JDBC Injection
Connection conn = [...];
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("update COFFEES set SALES = "+nbSales+" where COF_NAME = '"+coffeeName+"'");

or:

//Scala Slick Injection
db.run {
 sql"select * from people where name = '#$value'".as[Person]
}

or:

//Scala Anorm Injection
val peopleParser = Macro.parser[Person]("id", "name", "age")
DB.withConnection { implicit c =>
  val people: List[Person] = SQL("select * from people where name = '" + value + "'").as(peopleParser.*)
}

or:

//Android
String query = "SELECT * FROM  messages WHERE uid= '"+userInput+"'" ;
Cursor cursor = this.getReadableDatabase().rawQuery(query,null);

Replace it with the following:

// Persistence Manager
PersistenceManager pm = getPM();
Query q = pm.newQuery("select * from Users where name = nameParam");
q.declareParameters("String nameParam");
q.execute(input);

or:

// Entity Manager
TypedQuery<UserEntity> q = em.createQuery(
"select * from Users where name = usernameParam",UserEntity.class)
.setParameter("usernameParam", username);
UserEntity res = q.getSingleResult();<

or:

//JDBC Template
JdbcTemplate jdbc = new JdbcTemplate();
int count = jdbc.queryForObject("select count(*) from Users where name = ?", Integer.class, paramName);

or:

// JDBC Injection
Connection conn = [...];
conn.prepareStatement("update COFFEES set SALES = ? where COF_NAME = ?");
updateSales.setInt(1, nbSales);
updateSales.setString(2, coffeeName);

or:

//Scala Slick Injection
db.run {
 sql"select * from people where name = $value".as[Person]
}

or:

//Scala Anorm Injection
val peopleParser = Macro.parser[Person]("id",  "name", "age")
DB.withConnection { implicit c =>
  val people: List[Person] = SQL"select * from people where name = $value".as(peopleParser.*)
}

or:

//Android
String query = "SELECT * FROM  messages WHERE uid= ?" ;
Cursor cursor = this.getReadableDatabase().rawQuery(query,new String[] {userInput});

Test it
Ship it 🚢 and relax 🌴

Option B: Use Hibernate Securely

Solution-specific resources:

Go through the issues that GuardRails identified in the PR/MR

Look for insecure patterns like this:

Session session = sessionFactory.openSession();
Query q = session.createQuery("select t from UserEntity t where id = " + input);
q.execute();

Replace it with the following:

Session session = sessionFactory.openSession();
Query q = session.createQuery("select t from UserEntity t where id = :userId");
q.setString("userId",input);
q.execute();

or by using Hibernate Criteria:

Session session = sessionFactory.openSession();
Query q = session.createCriteria(UserEntity.class)
  .add( Restrictions.like("id", input) )
  .list();
q.execute();

Test it
Ship it 🚢 and relax 🌴

Option C: Use the ESAPI SQL Encoder for Special SQL Characters

Go through the issues that GuardRails identified in the PR/MR

Look for insecure patterns like this:

// EntityManager.createQuery
createQuery("select * from User where id = '"+inputId+"'");

or:

//Turbine API DSL
List<Record> BasePeer.executeQuery( "select * from Customer where id=" + inputId );

or:

//AWS Query Injection
String customerID = getAuthenticatedCustomerID(customerName, customerCredentials);
String productCategory = request.getParameter("productCategory");
...
AmazonSimpleDBClient sdbc = new AmazonSimpleDBClient(appAWSCredentials);
String query = "select * from invoices where productCategory = '"
            + productCategory + "' and customerID = '"
            + customerID + "' order by '"
            + sortColumn + "' asc";
SelectResult sdbResult = sdbc.select(new SelectRequest(query));

Leverage ESAPI for SQL encoding

// EntityManager.createQuery
import org.owasp.esapi.Encoder;
createQuery("select * from User where id = '"+Encoder.encodeForSQL(inputId)+"'");

or:

//Turbine API DSL
import org.owasp.esapi.Encoder;
BasePeer.executeQuery("select * from Customer where id = '"+Encoder.encodeForSQL(inputId)+"'");

or:

//AWS Query Injection
import org.owasp.esapi.Encoder;
String customerID = getAuthenticatedCustomerID(customerName, customerCredentials);
String productCategory = request.getParameter("productCategory");
...
AmazonSimpleDBClient sdbc = new AmazonSimpleDBClient(appAWSCredentials);
String query = "select * from invoices where productCategory = '"
            + Encoder.encodeForSQL(productCategory) + "' and customerID = '"
            + Encoder.encodeForSQL(customerID) + "' order by '"
            + Encoder.encodeForSQL(sortColumn) + "' asc";
SelectResult sdbResult = sdbc.select(new SelectRequest(query));

Test it
Ship it 🚢 and relax 🌴

Fixing Insecure Use of SQL Queries​

What is SQL injection (SQLi)?​

What is the impact of SQL injections?​

How to prevent SQL injections?​

References​

Taxonomies​

Explanation & Prevention​

Related CVEs​

Training​

Option A: Use Prepared Statements Securely​

Option B: Use Hibernate Securely​

Option C: Use the ESAPI SQL Encoder for Special SQL Characters​

Fixing Insecure Use of SQL Queries

What is SQL injection (SQLi)?

What is the impact of SQL injections?

How to prevent SQL injections?

References

Taxonomies

Explanation & Prevention

Related CVEs

Training

Option A: Use Prepared Statements Securely

Option B: Use Hibernate Securely

Option C: Use the ESAPI SQL Encoder for Special SQL Characters