Escape single quote inside single quote inside double quote

Question

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;'

Are the ` characters escaped in $line? I think it might solve the issue. — user8246956
– user8246956, Commented Dec 5, 2019 at 10:22
The $(...) around the command is wrong, unless you specifically expect the ssh command to return a command which should be evaluated locally (which in turn seems like a very risky construct; but really, I can't believe that's what you want here). — tripleee
– tripleee, Commented Dec 5, 2019 at 10:45
I tried to post an answer but it's really hard to guess what command you expect to actually end up executing. Could you please edit your question to show the exact command which should execute, and perhaps what you expect all these variables to be set to. — tripleee
– tripleee, Commented Dec 5, 2019 at 10:49
Can you post the code without escaping? Something that works. Ex. the mysql command that works but executed not in a one-liner but by typing into ssh session. Or the INTO OUTFILE part that works when typed inside mysql session. — KamilCuk
– KamilCuk, Commented Dec 5, 2019 at 11:04

tripleee · Accepted Answer · 2019-12-05 11:29:26Z

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.

Chris · Accepted Answer · 2019-12-05 11:29:02Z

After the comment by @KamilCuk it got me thinking about the escape characters and running the code without the SSH.

I have rewritten the code to only contain " or ' which results in:

ssh server "mysql --defaults-extra-file=$SQL_CREDS_FILE $R_DB -e \"$line INTO OUTFILE '$DIR/$tbl_count.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\n'\"" < /dev/null

Also removing the ` helped, as anything between that character will be executed as a command.

Collectives™ on Stack Overflow

Escape single quote inside single quote inside double quote

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related