Typescript SQL
Injection Guide:
Examples and Prevention

In this article, we'll discuss SQL injection in the context of Typescript-based applications. Our goal is to examine what SQL injection is, how it can impact your organization's infrastructure, and what measures you can implement to mitigate its damage. By the end of this article, you should have a comprehensive understanding of SQL injection, how the security issues impact your code, and how to address these issues properly. 

We'll use NodeJS as the platform of choice for our sample project and Typescript as the coding language. 

Note: We intend this article for NodeJS and Typescript developers. Therefore, we expect that you have a basic understanding of the NodeJS development stack and experience coding in Typescript. If you haven't dipped your toes in yet, please see the Typescript site for more information. Nevertheless, SQL injection is pretty consistent across platforms and languages, so you should be able to get some value out of this article regardless.

Starting with NodeJS and Typescript

Before we jump into SQL injection, let's prepare a simple boilerplate project so that you can play around with the code and follow through with what you're about to learn. 

First, make sure you have NodeJS installed. You can do this by running the following command in macOS:

Alternatively, you can use Brew to install it with the following command: 

If you run any other system, you can find instructions on the NodeJS site.

Now, creating a NodeJS project is extremely simple. Just create a folder called "nodejs_typescript_sample" (You can call this folder anything you like) and navigate to it. Once inside, create a file called app.js (this time we recommend you really do call it that) and paste the following code:

const http = require('http');

const hostname = '127.0.0.1';
const port = 3000;

const server = http.createServer((req, res) => {
  res.statusCode = 200;
  res.setHeader('Content-Type', 'text/plain');
  res.end('Hello World');
});

server.listen(port, hostname, () => {
  console.log(`Server running at http://${hostname}:${port}/`);
});

You can then go to the terminal and run the code using the following command: 

Visit http://localhost:3000 and you should see a message saying, "Hello World." 

Simple, right?

What Is SQL Injection?

Now, let's briefly explain what SQL injection is. It's a kind of injection attack that takes advantage of inadequate database integration and poor user input validation.  

Malicious SQL instructions inputted into the platform can reach the ORM layer and go directly into the SQL database through user-facing input fields and take over the whole system. 

Since SQL injection attacks target the system database and, when successful, can provide full or partial access, the impact can be massive. Therefore, one could be excused for believing that these breaches are very complex and used only in sophisticated attacks. Yet most SQL injection attacks are, in fact, not particularly complex or rare.  

In fact, the opposite is true.

SQL Injection Example

To illustrate this point, let's look at a typical implementation of a SQL injection attack targeting a system that allows user input. 

Imagine that you have code in your model layer or database integration layer where you can retrieve user information by formatting the following SQL query command:

As you can see, this simple (and very dangerous) query command searches through the Users table and retrieves the user with the matching credentials. 

Any bad actor with some basic understanding of SQL could take advantage of the lack of validation on user inputs by inputting values that the developer did not foresee a valid user providing.  

For example, if you input something like the following to this query, the system will surrender all users in the table to the attacker:

It should now be clear that there is little complexity or sophistication in this attack. It lives and dies on simple input validation. However, this doesn't mean SQL injection attacks can't be complex or be part of a more robust and sophisticated attack. 

If you want to read more about SQL injection, please check out our in-depth post here where we go into more details about it.

Common SQL Injection Attacks

Excluding the ' ""="" ' attacks that we explored earlier, a few more forms of injection attacks are widespread. They allow attackers to successfully target a vulnerable system with enough knowledge of the database structure and trial and error.

• Always true (or ' 1=1 ') attacks 

  • SELECT * FROM Users WHERE UserId = 105 OR 1=1;

• Query stacking attacks 

  • SELECT * FROM products WHERE id = 10; DROP members--

• Data exfiltration (or query comment) attacks 

  • SELECT * FROM health_records WHERE date = '22/04/1999'; -- ' AND id = 33

If you want to learn more and see examples of these attacks, you can find them here.

Mitigating Measures Against SQL Injection

Now that you have a basic understanding of how SQL injection attacks can take advantage of your system, let's look at some simple preventive measures. 

First, it's necessary to address the user input validation implemented in your user-facing front-end code. This validation would be your first layer of defense against bad actors and serve as a responsive mechanism for users. 

We need to make sure the values provided by the user are scoped and sanitized accordingly. That means that, for example, if an input field is intended to receive emails, it does not allow the user to submit the form with an invalid email —or no value at all.  

A simple implementation of this idea would look something like this:

const email_regex = /^(([^<>()[\]\\.,;:\s@"]+(\.[^<>()[\]\\.,;:\s@"]+)*)|(".+"))@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\])|(([a-zA-Z\-0-9]+\.)+[a-zA-Z]{2,}))$/;

function validate(email: string): boolean {
  if (email == "") {
    alert("Email must be filled out");
    
    return false;
  } else if (email_regex.test(email.toLowerCase()) == false) {
    alert("Email must be valid");
  
    return false;
  }

  return true;
}

Of course, we can expand this approach with input masks and responsive form styling. This serves to inform the user that the value provided is not valid, improving the user experience. 

Second, we must implement input validation at the application level, where most of the business layer exists. This strategy could be as simple as revalidating and, when appropriate, sanitizing the user input before reaching the Model layer.  

Additionally, adding a third-party library like "node-mysql" provides a more robust layer of protection against these attacks.

Database Layer

Finally, once the issues at the top level are addressed, we can work on securing the database layer.  

All we need to do is implement what is known as query placeholders or name placeholders. These placeholders, indicated here with the ? symbol, tell the interfacing layer to automatically escape the input passed to it and validate its type and format so that it complies with the database structure.  

For example, if a string is given to a column expecting an integer, the query aborts, throwing an exception. 

Let's say you had a method that retrieved sensitive information and passed the user input unvalidated.

app.post("/records", (request, response) => {
  const data = request.body;
  const query = `SELECT * FROM health_records WHERE id = (${data.id})`;

  // === MySQL ===
  const mysql = require('mysql');

  const mycon = mysql.createConnection({ host: host, user: user, password: pass, database: db });

  mycon.connect(function(err) {
    mycon.query(query, (err, rows) => {
      if(err) throw err;

      response.json({data:rows});
    });
  });
});

This code could lead to a lot of trouble.  

But addressing the issue is quite simple.

app.post("/records", (request, response) => {
  const data = request.body;

  // === MySQL ===
  const mysql = require('mysql');

  const mycon = mysql.createConnection({ host: host, user: user, password: pass, database: db });

  mycon.connect(function(err) {
    mycon.query('SELECT * FROM health_records WHERE id = ?', [data.id], (err, res) => {
      if(err) throw err;

      response.json({data:rows});
    });
  });
});

As you can see, the query instruction now uses the ? character as a placeholder to provide the parameters. This tells the query library to sanitize and validate the inputted value against injection. It is a subtle change, but it has a significant impact on the security of our code. 

To Summarize: How to Prevent Typescript SQL Injection

We explored SQL injection and included some examples, and we can summarize the best strategy this way: 

• Implement input validation and field masking at the View level.

• Ensure that your Model layer properly uses placeholders. 

• Avoid insecure packages that have access to the database.

• Make use of application security monitoring features.

• Enforce security policies and best practices with your team.

Complying with proper SQL injection prevention practices is not complicated.  

Thanks to the robustness and battle-tested approaches and libraries at our disposal, we can provide a solid level of protection without much work. However, depending on the size and complexity of the codebase, your mileage might vary.  

Nevertheless, the time investment that this protection requires will pay dividends for years to come. 

This post was written by Juan Reyes. Juan is an engineer by profession and a dreamer by heart who crossed the seas to reach Japan following the promise of opportunity and challenge. While trying to find himself and build a meaningful life in the east, Juan borrows wisdom from his experiences as an entrepreneur, artist, hustler, father figure, husband, and friend to start writing about passion, meaning, self-development, leadership, relationships, and mental health. His many years of struggle and self-discovery have inspired him and drive to embark on a journey for wisdom.