Search code examples
mysqlbashglobheredoc

How to pass an asterisk into bash heredoc?

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.

Solution

  • 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"