SQL Server 2005 : Dynamic T-SQL - Supporting Optional Parameters (part 3) - SQL Injection

SQL Injection

Concatenating string parameters such as @NationalIdNumber directly onto queries can open your applications to considerable problems. The issue is a hacking technique called a SQL injection attack, which involves passing bits of semiformed SQL into textboxes in order to try to manipulate dynamic or ad hoc SQL on the other side.

The example GetEmployeeData stored procedure doesn't actually have much of a problem as-is, because @NationalIdNumber is defined as only 15 characters—this doesn't give a hacker much room to work with. But what if you were working with another stored procedure that had to be a bit more flexible? The following example procedure, which might be used to search for addresses in the AdventureWorks database, gives an attacker more than enough characters:

CREATE PROCEDURE FindAddressByString
    @String NVARCHAR(60)
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @sql NVARCHAR(MAX)

    SET @sql = '' +
        'SELECT AddressId ' +
        'FROM Person.Address ' +
        'WHERE AddressLine1 LIKE ''%' + @String + '%'''

    EXEC(@sql)
END

This stored procedure can be executed with a string such as "Stone" used for the parameter value:

EXEC FindAddressByString
    @String = 'Stone'

This outputs the result set you might expect, with all of the address IDs that use that string in the AddressLine1 column. That output is shown in Figure 6.

Output of searching addresses for the string "Stone"

Consider what actually happened inside of the stored procedure. The WHERE clause for the query was concatenated, such that it literally became WHERE AddressLine1 LIKE '%Stone%'. But nothing is stopping someone from passing a string into the stored procedure that has a more profound effect. For instance, consider what happens in the following case, the output of which is shown in Figure 7:

EXEC FindAddressByString
    @String = ''' ORDER BY AddressId --'

After concatenation, the WHERE clause reads WHERE AddressLine1 LIKE '%' ORDER BY AddressId --%'. An ORDER BY clause—which was not there before—has been added to the query, and the %' at the end of the query has been commented out so that it will have no effect.

An injected ORDER BY clause ordered the results.

This is, of course, a fairly mundane example. How about something a bit more interesting, such as getting back the full pay history for every employee in the database?

EXEC FindAddressByString
    @String = '''; SELECT * FROM HumanResources.EmployeePayHistory --'

Assuming that the account used for the query has access to the HumanResources.EmployeePayHistory table, running the stored procedure produces the output shown in Figure 8. The fact is the attacker can do anything in the database that the authenticated account has access to do and that can be done in 60 characters (the size of the string parameter). This includes viewing data, deleting data, or inserting fake data. Such an attack can often be waged from the comfort of a web browser, and intrusion can be incredibly difficult to detect.

An attacker can use SQL injection to do anything that the authenticated account has access to do.

The solution is not to stop using dynamic SQL. Rather, it's to make sure that your dynamic SQL is always parameterized. Let me repeat that for effect: Always, always, always parameterize your dynamic SQL! The next section shows you how to use sp_executesql to do just that.