SQLite 参数替换问题 [英] SQLite parameter substitution problem

问题描述

在 Python 2.5 中使用 SQLite3，我尝试遍历列表并根据项目名称从数据库中提取项目的权重.

我尝试使用?"建议使用参数替换来防止 SQL 注入，但它不起作用.例如，当我使用:

 用于 self.inventory_names 中的项目:self.cursor.execute("SELECT weight FROM Equipment WHERE name = ?", item)self.cursor.close()

我收到错误:

<块引用>

sqlite3.ProgrammingError:提供的绑定数量不正确.当前语句使用1个，提供了8个.

我相信这是由数据库的初始创建引起的；我制作的实际创建数据库的模块确实有 8 个绑定.

cursor.execute("""CREATE TABLE Equipment(id 整数主键，名称文本，价格整数，重量真实，信息文本，ammo_cap 整数，可用性_西部文本，可用性_东文本)""")

但是，当我对每个项目名称使用不太安全的%s"替换时，它工作得很好.像这样:

 用于 self.inventory_names 中的项目:self.cursor.execute("SELECT weight FROM Equipment WHERE name = '%s'" % item)self.cursor.close()

当我只调用一个绑定时，我不明白为什么它认为我有 8 个绑定.我该如何解决?

解决方案

Cursor.execute() 方法需要一个序列作为第二个参数.您提供的字符串恰好是 8 个字符长.

改用以下表格:

self.cursor.execute("SELECT weight FROM Equipment WHERE name = ?", [item])

Python 库参考:sqlite3 光标对象.>

Using SQLite3 with Python 2.5, I'm trying to iterate through a list and pull the weight of an item from the database based on the item's name.

I tried using the "?" parameter substitution suggested to prevent SQL injections but it doesn't work. For example, when I use:

for item in self.inventory_names:
    self.cursor.execute("SELECT weight FROM Equipment WHERE name = ?", item)
    self.cursor.close()

I get the error:

sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 8 supplied.

I believe this is somehow caused by the initial creation of the database; the module I made that actually creates the DB does have 8 bindings.

cursor.execute("""CREATE TABLE Equipment 
    (id INTEGER PRIMARY KEY, 
    name TEXT,
    price INTEGER, 
    weight REAL, 
    info TEXT, 
    ammo_cap INTEGER, 
    availability_west TEXT,
    availability_east TEXT)""")

However, when I use the less-secure "%s" substitution for each item name, it works just fine. Like so:

for item in self.inventory_names:
    self.cursor.execute("SELECT weight FROM Equipment WHERE name = '%s'" % item)
    self.cursor.close()

I can't figure out why it thinks I have 8 bindins when I'm only calling one. How can I fix it?

解决方案

The Cursor.execute() method expects a sequence as second parameter. You are supplying a string which happens to be 8 characters long.

Use the following form instead:

self.cursor.execute("SELECT weight FROM Equipment WHERE name = ?", [item])

Python library reference: sqlite3 Cursor Objects.

这篇关于SQLite 参数替换问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！