oracle - Python Parameter pass to prevent sql injection. Why is this giving an error? -
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 dt_catalog" sql = sql + " cat_id = %s , 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
in code, you're using %s python substition string syntax, expects substitution values on same line, e.g.
sql = sql + " cat_id = %s , commons_id = %s " % (p_cat_id, p_commons_id) however, (as stated already) not best way because (a) can sql injection vulnerability; , (b) cause poor database performance due each call requiring hard parse of new sql statement.
instead, should use oracle bind variable syntax, e.g.:
c1 = connection.cursor() sql = "select commons_id, cat_id, cat_name dt_catalog" sql = sql + " cat_id = :foo , 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. first parameter bound first bind placeholder, , second parameter in list bound second placeholder.
a nicer method using dict assign values specific bind variables name. useful when difficult know order in placeholders have been added query, , makes code easier read , maintain:
c1 = connection.cursor() sql = "select commons_id, cat_id, cat_name dt_catalog" sql = sql + " cat_id = :foo , commons_id = :bar " param = {"foo": p_cat_id, "bar": p_commons_id} c1.execute(sql, param) return c1 more examples , tutorials: http://st-curriculum.oracle.com/obe/db/11g/r2/prod/appdev/opensrclang/pythonhol2010_db/python_db.htm
Comments
Post a Comment