Stop doing this in your SQL queries if you already are
Wondering what is SQL injection?
SQL injection is one of the most popular web hacking techniques which allows the attacker to peep into your database by simply injecting some lines of code.
The attacker can read, modify and even delete data from the database by simply making some changes in the parameters while sending a request to the server and thus modifying the original SQL query.
Let’s understand this with the simplest example:
Below is a GET request, asking the backend for sending the user’s data with
id = 3:
The example below shows, how a single change in the query can fetch all the user’s data from the database:
Note: This is one of the simplest examples to understand SQL injection
What happens behind the scenes?
In the above example, the user is sending query as
id = 3 OR (1 = 1).
When this query is extracted from the request in the backend, it treats the entire thing as one string which is further processed in the SQL query.
Technically the SQL query should be:
SELECT * from Persons WHERE PersonId = 3
But now with the given input, the SQL query becomes:
SELECT * from Persons WHERE PersonId = 3 OR (1 = 1)
As (1 = 1) always holds true, it returns all the Users existing in the
Definitely, you don’t want this issue in your next dev project!
Let’s dive deeper into this, by getting our hands dirty and writing some express backend with MySQL database and implement better practices to avoid SQL injection.
We’ll be creating the most minimal express project with this folder structure:
dotenv(to make sure every private thing stays in the .env file )
I have set up a dummy table in the MySQL database. To connect the database with the express backend, setup the
Note: I have set up a .env file to store the connection details
Let’s jump to writing APIs for fetching the Person’s details by ID:
Some of the methods are:
1. Using Placeholders:
Instead of using ES6 literals in the SQL query, we can use the placeholders, and voila!
2. Input Validation
Let’s take one more example to understand why we need to validate our input which has been extracted from the request. Consider fetching details of people aged 29 in a given city. We will be taking the city’s input from the user:
Below is the API for this request:
Suppose, the input city is “Manhattan”.
If the attacker sends the parameter as
“Manhattan”; — instead of
“Manhattan”, then, “ — ” comments out the rest of the SQL query, and “;” acts as a delimiter:
So the SQL query now becomes:
SELECT * FROM persons WHERE city = Manhattan ; --AND Age = 29
SELECT * FROM persons WHERE city = Manhattan AND Age = 29
And hence details of all the users staying in Manhattan are fetched thus affecting the data privacy of the system.
Similar to this example, the attacker can send various combinations of special characters to modify the original SQL query. We can validate the query by checking if the input string has alphabets only or depending upon the use case.
Below is the code for the same:
These were some of the easiest examples to understand SQL injection, and how to prevent them in your node.js projects to avoid crashes. You can get deeper into this topic by looking for language-specific methods to avoid SQL injection. You can also read about different types of SQL injection and its impact on the codebase.
Checkout: SQL Injection Cheat Sheet
Code GitHub link :
Want to Connect?Here's my LinkedIn handle.