如何使用python db api安全地生成类SQL语句

2024-05-14 18:52:13 发布

您现在位置:Python中文网/ 问答频道 /正文

我正在尝试使用python的db api组装以下SQL语句:

SELECT x FROM myTable WHERE x LIKE 'BEGINNING_OF_STRING%';

其中,字符串的开头应该是一个python变量,可以通过DB-API安全地填充。我试过了

beginningOfString = 'abc'

cursor.execute('SELECT x FROM myTable WHERE x LIKE '%s%', beginningOfString) 
cursor.execute('SELECT x FROM myTable WHERE x LIKE '%s%%', beginningOfString)

我没主意了,正确的方法是什么?


Tags: of字符串fromapiexecutedbsqlmytable
3条回答

编辑:

正如Brian和Thomas所指出的,更好的方法是使用:

beginningOfString += '%'
cursor.execute("SELECT x FROM myTable WHERE x LIKE ?", (beginningOfString,) )

因为第一个方法会让您面临SQL注入攻击。


留作历史:

尝试:

cursor.execute("SELECT x FROM myTable WHERE x LIKE '%s%%'" % beginningOfString)

注意Sqlite3文档:

Usually your SQL operations will need to use values from Python variables. You shouldn’t assemble your query using Python’s string operations because doing so is insecure; it makes your program vulnerable to an SQL injection attack.

Instead, use the DB-API’s parameter substitution. Put ? as a placeholder wherever you want to use a value, and then provide a tuple of values as the second argument to the cursor’s execute() method. (Other database modules may use a different placeholder, such as %s or :1.) For example:

# Never do this -- insecure!
symbol = 'IBM'
c.execute("... where symbol = '%s'" % symbol)

# Do this instead
t = (symbol,)
c.execute('select * from stocks where symbol=?', t)

# Larger example
for t in [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
          ('2006-04-05', 'BUY', 'MSOFT', 1000, 72.00),
          ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
         ]:
    c.execute('insert into stocks values (?,?,?,?,?)', t)

我想你想要这个:

cursor.execute('SELECT x FROM myTable WHERE x LIKE '%?%', (beginningOfString,) )

如果可以,最好将参数与sql分离。 然后您可以让db模块处理参数的正确引用。

sql='SELECT x FROM myTable WHERE x LIKE %s'
args=[beginningOfString+'%']
cursor.execute(sql,args)

相关问题 更多 >

    热门问题