using backslashes with bash and mysql

Question

This is boggling my mind today. I have a bash script to set a value in a MySQL table: The value I have to set is to UNC path with a trailing backslash: \\$HOSTNAME\path\

Inside mysql the query works:

update mytable SET myvalue = '\\\\MYSERVER\\path\\' WHERE ID=10;

But from bash, it fails:

mysql -e "update mytable SET myvalue = '\\\\$HOSTNAME\\path\\' WHERE ID=10;"

MySQL gives a syntax error:

ERROR 1064 (42000) at line 1: 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 ''\\MYSERVER\path\' WHERE ID=10' at line 1

Any help is greatly appreciated.

Answer 1

Because it's inside double quotes, you'll need to double all those backslashes.

If you don't need to expand any shell variables, swap the single and double quotes instead.