How to correctly handle apostrophes in Ansible templates for SQL statements?

Question

I wrote an Ansible playbook to grab information from some YAML files and store it in a MariaDB database.

It uses this Jinja SQL template:

REPLACE INTO mytable(hostname,fqdn,owner, ...) VALUES 
{% for h in ansible_play_hosts_all %}
(
    '{{ h }}',
    '{{ hostvars[h]['myvars'][0]['fqdn'] | default('FQDN unknown') }}',
    '{{ hostvars[h]['myvars'][0]['server_owner'] | default('No owner') }}',
(...)

and this task:

- name: Upload to database
  community.mysql.mysql_query:
    login_host: "{{ db_host }}"
    login_port: "{{ db_port }}"
    login_user: "{{ db_user }}"
    login_password: "{{ db_password }}"
    login_db: "{{ db_name }}"
    query: "{{ lookup('template', 'template.sql.j2') }}"
  delegate_to: "ansibletowerhost.example.com"

It works well, but fails whenever fields contain apostrophes (e.g. server_owner: "John O'Hara"):

fatal: [myhost42 -> ansibletowerhost.example.com]: FAILED! => {"changed": false,
"msg": "Cannot execute SQL 'REPLACE INTO mytable(hostname,fqdn,owner, ...) VALUES
\\n(\\n \\n    'myhost42',\\n    'myhost42.example.com',\\n    'John O'Hara',
\\n ... \\n);' args [None]: (1064, \\"You have an error in your SQL syntax;
check the manual that corresponds to your MariaDB server version for the right 
 syntax to use near 'Hara',\\\\n ...

How can I have Ansible correctly handle apostrophes, knowing that the original YAML files cannot be altered to fit SQL format (i.e. specifying server_owner: "John O''Hara" is not an option)?

I have tried this syntax but it fails with the same error:

'{{ hostvars[h]['myvars'][0]['server_owner'] | default('No owner') | replace("'", "''") }}',

Answer 1

You can try enclosing your values in double quotes instead of single ones. Of course, this still means they will break if the value can contain double quotes, but it should at least get you through the current problem:

REPLACE INTO mytable(hostname,fqdn,owner, ...) VALUES 
{% for h in ansible_play_hosts_all %}
(
    "{{ h }}",
    "{{ hostvars[h]['myvars'][0]['fqdn'] | default('FQDN unknown') }}",
    "{{ hostvars[h]['myvars'][0]['server_owner'] | default('No owner') }}",
(...)