Help with formatting Powershell & MySQL statement

PowerShell Help
1

I am righting to an existing MySQL DB (SnipeIT) and I am having issues getting the formatting correct with my last statement. I have to follow the format that is already in the DB.

The DB field is log_meta with the following:

{“notes”:{“old”:null,“new”:“Testing notes in DB”}}

I already know that I need to escape some of these characters. Here is the PowerShell

$cmd = $conn.CreateCommand()
$cmd.CommandText = "INSERT INTO action_logs (user_id,action_type,note,item_type,item_id,created_at,updated_at,company_id,log_meta,action_date) VALUES ('1238','update','Audit completed $date,'Apps\Models\Apps','$asset_id','$date','$date','`{`"notes`":`{`"old`":$null,`"new`":`"Audit completed $date`"`}`}',$date')"
$reader = $cmd.ExecuteNonQuery()

However, I get the following MySQL/PowerShell error:

ERROR: Exception calling “ExecuteNonQuery” with “0” argument(s): “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server
ERROR: version for the right syntax to use near ‘Apps\Models\Apps’,‘1’,‘2023-03-03 09:40:07’,‘2023-03-03 09:40:07’,‘{“notes”:{"ol’ at line 1”

I am confuse on how to make this string PowerShell and MySQL friendly.

2

I was over complicating this. Here is the working solution:

$cmd = $conn.CreateCommand()
$cmd.CommandText = "INSERT INTO snipeit.action_logs (user_id, action_type, note, item_type, item_id, created_at, updated_at, company_id, log_meta, action_date) VALUES ('1238', 'update', 'Audit completed $date', 'App\\Models\\Asset', '1', '$date', '$date', '1', '{`"notes`":{`"old`":null,`"new`":`"Audit Completed $date`"}}', '$date')"
$reader = $cmd.ExecuteNonQuery()
3

Glad you figured it out, just wanted to provide a tip to use SQL variables at the top of your SQL cmd and leverage here-string to make it formatted and clean. You can also just get the SQL variable (e.g. $sqlCmd) and paste it right into a SQL tool (e.g. SQL Mgmt Studio, Toad, etc.) for troubleshooting the command:

$cmd = $conn.CreateCommand()
$sqlCmd = @"
DECLARE @currDate DATETIME;
SET @currDate = GETDATE();
DECLARE @DateFromPS DATETIME = $($myDateVariableFromPowershell)

INSERT INTO snipeit.action_logs 
    (user_id, 
    action_type, 
    note, item_type, 
    item_id, 
    created_at, 
    updated_at, 
    company_id, 
    log_meta, 
    action_date)
VALUES 
    ('1238', 
     'update', 
     'Audit completed @currdate', 
     'App\\Models\\Asset', 
     '1', 
     '@currdate', 
     '@currdate', 
     '1', 
     '{`"notes`":{`"old`":null,`"new`":`"Audit Completed @currdate`"}}', 
     '@currdate');
"@

$cmd.CommandText = $sqlCmd
$reader = $cmd.ExecuteNonQuery()