How Can I Escape Characters In Sqlite Via Bash Shell?

I am trying to send a query to SQLite from the command line using bash. I need to escape both single quotes and double quotes, and escape them so that bash does not misinterpret th

Solution 1:

The trouble with MarkusQ's solution is knowing which characters are special inside double quotes - there are quite a lot of them, including back-ticks, dollar-open parenthesis, dollar-variable, etc.

I would suggest it is better to enclose the string inside single quotes; then, each single quote inside the string needs to be replaced by the sequence quote, backslash, quote, quote:

sqlite3.bin contacts.db 'select * from contactswhere source = "Nancy'\''s notes"'

The first quote in the replacement terminates the current single-quoted string; the backslash-quote represents a literal single quote, and the final quote starts a new single-quoted string. Further, this works with Bourne, Korn, Bash and POSIX shells in general. (C Shell and derivatives have more complex rules needing backslashes to escape newlines, and so on.)

Solution 2:

If bash is your only problem, enclose the whole thing in double quotes and then escape anything that's special within bash double quotes with a single backslash. E.g.:

sqlite3.bin contacts.db "select * from contacts where source = \"Nancy's notes on making \$\$\$\""

Solution 3:

Here I use two single quotes that sqlite interprets as one.

sqlite3.bin contacts.db "select * from contacts where source = 'Nancy''s notes on making \$\$\$'"

Solution 4:

foo_double_single_quote=`echo${foo_with_single_quote} | sed "s/\'/\'\'/"g`

sqlite3 "INSERT INTO bar_table (baz_colname) VALUES ('${foo_double_single_quote}');"

if you have a variable foo_with_single_quote whose contents you want to insert into a DB, you can use sed like so to create a variable that has the ' character duplicated as necessary.