typeorm: Result of query with comment is inconsistent and random

Is there an existing issue for this?

I have searched the existing issues

Current behavior

Executing the query from the satellite.controller.ts file with input being:

random') UNION SELECT tbl_name, CAST(1 AS VARCHAR), CAST(1 AS VARCHAR), CAST(1 AS VARCHAR), CAST(1 AS VARCHAR) FROM sqlite_master; --

random can be replaced with actual random strings

Will either return the correct and expected data or the error

The supplied SQL string contains more than one statement

Minimum reproduction code

https://github.com/kkrypt0nn/nestjs-typeorm-bug

Steps to reproduce

npm i
npm run start:dev
node test.js

As this bug is inconsistent, you may have to run the file multiple times. You will also see that the result will never be the same, sometimes it works sometimes it doesn’t - and after attempting the 50 initial requests, sending the same request X times will make it succeed after some point.

Expected behavior

All the 50 requests made should be working on first attempt and show the actual data and not the error.

Package version

9.0.1

NestJS version

9.3.9

Node.js version

18.15.0 & 19.8.1

In which operating systems have you tested?

macOS
Windows
Linux

Other

Logging with NestJS shows the following query is getting executed (query logged being always the same):

The exact same query being executed multiple times over and over, e.g. by spamming, will make the query return correct data after some time, which makes the bug inconsistent. Here is an example with some frontend on top of it:

https://user-images.githubusercontent.com/43011723/228337455-5952e980-67c2-4eb7-b135-eba5c23edb36.mp4

Switching the SQLite driver to the sqlite driver makes the query always work and return the expected result, never the error.

The issue is not happening in better-sqlite3 nor typeorm themselves as using these standalone do not have the issue. I have attempted the same code and test on both of these without NestJS and it works fine and never throws an error.

PS: I am very well aware that this is prone to SQLi attacks, it is also the purpose of my project: To showcase SQLi vulnerabilities. That is why I have made use of the dataSource to make a raw query instead of making use of repositories.

About this issue

Original URL
State: closed
Created a year ago
Comments: 20 (9 by maintainers)

Most upvoted comments

I was able to use your repo and eventually reproduce the error with typeorm alone using this script

const { DataSource } = require("typeorm");

const dataSource = new DataSource({
  type: "better-sqlite3",
  database: "db.sqlite",
  entities: ["./dist/**/*.entity.js"],
  synchronize: true,
});

const main = async () => {
  await dataSource.initialize();
  for (let i = 0; i < 5000; i++) {
    const random = Array.from(Array(20), () =>
      Math.floor(Math.random() * 36).toString(36)
    ).join("");
    const searchQuery = `${random}') UNION SELECT tbl_name, CAST(1 AS VARCHAR), CAST(1 AS VARCHAR), CAST(1 AS VARCHAR), CAST(1 AS VARCHAR) FROM sqlite_master; --`;
    const query = `SELECT *
						   FROM user_satellites_satellite
									FULL JOIN satellite ON user_satellites_satellite.satelliteId = satellite.id
						   WHERE user_satellites_satellite.userId = '630633ba-030c-4967-acd4-d790bf26cfda'
							 AND (satellite.name LIKE '%${searchQuery}%' OR satellite.description LIKE '%${searchQuery}%');`;
    const result = await dataSource.query(query);
    console.log(result);
  }
};

main();

This was after running build so that the dist had the proper entities in it. I also had to run it a few time to ensure that the error did happen, as you mentioned it is inconsistent. This is the actual error happening

/home/jay/Documents/code/help/nestjs-typeorm-bug/node_modules/.pnpm/better-sqlite3@8.2.0/node_modules/better-sqlite3/lib/methods/wrappers.js:5
	return this[cppdb].prepare(sql, this, false);
	                  ^

RangeError: The supplied SQL string contains more than one statement
    at Database.prepare (/home/jay/Documents/code/help/nestjs-typeorm-bug/node_modules/.pnpm/better-sqlite3@8.2.0/node_modules/better-sqlite3/lib/methods/wrappers.js:5:21)
    at BetterSqlite3QueryRunner.getStmt (/home/jay/Documents/code/help/nestjs-typeorm-bug/node_modules/.pnpm/typeorm@0.3.12_cepfjtr3qct7czdnayem4lr57m/node_modules/typeorm/driver/better-sqlite3/BetterSqlite3QueryRunner.js:37:43)
    at async BetterSqlite3QueryRunner.query (/home/jay/Documents/code/help/nestjs-typeorm-bug/node_modules/.pnpm/typeorm@0.3.12_cepfjtr3qct7czdnayem4lr57m/node_modules/typeorm/driver/better-sqlite3/BetterSqlite3QueryRunner.js:74:22)
    at async DataSource.query (/home/jay/Documents/code/help/nestjs-typeorm-bug/node_modules/.pnpm/typeorm@0.3.12_cepfjtr3qct7czdnayem4lr57m/node_modules/typeorm/data-source/DataSource.js:341:20)
    at async main (/home/jay/Documents/code/help/nestjs-typeorm-bug/test-db.js:22:20)

As this is reproducable without NestJS, this should be reported to TypeORM or better-sqlite3. Not sure which, but probably better-sqlite3

jmcdo29 on Mar 28, 2023

@kkrypt0nn oh sorry my bad 🙈🙈🙈

christian-forgacs on Mar 28, 2023

we can probably improve the docs to clarify what is the role of @nestjs/typeorm (or what it does under the hood).

micalevisk on Mar 28, 2023

using dataSource as usual

but you’ll create it by yourself instead of relying on @nestjs/typeorm

micalevisk on Mar 28, 2023

my bad, I missed that line. @nestjs/typeorm changes nothing on how typeorm works

So this is either an issue with typeorm, or with your code or even with nestjs core

micalevisk on Mar 28, 2023