CODEWITHSUNDEEP
mysqljsonansibledouble-quotes
botboy
botboy

Reputation: 1

Executing SQL that inserts JSON containing double quoted strings in Ansible

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' 
}

Upvotes: 0

Views: 44

Answers (1)

U880D
U880D

Reputation: 12124

Quick and lazy you could try to change from > to | (Literal style)

A minimal example playbook

---
- hosts: localhost
  become: false
  gather_facts: false

  vars:

    id_dns: DNS
    mysql_credentials_file_cmdline_parameter: TEST

  tasks:

    - debug:
        msg: |
          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';"

will result into an output of

TASK [debug] *************************************
ok: [localhost] =>
  msg: |-
    mysql TEST -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';"

Since there is many room for improvement on that single task, you could then start with change the formatting to get rid of the inline JSON string.

---
- hosts: localhost
  become: false
  gather_facts: false

  vars:

    DNS: DNS
    CREDENTIALS: TEST

    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"
    }

  tasks:

    - debug:
        msg: |
          mysql {{ CREDENTIALS }} -s -N -e
          "UPDATE test_db.test SET app_home_url = '{{ DNS }}',
           app_callback_urls = '{{ URLs | to_json }}',
           app_dns = '{{ DNS }}
           WHERE client_id = 'test';"

resulting into an output of

TASK [debug] *************************************
ok: [localhost] =>
  msg: |-
    mysql TEST -s -N -e
    "UPDATE test_db.test SET app_home_url = 'DNS',
     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"}',
     app_dns = 'DNS
     WHERE client_id = 'test';"

Proceed further with simplification, maybe with templating and up to the point where the task becomes easy to maintain and operate even after several year not looking on it.

Upvotes: 0

Related Questions