Protecting Against SQL Injection Attacks
Understand and avoid SQL injection. Detect existing vulnerabilites to SQL injection attacks.
The object 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 formulate SQL statements from user input (e.g., from values input in a form on a web site). The vulnerability is due to either 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:
The tricks of the SQL injector
Someone attempting SQL injection will use a number of tricks to get past your application and into the 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 a
WHERE clause. Knowing this, the SQL injector can end the input in a quote and follow it with further SQL.
- Select queries issued by the application can be modified to add conditional tests in the
WHERE clause, 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 via SQL injection, if the application is not well coded. See the next section for examples of injection attacks.
Basic examples of injection attacks
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 is 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
firstname.lastname@example.org' in the form, the resulting SQL is:
select email from users where email = 'email@example.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:
firstname.lastname@example.org or 'x' = 'x
The resulting SQL is:
select email from users where email = 'email@example.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:
firstname.lastname@example.org' or 1 = 1--
which results in a similar query:
select email from users where email = 'email@example.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, i.e., all rows in the users table are returned.
Depending on the database, the SQL injector may have to try using
' 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:
firstname.lastname@example.org'; select '1
and the database executes:
select email from users where email = 'email@example.com'
which will obviously not be what the application expects. A potentially more disastrous input might be:
firstname.lastname@example.org'; drop table users;--
which results in two SQL statements being executed:
select email from users where email = 'email@example.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 are using MS 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
MS 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
You can use similar tricks with MS SQL Server and
information_schema.columns to obtain table columns, and most other databases have special tables/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
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 (see examples above) like this:
select email from users where email = '<user_input>'
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. In ODBC in C, you would do this:
char *user_input; /* points to user input string */
SQLPrepare('select email from users where email = ?');
A similar example in Perl is:
my $user_input = 'something the user entered';
my $sth = $dbh->prepare('select email from users where email = ?');
In Perl, you can combine the last two statements with a single execute like this:
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 etc.
So what if you already have written your application, and do not know if it is 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 drivers are run under an ODBC driver manager, which allows the logging of the ODBC APIs called, including the SQL that is executed. (For the unixODBC driver manager, see Tracing ODBC Calls; for the Windows driver manager, see 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 a black box ODBC application that generates SQL from user input and passes it to a backend database. As you do not have access to the application’s source code, you need to enable query logging to see 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:
Logging = 0x100
On Windows, use Registry Editor (regedit.exe) to locate the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Easysoft ODBC-ODBC Bridge\Configuration\System\Settings. In this key, set Logging to:
Test your application. On UNIX, 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, see Is there any tracing in OOB I can use for debugging my application?
Another way to log SQL if your application is running on Linux/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
/tmp/sqlserver_driver.log. For example:
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, SQL Server ODBC driver driver output will be logged 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 wanted 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 tracing introduces, remember to turn off driver manager/driver tracing when you have finished auditing your application’s SQL.
Some databases provide a log of all SQL executed e.g., Apex in Oracle provides a way to view all the SQL executed by process.
Perl DBI Logging with DBIx::Log4perl
If you are using Perl, we can recommend the DBIx::Log4perl module, which can easily be inserted between Perl DBI and the DBD driver (see the Debugging Perl DBI article.) An alternative to this is enabling profiling in DBI, which will log all SQL executed (see DBI_PROFILE in the DBI pod).
Some of the most harmful attacks shown in the SQL injection examples are avoidable by using careful database permissions. For example, 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
delete access. The most important rule is do not give a database user permissions it does not require. It is not a solution to SQL injection, but is good practise that makes your application less vulnerable to attacks.
Appendix A: Resources