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"
    }
]