Quantcast

Need some help running sqlite3 command line

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
15 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Need some help running sqlite3 command line

Jacob Sylvia
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-webgui-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:[hidden email]
Web: http://www.jacobsylvia.com
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Need some help running sqlite3 command line

Clemens Ladisch
Jacob Sylvia wrote:
> Basically, I cant figure out how to update a text field in the sqlite
> config database from the command line...

sqlite3 config.db "UPDATE SomeTable SET SomeColumn = 'value' WHERE ID = 42"

You need to know the database file name, the table name, the column
name(s), the new value(s), and how to identify the row(s) to update.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Need some help running sqlite3 command line

Jacob Sylvia
Right...  I know the syntax. The problem lies in the 'value' part. Because
the text has line breaks / carriage returns, I can't figure out how to do
it appropriately from the command line.

I've tried setting the value in a bash variable, but the breaks get
interpreted and the syntax fails.

~Jake
--
Jacob D. Sylvia
Jake Sylvia's Consulting
430 Cole St.
Seekonk, MA 02771
Cell: (508) - 954 - 2536
EMail: [hidden email]
Web: http://www.jacobsylvia.com

On Mar 6, 2017 6:28 AM, "Clemens Ladisch" <[hidden email]> wrote:

> Jacob Sylvia wrote:
> > Basically, I cant figure out how to update a text field in the sqlite
> > config database from the command line...
>
> sqlite3 config.db "UPDATE SomeTable SET SomeColumn = 'value' WHERE ID = 42"
>
> You need to know the database file name, the table name, the column
> name(s), the new value(s), and how to identify the row(s) to update.
>
>
> Regards,
> Clemens
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Need some help running sqlite3 command line

Hick Gunter
From sql shell (from a file works the same way):

asql> create temp table test (a text);
asql> insert into test values('a
   ...> b
   ...> c
   ...> d');
rows inserted
-------------
1
asql> select * from test;
a
--------------
a
b
c
d

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Jacob Sylvia
Gesendet: Montag, 06. März 2017 13:02
An: SQLite mailing list <[hidden email]>
Betreff: Re: [sqlite] Need some help running sqlite3 command line

Right...  I know the syntax. The problem lies in the 'value' part. Because the text has line breaks / carriage returns, I can't figure out how to do it appropriately from the command line.

I've tried setting the value in a bash variable, but the breaks get interpreted and the syntax fails.

~Jake
--
Jacob D. Sylvia
Jake Sylvia's Consulting
430 Cole St.
Seekonk, MA 02771
Cell: (508) - 954 - 2536
EMail: [hidden email]
Web: http://www.jacobsylvia.com

On Mar 6, 2017 6:28 AM, "Clemens Ladisch" <[hidden email]> wrote:

> Jacob Sylvia wrote:
> > Basically, I cant figure out how to update a text field in the
> > sqlite config database from the command line...
>
> sqlite3 config.db "UPDATE SomeTable SET SomeColumn = 'value' WHERE ID = 42"
>
> You need to know the database file name, the table name, the column
> name(s), the new value(s), and how to identify the row(s) to update.
>
>
> Regards,
> Clemens
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Need some help running sqlite3 command line

Jacob Sylvia
I guess I should have mentioned that I'm trying to do this from a script,
so that makes the command shell difficult.

~Jake
--
Jacob D. Sylvia
Jake Sylvia's Consulting
430 Cole St.
Seekonk, MA 02771
Cell: (508) - 954 - 2536
EMail: [hidden email]
Web: http://www.jacobsylvia.com

On Mar 6, 2017 7:23 AM, "Hick Gunter" <[hidden email]> wrote:

> From sql shell (from a file works the same way):
>
> asql> create temp table test (a text);
> asql> insert into test values('a
>    ...> b
>    ...> c
>    ...> d');
> rows inserted
> -------------
> 1
> asql> select * from test;
> a
> --------------
> a
> b
> c
> d
>
> -----Ursprüngliche Nachricht-----
> Von: sqlite-users [mailto:[hidden email]]
> Im Auftrag von Jacob Sylvia
> Gesendet: Montag, 06. März 2017 13:02
> An: SQLite mailing list <[hidden email]>
> Betreff: Re: [sqlite] Need some help running sqlite3 command line
>
> Right...  I know the syntax. The problem lies in the 'value' part. Because
> the text has line breaks / carriage returns, I can't figure out how to do
> it appropriately from the command line.
>
> I've tried setting the value in a bash variable, but the breaks get
> interpreted and the syntax fails.
>
> ~Jake
> --
> Jacob D. Sylvia
> Jake Sylvia's Consulting
> 430 Cole St.
> Seekonk, MA 02771
> Cell: (508) - 954 - 2536
> EMail: [hidden email]
> Web: http://www.jacobsylvia.com
>
> On Mar 6, 2017 6:28 AM, "Clemens Ladisch" <[hidden email]> wrote:
>
> > Jacob Sylvia wrote:
> > > Basically, I cant figure out how to update a text field in the
> > > sqlite config database from the command line...
> >
> > sqlite3 config.db "UPDATE SomeTable SET SomeColumn = 'value' WHERE ID =
> 42"
> >
> > You need to know the database file name, the table name, the column
> > name(s), the new value(s), and how to identify the row(s) to update.
> >
> >
> > Regards,
> > Clemens
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___________________________________________
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: [hidden email]
>
> This communication (including any attachments) is intended for the use of
> the intended recipient(s) only and may contain information that is
> confidential, privileged or legally protected. Any unauthorized use or
> dissemination of this communication is strictly prohibited. If you have
> received this communication in error, please immediately notify the sender
> by return e-mail message and delete all copies of the original
> communication. Thank you for your cooperation.
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Need some help running sqlite3 command line

Clemens Ladisch
In reply to this post by Jacob Sylvia
Jacob Sylvia wrote:
> The problem lies in the 'value' part. Because the text has line breaks /
> carriage returns, I can't figure out how to do it appropriately from the
> command line.

Just use line breaks:

  $ sqlite3 ":memory:" "select 'first line
  > second line';"
  first line
  second line

What did you try?


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Need some help running sqlite3 command line

Hick Gunter
In reply to this post by Jacob Sylvia
Try using the "here document" syntax.

In <whatever>.sh

sqlite3 <<TheEnd
Update <table> set <field>='first line
second line
third line' where <condition>;
TheEnd

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Jacob Sylvia
Gesendet: Montag, 06. März 2017 13:25
An: SQLite mailing list <[hidden email]>
Betreff: Re: [sqlite] Need some help running sqlite3 command line

I guess I should have mentioned that I'm trying to do this from a script, so that makes the command shell difficult.

~Jake
--
Jacob D. Sylvia
Jake Sylvia's Consulting
430 Cole St.
Seekonk, MA 02771
Cell: (508) - 954 - 2536
EMail: [hidden email]
Web: http://www.jacobsylvia.com

On Mar 6, 2017 7:23 AM, "Hick Gunter" <[hidden email]> wrote:

> From sql shell (from a file works the same way):
>
> asql> create temp table test (a text); insert into test values('a
>    ...> b
>    ...> c
>    ...> d');
> rows inserted
> -------------
> 1
> asql> select * from test;
> a
> --------------
> a
> b
> c
> d
>
> -----Ursprüngliche Nachricht-----
> Von: sqlite-users
> [mailto:[hidden email]]
> Im Auftrag von Jacob Sylvia
> Gesendet: Montag, 06. März 2017 13:02
> An: SQLite mailing list <[hidden email]>
> Betreff: Re: [sqlite] Need some help running sqlite3 command line
>
> Right...  I know the syntax. The problem lies in the 'value' part.
> Because the text has line breaks / carriage returns, I can't figure
> out how to do it appropriately from the command line.
>
> I've tried setting the value in a bash variable, but the breaks get
> interpreted and the syntax fails.
>
> ~Jake
> --
> Jacob D. Sylvia
> Jake Sylvia's Consulting
> 430 Cole St.
> Seekonk, MA 02771
> Cell: (508) - 954 - 2536
> EMail: [hidden email]
> Web: http://www.jacobsylvia.com
>
> On Mar 6, 2017 6:28 AM, "Clemens Ladisch" <[hidden email]> wrote:
>
> > Jacob Sylvia wrote:
> > > Basically, I cant figure out how to update a text field in the
> > > sqlite config database from the command line...
> >
> > sqlite3 config.db "UPDATE SomeTable SET SomeColumn = 'value' WHERE
> > ID =
> 42"
> >
> > You need to know the database file name, the table name, the column
> > name(s), the new value(s), and how to identify the row(s) to update.
> >
> >
> > Regards,
> > Clemens
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___________________________________________
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: [hidden email]
>
> This communication (including any attachments) is intended for the use
> of the intended recipient(s) only and may contain information that is
> confidential, privileged or legally protected. Any unauthorized use or
> dissemination of this communication is strictly prohibited. If you
> have received this communication in error, please immediately notify
> the sender by return e-mail message and delete all copies of the
> original communication. Thank you for your cooperation.
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Need some help running sqlite3 command line

Keith Medcalf
In reply to this post by Jacob Sylvia

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:[hidden email]]
> On Behalf Of Jacob Sylvia
> Sent: Sunday, 5 March, 2017 17:39
> To: [hidden email]
> 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-webgui-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:[hidden email]
> Web: http://www.jacobsylvia.com
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Need some help running sqlite3 command line

Jacob Sylvia
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
EMail: [hidden email]
Web: http://www.jacobsylvia.com

On Mon, Mar 6, 2017 at 9:18 AM, Keith Medcalf <[hidden email]> 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:[hidden email]]
> > On Behalf Of Jacob Sylvia
> > Sent: Sunday, 5 March, 2017 17:39
> > To: [hidden email]
> > 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-
> webgui-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:[hidden email]
> > Web: http://www.jacobsylvia.com
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Need some help running sqlite3 command line

Jacob Sylvia
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: [hidden email]
Web: http://www.jacobsylvia.com

On Mon, Mar 6, 2017 at 11:28 AM, Jacob Sylvia <[hidden email]>
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: [hidden email]
> Web: http://www.jacobsylvia.com
>
> On Mon, Mar 6, 2017 at 9:18 AM, Keith Medcalf <[hidden email]> 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:[hidden email]
>> ]
>> > On Behalf Of Jacob Sylvia
>> > Sent: Sunday, 5 March, 2017 17:39
>> > To: [hidden email]
>> > 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:[hidden email]
>> > Web: http://www.jacobsylvia.com
>> > _______________________________________________
>> > sqlite-users mailing list
>> > [hidden email]
>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Need some help running sqlite3 command line

Richard Hipp-3
On 3/6/17, Jacob Sylvia <[hidden email]> wrote:
>
> How can I check if that support is compiled in?
>

The readfile() SQL function was add to the command-line shell in
SQLite version 3.8.6 released on 2014-08-15.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Need some help running sqlite3 command line

Jacob Sylvia
I know what the problem was... bash was interpreting the `table_name` piece
as a command.  I had to escape the backticks...

This looks like it's going to work like a charm!!!

I'll report back!


~Jake
--
Jacob D. Sylvia
Jake Sylvia's Consulting
430 Cole St.
Seekonk, MA 02771
Cell: (508) - 954 - 2536
EMail: [hidden email]
Web: http://www.jacobsylvia.com

On Mon, Mar 6, 2017 at 7:39 PM, Richard Hipp <[hidden email]> wrote:

> On 3/6/17, Jacob Sylvia <[hidden email]> wrote:
> >
> > How can I check if that support is compiled in?
> >
>
> The readfile() SQL function was add to the command-line shell in
> SQLite version 3.8.6 released on 2014-08-15.
>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Need some help running sqlite3 command line

Keith Medcalf
In reply to this post by Jacob Sylvia

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:[hidden email]]
> 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: [hidden email]
> Web: http://www.jacobsylvia.com
>
> On Mon, Mar 6, 2017 at 11:28 AM, Jacob Sylvia <[hidden email]>
> 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: [hidden email]
> > Web: http://www.jacobsylvia.com
> >
> > On Mon, Mar 6, 2017 at 9:18 AM, Keith Medcalf <[hidden email]>
> 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-
> [hidden email]
> >> ]
> >> > On Behalf Of Jacob Sylvia
> >> > Sent: Sunday, 5 March, 2017 17:39
> >> > To: [hidden email]
> >> > 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:[hidden email]
> >> > Web: http://www.jacobsylvia.com
> >> > _______________________________________________
> >> > sqlite-users mailing list
> >> > [hidden email]
> >> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >>
> >>
> >> _______________________________________________
> >> sqlite-users mailing list
> >> [hidden email]
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Need some help running sqlite3 command line

Jacob Sylvia
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: [hidden email]
Web: http://www.jacobsylvia.com

On Mon, Mar 6, 2017 at 8:18 PM, Keith Medcalf <[hidden email]> 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:[hidden email]]
> > 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: [hidden email]
> > Web: http://www.jacobsylvia.com
> >
> > On Mon, Mar 6, 2017 at 11:28 AM, Jacob Sylvia <[hidden email]>
> > 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: [hidden email]
> > > Web: http://www.jacobsylvia.com
> > >
> > > On Mon, Mar 6, 2017 at 9:18 AM, Keith Medcalf <[hidden email]>
> > 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-
> > [hidden email]
> > >> ]
> > >> > On Behalf Of Jacob Sylvia
> > >> > Sent: Sunday, 5 March, 2017 17:39
> > >> > To: [hidden email]
> > >> > 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:[hidden email]
> > >> > Web: http://www.jacobsylvia.com
> > >> > _______________________________________________
> > >> > sqlite-users mailing list
> > >> > [hidden email]
> > >> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/
> sqlite-users
> > >>
> > >>
> > >>
> > >> _______________________________________________
> > >> sqlite-users mailing list
> > >> [hidden email]
> > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >>
> > >
> > >
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
cl
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Need some help running sqlite3 command line

cl
In reply to this post by Jacob Sylvia
Jacob Sylvia <[hidden email]> wrote:
> I know what the problem was... bash was interpreting the `table_name` piece
> as a command.  I had to escape the backticks...
>
Yes, `command` is the old-fashioned way of saying $(command) in bash.

--
Chris Green
·

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...