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: 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>

