SQL Error from Trailing Line Break in VirtualColumn Query String

skyran1278 commented 4 weeks ago

Issue description

A trailing line break in a VirtualColumn query string can lead to SQL errors in the WHERE clause due to unquoted identifiers.

Expected Behavior

In TypeScript, using the @VirtualColumn decorator should correctly encapsulate the SQL identifier in quotes:

@VirtualColumn({
    type: 'int',
    query: (alias) => {
      return `
        SELECT
          "domain101"
        FROM
          "domain1"
        WHERE
          "id" = ${alias}.id
      `;
    },
  })
  domain102?: number;

This setup should produce a properly quoted SQL WHERE clause:

WHERE ( (((((
        SELECT
          "domain101"
        FROM
          "domain1"
        WHERE
          "id" = "Domain1"."id"
      ) = $1)))) )

Actual Behavior

The current implementation fails to quote the identifier in the generated SQL, leading to an error:

@VirtualColumn({
    type: 'int',
    query: (alias) => {
      return `
        SELECT
          "domain101"
        FROM
          "domain1"
        WHERE
          "id" = ${alias}.id
      `;
    },
  })
  domain102?: number;

Resulting in:

WHERE ( (((((
        SELECT
          "domain101"
        FROM
          "domain1"
        WHERE
          "id" = Domain1.id
      ) = $1)))) )

Steps to reproduce

The issue can be replicated using the @VirtualColumn decorator with a query containing a trailing line break:

@VirtualColumn({
    type: 'int',
    query: (alias) => {
      return `
        SELECT
          "domain101"
        FROM
          "domain1"
        WHERE
          "id" = ${alias}.id
      `;
    },
  })
  domain102?: number;

My Environment

Dependency	Version
Operating System	any
Node.js version	20.11.1
Typescript version	5.3.3
TypeORM version	0.3.20

Additional Context

The QueryBuilder.replacePropertyNamesForTheWholeQuery method does not correctly replace the alias due to the line break at the end of the query string. A potential fix involves modifying the SelectQueryBuilder.buildWhere method to escape the alias properly:

Before:

if (column.isVirtualProperty && column.query) {
    aliasPath = `(${column.query(alias)})`
}

After:

const escapedAliasName = this.escape(alias);
if (column.isVirtualProperty && column.query) {
    aliasPath = `(${column.query(escapedAliasName)})`
}

Relevant Database Driver(s)

[ ] aurora-mysql
[ ] aurora-postgres
[ ] better-sqlite3
[ ] cockroachdb
[ ] cordova
[ ] expo
[ ] mongodb
[ ] mysql
[ ] nativescript
[ ] oracle
[X] postgres
[ ] react-native
[ ] sap
[ ] spanner
[ ] sqlite
[ ] sqlite-abstract
[ ] sqljs
[ ] sqlserver

Are you willing to resolve this issue by submitting a Pull Request?

Yes, I have the time, but I don't know how to start. I would need guidance.

alenap93 commented 4 weeks ago

@skyran1278 why don't use "${alias}"."id"?

skyran1278 commented 4 weeks ago

Using "${alias}"."id" will produce an error, as typically, quotes are added around identifiers in the SELECT clause but not in the WHERE clause. This results in the following error:

Error Message: QueryFailedError: zero-length delimited identifier at or near "\"\""

typeorm / typeorm