By: Tim Smith | Updated: 2019-04-22 | Comments | Related: > PowerShell
Problem
Since SQL Server stored procedures can involve multiple parameters, mapping and validating values passed to these parameters in PowerShell is an additional technique we can use with the .NET library. We can limit ranges and values with PowerShell parameters and we still want to use procedure parameterization as much as possible, not only for security, but also for ensuring that we have compatible data types and that erroneous records are not passed into the procedure.
Solution
In this part, we'll use the same T-SQL code, which is below, that we used in getting started with PowerShell and stored procedures.
CREATE DATABASE GenExAll
GO
USE GenExAll
GO
CREATE TABLE tbProcExample(
Id SMALLINT,
IdVar VARCHAR(4)
)
GO
CREATE TABLE tbLogging_tbProcExample(
LogDate DATETIME
)
GO
INSERT INTO tbProcExample
VALUES (1,'V-1')
, (2,'V-2')
, (3,'V-6')
, (4,'V-8')
, (5,'V-90')
GO
---Report
CREATE PROCEDURE stpGetData
WITH ENCRYPTION
AS
BEGIN
SELECT Id, IdVar FROM tbProcExample
END
GO
---Change or add
CREATE PROCEDURE stpChangeAdd @id SMALLINT, @data VARCHAR(4)
WITH ENCRYPTION
AS
BEGIN
DECLARE @max SMALLINT
IF EXISTS (SELECT * FROM tbProcExample WHERE Id = @id)
BEGIN
UPDATE tbProcExample SET IdVar = @data WHERE Id = @id
END
ELSE
BEGIN
INSERT INTO tbProcExample VALUES (@id,@data)
END
END
GO
---Permissions
CREATE PROCEDURE stpMaxIdCheck
WITH ENCRYPTION
AS
BEGIN
SELECT MAX(Id) MaxId, 1 AS MinId FROM tbProcExample
INSERT INTO tbLogging_tbProcExample VALUES (GETDATE())
END
GO
USE [master]
GO
CREATE LOGIN [DeveloperOne] WITH PASSWORD = 'Lookyall,thisisnotagoodpassword,sodontuseit.Mmkay?'
GO
USE [GenExAll]
GO
CREATE USER [DeveloperOne] FROM LOGIN [DeveloperOne]
GRANT EXECUTE ON stpMaxIdCheck TO [DeveloperOne]
When we receive user input that gets passed into our procedure, we've looked at the following:
- Validating the parameter on the function level before the parameter is passed into the stored procedure by using the limitations, we've defined in our business rules. For an example, if the maximum value is 10 and the minimum value is 1, we can use a [ValidateRange(1,10)][int] to prevent outside values.
- Handling user input in the function itself, such as adding a converted numerical value to a string and using the string for the parameter. While there will always be string input we have to accept relative to our data, if we can limit this, we should take the opportunity to do so.
These can help us strictly limit user input, in the same manner that defining our data through business rules that limit some parameters can help us. We also have the option to add to our security by using SqlParameter objects.
First, we'll review our function for where we want the parameter objects to be added.
Function Execute-Procedure {
Param(
[Parameter(Mandatory=$true)][int]$id
, [Parameter(Mandatory=$true)][ValidateRange(1,8)][int]$idvarno
)
Process
{
$scon = New-Object System.Data.SqlClient.SqlConnection
$scon.ConnectionString = "Data Source=OurServer;Initial Catalog=OurDatabase;Integrated Security=true"
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.Connection = $scon
$cmd.CommandTimeout = 0
$idvar = ("V-" + $idvarno.ToString())
### Tie parameters using SqlParameters
$cmd.CommandText = "EXEC stpChangeAdd $id,'$idvar'"
try
{
$scon.Open()
$cmd.ExecuteNonQuery() | Out-Null
}
catch [Exception]
{
Write-Warning $_.Exception.Message
}
finally
{
$scon.Dispose()
$cmd.Dispose()
}
}
}
When we call a stored procedure with parameters, the T-SQL equivalent to what we're doing is the following:
EXEC stpChangeAdd @id = 8, @data = 'V-8' --- Check: SELECT * FROM tbProcExample WHERE Id = 8
We're tying the value to a specific parameter and we can even invert that stored procedure call above this by putting the @data variable before the @id variable, such as EXEC stpChangeAdd @data = 'V-8', @id = 8 (note that if we do not explicitly state the parameter name, such as EXEC stpChangeAdd 'V-8',8, it will throw an error because without the parameter names specified, it will default to the parameter order of the stored procedure).
In our above PowerShell code, we're only passing in the parameters by order without tying them to a specific parameter. We can add further validation here by using the SqlParameter object. We'll take the same function as above and add lines to tie our parameters passed in from our function to parameters in our stored procedure.
In the below PowerShell code, we've added four lines - two lines are creating two objects of SqlParameter types - $sqlParam1 and $sqlParam2. Notice that we specifically tie the appropriate PowerShell variables to the appropriate stored procedure parameters - $id is tied to @id and $idvar is tied to @data. After we set our CommandText property to use the stored procedure variable names of @id and @data, we then call our add method on our command object to add the SqlParameters we created.
In the first image below, we see output (the second image confirms that the Id of 9 successfully passed to the database).
Function Execute-Procedure {
Param(
[Parameter(Mandatory=$true)][int]$id
, [Parameter(Mandatory=$true)][ValidateRange(1,8)][int]$idvarno
)
Process
{
$scon = New-Object System.Data.SqlClient.SqlConnection
$scon.ConnectionString = "Data Source=OurServer;Initial Catalog=OurDatabase;Integrated Security=true"
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.Connection = $scon
$cmd.CommandTimeout = 0
$idvar = ("V-" + $idvarno.ToString())
$sqlParam1 = New-Object System.Data.SqlClient.SqlParameter("@id",$id)
$sqlParam2 = New-Object System.Data.SqlClient.SqlParameter("@data",$idvar)
$cmd.CommandText = "EXEC stpChangeAdd @id,@data"
$cmd.Parameters.Add($sqlParam1)
$cmd.Parameters.Add($sqlParam2)
try
{
$scon.Open()
$cmd.ExecuteNonQuery() | Out-Null
}
catch [Exception]
{
Write-Warning $_.Exception.Message
}
finally
{
$scon.Dispose()
$cmd.Dispose()
}
}
}
Execute-Procedure -id 9 -idvarno 1
--- Check: SELECT * FROM tbProcExample WHERE Id = 9
The output we see from the PowerShell function call comes from adding our parameters (we will later call the | Out-Null so that this does not return). We can see the information provided from this output that tells us about our database data types. For an example, we the ParameterName of @id is a DbType of Int32 and a SqlDbType of Int and the Direction is an input with the SqlValue of 9 to a column that is false for IsNullable. We can also see the data information for our @data parameter.
Sometimes, we get clashes with data types - such as numbers that are out of range (think of a SQL tinyint with a maximum value of 255, but passing in a .NET Int32 of 256 - a valid value for .NET, but for the T-SQL limitation of the column. This output can help us when debugging so that we make sure we're adding and setting the appropriate parameter values both on the stored procedure side that's accepting input and the function side that's accepting and passing its input. This also means we want to make sure that our rules are defined strict so that when we map our Powershell parameters to our stored procedure parameters, they are as strictly defined to limit possible errors and security attacks.
In the above function, I ordered the SqlParameter objects as we see them ordered in the stored procedure, but we can invert the order (see the below code). Even if I place the SqlParameter of $sqlParam2 first (the @data parameter in the procedure) it is still mapped to the appropriate variable, just like $sqlParam1 is. On this call, we've also removed the output that we received when we called the PowerShell function last time - when we call our method to add parameters to our command, we eliminate the output (| Out-Null).
Function Execute-Procedure {
Param(
[Parameter(Mandatory=$true)][int]$id
, [Parameter(Mandatory=$true)][ValidateRange(1,8)][int]$idvarno
)
Process
{
$scon = New-Object System.Data.SqlClient.SqlConnection
$scon.ConnectionString = "Data Source=OurServer;Initial Catalog=OurDatabase;Integrated Security=true"
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.Connection = $scon
$cmd.CommandTimeout = 0
$idvar = ("V-" + $idvarno.ToString())
$sqlParam2 = New-Object System.Data.SqlClient.SqlParameter("@data",$idvar)
$sqlParam1 = New-Object System.Data.SqlClient.SqlParameter("@id",$id)
$cmd.CommandText = "EXEC stpChangeAdd @id,@data"
$cmd.Parameters.Add($sqlParam2) | Out-Null
$cmd.Parameters.Add($sqlParam1) | Out-Null
try
{
$scon.Open()
$cmd.ExecuteNonQuery() | Out-Null
}
catch [Exception]
{
Write-Warning $_.Exception.Message
}
finally
{
$scon.Dispose()
$cmd.Dispose()
}
}
}
Execute-Procedure -id 10 -idvarno 5
--- Check: SELECT * FROM tbProcExample WHERE Id = 10
Finally, our use with the SqlParameters object in PowerShell .NET will also be compatible with using the sp_executesql function in SQL Server, which tends to be used frequently in dynamic or strict security environments. This function uses parameterization by executing code and requiring the parameters specifically defined following the code (below we see the specific data definitions of the parameters). At the end, the parameters are added in the call before the function is executed.
In the below code, we execute our same stored procedure, but now we're using the sp_executesql function its parameterization as well as the .NET parameterization.
Function Execute-Procedure {
Param(
[Parameter(Mandatory=$true)][int]$id
, [Parameter(Mandatory=$true)][ValidateRange(1,8)][int]$idvarno
)
Process
{
$scon = New-Object System.Data.SqlClient.SqlConnection
$scon.ConnectionString = "Data Source=OurServer;Initial Catalog=OurDatabase;Integrated Security=true"
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.Connection = $scon
$cmd.CommandTimeout = 0
$idvar = ("V-" + $idvarno.ToString())
$sqlParam2 = New-Object System.Data.SqlClient.SqlParameter("@data",$idvar)
$sqlParam1 = New-Object System.Data.SqlClient.SqlParameter("@id",$id)
$cmd.CommandText = "EXEC sp_executesql N'EXEC stpChangeAdd @id,@data',N'@id SMALLINT, @data VARCHAR(4)',@id,@data"
$cmd.Parameters.Add($sqlParam2) | Out-Null
$cmd.Parameters.Add($sqlParam1) | Out-Null
try
{
$scon.Open()
$cmd.ExecuteNonQuery() | Out-Null
}
catch [Exception]
{
Write-Warning $_.Exception.Message
}
finally
{
$scon.Dispose()
$cmd.Dispose()
}
}
}
Execute-Procedure -id 11 -idvarno 2
--- Check: SELECT * FROM tbProcExample WHERE Id = 11
As we see, in addition to mapping the parameters to ensure that correct data is passed, this helps enforce the input that the PowerShell function is passing to the stored procedure. We could rely only on strict PowerShell parameters, but oversights and data injection through manipulation happen. The above parameterization adds strict and thorough review to what is being passed into a stored procedure. Just like we're being strict with what is passed to a stored procedure, we should also be strict with the account executing the procedure.
Next Steps
- In most cases, it's a best practice to validate input with several layers, not only to prevent errors, but also as a security practice. As we see in this tip, we can strictly define input on the PowerShell, procedure, and parameter mapping level.
- For mapping parameters that come from user input in a stored procedure to a stored procedure's parameters, we can use the SqlParameter object in PowerShell and call the add method to our command object.
- We can use the SqlParameter object in .NET with the sp_executesql function in SQL Server as well. For dynamic code, using this combination is a recommended practice along with internal validation in functions.
- This tip involves using PowerShell version 5.1; to determine your version, type $PSVersionTable and review the PSVersion property.
About the author
Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2019-04-22
