Search code examples
jinja2salt-project

Literal simple quote inside jinja2 "set" statement

I am trying to get a mysql query to work inside a jinja2 "set" statement (salt context), as :

{% set KEY = salt.cmd.shell("mysql -u user -p'password' -D ann -h ann -B -N -e 'select T from ann.USER where NAME='key''") %}

I've been banging my head for some hours now, so if anyone has a way to achieve this, please go on!

I tried many (I mean MANY) different combination to make this work but I could not.

The best I came up with was this mysql error :

ERROR 1054 (42S22) at line 1: Unknown column 'key' in 'where clause'

Which I can reproduce on the mysql instance by not using simple quotes around "key", so for me this is indeed a quoting problem.

The Jinja2 renderer just remove the simple quote before passing the command to the shell, am I right ?

EDIT :

The sel-fish answer did the trick (thanks a lot for this) but now I realize I need to add some jinja inside the "where clause", and (I guess) because of the quoting (hell ?) the jinja variable cannot get interpreted.

Is there any way to have a correct jinja interpretation for the column name (key) inside the where clause ?

as in :

salt.cmd.shell("mysql -uroot -ppassword -h ann -D ann -B -N -e \'select T from ann.USER where NAME=\"{{ key }}\"\' 2>/dev/null; exit 0")

EDIT 2: The second proposal to fix the jinja {{ key }} is failing with :

[salt.state       :275 ][ERROR   ][5947] Unable to manage file: Jinja syntax error: expected token ':', got '}'; line 53

Where line 53 is the line where I put your second proposal.

Here is the actual (sanitized) line I am using :

{% set WEBCRMKEY = salt.cmd.shell("mysql -u{{ mysql_user }} -p{{ mysql_password }} -h {{ ann_NAME|join }}.{{ full_domain }} -D ann -B -N -e \'select P from ann.USER where NAME=\"" + {{ CLIENT }} + "\"\'") %}

I guess it is now time to say welcome to quoting hell.

@sel-fish : Any other hint ? (that would save my week!)

Last word

The latest sel-fish proposition was the good one.

Thanks

Solution

  • Use backslash to escape single quotation.

    The shell cmd like below: 

    salt.cmd.shell("mysql -uroot -ppassword -h ann -D ann -B -N -e \'select T from ann.USER where NAME=\"key\"\' 2>/dev/null; exit 0")

    Added 2>/dev/null; exit 0 at the end of cmd, the former is to filter mysql command line tool warning message, the latter is to avoid saltstack warning.

    If key is a jinja variable, then:

    salt.cmd.shell("mysql -uroot -ppassword -h ann -D ann -B -N -e \'select T from ann.USER where NAME=\"" + {{ key }} + "\"\' 2>/dev/null; exit 0")

    If you want to use more variables:

    salt.cmd.shell("mysql -u" + mysql_user + " -p" + mysql_password + " -h" + ann_NAME + "." + full_domain + " -D ann -B -N -e \'select T from ann.USER where NAME=\"" + key + "\"\' 2>/dev/null; exit 0")