When an attacker has an
exploitable SQL injection vulnerability, he can take one of two primary
exploit paths. He can go after the application data itself, which
depending on the application and the data could be very lucrative. This
is especially true if the application handles and insecurely stores
personally identifiable information or financial data, such as bank
account and credit card information. Alternatively, the attacker may be
interested in leveraging the database server to penetrate internal,
trusted networks. In this section, we’re going to look at ways to limit
unauthorized access to application data. Then we’ll look at some
techniques for hardening the database server to help prevent privilege
escalation and limiting access to server resources outside the context
of the target database server. You should fully test the steps we’ll be
covering in a non-production environment first, to avoid breaking the
functionality of existing applications. New applications have the
benefit of building these recommendations into the development life
cycle early to avoid dependencies on unnecessary and privileged
functionality.
Locking Down the Application Data
Let’s first
examine some techniques restricting the scope of an SQL injection attack
to the application database only. We’re also going to look at ways to
restrict access even if the attacker has been successfully sandboxed to
the application database.
Use the Least-Privileged Database Login
Applications should connect
to the database server in the context of a login that has permissions
for performing required application tasks only. This critical defense
can significantly mitigate the risk of SQL injection, by restricting
what an attacker can access and execute when exploiting the vulnerable
application. For example, a Web application used for reporting purposes,
such as checking the performance of your investment portfolio, should
ideally access the database with a login that has inherited only the
permissions on objects (stored procedures, tables, etc.) needed to
produce this data. This could be EXECUTE permissions on several stored
procedures and possibly SELECT permissions on a handful of table
columns. In the event of SQL injection, this would at least limit the
possible set of commands to the stored procedures and tables within the
application database and prevent malicious SQL outside this context,
such as dropping tables or executing operating system commands. It’s
important to remember that even with this mitigating control the
attacker may still be able to circumvent business rules and view the
portfolio data of another user.
To determine the
permissions assigned to a database login, find its role membership and
remove any unnecessary or privileged roles, such as the public or
database administrator role. Ideally, the login should be a member of
one (or possibly more) custom application roles. A follow-up step is to
audit permissions assigned to custom application roles to ensure that
they are locked down appropriately. During a database audit, it is very
common to find unnecessary UPDATE or INSERT permissions assigned to
custom application roles intended for read-only access. These audit and
subsequent cleanup steps can be performed with graphical management
tools that often accompany the database server platform or with SQL via
the query console.
Revoke PUBLIC Permissions
Every database server
platform has a default role to which every login belongs, usually called
the public role, which has a default set of permissions that includes
access to system objects. Attackers use this default access to query
system catalogs to map out database schema and target the juiciest
tables for subsequent querying, such as those storing application login
credentials. The public role is also assigned permission to execute
built-in system stored procedures, packages, and functions used for
administrative purposes.
Usually you cannot drop the
public role; however, it is recommended that you not grant additional
permissions to the public role, because each database user inherits the
permissions of this role. You should revoke public role permissions from
as many system objects as possible. Additionally, you must revoke
superfluous permissions granted to the public role on custom database
objects (such as application tables and stored procedures) unless a
justifiable reason for the permissions exists. If necessary, you should
assign database permissions to a custom role that you can use to grant a
default level of access to specific users and groups.
Use Stored Procedures
From a security
perspective, you should encapsulate application SQL queries within
stored procedures and grant only EXEC permissions on those objects. All
other permissions, such as SELECT, INSERT, and so on, on the underlying
objects can be revoked. In the event of SQL injection, a
least-privileged database login that has only EXECUTE permissions on
application stored procedures makes it more difficult to return
arbitrary result sets to the browser. This does not guarantee safety
from SQL injection, as the insecure code could not lie within the stored
procedure itself. Additionally, it may be possible to obtain result
sets via other means, such as with blind SQL injection techniques.
Use Strong Cryptography to Protect Stored Sensitive Data
A
key mitigating control against unauthorized viewing of sensitive data
in the database is the use of strong cryptography. The options include
storing a mathematical hash of the data (rather than the data itself) or
storing the data encrypted with a symmetric algorithm. In both cases,
you should use only public algorithms deemed cryptographically strong.
You should avoid homegrown cryptographic solutions at all costs.
If the data itself
does not require storage, consider an appropriately derived mathematical
hash instead. An example of this is data used for challenging the
identity of a user, such as passwords or security question answers. If
an attacker is able to view the table storing this data, only password
hashes will be returned. The attacker must go through the time-consuming
exercise of cracking password hashes to obtain the actual credentials.
Another clear benefit to hashing is that it eliminates the key
management issues associated with encryption. To stay consistent with
security best practices, ensure that the hashing algorithm of choice has
not been determined mathematically susceptible to collisions, such as
MD5 and SHA-1.
If you must store
sensitive data, protect it with a strong symmetric encryption algorithm
such as Advanced Encryption Standard (AES) or Triple DES (Data
Encryption Standard). The primary challenge to encrypting sensitive data
is storing the key in a location that the attacker cannot access
easily. You should never store encryption keys client-side, and the best
server-side solution for key storage usually depends on the application
architecture. If the key can be provided at runtime, this is ideal as
it will only reside in memory on the server (and depending on the
application framework it can be possible to protect it while in memory).
However, on-the-fly key generation is usually not feasible or practical
in most enterprise application environments. One possible solution is
to store the key in a protected location on the application server so
that the attacker needs to compromise both the database server and the
application server to decrypt it. In a Windows environment, you can use
the Data Protection API (DPAPI) to encrypt application data and leverage
the operating system to securely store the key. Another
Windows-specific option is storing the key in the Windows Registry,
which is a more complex storage format than a flat text file and
therefore could be more challenging to view depending on the level of
unauthorized access gained by the attacker. When operating system
specific storage options are not available (such as with a Linux
server), you should store the key (or secret used to derive it) on a
protected area of the file system with strict file system ACLs applied.
It’s also worth noting that as of Microsoft SQL Server 2005 and Oracle
Database 10g Release 2, both support column-level encryption natively.
However, these nice built-in features do not provide much additional
protection against SQL injection, as this information will usually be
transparently decrypted for the application.
Maintaining an Audit Trail
Maintaining
an audit trail of access on application database objects is critical;
however, many applications don’t do this at the database level. Without
an audit trail, it is difficult to know whether the integrity of
application data has been maintained given an SQL injection attack. The
server transaction log might provide some detail; however, this log
contains systemwide database transactions, making it hard to track down
application-specific transactions. All stored procedures could be
updated to incorporate auditing logic; however, a better solution is
database triggers. You can use triggers to monitor actions performed on
application tables, and you don’t have to modify existing stored
procedures to begin taking advantage of this functionality. Essentially,
you can easily add this type of functionality to existing applications
without having to modify any data access code. When using triggers, it’s
important to keep the logic simple to avoid possible performance
penalties associated with the additional code, and to ensure that the
trigger logic is written securely to avoid SQL injection within these
objects. Let’s take a closer look at Oracle database triggers to better
understand how triggers can be leveraged to detect possible SQL
injection attacks.
Oracle Error Triggers
Oracle offers a
feature called database triggers. These triggers can fire databasewide
in case of special events such as the creation of a Data Definition
Language (DDL; e.g., DDL trigger) or a database error (e.g., ERROR
trigger). This offers a simple and easy way to detect SQL injection
attempts.
In most cases, SQL
injection attempts, at least in the beginning of an attack, will create
error messages such as “ORA-01756 Single quote not properly terminated”
or “ORA-01789 Query block has incorrect number of result columns”. The
number of these error messages is small, and in most cases they are
unique to SQL injection attacks, therefore keeping the number of false
positives low.
The following code will find and document SQL injection attempts in an Oracle database:
-- Purpose: Oracle Database Error Trigger to detect SQL injection Attacks
-- Version: v 0.9
-- Works against: Oracle 9i, 10g and 11g
-- Author: Alexander Kornbrust of Red-Database-Security GmbH
-- must run as user SYS
-- latest version: http://www.red-database-security.com/scripts/oracle_error_
trigger.html
--
-- Create a table containing the error messages
create table system.oraerror (
id NUMBER,
log_date DATE,
log_usr VARCHAR2(30),
terminal VARCHAR2(50),
err_nr NUMBER(10),
err_msg VARCHAR2(4000),
stmt CLOB
);
-- Create a sequence with unique numbers
create sequence system.oraerror_seq
start with 1
increment by 1
minvalue 1
nomaxvalue
nocache
nocycle;
CREATE OR REPLACE TRIGGER after_error
AFTER SERVERERROR ON DATABASE
DECLARE
pragma autonomous_transaction;
id NUMBER;
sql_text ORA_NAME_LIST_T;
v_stmt CLOB;
n NUMBER;
BEGIN
SELECT oraerror_seq.nextval INTO id FROM dual;
-
n := ora_sql_txt(sql_text);
--
IF n >= 1
THEN
FOR i IN 1..n LOOP
v_stmt := v_stmt || sql_text(i);
END LOOP;
END IF;
--
FOR n IN 1..ora_server_error_depth LOOP
--
- log only potential SQL injection attempts
-- alternatively it’s possible to log everything
IF ora_server_error(n) in (’900’,’906’,’907’,’911’,’917’,’920’,’923’,’933’,’970’,
’1031’,’1476’,’1719’,’1722’,’1742’,’1756’,’1789’,’1790’,’24247’,’29257’,’29540’)
THEN
-- insert the attempt including the SQL statement into a table
INSERT INTO system.oraerror VALUES (id, sysdate, ora_login_user, ora_client_
ip_address, ora_server_error(n), ora_server_error_msg(n), v_stmt);
-- send the information via email to the DBA
-- <<Insert your PLSQL code for sending emails >>
COMMIT;
END IF;
END LOOP;
--
END after_error;
/
Locking Down the Database Server
Once
the application data has been secured, you still need to take a few
additional steps to harden the database server itself. In a nutshell,
you want to make sure the systemwide configuration is secured in a
manner that is consistent with the security principle of least privilege
and that the database server software is up to date and patched. If you
comply with these two key directives, it will be very difficult for an
attacker to access anything outside the scope of the intended
application data. Let’s take a closer look at some specific
recommendations.
Additional Lockdown of System Objects
Besides revoking
public role permissions on system objects, consider taking additional
steps to further lock down access to privileged objects, such as those
used for system administration, executing operating system commands, and
making network connections. Although these features are useful to
database administrators, they are also just as useful (if not more so)
to an attacker who has gained direct access to the database. Consider
restricting by ensuring that superfluous permissions are not granted to
application roles, disabling access to privileged objects systemwide via
server configuration, or dropping from the server completely (to avoid
reenabling should privilege escalation occur).
On Oracle, you should
restrict the ability to run operating system commands and to access
files on the operating system level from the database. To ensure that
(PL/)SQL injection problems cannot be used to run operating system
commands or access files, do not grant the following privileges to the
Web application user: CREATE ANY LIBRARY, CREATE ANY DIRECTORY, ALTER
SYSTEM, or CREATE JOB. Also, you should remove the PUBLIC grant at least
from the following packages if it is not needed: UTL_FILE, UTL_ TCP,
UTL_MAIL, UTL_SMTP, UTL_INADDR, DBMS_ADVISOR, DBMS_SQL, and DBMS_XMLGEN. If the functionality of these packages is required it should be used only via secure application roles.
In SQL Server, you should consider dropping dangerous stored procedures such as xp_cmdshell, as well as the procedures that match xp_reg?, xp_instancereg?, and sp_OA?. If this is not feasible, audit these objects and revoke any permissions that were unnecessarily assigned.
Restrict Ad Hoc Querying
Microsoft SQL Server supports a command called OPENROWSET
to query remote and local data sources. Remote querying is useful in
that it can be leveraged to attack other database servers on connected
networks. Querying the local server with this function allows an
attacker to reauthenticate to the server in the context of a more
privileged SQL Server database login. You can disable this feature in
the Windows Registry by setting DisallowAdhocAccess to 1 for each data provider at HKLM\Software\Microsoft\ MSSQLServer\Providers.
Similarly, Oracle
supports ad hoc querying of remote servers via database links. By
default, a normal user does not require this privilege and you should
remove it from the account. Check the CREATE DATABASE LINK privilege
(part of the connect role until Oracle 10.1) to ensure that only
required logins and roles are assigned to avoid attackers creating new
links.
Strengthen Controls Surrounding Authentication
You should review all
database logins, and disable or delete those that are unnecessary, such
as default accounts. Additionally, you should enable password strength
within the database server to prevent lazy administrators from selecting
weak passwords. Attackers can leverage weakly protected accounts to
reauthenticate to the database server and potentially elevate privilege.
Lastly, enable server auditing to monitor suspicious activity,
especially failed logins.
In SQL Server
databases, consider exclusive use of Integrated Windows Authentication
in favor of the less secure SQL Server Authentication. When you do this,
attackers will be unable to reauthenticate using something such as OPENROWSET;
in addition, it reduces the possibility of sniffing passwords over the
network, and can leverage the Windows operating system to enforce strong
password and account controls.
Run in the Context of the Least-Privileged Operating System Account
If an attacker is able
to break outside the context of the database server and gain access to
the underlying operating system, it is critical that this occurs in the
context of the least-privileged
operating system account. You should configure database server software
running on ?nix systems to run in the context of an account that is a
member of a custom group that has minimal file system permissions to run
the software. By default, SQL Server 2005 and later installers will
select the minimally privileged NETWORK SERVICE account for running SQL
Server.
|
SQL Server Taking Security Seriously
The good news is that
starting with SQL Server 2005, Microsoft included a handy configuration
utility called SQL Server Service Area Configuration, which makes it
really easy to disable most of the functionality that an attacker could
abuse. Previous versions of SQL Server required running Transact-SQL
statements or modifying the Windows Registry. Even better, most of the
dangerous features are disabled by default.
|
Ensure That the Database Server Software Is Patched
Keeping software up to
date with the current patch level is a fundamental security principle,
but it’s easy to overlook given that database servers are not
Internet-facing systems. An attacker can exploit server vulnerabilities
via an application-level SQL injection vulnerability just as easily as
though he were on the same network as the database server. The exploit
payload could be a sequence of SQL commands that exploit an SQL
injection vulnerability in a PL/SQL package, or even shell code to
exploit a buffer overflow in an extended stored procedure. Automated
update mechanisms are ideal for keeping up to date. You can keep SQL
Server up to date with Windows Update (www.update.microsoft.com). Oracle database administrators can check for current updates by signing up with the Oracle MetaLink service (http://metalink.oracle.com/CSP/ui/index.html). Third-party patch management systems are another way to keep patch levels current. Table 1
shows commands that can help you determine the version of the database
server software for SQL Server and Oracle. Also included in the table
are links for checking the version information to tell whether your
database server is completely patched.
Table 1. Determining SQL Server/Oracle Database Server Versions
| Database | Command | Version Reference |
|---|
| SQL Server |
| www.sqlsecurity.com/FAQs/SQLServer
VersionDatabase/tabid/63/ Default.aspx |
| Oracle |
-- show database version
select * from v$version;
-- show version of
installed components
select * from dba_registry;
- show patchlevel
select * from
dba_registry_history;
| www.oracle.com/technology/support/patches.htm |
