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.