CODEWITHSUNDEEP
sql-serverpowershell
DForck42
DForck42

Reputation: 20357

Prevent single quotes from breaking sql command

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?

Upvotes: 1

Views: 740

Answers (1)

Andy Schneider
Andy Schneider

Reputation: 8684

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

Upvotes: 4

Related Questions