Avoiding SQL Injection

I’ve recently seen some funny SQL injection jokes (included at the end of this post) so I thought now would be a good time to go through a few examples of SQL injection and how it can be avoided.

What is SQL Injection?

SQL injection occurs when malicious code is included in a SQL statement to manipulate the results. This can result in someone obtaining unauthorized data, altering data, or removing data. I’ve most often seen this around dynamic SQL so that’s the type of example we’ll look at today.

Breaking the Things

We’ll start out with some test data:

CREATE TABLE Account (
	Id INT identity(1, 1)
	,LastName NVARCHAR(100)
	,Username NVARCHAR(100)
	,Password NVARCHAR(100)
	,SSN NVARCHAR(15)
	)

INSERT INTO Account
VALUES      ( 'Callihan','ccallihan01','29jRVecX','000-00-0000' ),
            ( 'Faye','afaye123','nZRb53Y2','000-00-0000' ),
            ( 'Anne','manne3','9Nw34vJH','000-00-0000' ),
            ( 'Wayne','bwayne','imbatman','000-00-0000' )

And add a stored procedure to return account information based on the username. I included “PRINT @SQL” for when we want to see what statement is actually being executed.

CREATE PROCEDURE [dbo].[usp_GetAccountByUsername]
    @Username NVARCHAR(100) = NULL
AS
BEGIN
    DECLARE @SQL NVARCHAR(1000);

    SET @SQL = N'SELECT * FROM Account WHERE Username = ''' + @Username + '''';

    PRINT @SQL;

    EXEC (@SQL);
END

Let’s see what kind of trouble we can get into with that procedure. If we run it as intended, we’ll get the results for one account. Running the procedure for my account returns:

Here is where the maliciousness can come into play. What if we add an “OR 1=1” to try viewing more data?

Ah, well maybe we’re OK because we got this error about the unclosed quotation mark. What if we add a couple dashes to comment out that extra single quote?

Oh no…that’s not good. And it can get worse! Additional statements altogether can be executed. I wonder if we can find all tables?

Yep! Updating data to reset all passwords? That works, too. If we run the following and then check our Account table:

Perhaps even scarier is the ability for data to be dropped altogether. If the following runs to drop the Account table then not even our original stored procedure is going to work because that data is outta here:

Be Better

How can we reduce the risk of this problem? Instead of just blindly running the string that is entered with EXEC, we can use sp_executesql instead to parameterize the statement. Let’s add a new, safer stored procedure to get our information:

CREATE PROCEDURE [dbo].[usp_SafelyGetAccountByUsername]
    @Username NVARCHAR(100) = NULL
AS
BEGIN
    DECLARE @SQL NVARCHAR(1000);

    SET @SQL = N'SELECT * FROM Account WHERE Username = @Username';

    PRINT @SQL;

    EXEC sp_executesql @SQL, N'@Username NVARCHAR(100)', @Username;
END

Now we’ll test by recreating our Account table from above and searching a username: