SECRET OF CSS

Preventing SQL Injection Attacks in Node.js | by Dsrao | Jul, 2022


Stop doing this in your SQL queries if you already are

0*2Wv6shsb9giNwmP5
Photo by Caspar Camille Rubin on Unsplash

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.

Confused?

Let’s understand this with the simplest example:

1*6Lqnum01bmkmD EqHXlRfw
SQL injection example

Below is a GET request, asking the backend for sending the user’s data with id = 3:

1*skfTUqnF9uQPqddBVI KWQ
Getting users by ID

The example below shows, how a single change in the query can fetch all the user’s data from the database:

1*20uIv5u9Sz8c8zXO1qYBqg
Addition of(1==1) in the request parameter

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 Persons Table.

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:

1*X6A2
Folder structure

Package Requirements

  • mysql2
  • dotenv (to make sure every private thing stays in the .env file )
  • express

I have set up a dummy table in the MySQL database. To connect the database with the express backend, setup the connection.js file:

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:

Using this code, we will be facing the issue as shown in the first example. Notice, what’s wrong here? As JavaScript developers, we are tempted to use the ES6 template literals. But by doing so, we are simply making our code vulnerable to attacks.

Some of the methods are:

1. Using Placeholders:

Instead of using ES6 literals in the SQL query, we can use the placeholders, and voila!

Corrected SQL query using placeholder
1*3B OFmTrC9AWHomT krA5Q
Output for corrected SQL query

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:

1*CYtdfaFqlI TcuiPV9flEg
GetPeopleByCity api expected output

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:

1*JyQj94L kHhsOUUXbRaUow
Addition of “; — “ resulting in change of output

So the SQL query now becomes:

SELECT * FROM persons WHERE city = Manhattan ; --AND Age = 29

instead of:

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:

Validating input param extracted from the request
1*6ffKWw Orca 61NmJng Cw
Validated input and avoided SQL injection

It works!!

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.



News Credit

%d bloggers like this: