Cannot insert row in Postgres using Ansible and Jinja

Question

I have an Ansible playbook to backup sono devices, everything seems to work except when I try to insert the backup (and the differences, if any, with another backup) into a Postgres Database for two specific hosts out of 700+.

I think that the problem is with some special characters that need to be replace or escaped, I'm already escaping single quotes in my task but I guess there are other problematic characters.

Is there a way to escape/replace all the characaters in a task via Jinja (or something else)?

Those are the task that are currently run to insert backups without and with differences.

---
- 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)

The error is pretty generic

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',... 

I can't paste what's in the backup since those are company information but the error seems to be:

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

So I think is a problem with single/double quotes or some other "special" character, but when I try to insert that string directly instead of reading from the {{ new_config }} everything seems to work.

Answer 1

After reading the module documentazion again and testing some possible solution, I ended up solving the issue using positional_args and %s in the task, like:

---
- 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
            (%s,%s,%s, %s, %s, %s, %s, %s)
    positional_args:
      - "{{ networking_id }}"
      - "{{ new_config }}"
      - "{{ backup_diff.diff_text }}"
      - True
      - NOW()
      - "{{ ping }}"
      - True
      - True

I also kept the task without the %s and added a check to execute the insert with positional args only when the "original" insert fails.