I execute a MySQL statement with an asterisk from within a bash shell:
query=`cat <<EndOfMySQL
INSERT tmp_table
SELECT * FROM table
;
EndOfMySQL
`
echo $query
echo $query | mysql database
The problem is that the asterisk is replaced by the list of files in the current directory and the query becomes erroneous. How to avoid this behavior? It doesn't matter whether I use backticks or $(). I'd expect some escape sequence like \*, but at least this one doesn't work.
You could prevent parameter expansion, command substitution, and arithmetic expansion in a here-document by quoting the delimiter string:
... <<\EndOfMySQL
...
EndOfMySQL
Escaping the single character will also prevent the expansions listed above.
Edit: Note that the lines of the here-doc are no subject to filename generation (globbing)!
I suppose that the problem in this case is that you didn't quote the variable passed to mysql:
echo $query | mysql database
should be:
echo "$query" | mysql database
or better yet:
printf '%s\n' "$query" | mysql database
Why don't you use:
query='INSERT into tmp_table
SELECT * FROM table;'
printf '%s\n' "$query" | mysql
or (if your shell supports here-strings, recent versions of bash support them):
mysql <<< "$query"
To prevent * and other glob charcters from expanding. disable globbing –use set -f before your Here Document
Escaping any of the characters of the delimiter of Here Document does mean that No parameter expansion, command substitution, arithmetic expansion, or pathname expansion is performed – However, pathname expansion has a caveat !
From man bash
Pathname Expansion —
After word splitting, *unless the -f option has been set, bash scans each word for the characters *, ?, and [. If one of these characters appears, then the word is regarded as a pattern, and replaced with an alphabetically sorted list of file names matching the pattern.
Shell Builtin Commnds —
set -f – Disable pathname expansion.
From help set
-f – Disable file name generation (globbing).
How to pass an asterisk into bash heredoc?
There is no trick to doing this -- the here document works fine with an asterisk.
This question is based on a misinterpretation of the symptoms (which is an easy mistake to make here).
The issue with the asterisk has nothing to do with the here-doc or the execution of cat. The * is expanded during echo $query.
You could prove this to yourself by replacing cat with tee temp.txt, and reviewing the contents of the generated file.
However, other types of expansion are processed (by default), so if you did want to expand the asterisk, you could do something like this:
SELECT $(echo *) FROM table
You can put quotes around the name of the heredoc to disable expansion altogether:
cat <<'EndOfMySQL'
See also: How can I write a heredoc to a file in Bash script?
