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
– David Brabant, Commented Feb 20, 2014 at 14:53

Tanner · Accepted Answer · 2020-08-29 00:20:10Z

18

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, 2020 at 0:20

answered Feb 20, 2014 at 14:55

Tanner

22.8k9 gold badges70 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Ansgar Wiechers Over a year ago

+1 Always use prepared statements (or parametrized queries, as Microsoft calls them).

Pxtl Over a year ago

And how exactly should one do this when building a 1,000 line insert statement?

Tanner Over a year ago

@Pxtl I'd start by looking at why you have a 1000 line insert statement...

Pxtl Over a year ago

@Tanner ETL and I don't hate myself enough to use SSIS.

Bacon Bits Over a year ago

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

|

mklement0 · Accepted Answer · 2020-10-20 22:32:31Z

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.

Collectives™ on Stack Overflow

Escaping strings containing single quotes in PowerShell ready for SQL query

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related