Escaping strings containing single quotes in PowerShell ready for SQL query

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.

$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.

Can't you use quotename in your query? I.e.: INSERT INTO People(name) VALUES(QUOTENAME($name)) — David Brabant, Feb 20 '14 at 14:53

Tanner · Accepted Answer · 2020-08-29T00:20:10.940

17

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:

edited Aug 29 '20 at 00:20

answered Feb 20 '14 at 14:55

Tanner

21,369
9
63
79

4

+1 *Always* use prepared statements (or parametrized queries, as Microsoft calls them). – Ansgar Wiechers Feb 20 '14 at 15:06
And how exactly should one do this when building a 1,000 line insert statement? – Pxtl Jun 20 '19 at 18:19
@Pxtl I'd start by looking at why you have a 1000 line insert statement... – Tanner Jun 21 '19 at 11:49
@Tanner ETL and I don't hate myself enough to use SSIS. – Pxtl Jun 22 '19 at 04:27
@Pxtl You build a datatable, put it in a dataadapter, and tell it to run 1,000 inserts. Wrap the whole thing in a transaction and it will perform just fine. – Bacon Bits Aug 29 '20 at 01:00

score 1 · Answer 2 · answered Oct 20 '20 at 22:32

Tanner's helpful answer is definitely the most robust and secure solution, because using a [parameterized / prepared statement (query) eliminates any possibility of a SQL injection attack.

However, in this constrained case, where you want to insert a value into a single-quoted SQL string ('...'), you can get away with simply doubling any embedded ' characters in the value:

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

The above uses PowerShell's string interpolation via $(...), the subexpression operator, to embed an expression that uses the -replace operator to double all embedded ' instances in the value of $name.

Note: You could also use $name.Replace("'", "''") above, which performs better in this simple case, but PowerShell's -replace operator is generally preferable, not only for being PowerShell-native, but for offering superior abilities, because it is regex-based and supports array as its LHS - see this comment on GitHub.

Escaping strings containing single quotes in PowerShell ready for SQL query

2 Answers2

Linked

Related