Prevent single quotes from breaking sql command

Question

I have a powershell module that builds a string out of several variables in order to insert into sql server.

$QueryInsert =
"
insert into dbo.Table
(
    data
)
values
(
    '$data'
)
"

Invoke-Sqlcmd -ServerInstance 'server_name' `
        -Database db_name `
        -Query $QueryInsert `
        -QueryTimeout 20 `
        -OutputSqlErrors $True `
        -ConnectionTimeout 5 `
        -ErrorAction Continue

The problem with this methodology is that it's not particularly safe, and will fail if the variable $data contains any single quotes. Is there a way to prevent this? Is there a way to paramaterize this to keep it safe?

Stored procedure? Escape the single quote? Set quoted_identifier? — dfundako, Mar 29 '18 at 19:30
use brackets around the variable - that should escape any value with single quotes — Daniel Marcus, Mar 29 '18 at 19:35
Invoke-SqlCmd doesn't seem to have a good way to handle this. See http://www.sqlservercentral.com/blogs/cjsommercom/2015/10/13/running-parameterized-queries-against-sql-server-using-powershell/ You can always wrap your query into a straight SqlCommand object from System.Data.SqlClient which can be parameterized. Look at responses to this question for ideas: https://stackoverflow.com/questions/8423541/how-do-you-run-a-sql-server-query-from-powershell — RnP, Mar 29 '18 at 19:49
You could have PowerShell escape any single quotes that aren't already escaped (or escape them all if you want). `'$($data -replace "(?<!')'(?!')","''")'` — TheMadTechnician, Mar 29 '18 at 21:52
@TheMadTechnician I'd post that as an answer. If you don't have access to create a stored procedure (like in Andy Schneider's answer) then this would be my first solution. — Jonathan Leech-Pepin, Mar 29 '18 at 23:22
@DanielMarcus i tried that, and it fails if there's an odd number of single quotes in the string — DForck42, Mar 30 '18 at 13:10

Andy Schneider · Accepted Answer · 2018-03-29T20:30:50.123

If you have access to the database, you can write the query as a stored procedure and then call the stored procedure with PowerShell. You can pass parameters to a stored procedure in a safe manner that would not allow injecting code through a string like you can with the code above.

You might want to have a look at this question to see how to write a stored procedure and also this one.

In order to call a SPROC from PowerShell, you can use code similar to this.

    $sql='NameOfSprocBeingCalled'
    $sqlConnection = new-object System.Data.SqlClient.SqlConnection
    $sqlConnection.ConnectionString = $SqlConnectionString
    $sqlConnection.Open()

    $sqlCommand = new-object System.Data.SqlClient.SqlCommand
    $sqlCommand.Connection = $sqlConnection
    $sqlCommand.CommandText= $sql
    $sqlCommand.CommandType = [System.Data.CommandType]::StoredProcedure
    $sqlCommand.Parameters.Add("@Param1",[system.data.SqlDbType]::VarChar).value =$Param1
    $sqlCommand.Parameters.Add("@Param2",[system.data.SqlDbType]::VarChar).value =  $EventType


    $Datatable = New-Object System.Data.DataTable
    $DataReader = $sqlCommand.ExecuteReader()
    $Datatable.Load($DataReader)

    $sqlConnection.Close()

You just need to make sure you pass in the right type for parameters, using [System.Data.SqlDbType]::

This is an enum with the following types available:

# [enum]::GetValues([System.Data.SqlDbType])
BigInt
Binary
Bit
Char
DateTime
Decimal
Float
Image
Int
Money
NChar
NText
NVarChar
Real
UniqueIdentifier
SmallDateTime
SmallInt
SmallMoney
Text
Timestamp
TinyInt
VarBinary
VarChar
Variant
Xml
Udt
Structured
Date
Time
DateTime2

i know how to write a stored procedure. do you have an example of a powershell script using a stored procedure and passing in parameters in a safe manner? — DForck42, Mar 29 '18 at 20:02

Prevent single quotes from breaking sql command

1 Answers1