How to use wild cards in SQLAlchemy?

Question

I'm trying to use wildcards for a query using SQLAlchemy but I'm getting back an empty list.

My code:

engine = create_engine(os.getenv("DATABASE_URL"))
db = scoped_session(sessionmaker(bind=engine))
s = input("Search for a book: ")
q = db.execute(f"SELECT * FROM books WHERE isbn LIKE '%\:s\%' OR author LIKE '%\:s\%' OR title LIKE '%\:s\%'", {"s": s}).fetchall()

I'm using \ to escape the quotes that get inserted when the function uses the values of the placeholder variables, if I remove them I get this error:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "grey"
LINE 1: SELECT * FROM books WHERE isbn LIKE '%'grey'%' OR author LIK...

Is there anyway to use wildcards in SQLAlchemy?

I can make this work by using formatted strings instead of placeholders for variables but this will make my code vulnerable against SQL Injections. I'm also using PostgreSQL.

Why are you escaping your own parameters, when that's the point of parameterized queries? — roganjosh, Sep 21 '19 at 12:00
I was trying to escape the quote inserted by the placeholder variable. — Adham Salama, Sep 21 '19 at 12:51

score 3 · Accepted Answer · answered Sep 21 '19 at 11:59

3

The % characters should be part of the parameter you pass in, not the template string, and you shouldn't be manually adding quotes. Let SQLAlchemy do that for you.

Also, there's no need for the template to be an f-string.

For example:

s = input("Search for a book: ")
q = db.execute(
    "SELECT * FROM books WHERE isbn LIKE :s OR author LIKE :s OR title LIKE :s",
    {"s": "%" + s + "%"},
).fetchall()

answered Sep 21 '19 at 11:59

ChrisGPT was on strike

127,765
105
273
257

I tried this and it's still returning an empty list... – Adham Salama Sep 21 '19 at 12:18
1

Then your predicate does not match, or the table is empty. Note that `LIKE` is case sensitive (in Postgresql). Also in order to used named placeholders you have to wrap the query in a `text()` construct, if using an `Engine` or a `Connection` directly, instead of a `Session`. – Ilja Everilä Sep 21 '19 at 12:21
Oh I forgot to make the search case insensitive! I just used ILIKE instead of LIKE. Thanks so much for your help! – Adham Salama Sep 21 '19 at 12:26

How to use wild cards in SQLAlchemy?

1 Answers1

Linked