'Sanitizing SQL fstring query for psycopg2

I'm iteratively building up my SQL query to execute as such:


        sql = "SELECT * FROM txs "

        param_values = []
        if params:
            sql += f"WHERE name=%s" 
            param_values.append(params.name)
        
        ...

        with _psql_connection() as c:
            c.execute(sql, (*param_values)
            return c.fetchall()

It seems as though me using the fstring to append the name is injectable, and I'm not sure what the right solution here is. Shouldn't c.execute(sql, (*param_values) sanitize the input already ?

pythonpostgresqlpsycopg2sanitization


Solution 1:[1]

import psycopg2 
from psycopg2 import sql 
con = psycopg2.connect(dbname="test", host='localhost', user='postgres')

params = True
sql_str = sql.SQL("SELECT * FROM txs ")
where_str = sql.SQL("WHERE name=%s")

param_values = []
if params:
    sql_str = sql.Composed([sql_str, where_str])
    param_values.append(params.name)

print(sql_str.as_string(con))
SELECT * FROM txs WHERE name=%s


with con.cursor() as c:
  c.execute(sql_str, param_values)
  return c.fetchall()

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Adrian Klaver

Related Questions