Python参数传递防止sql注入。为什么这会出错？

发布于 2024-10-17 16:59:17 字数 681 浏览 2 评论 0 原文

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

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

拥醉 2024-10-24 16:59:17

在您的代码中，您使用 %s 这是 python 替换字符串语法，它期望替换值位于同一行，例如

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

但是，这（如前所述）不是最好的方法，因为（ a) 可能是SQL注入漏洞； (b) 由于每次调用都需要对新的 SQL 语句进行硬解析，因此可能会导致数据库性能不佳。

相反，您应该使用 Oracle Bind 变量语法，例如：

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

上面的例子使用了位置绑定，即第一个参数是绑定到第一个绑定占位符，列表中的第二个参数绑定到第二个占位符。

更好的方法是使用字典按名称将值分配给特定的绑定变量。当很难知道占位符添加到查询的顺序时，这非常有用，并且使代码更易于阅读和维护：

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

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

回复收藏 0 原文

~没有更多了~