Escaping strings containing single quotes in PowerShell ready for SQL query
powershell, sql, sql-server
Solution
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:
Problem
I am trying to run the following query, which takes someone's name and attempts to insert it into an 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 the single quote is causing an issue in my query. The query is being executed as ``` 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: '' ``` $name.Replace("'", "''") ``` Is there a more elegant solution out there, or a function that I can't seem to find? Thank you.