Escape a single quote in a shell variable

Question

I wrote a Bash script to insert values to an SQLite database. The command is as follows:

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

This command works fine until the f1 variable contains a single quote:

# E.g., f1="I'm just kidding"
# The command reported an error:
Error: near "m": syntax error

How can we escape the single quote inside the variable?

Answer 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//\'/''}');"

Answer 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 the PHP, Perl, and Python implementations all have modules to help sanitise input.