Using tr or sed to prepare string for MySQL input

Question

I've been searching around for a solution to my problem, being that I have a bash script recursively inputting data into a MySQL database.

Sometimes, the string I want to input contains single quote marks, and this is where my coding becomes stuck.

I want to run a sed or a tr filter over the string before I send it to my SQL processing, and use it to change single quotes to an escape character followed by the same quote, e.g. from ' to \'.

I have tried the following off other suggestions but the output remains unaltered:

string="Karl's Oil Lamp"
find="'"
replace="\\\'"
echo $string | sed -r 's/$find/$replace/g'

I can see that my string remains unchanged with the output being Karl's Oil Lamp. I have similar luck when using tr though that may not be the solution I need anyway.

Any kind suggestions? Running Ubuntu 16.04.2 LTS.

Answer 1

Use the following sed expression:

sed "s/'/\\\'/" <<< $string

The output:

Karl\'s Oil Lamp

Answer 2

With parameter expansion, no external tools:

$ string="Karl's Oil Lamp"
$ echo "${string//\'/\\\'}"
Karl\'s Oil Lamp

This is an expansion of the form "${parameter//pattern/string}", replacing all occurrences of pattern with string. pattern is a single quote, which we have to escape \', and string is a backslash and a quote, both of which we have to escape \\\'.

---

The reason your try fails is that single quotes prevent parameter expansion, see Environment variable substitution in sed.