TypeORM is an Object-Relational Mapping (ORM) tool that is designed to work with TypeScript. It allows developers to interact with databases using an object-oriented syntax and supports a wide range of popular databases, including MySQL, PostgreSQL, and SQLite. But most probably, you already know that. So the point of that article is not to explain what TypeORM is but to show some unexpected discoveries I made while working with it. Upgrading TypeORM to 0.3.11 Recently I was working on a project that uses TypeORM. The project was quite a bit outdated, and the version of TypeORM was 0.2.*. I decided to upgrade it to the latest version 0.3.11. I was expecting to find some breaking changes, and indeed they made it. Things I liked about the new version Let's say a few words about the incompatible changes I liked. First of all of will no longer return an . Instead, these methods will return . This is a good change, is a value and having the result as means that we did something but had an empty result. , on the other hand, is a type, and it means that we did not do anything. So it is a good change. .findOne() findOneBy() undefined null null null undefined Let's look at the example: import {Entity, Column, PrimaryGeneratedColumn} from "typeorm"; @Entity() export class User { @PrimaryGeneratedColumn() id: number; @Column() name: string; @Column() email: string; } The old behavior: const user = await User.findOne({name: "the name we don't have in the database"}); console.log(user); // undefined Current behavior: const user = await User.findOne({name: "the name we don't have in the database"}); console.log(user); // null Things I'm OK in the new version Next, the changes in API are related to the method. The old version was more flexible and allowed to pass just parameters or the whole object. The new version requires passing the whole object. The new version is strict, and you should always pass the whole object. .find() where FindOptions FindOptions FindOptions The old behavior: const users = await User.find({ name: "Alice" }) The new one: const users = await User.find({ where: {name: "Alice"} }) Lucky for us, the new version has a new method called and will accept just parameters. .findMy() where const users = await User.findBy({ name: "Alice" }) Things I don't like in the new version And last but not least is the changes in handling and values of where parameters. This one came as a surprise for me as I was not able to find any information about that change in the documentation and the changelog. And actually, I stumbled upon that change only in the testing stage. In general, the application I was working on has an impressive test coverage (more than 90%), and REST API has a pretty good validation layer. So it is not easy to send a or parameter to the API. So let me get to the issue itself. null undefined null undefined Let's imagine that in some places, we don't have a proper query params validation, and we can send a or to the API. null undefined Let's have a look at the example: function findUserByEmail(email: string): Promise<User | null> { return User.findOneBy({ email: email }); } In the perfect Typescript world, that function looks just fine, and it is not possible to directly pass or in the code - it will break the compilation. But at the end of the day, it will be compiled into JavaScript and the resulting code will look something like this: null undefined function findUserByEmail(email) { return User.findOneBy({ email: email }); } So it is possible to pass or to the function. null undefined findUserByEmail And now, let's have a look at the generated SQL queries for the following function calls. For the valid email parameter, both versions of TypeORM will generate the same query: select * from user where email = :email limit 1 I'm simplifying the query here, but the point is that the query is the same. But for the and parameters, the query in the new version will be different: null undefined select * from user limit 1 So as you can see, that query will return the first user from the database. And that is not what we want. The reason for this is that TypeORM developers decided to skip the clause if the parameter is null or undefined. In the previous version, such parameters will trigger an error, and the query will not be executed. But in the new version, everything will be skipped, and the query will be executed without the clause. There is a Github issue related to that, but it looks like it is not going to be fixed soon. where where where https://github.com/typeorm/typeorm/issues/9316 So instead of waiting for the fix, I decided to write a small wrapper class to provide an additional layer of params validation. The wrapper class will check if the parameter is or and will throw an error. where where null undefined import {BaseEntity, FindOneOptions} from 'typeorm' import {FindManyOptions} from 'typeorm/find-options/FindManyOptions' import {FindOptionsWhere} from 'typeorm/find-options/FindOptionsWhere' interface Constructable { new(...args: any[]): any } function paramsCheckingMixin<T extends Constructable>(base: T): T { return class extends base { /** * Counts entities that match given options. */ static async count<T extends BaseEntity>( this: { new(): T } & typeof BaseEntity, options?: FindManyOptions<T> ): Promise<number> { await validateWhereOptions(options?.where) return this.getRepository<T>().count(options as FindManyOptions<BaseEntity>) } /** * Counts entities that match given WHERE conditions. */ static async countBy<T extends BaseEntity>( this: { new(): T } & typeof BaseEntity, where: FindOptionsWhere<T> ): Promise<number> { await validateWhereOptions(where) return this.getRepository<T>().countBy(where as FindOptionsWhere<BaseEntity>) } /** * Finds entities that match given options. */ static async find<T extends AppBaseEntity>( this: { new(): T } & typeof AppBaseEntity, options?: FindManyOptions<T> ): Promise<T[]> { await validateWhereOptions(options?.where) return this.getRepository<T>().find(options) } /** * Finds entities that match given WHERE conditions. */ static async findBy<T extends AppBaseEntity>( this: { new(): T } & typeof AppBaseEntity, where: FindOptionsWhere<T> ): Promise<T[]> { await validateWhereOptions(where) return this.getRepository<T>().findBy(where) } /** * Finds entities that match given find options. * Also counts all entities that match given conditions, * but ignores pagination settings (from and take options). */ static async findAndCount<T extends AppBaseEntity>( this: { new(): T } & typeof AppBaseEntity, options?: FindManyOptions<T> ): Promise<[T[], number]> { await validateWhereOptions(options?.where) return this.getRepository<T>().findAndCount(options) } /** * Finds entities that match given WHERE conditions. * Also counts all entities that match given conditions, * but ignores pagination settings (from and take options). */ static async findAndCountBy<T extends AppBaseEntity>( this: { new(): T } & typeof AppBaseEntity, where: FindOptionsWhere<T> ): Promise<[T[], number]> { await validateWhereOptions(where) return this.getRepository<T>().findAndCountBy(where) } /** * Finds first entity that matches given conditions. */ static async findOne<T extends AppBaseEntity>( this: { new(): T } & typeof AppBaseEntity, options: FindOneOptions<T> ): Promise<T | null> { await validateWhereOptions(options?.where) return this.getRepository<T>().findOne(options) } /** * Finds first entity that matches given conditions. */ static async findOneBy<T extends AppBaseEntity>( this: { new(): T } & typeof AppBaseEntity, where: FindOptionsWhere<T> ): Promise<T | null> { await validateWhereOptions(where) return this.getRepository<T>().findOneBy(where) } /** * Finds first entity that matches given conditions. */ static async findOneOrFail<T extends AppBaseEntity>( this: { new(): T } & typeof AppBaseEntity, options: FindOneOptions<T> ): Promise<T> { await validateWhereOptions(options?.where) return this.getRepository<T>().findOneOrFail(options) } /** * Finds first entity that matches given conditions. */ static async findOneByOrFail<T extends AppBaseEntity>( this: { new(): T } & typeof AppBaseEntity, where: FindOptionsWhere<T> ): Promise<T> { await validateWhereOptions(where) return this.getRepository<T>().findOneByOrFail(where) } } } export abstract class AppBaseEntity extends paramsCheckingMixin(BaseEntity) { } function validateWhereOptions(where?: FindOptionsWhere<BaseEntity>[] | FindOptionsWhere<BaseEntity>): Promise<void> { if (!where) { return Promise.resolve() } if (!Array.isArray(where)) { where = [where] } const errors: string[] = [] where.forEach((findOptionsWhere) => { for (const key in findOptionsWhere) { if (findOptionsWhere[key] === null || findOptionsWhere[key] === undefined) { errors.push(`Invalid value of where parameter ${key}`) } } }) if (errors.length) { return Promise.reject(errors.join('. ')) } return Promise.resolve() } The wrapper class is used as a base class for all entities. Conclusion I believe that security is a very important part of any application, as we all care about our users and their data. Unfortunately, it is often overlooked, and developers don't pay enough attention to it. I hope that this article will help you to improve your application security on the database level and help you avoid some common mistakes.
