Revisiting running SQL queries from Powershell
In a prior article, I wrote about how SQL injection attacks could be a problem when dynamically creating your SQL code from Powershell. See: https://robshort.hashnode.dev/powershell-vs-sql-injection-attacks
However, some people asked how would I tackle this when I need to authenticate to the SQL server since my SQL function doesn't include any authentication capabilities. In my particular use case, the service user account that was running Powershell was set up with permissions within the SQL server to allow for it to do exactly what it needed, but alas there are differing use cases where being able to pass credentials to the function would be more appropriate. At the end of this article, I'm going to provide an update to my invoke-sql function, but I also want to provide readers with an alternative should they have a use case that isn't applicable to my function.
In cases where my simple function just isn't good enough, the best course of action is to use the Powershell functions built into the SQLServer module that is maintained by Microsoft. You can grab this module with this command (assuming you have Nuget setup):
install-module sqlserver
You can then call the invoke-sqlcmd function similarly to the invoke-sql function that I presented in my prior article, but the way to pass in SQL parameters is very different.
Let's examine the following code:
$cmd=@"
select * from [database].dbo.table1
where column1 =`$(var1) and column2=`$(var2)
offset (`$(skip)) rows fetch next (`$(first)) rows only
"@
$sqlparam=@(
"var1='String1'",
"var2=$var2",
"skip=$skip",
"first=$first"
)
$dataset=invoke-sqlcmd -query $cmd -serverinstance $ds -username $un -password $pw -variable $sqlparam
The first thing we need to look at is how to format the SQL code. The Invoke-SQLcmd function looks for the name of the variable inside $(), but since $ is a special character in Powershell we need an escape character (the backtick) before the $ or it would treat it not as part of a string, but instead attempt to evaluate the expression.
Next, let's examine the parameters we want to pass the SQL command. Invoke-SQLcmd looks for an array of strings. So the first string in our array passes 'String1' as the var1 variable. The contents of $var2 get passed as var2 and so forth for skip and first.
While it doesn't look much different, if you are working with a particularly complex SQL query the parentheticals can get confusing, but for overly complex queries you would probably be better off building a view in SQL first and then calling that instead of direct table access.
Now some of you might say, I like the '?' nomenclature of your prior example much better than the Microsoft way of doing things. How can I pass credentials using your function? Or some people don't want to use the Microsoft module because it doesn't just add one function, it adds a whole bunch. And then some people just want to be able to fiddle with the insides of the function which you can't easily do with the Microsoft function. So how to add authentication? The secret there is in how we create the connection string.
If a user passes credential info we need to change the connection string to not use Windows authentication and pass the credentials, like this:
if($null -eq $username -or $null -eq $password){
write-host 'no usernname supplied'
$connectionString = "Data Source=$dataSource; " +
"Integrated Security=SSPI; " +
"Initial Catalog=$database"
}
else {
write-host "connecting as $username"
$connectionString = "Data Source=$dataSource; " +
"Initial Catalog=$database;" +
"User Id=$username;" +
"Password=$password"
}
Then I've added the parameters to the function and updated the list of parameters and the updated function is now complete.
function invoke-sql{
<#
.SYNOPSIS
Runs a SQL query
.DESCRIPTION
Connection variables and a SQL string, optionally an array of parameters are passed to the function, which then connects to the designated SQL instance and performs the query.
Because a dynamically generated SQL statement is NOT secure and vulnerable to SQL injection attacks, unless otherwise handled, use the parameterized option for passing variables.
.PARAMETER Datasource
A string of the datasource, AKA the server name. Required. Defaults to the first position
.PARAMETER Database
A string of the database name. Required. Defaults to the second position
.PARAMETER SQLCommand
A string of the query to run. Required. Defaults to the third position. When used as a parameterized query the paramters should be replaced with question marks.
.PARAMETER Parameters
An array of variables that the function will parameterize for the SQL server. Optional. Defaults to fourth position
.PARAMETER username
If both username and password are supplied the function will use them to connect to the SQL server rather than using Windows authentication
.PARAMETER password
If both username and password are supplied the function will use them to connect to the SQL server rather than using Windows authentication
.EXAMPLE
$sqlcommand="Select * from table"
invoke-sql localhost $database $sqlcommand
.EXAMPLE
$sqlcommand= "Select * from table where field=? or field =?"
$params=@("value1","value2")
invoke-sql localhost $database $sqlcommand $params
.EXAMPLE
$sqlcommand= "Select * from table where field=? or field =?"
$params=@("value1","value2")
invoke-sql localhost $database $sqlcommand $params -username $un -password $pw
#>
[CmdletBinding()]
param(
[parameter(position=0, mandatory=$true)]
[string]$datasource,
[parameter(position=1, mandatory=$true)]
[string]$database,
[parameter(position=2, mandatory=$true)]
[string] $sqlCommand,
[parameter(position=3)]
[array]$parameters,
[parameter()]
[string]$username,
[parameter()]
[string]$password
)
if($null -eq $username -or $null -eq $password){
write-host 'no usernname supplied'
$connectionString = "Data Source=$dataSource; " +
"Integrated Security=SSPI; " +
"Initial Catalog=$database"
}
else {
write-host "connecting as $username"
$connectionString = "Data Source=$dataSource; " +
"Initial Catalog=$database;" +
"User Id=$username;" +
"Password=$password"
}
$connection = new-object system.data.SqlClient.SQLConnection($connectionString)
if($null -eq $parameters){
$command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
}
else {
#the user has sent a parameterized query. Search the query for the character ? and replace with a @a# where # is the number of ? found.
$mymatches=$([regex]::matches($sqlcommand,'\?'))|sort index -desc #we want to start at the end of the string so as we adjust the string count it doesn't break things
if($parameters.Length -ne $mymatches.length){throw "Parameter counts do not match."}
$counter=$mymatches.length
foreach ($match in $mymatches){
$sqlcommand=$sqlcommand.substring(0,$match.index) +"@a$counter"+ $sqlcommand.substring($match.index+1)
$counter--
}
$counter++
$command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
foreach ($p in $parameters){
$command.Parameters.AddWithValue("@a$counter",$p)|out-null
$counter++
}
}
$connection.Open()
$adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
$dataset = New-Object System.Data.DataSet
$adapter.Fill($dataSet) | Out-Null
$connection.Close()
$dataSet.tables
}
Bonus content for anyone who scrolled this far. If you want to test to see what context you are running a SQL query in, the SQL command to see what user you are is:
select user_name()