TypeORM - Prevent SQL Injection with Node.js, React and TypeScript
Tags: node, security, sql, typescript
Published: 2021-06-08 12:11:50

---
title:TypeORM - Prevent SQL Injection with Node.js, React and TypeScript
description: The article demonstrates how we should prevent SQL injection with TypeORM in Node.js, React and TypeScript.
series: TypeORM
tags: node, security, sql, typescript
---

##Introduction
I have demonstrated some data manipulations with [ORM(Object-Relational Mapping)](https://en.wikipedia.org/wiki/Object%E2%80%93relational_mapping) frameworks such as [TypeORM](https://typeorm.io/#/) so far. Today's topic is still cases with [TypeORM](https://typeorm.io/#/). However, it focuses on the security, which is my main field at the moment. I think you understand why I select topics regarding complex queries, performance, and the difference from the Microsoft ecosystem.

##Demo Application
I developed a demo application in [GitHub](https://github.co.jp/).

[Demo Application for SQL injection tutorials](https://github.com/y-matsutomo/blogs_demo_app_about_sql_injection)

##Utilization of ORM is Safe?
Some experienced developers mention that [ORM(Object-Relational Mapping)](https://en.wikipedia.org/wiki/Object%E2%80%93relational_mapping) frameworks such as [TypeORM](https://typeorm.io/#/) support prevent [SQL injection](https://en.wikipedia.org/wiki/SQL_injection), which is a traditional security matter. However, I still find high-risk cases with [ORM](https://en.wikipedia.org/wiki/Object%E2%80%93relational_mapping) frameworks in [GitHub](https://github.co.jp/) repositories, public project spaces, or in my work experience.

###Example High-Risk Code with TypeORM
This is a typical high-risk case with [TypeORM](https://typeorm.io/#/) even though they use the [ORM](https://en.wikipedia.org/wiki/Object%E2%80%93relational_mapping) framework that prevents the [SQL injection](https://en.wikipedia.org/wiki/SQL_injection).

```TypeScript
    // TO-DO temporary solution
    // I need to update it when I have a spare time
    const users = await this.userRepository.query(
      `SELECT user.id as id,
        user.firstName as firstName,
        user.lastName as lastName,
        user.age as age,
        user.clientId as clientId,
        user.createdDateTime as createdDateTime,
        user.updatedDateTime as updatedDateTime,
        Client.companyName as 'clientCompanyName',
        Client.createdDateTime as 'clientCreatedDateTime',
        Client.updatedDateTime as 'clientUpdatedDateTime'
      FROM User INNER JOIN Client ON user.clientId = Client.id 
      WHERE clientId = ${clientId} 
        AND (firstName LIKE '%${name}%' 
        OR lastName LIKE '%${name}%')`
    );
```

##Why ORM is Safe?
Let's step back once. We should review the traditional way of executing SQL. 
```TypeScript

const users = await connection.query(
  `SELECT * 
   FROM users 
   WHERE clientId = ${clientId} 
    AND name LIKE %${name}%;`);

await connection.end();
```

How do you think if we enter `%' OR 1=1 --` in the form to search users?

![react_demo_app_screenshot_1](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qssz4jepv26719y9n3gq.png)


The method accepts the value, and the generated SQL is: 

```sql
SELECT * 
FROM users 
WHERE clientId = 1 
  AND name LIKE '%%' OR 1=1 -- %; 
```

The application should return only users of a client ID, 1, but this actually returns all users including the other clients'. 

![react_demo_app_screenshot_2](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gtobzre7vllt4cmb7jsm.png)

On the other hand, most developers basically utilize [ORM](https://en.wikipedia.org/wiki/Object%E2%80%93relational_mapping) frameworks in the modern age. Below is the case with [TypeORM](https://typeorm.io/#/).
```TypeScript
await this.userRepository.find({
  where: [
    { clientId: clientId, 
      name: Like(`%${name}%`) },
  ],
});
```

The code generates the same SQL code as
`SELECT * FROM users WHERE clientId = 1 AND name LIKE '%{{name}}%';`. However, [ORM](https://en.wikipedia.org/wiki/Object%E2%80%93relational_mapping) frameworks treat the value, `name` as [prepared statements](https://en.wikipedia.org/wiki/Prepared_statement), not partial SQL syntaxes. Of course, the return value is empty.

![react_demo_app_screenshot_3](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pimskl7t69sk4zqdh0s6.png)

>**POINT**: Prepared Statements
The certain values are left unspecified when the SQL is sent and compiled. The application binds the values to the parameters, and the database executes the statement.

##This is Serious Vulnerability?
Some people do not think this is a critical issue. They might say:
> Hey Yoshi! Do not worry too much. The application just displays user's information which does not include their privacy, such as their password. Take it easy!

###UNION-based SQL Injection
If an attacker notices that the system has a security hole here, they will enter `%' OR 1=1 UNION SELECT 'fakeName' as companyName, email as firstName, password as lastName FROM Login; --` to get login details. "First Name" column has user's login email addresses and "Last Name" column has their password.

![react_demo_app_screenshot_4](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pikonc5s6hzmbfgnkand.png)

>**POINT**: External Authentication
Some enterprise applications utilize external authentication provided by [Firebase Auth](https://firebase.google.com/products/auth) and [Auth0](https://auth0.com/jp). However, I also know that some companies still manage legacy applications with old-fashioned frameworks, which are not supported by them. 

>**POINT**: Password Encryption
The sample app stores the password as plain text, which is not encrypted, and you might think that you are okay because your app encrypts critical data, but do you adopt the latest encryption methodology? 

##This is Still Serious Vulnerability?
Some people still do not think that this is a critical issue, and they might say:
> Hey Yoshi! Do not worry! Crackers have no idea about all table schemas including a login table. If not, they are not able to execute it. Take it easy, man!

For [SQLite](https://www.sqlite.org/index.html), they will enter `%' OR 1=1 UNION SELECT 'fakeName' as companyName, sql as firstName, 'fakelastname' as lastName FROM sqlite_master; --` to show table schemas. "First Name" column has tables' information.

![react_demo_app_screenshot_5](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fr72qavsg8ucj0aoi5c8.png)

##Solution

The first code should be replaced by the below code. Yes, this is simple.

```TypeScript
     const users = await this.userRepository.find({
      where: [
        { clientId: clientId, firstName: Like(`%${name}%`) },
        { clientId: clientId, lastName: Like(`%${name}%`) },
      ],
      relations: ["client"]
    });
```

##Causes of Vulnerability
Some C-level or managers assume that the cause is the lack of education for junior developers, and they attempt having the security training or pair-work with senior engineers.

Is this true?

The fundamental knowledge of secure coding is mandatory. However, to be honest with you, this might be one of the reasons and I have to conclude this is not correct. I deeply investigated and found some potential causes.

###Lack of Third-party's Module Information
I moved from the Microsoft development world to [OSS](https://en.wikipedia.org/wiki/Open-source_software), which is the development in [Node.js](https://nodejs.org/en/)/[TypeScript](https://www.typescriptlang.org/) with third-party modules. I think that their official documents provide too general and basic information only in comparison with Microsoft.

They basically provide the cases with detailed examples and combination of libraries. It is easy because most libraries are provided by Microsoft themselves! 

For instance, with [TypeORM](https://typeorm.io/#/), I found the below code that developers use the official ways for simple code. However, if they have to write complex data operations such as creating join tables or executing with subqueries, they copy and paste the raw SQL and leave it once.
  
```TypeScript
import { Service, Token } from "typedi";
import { Repository, UpdateResult } from "typeorm";
import { InjectRepository } from "typeorm-typedi-extensions";
import { Client } from "../entity/Client";
import { User } from "../entity/User";

export interface IRiskyUserUsecase {
  getAll(clientId: number): Promise<User[]>;
  save(user: User): Promise<User>;
  update(id: number, user: User): Promise<UpdateResult>;
  remove(user: User): Promise<User>;
  search(clientId: typeof Client.prototype.id, name: string): Promise<User[]>;
}
export const RiskyUserUsecaseToken = new Token<IRiskyUserUsecase>();

@Service(RiskyUserUsecaseToken)
export class RiskyUserUsecase implements IRiskyUserUsecase {
  @InjectRepository(User)
  private userRepository: Repository<User>;

  async getAll(clientId: number) {
    return await this.userRepository.find({ clientId: clientId });
  }

  async save(user: User) {
    return await this.userRepository.save(user);
  }

  async update(id: number, user: User) {
    return await this.userRepository.update(id, user);
  }

  async remove(user: User) {
    return await this.userRepository.remove(user);
  }

  // TO-DO temporary solution
  // I need to update it when I have a spare time
  async search(clientId: typeof Client.prototype.id, name: string) {
    const users = await this.userRepository.query(
      `SELECT user.id as id,
      user.firstName as firstName,
      user.lastName as lastName,
      user.age as age,
      user.clientId as clientId,
      user.createdDateTime as createdDateTime,
      user.updatedDateTime as updatedDateTime,
      Client.companyName as 'clientCompanyName',
      Client.createdDateTime as 'clientCreatedDateTime',
      Client.updatedDateTime as 'clientUpdatedDateTime'
      FROM User INNER JOIN Client ON user.clientId = Client.id 
      WHERE clientId = ${clientId} AND (firstName like '%${name}%' OR lastName like '%${name}%')`
    );
    return users.map(
      (u): User => {
        return {
          id: u.id,
          firstName: u.firstName,
          lastName: u.lastName,
          age: u.age,
          clientId: u.clientId,
          client: {
            id: u.clientId,
            companyName: u.clientCompanyName,
            createdDateTime: u.clientCreatedDateTime,
            updatedDateTime: u.clientUpdatedDateTime,
          },
          createdDateTime: u.createdDateTime,
          updatedDateTime: u.updatedDateTime,
        };
      }
    );
  }
}
```

It would be best if you maximize the full benefits of [TypeORM](https://typeorm.io/#/) and do not execute the raw SQL. However, at least, you should use the [prepared statements](https://en.wikipedia.org/wiki/Prepared_statement) for security reasons like below if you need.

```TypeScript

---

@Service(RiskyUserUsecaseToken)
export class RiskyUserUsecase implements IRiskyUserUsecase {
  @InjectRepository(User)
  private userRepository: Repository<User>;

  // TO-DO temporary solution
  // I need to update it when I have a spare time
  async search(clientId: typeof Client.prototype.id, name: string) {
    const users = await this.userRepository.query(
      `SELECT user.id as id,
      user.firstName as firstName,
      user.lastName as lastName,
      user.age as age,
      user.clientId as clientId,
      user.createdDateTime as createdDateTime,
      user.updatedDateTime as updatedDateTime,
      Client.companyName as 'clientCompanyName',
      Client.createdDateTime as 'clientCreatedDateTime',
      Client.updatedDateTime as 'clientUpdatedDateTime'
      FROM User INNER JOIN Client ON user.clientId = Client.id 
      WHERE clientId = :clientId AND (firstName like '%:name%' OR lastName like '%:name%')`,
      [{ clientId: clientId }, { name: name }]
    );
    return users.map(
      (u): User => {
        return {
          id: u.id,
          firstName: u.firstName,
          lastName: u.lastName,
          age: u.age,
          clientId: u.clientId,
          client: {
            id: u.clientId,
            companyName: u.clientCompanyName,
            createdDateTime: u.clientCreatedDateTime,
            updatedDateTime: u.clientUpdatedDateTime,
          },
          createdDateTime: u.createdDateTime,
          updatedDateTime: u.updatedDateTime,
        };
      }
    );
  }
}
```

But again, you should not do that for code-reviewers. Can you recognize the difference between the raw SQL with and without [prepared statements](https://en.wikipedia.org/wiki/Prepared_statement)? 

It must be tough!

###Upgrade of Old-Fashioned Applications
We sometimes develop pure new applications = we can select the appropriate frameworks and environments, which are the modern architecture. 

On the other hand, we have to convert from the old applications to ones with the modern frameworks. For example:

1. Conversion from Web Form applications in .Net to MVC App/SPA in .Net Core
2. Conversion from MVC applications in [PHP](https://www.php.net/) to [React](https://reactjs.org/) applications in [Node.js](https://nodejs.org/en/)/[TypeScript](https://www.typescriptlang.org/) 

In this case, it might occur the above case because developers try completing the task within limited time allocation = they  use existing code.    

###Error of Human Resource/Time Allocation
I assume that this is the main reason. Developers handle their tasks with limited human/time resources every day, even though it is sometimes unreasonable and puts pressure on them. It is fantastic if they can always choose the best solution, but it is difficult.

Managers and leads, do you think that you understand the current situation and manage it properly?

It is unacceptable for us to fire developers making security mistakes even though nobody wants to write insecure code, and it often happens due to external factors. Without thinking about why they made such a choice, it would not be the ultimate solution. 

##Potential Solutions for Developers
1. Review the parts of comment out with TO-DO and temporary solutions once - Developers and code-reviewers might not imagine how simple mistakes pose critical incidents. It is good timing to review them.   
2. Security issues would come from human errors - Please do not focus on improving their security skills/knowledge. This is significant but you would also need to review your team management and development environment.

##Potential Solutions by Publishers
1. Provide intermediate/senior-level information and a variety of examples as well - If the information is too general, developers might select lazy ways even though they do not want to.
2. Provide information regarding the utilization of the modern  modules/library's combination - I faced multiple times that when I used the third-party module independently, it worked well. But when I used it with the other modules, it did not work.  If developers have trouble implementing complex tasks with multiple third-party modules, they might select temporary solutions. 

##Demo Application
I developed a demo application in [GitHub](https://github.co.jp/).

[Demo Application for SQL injection tutorials](https://github.com/y-matsutomo/blogs_demo_app_about_sql_injection)

![github_repo_screenshot](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/c31floxy87nm8skiiqcq.png)

##Conclusion
I will continuously publish security articles. However, I also understand the importance of providing the technical skills/knowledge in a non-security field. I believe that if we can provide helpful information about the development itself or project management, it would indirectly prevent security matters.

We are with you, friends.

##Bio
When I was 30 years old, I went to Australia for changing my career on a Working Holiday visa. I graduated from University of Sydney with a Bachelor of Computer Science and Technology. During the period, I also worked as a Fraud Prevention Analyst at Amex in Sydney, Australia (yes, it was super busy. I went to my uni during the day and worked at night...) 

After graduation, I worked as a C#/.Net developer for an Australian small business for 5 years. Now, I came back to Japan and work as a [TypeScript](https://www.typescriptlang.org/)/[Go](https://golang.org/)/[React](https://reactjs.org/) developer for a Japanese security start-up company. 

I love learning new fields which is a big challenge. I am happy if you support me to accelerate the improvement of my skill/knowledge. Please feel free to contact me if you are interested in my unique career.

Thanks,