How can I safely "inject" values to my query WHERE clause (Python, Pandas)?-CodePudding

I have the following query:

q = "SELECT id, age, weight, status from my_table"

conn is given. I would like to add a WHERE clause with some params I will get from the users (securely). For example WHERE id = 64 AND status=4 or any other combination I will pass to it.

This query is part of a function:

import pandas as pd

def customized_q(params):

    return pd.read_sql_query(q, conn, params)

I want the end result to take the params with their values and add to WHERE clause with AND operator.

For example if params = {'id':64, 'status':4} it will be translated to a modified q:

"SELECT id, age, weight, status from my_table WHERE id=5 AND status=4"

Please advise how can I achieve it?

CodePudding user response：

You want to create the WHERE clause on the fly, based on the params:

query = "SELECT id, age, weight, status from my_table WHERE "
query  = ' AND '.join([f'{k}={v}' for k,v in params.items()])

CodePudding user response：

If you know that you will always have an ID and a status you could do the following. However, this would only work if you knew every time both params would not be null. if they weren't you would have to back in some additional logic

query = f"""
SELECT 
    id, 
    age,
    weight, 
    status 
from my_table 
WHERE 1=1
    and id = {params['id']}
    AND status = {params['status']}
"""
print(query)
```