4

I am trying to insert a value into my sqlite DB that consists of multiple lines and special characters.

For example:

{
"foo"="$BAR"
}

I have been using the sqlite3 command but I dont know how to get this in.

Also within my bash script I have variables like $BAR that need to be parsed.

Updated command (with samples variables.)

API=2039470928570983
USERNAME=Admin
PASSWORD=PASSWORD
sqlite3 /home/xbmc/test.db "INSERT INTO DownloadClients VALUES (1,1,Sabnzbd,Sabnzbd,'{"host": "localhost", "port": 8085, "apiKey": "$API", "username": "$USERNAME", "password": "$PASSWORD", "tvCategory": "tv", "recentTvPriority": 1, "olderTvPriority": -100, "useSsl": false}', SabnzbdSettings)"

I now get Error: no such column: Sabnzbd I did try listing the column names before the VALUES operator but I got the same error.

JpaytonWPD
  • 141
  • 1
  • 6
  • post script block; I don't understand what is the problem with `"foo"="$BAR"` part. Maybe you want your script to literally have $BAR at some point; not evaluated, in which case you should use `'$BAR'` – Hrvoje Špoljar Nov 17 '14 at 16:55
  • The variable may work for all I know. I cant get past the `{` with out an error. There are also commas within the value. Everything between and including the `{}` is a value for one column – JpaytonWPD Nov 17 '14 at 17:16
  • If you don’t want to move to something like Python, where this would be safer, you could install jq and use that to escape the json to be shell-escaped (I think no has a function for that) – Cameron Kerr Dec 01 '18 at 07:56

3 Answers3

1

Somewhere on the internet I had found this solution some time ago:

sqlite3 $database <<EOF
ATTACH DATABASE "${example_db}" AS ref;
YOUR MULTILINE
SQLITE STATEMENT;
CREATE TEMPORARY TABLE...;
TEMPORARY TABLE STILL THERE;
EOF

Not sure what happens when you do multiple SELECT statements.

Radio Controlled
  • 113
  • 4
0

try with this 

sqlite3 test.db "INSERT INTO table VALUES (1,1,Sabnzbd,Sabnzbd,q'{"host": "localhost", "port": 8085, "apiKey": "$API", "username": "$USERNAME", "password": "$PASSWORD", "tvCategory": "tv", "recentTvPriority": 1, "olderTvPriority": -100, "useSsl": false}', SabnzbdSettings)"

This way you will get $VARIABLES to render inside query, if {} still throw errors you will need to figure out a way to escape this json string you're trying to insert.

Hrvoje Špoljar
  • 5,162
  • 25
  • 42
  • That does seem to work but now I am getting `Error: no such column: Sabnzbd` Sabnzbd is the value not the column. I tried adding the list of column names relitave to the list of the values before the VALUES operator but it says the same thing. – JpaytonWPD Nov 17 '14 at 22:14
  • try longer form `INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...)` – Hrvoje Špoljar Nov 17 '14 at 22:18
  • Thats what I tried. Turns out it needs single quotes around the values. – JpaytonWPD Nov 17 '14 at 22:31
0

The whole string has to be in Double Quotes. " The Values have to be in Single Quotes. '

sqlite3 /home/xbmc/test.db "INSERT INTO DownloadClients VALUES (NULL,'$USERNAME','Three','Four','{"host": "localhost", "port": 8085, "apiKey": "$API", "username": "$USERNAME", "password": "$PASSWORD", "tvCategory": "tv", "recentTvPriority": 1, "olderTvPriority": -100, "useSsl": false}', 5)"
JpaytonWPD
  • 141
  • 1
  • 6