Using LIKE '%a%' in PostgreSQL while sanitizing data agains SQL injections

Question

Hello there im trying to run a postgresql query on flask using SQLAlchemy, but I don't understand how I can keep my query sanitized while using the LIKE '%' argument.

db.execute("SELECT * FROM books WHERE isbn LIKE '%:isbn%' OR title LIKE '%:title%L' OR author = '%:author%'", {"isbn": isbn, "title": title, "author": author})

That is what I got but of course it does not run. and I do not want to sacrifice the integrity of the system to be allowed to use LIKE.

Does anyone have a suggestion for me?

Shouldn't the mapper you use take care of that by sanitizing the passed variables? — Gh05d, May 22 '20 at 11:47
That db.execute function. Doesn't it come from some Library and inject the passed variables into the query? Maybe this is helpful for you: https://stackoverflow.com/questions/1279613/what-is-an-orm-how-does-it-work-and-how-should-i-use-one#1279678 — Gh05d, May 22 '20 at 11:56
Are you worried about the user injecting wildcards where you do not want them, or about them escaping from the string altogether and running entirely different commands? — jjanes, May 22 '20 at 12:23

Bill Karwin · Accepted Answer · 2020-05-22T18:55:33.770

The parameter placeholder cannot be inside a quoted string in the SQL expression. Otherwise there would be no way to use characters that look like a placeholder as a literal string in SQL.

So you must put the placeholder outside of a quoted string, and concatenate with the wildcards using the || string concatenation operator.

db.execute("""SELECT * FROM books 
  WHERE isbn LIKE '%'||:isbn||'%' 
  OR title LIKE '%'||:title||'%L' 
  OR author LIKE '%'||:author||'%'""", 
  {"isbn": isbn, "title": title, "author": author})

An alternative is to concatenate the value of the parameter with the % SQL wildcard characters in Python, then pass the resulting string as the parameter. In this case, you can skip putting the wildcards in your query. Still don't put the parameter placeholder inside string quotes in the SQL expression.

db.execute("""SELECT * FROM books 
  WHERE isbn LIKE :isbn 
  OR title LIKE :title
  OR author LIKE :author""", 
  {"isbn": "%"+isbn+"%", "title": "%"+title+"%L", "author": "%"+author+"%"})

P.S.: I edited your author = to author LIKE because you can't use wildcards with =.

Also I think you have an extra L after the title wildcard. But I don't know if this is intentional, so I left it in my example.

Using LIKE '%a%' in PostgreSQL while sanitizing data agains SQL injections

1 Answers1