Executing SQL the Right Way in PowerShell

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

Share

4 Comments

  1. Awesome. I’m writing a report against a sql database and this is exactly what I was looking for. My first approach was exactly what you described – totally vulnerable and not what I wanted to use at all. Thanks for the post.

    One question about the code – when you say

    if ($close) {
    $conn.Close()
    }

    Where does $close come from? Should this be $conn?

    • Glad you like it. I’m writing a lot less stored procedures now that I have this in my arsenal.

      As far as the $close…That’s not supposed to be there. This code was a simplified extract of a much larger toolset that I wrote to handle SQL queries. I usually don’t like to keep connections open unless I’m actively using transactions (which I don’t tend to need very often) or if I’m running a lot of SQL Statements (like thousands). Because of that, when I get a connection, I usually “decorate” them with a .Close property to tell the SQL functions to open/close the connection before and after the access.

      In a similar vein, the “handlereturn” function that is missing is something that I wrote to handle the multiple kinds of “return values” that can come back from a query:

      Multiple selected results
      Return codes
      Out-parameters (my favorite)

      I should probably start running my code examples in a sandbox. :-)

  2. Pingback: SQL PowerShell Extensions (SQLPSX) 2.0 Released « PowerShell Station

  3. Pingback: SQLPSX Developer Blog | Sev17

Comments are closed.