Result of query with comment is inconsistent and random about typeorm HOT 20 CLOSED

@kkrypt0nn oh sorry my bad 🙈🙈🙈

from typeorm.

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/[email protected]/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/[email protected]/node_modules/better-sqlite3/lib/methods/wrappers.js:5:21)
    at BetterSqlite3QueryRunner.getStmt (/home/jay/Documents/code/help/nestjs-typeorm-bug/node_modules/.pnpm/[email protected]_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/[email protected]_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/[email protected]_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

from typeorm.

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

from typeorm.

using dataSource as usual

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

from typeorm.

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

from typeorm.

this is not related with @nestjs/typeorm

Please report this to https://github.com/typeorm/typeorm/ instead

from typeorm.

As written at the bottom, this does not seem related to typeorm itself because when just using typeorm the issue is not present 🤔

Though I can report it there if you get the issue to occur by just using typeorm, I haven't managed to get it to occur with similar code.

from typeorm.

Will make a report at typeorm itself, will see what they say.

Thanks for your time and sorry if this was in the wrong repository ^-^

from typeorm.

Before opening an issue there..

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

I doubt the issue is within the code itself, if you want you can look at it it's super basic and simple.

How would it be known if it's an issue with nestjs/core?

from typeorm.

@kkrypt0nn the issue is you use the raw query without parameter... also this is a possible sql injection.

Try to use the query builder (https://typeorm.io/select-query-builder) instead of this raw query or add parameters into your raw query.

from typeorm.

The issue is you usage of the raw query without parameter... also this is a possible sql injection.

Try to use the query builder (https://typeorm.io/select-query-builder) instead of this raw query or add parameters into your raw query.

Please take the time to read the PS at the bottom and how the actual bug occurs ✨

from typeorm.

@kkrypt0nn that's probably an issue with how your test.js works.

At some iteration, the generated query is probably malformed (from SQL PoV). And since you have no validation on the path param query, you're suitable to such errors due to how you're using typeorm.

from typeorm.

@micalevisk The attempted queries in the test.js file is always the same, after failing and trying again exactly the same a few times it succeeds. Logging the queries TypeORM is executing shows it's always the same and not some different malformed query.

from typeorm.

The attempted queries in the test.js file is always the same

I didn't get what do you mean. It's pretty clear that each iteration will likely to have its on query value due to Math.random() * 36

also, that query variable is probably not URL-safe.

from typeorm.

@kkrypt0nn when trying this without using @nestjs/typeorm, did you tested the controller->service path as well or just the dataSource.query() call?

from typeorm.

What I mean is that after the attempts here

If it failed, it will attempt 50 times the exact same request below:

After some random j, the request succeeds and responds as expected with the result of the SQL query.

from typeorm.

@kkrypt0nn when trying this without using @nestjs/typeorm, did you tested the controller->service path as well or just the dataSource.query() call?

Just the call to the data source

from typeorm.

so my bet is that the query path parameter isn't what you think it is.

can you can try it out again without @nestjs/typeorm but making the whole path controller -> service -> db query

from typeorm.

so my bet is that the query path parameter isn't what you think it is.

Well by logging it, it shows what I want it to be - especially when repeating the exact same query multiple times leads to a success after some time.

can you try it out again without @nestjs/typeorm but making the whole path controller -> service -> db query

So just the query to the database instead of making use of the dataSource?

from typeorm.

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

I believe that would be awesome indeed - also many thanks @jmcdo29 :D - my attempted code was most likely different and failed to isolate the issue correctly.

Apologies for the wrongly opened issue from my side.

from typeorm.