Regex – Ansible Lineinfile – escaping single quote when using a back reference

ansiblebackreferenceregex

I am trying using the following Ansible play to change the default weak password for a number of SQL scripts:

- name: amend SQL User Passwords
  sudo: yes
  lineinfile: dest=/path/to/script/{{ item.file }} state=present backup=yes
              regexp="CREATE USER (.*) PASSWORD 'password';$" line='CREATE USER \1 PASSWORD ''strongpassword'';' backrefs=yes
  with_items:
    - { file: create_db1_users_tables.sql }
    - { file: create_db2_users_tables.sql }
    - ...
    - { file: create_dbNN_users_tables.sql }

Though the YAML specification suggests that it should,

Inside single quotes, you can represent a single quote in your string by using two single quotes next to each other.

the double single-quote are not acting as an escape character; instead they are being completely removed from the output line:

CREATE USER user1 PASSWORD stR0ngP@55w0rD

instead of

CREATE USER user1 PASSWORD 'stR0ngP@55w0rD'

So far I have tried:

  1. Using double quotes – this allows the single quote around the password, however the backref is no-longer parsed, and just prints out as \1. Attempting to escape this as \\1 appears to have no affect.
  2. Various different ways of escaping the single quote – backslashes, ticks. Most produce syntax errors or just output nothing.
  3. Surrounding all the lineinfile arguments in double quotes (and suitably escaping everything within)
  4. I can't use the Ansible database modules
  5. I can't use templates, the scripts create and populate a number of tables, and can change between versions of the product – keeping templates up to date would be too much overhead.

I've found a similar Ansible question, Quotes in ansible lineinfile, but the solutions did not help.

Does anyone have any further suggestions?

Best Answer

Not that I enjoy answering my own posts but having trawlled more of the internet I came across this post, where the suggestion was made to use the replace module instead of the lineinfile**. The play:

- name: amend SQL User Passwords
  sudo: yes
  replace: dest=/path/to/script/{{ item.file }}
           backup=yes
           regexp="^CREATE USER (.*) PASSWORD 'password';$"
           replace="CREATE USER \\1 PASSWORD 'strongpassword';"
  with_items:
    - { file: create_db1_users_tables.sql }
    - { file: create_db2_users_tables.sql }
    - ...
    - { file: create_dbNN_users_tables.sql }

Works as expected, generating the desired SQL.

** This also re-enforces the fact that I should spend more time reading the Ansible manuals.

Related Topic