Bash script / Mysql query

Question

I want to create a bash script with query:

mysql -D $Database -u $User -p$Password \
      -e "UPDATE example SET example='["TEXT","TEXT","TEXT"]' WHERE example='example';"`

My result in table:

[TEXT,TEXT,TEXT]

I need to:

["TEXT","TEXT","TEXT"]

How to fix it?

Answer 1

When you start a quoted string with double quotes (") the quoted string ends on the first, non-escaped, " is finds. In other words after dropping the quotes the argument after -e slowly becomes this:

"UPDATE example SET example='["TEXT","TEXT","TEXT"]' WHERE example='example';"
UPDATE example SET example='[TEXT","TEXT","TEXT"]' WHERE example='example';"  # dropped first quotes
UPDATE example SET example='[TEXT,TEXT","TEXT"]' WHERE example='example';"  # drop 2nd quotes (",")
UPDATE example SET example='[TEXT,TEXT,TEXT"]' WHERE example='example';"  # drop 3rd (",")
UPDATE example SET example='[TEXT,TEXT,TEXT]' WHERE example='example';  # drop last

But since there are no spaces within the quotes, the shell buck them together into a single argument.

Therefore you need to escape " inside a string delimited by double quotes ("). i.e.

mysql -D $Database -u $User -p$Password \
      -e "UPDATE example SET example='[\"TEXT\",\"TEXT\",\"TEXT\"]' WHERE example='example';"

Extra examples:

$ echo yay
yay

$ echo "yay"
yay

$ echo "yay yay"
yay yay

$ echo "yay 'yay' yay"
yay 'yay' yay

$ echo "yay 'yay yay yay' yay"
yay 'yay yay yay' yay

$ echo "yay 'yay "yay" yay' yay"
yay 'yay yay yay' yay

$ echo "yay 'yay \"yay\" yay' yay"
yay 'yay "yay" yay' yay

Yay! It works. When you escape (add a \ before a character) the double quotes, they become part of the argument.

Now an example of how this can go wrong:

$ perl -e 'print "$ARGV[0]\n"' "yay "yay" yay"
yay yay yay

$ perl -e 'print "$ARGV[0]\n"' "yay "yay "yay"
yay yay

In the last example the arguments were split because there is a space that has not been quoted. What has been quotes are "yay " (at the beginning) and "yay" (at the end).