文章目录psycopg2介绍核心特性安装方式基本使用示例版本演进提示sql.SQL动态 SQL 语句的安全拼接1. 代码逐行解释2. 为什么用 sql.SQL 而不是直接组装字符串❌ 错误示范 1直接字符串拼接SQL 注入漏洞❌ 错误示范 2尝试使用 %s 替换整个 IN 列表语法错误✅ 正确做法使用 psycopg2.sql 模块总结psycopg2介绍psycopg2是 Python 编程语言中最流行且广泛使用的 PostgreSQL 数据库适配器接口。它遵循 Python DB API 2.0 规范充当了 Python 程序与 PostgreSQL 数据库之间通信的桥梁允许开发者在 Python 中轻松连接、查询和操作 PostgreSQL 数据库。核心特性全面支持 DB API 2.0完全遵循 Python 数据库接口规范并支持安全的多线程并发操作适用于高并发场景。底层协议支持采用 C 语言实现了对libpqv3 协议的完全封装支持客户端/服务器端游标、异步通信以及COPY TO/COPY FROM命令。自动类型转换支持 Python 与 PostgreSQL 之间基本数据类型的自动映射与转换如字符串、整型、浮点型、布尔型、日期时间型、Unicode 以及数组类型等。安全性与事务管理支持参数化查询以防止 SQL 注入攻击支持 ACID 事务特性需要显式调用commit()提交变更。安装方式通常使用pip进行安装主要有两种方式二进制安装包psycopg2-binary无需本地编译环境即可快速部署适合初学者和开发测试环境。源码构建版psycopg2需要系统安装 C 编译器和 PostgreSQL 开发库如libpq-dev。虽然配置稍显复杂但官方建议在生产环境中优先使用此版本以获得更好的性能和定制性。基本使用示例使用psycopg2的典型流程包括建立连接、创建游标、执行 SQL 和获取结果importpsycopg2# 1. 建立数据库连接connpsycopg2.connect(hostlocalhost,databasetest,userpostgres,passwordpostgres,port5432)# 2. 创建游标对象cursorconn.cursor()# 3. 执行 SQL 脚本使用 %s 作为参数占位符防止 SQL 注入cursor.execute(SELECT * FROM test WHERE id %s;,(5,))# 4. 获取查询结果# 常用方法有 fetchone() 返回单条, fetchall() 返回所有, fetchmany(n) 返回指定条数resultscursor.fetchall()forrowinresults:print(row)# 5. 提交事务在执行 INSERT, UPDATE, DELETE 后必须调用# conn.commit()# 6. 关闭游标和连接cursor.close()conn.close()版本演进提示值得注意的是psycopg2是旧版本目前官方已推出了新一代的psycopg即 psycopg3。新版本提供了更现代的 API、原生异步编程支持、连接池以及更好的性能优化且仅支持 Python 3。如果是新项目或需要利用最新特性建议直接使用psycopg版本 3。如果是维护依赖 Python 2 的老旧项目或已经深度使用psycopg2的成熟系统继续使用psycopg2仍是稳妥的选择。sql.SQL动态 SQL 语句的安全拼接1. 代码逐行解释user_name[Alice,Bob]cur.execute(sql.SQL(SELECT id, username FROM users_user WHERE username IN ({})).format(sql.SQL(, ).join(map(sql.Literal,user_name))))这段代码的执行过程如下map(sql.Literal, user_name)将user_name列表中的每一个元素比如[Alice, Bob]包装成sql.Literal对象。Literal表示这是一个字面量值在执行时会被安全地转义并加上引号如Alice。sql.SQL(, ).join(...)使用sql.SQL对象作为分隔符将上面转换好的Literal对象拼接起来中间用逗号分隔。结果类似于sql.Literal(Alice), sql.Literal(Bob)。sql.SQL(... IN ({})).format(...)将拼接好的参数安全地填入 SQL 模板的{}占位符中。cur.execute(...)将最终生成的安全 SQL 发送给 PostgreSQL 执行。最终执行的 SQL 大致为SELECTid,usernameFROMusers_userWHEREusernameIN(Alice,Bob)2. 为什么用sql.SQL而不是直接组装字符串在 Python 中我们通常被教导使用%s占位符来防止 SQL 注入例如cur.execute(SELECT * FROM users WHERE id %s,(user_id,))但是%s占位符只能用来替换“值”如字符串、数字不能用来替换“SQL 关键字、表名、列名或 IN 子句的列表”。如果你直接用字符串拼接来构建IN子句会面临以下致命问题❌ 错误示范 1直接字符串拼接SQL 注入漏洞# 假设 user_name [Alice; DROP TABLE users_user; --]sql_strSELECT * FROM users_user WHERE username IN (, .join(user_name))cur.execute(sql_str)如果用户输入了恶意字符你的数据库可能会被删库或数据泄露。❌ 错误示范 2尝试使用%s替换整个 IN 列表语法错误# 假设 user_name [Alice, Bob]cur.execute(SELECT * FROM users_user WHERE username IN (%s),(user_name,))这会导致报错因为psycopg2会将整个列表转义成一个单一的字符串生成的 SQL 变成WHERE username IN ({Alice, Bob})这在 PostgreSQL 中是无效的语法。✅ 正确做法使用psycopg2.sql模块psycopg2.sql模块的设计初衷就是为了解决动态 SQL 结构的安全问题sql.SQL()用于包裹 SQL 关键字、表名、列名、运算符等结构部分。它告诉psycopg2“这是安全的 SQL 代码不要对它进行转义”。sql.Literal()用于包裹用户传入的数据值。它会自动处理转义、引号和特殊字符彻底杜绝 SQL 注入。总结使用sql.SQL和sql.Literal的组合既满足了IN (...)这种动态数量参数的语法需求又完美保留了psycopg2底层的安全转义机制是处理动态 SQL 结构如动态表名、动态列名、动态 IN 列表的唯一官方推荐做法。(注如果你使用的是较新版本的psycopg(v3)它已经支持直接将列表作为参数传入IN %s但在psycopg2中必须使用上述的sql模块方案。)