Learn

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

1.

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.

2.

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.

3.

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.

Sign up to start coding

Mini Info Outline Icon
By signing up for Codecademy, you agree to Codecademy's Terms of Service & Privacy Policy.
Already have an account?