mysql in语句参数化

SELECT * FROM users WHERE user_id IN (1, 2, 3);

import mysql.connector

# 连接到数据库
db = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="yourdatabase"
)

cursor = db.cursor()

# 用户ID列表
user_ids = [1, 2, 3]

# 参数化查询
query = "SELECT * FROM users WHERE user_id IN %s"
cursor.execute(query, (tuple(user_ids),))

# 获取结果
results = cursor.fetchall()

for row in results:
    print(row)

# 关闭连接
cursor.close()
db.close()

# 错误示例
query = "SELECT * FROM users WHERE user_id IN %s"
cursor.execute(query, [1, 2, 3])  # 这里应该传递一个元组

# 正确示例
query = "SELECT * FROM users WHERE user_id IN %s"
cursor.execute(query, (1, 2, 3))  # 传递一个元组

# 不安全的示例
user_input = "1; DROP TABLE users;"
query = "SELECT * FROM users WHERE user_id IN (%s)"
cursor.execute(query, (user_input,))  # 这会导致SQL注入

# 安全的示例
user_input = "1, 2, 3"
user_ids = [int(id) for id in user_input.split(',')]
query = "SELECT * FROM users WHERE user_id IN %s"
cursor.execute(query, (tuple(user_ids),))  # 确保输入经过验证和清理