How do I prevent SQL injection in a LIKE '%input%' clause?

Question

I’m using SQLAlchemy with PostgreSQL and I need to generate a statement like

SELECT * FROM entities WHERE name LIKE '%input%';

where the “input” bit of the LIKE string is coming from a user. I would like the user to be able to include literal % and _ characters and have these match exactly, and obviously I need to ensure that the query doesn’t allow SQL injection. What is the idiomatic way to do this in SQLAlchemy? I tried

entities = session.query(Entity).filter(Entity.name.like('%:text%')) \
               .params(text=user_input).all()

but this didn’t seem to work—it executed without complaint but did not match rows that should have matched.

Answer 1

Use contains() and autoescape=True:

Entity.name.contains(user_input, autoescape=True)

Given autoescape=True SQLAlchemy will establish an escape character and escape occurrences of "%", "_" and the escape character itself. You can control the escape character explicitly using the escape= parameter.