SQL Injection Attack

[Frinavale]

SQL Injection Attack
A database is a collection of information organised in such a way that allows computer programs to access data (even large amounts) quickly and easily. Data within a database is organised into tables, which contain records/rows of fields. A field contains the actual data used by the program.

Relational Database Management Systems (RDBMS or sometimes just DBMS) allow users the ability to access and manipulate data within databases. They are also used to run reporting and analysis tools. System administrators use them to secure and maintain databases. Some common RDBMSs include MySQL, Oracle, and MS-SQL.

SQL stands for Structured Query Language (generally pronounced as Sequel). It is the language used to issue commands to an RDBMS in order to retrieve or manipulate data. These commands can also be issued to manipulate the database structure itself. A SQL Injection attack exploits security vulnerabilities within the parts of an application's code responsible for taking input from a user and sending it, as part of a SQL string, to the RDBMS.

In order to store or retrieve information from the database, an application may dynamically create a SQL string to issue as a query. The SQL string is compiled by the RDBMS and then executed as a command. For example, the SQL string "SELECT * FROM AddressBook WHERE FirstName = 'John'" will result in the RDBMS returning all of the records from the table named AddressBook that have a FirstName field with the value of 'John'.

It may be that a first name is requested from the user, which is then added into the string to create the full SQL string required. EG. If the user is prompted for a first name which is then put into the string, the string is built up as :

"SELECT * FROM AddressBook WHERE [FirstName]='" + {first name from user here} + "'"

A SQL Injection attack occurs when a user enters SQL commands into an application instead of the intended value. For example, in this case, when the program prompts the user to enter a value for the first name, they might enter something like :

a'; DELETE * FROM [OverduePayments] WHERE [AccountNo]='MyAccountNo';

This might ensure that a banking system forgets all your overdue mortgage payments. When the application creates the SQL string to access the database based on the user's input, the user's SQL commands are injected along with it. The RDBMS then compiles this resultant SQL string into a command and executes it. This results in the malicious user's command being executed along with the legitimate application command, allowing the malicious user unfettered access to the database.

In order to protect your applications against this form of attack, applications should implement strong validation on all user input before any queries are executed. Also, any data that may be used in SQL query creation should be stripped of harmful SQL commands. It is only necessary to remove any quotes from the data to strip user input of harmful SQL commands. Literal text values (What is required from the user in this example) are surrounded by quotes when used in a SQL string. Generally, this refers only to single-quotes ('), but most versions of Access will work with double-quotes (") just as happily. It is therefore advisable to remove all quotes from the input before attempting to use it to form the SQL string. Without a quote in the input, it will all be treated as data and is therefore quite safe.

Another means to protect against this type of attack is by utilizing stored procedures. Stored procedures are precompiled SQL commands that reside in the RDBMS. When a SQL query is provided by a program to the RDBMS in the form of a SQL string, the SQL is first compiled by the RDBMS into a command before it is executed. Stored procedures are already compiled, therefore they use fewer resources; but more importantly, the injected SQL will not be compiled to become part of the SQL instruction (It remains in the SQL, but just as data). Stored procedures also provide extra security, in that the database user can be further restricted to utilizing specific stored procedures.

Chapple, M. (2007). SQL Server Stored Procedures.

[NeoPa]

A more flexible approach, rather than removing all quotes from entered strings, is to double them up.
The example entered above :

a'; DELETE * FROM [OverduePayments] WHERE [AccountNo]='MyAccountNo';

would be converted to :

a''; DELETE * FROM [OverduePayments] WHERE [AccountNo]=''MyAccountNo'';

The intention is still clear, but it is rendered quite harmless by this technique.

[NeoPa]

A VBA function (MS Access specific) for handling this would be :

Option Compare Database
Option Explicit
 
Public Function SafeSQL(strArg As String) As String
    SafeSQL = Replace(strArg, "'", "''")
End Function

This would be incorporated into a Standard (not class of any form) Module.
In the code to create the SQL string you might use something like :

If DCount(Expr:="*", _
          Domain:="[tblSecurity]", _
          Criteria:="[UserName]='" & SafeSQL(Me.User) & "' AND " & _
                    "[Password]='" & SafeSQL(Me.Password) & "'") > 0 Then
...