如何使用Python的oracledb正确转义含特殊字符的SQL查询

原因分析 #

set escape on是SQL*Plus（Oracle Developer等客户端工具）的客户端命令，并非Oracle数据库原生支持的SQL语句。Python的Oracle驱动（如cx_Oracle）仅能执行标准SQL或会话级ALTER命令，因此直接调用cursor.execute('set escape on;')会触发ORA-00922错误。而Oracle默认将&视为绑定变量占位符，未处理的话会导致SQL执行失败。

解决方法 #

方法1：修改SQL语句，用&&替代&

将SQL中的单个&替换为&&，Oracle会自动解析为普通&字符，无需额外设置客户端命令：

sql = """SELECT * FROM CLINICAL_NOTES
         WHERE NOTE_TYPE IN 
         ( 'ED Notes',
           'ED Provider Notes',
           'ED Triage Notes',
           'H&&P' )"""
cursor.execute(sql)

方法2：使用绑定变量（推荐）

通过绑定变量传递参数，既能规避特殊字符问题，又能防止SQL注入，是更规范的写法：

# 定义需要匹配的NOTE_TYPE列表
target_note_types = ['ED Notes', 'ED Provider Notes', 'ED Triage Notes', 'H&P']

# 构造带占位符的SQL
placeholders = ', '.join([f':{i}' for i in range(1, len(target_note_types)+1)])
sql = f"SELECT * FROM CLINICAL_NOTES WHERE NOTE_TYPE IN ({placeholders})"

# 执行查询，直接传入参数列表
cursor.execute(sql, target_note_types)

方法3：通过ALTER SESSION禁用变量替换

如果需要全局关闭当前会话的&变量解析，可执行数据库支持的会话级ALTER命令：

# 先禁用当前会话的DEFINE变量替换
cursor.execute("ALTER SESSION SET DEFINE OFF")

# 再执行原SQL语句（无需转义&）
sql = """SELECT * FROM CLINICAL_NOTES
         WHERE NOTE_TYPE IN 
         ( 'ED Notes',
           'ED Provider Notes',
           'ED Triage Notes',
           'H&P' )"""
cursor.execute(sql)

内容的提问来源于stack exchange，提问作者horcle_buzz