Building Secure RESTful APIs with NestJS: A Practical Guide/

...

/

TypeORM QueryBuilder

TypeORM QueryBuilder

Discover what can be done with QueryBuilder and practice using it.

We'll cover the following...

QueryBuilder

TypeORM QueryBuilder is a powerful feature. We can use it to construct SQL queries programmatically with a chainable interface. Chainable means linking or connecting multiple functions or methods in a sequence. When constructing SQL queries with a chainable interface, each part of the query can be added sequentially, making it more flexible and readable.

QueryBuilder supports a variety of operations for selecting, filtering, sorting, and manipulating data within the database tables.

In this lesson, we’ll explore how this feature empowers us to construct complex and efficient database queries within our NestJS applications.

Select query

Repository API and entity manager are two ways to interact with the database. We can create the QueryBuilder with either one.

Repository API

Let’s convert the getById method to use QueryBuilder using repository API.

Press + to interact
// address.service.ts
// use Repository API
async getById(id: number) {
  return await this.addressRepository
    .createQueryBuilder('address')
    .where('address.id=:id', {id})
    .getOne();
}

We use the repository.createQueryBuilder method to create a new instance of QueryBuilder and construct a query that retrieves a single address entity based on the id value.

Query parameters and SQL injection

In the example above, there is a where condition: where('address.id=:id', {id}). It specifies the :id query parameter.

Why don’t we write it as where('address.id=' + id)?

Using the string concatenation can lead to potential SQL injection risk. Here is an example:

Press + to interact
// a user provides input for a simple login query using string concatenation:
const userInput = "'; DROP TABLE users; --";
const queryString = `SELECT * FROM users WHERE username = '${userInput}'
     AND password = 'somepassword'`;
// the resulting query becomes the following:
SELECT * FROM users WHERE username = ''; DROP TABLE users;
     --' AND password = 'somepassword';
...