Creating a SQL Injection protection function

The Problem

The Problem demonstrated here describes a very common scenario. The IT Security group orders all programmers that all the dynamic input strings that comes from user input to be checked for suspicious SQL injection intentions.

SQL injection is a code injection technique used to attack data-driven applications. During the attack, malicious SQL statements are inserted into data entry fields for execution inside the database engine.

SQL injection is a common attack method on websites and can be used to attack any type of SQL database.

The damages that SQL injection causes are many and include, among others:

Disclosure user’s identity and password
Tampering with existing data
Interfering with the system’s transactions like changing account balances
Disclosure of all data on the system
Destruction of the data
Making the data unavailable
Taking control of the database server by getting administrative privileges

The great importance for defending against SQL injection attacks was emphasized by a study done in 2012. The observation was that the average web application received 4 SQL injection attacks per month.

There are many 3^rd party web application firewall (WAF) tools that checks for SQL injection. The solution demonstrated in this article is an applicative one and does not involve any 3^rd party SQL inspection tool.

The programmers have to apply the function for each input, immediately after getting the input parameter and halt execution if the function result indicates SQL injection suspicious input string.

The suggested solution

The suggested solution presented here involves creating a user defined T-SQL scalar function that checks the input string for any suspicious key words that might indicate the SQL injection intents.

The function checks the input string against a set of pre-defined keywords that are known to be used in SQL injection cases.

The list of keywords is stored inside a special, dedicated table so that any addition of any other keywords and thus strengthening the security protection power of the function by simply adding rows to that table.

Here is the list of suspicious words that I have entered along with an explanation of what makes them suspicious for SQL injection purposes.

The initial words selected here comes from my personal experience in the subject and include data modification keywords, SQL data definition language commands, stored procedures and extended stored procedure common prefixes, remark and concatenation signs, control flow keywords , transaction control keywords, data set functions, batch control keywords , server control commands and so on.

The words are ordered alphabetically.

Here is my suggestion for the initial words list

The Word	The Reason why it is suspicious as SQL injection

Alter	Attempt to change database structure is not allowed
Begin	Begin of TSQL block inside dynamic T-SQL is not allowed
Break	Usage of control flow inside dynamic SQL is not allowed
Checkpoint	Attempt to control transaction behavior is not allowed
Commit	Attempt to control transaction behavior is not allowed
Create	Attempt to change database structure is not allowed
Cursor	Attempt to use cursors within Dynamic T-SQL execution is not allowed
DBCC	Using administrative Database consistency checker commands (DBCC) is not allowed
Deny	Attempt to change any database permissions within Dynamic T-SQL not allowed
Drop	Attempt to change database structure is not allowed
Exec	Attempt to execute within Dynamic T-SQL not allowed
Execute	Attempt to execute within Dynamic T-SQL not allowed
Insert	Attempt to change the existing data within dynamic T-SQL Is not allowed
Go	Attempt for multiple batch scripts within Dynamic T-SQL not allowed
Grant	Attempt to change any database permissions within Dynamic T-SQL not allowed
Opendatasource	Usage of distributed transaction row set function within Dynamic T-SQL not allowed
Openquery	Usage of distributed transaction row set function within Dynamic T-SQL not allowed
Openrowset	Usage of distributed transaction row set function within Dynamic T-SQL not allowed
Shutdown	Attempt to control server behavior is strictly not allowed
Sp_	Any attempt to execute a system stored procedure within Dynamic T-SQL not allowed
Tran	Attempt to control transaction behavior is not allowed
Transaction	Attempt to control transaction behavior is not allowed
Update	Attempt to change the existing data within dynamic T-SQL is not allowed
While	Usage of control flow inside dynamic SQL is not allowed
;	Any attempt to concatenate T-SQL commands within a single T-SQL command is not allowed
—	Entering comment marks inside with dynamic T-SQL command is a common SQL injection technique and is not allowed
Xp_	Any attempt to execute an extended and/or system stored procedure within Dynamic T-SQL not allowed

Here is the Script for the table creation in master database (I called the table ReservedWords)

use master

Create Table ReservedWords

( id int identity not null PRIMARY KEY, word varchar(20))

Insert Into ReservedWords

values (' alter '),

(' begin '),

(' break '),

(' checkpoint '),

(' commit '),

(' create '),

(' cursor '),

(' dbcc '),

(' deny '),

(' drop '),

(' escape '),

(' exec '),

(' execute '),

(' insert '),

(' go '),

(' grant '),

(' opendatasource '),

(' openquery '),

(' openrowset '),

(' shutdown '),

(' sp_'),

(' tran '),

(' transaction '),

(' update '),

(' while '),

(' xp_'),

(';'),

('--')

Here is the user defined function T-SQL code:

use master

Create Function dbo.VerifySQLInjection (@TSQL varchar(max))

Returns bit

BEGIN

DECLARE @IsSQLInjectionSuspected bit;

DECLARE @pos int;

set @pos = 0;

select @pos += charIndex (lower(word) , lower(@TSQL))

from ReservedWords

set @IsSQLInjectionSuspected = if ((@pos > 0) ,1 ,0)

RETURN @IsSQLInjectionSuspected

END

Explanation for the code:

I called the function VerifySQLInjection. It gets a varchar(max) Dynamic T-SQL string to be inspected for SQL injection.

The value returned is bit. 0 for a non-suspicious SQL injected string and 1 for a suspected one. The function uses the charIndex T-SQL built in function in order to check all the reserved words list inside the searched TSQL string.

If one or more word is found inside the string, the charIndex function will return a positive value (the position inside the string) making the result a positive , greater than zero result.

It the result of all charindex inspections turns out to be greater than zero then the function returns 1 and otherwise 0.

Here are some Tests for the function execution:

Statement	Result

SELECT master.dbo.VerifySQLInjection(‘select productname from Northwind.dbo.products’)	0
SELECT master.dbo.VerifySQLInjection(‘alter table Northwind.dbo.products alter column photo varbinary(max)’)	1
SELECT master.dbo.VerifySQLInjection(‘;shutdown’)	1
SELECT master.dbo.VerifySQLInjection(‘exec sp_helpdb’)	1

Author
Recent Posts

Eli Leiba

Eli Leiba is a senior application DBA at Israel Electric Company, a teacher at Microsoft CTEC, and senior database consultant with 24 years of experience working with both SQL Server and Oracle RDBMS. He is certified in Oracle and SQL Server database administration and implementation and has a B.S. in Computer Science.He can be reached at: iecdba@hotmail.com

View all posts by Eli Leiba

The Problem

The suggested solution

Related posts: