Preventing SQL Injection on Node.js

I’ve recently moved into an application security part-time role at work and felt the need to document some of my learnings. This article, or any security-related articles, are not intended for use in hacking. I am in no way responsible if you try to use this knowledge for any malicious activity. I am in no way a security expert, if you try anything in this article you will get caught. I have also been vague on certain parts of the article.

SQL injection may be the most famous attack vector in computer programming. A successful SQL injection can net all sorts of sensitive information stored on a database. The reality is that we live in a data-driven world and that data has to be stored somewhere. As technology has become more widespread companies have started investing in security to prevent these attacks but bad actors are always evolving and improving their attack vectors. Below I’ll go over what a possible SQL injection may look like and how to prevent this attack.

The Flaw

Let’s start with some vulnerable code. This Gist shows the skeleton code of an authorization. The username and password will come off the HTTP request. The sqlQuery contains a query that has been built to authenticate the incoming credentials. The SQL query is then executed on line 6.

The major flaw in this code is the SQL query and the lack of sanitization of username and password, the inputs. What would happen if a password with an unmatched quote is input into our query? The database would interpret it was a string delimiter and cause a 500 error/SQL syntax error. This alone is pretty harmless but it opens the door to malicious attacks on the database.

The Attack

Knowing that the authorization query can is vulnerable to manipulation opens the function up to all sorts of possible injections. Let's say the bad actor wants to get authenticated without a password. Using the string literal the actor could append a statement that will always resolve to true onto the end of the query and use # to comment out the end of the query making it syntactically valid. If the manipulated query is syntactically valid and true the actor will be successfully authenticated. More common goals of SQL injection are data retrieval or snooping on the competition.

The Fix

The most impactful fix for SQL injection is sanitization, any input a program receives should be sanitized. Most languages have some framework that will provide a sanitization method, a quick search on npm will give an idea of the sanitization libraries available for Node.

A whitelist approach can also be taken with validation. Only data that fits a specified pattern should be allowed, rather than a blacklist approach of trying to reject bad patterns. Some things that the whitelist could check for are size, format, and expected values of the input.

A prepared statement or parameterized query should be used instead of concatenation. A prepared statement serves to abstract the SQL syntax from any input parameters. As a bonus prepared statements will increase code readability due to the SQL logic being separated out from the rest of the code. Below is our authentication Gist example, now using a prepared statement.

The big changes here are that the sqlQuery now uses placeholders for username and password. The query is then passed to a preparedStatement object which is passed to a prepare function. The input method takes in a username or password and the parameter variable type. Next, the query is executed. The code above utilizes the prepared statement to ensure any parameters are automatically escaped.

Finally, stick to the Principle of Least Privilege. This principle applies across architecture and tends to be something every computer scientist learns early in their career so I won’t elaborate, check out Wikipedia if you need a refresher.

Conclusion

SQL injection attacks have been around since 1998 and are still the most common attack vector. Because this is such a common attack it is not uncommon to get asked about prepared statements in interviews. As a software engineer, it's essential to know how to write secure code that can withstand this attack. Coding to stop an injection is no more difficult than coding without prepared statements and may actually boost the readability of code, why not code securely?