What is SQL Injection? How to Prevent SQL Injection Vulnerabilities

SQL injection vulnerabilities arise when you make unsafe database queries. Simply put, users can view your website’s database by entering a query into the URL or filling in the form. Don’t take SQL injection lightly because it’s in Top 10 web security vulnerabilities according to OWASP 2020 announcement there.

How to exploit SQL Injection vulnerability

The simplest is to use the SQLi auto exploit tool. I usually use SQLmap, because according to experience this is the tool with the best possible exploitation. But the downside is that you have to use commands instead of interfaces.

Or you can use the tool SQL Dumper has an interface with more features that are easier to set up. If you are new to learning, you can try this tool to exploit the Website’s database.

How to prevent SQL injection vulnerabilities?

The best way to prevent SQL injection vulnerabilities is to use a framework that allows you to securely filter input data before it enters the database. ORM (Object Relational Mapper) is a good option that you should try. For additional layers of security, validate all input and use WAF (Web Application Firewall).

Simple example

Let’s say I have a Java application that allows users to retrieve their documents by ID. I can do it like this:

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

If the user ID is 25 and the URL is https://www.example.com/documents/?docName=ABC123, the query would be:

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

Still fine, right? But what if the URL is https://www.example.com/documents/?docName=ABC123’OR’1’=’1?

Now I will get the following query that returns all documents of all users (because 1 = 1 is always true):

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

So how to avoid this error?

Using Object Relational Mapping

Taking Java as an example, using an ORM such as hibernate to implement JPA (Java Persistence API) might look like this.

First, define the model.

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

Then define the class repository.

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

Finally, you can use the repository and fetch the documents as follows:

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

The ORM will handle all parameters safely. Now suppose you want more control over the queries. In that case, many ORMs provide query builder you can use, such as the Hibernate Criteria API.

If you use Python, Django has an equally great ORM; If you don’t use Django, sqlalchemy is a great option.

PHP has Doctrine. You just need to google to search for ORMs that match the technology of your choice.

Warning

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();