Column name as variable in typeorm querybuilder

Question

This is my original code which does not work.

this.createQueryBuilder().where(
      'LOWER(:column) LIKE LOWER(:name)',
      { column: 'itemName', name: `%${options.name}%` }
    );

{ "total": 0, "results": [] }

I get no results from the above query but when I explicitly place the column name in the query like this, it works:

this.createQueryBuilder().where(
      'LOWER(itemName) LIKE LOWER(:name)',
      { name: `%${options.name}%` }
    );

{"total":9, "results": [<RESULTS GOES HERE>] }

Is it possible to use a variable in the column name for typeorm?

Answer 1

I was attempting something similar and stumbled a (not particularly well written) explanation here: https://www.tutorialspoint.com/typeorm/typeorm_query_builder.htm

Basically, parameters are intended to prevent SQL injection, so while it's hard to see what's going on under the hood, I'm guessing that any string parameter automatically gets wrapped in single quotes in the final query so it can't be interpreted as anything other than a value.

I even tried surrounding the parameter in single quotes, e.g.

this.createQueryBuilder.where(
    '":columnName" = :value',
    { columnName: 'my_column': value: 'my_value' }
);

No good. This prevents parameter substitution entirely and gives the error QueryFailedError: column "$1" does not exist and seems to confirm that this feature is designed to prevent SQL injection.

This does leave template strings or string concatenation if you want to dynamically set column names, table names, etc. However, for the very same reason this safeguard exists in the first place, I would avoid doing that with user input.