How can I escape characters in SQLite via bash shell?

Question

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 them. Here is a typical query:

select * from contacts where source = "Nancy's notes";

How can I send this query from the command line? The basic syntax is something like this:

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

But in this case, the shell misinterprets either the single or double quotes. I've tried escaping using both double and triple slashes but this doesn't work. I'm befuddled. Any suggestions?

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 contacts
      where 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 \$\$\$'"

Comments

[Tony]

Thanks! In some cases double quotes should not be escaped (if they are within the quoted source string, for example), but in general this approach works.

[MarkusQ]

No, they should always be escaped or bash will intercept them. Inside the quoted source string you should use an escaped backslash followed by an escaped quote, i.e., the sequence [\\\"]; sqlite will get a backslash and a quote [\"] which it should then interpret as a quote ["] inside the string.