Eli Leiba

Creating a SQL Injection protection function

December 9, 2015 by

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:

  1. Disclosure user’s identity and password
  2. Tampering with existing data
  3. Interfering with the system’s transactions like changing account balances
  4. Disclosure of all data on the system
  5. Destruction of the data
  6. Making the data unavailable
  7. 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)

Here is the user defined function T-SQL code:

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

Eli Leiba

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
Eli Leiba
Security

About 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

1,424 Views