Escaping strings containing single quotes in PowerShell ready for SQL query

You can try to update your code to to use a parametrised value that will cope with quotes in a string:

$query = "INSERT INTO People(name) VALUES(@name)"

$command = $connection.CreateCommand()
$command.CommandText = $query
$command.Parameters.Add("@name", $name)  -- | Out-Null (may be required on the end)
$command.ExecuteNonQuery()

I'm not experienced with powershell but referenced this post for a parametrised query: