7

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.

Danny Cullen
  • 1,782
  • 5
  • 30
  • 47

2 Answers2

19

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:

Tanner
  • 22,205
  • 9
  • 65
  • 83
  • 5
    +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
  • Good advice, but I think the syntax of the `Parameters.Add()` call (which you've taken from the linked answer) won't work. [The docs](https://learn.microsoft.com/en-US/dotnet/api/System.Data.SqlClient.SqlParameterCollection.Add) suggest that you must use something like: `$command.Parameters.Add([System.Data.SqlClient.SqlParameter]::new("name", $name))` – mklement0 Dec 15 '22 at 20:12
1

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.

mklement0
  • 382,024
  • 64
  • 607
  • 775