用 Python(PyMySQL/SQLAlchemy)安全操作数据库

用 Python 连 MySQL 时,安全第一原则是:永远不要把用户输入拼进 SQL 字符串。本文给出 PyMySQL 与 SQLAlchemy 的正确姿势。

PyMySQL 参数化查询

import pymysql

conn = pymysql.connect(
    host="127.0.0.1",
    user="app",
    password="***",
    database="demo",
    charset="utf8mb4",
    cursorclass=pymysql.cursors.DictCursor,
)

def get_user(user_id: int):
    sql = "SELECT id, name, email FROM users WHERE id=%s"
    with conn.cursor() as cur:
        cur.execute(sql, (user_id,))
        return cur.fetchone()

# 错误示范:f-string 拼接,有 SQL 注入风险
# cur.execute(f"SELECT * FROM users WHERE id={user_id}")

批量插入

rows = [("Alice", "a@x.com"), ("Bob", "b@x.com")]
sql = "INSERT INTO users(name, email) VALUES (%s, %s)"
with conn.cursor() as cur:
    cur.executemany(sql, rows)
conn.commit()

SQLAlchemy 核心写法(简版)

from sqlalchemy import create_engine, text

engine = create_engine("mysql+pymysql://app:***@127.0.0.1:3306/demo?charset=utf8mb4")

with engine.begin() as conn:
    result = conn.execute(
        text("SELECT * FROM users WHERE city=:city"),
        {"city": "北京"},
    )
    for row in result.mappings():
        print(row)

工程实践清单

  • 连接信息放环境变量,不写进仓库
  • 生产库账号最小权限(不要随便用 root)
  • 读写超时、连接池、失败重试要有
  • 变更数据必须事务:成功 commit,失败 rollback

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注