Secure Ecto LIKE queries

Hello

The documentation for Ecto.Query.API.like/2 says:

You should be very careful when allowing user sent data to be used as part of LIKE query, since they allow to perform LIKE-injections.

How should I make these LIKE queries safe to execute?

Thank you for any help!

First and foremost, if the pattern used in the LIKE query is built from data that you don’t fully control, you need to remove or escape the wildcards. PostgreSQL for example, in its default configuration, has two LIKE wildcards: % and _.

The idea is that, if your query looks like from u in User, where: like(u.name, "#{prefix}%") (basically a prefix search for users whose name starts with prefix, you need to make sure that prefix does not contain unescaped wildcards. A malicious user could in fact otherwise send you the value % (or _) for prefix, so the SQL query would become something like:

SELECT * FROM users WHERE users.name LIKE '%%';

Which matches any user. With escaping, it would become:

SELECT * FROM users WHERE users.name LIKE '\%%';

Which would only match users whose name starts with the literal % character. Mind that you also have to escape the escape character itself.

If % or _ should not be part of the input, it’s just easier to remove them instead of escaping them.

3 Likes