Bash script variable with comma seperated values is not comma seperated when appended to a different file

I have a bash script that validates table entries. The final output value is $out which is inserted into an SQL INSERT statement which is then appended to /tmp/crew.txt. While $out is clearly comma separated there are no commas between the values in the resulting INSERT statement in /tmp/crew.txt. Why is this the case and how can it be fixed so I do have comma separated values in /tmp/crew.txt?

#!/bin/bash

out=290,'02:20:00','02:40:00',20.5,NULL

echo "${out:1}"

290,'02:20:00','02:40:00',20.5,NULL


echo "INSERT INTO tip_run (date_linkid, start_time, finish_time, weight, note) VALUES ("${out:1}");" >> /tmp/crew.txt

vi /tmp/crew.txt

INSERT INTO tip_run (date_linkid, start_time, finish_time, weight, note) VALUES ( 290 '02:20:00' '02:40:00' 20 NULL);

Thus the result in /tmp/crew.txt should be:

INSERT INTO tip_run (date_linkid, start_time, finish_time, weight, note) VALUES ( 290,'02:20:00','02:40:00',20,NULL);

Solution 1:

Hypothesis: your IFS variable contains ,. The script does not change the IFS, so the non-default value must have been in the environment.

This other answer noticed you have unquoted ${out:1} in a line that looks like this:

echo "foo"${out:1}"bar"

Unquoted variable undergoes word splitting and filename generation. Word splitting is by IFS. The unquoted ${out:1} is split to multiple words, echo gets multiple arguments and prints them separated by single spaces (because this is what echo does, no matter the IFS).

If you used printf as advised, it would be easier to tell if the variable is quoted:

printf 'INSERT INTO tip_run (date_linkid, start_time, finish_time, weight, note) VALUES (%s);\n' "${out:1}"

And if you didn't double-quote the variable, printf would get multiple arguments from ${out:1} and it would generate multiple lines. Then it should be quite obvious some splitting occurs. Using echo that concatenates its arguments obfuscated this fact to some degree.

Separate issues:

• The desired output suggests you don't want "${out:1}" but " $out" with a leading space.

• To include single-quotes in the variable you should make sure the shell doesn't remove them. Escape them (credits to the already mentioned answer) or double-quote them:

  out="290,'02:20:00','02:40:00',20.5,NULL"
  

Solution 2:

Why is this the case and how can it be fixed

Removing the echoed output and blank lines from your script gives the following cleaned up script:

#!/bin/bash
out=290,'02:20:00','02:40:00',20.5,NULL
echo "${out:1}"
echo "INSERT INTO tip_run (date_linkid, start_time, finish_time, weight, note) VALUES ("${out:1}");" >> /tmp/crew.txt

Running that script though ShellCheck – shell script analysis tool produces the following error:

$ shellcheck myscript
 
Line 4:
echo "INSERT INTO tip_run (date_linkid, start_time, finish_time, weight, note) VALUES ("${out:1}");" >> /tmp/crew.txt
>>                                                                                      ^-- SC2027: The surrounding quotes actually unquote this. Remove or escape them.

$ 

Removing the quotes as suggested fixes that error, so now we have:

#!/bin/bash
out=290,'02:20:00','02:40:00',20.5,NULL
echo "${out:1}"
echo "INSERT INTO tip_run (date_linkid, start_time, finish_time, weight, note) VALUES (${out:1});" >> /tmp/crew.txt

However running this still doesn't give the right answer as echo "${out:1}" shows that the single quotes ' are not stored in out, and they will need to be escaped:

$ test.sh
90,02:20:00,02:40:00,20.5,NULL
$

Fixing this gives:

#!/bin/bash
out=290,\'02:20:00\',\'02:40:00\',20.5,NULL
echo "${out:1}"
echo "INSERT INTO tip_run (date_linkid, start_time, finish_time, weight, note) VALUES (${out:1});" >> test.txt

Note I've changed the output filename to test.txt

Testing:

$ test.sh
90,'02:20:00','02:40:00',20.5,NULL
$ cat test.txt
INSERT INTO tip_run (date_linkid, start_time, finish_time, weight, note) VALUES (90,'02:20:00','02:40:00',20.5,NULL);
$

So the final fixed version of your script is:

#!/bin/bash
out=290,\'02:20:00\',\'02:40:00\',20.5,NULL
echo "${out:1}"
echo "INSERT INTO tip_run (date_linkid, start_time, finish_time, weight, note) VALUES (${out:1});" >> >> /tmp/crew.txt

Here endeth the lesson on debugging your broken script.