DevNull 知识库

Appearance

32｜SQL 增删改查与参数安全 ​

表建好后，需要对数据进行增删改查（CRUD）：插入新记录、查询数据、更新字段、删除记录。同时必须防范 SQL 注入——把用户输入直接拼进 SQL 字符串是最常见的安全漏洞之一。

一、插入（INSERT） ​

python
cursor.execute(
    "INSERT INTO servers (hostname, ip, status) VALUES (?, ?, ?)",
    ("web-01", "192.168.1.10", "running"),
)
conn.commit()

print(cursor.lastrowid)   # 刚插入这条的自增 ID

参数用 ? 占位，值放在元组里传。绝对不要用 f-string 或字符串拼接拼 SQL：

python
# 危险：SQL 注入
title = "'; DROP TABLE servers; --"
cursor.execute(f"INSERT INTO servers (hostname) VALUES ('{title}')")
# 实际执行的 SQL：INSERT INTO servers (hostname) VALUES (''; DROP TABLE servers; --')

? 占位符是防 SQL 注入的：用户传入的值只会被当成数据，不会被当成 SQL 代码执行。

批量插入 ​

python
servers = [
    ("web-01", "192.168.1.10", "running"),
    ("web-02", "192.168.1.11", "stopped"),
    ("web-03", "192.168.1.12", "running"),
]

cursor.executemany(
    "INSERT INTO servers (hostname, ip, status) VALUES (?, ?, ?)",
    servers,
)
conn.commit()

executemany 比循环调用 execute 效率更高。

二、查询（SELECT） ​

查询全部 ​

python
cursor.execute("SELECT id, hostname, ip, status FROM servers")
rows = cursor.fetchall()

for row in rows:
    print(row)   # (1, 'web-01', '192.168.1.10', 'running')

条件查询 ​

python
cursor.execute(
    "SELECT * FROM servers WHERE status = ?",
    ("running",),
)
rows = cursor.fetchall()

条件值同样用 ? 占位。

查询单条 ​

python
cursor.execute(
    "SELECT * FROM servers WHERE id = ?",
    (1,),
)
row = cursor.fetchone()   # 没有结果时返回 None

if row:
    print(row["hostname"])

模糊查询 ​

python
cursor.execute(
    "SELECT * FROM servers WHERE hostname LIKE ?",
    ("web-%",),
)

% 是通配符，匹配任意字符序列。web-% 匹配 web-01、web-02 等。

排序和限制 ​

python
# 按创建时间倒序，取前 10 条
cursor.execute(
    "SELECT * FROM servers ORDER BY created_at DESC LIMIT ?",
    (10,),
)
子句作用
ORDER BY 列 ASC升序排列
ORDER BY 列 DESC降序排列
LIMIT n最多返回 n 条
LIMIT n OFFSET m跳过 m 条，返回 n 条

三、更新（UPDATE） ​

python
cursor.execute(
    "UPDATE servers SET status = ? WHERE id = ?",
    ("stopped", 1),
)
conn.commit()

print(cursor.rowcount)   # 受影响的行数

UPDATE 必须带 WHERE 条件，否则更新整张表：

python
# 危险：所有记录的状态都变成 stopped
cursor.execute("UPDATE servers SET status = 'stopped'")

四、删除（DELETE） ​

python
cursor.execute(
    "DELETE FROM servers WHERE id = ?",
    (1,),
)
conn.commit()

DELETE 也必须带 WHERE 条件，否则清空整张表。

五、参数化查询的本质 ​

python
# 参数化查询的执行过程
cursor.execute(
    "SELECT * FROM servers WHERE hostname = ?",
    ("web-01",),
)

数据库驱动会把 SQL 模板和参数分开处理：

  1. 解析 SQL 模板，生成执行计划
  2. 把参数按类型绑定到占位符
  3. 执行计划不变，参数值不会被解析为 SQL 代码

这和字符串拼接的本质区别：拼接后的字符串是一个完整的 SQL 语句，数据库会整体解析；参数化查询中参数只是数据填充。

六、常见错误 ​

参数类型不匹配 ​

python
# 错误：传了字符串但字段是整数类型
cursor.execute("SELECT * FROM servers WHERE id = ?", ("1",))
# SQLite 会自动转换，但其他数据库可能报错

# 正确
cursor.execute("SELECT * FROM servers WHERE id = ?", (1,))

忘记 commit ​

python
# 错误：insert/update/delete 后没有 commit，数据不会保存
cursor.execute("INSERT INTO servers ...", (...))
# 缺少 conn.commit()

# 正确
cursor.execute("INSERT INTO servers ...", (...))
conn.commit()

SELECT 后调用 commit ​

python
# 多余：纯查询不需要 commit
cursor.execute("SELECT * FROM servers")
rows = cursor.fetchall()
conn.commit()   # 无害但多余

executemany 参数格式错误 ​

python
# 错误：参数不是列表的列表/元组的元组
cursor.executemany("INSERT ... VALUES (?)", ["a", "b", "c"])

# 正确：每个参数必须是元组/列表
cursor.executemany("INSERT ... VALUES (?)", [("a",), ("b",), ("c",)])