I'm trying to run a query on a remote server from by bash script. Problem is, I have to use single quotes inside of the query which already has surrounding single quotes.
My code:
# note: $line has ` in it, as this is a SQL query
ssh server "mysql --defaults-extra-file=$SQL_CREDS_FILE $R_DB $t -e '$line INTO OUTFILE \"\'\"$DIR/$tbl_count.csv\"\'\" FIELDS TERMINATED BY \"\'\",\"\'\" ENCLOSED BY \"\'\"\"\'\" LINES TERMINATED BY \"\'\"\n\"\'\"'"
The error I'm seeing upon running this code:
bash: -c: line 0: unexpected EOF while looking for matching `"'
bash: -c: line 1: syntax error: unexpected end of file
Example of working MYSQL query:
mysql -u user -pPassword database -e 'select `id`,`title`,`name`,`description`,`icon`,`creation_date` as `created_at`,`mutation_date` as `created_at` from achievement;'
The simplest workaround is to use a context where you don't quote the expression at all.
Using a here document will tie up the standard input of ssh, but in this case it seems acceptable (or perhaps even desirable).
ssh server <<____HERE
# Guessing $R_DB contains "database"
# Guessing $t contains your credentials ...?
# Guessing $DIR contains a directory name
# Guessing $tbl_count contains a file name
# Guessing $line contains your actual query
mysql "$R_DB" $t -e "${line//\`/\\\`} INTO OUTFILE '$DIR/$tbl_count.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\n'"
____HERE
The parameter expansion ${line//\`/\\\`} is a Bash-only feature so this depends on the remote shell being Bash.