For many years I didn’t really think about the implications of how I was retrieving data from my SQL Servers in PowerShell. I was just happy that I was able to retrieve the data! As I learned more about SQL Server I started to think of things like SQL injection and using parameterized queries to promote plan reuse.
I went back and looked at some of the old PowerShell scripts that I had written and found that I was way off! Most of the old scripts would be prime candidates for SQL Injection. I wasn’t really concerned early on because I was the one running my scripts and passing in the parameters, but as my scripts became more automated and database driven, they became more vulnerable to SQL Injection. And parameterized queries? What the heck is a parameterized query? I had no clue when I first started out.
This blog post is about how I changed my thinking and my methods.
I am running PowerShell 4.0 along with SQL Server 2012 on my laptop, so nothing special there really. Each test script is a PowerShell function that performs a SELECT statement against [person].[person] table in the AdventureWorks2012 database. The function also accepts a parameter for ‘LastName’ which is used in the search predicate. Pretty straight forward and very common. You’ll see the actual scripts below in all of the code snippets.
There is also a table in the AdventureWorks2012 database called DeleteMe. DeleteMe is used as the target of my SQL Injection attacks. The SQL Injection attack I try in all 3 examples is to try and delete that table.
Ultimately I wanted to find a method that protects me from SQL injection and promotes plan reuse by using parameteried queries. Enough about that, lets see the fun stuff!
function Select-UnparameterizedSQLPS
{
[cmdletbinding()]
param (
[string]$LastName
)
Push-Location; Import-Module SQLPS -DisableNameChecking; Pop-Location
$sql1 = "
SELECT [BusinessEntityID]
,[PersonType]
,[NameStyle]
,[Title]
,[FirstName]
,[MiddleName]
,[LastName]
,[Suffix]
,[EmailPromotion]
,[AdditionalContactInfo]
,[Demographics]
,[rowguid]
,[ModifiedDate]
FROM [AdventureWorks2012].[Person].[Person]
WHERE [LastName] = '${LastName}'
"
$sql1
Invoke-Sqlcmd -ServerInstance 'localhost\inst1' -Database 'AdventureWorks2012' -Query $sql1
}
Clear-Host
$result = Select-UnparameterizedSQLPS -LastName "Duffy' ; DROP TABLE [DeleteMe] ;--"
$result | Format-Table -AutoSize
The SQL Query:
It is clear from the SQL output what this was going to do, and sure enough, the DeleteMe table is now gone. Not only is this query vulnerable to SQL injection, but it also does not promote plan reuse because the T-SQL includes the literal value in the search predicate.
function Select-ParameterizedSQLPS
{
[cmdletbinding()]
param (
[string]$LastName
)
Push-Location; Import-Module SQLPS -DisableNameChecking; Pop-Location
$sql1 = "N'SELECT [BusinessEntityID]
,[PersonType]
,[NameStyle]
,[Title]
,[FirstName]
,[MiddleName]
,[LastName]
,[Suffix]
,[EmailPromotion]
,[AdditionalContactInfo]
,[Demographics]
,[rowguid]
,[ModifiedDate]
FROM [AdventureWorks2012].[Person].[Person]
WHERE [LastName] = @LastName'"
$sql1
$Params = "N'@LastName VARCHAR(50)'"
$Query = "EXECUTE sp_executesql @stmt = $sql1, @params = $Params, @LastName = $LastName ;"
Invoke-Sqlcmd -ServerInstance 'localhost\inst1' -Database 'AdventureWorks2012' -Query $Query
}
Clear-Host
$result = Select-ParameterizedSQLPS -LastName "'Duffy' ; DROP TABLE [DeleteMe] ;--"
$result | Format-Table -AutoSize
The SQL Query:
It was not clear from the raw T-SQL output what this query was going to do because it was parameterized. This was just one version of how I tried to use sp_executesql to get around the injection attack, but as I found out there really is no good way to do it using Invoke-Sqlcmd. After running this script my DeleteMe table was gone. This appears to be a huge limitation in Invoke-Sqlcmd for running ad-hoc queries.
function Select-ParameterizedADOLib
{
[cmdletbinding()]
param (
[string]$LastName
)
Import-Module adoLib
$sql1 = 'SELECT [BusinessEntityID]
,[PersonType]
,[NameStyle]
,[Title]
,[FirstName]
,[MiddleName]
,[LastName]
,[Suffix]
,[EmailPromotion]
,[AdditionalContactInfo]
,[Demographics]
,[rowguid]
,[ModifiedDate]
FROM [AdventureWorks2012].[Person].[Person]
WHERE [LastName] = @LastName'
$sql1
$params = @{'LastName'=$LastName}
$conn = New-Connection 'localhost\inst1' -database 'AdventureWorks2012'
Invoke-Query -connection $conn -sql $sql1 -parameters $params
}
Clear-Host
$result = Select-ParameterizedADOLib -LastName "Duffy' ; DROP TABLE [DeleteMe] ;--"
$result | Format-Table -AutoSize
The SQL Query:
Once again it isn’t really clear what would happen based on the raw T-SQL that was output from the function, but after running the query I did find that my DeleteMe table remained intact. This implementation protected me from that SQL injection and because it is a parameterized query it also helps with plan reuse.
You won’t start out knowing the best methods for accomplishing a task and that’s OK. Don’t let that stop you from picking up the keyboard and banging out some code. Just understand that you will find better ways of doing things over time, and that’s a good thing! Embrace change and use that additional knowledge to become better at what you do.