Bash and MySQL variables error

Question

I have the following script:

#!/bin/sh    
Q=`</dev/urandom tr -dc A-Za-z0-9 | head -c30`
mysql -uusername -ppasword accounts -e "update forum set key='$Q' where id='1';"

I have to add back-ticks (``) to "forum", "key" and "id", otherwise it returns me an 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 'key='xdindSG7hK9KaYgs9RISJNqrzmn4LJ' where id='1'' at line 1

But if I add the back-ticks, bash interprets them as variables.

What should I do?

Answer 1

Try a HERE document:

#!/bin/sh    
Q=`</dev/urandom tr -dc A-Za-z0-9 | head -c30`
mysql -uusername -ppasword accounts <<HERE
    update forum set key='$Q' where id='1';
HERE

Try with

cat <<HERE
    update forum set key='$Q' where id='1';
HERE

Output:

update forum set key='fnPIOid15anEJ2a3zVL6I1wbRjAKk0' where id='1';

Answer 2

Switch the single and double quotes. Single quotes instructs bash to ignore the contents and you will be able to add your back-ticks.