Parameterized identifiers in queries

[tcort]

I'm building up MySQL SELECT statements using squel with user specified values and column names. The escaping for identifiers such as column names is different than for values. For MySQL it's done with mysql.escapeId(id) instead of mysql.escape(val).

The mysql module for node allows parameterized queries to call the right escape function implicitly by using ?? for IDs and ? for values. See Escaping query identifiers.

I want to be able to do something like this:

alert(JSON.stringify(
  squel.select()
    .from("students")
    .where("?? = ? AND ?? = ?", "a", "test", "b", true)
    .order("CALC(?, ?)", true, 1.2, false)
    .toParam()
));

/*
  {
    text:  SELECT * FROM students WHERE (?? = ? AND ?? = ?) ORDER BY CALC(?, ?) ASC,
    values:  [ 'a', 'test', 'b', true, 1.2, false ]
  }
*/

But it eats up the question marks. Here's a simplified example with actual output from squel 4.2.0:

> squel.select().from("students").where("?? = ? AND ?? = ?", "a", "test", "b", true).toParam();
{ text: 'SELECT * FROM students WHERE (?? = ? AND ?)',
  values: [ 'a', 'test', 'b', true ] }

Is there a way I can have the column names parameterized?

[hiddentao]

Thanks for raising this, this looks like a bug with the WHERE expression parser. We haven't come across this use case before which is why it's happening.

[wirehead]

Related to question marks and the WHERE parser.

You can create a SQL query in PostgreSQL that looks like this:

select workflow from wh_log where workflow ? 'needsReview' = true;

But if I try to add something in squel like this:

s.where('workflow ?  \'needsReview\'');

It generates an invalid query for what is otherwise a valid SQL use case (unless there's a way to escape that question mark that I'm missing)

[hiddentao]

@wirehead Have you tried using squel.str() to generate this?

[ronny332]

Hi, old bug, but seemingly still existent in a similar way:

I'm using the lastest release from npmjs (5.12.0) and the handling of parameterized column names isn't working for me. Here's a short example:

const expr = squel.expr();

for (const f in search) {
  expr.and('?? = ?', f, search[f]);
}

const query = squel
  .select()
  .field(field)
  .from(table)
  .where(expr);

query.toParam()
{ text: 'SELECT id FROM table WHERE (?? = ? AND ?? = ?)',
  values: [ 'id', 123 undefined, 'name', 'myself', undefined ] }

The flavor is set to mysql. In my eyes it looks like the parser behind .and() is using every single ? as one value, but doesn't know about the ?? feature of node-mysql.

If cause it is possible to work around this issue:

expr.and(db.escapeId(f) + ' = ?', search[f]);

Is this a bug, or a feature (and maybe still related to this now 2 years old topic)? Squel looks so comprehensive to me, I don't assume it is a feature ;-).

[hiddentao]

@ronny332 You're right about the parser using every single ? as a value - that's how it was setup to work, yes. The MySQL flavour doesn't currently affect the expression parser.