Invoke-SQLCmd considered harmful

Posted by on

I mentioned here that Invoke-SQLCmd (included in the SQLPS module for SQL Server) was susceptible to SQL-Injection attacks, but I haven’t demonstrated that or ever seen anyone show it.

To do so, I’ll start with code out of the help for Invoke-SQLCmd.  Here’s the code (taken from here)

$MyArray = "MyVar1 = 'String1'", "MyVar2 = 'String2'"
Invoke-Sqlcmd -Query "SELECT `$(MyVar1) AS Var1, `$(MyVar2) AS Var2;" -Variable $MyArray

Notice that the parameters are encoded in a string. This is a easy-to-see red flag that SQL-Injection might be possible.  To see the problem, let’s replace ‘String1’ with a SQL statement:

$MyArray = "MyVar1 = (select top 1 name from msdb.sys.databases)", "MyVar2 = 'String2'"
Invoke-Sqlcmd -Query "SELECT `$(MyVar1) AS Var1, `$(MyVar2) AS Var2;" -Variable $MyArray

sqlcmd_injection

So even though our select statement (the -Query parameter to Invoke-SQLCmd) wasn’t looking at any tables, the result we got was a value out of a table. I did have to remove the single quotes, but the command which was in a parameter was executed.

A couple of minor problems with SQLPS which are unrelated to Invoke-SQLCmd but bother me nonetheless are:

  • Encode-SQLName and Decode-SQLName both use unapproved verbs.  Cmdlets with these names were present in the SQLPS Snap-In, so I can understand them being here now.  A better solution would be to rename them (ConvertTo-SQLName/ConvertFrom-SQLName?) and provide aliases with the legacy Encode/Decode names.
  • Importing the module changes the current location.  The module does implement the SQLServer provider and SQLServer: drive, but it should not do a Set-Location when the module is imported.  Especially with automatic importing, this could cause some strange unexpected results if someone isn’t paying attention.

There are a lot of cmdlets in the SQLPS module which are very well implemented, so don’t think that I’m saying you should avoid it altogether.  Also, the parameter interface for Invoke-SQLCmd was implemented the way it was to stay compatible with the command-line tools that ship with SQL Server, so I understand where they are coming from.  I would have preferred that they also provide a solution using real parameters (SQL language parameters, that is).  I would recommend that you look at Invoke-SQLCmd2, Adolib, or POSH_Ado for examples of parameterized implementations.