Dynamic SQL queries with SQLite3

Question

I would like to allow users to query a sql database. The database is here

So the user will be able to enter the queries they want:

csr_city= input("Enter a city >>").lower().strip().replace(' ','')
csr_type = input("Enter a type >>").lower().strip()

and then the query will execute:

cur = conn.cursor()
cur.execute('SELECT * FROM crime_scene_report WHERE city=?  AND type=? ', (csr_city,csr_type))
rows = cur.fetchall()
rows

If the user enters both variables like city='SQL City' and type='murder' it works as it finds both values in the same row, but if the user leaves one empty, ex type, then it returns a blank table as both conditions do not exist on one single row.

What I would like to do is for SELECT to ignore the variable if it is empty. I guess I could do it with if statements but that would create a mess?? Is there a better way?

I tried How to ignore the condition that user did not pass in SQLITE?, but didnt work for me, still getting empty tables.

Try passing the city as a parameter twice coupled with: `WHERE (city = ? or ? IS NULL)`might work. Alternatively you might try `WHERE city LIKE ISNULL(?,'')+'%'` — JonSG, Feb 03 '23 at 15:37
@JonSG `?` will never be `NULL`, since that corresponds to `None` in Python, not an empty string. — Barmar, Feb 03 '23 at 15:38
The solution in that question should have worked. Show how you tried to implement it. — Barmar, Feb 03 '23 at 15:39
Well to improve it, you can fetch all unique city and type and keep them in set and for user input check if that match to cities and types present or not if match then run the query to fetch all result. — sahasrara62, Feb 03 '23 at 15:47
I read somewhere that None is interpreted by SQLite as null but not sure if it is true — ruthpozuelo, Feb 03 '23 at 16:13

score 1 · Accepted Answer · answered Feb 03 '23 at 15:40

1

You're effectively looking for an SQL query builder (such as SQLAlchemy's core layer), but to begin with, some ifs are just fine:

csr_city = input("Enter a city >>").lower().strip().replace(" ", "")
csr_type = input("Enter a type >>").lower().strip()

cur = conn.cursor()

sql_where = []
args = []
if csr_city:
    sql_where.append("city = ?")
    args.append(csr_city)
if csr_type:
    sql_where.append("type = ?")
    args.append(csr_type)

if not sql_where:
    sql_where.append("1 = 1")  # always true

sql_query = f'SELECT * FROM crime_scene_report WHERE {" AND ".join(sql_where)}'
cur.execute(sql_query, args)
rows = cur.fetchall()

answered Feb 03 '23 at 15:40

AKX

152,115
15
115
172

Works perfectly and it wasnt as complicated as I thought it would be with if statements. Fabulous. One question, do you mean that if i use SQL Alchemy to query SQLite I can then avoid the if statements? – ruthpozuelo Feb 03 '23 at 16:20
Another question, what is this doing? `if not sql_where: sql_where.append("1 = 1") # always true` – ruthpozuelo Feb 03 '23 at 16:51
It makes sure there's always some where clause if the user didn't enter anything else. If you use SQLAlchemy, it basically generates the SQL for you. – AKX Feb 03 '23 at 18:00
Thanks! Last question: I have been told that the code is vulnerable to sql injections. Is there a way to protect it or sqlalchemy is the only way? – ruthpozuelo Feb 04 '23 at 08:28
This code is not vulnerable, as it uses parameterized queries. – AKX Feb 04 '23 at 10:40

Dynamic SQL queries with SQLite3

1 Answers1