Python Parameter pass to prevent sql injection. Why is this giving an error?

Question

from django.db import connection, transaction

def pk_dt_catalog(p_CAT_ID,p_COMMONS_ID):

    c1 = connection.cursor()
    sql = "SELECT COMMONS_ID, CAT_ID, CAT_NAME 
             FROM DT_CATALOG"

    sql = sql + " WHERE CAT_ID = %s 
                    AND COMMONS_ID = %s "

    param =(p_CAT_ID, p_COMMONS_ID)
    c1.execute(sql, param)
    return c1


>>> c = dt_catalog.pk_dt_catalog(513704,401)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "dt_catalog.py", line 24, in pk_dt_catalog
    c1.execute(sql,(p_CAT_ID, p_COMMONS_ID,))
cx_Oracle.DatabaseError: ORA-01036: illegal variable name/number

Answer 1

In your code, you're using %s which is python substition string syntax, which expects the substitution values on the same line, e.g.

sql = sql + " WHERE CAT_ID = %s
                AND COMMONS_ID = %s " % (p_CAT_ID, p_COMMONS_ID)

However, this (as stated already) is not the best way because (a) it can be a SQL injection vulnerability; and (b) it will probably cause poor database performance due to each call requiring a hard parse of a new SQL statement.

Instead, you should use Oracle Bind variable syntax, e.g.:

c1 = connection.cursor()
sql = "SELECT COMMONS_ID, CAT_ID, CAT_NAME 
         FROM DT_CATALOG"

sql = sql + " WHERE CAT_ID = :foo 
                AND COMMONS_ID = :bar "

param = (p_CAT_ID, p_COMMONS_ID)
c1.execute(sql, param)
return c1

More info: http://guyharrison.squarespace.com/blog/2010/1/17/best-practices-with-python-and-oracle.html

The above example uses positional binding, i.e. the first parameter is bound to the first bind placeholder, and the second parameter in the list is bound to the second placeholder.

A nicer method is using a dict to assign values to specific bind variables by name. This is useful when it is difficult to know the order in which the placeholders have been added to the query, and makes the code easier to read and maintain:

c1 = connection.cursor()
sql = "SELECT COMMONS_ID, CAT_ID, CAT_NAME 
         FROM DT_CATALOG"

sql = sql + " WHERE CAT_ID = :foo 
                AND COMMONS_ID = :bar "

param = {"foo": p_CAT_ID, "bar": p_COMMONS_ID}
c1.execute(sql, param)
return c1

More examples and tutorials: http://st-curriculum.oracle.com/obe/db/11g/r2/prod/appdev/opensrclang/pythonhol2010_db/python_db.htm