Constructing Dynamic SQL with Parameters

When building dynamic SQL, safety is crucial. As we saw last week, we have the QUOTENAME function which can help when referencing object names.

Another aspect to consider is use of parameters. Integrating them incorrectly can leave us vulnerable to SQL injection attacks. Let’s take a look at how to handle them the wrong way, followed by the right way.

The issue

We’ll start with the problem that we can run into when concatenating without any safety measures in place. Here’s an example in a procedure:

CREATE OR ALTER PROC dbo.GetCustomer (
	@FirstName VARCHAR(20),
	@Surname VARCHAR(50)
) AS
BEGIN

	DECLARE @Sql NVARCHAR(MAX);

	SELECT @Sql = 'SELECT CustomerID
		FROM dbo.Customers
		WHERE FirstName = ''' + @FirstName + '''
			AND Surname = ''' + @Surname + '''';

	EXEC sp_executesql @Sql;

END

Using typical parameters all would be well:

EXEC dbo.GetCustomer
	@FirstName = 'Andy',
	@Surname = 'B';

However we could throw something a little more poisonous into the mix:

EXEC dbo.GetCustomer
	@FirstName = 'Andy',
	@Surname = 'B'' OR 1 = ''1';

This would return all of the customers in our data as it would render our query as:

...
WHERE FirstName = 'Andy'
	AND Surname = 'B'
    OR 1 = '1';

This is another example of SQL injection. It’s not good.

So we parameterise

Whilst we can call sp_executesql to simply execute a SQL command, it can take parameters like our procedure does.

We don’t need to worry about crafting this whole query ourselves and trying to safeguard against rogue values manually.

By using parameters for the query we’re building, we can simply pass the existing variables through and the engine will handle those for us.

Let’s look at the modified procedure:

CREATE OR ALTER PROC dbo.GetCustomer (
	@FirstName VARCHAR(20),
	@Surname VARCHAR(50)
) AS
BEGIN

	DECLARE @Sql NVARCHAR(MAX);

	SELECT @Sql = 'SELECT CustomerID
		FROM dbo.Customers
		WHERE FirstName = @First
			AND Surname = @Last';

	EXEC sp_executesql
		@stmt = @Sql,
		@params = N'@First VARCHAR(20), @Last VARCHAR(50)',
		@First = @FirstName,
		@Last = @Surname;

END

You’ll see we’ve needed to change both the construction of the query as well as its execution.

Building the query is much simpler as we don’t need to concatenate any strings at all. We’ve instead used variables specific to that query. We define these as part of the execution.

The call to sp_executesql is changed to not only provide the statement as before, but some additional parameters. The call uses the @params parameter to list the variables used and their corresponding data types, and then followed by listing each parameter declared.

With that change in place, our poisonous parameters would now effectively parse as:

...
WHERE FirstName = 'Andy'
	AND Surname = 'B'' OR 1 = ''1';

This would mean that instead of returning all of the records from the table we’d likely return nothing.

Wrap up

In this shorter post we’ve looked at how to parameterise dynamic SQL queries to build safer code.

We’ve demonstrated this with a simple example to show what a bad implementation looks like and and how straightforward it is to fix the issue with small modifications to the construction and execution of the query.

By using parameters within our dynamic SQL as opposed to concatenating it ourselves, we can let the engine take care of the variables and we can produce cleaner and safer code.