dangerous-raw-sql-used-with-orm

Ensure no raw SQL queries

Using an ORM (Object Relational Mapper) framework to interface with a database, instead of writing raw SQL queries is great as it generally protects against SQL injection. That said, some APIs of some ORM frameworks (such as typeorm, sequelize, ...) offer the ability to customize the SQL query that will be issued to the database. So called "Raw SQL" features are dangerous and should be used with extreme caution as they do not provide the safe escaping of user input that the framework normally provides through parametrized queries. There are valid case for using those advanced features, but you should avoid using user input or you must explicitely sanitize (avoid raw strings, use an allow-list or cast as number). In some cases, you can use alternative syntax to still have the same level of flexibility and leverage the framework's built-in sanitization.

Examples

Insecure Example

TypeScript (typeorm)

import {
    Controller, Get, Put, Param
} from "@nestjs/common";
import { getConnection } from "typeorm";

@Controller("cart")
export class CartController {

    @Put("product/:id/:quantity")
    async incrementProductQuantity(
        @Param("id") productId: string,
        @Param("quantity") quantity: string
    ): Promise<number> {
        return await getConnection()
            .createQueryBuilder()
            .update("cart")
            .set({
                // Using update-query-builder Raw SQL support - "function style value" - is dangerous
                // https://typeorm.io/#/update-query-builder/raw-sql-support
                // ---> SQL injection in UPDATE clause
                quantity: () => "quantity + " + quantity,
            })
            .where("productId = :id", { id: productId })
            .execute();
    }

    @Get("product/:id")
    async fetchProductQuantity(
        @Param("id") productId: string,
    ): Promise<number> {
        return await getConnection()
            .createQueryBuilder()
            .select("quantity")
            .from(getCurrentCart(), "cart")
            // Select query builder
            // https://typeorm.io/#/select-query-builder/using-parameters-to-escape-data
            // ---> SQL injection in WHERE clause
            .where("productId = '" + productId + "'")
            .execute();
    }

    @Put("product/:id/:quantity")
    async addNewProduct(
        @Param("id") productId: string,
        @Param("quantity") quantity: string
    ): Promise<number> {
        await getConnection()
            .createQueryBuilder()
            .insert()
            .into(getCurrentCart())
            .values({
                // Insert using query builder Raw SQL support - "function style value" - is dangerous
                // https://typeorm.io/#/insert-query-builder/raw-sql-support
                // ---> SQL injection in INSERT clause
                productId: () => "PRODUCT-" + productId),
                quantity: quantity,
            })
            .execute();
    }
}

TypeScript (sequelize)

import Sequelize from "sequelize";

class apiController {
    GetBoardIdFromHash(hash: string) {
        return sequelize
            // This is plain old SQL injection
            .query("select id from boards where hash =" + hash, {
                raw: true,
                type: Sequelize.QueryTypes.SELECT,
            })
            .then((res: any) => {
                if (res && res.length) {
                    return res[0];
                }
                return { id: 0 };
            });
    }
}

export = new apiController();

Secure Example

TypeScript (typeorm)

import {
    Controller, Get, Put, Param
} from "@nestjs/common";
import { getConnection } from "typeorm";

@Controller("cart")
export class CartController {

    @Put("product/:id/:quantity")
    async incrementProductQuantity(
        @Param("id") productId: string,
        @Param("quantity") quantity: string
    ): Promise<string> {
        await getConnection()
            .createQueryBuilder()
            .update(getCurrentCart())
            .where("productId = :id", {"id": productId})
            // This benefits from framework sanitization
            .set({ quantity: () => 'quantity + :x' })
            .setParameter("x", quantity)
            .execute();

        await getConnection()
            .getRepository(Cart)
            // Atomic increment is supported
            .increment({productId: productId}), "quantity", quantity);

        return "ok";
    }
}

TypeScript (sequelize)

import Sequelize from "sequelize";

class apiController {
GetBoardIdFromHash(hash: string) {
    return sequelize
        // This is using parametrized query
        .query("select id from boards where hash = :hash", {
            raw: false,
            // Using bound parameters
            replacements: { hash: hash },
            type: Sequelize.QueryTypes.SELECT,
        })
        .then((res: any) => {
            if (res && res.length) {
                return res[0];
            }
            return { id: 0 };
        });
    }
}
}

export = new apiController();