CODEWITHSUNDEEP
templatesansiblejinja2ansible-template
oucil
oucil

Reputation: 4554

Ansible breaking templates with quotes around variables when more than one on same line

I'm using an Ansible template to include variables in a SQL import file, and everything was working fine until lately where ansible is now parsing the quotes around variables in the template and eliminating some of them, where it never used to.

For example, the following...

INSERT INTO `my_table` VALUES (1, '{{ var.one }}', NULL, '{{ var.two }}');

...is now resulting in...

INSERT INTO `my_table` VALUES (1, 'value-one, NULL, value-two');

Notice that the two single quotes in the middle have been removed. This breaks the SQL. As per the MySQL/ANSI standards, I'm supposed to be using single quotes for string literals, and backticks only for identifiers. So what's the solution here? And does anyone know when this behaviour changed?

I'm using Ansible 2.9.27

As requested, here is the simple template task call...

- name: Copy SQL Dynamic Data Dump File for import
  template: src=data.sql.j2 dest=/tmp/data.sql

Upvotes: 0

Views: 1924

Answers (2)

Vladimir Botka
Vladimir Botka

Reputation: 67984

This issue has already been solved. The example below works for me both in 2.10 and 2.12

  • ansible [core 2.12.1], python 3.8.5, jinja 3.0.1
  • ansible 2.10.11, python 3.6.9, python3-jinja2 2.10-1ubuntu0.18.04.1
shell> cat import.sql.j2
INSERT INTO my_table VALUES (1, '{{ var.one }}', NULL, '{{ var.two }}');

and the task

     - template:
         src: import.sql.j2
         dest: import.sql
       vars:
         var:
           one: value-one
           two: value-two

give valid SQL

shell> cat import.sql
INSERT INTO my_table VALUES (1, 'value-one', NULL, 'value-two');

As a workaround put the single quote into the expression

shell> cat import.sql.j2
INSERT INTO my_table VALUES (1, {{ "'" }}{{ var.one }}{{ "'" }}, NULL, {{ "'" }}{{ var.two }}{{ "'" }});

Use sed to make these changes globally, e.g.

shell> cat templates/import.sql.j2
INSERT INTO my_table VALUES (1, '{{ var.one }}', NULL, '{{ var.two }}');

shell> for i in templates/*; do sed -i "s/'/{{ \"'\" }}/g" $i; done

shell> cat templates/import.sql.j2
INSERT INTO my_table VALUES (1, {{ "'" }}{{ var.one }}{{ "'" }}, NULL, {{ "'" }}{{ var.two }}{{ "'" }});

Optionally choose a linter and validate SQL. The validate option in the template module doesn't work for sqlfluff hence the next command task is used below. The testing of the line length is excluded (--exclude-rules L016).

    - template:
        src: import.sql.j2
        dest: import.sql
      register: result
      vars:
        var:
          one: value-one
          two: value-two
    - command: "sqlfluff lint {{ result.dest }}  --exclude-rules L016"
      changed_when: false

Upvotes: 1

oucil
oucil

Reputation: 4554

So apparently this is related to a known bug in Jinja2 that was fixed in 2.11. I'm using 3.0.3 though so it's odd that its still a problem, so I launched a bug report with Ansible and they've confirmed the behaviour. The way Jinja2 Native is used in templating has changed in Ansible 2.11 which should alieviate the problem, however until it's available in the release channel for our distro, we've been able to fix the problem by disabling "native" support in Jinja2 which we were using. This may cause other problems, but we're stuck since Ansible 2.9 is only receiving security patches now.

My Bug report is here: https://github.com/ansible/ansible/issues/76761

The Jinja2 Bug report is here: https://github.com/pallets/jinja/issues/1020

Upvotes: 0

Related Questions