How to Prevent SQL Injection in Node.js

While Node.js can help improve developer productivity, it’s prone to SQL injection bugs as much as any other modern programming language in use today.

In this article, we walk through a SQL injection issue, it’s impact, and how to fix it.

A classic example using MySQL

Imagine a web app that requires you to verify your bank account so you can access your personal funds. Upon verifying some information, the system will aggregate and display your total balance so you can do things like bank transfers, make payments, etc.

Behind the scenes, a Node.js developer has implemented the following code:

connection.query("SELECT * FROM bank_accounts WHERE dob = '" + req.body.dob + "' AND bank_account = '" + req.body.account_number + "'",
    function(error, results) {

    }
);

What’s wrong with this code? Well, aside from questionable verification logic (your date of birth is the only extra factor required?!), the code allows user input to pass through as-is. No escaping or character filtering whatsoever.

How do I fix SQL injection?

The fix is straightforward. You need to tweak your code so any user input is automatically escaped before being executed. With MySQL, you can specify which variables get escaped within the query() method itself.

You have two options for fixing this:

Placeholders
You can map values in the array to placeholders (the question marks) in the same order as they are passed.

connection.query("SELECT * FROM bank_accounts WHERE dob = ? AND bank_account = ?",
    [
     req.body.dob,
     req.body.account_number
    ],
    function(error, results) { 
    
    }
);

Named Placeholders
This is nearly identical to the above example, however attribute names inside the object become the placeholders in the SQL query.

connection.query("SELECT * FROM bank_accounts WHERE dob = :dob AND bank_account = :account_number",
    {
     dob: req.body.dob,
     account_number: req.body.account_number
    },
    function(error, results) { 
    
    }
);

What could happen if I don’t fix this code?

When you don’t properly escape user input, MySQL will execute the string exactly the way it was entered. This means any characters that include SQL syntax will be considered as part of the overall SQL query.

A variety of hacks can come from this. We’ll cover two examples: data exfiltration and destructive queries.

Data exfiltration
Imagine if you enter this string into the date of birth form field:

1970/01/01’; --

This is how MySQL interprets it:

SELECT * FROM bank_accounts WHERE dob = '1970/01/01';  -- ' AND bank_account = '1111987654321'

As a result, only part of the query logic runs. The additional SQL logic that requires a bank_account number is ignored. In our banking use case, this would give the user access to all the customers with a birthday of January 1st, 1970!

Even if your web app has safeguards that throw an exception if a large number of rows are returned, attackers can bypass such countermeasures by simply adding a 'LIMIT' clause to their attack string.

Destructive SQL injection
Now, let’s expand the query just a bit. We will add a DROP command to the attack:

1970/01/01'; DROP TABLE bank_accounts; --

This is how MySQL interprets it:

SELECT * FROM bank_accounts WHERE dob = '1970/01/01'; DROP TABLE bank_accounts; -- ' AND bank_account = '1111987654321'

You’ve effectively told MySQL to run two queries, one of which drops the bank_accounts table!

Don't rely on custom filtering

You may be saying to yourself: "I’m already applying a custom filter to user input before passing it into the SQL query - so I don’t need to change my code any further."

Generally speaking, that's a risky proposition. There’s a good chance an attack pattern exists that you haven’t thought of that could sneak through your own character filtering logic. Of course, your custom filter logic may make it harder for an attacker to be successful -- but not impossible. For a few targeted code changes, you can rest easier at night.

In the data exfiltration example, no database or operational control could stop a malformed query. Simply put, the attack string modified the SQL query in such a way that additional filter criteria was being ignored. This is a sure-fire way to return the wrong data to your users!

With regard to destrucive queries, you may be telling yourself: "we don’t allow the DROP command to be used in production”. That’s a good safeguard, but it’s possible that production configurations may change overtime, and sometimes accidentally. Operational controls and safeguards are important aspects of defense-in-depth, but they are not guarantees. Like custom character filtering logic, they may make it harder for an attacker to break-in; but such controls are usually "bandaids" to temporarily reduce risk while you fix the underlying code.

Bottom-line: Fixing your code is the only way to prevent SQL injection!

Stay tuned for a future post that shows you how to prevent NoSQL injection and other bugs!

Want a demo of Veracode Interactive Analysis?

Veracode Interactive Analysis (IAST) helps teams instantly discover vulnerabilities in their applications at runtime by embedding security into their development processes and integrating directly into their CI/CD pipelines. Get a demo.