How to properly handle building a properly escaped wildcard string with user input?

[bbuck]

I've read through the docs and it's unclear to me exactly when and how content is escaped in Drizzle. We have a basic search endpoint that accepts a query, on the back-end we build the condition like this:

const queryCondition = ilike(table.columnToSearch, `%${query}%`);

(NOTE: I'm aware this is not the most efficient way to do searches, I'm not asking about that).

From my understanding from the docs, I think this works like this:

1. Assume query is "test"
2. The template string is evaluated, %${query}% becomes %test%
3. The string "%test%" is passed as a value to ilike(...)
4. ??? At this point, I assume Drizzle escapes the string, but it doesn't seem concerned about % (and maybe that's fine).

Is this the safest way to build a string like that contains some database internal parts AND user input?

I've tried doing this with the sql operator too, but I get errors. If I do:

ilike(table.column, sql`%${query}%`)

I get a database syntax error at "%" -- okay. I see that. So then I quote it:

ilike(table.column, sql`'%${query}%'``)

I then get an error "could not determine the type of parameter $1," again... okay that makes some sense I guess. So I try:

ilike(table.column, sql<string>`'%${query}%'`)

But that returns the same error as without a type. So I guess using sql is not the solution.

[unkindypie]

+1 to this discussion. I have the same issue.

[Tboules]

hey guys, I actually figured out a work around this issue.

let query = 'testing'
query = '%' + query + '%'

ilike(table.column, query)

the actual code I used is as follows (havent tested the above code)

export async function findDesertFigure(val: string) {
  val = "%" + val + "%";
  console.log(val);
  try {
    const res = await db
      .select()
      .from(df)
      .where(
        sql`concat_ws(' ', ${df.title}, ${df.firstName}, ${df.lastName}, ${df.epithet}) ilike ${val}`,
      );

    console.log(res);
    return res;
  } catch (error) {
    console.log(error);
  }
}

[bbuck]

The working code I mentioned:

const queryCondition = ilike(table.columnToSearch, `%${query}%`);

Is equivalent to the de-sugared solution you're proposing:

const queryCondition = ilike(table.columnToSearch, '%' + query + '%');

Which is not an answer to the question. I understand my title may not be exactly clear but from reading the post the intent of the question is about ensuring that the value of query is properly escaped before feeding it into the database.

My (rudimentary/surface level) understanding of Drizzle right now suggests that the variable value:

const example = `%${query}%`;

Is properly escaped. The concern is that this builds a raw string with potential user input coupled with characters that have very specific meaning in the database. I don't want the user input to contain % characters that the database respects. In that regard, my question is focused on correctly passing user input as a query into the database.

[kyurikotpq]

Hey @bbuck! If you run .toSQL() on your queries, the parameters for all comparison commands should show up as ?. I think that means they're automatically parameterized and escaped?

E.g.

db.select().from(table).where(like(table.id, `${year}-%`)).toSQL()

Should give the following:

select * from table where table.id LIKE ?

[pfraces-rc]

@kyurikotpq, the parameters of comparison methods are escaped to prevent SQL injection (for instance the single quote ' is escaped to be treated as a literal character instead of ending the string). If I'm not mistaken, OP is asking about escaping LIKE special characters (%, _) so when query contains % it is treated as a literal % instead of being treated as a wildcard. Those cannot be escaped by the comparison methods or couldn't be used as a wildcards.

[madflow]

For postgres: I would argue, that in an ilike expression, the % and the _ characters have meaning. So in addition to the already working sql injection prevention with:

const queryCondition = ilike(table.columnToSearch, `%${query}%`);

... One would also have to "manually" escape % and _in the searched string. This would allow searching for strings, that actually start with a % sign and prevent allowing the user to inject a "meaningfull" character.

For example:

const escapedSearch = search.replace(/[%_]/g, "\\$&");
const queryCondition = ilike(column, `%${escapedSearch}%`);