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

user_12263245 0
Viewed 287

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?

flask-sqlalchemy sql-injection postgresql
1 Answers

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.

Bill_Karwin_208600
Related