By: Tim Wiseman | Updated: 2020-01-07 | Comments (10) | Related: 1 | 2 | > Stored Procedures
Problem
One of the benefits of SQL is the ability to write a query and use parameters to dynamically act upon the resultset. Depending on the situation, there can be benefits to parameterizing queries, but it is not always clear when or how to do this. In this tip we look at different ways to pass in values as parameters to queries and the advantages and disadvantages.
Solution
Properly parameterizing queries can bring advantages such as:
- Encouraging execution plan reuse for complex queries.
- Providing some protection against SQL Injection Attacks under some circumstances
Generally, when creating a condition in a query where you might use one of several values, it makes sense to parameterize. But, as will be discussed later in this tip, there are cases where the query cannot be fully parameterized.
Parameterizing a Query By Making It a Stored Procedure
If you want to find the sales data for Jack, you could start with a non-parameterized query that just pulls up that data:
select SalesPerson, Mon, amount from SalesData where SalesPerson = 'Jack';
Under some circumstances, SQL Server can attempt to parameterize this behind the scenes to facilitate execution plan reuse, but its ability to do that can be limited. If you want to use this query repeatedly to get the data for different sales people, you could instead parameterize the query and turn it into a stored procedure like:
create procedure getSalesperson @sp varchar(25) as select SalesPerson, Mon, amount from SalesData where SalesPerson = @sp; Go
And it could be called like:
declare @sp varchar(25) set @sp = 'Jack' exec getSalesperson @sp
Doing it this way explicitly tells SQL Server what the parameters are, which makes query execution plan reuse more likely. It also ensures that the salesperson value is handled in a way that is normally safer and makes SQL Injection Attacks through this procedure more difficult.
This is substantially different from a stored procedure that builds the query through concatenation like:
create procedure getSalesperson_bad @sp varchar(25) as declare @sql varchar(max) set @sql = ' select SalesPerson, Mon, amount from SalesData where SalesPerson = ''' + @sp + ''';' exec (@sql) Go
This second version builds a non-parameterized query using dynamic sql. It is simple to exploit a procedure like this in a SQL Injection Attack. It also does not explicitly tell SQL Server where the parameters are.
Parameterizing in T-SQL with sp_executesql
Another direct way to parameterize a query in T-SQL is to use sp_executesql and explicitly add your parameters. It looks like:
declare @sql nvarchar(4000) declare @monthNo int declare @minAmount decimal set @sql = N' select SalesPerson from dbo.SalesData where mon = @MonthNo and amount > @minAmount' set @monthNo = 2 set @minAmount = 100 exec sp_executesql @sql, N'@monthNo int, @minAmount decimal', @monthNo, @minAmount
With sp_executesql the first parameter is the SQL code to be executed, the second lists the parameters that will be supplied and indicates whether they are output variables, and then the actual parameters are passed into the procedure. Both the SQL statement and the list of parameters must be presented in unicode (nvarchar, nchar, or a string prefixed by N like the parameter list in the example.)
Parameterizing in SQL from other languages
Languages that interact with SQL tend to make it simple to parameterize. To parameterize a SqlCommand put the names of the parameters in the CommandText and then use Parameters.Add to add parameters that match the name to the command before executing. It looks like:
using System;
using System.Text;
using System.Data.Sql;
using System.Data.SqlClient;
namespace ConsoleApplication2
{
class Program
{
static void Main(string[] args)
{
using (SqlConnection conn = new SqlConnection("YourConnectionString"))
{
conn.Open();
string sqlCom = "select SalesPerson from dbo.SalesData where mon = @monthNo and amount > @minAmount";
SqlCommand cmd = new SqlCommand(sqlCom, conn);
cmd.Parameters.Add("@monthNo", System.Data.SqlDbType.Int);
cmd.Parameters["@monthNo"].Value = 2;
cmd.Parameters.Add("@minAmount", System.Data.SqlDbType.Decimal);
cmd.Parameters["@minAmount"].Value = 100.00;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("SalesPerson = {0}", reader[0]);
}
reader.Close();
}
}
}
}
A Python example using pyodbc would look like:
import pyodbc
connString = (r"YourConnString")
conn = pyodbc.connect(connString)
curs = conn.cursor()
sql = """select SalesPerson
from dbo.SalesData
where mon = ? and amount > ?"""
curs.execute(sql, (2, 100.00))
for result in curs:
print result
When the query cannot be (fully) parameterized
Parameterization brings several benefits, including some protection against SQL injection attacks under some circumstances. But there are certain types of dynamic queries that cannot be fully parameterized. For instance, SQL Server will not accept a table name or a column name as a parameter. If you tried to do it with sp_executesql, like:
declare @sql nvarchar(4000) declare @colName nvarchar(256) set @sql = N' select @colName from dbo.SalesData where mon = 2 and amount > 100.0' set @colName = 'SalesPerson' exec sp_executesql @sql, N'@colName nvarchar(256)', @colName
The server merely returns a result set of "SalesPerson". Trying to use a parameter for a tablename in a query causes the server to try to interpret the parameter as a table variable and gives an error like: "Msg 1087, Level 16, State 1, Line 3 Must declare the table variable "@tableName"."
So a procedure meant to run against an arbitrary table would need to actually build the SQL command by constructing the string. Other parts of that query could still be parameterized of course. A simplified example could look like:
set @tableName = 'SalesData' set @monthNo = 2 set @sql = N' select SalesPerson from ' + @tableName + ' where mon = @monthNo' exec sp_executesql @sql, N'@monthNo int', @monthNo
However, building the string that way can make SQL Injection attacks simpler, especially if the user is directly prompted to supply the table or column names. Depending on the expected use cases, it may be wise to perform some string validation before execution. Ensuring the application runs with the minimal necessary access to SQL Server can help mitigate that risk to a degree.
Summary
In general, properly applied parameterization can assist in security for SQL Server and can have performance implications. But, some queries cannot be fully parameterized such as when the column names, table names, or other clauses need to be added or modified dynamically. When non-parameterized dynamic SQL is used, the performance and security implications should be kept in mind.
Next Steps
- To find more on parameterizing in the context of dynamic SQL, look at Erland Sommarskog's The Curse and Blessings of Dynamic SQL
- SQL Injection: Defense in Depth has more on using parameterization as one layer of defense against some SQL injection attacks.
- Ad Hoc Queries Don't Reuse Execution Plans: Myth or Fact has more on execution plan reuse
- SQL Server Simple and Forced Parameterization has more information on how to encourage SQL Server to reuse query execution plans without explicitly parameterizing the queries yourself.
About the author
Tim Wiseman is a SQL Server DBA and Python Developer in Las Vegas.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2020-01-07
