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.
Instructions
In the workspace, we have a web application with an input box vulnerable to SQL injection.
Start the server by running node app.js
in the terminal and refresh the workspace browser.
Next, explore the form submission by submitting Adams
to the Last Name
field. Then cause a SQL injection by submitting 1' OR '1' = '1
into the Last Name
field.
This SQL injection will give you all the employee information in the database.
Look over to the code. The insecure code is in the /info
route in app.js. Change the SQL query into a prepared statement using array syntax.
Restart the node server, and try to submit 1' OR '1' = '1
in the Last Name
field to cause a SQL injection again. The output should look different. Try inputting Adams
as a valid employee last name.