jdhitsolutions
Passing malformed query to "Invoke-MySQLiteQuery", resulting in SQLite Error/Warning does not raise exception
Hi,
I really appreciate all the work put into this module. It's been overall easy to use and extremely useful. However, I am finding it difficult to properly error handle, or even make my code error aware when using Invoke-MySQLiteQuery. Not sure if this has been brought up before.
Basically, if I run the command and provide a malformed query, such as one that does not have an escaped ' special character, Invoke-MySQLiteQuery generates a warning/error message, but that warning does not seem to be piped to either STDOUT, or raise any sort of error into STDERR
Given the query saved into a variable named $query:
INSERT INTO company_assets ( id, hostname, fqdn, ipv4_address, os_platform, is_inactive, inactive_reason, segment_status, monitored_by_status, asset_source, asset_type, health_status, health_status_issue, was_asset_connected, date_first_seen, date_last_seen, last_connected_at, last_disconnected_at, segmented_at ) VALUES ( '2dfa2s5fda2', 'exampleserver1', 'exampleserver1.mycompany.com', 'NULL', 'Windows 10 Enterprise', 'True', 'MANUAL', 'Not segmented', 'Can't be monitored (inactive entity)', 'Active directory', 'Client', 'Unknown', 'NULL', 'False', '2024-07-15T11:33:50.2505436-04:00', '2024-07-15T11:33:50.2505678-04:00', 'NULL', 'NULL', 'NULL'
Where the phrase 'Can't be monitored (inactive entity)' is considered malformed, due to an unescaped ' character, executing this query by invoking Invoke-MySQLiteQuery -Path $DatabaseFileName -Query $query -ErrorVariable $sql_err, results in the following being displayed in my PS7 session.
WARNING: SQL logic error WARNING: INSERT INTO zn_assets ( id, hostname, fqdn, ipv4_address, os_platform, is_inactive, inactive_reason, segment_status, monitored_by_status, asset_source, asset_type, health_status, health_status_issue, was_asset_connected, date_first_seen, date_last_seen, last_connected_at, last_disconnected_at, segmented_at ) VALUES ( '2dfa2s5fda2', 'exampleserver1', 'exampleserver1.mycompany.com', 'NULL', 'Windows 10 Enterprise', 'True', 'MANUAL', 'Not segmented', 'Can't be monitored (inactive entity)', 'Active directory', 'Client', 'Unknown', 'NULL', 'False', '2024-07-15T11:33:50.2505436-04:00', '2024-07-15T11:33:50.2505678-04:00', 'NULL', 'NULL', 'NULL' ) WARNING:
Instead of raising an error into the $sql_err variable (configured as the -ErrorVariable), this is just displayed. Additionally, this is not captured even if you save the output of the command into a variable, such as $query_result = Invoke-MySQLiteQuery -Path $DatabaseFileName -Query $query -ErrorVariable $sql_err, making me believe it's not even getting piped into STDOUT. This makes it impossible to handle or even be aware when a query is not executed successfully. I am hoping this can be improved upon as everything else about this module is great. It just needs to be able to raise errors when SQLite does.
Here is the output of $PSVersionTable for reference:
` Name Value
PSVersion 7.4.2 PSEdition Core GitCommitId 7.4.2 OS Microsoft Windows 10.0.22631 Platform Win32NT PSCompatibleVersions {1.0, 2.0, 3.0, 4.0…} PSRemotingProtocolVersion 2.3 SerializationVersion 1.1.0.1 WSManStackVersion 3.0 `
The Catch block uses Write-Warning, which is what I personally prefer. However, it sounds like I should change this to Write-Error. If you got an exception object instead of a warning, would that "solve" your issue?
My code isn't signed, so you could modify the Invoke-mySqliteQuery.ps1 file in the functions folder. Change all occurrences of Write-Warning $_.Exception.message to Write-Error $_.Exception.message. Test in a new session and see if it works better for you.
If I run Invoke-SQLiteQuery with -ErrorVariable the underlying error is captured.
Invoke-MySQLiteQuery -Path C:\temp\inventory.db -Query "Insert Into OS (Computername,OS,InstallDate,Version,IsServer) values ('$Name','Microsoft Windows 11 Pro','$(Get-Date)','11.0.0','0')" -ErrorVariable e
In my example, the $Name variable is malformed.
The error variable contains the error from the function, not SQLite. Although if I dig into the error variable, it looks like it is surfacing the underlying SQLite error.
Remember, the ErrorVariable does NOT use the $.
You are running:
Invoke-MySQLiteQuery -Path $DatabaseFileName -Query $query -ErrorVariable $sql_err
But you should be running:
Invoke-MySQLiteQuery -Path $DatabaseFileName -Query $query -ErrorVariable sql_err
Then, you can reference $sql_err. Sorry I missed that critical detail.
Try again and let me know where we stand with this issue.
There is an Invoke-MySQLiteQuery -WarningAction Stop which should be catchable though that is a bit obtuse for normal usage. dbatools module solves this problem by implementing an -EnableException parameter. It taps into the PSFramework module style of error handling.
I published version 1.0.0 to the PowerShell Gallery with changes to how SQLite errors are handled. I'm sure there's room for improvement. Very receptive to a well-tested PR.