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 3rd 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 3rd 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)
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 '),
(' tran '),
(' transaction '),
(' update '),
(' while '),
Here is the user defined function T-SQL code:
Create Function dbo.VerifySQLInjection (@TSQL varchar(max))
DECLARE @IsSQLInjectionSuspected bit;
DECLARE @pos int;
set @pos = 0;
select @pos += charIndex (lower(word) , lower(@TSQL))
set @IsSQLInjectionSuspected = if ((@pos > 0) ,1 ,0)
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:
|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(‘exec sp_helpdb’)||1|
View all posts by Eli Leiba
Latest posts by Eli Leiba (see all)
- Creating a stored procedure to fix orphaned database users - January 25, 2016
- Creating a gap in sequences – TSQL Stored Procedure advisor - January 6, 2016
- Construct a special multi-statement table function for checking SQL Server’s health - December 24, 2015