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
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.