[BUG]: inArray in prepared statements causes syntax error with Planetscale

[geauser]

What version of drizzle-orm are you using?

0.28.6

What version of drizzle-kit are you using?

0.19.13

Describe the Bug

When using inArray in a prepared statement in Planetscale it throws a syntax error:

const prepared = await db.select().from(table).where(inArray(table.field, sql.placeholder('fields'))).prepare();
await prepared.execute({ fields: ['SOME_VALUE_IN_ARRAY'] });

I get an error like this:

syntax error at position 256 near 'SOME_VALUE_IN_ARRAY'

Expected behavior

No response

Environment & setup

No response

[geauser]

Any news on this?

[Angelelz]

I don't think this pattern is technically possible without mayor reworking.

When you prepare a query, drizzle needs to know how many parameters will have placeholders. If you try to do it with an array, there is no way for drizzle to know how many elements the array that you'll give it will have.

For example, if you examine the query that will get generated by your example query, it will look like this:

select * from table where table.field in (?) -- params: []

Then, when you call execute, and you pass the parameter, there is no way for drizzle to affect the query that was prepared, because it has already been prepared, drizzle will only fill in the parameter:

select * from table where table.field in (?) -- params: [['SOME_VALUE_IN_ARRAY']]

Which is not correct.

If you were to execute this directly with an array of 2 elements, the query would look like this:

select * from table where table.field in (?, ?) -- params: ['SOME_VALUE_IN_ARRAY', 'ANOTHER_VALUE']

Hopefully you get the idea.

[jCube1980]

Here is a workaround that works for Postgres. Instead of an array, I pass it as a comma-separated string and use the string to array function with a varchar[] typecast.. Hope this helps someone.

export const fetchAttributeIds = db.select({
    id: contentAttribute.id
  }).from(contentAttribute)
  .where(eq(contentAttribute.code, sql`any(string_to_array(${placeholder("attributeCodes")}, ',')::varchar[])`))
  .prepare('fetchAttributes')

[mauriciabad]

I have the same problem, I noticed that if instead of sql.placeholder('fields'), you put a hardcoded array directly, it works.

So, my workarround was to use a higher order function to generate the query with hardcoded values:

const makeQuery = (fields: string[]) => {
  return db.select().from(table).where(inArray(table.field, fields)).prepare()
};
const prepared = makeQuery(['SOME_VALUE_IN_ARRAY'])
const result = await prepared.execute();

And if you need dynamic values, you can use a subquery, that generates the list from a single initial value:

const prepared = db
  .select()
  .from(table)
  .where(
    inArray(
      table.field,
      db
        .select({ data: table.field })
        .from(table)
        .where(inArray(table.someOtherField, sql.placeholder('someOtherField')))
    )
  )
  .prepare()

const result = await prepared.execute({ someOtherField: 'SOME_VALUE_IN_ARRAY' })

[geovanisouza92]

I have a similar problem with MySQL, and while

const prepared = db
  .select()
  .from(<table>)
  .where(inArray(<field>, sql.placeholder('field')))
  .prepare();

causes the error

{
    "error": {
        "message": "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 '38697, 38698, 38699, 38700, 38701, 38702, 38703, 38704, 38705, 38706, 38707, 387' at line 1",
        "code": "ER_PARSE_ERROR",
        "errno": 1064,
        "sql": "select <fields> from <table> where <field> in 38697, 38698, 38699, ..., 38882",
        "sqlState": "42000",
        "sqlMessage": "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 '38697, 38698, 38699, 38700, 38701, 38702, 38703, 38704, 38705, 38706, 38707, 387' at line 1"
    }
}

I noticed that it was a syntax error, meaning I could workaround by changing the prepared statement to

const prepared = db
  .select()
  .from(<table>)       // ⬇️ here
  .where(inArray(<field>, sql`(${sql.placeholder('field')})`))
  .prepare();

[ihadabs]

@jCube1980

I tried the following and also it worked. Is there any reason for using string_to_array .

sql`any(${sql.placeholder('someArray')}::text[])`

[L-Mario564]

It's not really recommended to use placeholders with inArray. We'll document this detail.

[tmoran-stenoa]

I have a similar problem with MySQL, and while

const prepared = db
  .select()
  .from(<table>)
  .where(inArray(<field>, sql.placeholder('field')))
  .prepare();

causes the error

{
    "error": {
        "message": "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 '38697, 38698, 38699, 38700, 38701, 38702, 38703, 38704, 38705, 38706, 38707, 387' at line 1",
        "code": "ER_PARSE_ERROR",
        "errno": 1064,
        "sql": "select <fields> from <table> where <field> in 38697, 38698, 38699, ..., 38882",
        "sqlState": "42000",
        "sqlMessage": "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 '38697, 38698, 38699, 38700, 38701, 38702, 38703, 38704, 38705, 38706, 38707, 387' at line 1"
    }
}

I noticed that it was a syntax error, meaning I could workaround by changing the prepared statement to

const prepared = db
  .select()
  .from(<table>)       // ⬇️ here
  .where(inArray(<field>, sql`(${sql.placeholder('field')})`))
  .prepare();

This worked perfectly for me. It also seems to be robust against SQL injection attempts. Is there a reason why this is not an accepted answer? cc @Angelelz