Get rid of the funny quotes.
bash (at least, and others) interprets funny quotes (backticks) as delimiters
for commands which are to be executed by calling bash and inserting the results
into the command. You only need delimiters around identifiers if you insist on
using ill-conceived characters within the names for identifiers -- like
embedded spaces or other characters with special meanings such as & | && || ^
etc.
sqlite3 freenas-v1.db "UPDATE system_certificate SET
cert_certificate=readfile('fullchain.pem'),
cert_privatekey=readfile('privkey.pem') WHERE ID=4;"
> -----Original Message-----
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Jacob Sylvia
> Sent: Monday, 6 March, 2017 17:10
> To: SQLite mailing list
> Subject: Re: [sqlite] Need some help running sqlite3 command line
>
> So apparently the version of sqlite3 that comes with FreeNAS (3.14.1
> 2016-08-11 18:53:32 a12d8059770df4bca59e321c266410344242bf7b) AND the
> (older) version that comes with Ubuntu 16.04 (3.11.0 2016-02-15 17:29:24
> 3d862f207e3adc00f78066799ac5a8c282430a5f) don't support the readfile
> method:
>
> ./update_certs.sh: line 32: system_certificate: command not found
> Error: near "SET": syntax error
>
> Here is line 32:
> sqlite3 freenas-v1.db "UPDATE `system_certificate` SET
> cert_certificate=readfile('fullchain.pem'),
> cert_privatekey=readfile('privkey.pem') WHERE ID=4;"
>
> I might have to resort to the "heredoc" version, even though I'd rather
> not... Perhaps I can get a binary with the readfile function compiled in,
> or perhaps I'm missing something?
>
> How can I check if that support is compiled in?
>
>
>
> ~Jake
> --
> Jacob D. Sylvia
> Jake Sylvia's Consulting
> 430 Cole St.
> Seekonk, MA 02771
> Cell: (508) - 954 - 2536
> EMail: jacob.syl...@gmail.com
> Web: http://www.jacobsylvia.com
>
> On Mon, Mar 6, 2017 at 11:28 AM, Jacob Sylvia <jacob.syl...@gmail.com>
> wrote:
>
> > Keith, thanks! I *think* this is exactly what I'm looking for. I will
> > try this as soon as I get home.
> >
> >
> > ~Jake
> > --
> > Jacob D. Sylvia
> > Jake Sylvia's Consulting
> > 430 Cole St.
> > Seekonk, MA 02771
> > Cell: (508) - 954 - 2536 <(508)%20954-2536>
> > EMail: jacob.syl...@gmail.com
> > Web: http://www.jacobsylvia.com
> >
> > On Mon, Mar 6, 2017 at 9:18 AM, Keith Medcalf <kmedc...@dessus.com>
> wrote:
> >
> >>
> >> If you have a recent enough version of the SQLite3 command line shell:
> >>
> >> sqlite3 config.db "update table set value=readfile('pubkey.pem') where
> >> key='pubkey';"
> >> sqlite3 config.db "update table set value=readfile('privkey.pem') where
> >> key='privkey';"
> >>
> >> or, create a file, called for example, updatekeys.sql containing:
> >>
> >> update table set value=readfile('pubkey.pem') where key='pubkey';
> >> update table set value=readfile('privkey.pem') where key='privkey';
> >>
> >> then run in with:
> >>
> >> sqlite3 config.db < updatekeys.sql
> >>
> >> If you want to copy the existing fields to files you can do something
> >> like this:
> >>
> >> select writefile('pubkey.bak', value) from table where key='pubkey';
> >> select writefile('privkey.bak', value) from table where key='privkey';
> >>
> >>
> >> This is documented in section 6.1 of the CLI page
> >> https://www.sqlite.org/cli.html
> >>
> >>
> >> > -----Original Message-----
> >> > From: sqlite-users [mailto:sqlite-users-
> boun...@mailinglists.sqlite.org
> >> ]
> >> > On Behalf Of Jacob Sylvia
> >> > Sent: Sunday, 5 March, 2017 17:39
> >> > To: sqlite-users@mailinglists.sqlite.org
> >> > Subject: [sqlite] Need some help running sqlite3 command line
> >> >
> >> > Howdy all!
> >> >
> >> > I'm working on getting letsencrypt certificates into my FreeNAS
> >> > installation... I can't find anyone who can help me with the last
> >> piece,
> >> > the sqlite command line piece...
> >> >
> >> > The reference post is here:
> >> > https://forums.freenas.org/index.php?threads/letsencrypt-web
> >> gui-ssl-need-
> >> > sqlite3-cli-guru.50678/
> >> >
> >> > Basically, I cant figure out how to update a text field in the sqlite
> >> > config database from the command line...
> >> >
> >> > The FreeNAS Config file is a sqlite3 database, and the two
> certificate
> >> > entries (the certificate and the private key) are stored in "TEXT"
> >> fields
> >> > in a specific table. What I need to do, is take the text from the
> two
> >> PEM
> >> > certificate/key files, and, via the command line, update the
> >> > appropriate fields... I can do "simple" fields, but I can't figure
> out
> >> > how
> >> > to do "TEXT" fields, given the fact that the text has line-breaks,
> etc.
> >> >
> >> > Any help would be greatly appreciated.
> >> >
> >> > Thanks!!!
> >> >
> >> > ~Jake
> >> > --
> >> > Jacob D. Sylvia
> >> > Jake Sylvia's Consulting
> >> > 430 Cole St.
> >> > Seekonk, MA 02771
> >> > Cell: (508) - 954 - 2536
> >> > EMail:jacob.syl...@gmail.com
> >> > Web: http://www.jacobsylvia.com
> >> > _______________________________________________
> >> > sqlite-users mailing list
> >> > sqlite-users@mailinglists.sqlite.org
> >> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >>
> >>
> >> _______________________________________________
> >> sqlite-users mailing list
> >> sqlite-users@mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
> >
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
