/jul 9, 2018

How to Prevent SQL Injection in Node.js

By Bipin Mistry

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.

Blog SQL Injection Form

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:

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 = ?",
    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.

Related Posts

By Bipin Mistry

Bipin Mistry is Sr. Director of Product Management for WAS/IAST product line.  Prior to joining Veracode he was VP Product Management for NEC/Netcracker in their SDN/NFV and Security business unit.  At NEC/Netcracker Bipin’s primary focus is to develop solutions and architectures specifically mapped to NFV/SDN and Orchestration. He has over 28 years expertise in Security, Software Architectures, Mobile and Core Networking Technologies, Product Management, Marketing, Engineering and Sales.  Prior to joining NEC/Netcracker Bipin was VP President of Product Management for a security startup in the field of DDoS analysis and mitigation.  Bipin has also held architectural and management roles at both Juniper Networks (Chief Mobile Architect) and Cisco Systems (Sr. Director of SP Architecture).

Bipin lives Shrewsbury MA with his wife and 2 children.  In his spare time Bipin is a keen runner and is currently attempting to learn Spanish.