CODEWITHSUNDEEP
mysqlbash
Mike
Mike

Reputation: 299

Run MySQL command with accent mark (`) using bash script

I would like to run a mysql command using a bash script but getting an syntax error because of the accent mark (`).

mysql -u root -p -e "GRANT ALL PRIVILEGES ON `testuser\_%` . * TO 'testuser'@'%';"

Let MySQL users create databases, but allow access to only their own databases

bash: testuser_%: command not found... Enter password: ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '* TO 'testuser'@'%'' at line 1

Looks like a bash confuses this part `testuser\_%` Any suggestions?

Upvotes: 0

Views: 284

Answers (3)

Bill Karwin
Bill Karwin

Reputation: 562358

As others have answered, the back-ticks are command-substitution metacharacters to the shell—but that's only true if you put them inside a double-quoted string.

So the simplest solution is to put them inside a single-quoted string instead:

mysql -u root -p -e 'GRANT ALL PRIVILEGES ON `testuser\_%` .  * TO testuser@%;'

Notice that I omitted the quotes around the user@host. You don't need them in this case. https://dev.mysql.com/doc/refman/5.7/en/account-names.html says:

The user name and host name need not be quoted if they are legal as unquoted identifiers. Quotes are necessary to specify a user_name string containing special characters (such as space or -), or a host_name string containing special characters or wildcard characters (such as . or %); for example, 'test-user'@'%.com'.

Quote user names and host names as identifiers or as strings, using either backticks (`), single quotation marks ('), or double quotation marks (").

Upvotes: 0

Barmar
Barmar

Reputation: 781059

Escape the backticks, they're used for command substitution in double quotes.

mysql -u root -p -e "GRANT ALL PRIVILEGES ON \`testuser\_%\` .  * TO 'testuser'@'%';"

You can also put the MySQL command in single quotes, then no substitution occurs. You'll need to switch the embedded quotes to double quotes:

mysql -u root -p -e 'GRANT ALL PRIVILEGES ON `testuser\_%` .  * TO "testuser"@"%";'

Upvotes: 0

tadman
tadman

Reputation: 211590

In the shell world, if you're having trouble with delimiters, add more backslashes:

"GRANT ALL PRIVILEGES ON \`testuser\_%\` .  * TO 'testuser'@'%';"

That should fix the issue.

An unescaped ` character means "execute this command and inline the result".

Upvotes: 1

Related Questions