double quote inside mysql script not working

I want to create a cron script for my FreePBX. If I connect from terminal to mysql it works this way:

mysql -u user -ppasswd table
UPDATE kvstore SET val='$HOST' where `key`="externip";

but inside a script like this:

mysql -u user -ppasswd table  -e "UPDATE kvstore SET val='$HOST' where `key`="externip";";

it returns me:

 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '=externip' at line 1

For instance I have this script with no problem because have no double quotes inside the script:

mysql -u user -ppasswd table -e "UPDATE sipsettings SET data = '$HOST' WHERE sipsettings.keyword = 'externip_val' AND sipsettings.seq = 40 AND sipsettings.type = 0;";

I tried all the ways I can putting \" for the quotes but no way to find the solution.

Thanks for your answer if anyone can help me!

2 answers

  • answered 2019-10-09 09:51 GMB

    The embedded double quotes do clash with the surrounding double quotes. In SQL, it is generally a good practice to use single quotes to define strings anyway, so I would recommend:

    mysql -u user -ppasswd table  -e "UPDATE kvstore SET val='$HOST' where `key`='externip';";
    

  • answered 2019-10-09 12:37 Antonio Perez

    I tried that and not working and I tried too with \"

    You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '='externip'' at line 1
    

    Thanks