sqlite query from bash script, single quote breaks syntax

Question

I have following in bash script:

sqlite3 database.db "INSERT INTO tbl (col1,col2) VALUES ('\"$line\"','\"$stdout\"');"

,where $line variable isn't going to make any trouble, but my $stdout variable is output from whois $line, so it contains many chars, one of them being ' which breaks the sqlite3 syntax.

Is there some way I could quote-escape that variable, so that it passes well in this query?

EDIT

Sorry, I found my solution here, https://stackoverflow.com/a/14340973/2434479.

Should I delete this question?

Yes. This is a pretty close match to that other question. I'm glad Stack Overflow solved your problem one way or the other. — Austin Mullins, Jul 19 '14 at 22:00
As a side note, aren't you "over quoting" your SQL statement? Are those `\"` required? — Sylvain Leroux, Jul 19 '14 at 22:03
No, they aren't, I corrected that in my script, but left them here untouched. ;) — branquito, Jul 19 '14 at 22:04

Sylvain Leroux · Accepted Answer · 2014-07-19T22:06:18.747

3

The standard SQL escape sequence for simple quote ' is the double simple quote ''.

Using bash pattern substitution, you may perform the necessary replacements:

sh$ DSQ="''"
sh$ stdout="I'm in a big'trouble"
sh$ echo "${stdout//\'/$DSQ}"
I''m in a big''trouble

In your specific case:

DSQ="''"
sqlite3 database.db \
    "INSERT INTO tbl (col1,col2) VALUES ('$line','${stdout//\'/$DSQ}');"

edited Jul 19 '14 at 22:06

answered Jul 19 '14 at 21:58

Sylvain Leroux

50,096
7
103
125

sqlite query from bash script, single quote breaks syntax

1 Answers1