Teo Selenius

Posted on Feb 22, 2021 • Edited on Mar 7, 2021 • Originally published at appsecmonkey.com

SQL Injection Attacks & Prevention in 2021: A Developer's Guide

The original article can be read here.

What are SQL injection vulnerabilities?

SQL injection vulnerabilities arise when you construct database queries unsafely, and untrusted data gets interpreted as a part of the SQL query structure.

How to prevent SQL injection vulnerabilities?

The best way to prevent SQL injection vulnerabilities is to use a framework that allows you to construct and parameterize queries safely. An ORM (Object Relational Mapper) is a good option. For additional security layers, validate all input and use a WAF (Web Application Firewall) product.

A simple example

Let's say we have a Java app that allows for users to retrieve their documents by ID. We might do something like this:

String query = "SELECT * FROM documents WHERE ownerId=" + authContext.getUserId() + " AND documentName = '" + request.getParameter("docName") + "'";
executeQuery(query);

So if the user ID is 25 and the URL is something like https://www.example.com/documents/?docName=ABC123, then the query would be:

SELECT * FROM documents WHERE ownerId=25 AND documentName='ABC123';

So far, so good. But what if the URL is https://www.example.com/documents/?docName=ABC123'OR'1'='1?

Now we get the following query which returns all documents for all users (because 1=1 is always true):

SELECT * FROM documents WHERE ownerId=25 AND documentName='ABC123' OR '1'='1';

Oops. So how to avoid this?

Object Relational Mappers

Using Java as an example, using an ORM such as hibernate that implements the JPA (Java Persistence API) could look like this.

Define a model

@Entity
public class Document {
  @Id
  @GeneratedValue(strategy=GenerationType.AUTO)
  private Long id;
  private String documentName;
  private Integer ownerId;
}

Define a repository class

@Repository
public interface DocumentRepository extends JpaRepository<Document, Long> {
  List<Document> findByDocumentNameAndOwnerId(String documentName, Integer ownerId);
}

Use the repository

Now you can fetch the documents like so:

List<Document> docs = documentRepository.findByDocumentNameAndOwnerId(request.getParameter("docName"), authContext.getUserId());

And the ORM will take care of handling all parameters safely. And suppose you want more control over your queries. In that case, many ORMs provide query builders that you can use, such as Hibernate Criteria API.

If you use Python, Django has a great ORM, and if you don't use Django, sqlalchemy is an excellent option.

PHP has Doctrine. Just google for an ORM for your technology of choice.

https://www.doctrine-project.org/

Warning

ORM frameworks are not a silver bullet in two senses.

First is that they still have functionality for supporting raw SQL queries/query parts. Just don't use those features, and you're golden.

The second is that ORM frameworks have vulnerabilities from time to time, just like any other software package. So follow other good practices: validate all input, use a WAF and keep your packages up to date, and you should be fine.

Prepared statements

This is more of a legacy option and should be avoided because compared to ORM it has a significantly higher risk of human error. But it still beats plain string concatenation. This approach could look like this:

String query = "SELECT * FROM documents WHERE ownerId=? AND documentName = ?";
PreparedStatement ps = conn.prepareStatement(query);
ps.setString(1, authContext.getUserId());
ps.setString(2, request.getParameter("docName"));
ResultSet rs = ps.executeQuery();

In theory, this is safe. But in my experience, as the codebase grows larger, mistakes start to creep in. And you only need one slip to completely vulnerable. Edge cases such as arrays (documentId IN ("foo", "bar")) are where the blunders often happen.

So if you decide to go with this method, be very careful with it.

Web Application Firewalls

WAF products also are not a silver bullet, and should not be considered a security control per se. But they are an awesome additional security layer and usually quite effective against SQL injection attacks.

An excellent open-source solution is to deploy Apache with ModSecurity CRS in front of your web application.

https://modsecurity.org/rules.html

Database Firewalls

Depending on your database product and budget, you might want to consider giving database firewalls a try. I haven't tried one personally, but such things do exist.

https://www.mysql.com/products/enterprise/firewall.html

Conclusion

SQL injection is at its heart a simple injection vulnerability. And like all injection vulnerabilities, you can prevent it by using a proper library/framework to construct the protocol, in this case, SQL.

ORM is safer than prepared statements. And if you need low-level control of the queries, use a lower level ORM often referred to as a query builder. A WAF product can add a security layer, but you should never rely on it for your security.

Get the web security checklist spreadsheet!

☝️ Subscribe to AppSec Monkey's email list, get our best content delivered straight to your inbox, and get our 2021 Web Application Security Checklist Spreadsheet for FREE as a welcome gift!

Don't stop here

If you like this article, check out the other application security guides we have on AppSec Monkey as well.

Thanks for reading.

DEV Community