Parametized query wont work

[roelzkie15]

Hi!

Im using typescript-express-example template and i was successfully connected my application to my MySQL database.

Now i am working with entityManager.query to insert, select etc.. some data.

Here is my errors when running this:

const result = await getEntityManager().query('SELECT * FROM tbl_1 WHERE name = :name;', [ p_name ])

Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near ':name' at line 1

Another error is when running this:

const result = await getEntityManager().query('SELECT * FROM tbl_1 WHERE name = ` + 'some_name' + `;')

Error: ER_BAD_FIELD_ERROR: Unknown column 'some_name' in 'where clause'

But when using a direct plain text:

const result = await getEntityManager().query('SELECT * FROM tbl_1 WHERE name = 'some_name';')

It works by this way.

Any idea how to resolve this errors?

[pleerock]

When using query method use underlying driver escaping mechanism. For mysql:

await getEntityManager().query('SELECT * FROM tbl_1 WHERE name = ?', [ p_name ])

[roelzkie15]

@pleerock thanks for helping again but why does :param wont work? According to your comment here that will be the usage

[pleerock]

maybe i confused it with different driver. as i told it driver-specific thing

[roelzkie15]

Well, that's fine then, i got it working now thanks a lot!

[avbentem]

For future reference, it seems one could currently use the built-in escapeQueryWithParameters to take care of translating :name named parameters into a driver-specific placeholder such as $1, @1, name or ?, and get a matching array of sorted values.

Like:

// For PostgreSQL yields:
//   query: select a, b from my_table where x between $1 and $2
//   parameters: [ 2, 10 ]
const [query, parameters] = conn.driver.escapeQueryWithParameters(
    'select a, b from my_table where x between :low and :high',
    { high: 10, low: 2 }
);
conn.manager.query(query, parameters).then(...);

Of course, this does not seem to be in the public API, so might change in future releases.

[herenickname]

What about arrays?

.query('SELECT * FROM test WHERE id IN ?', [[1,2,4]]

[anodynos]

What about arrays?
.query('SELECT * FROM test WHERE id IN ?', [[1,2,4]]

Apparently, this works (in Postgres):

  .query('SELECT * FROM test WHERE id = ANY($1)', [[1,2,4]]

Source: brianc/node-postgres#1452 (comment)

[edencorbin]

Had similar issues and needed to use another format to handle ILIKE thought I would share in case it helps any TypeOrm (ers)
await entityManager.query(`SELECT id FROM MyDB.public.MY_DATA WHERE tag ILIKE '%%' || $1 || '%%'`, [search]); This did the trick

[quezak]

The trick with connection.driver.escapeQueryWithParameters(...) was really nice.
However: I see that now it has a third required parameter nativeParameters: ObjectLiteral. Does anyone know what should I put in there?

Alternatively, is there any other clean (non-driver-dependent) way to properly escape query parameters when using a raw query (not QueryBuilder)?

// sorry for using an old issue to ask a question, but people will keep arriving here from a google search (like me), so it kinda feels in place :)

EDIT: after some more research (thx @akwodkiewicz) in the driver code, we found out that for this usecase nativeParameters can be safely omitted by passing {}. (though I'm still not sure what they're actually used for -- some internal typeorm query params? or some $1, $2 params you already have in the query before the additional escaped params added by this method?)

[cooljeffro]

If you're using mysql, you can set namedPlaceholders to true (it is false by default) in your configuration options:

{
  type: 'mysql',
  host: 'localhost',
  port: 3306,
  ...
  extra: {
    namedPlaceholders: true
  }
}

https://github.com/sidorares/node-mysql2/blob/master/documentation/Extras.md#named-placeholders

[ippeiukai]

Based on comments above (#556 (comment) and #556 (comment)), we now use the following utility in migrations:

const queryWithParams = (
   queryRunner: QueryRunner,
   sql: string,
   params: ObjectLiteral
 ): ReturnType<QueryRunner['query']> =>
   queryRunner.query(...queryRunner.connection.driver.escapeQueryWithParameters(sql, params, {}));

Thanks!

[QuanTran91]

I got an error when running this code:
await queryRunner.query('GRANT ALL PRIVILEGES ON ?.* To ?@? ;', [dbName, dbUser, host]);
Turnout, the query automatically append sing quote (') in dbName which result in MySQL syntax error.