使用Ansible和Jinja向PostgreSQL插入行失败的问题求助

我编写了一个用于备份Sono设备的Ansible Playbook，在700余台主机中，仅两台主机无法将备份数据（含差异内容）插入PostgreSQL数据库。目前已对单引号做转义处理，但仍有特殊字符导致报错，想知道能否通过Jinja或其他方式批量转义所有特殊字符？

当前任务代码 #

无差异备份插入任务 #

---
- name: Insert row with backup without difference
  vars:
    backup_column: "{{ backup_column }}"
  local_action:        
    module: community.postgresql.postgresql_query        
    login_host: '{{ lookup("ansible.builtin.env","networking_pg_host") }}'
    login_password: '{{ networking_db_password }}'
    login_user: '{{ lookup("ansible.builtin.env","networking_pg_user") }}'
    db: '{{ lookup("ansible.builtin.env","networking_pg_db") }}'
    port: '{{ lookup("ansible.builtin.env","networking_pg_port")|int }}'
    query: INSERT INTO backup
            (inventory_id, {{ backup_column }}, backup_done, backup_date, backup_ping, backup_ssh_connection,
            backup_ssh_authentication)
            VALUES
            ('{{ networking_id }}',
              '{{ new_config|regex_replace("[']", "''")}}', True, NOW(), '{{ ping }}', True, True)

带差异备份插入任务 #

---
- name: Insert row with backup and differences
  vars:
    backup_column: "{{ backup_column }}"
  local_action:        
    module: community.postgresql.postgresql_query        
    login_host: '{{ lookup("ansible.builtin.env","networking_pg_host") }}'
    login_password: '{{ networking_db_password }}'
    login_user: '{{ lookup("ansible.builtin.env","networking_pg_user") }}'
    db: '{{ lookup("ansible.builtin.env","networking_pg_db") }}'
    port: '{{ lookup("ansible.builtin.env","networking_pg_port")|int }}'
    query: INSERT INTO backup
            (inventory_id, {{ backup_column }}, last_backup_diff, backup_done, backup_date, backup_ping, backup_ssh_connection,
            backup_ssh_authentication)
            VALUES
            ('{{ networking_id }}',
              '{{ new_config|regex_replace("[']", "''") }}', 
      '{{ backup_diff.diff_text|regex_replace("[']", "''") }}', True, NOW(), '{{ ping }}', True, True)

报错信息 #

msg": "Cannot execute SQL 'INSERT INTO backup (inventory_id, backup_rsc, backup_done, backup_date, backup_ping, backup_ssh_connection, backup_ssh_authentication) VALUES ('4210',... 

具体语法错误：

None: syntax error at or near \"4\"\nLINE 129: ...r $1a$}~-(&1J|xE$0XvxCrfK7BLr&7V0p8g;x&EkV@+:o85z&4''ce\\''4$\n ^\n 

注意：直接手动将该字符串插入数据库时正常，仅通过{{ new_config }}变量插入时出错。

解决方案 #

不要手动拼接SQL语句，采用参数化查询是解决特殊字符转义和SQL注入问题的标准方案，community.postgresql.postgresql_query模块原生支持该功能。

修改后的无差异备份任务 #

---
- name: Insert row with backup without difference
  vars:
    backup_column: "{{ backup_column }}"
    insert_query: >
      INSERT INTO backup
      (inventory_id, {{ backup_column }}, backup_done, backup_date, backup_ping, backup_ssh_connection, backup_ssh_authentication)
      VALUES (%s, %s, %s, NOW(), %s, %s, %s)
    query_params:
      - "{{ networking_id }}"
      - "{{ new_config }}"
      - True
      - "{{ ping }}"
      - True
      - True
  local_action:        
    module: community.postgresql.postgresql_query        
    login_host: '{{ lookup("ansible.builtin.env","networking_pg_host") }}'
    login_password: '{{ networking_db_password }}'
    login_user: '{{ lookup("ansible.builtin.env","networking_pg_user") }}'
    db: '{{ lookup("ansible.builtin.env","networking_pg_db") }}'
    port: '{{ lookup("ansible.builtin.env","networking_pg_port")|int }}'
    query: "{{ insert_query }}"
    params: "{{ query_params }}"

修改后的带差异备份任务 #

---
- name: Insert row with backup and differences
  vars:
    backup_column: "{{ backup_column }}"
    insert_query: >
      INSERT INTO backup
      (inventory_id, {{ backup_column }}, last_backup_diff, backup_done, backup_date, backup_ping, backup_ssh_connection, backup_ssh_authentication)
      VALUES (%s, %s, %s, %s, NOW(), %s, %s, %s)
    query_params:
      - "{{ networking_id }}"
      - "{{ new_config }}"
      - "{{ backup_diff.diff_text }}"
      - True
      - "{{ ping }}"
      - True
      - True
  local_action:        
    module: community.postgresql.postgresql_query        
    login_host: '{{ lookup("ansible.builtin.env","networking_pg_host") }}'
    login_password: '{{ networking_db_password }}'
    login_user: '{{ lookup("ansible.builtin.env","networking_pg_user") }}'
    db: '{{ lookup("ansible.builtin.env","networking_pg_db") }}'
    port: '{{ lookup("ansible.builtin.env","networking_pg_port")|int }}'
    query: "{{ insert_query }}"
    params: "{{ query_params }}"

为什么参数化查询能解决问题？ #

• 参数化查询由PostgreSQL驱动自动处理所有特殊字符的转义，包括单引号、反斜杠、特殊符号等，无需手动编写正则替换规则。
• 彻底规避SQL注入风险，同时确保SQL语句语法绝对正确。

备选方案：使用Jinja的postgres_quote过滤器 #

如果必须手动处理字符串（不推荐），可以使用Ansible内置的postgres_quote过滤器，它专门针对PostgreSQL做字符串转义：

'{{ new_config | postgres_quote }}'

但仍优先推荐参数化查询，这是更安全、更可靠的解决方案。

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