Sqlite3 Export to JSON

I create custom scripted Detection Methods for SCCM utilizing JSON. Powershell handles JSON nicely, particularly if converting from an object. Powershell will do the necessary escaping of characters, which can be missed when manually creating JSON, or when you know there will be many characters to escape. For example, here is some data which would need escaping, and its resultant JSON:

PS A:\> $Example = @"
>> localpath,registry,share,text
>> C:\Temp,HKLM:\SOFTWARE,\\thatshare\me,use a`ttab
>> "@
PS A:\> $Example
localpath,registry,share,text
C:\Temp,HKLM:\SOFTWARE,\\thatshare\me,use a     tab
PS A:\> $Example | ConvertFrom-Csv | ConvertTo-Json
{
    "localpath":  "C:\\Temp",
    "registry":  "HKLM:\\SOFTWARE",
    "share":  "\\\\thatshare\\me",
    "text":  "use a\ttab"
}

My Detection Methods will often look for file checksums, which means a path to the file and the expected checksum.To avoid mistakes, and to make it easier on me, I create a SqLite3 database with the items for my JSON. Exporting from SqLite3 can be sent to a CSV file, and then parsed into JSON. A simple database example:

PS A:\> sqlite3 .\example.db
SQLite version 3.24.0 2018-06-04 19:24:41
Enter ".help" for usage hints.
sqlite> .mode line
sqlite> SELECT * FROM example;
   name = David
twitter = dbsteimle
    url = rhymeswithtimely.blogspot.com

   name = Commander Candy
twitter = codingComander
    url = codingcommanders.com
sqlite> .quit

Now, a new trick to me it utilizing temporary files. I am using the dot NET method, which is usable in Linux Powershell as well (your mileage may vary). You can create a temp file with [System.IO.Path]::GetTempFileName(). To use that file, you want to assign it to a variable.

PS A:\> $TempCsv = [System.IO.Path]::GetTempFileName()
PS A:\> $TempCsv
C:/Users/david/AppData/Local/Temp/tmp47AD.tmp

Next, because SqLite3 does not use \ character in its paths, we need to change them to /.

PS A:\> $TempCsv = $TempCsv.Replace("\","/")
PS A:\> $TempCsv
C:/Users/david/AppData/Local/Temp/tmp47AD.tmp

Now we can create our SQL commands. This could also be a file, but I will use a here-string instead.

PS A:\> $TempSql = @"
>> .headers on
>> .mode csv
>> .output $TempCsv
>> SELECT name,
>>        twitter,
>>        url
>> FROM example;
>> .quit
>> "@
PS A:\> $TempSql
.headers on
.mode csv
.output C:/Users/david/AppData/Local/Temp/tmp47AD.tmp
SELECT name,
       twitter,
       url
FROM example;
.quit

Notice our converted $TempCsv value is in the $TempSql here-string.

Now, pipe the $TempSql into SqLite3:

PS A:\> $TempSql | sqlite3 .\example.db

Our $TempCsv frile will now have the output from SqLite3 in CSV format.

PS A:\> gc $TempCsv
name,twitter,url
David,dbsteimle,rhymeswithtimely.blogspot.com
"Commander Candy",codingComander,codingcommanders.com

We can now use this CSV formatted data, but must convert it to a Powershell Object.

PS A:\> Get-Content $TempCsv | ConvertFrom-Csv | ConvertTo-Json
[
    {
        "name":  "David",
        "twitter":  "dbsteimle",
        "url":  "rhymeswithtimely.blogspot.com"
    },
    {
        "name":  "Commander Candy",
        "twitter":  "codingComander",
        "url":  "codingcommanders.com"
    }
]

Using Powershell to Get Objects from Sqlite3

As discussed in SQLite3 and Powershell Redirection, we do not need to add any special Powershell tools to access sqlite3 if we utilize piping.

Versioning on my system:

SQLite version 3.24.0 2018-06-04 19:24:41
PSVersion 5.1.14393.2791

Note: I create an Alias for sqlite3:

PS A:\sqlite_to_csv> Set-Alias -Name sqlite3 -Value A:\sqlite3\sqlite3.exe


Creating an Object in Powershell is generally the best way to utilize data, and in my case I need an easy way to generate JSON files, which Powershell can be quite helpful at.

First, let's make a sample database:

PS A:\sqlite_to_csv> sqlite3 sample.db
SQLite version 3.24.0 2018-06-04 19:24:41
Enter ".help" for usage hints.
sqlite> CREATE TABLE sample (
   ...> id INTEGER PRIMARY KEY AUTOINCREMENT,
   ...> name TEXT,
   ...> twitter TEXT,
   ...> blog TEXT
   ...> );
sqlite> INSERT INTO sample
   ...> (name,twitter,blog)
   ...> VALUES
   ...> ('David Steimle','dbsteimle','rhymeswithtimely.blogspot.com');
sqlite> SELECT * FROM sample;
1|David Steimle|dbsteimle|rhymeswithtimely.blogspot.com


Now we have something to query.

First, we want to create the header, which will provide output in CSV mode with headers. For this I use a Here-String.

PS A:\sqlite_to_csv> $QuerySetup = @"
>> .mode csv
>> .headers on
>>
>> "@


Then we can define our query; we will do a simple SELECT only, for now, and combine it with our query setup.

PS A:\sqlite_to_csv> $Query = "SELECT * FROM sample;"
PS A:\sqlite_to_csv> $MySql = "$QuerySetup$Query"
PS A:\sqlite_to_csv> Write-Host $MySql
.mode csv
.headers on
SELECT * FROM sample;


We then assign our result to an Object with pipes:

PS A:\sqlite_to_csv> $MyObject = $MySql | sqlite3 .\sample.db | ConvertFrom-Csv
PS A:\sqlite_to_csv> $MyObject | Format-List

id      : 1
name    : David Steimle
twitter : dbsteimle
blog    : rhymeswithtimely.blogspot.com


This Object is now usable in all the normal ways, such as turning it into JSON (I will output to a file, as that would be my end goal):

PS A:\sqlite_to_csv> $MyObject | ConvertTo-Json | Set-Content sample.json
PS A:\sqlite_to_csv> Get-Content .\sample.json
{
    "id":  "1",
    "name":  "David Steimle",
    "twitter":  "dbsteimle",
    "blog":  "rhymeswithtimely.blogspot.com"
}


While in a basic sense this works for me, I do need the ability to have sub-objects and arrays in my JSON. This means additional tables. The obvious place to start in this example would be with Social Media. First, we will make a table of social media sites:

sqlite> CREATE TABLE socialmedium (
   ...> id INTEGER PRIMARY KEY AUTOINCREMENT,
   ...> name TEXT,
   ...> url TEXT
   ...> );
sqlite> INSERT INTO socialmedium (name,url) VALUES
   ...> ('Twitter','https://twitter.com/');
sqlite> INSERT INTO socialmedium (name,url) VALUES
   ...> ('Instagram','https://instagram.com/');
sqlite> SELECT * FROM socialmedium;
id          name        url
----------  ----------  --------------------
1           Twitter     https://twitter.com/
2           Instagram   https://instagram.co


Then a lookup table with user details:

sqlite> CREATE TABLE socialuser (
   ...> id INTEGER PRIMARY KEY AUTOINCREMENT,
   ...> user INTEGER,
   ...> site INTEGER,
   ...> username TEXT
   ...> );
sqlite> INSERT INTO socialuser (user,site,username) VALUES
...> (1,1,'dbsteimle');
sqlite> SELECT * FROM socialuser;
id user site username
---------- ---------- ---------- ----------
1 1 1 dbsteimle

These tables use the PRIMARY KEY from the sample table as a reference point. To work with out current Object, we need to add a new Property, and assign it as an empty hash table:

PS A:\sqlite_to_csv> $MyObject | Add-Member -NotePropertyName SocialMedia -NotePropertyValue @{}
PS A:\sqlite_to_csv> $MyObject

id          : 1
name        : David Steimle
twitter     : dbsteimle
blog        : rhymeswithtimely.blogspot.com
SocialMedia : {}


We need to use an INNER JOIN in this scenario, since we are looking up values in two lookup tables:

PS A:\sqlite_to_csv> $Query = @"
>> SELECT user,name,url,username
>> FROM socialuser
>> INNER JOIN socialmedium ON socialmedium.id=socialuser.site
>> WHERE user=$($MyObject.id);
>> "@
PS A:\sqlite_to_csv> $MySql = "$QuerySetup$Query"
PS A:\sqlite_to_csv> Write-Host $MySql
.mode csv
.headers on
SELECT user,name,url,username
FROM socialuser
INNER JOIN socialmedium ON socialmedium.id=socialuser.site
WHERE user=1;


Now, pipe through sqlite3:

PS A:\sqlite_to_csv> $MyObject.SocialMedia = $MySql | sqlite3 .\sample.db | ConvertFrom-Csv
PS A:\sqlite_to_csv> $MyObject.SocialMedia

user name    url                  username
---- ----    ---                  --------
1    Twitter https://twitter.com/ dbsteimle


And Conversion to JSON:

PS A:\sqlite_to_csv> $MyObject | ConvertTo-Json | Set-Content sample.json
PS A:\sqlite_to_csv> Get-Content .\sample.json
{
    "id":  "1",
    "name":  "David Steimle",
    "twitter":  "dbsteimle",
    "blog":  "rhymeswithtimely.blogspot.com",
    "SocialMedia":  {
                        "user":  "1",
                        "name":  "Twitter",
                        "url":  "https://twitter.com/",
                        "username":  "dbsteimle"
                    }
}


To best utilize this, we would want a function where we could utilize a known value as a parameter. We may not know that id 1 matches name 'David Steimle', but if we know the database, we might know to look for name="David Steimle"

function Get-MyObject {
param( [string]$Name )

$QuerySetup = @"
.mode csv
.headers on

"@

$Query = "SELECT * FROM sample WHERE name='$Name';"
$MySql = "$QuerySetup$Query"
$Temp = $MySql | sqlite3 .\sample.db | ConvertFrom-Csv

$Temp | Add-Member -NotePropertyName SocialMedia -NotePropertyValue @{}

$Query = @"
SELECT user,name,url,username
FROM socialuser
INNER JOIN socialmedium ON socialmedium.id=socialuser.site
WHERE user=$($Temp.id);
"@
$MySql = "$QuerySetup$Query"
$Temp.SocialMedia = $MySql | sqlite3 .\sample.db | ConvertFrom-Csv

$Temp | ConvertTo-Json | Set-Content ".\$Name.json"

return $Temp
}

PS A:\sqlite_to_csv> Get-MyObject -Name "David Steimle"

id          : 1
name        : David Steimle
twitter     : dbsteimle
blog        : rhymeswithtimely.blogspot.com
SocialMedia : @{user=1; name=Twitter; url=https://twitter.com/; username=dbsteimle}

PS A:\sqlite_to_csv> Get-Content '.\David Steimle.json'
{
    "id":  "1",
    "name":  "David Steimle",
    "twitter":  "dbsteimle",
    "blog":  "rhymeswithtimely.blogspot.com",
    "SocialMedia":  {
                        "user":  "1",
                        "name":  "Twitter",
                        "url":  "https://twitter.com/",
                        "username":  "dbsteimle"
                    }
}

Or, assign the result to a variable:

PS A:\sqlite_to_csv> $DavidSteimle = Get-MyObject -Name 'David Steimle'
PS A:\sqlite_to_csv> $DavidSteimle

id : 1
name : David Steimle
twitter : dbsteimle
blog : rhymeswithtimely.blogspot.com
SocialMedia : @{user=1; name=Twitter; url=https://twitter.com/; username=dbsteimle}