Escape string value for mysql insert/update request in ansible

Question

I need to create ansible task which inserts a string value into database. The problem is that the string value may contain all sorts of spaces, quotes, etc.

I am going to do approximately this:

- name: Insert record
  command: mysql db_name -e "INSERT INTO table(column) VALUES ('{{ the_value }}');"

the_value variable contains the string, but as I mentioned there are all sorts of spaces and quotes in it. So, it should be escaped.

The standard ansible quote filter probably is not appropriate here, because it escapes strings to be used in shell commands, but I do not use shell.

There is somewhat similar question here: Insert data into mysql tables using ansible , but it doesn't help to solve the escaping problem.

Are you delcaring `the_value` in an `ansible-playbook` call? If so, can you post that call with an abbreviated version of the space-laden value? — WEBjuju, Nov 22 '19 at 11:17
@WEBjuju I am new to ansible, so I do not know exactly how `the_value` variable will get it's value. It's value will be read from a file - which is supposed to contain some config. Just need to save the whole contents of this file into database. — lesnik, Nov 22 '19 at 11:25

score 0 · Answer 1 · answered Aug 29 '21 at 14:39

I combined the answers from https://stackoverflow.com/a/6478616/2897386 and https://stackoverflow.com/a/60461596/2897386 , to produce this code:

- name: "MySQL escape the_value"
  set_fact:
    the_value: "{{ the_value | regex_replace(item.0, item.1) }}"
  loop:
    - ["\\\\", '\\\\']
    - ["\u0000", '\\0']
    - ["'", "\\'"]
    - ["\"", '\\"']
    - ["\b", '\\b']
    - ["\n", '\\n']
    - ["\r", '\\r']
    - ["\t", '\\t']
    - ["\u001A", '\\Z']

score -1 · Answer 2 · answered Nov 28 '19 at 08:27

I failed to find an appropriate filter, so I used slightly different approach. First I save data to a file on a host where database is running, and after that I use LOAD_FILE sql function.

Something like this:

- name: Insert record
  command: mysql db_name -e "INSERT INTO table(column) VALUES (LOAD_FILE('{{ path_to_data_file }}'));"

Some notes.

the LOAD_FILE function is probably mysql-specific so you might have to use different function/syntax for different databases
the file should be saved on the host the database is runnig (which may be different from the remote host of this task)
to save data to remote file you can use copy module with content parameter.

Escape string value for mysql insert/update request in ansible

2 Answers2