Question:
I am trying to run the following query, which takes someone’s name and attempts to insert it into an SQL Server database table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
$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 the single quote is causing an issue in my query. The query is being executed as
|
1 2 |
INSERT INTO People(name) VALUES('Ronnie O'Sullivan') |
which causes an SQL syntax error.
My question is how do I escape my $name variable so that it renders on the SQL side.
One solution is to do a find and replace on my $name variable, find: ‘ replace: ”
|
1 2 |
$name.Replace("'", "''") |
Is there a more elegant solution out there, or a function that I can’t seem to find?
Thank you.
Answer:
You can try to update your code to to use a parametrised value that will cope with quotes in a string:
|
1 2 3 4 5 6 7 |
$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: