How to properly use SQL LIKE statement to query DB from Flask application

Issue

I am having a very hard time using a LIKE statement in my Flask application to query my DB. I keep getting a syntax error.

My application is for searching for books and I need to be able to search all columns. I know I need to use LIKE but it seems that the single quotes from my variable are getting in the way. However, I am not 100% positive.

I know if I wanted to SELECT all from a table based on in id, the format would be like this:

id = request.form.get('id')
name = db.execute("SELECT first_name FROM users WHERE id=:id", {"id": current_id})

If I try this similar format with LIKE, such as:

search = request.values.get('search')
books = db.execute("SELECT * FROM books WHERE author LIKE '%:search%'", {"search": search}).fetchall()

I get this error:

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

[SQL: SELECT * FROM books WHERE author LIKE '%%%(search)s%%']
[parameters: {'search': 'Raymond'}]
(Background on this error at: http://sqlalche.me/e/f405)

From this error, I can see that single quotes are being added to my variable value (search) but I haven’t been successful in stripping or replacing them. So I tried this format:

search = request.values.get('search')
books = db.execute("SELECT * FROM books WHERE author LIKE %s", ('%' + search + '%',)).fetchall()

But that also gave me an error:
AttributeError: 'list' object has no attribute 'keys'

I’ve been working on this for about four days and nothing I’ve tried has worked. I am at a loss. I know that I need to get those single quotes off, but I am so unsure how. I have a feeling that it’s super easy, which is going to make me so sad but I am trying my best as a newbie.

Please let me know if you need any other information.

EDIT: Including tracebacks

Traceback for books = db.execute("SELECT * FROM books WHERE author LIKE %s", ('%' + search + '%',)).fetchall()

AttributeError
AttributeError: 'tuple' object has no attribute 'keys'

Traceback (most recent call last)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 2464, in __call__
return self.wsgi_app(environ, start_response)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 2450, in wsgi_app
response = self.handle_exception(e)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 1867, in handle_exception
reraise(exc_type, exc_value, tb)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\_compat.py", line 39, in reraise
raise value
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 2447, in wsgi_app
response = self.full_dispatch_request()
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 1952, in full_dispatch_request
rv = self.handle_user_exception(e)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 1821, in handle_user_exception
reraise(exc_type, exc_value, tb)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\_compat.py", line 39, in reraise
raise value
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 1950, in full_dispatch_request
rv = self.dispatch_request()
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 1936, in dispatch_request
return self.view_functions[rule.endpoint](**req.view_args)
File "C:\Users\Nilaja Williams\Desktop\Harvard Studies\nilajawill\project1\application.py", line 73, in current_search
books = db.execute("SELECT * FROM books WHERE author LIKE %s", ('%' + search + '%',)).fetchall()
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\orm\scoping.py", line 163, in do
return getattr(self.registry(), name)(*args, **kwargs)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\orm\session.py", line 1291, in execute
return self._connection_for_bind(bind, close_with_result=True).execute(
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\engine\base.py", line 1020, in execute
return meth(self, multiparams, params)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\sql\elements.py", line 298, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\engine\base.py", line 1099, in _execute_clauseelement
keys = list(distilled_params[0].keys())
AttributeError: 'tuple' object has no attribute 'keys'
The debugger caught an exception in your WSGI application. You can now look at the traceback which led to the error.
To switch between the interactive traceback and the plaintext one, you can click on the "Traceback" headline. From the text traceback you can also create a paste of it. For code execution mouse-over the frame you want to debug and click on the console icon on the right side.

You can execute arbitrary Python code in the stack frames and there are some extra helpers available for introspection:

dump() shows all variables in the frame
dump(obj) dumps all that's known about the object

Traceback for books = db.execute("SELECT * FROM books WHERE author LIKE '%:search%'", {"search": search}).fetchall()

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

[SQL: SELECT * FROM books WHERE author LIKE '%%%(search)s%%']
[parameters: {'search': 'Raymond'}]
(Background on this error at: http://sqlalche.me/e/f405)

Traceback (most recent call last)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\engine\base.py", line 1247, in _execute_context
self.dialect.do_execute(
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\engine\default.py", line 590, in do_execute
cursor.execute(statement, parameters)
The above exception was the direct cause of the following exception:
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 2464, in __call__
return self.wsgi_app(environ, start_response)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 2450, in wsgi_app
response = self.handle_exception(e)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 1867, in handle_exception
reraise(exc_type, exc_value, tb)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\_compat.py", line 39, in reraise
raise value
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 2447, in wsgi_app
response = self.full_dispatch_request()
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 1952, in full_dispatch_request
rv = self.handle_user_exception(e)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 1821, in handle_user_exception
reraise(exc_type, exc_value, tb)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\_compat.py", line 39, in reraise
raise value
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 1950, in full_dispatch_request
rv = self.dispatch_request()
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 1936, in dispatch_request
return self.view_functions[rule.endpoint](**req.view_args)
File "C:\Users\Nilaja Williams\Desktop\Harvard Studies\nilajawill\project1\application.py", line 73, in current_search
books = db.execute("SELECT * FROM books WHERE author LIKE '%:search%'", {"search": search}).fetchall()
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\orm\scoping.py", line 162, in do
return getattr(self.registry(), name)(*args, **kwargs)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\orm\session.py", line 1277, in execute
return self._connection_for_bind(bind, close_with_result=True).execute(
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\engine\base.py", line 984, in execute
return meth(self, multiparams, params)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\sql\elements.py", line 293, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\engine\base.py", line 1097, in _execute_clauseelement
ret = self._execute_context(
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\engine\base.py", line 1287, in _execute_context
self._handle_dbapi_exception(
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\engine\base.py", line 1481, in _handle_dbapi_exception
util.raise_(
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\util\compat.py", line 178, in raise_
raise exception
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\engine\base.py", line 1247, in _execute_context
self.dialect.do_execute(
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\engine\default.py", line 590, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "Raymond"
LINE 1: SELECT * FROM books WHERE author LIKE '%'Raymond'%'
^

[SQL: SELECT * FROM books WHERE author LIKE '%%%(search)s%%']
[parameters: {'search': 'Raymond'}]
(Background on this error at: http://sqlalche.me/e/f405)
The debugger caught an exception in your WSGI application. You can now look at the traceback which led to the error.
To switch between the interactive traceback and the plaintext one, you can click on the "Traceback" headline. From the text traceback you can also create a paste of it. For code execution mouse-over the frame you want to debug and click on the console icon on the right side.

You can execute arbitrary Python code in the stack frames and there are some extra helpers available for introspection:

dump() shows all variables in the frame
dump(obj) dumps all that's known about the object

Solution

For parameterized queries you never need to have quotes inside the statement, they will be added by the db api. This should work:

search = request.values.get('search')
books = db.execute("SELECT * FROM books WHERE author LIKE :search", {"search": '%' + search + '%'}).fetchall()

The % signs need to be part of the parameter, so that :search can finally be replaced with the quoted search string, as described here.

The error messages show what happens if the query already contains quotes: anohter set of qoutes will be added, breaking the query.

But note that this could also have problems as described here: backslashes in LIKE expresssions are treated as escape characters which can be used to treat wildcards into the passed string as their literal value, but if not used correctly can cause unexpected search results, or can enven cause an error (e.g. if the like pattern ends with a backslash).
If you don’t intend to allow the user to insert wildcards in the search string, you’d need to escape them, or consider using the position() funciton instead:

position(:search in author) > 0

Answered By – mata

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published