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
– JonSG, Commented Feb 3, 2023 at 15:37
@JonSG ? will never be NULL, since that corresponds to None in Python, not an empty string. — Barmar
– Barmar, Commented Feb 3, 2023 at 15:38
The solution in that question should have worked. Show how you tried to implement it. — Barmar
– Barmar, Commented Feb 3, 2023 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
– sahasrara62, Commented Feb 3, 2023 at 15:47
I read somewhere that None is interpreted by SQLite as null but not sure if it is true — ruthpozuelo
– ruthpozuelo, Commented Feb 3, 2023 at 16:13

AKX · Accepted Answer · 2023-02-03 15:40:24Z

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 3, 2023 at 15:40

AKX

171k16 gold badges146 silver badges228 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

ruthpozuelo Over a year ago

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 Over a year ago

Another question, what is this doing? if not sql_where: sql_where.append("1 = 1") # always true

AKX Over a year ago

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.

ruthpozuelo Over a year ago

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?

AKX Over a year ago

This code is not vulnerable, as it uses parameterized queries.

Collectives™ on Stack Overflow

Dynamic SQL queries with SQLite3

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related