Arguably, the best technique to protect against SQL injections is a method called prepared statements. Prepared statements are predefined SQL queries that take user input and place them into placeholders using array syntax.

Consider this insecure SQLite database query that directly constructs the query string from user input:

db.get(`SELECT * FROM Employee  WHERE FirstName = ${req.body.firstName} AND LastName =  ${req.body.lastName}`, (error, results) => {
  ...
});

The query can be reconstructed to use the following array syntax, where the first element replaces the first question mark, and the second element replaces the second question mark.

db.all("SELECT * FROM Employee  WHERE FirstName = ? AND LastName = ? ", 
  [req.body.lastName, req.body.firstName], 
  (error, results) => {
  ...
});

This minimal change ensures that the input strings are properly escaped and special characters are removed, preventing SQL injection attacks. Let’s try this out in our workspace!

In the workspace is a form where employees can input their Last Name and get their personal employee records. For example, an employee named “Andrew Adams” can input “Adams” to get their records.

A disgruntled employee discovered that they can get all the employee records by submitting malicious code into the input field.