How to db.execute in postgresql using the LIKE operator with variables within flask

Question

I'm trying to get my db.execute to work but encounter a syntax error when using the LIKE operator along with a variable passed in from HTML like so:

@app.route("/search", methods=["POST"])
def search():
    """Search for books"""

    #olaf: pass the search field as a SQL command into database and return the result
        #olaf: display the result back into the HTML by using a list and loop
    searchBookVariableOnApplication_py=request.form['searchBook']

    found = db.execute("SELECT * FROM books_table WHERE (isbn LIKE '%:lookingFor%') OR (title LIKE '%:lookingFor%') OR (title LIKE '%:lookingFor%') OR (year::text LIKE '%:lookingFor%')", {'lookingFor': searchBookVariableOnApplication_py}).fetchall();


    #olaf: working code
    #found = db.execute("SELECT * FROM books_table WHERE (isbn LIKE '%123%') OR (title LIKE '%123%') OR (title LIKE '%123%') OR (year::text LIKE '%2012%')");
    return render_template("search.html", found=found)

This is my error message:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "robot" LINE 1: SELECT * FROM books_table WHERE (isbn LIKE '%'robot'%') OR (... ^

[SQL: SELECT * FROM books_table WHERE (isbn LIKE '%%%(lookingFor)s%%') OR (title LIKE '%%%(lookingFor)s%%') OR (title LIKE '%%%(lookingFor)s%%') OR (year::text LIKE '%%%(lookingFor)s%%')] [parameters: {'lookingFor': 'robot'}] (Background on this error at: http://sqlalche.me/e/f405)

How do I fix my syntax?

score 0 · Accepted Answer · answered Mar 28 '20 at 16:03

0

Your library is naively substituting the value for :lookingFor into the middle of an SQL string, and the quoting is not correct for doing that. You could write the query such that the variable doesn't occur inside an SQL string:

isbn LIKE '%' || :lookingFor || '%'

Or, you could programatically add the '%' to the search string before passing it to the database. The latter options is likely best, because you should also be escaping any % or _ that happen to occur inside the :lookingFor already, so adding the unescaped % before and after would be a natural addition to that task.

answered Mar 28 '20 at 16:03

jjanes

37,812
5
27
34

I've tried doing it, adding the following options all did not work: `found = db.execute("SELECT * FROM books_table WHERE (isbn LIKE '%||:lookingFor||%') OR (title LIKE '%||:lookingFor||%') OR (title LIKE '%||:lookingFor||%') OR (year::text LIKE '%||:lookingFor||%')", {'lookingFor': searchBookVariableOnApplication_py}).fetchall();` returning the same error. The error message gave a weird syntax of '%'robot'%' with extra ' around my search query. Even when I tried doing it programatically, my code doesn't seem to be passing anything into my variable "found". – nvs0000 Mar 29 '20 at 05:00
`'%||:lookingFor||%'` You just added `||` as literal characters inside the string. You need to terminate the string and use the || operator on it to append more to the string. `'%' || :lookingFor || '%'` – jjanes Mar 29 '20 at 14:08

How to db.execute in postgresql using the LIKE operator with variables within flask

1 Answers1

Linked