How to properly escape user input for the SQL "LIKE" operator? (Postgres)

Question

LIKE operator with user-provided input

The Postgres LIKE operator is useful for offering a rudimentary search on your table's row to the user. However, user-provided input for the LIKE operator needs to be properly escaped as certain characters such as underscore (_) are being interpreted. It is always advisable to pass user-provided input via a Postgres prepared statment:

PREPARE getUsernames(text) AS
SELECT "name" FROM "users" WHERE "name" LIKE '%' || $1 || '%';
EXECUTE getUsernames('_')

Although passing the user input via a variable inside a prepared statement, the underscore is interpreted by the LIKE operator as a wildcard such that the above SQL query matches to every entry inside the users table.

Proposed Solution

The user expect that searching for underscore returns all results where the name contains the underscore character (instead of it being interpreted as a wildcard). We therefore have to escape the user-input such that it is never being interpreted by the LIKE operator. Postgres offers the specification of an escape character, which is the backslash by default. So every character with a special meaning must be escaped using this escape character. However, keeping a blacklist of characters feels error-prone as there is always the possibility of having an incomplete blacklist. Therefore, the idea of my proposed solution is to always prepend every character of the user's input with the escape character regardless of whether it will actually be interpreted or not. Postgres accepts this escaping of characters that would not need to be escaped.

Here is an example implementation of this user input escaping written in TypeScript:

function escapePostgresLikeStr(str: string, escapeChar = '\\'): string {
  return str
    .split('')
    .map(char => `${escapeChar}${char}`)
    .join('');
}

I did not find any information on this particular problem so any feedback, security considerations and improvements are welcome! :)

Cheers, derbenoo

Fifteen

Answer 1

If you don't want the use to use wildcards, don't use like. Instead:

where position(? in name) > 0

Note that ? is a parameter placeholder so you don't have to munge the query string with (dangerous) user input.

Use LIKE (or better yet regular expressions) if you want users to take advantage of the wildcards. Otherwise, I don't see an advantage to that.

Kacey

Thanks for your response Gordon Linoff, I was not aware of the position function. Reading the documentation, I fully agree with your answer that my described use-case is best implemented using the position function instead of the LIKE operator. During implementation I noticed that the WHERE clause has to be WHERE position(? in name) > 0 so that it evaluates to a boolean value. – Fifteen

nice! thank you! – Karriekarry