How to Properly Escape Asterisk (*) Characters in Bash Scripts for MySQL Commands

When working with Bash scripts that need to pass MySQL commands containing asterisks (*), you'll encounter unexpected filename expansion. This occurs because Bash treats the asterisk as a wildcard character when it's in an unquoted variable.

#!/bin/bash
# This will fail due to asterisk expansion
command="GRANT ALL ON *.* TO 'user'@'localhost';"
mysql <<< "$command"

Bash has several quoting mechanisms that affect how special characters like asterisks are interpreted:

Single quotes ('') preserve all literal values
Double quotes ("") allow for variable expansion but protect most special characters
Backslash (\) escapes individual characters

Solution 1: Using Single Quotes for the Entire String

#!/bin/bash
# Single quotes prevent all expansions
command='GRANT ALL ON *.* TO '\''user'\''@'\''localhost'\'';'
mysql -e "$command"

Solution 2: Escaping Individual Asterisks

#!/bin/bash
# Backslash escapes each asterisk
command="GRANT ALL ON \\*.* TO 'user'@'localhost';"
mysql <<< "$command"

Solution 3: Using Heredoc for Complex Commands

#!/bin/bash
# Heredoc preserves all special characters
mysql <<EOF
GRANT ALL ON *.* TO 'user'@'localhost';
EOF

When you need to build commands dynamically while protecting special characters:

#!/bin/bash
username="$1"
# Using printf with %q for proper escaping
printf -v safe_user "%q" "$username"
command="GRANT ALL ON \\*.* TO ${safe_user}@'localhost';"
mysql -e "$command"

Forgetting that variable assignment doesn't perform word splitting
Assuming double quotes alone will protect asterisks
Neglecting to escape other special characters like dollar signs ($)

Always validate and sanitize user input
Prefer heredoc syntax for complex SQL commands
Consider using prepared statements via command-line tools
Test commands with echo before execution

# Safe execution pattern example
debug_mode=true
sql_command='SELECT * FROM db.table WHERE id = 123;'

if [ "$debug_mode" = true ]; then
    echo "Would execute: $sql_command"
else
    mysql -e "$sql_command"
fi

When working with bash scripts that need to pass SQL commands containing asterisks (*) to MySQL, you might encounter unexpected file expansion. This happens because bash treats the asterisk as a wildcard character that expands to match filenames in the current directory.

The simplest solution is to properly quote your SQL command string:

#!/bin/bash
c="GRANT ALL ON \*.* TO '$1'@'localhost';"
mysql -e "$c"
exit 0

Key points about this solution:

Backslash escapes the first asterisk
Double quotes around the variable prevent word splitting
The -e option tells mysql to execute the command directly

Here are some other ways to handle this situation:

1. Using Single Quotes

#!/bin/bash
c='GRANT ALL ON *.* TO '\''$1'\''@'\''localhost'\'';'
mysql -e "$c"

2. Using printf

#!/bin/bash
printf -v c "GRANT ALL ON %s TO '%s'@'localhost';" "*.*" "$1"
mysql -e "$c"

3. Here Document

#!/bin/bash
mysql <


The original approach using mysql < $c has multiple issues:

The asterisk expands to filenames before mysql sees the command
Using input redirection (<) expects a filename, not a command string
No proper quoting of the SQL statement

When incorporating SQL in bash scripts:

Always validate and sanitize user input ($1 in our example)
Consider using prepared statements or parameterized queries
For complex SQL, use heredocs or external SQL files
Test with echo first to verify the actual command being generated

Here's a more robust version of our script:
#!/bin/bash
if [ -z "$1" ]; then
    echo "Error: No username specified" >&2
    exit 1
fi

printf -v sql "GRANT ALL ON %s TO '%s'@'localhost';" "*.*" "$1"

if ! mysql -e "$sql"; then
    echo "MySQL command failed" >&2
    exit 1
fi

echo "Privileges granted successfully"
exit 0