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:

That looks like what we would expect. Let’s try accessing more data and then dropping the Account table again:

Results doesn’t show any data. What does the messages tab show?

We see @Username for both SELECT statements which is what we want. Did any changes get made?

Nope, our data remains safely intact. No unauthorized data was able to be selected and the data was not modified or dropped. Isn’t that a relief?

Oh, So You Got Jokes

Both found on r/ProgrammerHumor:

Thanks for reading!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s