MySql query fails when the shell variable contains ampersand

Question

I have a table variable in shell-script which contains '&'. When I try to update the table in shell script the query fails.

I have tried several options to embrace the table name with single quotes, double quotes, back quote '`' (back quote considers the statement in it as a command to shell)

Also tried setting below options: set define on set escape on

#!/bin/bash

Table='City_&_District'
mysql --user=$DB_USER --password=$DB_PASSWD $DB_NAME << EOF
update $Table set Actual='$Actual', Normal='$Normal', Dep_Per='$Dep', Cat='$Cat' where Date='$Date';
EOF

If I Input Table name as: 'City' the query succeeds If I Input Table name as: 'City_&_District' the query Fails

ERROR 1064 (42000) at line 2: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '&_District set Actual='0.4', Normal='0.1', Dep_Per='340%', Cat='LE' where Date='' at line 1

Possible duplicate of [When to use single quotes, double quotes, and backticks in MySQL](https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql) — Paul Spiegel, May 08 '19 at 11:52
If your table name contains special characters, you should quote it with backticks. ``update `$Table` ...`` — Paul Spiegel, May 08 '19 at 11:53
As we see from the accepted answer they do work. How to pass them in the shell command - is another question: [escape-backquote-in-a-double-quoted-string-in-shell](https://stackoverflow.com/questions/1824160/escape-backquote-in-a-double-quoted-string-in-shell) — Paul Spiegel, May 08 '19 at 16:04

Alex · Accepted Answer · 2019-05-08T12:16:52.860

2

Try using backslashes with backticks as backticks are considered Command Substitution in shell and they are evaluated in double quoted strings.

You need to escape the backticks

"\`City_&_District\`"

edited May 08 '19 at 12:16

answered May 08 '19 at 11:59

Alex

878
1
10
25

Yes I'll try it – SB1990 May 08 '19 at 12:19

score 0 · Answer 2 · answered May 08 '19 at 11:55

0

Use can use ` around the table name (not ' or ")

answered May 08 '19 at 11:55

Nedret Recep

726
5
8

score 0 · Answer 3 · answered May 08 '19 at 12:26

0

Adding backslash before backtick works in table variable

answered May 08 '19 at 12:26

SB1990

43
9

MySql query fails when the shell variable contains ampersand

3 Answers3