escape single quote in shell variable

问题:

I wrote a bash script to insert values to sqlite database. The command is as follow

sqlite3 ${db_name} "insert into ${table_name} (${column1},${column2}) values ('$f1','$f2');"

This command works fine until f1 variable contains a single quote

# e.g f1="I'm just kidding"
# the command reported error
Error: near "m": syntax error

May someone please show me how can we escape the single quote inside the variable? Any recommendations are appreciated. Thanks!

回答1:

To escape a single quote for SQL, you double it (https://www.sqlite.org/faq.html#q14):

$ f1="I'm just kidding"
$ echo "${f1//\'/''}"
I''m just kidding
$ f2="no single quotes"
$ echo "${f2//\'/''}"
no single quotes

So

sqlite3 ${db_name} "insert into ${table_name} (${column1},${column2}) values ('${f1//\'/''}','${f2//\'/''}');"

回答2:

from bash you can use ${varname//x/y} to replace all instances of x with y in the varname variable.

sqlite3 ${db_name} "insert into ${table_name} (${column1},${column2}) values ('${f1//\'/\'}','${f2//\'/\'}');"

will replace any ' with \' though @ignacioVazquez-Abrams has the best answer as php perl python all have modules to help sanitise input.

回答3:

You could use \

f1="I\'m just kidding"