Multiple placeholder SQL in Python script not working

Question

I'm trying to insert two dates to an SQL query, but the script gives me the following error: There is an error in the Oracle database: ORA-01036: illegal variable name/number

starting_date='01/01/2023'
ending_date='01/04/2023'

params = (starting_date,ending_date)
cur.execute('SELECT analisi.codice, analisi.descrizione FROM analisi LEFT JOIN ris ON analisi.codice = ris.analisi LEFT JOIN ric ON ris.richiesta_id = ric.richiesta_id WHERE ric.richiesta_ins_data >= TO_DATE(?, \'dd/mm/yyyy\')         AND ric.richiesta_ins_data <= TO_DATE(?, \'dd/mm/yyyy\') AND ris01 IS NOT NULL AND ris01 NOT IN (\'N01\',\'N10\') GROUP BY analisi.codice, analisi.descrizione', params)

I've tried with only one placeholder and it works

sql = "SELECT analisi.codice, analisi.descrizione FROM analisi LEFT JOIN  ris ON analisi.codice = ris.analisi LEFT JOIN ric ON ris.richiesta_id = ric.richiesta_id WHERE ric.richiesta_ins_data >= TO_DATE('%s', \'dd/mm/yyyy\')         AND ric.richiesta_ins_data <= TO_DATE(\'31/03/2023\', \'dd/mm/yyyy\') AND ris01 IS NOT NULL AND ris01 NOT IN (\'N01\',\'N10\') GROUP BY analisi.codice, analisi.descrizione" %starting_date
        
        # cur.execute(sql)

What I'm doing wrong?

Answer 1

The problem is using string concatenation and interpolation in the first place, and can't be fixed with any amount of quoting or sanitization. This allows SQL injection attacks and conversion errors like those we still see in the news, about huge bills due to "computer errors". Is that date April 1 or January 4 and *why should anyone assume either"?

A far better and actually easier option is to use query parameters. In that case, the parameter values are never converted to text and never become part of the query, not even the execution plan. The values are passed to the database without change. The database compiles the query into an execution plan with parameters and executes it using the values of the query parameters.

In Oracle query parameters are called Bind Variables and can be passed by name or position. Using them results in cleaner, not just safer code:

sql = """insert into departments (department_id, department_name)
         values (:dept_id, :dept_name)"""
cursor.execute(sql, [280, "Facility"])

The question's code can be changed to this:

from datetime import date


sql=""" 
    SELECT analisi.codice, analisi.descrizione 
    FROM analisi LEFT JOIN ris ON analisi.codice = ris.analisi 
    LEFT JOIN ric ON ris.richiesta_id = ric.richiesta_id 
    WHERE ric.richiesta_ins_data >= :ins_start 
      AND ric.richiesta_ins_data <= :ins_end
      AND ris01 IS NOT NULL 
      AND ris01 NOT IN (:ris1,:ris2) 
    GROUP BY analisi.codice, analisi.descrizione
    """

starting_date=date(2023,1,1)
ending_date=date(2023,4,1)
ris1='N01'
ris2='N10'

cur.execute(sql,[starting_date,ending_date,ris1,ris2])