SQLite3 and Powershell Redirection

I have a terrible memory, and always forget how this goes.

(Full disclosure, I am using Powershell 5.1.14393.2636/Desktop, and SQLite version 3.24.0)

My primary database at work is SQLite3, and my shell is Powershell. Sometimes I need to grab a set of information from a number of systems, or over a period of time, and record them for analysis. I could use a PS-Object for this, but what if my system crashes or is rebooted by IT? If I use a database I can at least get all data up-until the crash/reboot occurs. So, without adding any fancy connectors from github, lets just pipe.

All of these operations could be performed with a pipe, but I created a sample database in sqlite3. Note that I have created an alias to the executable.

PS A:\> sqlite3 .\sample.db
SQLite version 3.24.0 2018-06-04 19:24:41
Enter ".help" for usage hints.
sqlite> .mode column
sqlite> .headers on
sqlite> CREATE TABLE sample (data TEXT);
sqlite> INSERT INTO sample (data) VALUES ('this');
sqlite> SELECT * FROM sample;
data
----------
this
sqlite> .quit

Now, I like to put text into a $() to variableize it in Powershell. Let's insert a new row and verify:

PS A:\> $("INSERT INTO sample (data) VALUES ('is');") | sqlite3 .\sample.db
PS A:\> sqlite3 .\sample.db
SQLite version 3.24.0 2018-06-04 19:24:41
Enter ".help" for usage hints.
sqlite> .mode column
sqlite> .headers on
sqlite> SELECT * FROM sample;
data
----------
this
is
sqlite> .quit

How about two more?

PS A:\> $("INSERT INTO sample (data) VALUES ('an');") | sqlite3 .\sample.db
PS A:\> $("INSERT INTO sample (data) VALUES ('example');") | sqlite3 .\sample.db

In the above instances we are only performing an INSERT query. If we wanted to do a SELECT query with some style options, we need to create a file (often with a .sql extension):

PS A:\> vi sample.sql
.mode column
.headers on
SELECT * FROM sample;
:wq

Now, our variableized query is in the form of Get-Content:

PS A:\> $(Get-Content .\sample.sql) | sqlite3 .\sample.db
data
----------
this
is
an
example
PS A:\>

If we want our query in a hashtable, we need to make a few changes. First, we need the mode to be CSV:

PS A:\> vi sample.sql
.mode csv
.headers on
SELECT * FROM sample;
:wq

Next, we need to add another pipe to our string:

PS A:\> $samples = $(Get-Content .\sample.sql) | sqlite3 .\sample.db | ConvertFrom-Csv

This will assign the queries output to a hashtable named $samples.

PS A:\> $samples

data
----
this
is
an
example

PS A:\> $samples[0]

data
----
this

PS A:\>

Let's expand that hashtable a bit, by adding a new column:

PS A:\> $("ALTER TABLE sample ADD COLUMN data2 TEXT") | sqlite3 .\sample.db
PS A:\> $("UPDATE sample SET data2='database' WHERE data='this'") | sqlite3 .\sample.db
PS A:\> $("UPDATE sample SET data2='not' WHERE data='is'") | sqlite3 .\sample.db
PS A:\> $("UPDATE sample SET data2='excellent' WHERE data='an'") | sqlite3 .\sample.db
PS A:\> $("UPDATE sample SET data2='of a database' WHERE data='example'") | sqlite3 .\sample.db

PS A:\> vi .\sample.sql
.mode csv
.headers on
SELECT * FROM sample;

PS A:\> $samples = $(Get-Content .\sample.sql) | sqlite3 .\sample.db | ConvertFrom-Csv
PS A:\> $samples

data    data2
----    -----
this    database
is      not
an      excellent
example of a database

Now, editing that .sql file might be a bit of a pain, especially if you use an external editor, or are working on a remote machine. Two options are available.

First, escape the newlines with `n notation. Not that you must use double quotes:

PS A:\> $query = ".mode column`n.headers on`nSELECT * FROM sample;"
PS A:\> $query
.mode column
.headers on
SELECT * FROM sample;
PS A:\> $query | sqlite3 .\sample.db
data        data2
----------  ----------
this        database
is          not
an          excellent
example     of a datab

Second, you can use a here string:

PS A:\> $query = @"
>> .mode column
>> .headers on
>> SELECT * FROM sample;
>> "@
PS A:\> $query | sqlite3 .\sample.db
data        data2
----------  ----------
this        database
is          not
an          excellent
example     of a datab

For a slightly more practical example, let's grab all the DLL files in C:\Windows\System32, and create a database:

$testfiles = Get-ChildItem C:\Windows\System32\*.dll
PS A:\> $query = @"
>> CREATE TABLE dllfiles (
>> lastwritetime TEXT,
>> length INT,
>> name TEXT
>> );
>> "@
PS A:\> $query | sqlite3 dllfiles.db
PS A:\> $('.schema') | sqlite3 dllfiles.db
CREATE TABLE dllfiles (
lastwritetime TEXT,
length INT,
name TEXT
);

Note that .schema is a command to see table information in SQLite3.

Now, let's loop through $testfiles, and populate our database. My system shows 3229 such files.

PS A:\> foreach($file in $testfiles){
>> $("INSERT INTO dllfiles (lastwritetime,length,name) VALUES ('$($file.lastwritetime)',$($file.length),'$($file.name)');") | sqlite3 dllfiles.db
>> }

So, now we can query those 10 files as above from the database:

PS A:\> $query = @"
>> .mode column
>> .headers on
>> SELECT name,length FROM dllfiles LIMIT 10;
>> "@
PS A:\> $query | sqlite3 dllfiles.db
name               length
-----------------  ----------
aadauthhelper.dll  34816
aadcloudap.dll     425984
aadtb.dll          1122304
AagMmcRes.dll      26112
AboveLockAppHost.  284672
accelerometerdll.  53280
accessibilitycpl.  3825152
accountaccessor.d  322048
AccountsRt.dll     441856
ACCTRES.dll        39936

One use this might serve is to add a column for "new_length" or "delta_length", which could be populated after monthly security updates have been applied. You could then query where length != new_length. You would just change the query above (after adding the column) to:

PS A:\> $testfiles = Get-ChildItem C:\Windows\System32\*.dll
PS A:\> foreach($file in $testfiles){
>> $("UPDATE dllfiles SET new_length=$($file.length) WHERE name='$($file.name)');") | sqlite3 dllfiles.db
>> }