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
– Austin Mullins, Commented Jul 19, 2014 at 22:00
As a side note, aren't you "over quoting" your SQL statement? Are those \" required? — Sylvain Leroux
– Sylvain Leroux, Commented Jul 19, 2014 at 22:03
No, they aren't, I corrected that in my script, but left them here untouched. ;) — branquito
– branquito, Commented Jul 19, 2014 at 22:04

Sylvain Leroux · Accepted Answer · 2014-07-19 22:06:18Z

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, 2014 at 22:06

answered Jul 19, 2014 at 21:58

Sylvain Leroux

52.3k8 gold badges114 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

sqlite query from bash script, single quote breaks syntax

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related