This is the bulk of what I needed thanks everyone for their help. This worked like a charm... Now on to why FreeNAS re-encodes the private key into something else... Thought I had it... SO CLOSE.
Thanks all!!! ~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 8:18 PM, Keith Medcalf <kmedc...@dessus.com> wrote: > > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
