Protecting Against SQL injection attacks
Understand and avoid SQL injection. Detect existing vulnerabilites to SQL injection attacks.
Contents
- Introduction
- What is SQL injection?
- Why would someone attempt SQL injection?
- The tricks of the SQL injector
- Basic examples of injection attack
- Avoiding SQL injection — the hard way
- Avoiding SQL injection — the easy way
- Finding vulnerabilities
- Database permissions
Introduction
The aim of this document is not to outline all the clever methods a SQL injector can use to take advantage of your vulnerable application, but to:
- Make you aware of the risks.
- Show you how to avoid attacks.
- Show you how to find existing vulnerabilities that a SQL injector could exploit.
What is SQL injection?
SQL injection is a technique in which an attacker inserts malicious code into strings that are later passed to a database for execution. SQL injection exploits applications that generate SQL statements from user input. (For example, from values input in a form on a web site.) The vulnerability is either due to incorrectly filtered input or wrongly typed input, but is always the result of concatenating user input with SQL strings to perform a database action.
Why would someone attempt SQL injection?
SQL injection is used by someone to:
- Obtain information you have in your database.
- Delete or change information in your database.
- Cause denial of service (DoS) by injecting SQL that takes a long time to run and perhaps locks rows in your database.
DoS is any form of attack on a system that tries to prevent legitimate users from accessing it.
The tricks of the SQL injector
Someone attempting SQL injection will use a number of tricks to get past your application and into your database:
- The SQL-92 comment introducer is
--and most databases will ignore anything after--. - The normal quote character is
'. Anyone concatenating user input to form a SQL query will undoubtedly add quotes either side of a value in aWHEREclause. Knowing this, the SQL injector can end the input in a quote and follow it with further SQL. SELECTqueries issued by the application can be modified to add conditional tests in theWHEREclause, which can cause the result set to always be returned or never be returned.- Some databases allow multiple statements to be issued at once, with a
;separating statements.
Combining these tricks can provide easy access to the database through SQL injection, if the application is not well coded. Refer to the next section for examples of injection attacks.
Basic examples of injection attack
Assume the application is vulnerable to SQL injection, as it uses unvalidated user input to form SQL strings. Imagine this application has an email form where users enter their email address, and it needs to check the email is valid before allowing access to other parts of the application. It's likely the SQL executed is:
SELECT email FROM users WHERE email = 'user_input'
where user_input is what you enter in the form.
If we enter someone@somewhere.com' in the form, the resulting SQL is:
SELECT email FROM users WHERE email = 'someone@somewhere.com''
and this is likely to produce a syntax error in the application when the database parses the SQL and objects to the last '. Having tried this, the SQL injector already knows you concatenate strings without validating them and that you are vulnerable.
Now assume the form input is changed to:
someone@somewhere.com or 'x' = 'x
The resulting SQL is:
SELECT email FROM users WHERE email = 'someone@somewhere.com' or 'x' = 'x'
which is guaranteed to return a result set. The application will probably expect either 1 row or no rows as a result of the query, but in this case, will get multiple rows; one for each row in the users table. The typical logic that such an application expects is that so long as a row is returned, the email address must be valid, and hence you gain access.
Another variation on the above is using the SQL comment introducer:
someone@somewhere.com' or 1 = 1--
which results in a similar query:
SELECT email FROM users WHERE email = 'someone@somewhere.com' or 1 = 1--'
where the SQL engine will ignore the quote after -- as it is a comment, and the or 1 = 1 provides the same result as above, that is, all rows in the users table are returned.
Depending on the database, the SQL injector may have to try using " or ' as the quote character, as there is some variation across databases in quote usage.
Making use of multiple statements
Some databases allow multiple statements to be prepared and executed at once with a semicolon as a separator between the statements. Continuing from our example above, with a single SELECT to find an email, we enter:
someone@somewhere.com'; SELECT '1
and the database executes:
SELECT email FROM users WHERE email = 'someone@somewhere.com' SELECT '1'
which will obviously not be what the application expects. A potentially more disastrous input might be:
someone@somewhere.com'; DROP TABLE users;--
which results in two SQL statements being executed:
SELECT email FROM users WHERE email = 'someone@somewhere.com' DROP TABLE users
Now if you have been careless with your database permissions, you have just lost your users table.
Using error messages to obtain database information
Be very careful what error messages you display in your application, as they can provide an easy way for the SQL injector to obtain information about your database.
Assume you show database error messages in your application, you're using Microsoft SQL Server and you have a query like:
SELECT username FROM users WHERE userid = '<user_input>'
The SQL injector enters:
10 UNION SELECT top 1 table_name FROM information_shema.tables--
which results, after string concatenation, with:
SELECT username FROM users WHERE userid = 10 UNION SELECT top 1 table_name FROM information_shema.tables
Microsoft SQL Server will fail to union 10 with the varchar columns in the SELECT statement and output an error like:
Syntax error converting the nvarchar value 'mytable' to a column of data type int
giving away the fact that you have a table called mytable.
You can use similar tricks with Microsoft SQL Server and information_schema.columns to obtain table columns, and most other databases have special tables or views that return table and column names in errors.
Avoiding SQL injection — the hard way
The problem with the above examples is that the application allows unfiltered input into the SQL. The application needs to filter out all characters with special meaning in SQL, like single or double quotes, semi-colons, the comment introducer --, and so on.
But say, for example, a user wants to use one of these characters in a password. Can you be sure you have filtered out all the right characters?
Avoiding SQL injection — the easy way
Never concatenate user input with application SQL to form the SQL sent to the database. The easy way to do this is to use parameterised statements. Parameterised statements are where the variable parts of the SQL are replaced with markers (usually ?). Instead of concatenating the user input for the email address (refer to the earlier examples) like this:
SELECT email FROM users WHERE email = '<user_input>'
we use:
SELECT email FROM users WHERE email = ?
The SQL is prepared when the SQL Engine parses it, validates it, and notes that there is one parameter for the email address. When you execute it, you pass the parameter separately from the SQL. How you do this depends on the language you are using. For ODBC in C, you would do this:
char *user_input; /* points to user input string */ SQLPrepare('SELECT email FROM users WHERE email = ?'); SQLBindParameter(1, user_input); SQLExecute;
A similar example in Perl is:
my $user_input = 'something the user entered'; my $sth = $dbh->prepare('SELECT email FROM users WHERE email = ?'); $sth->bind_param(1, $user_input); $sth->execute;
In Perl, you can combine the last two statements with a single execute like this:
$sth->execute($user_input);
Now it does not matter if a user enters any special SQL characters, because they are never parsed by the SQL engine.
Similar methods for using parameterised SQL are available in PHP, Python, Java, and so on.
Finding vulnerabilities
So what if you already have written your application, and do not know if it's vulnerable to SQL injection? Obviously, you could examine the source code and find any instances of concatenating user input with SQL, but that could be very tedious and time consuming. You could also use the techniques described in the examples above to test your user input fields, but that could also be time consuming.
Although not foolproof (because it relies on what code in your application you can be sure is run), you can enable logging of database queries and examine the logs to see which queries are using parameterised statements with user input and which are not. How you enable query logging depends on the database driver you are using.
ODBC logging
ODBC drivers are run under an ODBC Driver Manager, which lets you log the ODBC APIs called, including the SQL that's executed. (For the unixODBC Driver Manager, refer to tracing ODBC calls; for the Windows Driver Manager, refer to How do I generate a Windows ODBC trace?) However, not all ODBC Driver Managers log all of the SQL executed. Some database drivers (like the ODBC-ODBC Bridge) can provide full logging of all SQL executed on request.
For example, you have an ODBC application that generates SQL from user input and passes it to a back-end database. As you do not have access to the application's source code, you need to enable query logging to find out what SQL the application is sending to the database. To do this, you can install the ODBC-ODBC Bridge (which you can download from this site) on the machine where the application is running. Then create an ODBC-ODBC Bridge data source that points to an ODBC driver data source for the target database. If your application is running on a non-Windows platform, to enable query logging, create a file named odbc.ini in your current working directory. Add these lines:
{Settings}
Logging = 0x100
On Windows, use Registry Editor (regedit.exe) to locate the registry subkey HKEY_LOCAL_MACHINE\SOFTWARE\Easysoft ODBC-ODBC Bridge\Configuration\System\Settings. In this key, set Logging to:
0x100
Test your application. On UNIX and Linux, the SQL that your application generates is logged to /tmp/esoobclient.log_PID. On Windows, the SQL is logged to LogDir/esoobclient.log, where LogDir is the directory specified by the ODBC-ODBC Bridge configuration option LogDir. For more information about ODBC-ODBC Bridge trace logs, refer to Is there any ODBC-ODBC Bridge tracing I can use for debugging my application?
Another way to log SQL if your application is running on Linux or UNIX and your database is SQL Server, is to use our SQL Server ODBC driver. To enable logging, create a SQL Server ODBC driver data source for the target database in /etc/odbc.ini. In this data source, set Logging to Yes and Logfile to /tmp/sqlserver_driver.log. For example:
[MSSQL] Driver = Easysoft ODBC-SQL Server Server = my_machine\myinstance User = my_domain\my_user Password = my_password Database = my_webapp_db # Enable driver logging. Logging = Yes Logfile = /tmp/sqlserver_driver.log
When you run your application, the SQL Server ODBC driver logs output to /tmp/sqlserver_driver.log. Although the log output includes the SQL sent to the database, the log file also contains output generated from other driver actions (unlike the ODBC-ODBC Bridge example).
How you enable driver logging, may vary between drivers. For example, if you want to audit the SQL sent by an Linux application to an Oracle database, you would enable Oracle ODBC driver logging by adding this line to your data source:
Log = /tmp/oracle_driver.log
Because of the performance overhead that logging introduces, remember to turn off ODBC Driver Manager and ODBC driver logging when you have finished auditing your application's SQL.
Database logging
Some databases provide a log of all SQL executed. For example, Apex in Oracle provides a way to view all the SQL executed by a process.
Perl DBI logging with DBIx::Log4perl
If you're using Perl, we can recommend the DBIx::Log4perl module, which you insert between Perl DBI and the DBD driver. Alternatively, you can enable profiling in DBI, which logs all SQL executed (refer to DBI_PROFILE in the DBI documentation).
Database permissions
Some of the most harmful attacks shown in the SQL injection examples are avoidable by using careful database permissions. The example that drops the user table would not succeed if the database user the application was using did not have DROP permission. You should always run your application using a database user with the minimum permissions it needs to perform its functions. It is doubtful that many applications need to drop tables and possible that they only need SELECT access and no UPDATE and DELETE access. The most important rule is do not give an application any database permissions it does not require. It's not a complete solution to SQL injection, but is good practice that makes your application less vulnerable to attacks.
