Failing on inserting text containing '

[martincaddick]

I've got text similar to this in a json file that I am importing where it contains the single quotes.

"Item": "Restrict user ability to access groups features in My Groups. Group and User Admin will have read-only access when the value of this setting is 'Yes'.",

Adding to the database I get this error

WARNING: SQL logic error WARNING: Insert into database Values ('Self Service Group Management', '', '', '"General"', 'configuration\entra-id\groups\general.md', '10', 'https://entra.microsoft.com/#view/Microsoft_AAD_IAM/GroupsManagementMenuBlade/~/General', 'Restrict user ability to access groups features in My Groups. Group and User Admin will have read-only access when the value of this setting is 'e[7mYese[0;93m'.', 'No')

[jdhitsolutions]

I run into problems with quotes often. That is a SQL issue more than anything. I would hesitate to modify the query. But I could try to perform some type of validation check and present a warning.

[martincaddick]

Dodgy workaround Jeff, if it works it's ok isn't it ;-)?

$jsonData | ForEach-Object -Begin { $cx = Open-MySQLiteDB $dbpath } -Process { try { $item = $.Item -replace "'", "''" $value = $.Value -replace "'", "''" $query = "INSERT INTO asd VALUES ( '$($.H3)', '$($.H4)', '$($.H5)', '$($.Title)', '$($.Filename)', '$($.Weight)', '$($.Url)', '$item', '$value' )" Invoke-MySQLiteQuery -Connection $cx -KeepAlive -Query $query } catch { Write-Host "Error: $" } } -End { Close-MySQLiteDB $cx }

[jdhitsolutions]

I think it has to be up to the user to validate and adjust what they are storing in the database. In a simple example, say I want to store this value in a table.

$Name= "Alice'sPC"
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')"

This will fail, as you've seen before.

But I can escape the quotes.

$Name=  $name.Replace("'","''")

And now the query works.

PS C:\> Invoke-MySQLiteQuery -Path C:\temp\inventory.db -Query "Select * from OS where computername='$Name'"

Computername : Alice'sPC
OS           : Microsoft Windows 11 Pro
InstallDate  : 08/21/2024 14:55:17
Version      : 11.0.0
IsServer     : 0

I am hesitant to taking responsibility for validating data. I would prefer that the user handle this task. I am sure I can improve documentation if that would help.