DEV Community

loading...
Cover image for TypeORM - Prevent SQL Injection with Node.js, React and TypeScript

TypeORM - Prevent SQL Injection with Node.js, React and TypeScript

Yoshiaki Matsutomo
Work for a security start-up company as a Full-stack Engineer in Japan
Updated on ・9 min read

Introduction

I have demonstrated some data manipulations with ORM(Object-Relational Mapping) frameworks such as TypeORM so far. Today's topic is still cases with TypeORM. 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.

Demo Application for SQL injection tutorials

Utilization of ORM is Safe?

Some experienced developers mention that ORM(Object-Relational Mapping) frameworks such as TypeORM support prevent SQL injection, which is a traditional security matter. However, I still find high-risk cases with ORM frameworks in GitHub repositories, public project spaces, or in my work experience.

Example High-Risk Code with TypeORM

This is a typical high-risk case with TypeORM even though they use the ORM framework that prevents the SQL injection.

    // 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}%')`
    );
Enter fullscreen mode Exit fullscreen mode

Why ORM is Safe?

Let's step back once. We should review the traditional way of executing SQL.


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

await connection.end();
Enter fullscreen mode Exit fullscreen mode

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

react_demo_app_screenshot_1

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

SELECT * 
FROM users 
WHERE clientId = 1 
  AND name LIKE '%%' OR 1=1 -- %; 
Enter fullscreen mode Exit fullscreen mode

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

On the other hand, most developers basically utilize ORM frameworks in the modern age. Below is the case with TypeORM.

await this.userRepository.find({
  where: [
    { clientId: clientId, 
      name: Like(`%${name}%`) },
  ],
});
Enter fullscreen mode Exit fullscreen mode

The code generates the same SQL code as
SELECT * FROM users WHERE clientId = 1 AND name LIKE '%{{name}}%';. However, ORM frameworks treat the value, name as prepared statements, not partial SQL syntaxes. Of course, the return value is empty.

react_demo_app_screenshot_3

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

POINT: External Authentication
Some enterprise applications utilize external authentication provided by Firebase Auth and Auth0. 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, 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

Solution

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

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

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, which is the development in Node.js/TypeScript 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, 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.

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,
        };
      }
    );
  }
}
Enter fullscreen mode Exit fullscreen mode

It would be best if you maximize the full benefits of TypeORM and do not execute the raw SQL. However, at least, you should use the prepared statements for security reasons like below if you need.


--------

@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,
        };
      }
    );
  }
}
Enter fullscreen mode Exit fullscreen mode

But again, you should not do that for code-reviewers. Can you recognize the difference between the raw SQL with and without prepared statements?

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 to React applications in Node.js/TypeScript

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.

Demo Application for SQL injection tutorials

github_repo_screenshot

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/Go/React 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,

Discussion (0)