Ansible: Single Quote Escape Issues within a task utilizing the shell module-CodePudding

I have a task within a playbook which utilizes the shell module. This task also happens to include an sql command which requires single quotes within it's syntax, which also happen to be within a variable with string appending(s). I have tried many iterations of ways to escape these single quotes to no avail, including backtics, double single quoting, the escape/quote filter, and even scalar folding, none seem to be working.

How it would look if you were typing the command in the shell. (This does work)

sudo mysql databasename -e "insert into Scanner values (63,63,'M63')"

What the code in the task looks like which is causing the issue, getting proper interpolation of the ct_machine_serial variable is not happening due to the single quote issue: (This is where the problem is.)

     - name: Insert scanner ct/ecc serial values into the sql database on both dispatchers.
       shell: >-
         mysql databasename -e "insert into Scanner values ({{ ctfile1_vars.ct_machine_serial_number | string  ','  ctfile1_vars.ct_machine_serial_number | string  ',' '''   ctfile1_vars.ct_machine_serial | upper  ''' }})"
       become: yes
       become_method: sudo
       register: result

Further, ctfile1_vars derived from an include_vars import of data from a yaml file. The file contents are simple and as follows:

ct_machine_serial: m63
ct_machine_serial_number: 63

So basically the shell task is supposed to be formulated such that it outputs exactly like the shell command I have in the first example. However, what I expect isn't happening.

Now that I have defined the basics, here below is the actual playbook code fully included.

   tasks:

     - name: Full import of the active scanner variables storage file for next sql modificaton.
       include_vars:
         file: ~/active_ct-scanner-vars.yml
         name: ctfile1_vars

     - name: Insert scanner ct/ecc serial values into the sql database on both dispatchers.
       shell: >-
         mysql databasename -e "insert into Scanner values ({{ ctfile1_vars.ct_machine_serial_number | string  ','  ctfile1_vars.ct_machine_serial_number | string  ',' '''   ctfile1_vars.ct_machine_serial | upper  ''' }})"
       become: yes
       become_method: sudo
       register: result

Lastly, here is the results of the run with super verbose -vvv being utilized which does show the error well:

fatal: [server-1]: FAILED! => {
    "changed": true,
    "cmd": "mysql databasename -e \"insert into Scanner values (63,63,   ctfile1_vars.ct_machine_serial | upper  )\"",
    "delta": "0:00:00.011330",
    "end": "2022-10-17 17:13:46.813905",
    "invocation": {
        "module_args": {
            "_raw_params": "mysql databasename -e \"insert into Scanner values (63,63,   ctfile1_vars.ct_machine_serial | upper  )\"",
            "_uses_shell": true,
            "argv": null,
            "chdir": null,
            "creates": null,
            "executable": null,
            "removes": null,
            "stdin": null,
            "stdin_add_newline": true,
            "strip_empty_ends": true,
            "warn": true
        }
    },
    "msg": "non-zero return code",
    "rc": 1,
    "start": "2022-10-17 17:13:46.864367",
    "stderr": "ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1",
    "stderr_lines": [
        "ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1"
    ],
    "stdout": "",
    "stdout_lines": []
}

As the output shows, the single quotes (I assume) are screwing up the proper interpolation/processing of the ctfile1_vars.ct_machine_serial | upper variable and filter. This obviously prevents the proper value to be inserted as well as screws up the mysql syntax because the single quotes are missing. It should obviously instead simply be: 'M63' like in the working command shell example.

I've tried hundreds of iterations of fixes, so I'm hoping someone here can tell me what I am doing incorrectly. Thanks in advance!

CodePudding user response：

The following should fix your issue:

    - name: Insert scanner ct/ecc serial values into the sql database on both dispatchers.
      vars:
        sernum: "{{ ctfile1_vars.ct_machine_serial_number }}"
        ser: "{{ ctfile1_vars.ct_machine_serial }}"
      shell: >-
        mysql databasename -e "insert into Scanner values
        ({{ sernum | int }}, {{ sernum | int }}, '{{ ser | upper }}')"

Meanwhile, as proposed in comments, you could follow ansible good practice (i.e. don't use shell when a module exists) and at the same time drop one quoting level:

    - name: Insert scanner ct/ecc serial values into the sql database on both dispatchers.
      vars:
        sernum: "{{ ctfile1_vars.ct_machine_serial_number }}"
        ser: "{{ ctfile1_vars.ct_machine_serial }}"
      mysql_query:
        login_user: "{{ vault_encrypted_login }}"
        login_password: "{{ vault_encrypted_password }}"
        login_db: databasename
        query: >-
          insert into Scanner values 
          ({{ sernum | int }}, {{ sernum | int }}, '{{ ser | upper }}')