Escaping single-quote strings in PowerShell, ready for SQL query

Question

I am trying to run the following query, which takes a name and tries to insert it into a SQL Server database table.

$name = "Ronnie O'Sullivan"

$dataSource = "127.0.0.1"
$database = "Danny"
$connectionString = "Server=$dataSource;Database=$database;Integrated Security=True;"

$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()

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

$command = $connection.CreateCommand()
$command.CommandText = $query
$command.ExecuteNonQuery()

$connection.Close()

The problem I am facing is that a single quote causes a problem in my request. The request is executed as

INSERT INTO People(name) VALUES('Ronnie O'Sullivan')

which is causing the SQL syntax error.

My question is how can I avoid the $ name variable so that it appears on the SQL side.

One solution is to search and replace the variable my name, find: 'replace:' '

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

Is there a more elegant solution out there or a feature that I cannot find?

Thanks.

Answer 1

, , :

$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()

powershell, :