Insert a JSON containing double quoted Strings using SQL statment

Get Help
,
1

I am trying to execute an Ansible play to execute a MySQL statement that inserts a JSON object where all the keys and values are Strings. However, with my current code, the resulting JSON does not have double quotes around the keys and values.

Current code:

- name: Execute SQL to update entries for client_id 'test')
    shell: >
      mysql {{ mysql_credentials_file_cmdline_parameter }} -s -N -e 
      "UPDATE test_db.test SET app_home_url = '{{id_dns}}',
     
    app_callback_urls = '{"notification_callback_urls":{"app_novuid_registration":"{{id_dns}}/user_registration","forgot_password":"{{id_dns}}/password_reset"},"user_attribute_callback_url":"{{id_dns}}/user_updated","check_switch_callback_url":"{{id_dns}}/email_switched"}', 
    
    app_dns = '{{id_dns}}' 
    WHERE client_id = 'test';"

However, the result I am getting when I examine the results in MySQL does not contain double quotes around the Strings.

{ notification_callback_urls:  
     { app_novuid_registration : https://dns/user_registration, 
       forgot_password : https://dns/password_reset
     }, 
       user_attribute_callback_url : https://dns/user_updated, 
       check_switch_callback_url : https://dns/email_switched
}

Instead, I want this:

{"notification_callback_urls":  
     { "app_novuid_registration" : "https://dns/user_registration", 
       "forgot_password" : "https://dns/password_reset"
     }, 
       "user_attribute_callback_url" : "https://dns/user_updated", 
       "check_switch_callback_url" : "https://dns/email_switched"
}

I tried escaping the double quotes as so:

- name: Execute SQL to update entries for client_id 'test')
    shell: >
      mysql {{ mysql_credentials_file_cmdline_parameter }} -s -N -e 
      "UPDATE test_db.test SET app_home_url = '{{id_dns}}',
     
    app_callback_urls = '{\"notification_callback_urls\":{\"app_novuid_registration\":\"{{id_dns}}/user_registration\",\"forgot_password\":\"{{id_dns}}/password_reset\"},\"user_attribute_callback_url\":\"{{id_dns}}/user_updated\",\check_switch_callback_url\":\"{{id_dns}}/email_switched\"}', 
    
    app_dns = '{{id_dns}}' 
    WHERE client_id = 'test';"

However, upon running I get an generic error, and it looks like trying to escape the double quotes with \" results in 3 slashes in the command that gets run.

TASK [Execute SQL to update entry] *********************************************
fatal: [localhost]: FAILED! => 
{
    "changed": true,
    "cmd": 
"mysql {{ mysql_credentials_file_cmdline_parameter }} -s -N -e 
      "UPDATE test_db.test SET app_home_url = 'dns',
     
    app_callback_urls = '{\\\"notification_callback_urls\\\":{\\\"app_novuid_registration\\\":\\\"dns/user_registration\\\",\\\"forgot_password\\\":\\\"dns/password_reset\\\"},\\\"user_attribute_callback_url\\\":\\\"dns/user_updated\\\",\\\"check_switch_callback_url\\\":\\\"dns/email_switched\\\"}', 
    
    app_dns = 'dns' 
    WHERE client_id = 'test';"

Interestingly, just to try things out, I tried putting single quotes around the escaped \", Example: '\"'notification_callback_urls'\"'

I get a JSON with values surrounded by single quotes, which still isn’t quite what I want, but it’s closer.

{'notification_callback_urls':        
     { 'app_novuid_registration' : 'https://dns/user_registration',         
      'forgot_password' : 'https://dns/password_reset'
      },
 'user_attribute_callback_url' : 'https://dns/user_updated',         
 'check_switch_callback_url' : 'https://dns/email_switched' 
}

I also tried setting app_callback_urls as a variable using set_fact and then passing it into the string: {{ app_callback_urls | to_json}}. I get the same result as the original code (no quotes surrounding values in the JSON).

1 Like
2

As you have suggested I’d use the to_json filter to manage the formatting and then I’d probably use the community.mysql.mysql_query module, rather than shell, would something along these lines work?

app_callback_urls:
  notification_callback_urls:
    app_novuid_registration: https://dns/user_registration
    forgot_password: https://dns/password_reset
  user_attribute_callback_url: https://dns/user_updated
  check_switch_callback_url: https://dns/email_switched

- name: Execute SQL to update entries for client_id 'test'
  community.mysql.mysql_query: 
    query: "{{ mysql_query }}"
  vars:
    mysql_query: >-
      UPDATE test_db.test SET app_home_url =
      "{{ app_callback_urls | ansible.builtin.to_json }}"
1 Like
3

Both of your examples are broken by indentation. “app_callback_urls” starts in the same column as “shell:”.

Fix the indentation, remove all the escapes, and you should be much better off.

What I don’t understand is why you got Ansible to run at all with that indentation.

2 Likes
4

Updating with the solution. I had to surround the variable declarations with escaped backslashes with single quotes and then use to_json function.


    - name: Set app_callback_urls 
      set_fact:
        app_callback_urls:
          '\"notification_callback_urls\"':
            '"\app_novuid_registration\"': '\"{{ dns }}/user_registration\"'
            '"\forgot_password\"': '\"{{ dns }}/password_reset\"'
          '"\user_attribute_callback_url\"': '\"{{ dns }}/user_updated\"'
          '"\check_switch_callback_url\"': '\"{{ dns }}/email_switched\"'

    - name: Execute SQL to update entries
      shell: >-
        mysql {{ mysql_credentials_file_cmdline_parameter }} -s -N -e 
        "UPDATE veevaid.veevaid_app SET app_home_url = '{{ dns }}', app_callback_urls = '{{ app_callback_urls | to_json }}', app_dns = '{{ dns }}''"
1 Like
5

I’ve been playing around with this off and on all day, trying to come up with a more readable and maintainable way to do this. (While I may claim that multiple instances of '"\ instead of '\" in the prior post are evidence justifying the effort, mostly I enjoy quoting problems.)

One thing to note: If you don’t use your app_callback_urls fact anywhere else, you don’t need the set_fact task. Instead, you can create it as a task variable by adding a vars: section to your [ansible.builtin.]shell: task.

Also, as inadvertently demonstrated above, manually \-escaping a bunch of otherwise unnecessary double-quotes is error prone. This is much more easily and consistently accomplished with a |regex_replace('"','\"') filter after to_json.

For the example below, I don’t have a particular desire to execute the mysql command with these parameters, so I’ve inserted printf "%q\n" in front of it. The result is a shell script one-liner that prints what it sees as each parameter on separate lines. That in combination with -vv on ansible-playbook’s command line shows enough detail to feel pretty confident that things are being quoted as intended. (Whether it works with mysql is a separate question which, for me at least, is out of scope.)

One last thing to point out: Quoting is hard; nesting quotes multiplies that difficulty. In the example below, I broke out the “escaped” variable initialization with its regex_replace of double-quotes with escaped double-quotes to separate it from where the resulting escaped JSON gets used. There are two reasons I did this. The second reason is that it’s a whole lot easier to read and understand what it’s doing. But the primary reason is that, despite way more time spent on it than I care to admit, I could not incorporate that expression directly into the shell script body without ansible-playbook throwing errors about either unbalanced quotes or mismatched Jinja operators. Fortunately, I like it better this way.

    - name: Execute SQL to update entries
      ansible.builtin.shell: >-
        printf "%q\n"
        mysql {{ mysql_credentials_file_cmdline_parameter }} -s -N -e
        "UPDATE veevaid.veevaid_app SET app_home_url = '{{ dns }}',
        app_callback_urls = '{{ escaped }}',
        app_dns = '{{ dns }}'"
      vars:
        mysql_credentials_file_cmdline_parameter: mysql_credentials_file_cmdline_parameter
        dns: DnS
        app_callback_urls:
          notification_callback_urls:
            app_novuid_registration:   '{{ dns }}/user_registration'
            forgot_password:           '{{ dns }}/password_reset'
          user_attribute_callback_url: '{{ dns }}/user_updated'
          check_switch_callback_url:   '{{ dns }}/email_switched'
        escaped: >-
          {{ app_callback_urls | to_json | regex_replace('"','\"') }}

And here’s the output from the job log:

TASK [Execute SQL to update entries] ***************************************************************************
task path: /home/utoddl/ansible/botboy_01.yml:7
changed: [localhost] => changed=true 
  cmd: 'printf "%q\n" mysql mysql_credentials_file_cmdline_parameter -s -N -e "UPDATE veevaid.veevaid_app SET app_home_url = ''DnS'', app_callback_urls = ''{\"notification_callback_urls\": {\"app_novuid_registration\": \"DnS/user_registration\", \"forgot_password\": \"DnS/password_reset\"}, \"user_attribute_callback_url\": \"DnS/user_updated\", \"check_switch_callback_url\": \"DnS/email_switched\"}'', app_dns = ''DnS''"'
  delta: '0:00:00.002708'
  end: '2024-10-29 17:40:42.634608'
  msg: ''
  rc: 0
  start: '2024-10-29 17:40:42.631900'
  stderr: ''
  stderr_lines: <omitted>
  stdout: |-
    mysql
    mysql_credentials_file_cmdline_parameter
    -s
    -N
    -e
    UPDATE\ veevaid.veevaid_app\ SET\ app_home_url\ =\ \'DnS\'\,\ app_callback_urls\ =\ \'\{\"notification_callback_urls\":\ \{\"app_novuid_registration\":\ \"DnS/user_registration\"\,\ \"forgot_password\":\ \"DnS/password_reset\"\}\,\ \"user_attribute_callback_url\":\ \"DnS/user_updated\"\,\ \"check_switch_callback_url\":\ \"DnS/email_switched\"\}\'\,\ app_dns\ =\ \'DnS\'
  stdout_lines: <omitted>
2 Likes