We all know that using string concatenation (or substitution) in SQL is a “bad thing”.  If you google “SQL Injection”, you’ll find hundreds of pages that tell you not to do things this way and that you need to use parameterized queries.  However, I still see a lot of code in PowerShell that does this:

$cmd.ExecuteNonQuery("delete from Table1 where Column1='$value'")

Since code like this is obviously prone to SQL injection attacks, it must be that doing it the right way is difficult, right?  Actually, no.  Here’s a simple function that allows you to run parameterized queries easily using dictionaries.

function exec-query( $sql,$parameters=@{},$conn,$timeout=30,[switch]$help){
 if ($help){
 $msg = @"
Execute a sql statement.  Parameters are allowed.
Input parameters should be a dictionary of parameter names and values.
Return value will usually be a list of datarows.
"@
 Write-Host $msg
 return
 }
 $cmd=new-object system.Data.SqlClient.SqlCommand($sql,$conn)
 $cmd.CommandTimeout=$timeout
 foreach($p in $parameters.Keys){
 [Void] $cmd.Parameters.AddWithValue("@$p",$parameters[$p])
 }
 $ds=New-Object system.Data.DataSet
 $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
 $da.fill($ds) | Out-Null

 return $ds
}

Example usage:

$conn=new-object data.sqlclient.sqlconnection "Server=servername;Integrated Security=True"
$conn.open()
exec-query 'select * from sys.databases' -conn $conn
exec-query 'select FirstName,LastName from AdventureWorks2008.Person.Person where FirstName=@fname' -parameter @{fname='Mike'} -conn $conn

As you can see from the second example, using a parameterized function wasn’t much harder than it would have been to do string concatenation or substitution.  And you don’t have to worry about little Bobby Tables.

The code above still needs some work to take care of output parameters, but I’ll save that for another post.

Let me know if you have any questions, comments, or complaints.

Mike