Chapter 6: Accessing Data – Hacking the Code

Chapter 6

Accessing Data


Depending on a hacker’s goal, an application’s data may be his or her primary target. In general, the back end of the application is where all the personal, sensitive, and often desirable data is stored—from credit card numbers to medical information. Most Web application use some kind of data storage. This chapter describes how easy it is to compromise a database and demonstrates the problems that plague many current Web applications. However, after you read the solutions in this chapter, your own Web application will not have to be one of them.

To secure your database, we will take a bottom-up approach by first discussing how to secure drivers that an application uses to communicate with a database. Next, we’ll examine how to secure the database as a whole by tightening the default installation and using policies such as least privileges. We will cover firewalls and other means to protect your application as well as monitor intrusion attempts. Finally, we’ll have an in-depth discussion of how to write secure code that safely connects to your database. This discussion will include specific details of the ways attackers crack systems using SQL injections and the multiple layers of security you can use to protect your application.

The threats discussed in this chapter are:

 Data compromise An attacker gains access to read or modify private data.

 Database compromise An attacker gains access to modify the database structure itself.

 SQL injection Manipulating user input to construct SQL statements that execute on the database server.

 Buffer overflows Overwriting a buffer by sending more data than a buffer can handle, resulting in the application crashing or executing code of the attacker’s choice.

 Privilege escalation Accessing system resources or executing code within the security context of a privileged user account.

 Information leakage Revealing sensitive information or private user data.

Securing Databases

Your data access code’s security depends greatly on your entire database infrastructure. Security vulnerabilities may occur due to bugs in the database or its drivers, unsafe database location, or poor database configuration. Before writing any data access code in your Web application, you should first consider the security of the database itself.

Securing the Database Location

Summary: Carefully design your database’s location with regard to firewalling technologies
Threats: Database compromise, bypassing security measures

An important first step in securing your database is to properly control access to the database itself. You should be careful to configure your firewall to restrict access so that only the Web application itself can directly access database ports. Where you physically place the database server on your network can also have an impact on database security.

For an example, let’s say that we have a .NET Web application named myApplication that uses a database called myApplication Database. Figure 6.1 shows a common firewall layout for this scenario.

Figure 6.1 Firewall Layout #1

Notice that a firewall separates the myApplication Web server from the Internet, and another firewall separates the myApplication Web server from the myApplication database. The area in which the Web server resides is called a demilitarized zone (DMZ). Behind the second firewall reside the database and often the internal company network.

This scenario is common because many administrators believe it provides a second layer of protection for the database in case an attacker compromises the Web server or another server in the DMZ. However, this may not be the most secure configuration. If an attacker obtains access to the database itself, perhaps through SQL injection or a buffer overflow, the attacker gains a direct line to the internal network, completely sidestepping the protections of the firewall. Consider an alternative network layout, presented in Figure 6.2.

Figure 6.2 Firewall Layout #2

In this scenario, we have placed the database in the DMZ, along with the Web server. This scenario treats the database server as a high-risk server and isolates it from other databases and the rest of the internal network. Placing the database server in the DMZ contains and limits the scope of a database attack.

Your specific firewall configuration will depend on the needs of the applications you are running as well as the type of data you store in the database. Wherever you place your database, you must consider the risks involved.

Security Policy

 Review your network topology and security needs to design a firewall layout best suited for your environment.

 Assume worst-case scenarios when designing your firewall layout.

A database driver is the software interface that communicates with a database. Because drivers are essential for database communication, they are a popular point of attack. The single most important thing you can do to protect your database drivers is to keep them up to date. As with most popular enterprise software, drivers are frequently updated because vulnerabilities in them can have such dire effects on a system. Many recent viruses, worms, and Internet exploits could have been prevented if the compromised systems had updated drivers and software.

In addition to keeping your drivers up to date, other good security practices you can follow include limiting the areas of your system an attacker can compromise and understanding and preventing buffer overflow attacks. We will also examine logging and tracing methods to determine when and how attackers may have invaded, or attempted to invade, your system.

Limiting the Attack Surface

Summary: Remove unused drivers from your database to reduce the number of attack vectors
Threats: Database compromise

Software companies build databases to accommodate a huge variety of Web site needs. Chances are, your application doesn’t use every feature available with a default database installation. Every database feature is a potential attack vector; therefore, unused features do nothing more than increase the attack surface for a hacker. Decrease this attack surface by removing any unused database drivers.

By default, a Windows 2000 installation comes with various Open Database Connectivity (ODBC) drivers. You can safely remove the ODBC drivers if you are not using them. You need to manually remove the ODBC drivers from the registry. Using regedit, remove any unused drivers from the following keys:

Figure 6.3 shows how to use regedit to remove these keys.

Figure 6.3 Removing an ODBC Driver from the Registry

If you are not using the Jet drivers, you can safely remove them as well. Delete the unused Jet engines and Index Sequential Access Method (ISAM) formats that you find below the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet registry key.

Finally, some Windows applications install User, System, or File data source names (DSNs). Generally, the applications do not even notify you that they installed these DSNs. If you don’t use these DSNs, you should delete them. Use the User Data Sources (ODBC) Administrative Tool and click Remove for each unused driver, as shown in Figure 6.4. Alternatively, you can remove the DSNs by deleting the entries under the HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCI.INI registry key.

Figure 6.4 Removing DSNs


Some updates or patches may replace the registry entries you removed. Check your registry and remove any restored entries after you install updates.

Securing Specific Drivers

After removing unused database drivers, you should further reduce the attack surface by securing the remaining drivers. Most database drivers provide settings to limit functionality or restrict driver usage. Here we discuss some example settings for various common drivers.

SQL Server

Keeping a record of who is trying to log in to your database is a valuable practice from many standpoints. If you see multiple attempts and failures with different usernames and/or passwords, you can surmise that an attacker is trying to use brute force to access your database. Likewise, if you notice successful logins at strange times, you may need to contact the user who logged in to make sure there was a valid reason for his or her use of the database.

By default, SQL Server does not audit logins and login attempts. You can record login attempts by setting a value in the registry key located at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\AuditLevel. The possible values are:

 0 No logins are recorded (this is the default).

 1 Only successful logins are recorded.

 2 Only failed logins are recorded.

 3 Both successful and failed logins are recorded.

Alternatively, you can set the recording level using SQL Server’s Enterprise Management tool. Right-click the database group and select Properties. A new window will pop up. Select the Security tab from the top. You should see a window similar to Figure 6.5.

Figure 6.5 Setting the Maximum Logging Level

The Audit level area lets you set the logging level. Whether you use the registry setting or the GUI, set the audit level to record all login attempts. SQL

Server’s default location to write the logs in C:\Program Files\Microsoft SQL Server\MSSQL\LOG.


You can configure IIS to automatically log details to an ODBC source. You can log data such as the client’s IP address, the request the client performed, parameters passed, page requested, and many other properties of the connected user’s communication. With this data stored in a database, you can search for and respond to attacks and abnormal activity.

To set up IIS for ODBC logging, right-click your Web site, shown in the IIS Administration window. Select Properties, and you will see a window similar to Figure 6.6.

Figure 6.6 Setting IIS for ODBC Logging

From this window, you can select ODBC Logging from the bottom area labeled “Active log format.” After selecting ODBC Logging, you need to click the Properties button to the right to configure the ODBC DSN, the table to log to, and your user credentials to connect to the data source. You can find a list of the table structure appropriate for logging, as well as a script to create the table for you, in Microsoft’s Knowledge Base Article 245243, at;en-us;Q245243.

Jet and ISAM

If you are using Jet or ISAM, you should use certain settings to improve your security. Jet drivers can and should run in Sandbox mode. You may have heard the term sandbox applied to Java applets. The idea with Jet drivers is the same. A sandbox is a protected portion of memory in which an application or drivers can run without the risk of causing damage to other applications running. The Jet Sandbox mode prevents users from embedding sensitive commands such as Shell in SQL queries.

You should change the registry entries at HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\3.5\engines\SandboxMode and HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\engines\SandboxMode to run at the value of 3, which is the most secure Sandbox mode. The possible modes are:

 0 Sandbox mode is disabled.

 1 Sandbox mode is used only with Access applications.

 2 Sandbox mode is used only with non-Access applications.

 3 Sandbox mode is used on all applications.

Another setting you can tighten to increase security deals with the text ISAM. By default, the text ISAM allows you to read and write any text file. The registry key that controls this feature is located at HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\Text\DisabledExtensions and contains the default values of !txt,csv,tab,asc,tmp,htm,html. Increase the restrictions on which files can be read and written by replacing the txt entry with whatever text extensions you are using.

Security Policy

 Remove or disable unused drivers from your database.

 Periodically check for and remove any new unused drivers, especially after updates or patches.

 Configure your database drivers to maximum security.

 Configure your database drivers to log access activity.

Ensuring Least Privilege

Summary: Restrict access and permissions to your database to the smallest set of permissions that still retains functionality
Threats: Information leakage, database compromise, elevated permissions

Software engineers by nature are concerned with meeting their deadlines and producing their deliverables. When a programmer needs to connect to a database, he or she often writes code that uses the quickest and easiest way to connect and get the data needed. This generally involves using SQL Server’s sa, or System Administrator, account, which has superuser access and won’t give the developer any hassles about permissions or authorization. “I’ll go back and lock the system down once I get time,” the programmer often thinks.

Experience shows that with the approaching deadlines and last-minute bugs, the programmer forgets or does not have time to go back and tighten the database’s security. This is how software can end up in a production environment with elevated permissions. It’s also why an attacker can leverage one system running with unnecessarily high permissions to gain access to every other machine in the company.

The rule of least privilege states that any user, application, or process should have the bare minimum of access to do what it needs to complete its function. Do not give or allow access based on the idea that the user might need the access later or that it’s easier than figuring out what the minimum allowed access should be. Grant only the minimum required privileges to a user. If, in the future, the user needs more access, grant it then and not before.

SQL Server has various mechanisms to help you apply the rule of least privilege, including roles, groups, and access control lists (ACLs). We’ve already mentioned the first and most obvious (but often neglected) thing you should do: Never use the sa account for data access. The sa account has rights to do all kind of things your application likely doesn’t need to do, such as deleting an entire database. Instead, create a user account on the SQL Server that only has rights to do specifically what your code requires, whether reading a table, executing a stored procedure, or the like. You can set permission rights as specific as an individual column of a table or an individual stored procedure. As you find you need more access, add the explicit right you need. Locking down your access on a database this way obviously limits the actions a user can perform. This is fantastic from a security point of view. If an attacker is able breach your database, the attacker will find himself restricted by the same rights the application had. This could potentially minimize both the data the attacker can steal and the damage he can cause.

A firewall is another method of applying least privilege. By giving permission only to the ports your database uses to go through the firewall, you are further restricting unexpected or unauthorized communication. SQL Server, by default, communicates through port 1433 for TCP/IP access. You can change this setting, but be aware that a port change will cause complexity for traffic profiling and other monitoring that relies on SQL Server using port 1433.

Internet Protocol Security (IPSec) and Secure Sockets Layer (SSL) are two additional methods you can use to restrict who can connect to your database. IPSec uses policies that consist of filters, filter actions, and rules. With IPSec, you explicitly specify, by IP address, which computers may connect to your database. SSL uses certificates to restrict who can connect. A client computer must have a known and trusted certificate to successfully connect to the database. An advantage of SSL over IPSec is that configuration changes are not required if the client’s IP address changes.

Use the rule of least privilege throughout the design and implementation of your application in many different ways. It is part of a solid foundation of good security. We will continue to mention this policy and recommend more ways to use it in other areas of your application.

Security Policy

 Always grant the minimum of required access and permission.

 Use firewalls to restrict inappropriate access.

 Use IPSec or SSL to restrict who can connect to your database.

Securing the Database

Summary: Remove unused features and restrict default settings on your database to prevent successful attacks
Threats: Database compromise

As we’ve mentioned before, databases include many features to appeal to the widest market and range of uses as possible. You will probably not need the majority of these features and can safely reduce your attack surface by disabling or removing these unused features. Furthermore, databases often have weak default settings. The following is a list of features you can remove, as well as default settings you should strengthen:

 Keep your service packs, drivers, updates, hotfixes, and patches current. Review your database vendor’s Web site often to find what service patches are available.

 Use strong passwords for all accounts. This is especially important for the sa account, which is present by default in SQL Server. Strong passwords decrease an attacker’s ability to successfully guess, brute-force, dictionary attack, or otherwise discover an account’s password. Always use long and complex passwords for database accounts.

 Remove sample code, sample databases, and sample stored procedures. By default, SQL Server comes with the Northwind and Pubs databases and associated stored procedures. There is no reason for these databases to be present in a production environment.

 Remove unused network libraries. SQL Server can communicate with applications in a variety of ways. Network libraries, or netlibs, are the modules that determine the method of communication SQL Server can use. TCP/IP is the most popular method of communication, but you can also use Shared Memory, Named Pipes, Banyan Vines, AppleTalk, or VIA GigaNet SAN, as well as others. While you are deciding which communication method to use, disable all the netlibs to prevent access to your database. When you have decided which netlib(s) to use, disable the unused netlibs to limit your attack surface.

 Remove extended stored procedures. By default, SQL Server comes with over 60 stored procedures that you might not need. These stored procedures are primarily for convenience and provide the functionality of certain graphical user interface (GUI) tools, such as the SQL Server Enterprise Manager. By removing these stored procedures, you will limit tools and methods an attacker can use to gain access and compromise your database.

The process for securing a database is complex and unique for each platform and database application; the tips mentioned here are only an introduction. Take time to learn the specific security measures for your particular environment.

Security Policy

 Keep your database software up to date.

 Disable or remove unused features of your database.

 Strengthen weak default passwords and permissions.

Writing Secure Data Access Code

We have covered how to secure the database drivers and the database itself, and you’ve seen the various settings you can use to lock down functionality and access. However, this is only the half the solution to securing data access. If your code does not properly filter user input, an attacker might be able to leverage your Web application to execute SQL statements on the database.

In this section, you will learn how to prevent an attacker from gaining access to your database. Here we will cover:

 Connecting to a data source

 Preventing SQL injection

 Writing secure data access code

 Reading and writing to data files

Connecting to the Data Source

Summary: Connecting to the data source can potentially expose sensitive information
Threats: Information leakage, data corruption, data destruction

Authentication and authorization are critical elements when using a data source. Before your application can use your data source, the data source should authenticate your application to connect and authorize the activities your application attempts to perform. The lack of either of these steps suggests a database with weak security. A data source that does not authenticate allows anyone and everyone to connect, from the most trusted user to the most dangerous hacker. A data source that does not authorize regards every connected user as a superadministrator, with rights to read all stored data and perform any kind of data source change. SQL Server has multiple options for both authentication and authorization, which we explore in this section.


Authentication is the process by which your application connects to the database. An attacker would have a difficult time gaining access to a database to which he cannot connect.

Your application can authenticate to an SQL Server database in two different ways. We recommend the more secure method, called Windows Authentication. With Windows Authentication, Windows manages credentials for you, so there is no need to transmit them over the network. For this reason, there is no need for usernames or passwords to be stored in the connection string. You have various options as to how you use Windows Authentication when connecting to an SQL Server from an ASP.NET application. These options include using the ASP.NET process identity, using fixed identities within ASP.NET, using serviced components, using the LogonUser API to impersonate a specific identity, using the original caller’s identity, and using the anonymous Internet User account.

We recommend using the ASP.NET process identity, because it is one of the simplest and most secure methods of connection. To use ASP.NET, you need to change the local ASP.NET process identity’s password value on the Web server and create a mirrored account on the database server by creating a local user with the same name and password. Outlined here are the necessary steps in this process:

1. Change the ASPNET account on the Web server to a known strong password value containing upper- and lowercase letters, numbers, and specials characters such as !, @, #, or %. For example, wh!t3Rabitt..hop..hop.

2. Change the password in Machine.config (usually found at C:\Windows\Microsoft.NET\Framework\<Framework version>\CONFIG) at the processModel element to match. Example:

3. Protect the machine.config file from unauthorized access by using ACLs.

4. Create this same account (a mirrored account) on the database server.

5. On the database server, create a server login for the local ASPNET account and map the login to a user account within the appropriate database. Create a database user role, add the database user to the role, and configure the appropriate database permissions for the role.

After completing these steps, you will be able to connect to the SQL Server using Windows Authentication. Figure 6.7 (C#) and Figure 6.8 (VB.NET) show example connection strings using Windows Authentication.

Figure 6.7 Windows Authentication [C#]

Figure 6.8 Windows Authentication [VB.NET]

Windows Authentication is not always a possibility, however. The second and less secure method for an application to connect to a SQL Server database is SQL Authentication. Consider SQL Authentication only if:

 Your database doesn’t support Windows Authentication.

 Your application cannot use Windows Authentication because of a firewall.

 Your application must connect to the database using multiple identities and you are not using impersonation in your ASP.NET application.

SQL Authentication can be dangerous because credentials must be stored and passed to the database. You must protect the credentials on the application server as well as in transit to the SQL Server. One method is to install a server certificate on the SQL Server database to automatically encrypt credentials sent over the network. You could also use an IPSec encrypted channel to protect communication between the application and SQL Server. Encrypt the database connection string your application uses as well, in case an attacker finds a way to read your file system. The next section discusses methods for encrypting the database connection string. Figure 6.9 (C#) and Figure 6.10 (VB.NET) show example connection strings for SQL Authentication.

Figure 6.9 SQL Authentication Connection String [C#]

Figure 6.10 SQL Authentication Connection String [VB.NET]

With either method, it is imperative that you connect to the database using a least privileged account. ASP.NET Web applications, by default, use the ASPNET account. Create the appropriate account on the SQL Server and give the minimum appropriate permissions for the application to access and use the desired database. Again, we are applying the rule of least privilege.

Protecting Connection Strings

Protecting your connection strings is particularly important if you are using SQL Authentication, because the connection string will contain a username and password. You should never store connection information in the Web application code itself. Even if you are using Windows Authentication, the connection string still contains the server and database to which you are connecting. The less information your application server exposes in case of a compromise, the better. The following methods are better alternatives to protect your connection string.


Data Protection Application Programming Interface (DPAPI) is one of the most secure methods for storing your connection strings, although it is a bit complicated to use. DPAPI is part of Windows 2000 and later Windows operating systems. Use DPAPI for encrypting and decrypting data. The advantage of DPAPI is that the operating system manages the encryption key, instead of the application. DPAPI also leverages the login of the account calling the DPAPI code to derive the encryption key. See for more information on using DPAPI from ASP.NET.

UDL Files

If you are using the OLE DB .NET data provider, using UDL files is an option. Take care to store the UDL outside the Web application’s virtual directory, and protect the files with the proper NTFS permissions. Always use a fully qualified path for UDL files to be sure that you are using the correct UDL file. Note that UDL files do not use encryption to store connection information.

COM+ Component

If your application includes service components, you can use COM+ to store connection strings. Store the connection strings as constructor strings, and administer them using the Components Service tool. For more information on using COM+ for storing connection strings, see;en-us;Q271284.


A connection string can be stored in the HKEY_LOCAL_MACHINE or HKEY_CURRENT_USER registry hive. Use proper ACLs and encryption to protect any information stored in the registry.

Text Files

Text files are an insecure method of storing connection strings. If you must use text files, make sure you encrypt the files, store them outside the Web application’s virtual directory, and protect them with proper NTFS permissions.


Never use the sa or db_owner accounts for application data access. Attackers will always first try using these built-in default accounts. Make sure these accounts have very strong passwords and that you use them only for administration.


Authorization is the process by which the database determines whether your connected application has sufficient rights for the operations it is trying to perform. In other words, does the connected application have permission to read this table or modify this column? SQL Server provides a role-based approach for authorization. Roles can grant and restrict read and write access to databases, tables, columns, roles, and stored procedures. Three categories of role are supported:

 User-defined database roles These roles are used to group users who have the same access rights on the database. For example, the administrator can make a role named Human Resources and specify that this role can only view the EmployeePersonalInformation databases. All other databases on the server, such as CustomerInformation, will refuse access to a user in the Human Resource role. Finally, the administrator needs to assign specific users to the role. In this example, the database admin would assign the login for Martha from Human Resources to the Human Resource role.

 Application roles These are roles used to grant specific applications access rights on the database. An application uses a built-in stored procedure to activate the rights.

 Fixed database roles These are fixed roles the SQL Server database comes with out of the box. These general-use roles are for common activities such as the db_backup role that has access to back up the database.

Security Policy

 Use roles to apply the least privileged accounts.

 Use Windows Authentication whenever possible.

 Keep database connection strings secure.

 Set strong passwords for the sa and db_accounts. Do not use these accounts in your applications!

Preventing SQL Injection

Summary: An attacker can run malicious SQL code against your database
Threats: Information leakage, data corruption, data destruction

SQL injection attacks are among the most dangerous and commonly used Web-based attacks today. The basis for an SQL injection attack involves a malicious user causing a database to run destructive and compromising SQL commands. We will give specific examples of how attackers use SQL injection attacks, the information the attacker can gather, and the damage an attacker can perform. We will then cover several methods for preventing these attacks.

SQL Injection Examples

Virtually every Web site reads information entered by users, from login information to search criteria. When a user purposely inserts SQL code into data that the database is going to process, a SQL injection occurs. For example, a Web site may read in the username and password of a user during login and check the database to see if this is valid login information. A common and insecure SQL query string may look like the strings shown in Figure 6.11 (C#) or Figure 6.12 (VB.NET).

Figure 6.11 Common Query String [C#]

Figure 6.12 Common Query String [VB.NET]


The SQL query strings in Figures 6.9 and 6.10 have various critical problems. If you have any strings like these in your code, you are susceptible to attack on many levels. Please read on to learn how to secure your queries.

The intention is that users enter their information, and the following query would run against the database:

The code uses the result set returned from this query to determine whether or not to grant access. However, for a username, a malicious user might enter:


and for a password:

‘; drop table Accounts

This means that the following query runs against the database:

Two statements run. The first performs a useless lookup on the user hahaha; the second statement destroys your accounts database table. This is the crux of SQL injections: Malicious code runs against your database when user input is not cleaned, validated, and secured.

Some basic SQL notation will help explain these attacks:

 ‘ Opens and closes a database string.

 ; Ends a statement.

 -- Creates a comment. Anything after the -- is ignored.

The following list shows some of the attacks a malicious user can use to compromise and destroy data. The attacks use our original query string shown in Figure 6.9.

 Retrieve database structure information For an attacker to mount a successful attack, he or she needs to learn which tables and columns are available. The default behavior of SQL Server is to return informational error messages when incorrect queries run. For example, if for the username the attacker entered:

    ‘ having 1=1

    the database will return an error message containing the table name as well as the first column in the code’s query, as shown in Figure 6.13.

Figure 6.13 Table Name Exposed in Error Message

    You can see that the error message contains the table “Accounts” and the first column of the query “username.” The group by statement can ascertain further columns in the query, as shown here:

    ‘ group by Accounts.username having 1 = 1 - -

    The database will return the next column in the query, as shown in Figure 6.14.

Figure 6.14 Next Column in Query Is Exposed in Error Message

    You can see that the error message contains the next column in the query, “password.” An attacker can continue to append group by statements until he stops receiving error messages. In our example query string, the attacker will stop getting error messages when he enters:

    ‘ group by Accounts.username,Accounts.password having a=a--

    The error messages stop because all the columns in the table “Accounts” are present in the group by statement. The attacker now knows the columns in the Accounts table and can insert a record to gain access.

 Retrieve database content information An attacker can retrieve the data stored in the database by taking advantage of a conversion error message. When a query tries to perform an illegal conversion, SQL Server returns the actual data that could not be converted. For example, if for a username the attacker enters:

the database returns the first username in the Accounts table—in this case, “admin,” as shown in Figure 6.15.

Figure 6.15 Username Value Exposed in Error Message

    An attacker could use the same SQL injection and substitute password for username to learn the admin account’s password, and so forth for every table or record in the database.

 Compromise database integrity This attack aborts a query by closing the original statement appropriately, often by a closing quotation mark and/or a semi-colon, and then appends a destructive or corruptive SQL statement. Here are some examples of destructive input that could be entered as a password:

    ‘; delete from Accounts


    These queries result in the intended query running with a blank password and an unintended, destructive query running second.

 Compromise a query An attacker may shorten a query and circumvent the authentication process. In this example, the user name entered was:

    admin’- -

    Using the example query shown previously in Figure 6.1, this query causes the SQL query to prematurely end after specifying the using “admin.” Assuming an account named “admin” exists, the attacker circumvented knowing the admin password, and the code permits the attack to log in as “admin” with the appropriate password.

    Logical statements can also corrupt a query. If for a username, the attacker entered:

    ‘or a=a- -

    the attacker will be logged in as the first user in the database table. This works because the SQL Server will match the logically always true statement of a=a with the first account in the table.

These are some of the more common methods used for SQL injection. Most attacks use one or more of these methods. This is not, however, an exhaustive list of all the different permutations. Hackers are developing new SQL injection techniques all the time. Thankfully, code and operating policies can protect you from most, if not all, attacks.

Many Web sites will tell you that all you need to do to prevent SQL injections is to filter out or escape certain characters used in SQL injection attacks, such as ‘, --, and;. Filtering and escaping is not enough. The following are some solutions you can use to prevent SQL injections. Ideally, you should use more than one of these techniques.

Filtering or Escaping Dangerous Characters

Filtering or escaping dangerous characters is the most common and easiest to break method of preventing SQL injections. The idea behind the technique is to either remove (filter) dangerous characters from user input or cause the database to treat a dangerous character as a literal (escape).

Filtering can be a bad idea because the “dangerous” character might be a valid part of the user’s input. For example, removing a single quote (‘) from the company’s name or a user’s password could cause problems. You can, however, raise an error in the presence of “known bad” data. Known-bad data is characters that generally have no place outside an SQL statement, such as — or; characters. If these characters are inappropriate for the specific field—for example, a username field or a password field that doesn’t allow punctuation characters—instead of trying to filter or escape the characters, present an error to the user that says these characters are not allowed.

Escaping characters generally involves duplicating the dangerous character so that the code treats the character as a literal instead of the close of a string, in the case of the ‘ character. Figure 6.16 (C#) and Figure 6.17 (VB.NET) show example code to escape the ‘ character.

Figure 6.16 Escaping the ‘ Character [C#]

Figure 6.17 Escaping the ‘ Character [VB.NET]

Merely escaping dangerous characters is not sufficient protection, because an attacker could still insert malicious data into your database that your database accidentally activates later. For example, consider an attacker entering the following as a username:

Timebomb’; drop table account- -

The escapeQuoteCharacter method escapes the string, The new string reads:

Timebomb‘’; drop table Accounts- -

Since the double” marks means the literal ‘, the code safely inserts into the database:

Timebomb’; drop table Accounts- -

No damage has been caused at this point, since the ‘ character was treated as a literal; the user just has a strange username. For this example, assume that the Accounts table contains an e-mail column. Consider what happens when the Web site tries to send all users in the system an e-mail. Code would typically create a dataset containing the username of all the users to whom the application will send an e-mail. Here is the code that will run when the application uses the username data to retrieve the e-mail of the user with the malicious username:

The ‘ character in the username closes the select statement, the drop clause is appended, and the final quotation mark is commented out. The database interprets the username and drops the Accounts table. To prevent attacks such as this, escape the data contained in the results of all database queries. If the application had run the escapeQuoteCharacter method on each username in the result set before querying for the e-mail, the attack would have failed.

Another reason escaping characters is not sufficient protection is that an attacker could use ASCII hexadecimal characters and other character sets to bypass the checks. The database and code can interpret these hexadecimal characters correctly as the ‘ character, but if your escape code sees the value 0x2C instead of the ‘ character, it won’t escape it.

Using SqlParameters

The .NET framework has a collection type called SqlParameter that can provide type and length checking as well as automatically escaping user input. Figure 6.18 (C#) and Figure 6.19 (VB.NET) show examples of how to use the SqlParameter collection to assign variables when you’re building an SQL statement.

Figure 6.18 Using SqlParameters in Building SQL Statements (C#)

Figure 6.19 Using SqlParameters in Building SQL Statements (VB.NET)

Use this same technique when calling stored procedures. See Figure 6.20 (C#) and Figure 6.21 (VB.NET) for an example of how to use the SqlParameter when calling a stored procedure.

Figure 6.20 Using SqlParameters in Calling Stored Procedures (C#)

Figure 6.21 Using SqlParameters in Calling Stored Procedures (VB.NET)

The database regards input assigned to the parm. Value as a literal, so there is no need to escape the user input. Notice SqlParameter also enforces types and type length. If the user input values don’t conform to the described type and size, the code throws an exception. Whenever possible, constrain user data by the type and length it must be to benefit from type and length checking.

Constraining Data Types and Length

If you are collecting a date from a user, store it as a date in the database. If you are collecting an ID number, store it as a number in the database. If you are collecting an eight-character password, store it as a varchar of 8 characters maximum. If you combine the use of SqlParameter and data constraints, your code can reject data that doesn’t belong. For example, if an attacker to tries to inject a new user account on the end of the password field:

our SqlParameter code will detect that the password is more than 8 characters long, and it will throw an exception. Alternatively, if an attacker tries to perform the same attack on a numeric field, the SqlParameter code will refuse it because the attack includes nonnumeric characters.

Using Least Privileges

Restrict the database user to the bare minimum of actions. If your application only needs to read data from the database, there is no reason to allow the database user to drop tables, insert records, or anything other than reading data. If hostile code does make it to the database, lack of permission will minimize the damage.

Rejecting Known Attack Signatures

Depending on what your application does, you might be able to reject a query based on bad data that could be dangerous. Another way to look at this idea is to apply the principle of least privilege through code. Consider filtering user input keywords for dangerous SQL commands, such as drop or delete. Figure 6.22 (C#) and Figure 6.23 (VB.NET) show an example of filtering potentially dangerous SQL commands.

Figure 6.22 Filtering Dangerous SQL Commands (C#)

Figure 6.23 Filtering Dangerous SQL Commands (VB.NET)

If the method returns true, the user input contained bad data. You can take this idea further by creating regular expressions that check for an attacker trying to enter SQL syntax into a field. Take care to consider which user input fields you check with this kind of method. If you are validating a field that contains a user’s comments, there may be legitimate reasons for the user to type some of the dangerous command words.

Handling Errors on the Server

As explained in the SQL injection examples, error messages can give an attacker many details about your database. Wrap database actions in Try and Catch statements and properly process errors on the server side. In your Catch statement, log details about the error that occurred. This will help you know that an attack was attempted and what the attack was trying to do. By processing errors on the server, you will prevent the server from passing error messages, and the sensitive details they contain, to the client. Keep in mind that a successful SQL injection attack won’t necessarily cause errors. SQL injections that cause errors are often an attacker gathering information about your database as a precursor to an attack. See Chapter 7 for more details on how to properly handle errors.

Properly implemented, these solutions will greatly reduce your susceptibility to SQL injection attacks. Keep in mind, however, that preventing SQL injections is an ongoing battle. Hackers regularly find new exploits across all the different databases. Here are a few sites you can use to keep up to date with the latest SQL injection developments:

Security Policy

 Code a variety of protections against SQL injection, not just one method.

 Escape user input upon insertion into and retrieval from the database.

 Use SqlParameters.

 Process all errors on the server side.

 Enforce the rule of least privilege in the code and in the database account.

Writing Secure SQL Code

Summary: Security-conscious code can protect your application from future attacks and code compromise
Threats: Information leakage, data corruption, data destruction

An environment as hostile as the Internet will expose your application to threats that did not exist when you wrote the application. By writing security-conscious code, you can increase your application’s resiliency against known attacks as well as future attacks. Here are some examples of how you can code defensively to reduce or eliminate your code’s vulnerability:

 Avoid “SELECT * FROM” Use specific column names in your database queries to reduce the attack surface available. Use of the * wildcard is easier than enumerating all the columns you want from the database, but it gives a potential intruder or eavesdropper more data to work with. Use of the * wildcard also makes it easier for an attacker to manipulate the query without causing an error, since the * wildcard will match anything. By naming specific columns in your query, you will limit the query structure and output, making it more difficult for an attacker to construct the proper query for an attack. Always retrieve the minimum needed set of data.

 Sanity-check query results You can increase your code’s robustness by checking that your result set makes sense. For example, say you are retrieving information about a user from the database by username, and usernames are unique. Check that your result set contains exactly one record of data, and abort your application if it doesn’t. You certainly wouldn’t expect your result set to contain more than one user, but this is an example of coding defensively. An attack may come out that causes your result set to contain all the users, either by code injection, or a database flaw. The cost of checking the count of your result set is well worth it to avoid the damage done by compromised user’s information.

 Use stored procedures Stored procedures not only tend to run faster than constructed SQL, but they have security benefits as well. The fixed nature of the stored procedure deters SQL injection attacks (but does not solve them!), and it constrains the amount of information returned. Each stored procedure can have a specific security permission assigned to it. A read-only user is only able to call read-only stored procedures, which continue to support the rule of least privilege. Also, in the case of application code compromise, an attacker cannot infer the structure of your database from a stored procedure. The attacker will know you called the CreateAccount stored procedure with a username and password as parameters, but the attacker will not know the names of the tables or columns modified from the stored procedure.

 Structure your code for security The order in which your code operates can even enforce security. For an example, let’s use the logon scenario again, since logging in to a system is one of the most vulnerable and logical points of attack. Common code to authenticate a user to a Web site is shown in Figure 6.24 (C#) and Figure 6.25 (VB.NET).

Figure 6.24 Common Authentication Code (C#)

Figure 6.25 Common Authentication Code (VB.NET)

The code simply takes a user’s credentials (username and password) and queries to see if there is an account in the database matching those credentials. This code isn’t bad, but it could be better. Consider what happens if perhaps through a new exploit, an attacker succeeds in injecting SQL into the username, so that the password isn’t checked by using admin’— as the username. (See the “Compromise a Query” bullet point in the “Preventing SQL Injection” section for details on this attack.) The code will authenticate the user if there is an account with a username admin without the attacker knowing the password.

Prevent this kind of attack simply by slightly modifying the structure of the authenticating code. Retrieve the password of the user based on the given username. If the query returns a password, check the retrieved password against the user supplied password for a match. Figure 6.26 (C#) and Figure 6.27 (VB.NET) show this code.

Figure 6.26 Improved Authentication Code (C#)

Figure 6.27 Improved Authentication Code (VB.NET)

This code is no longer vulnerable to the aforementioned SQL injection attack. For the given username, the attacker’s entered password must match the returned password from the database. The code now enforces the match rather than database enforcing it.

Programming with security in mind can prevent current and future exploits. A good rule of thumb is to evaluate how exposed the area of code is to an attacker and then apply one or more of the given suggestions. We’ve emphasized how important it is to write secure logon code. The same security precautions may not apply to an internal module that calculates a coefficient for gravity (unless you work for NASA). But by applying secure coding principles, you can stay one step ahead of an attacker.

Security Policy

 Retrieve the minimum required data from the database.

 Check result sets for expected attributes.

 Use coding structures that emphasize security.

Reading and Writing to Data Files

Summary: An attacker can damage or destroy your application and operating system by attacking your data files
Threats: Data compromise and/or destruction, application compromise and/or destruction, operating system compromise and/or destruction

Any application that reads and writes data files can be susceptible to a number of risks. The data files might be from a file-based database, such as Access or dbase, or the data files might be files the application sends or receives from a user. Either way, since these data files generally reside on the host operating system, the application has permission to access the operating system’s file system. A malicious user can take advantage of this by launching attacks that attempt to read or delete information contained in sensitive files or even critical operating system files. That attacker could also launch a DoS attack by filling up your operating system’s file space.

In this section, we will review some of the most prevalent and damaging attacks and what you can do to prevent them. Any one of the solutions mentioned may prevent all the other attacks from succeeding. Our intention is not to be as secure as required but as secure as possible. There is always the possibility that a new flaw or newly discovered attack will render one of the precautions useless, validating the implementation of redundant precautions.

The first thing you should do is lock down the file system of your .NET application. If possible, place all your data files outside the Web root. If you must keep the data files within your application because of a policy or architecture constraints, make sure to place the files inside a directory that does not have IIS read or write permissions. To lock down IIS, launch the Internet Information Services administration window. Find the Web application, and expand its directory structure. Then right-click the directory that contains your data files, and select Properties. Figure 6.28 shows an example with a Web application named webapp and a file directory named data file directory.

Figure 6.28 Locking Down Access

From the Properties window that appears, make sure the Read and Write options are unchecked, as shown in Figure 6.29.

Figure 6.29 Restricting Read and Write Access


It is the operating system file system, not IIS, that needs read permissions on a data file. By default, ASP.NET will access your file system with the ASPNET account.

Next, you need to give the ASPNET process access to these data files through NTFS. Using Windows Explorer, browse to the directory where you have placed your data files. Right-click the directory, and select Properties. Select the Security tab at the top of the window to view the directory’s security access, as shown in Figure 6.30.

Figure 6.30 Setting NTFS Permissions

From this window, you can lock down the directory access by preventing Read & Execute, List Folder Contents, Write, and Read access. Lock down the data file directory, but relax the permission on the files your Web application uses. Now if a malicious user gains access to the directory, he or she will not be able to create files.

Another precaution is to use a specialized .dll to restrict access to specific file extensions in IIS 5. For example, if you are using Access database files, you can map all requests for files with the .mdb extension to a “404- File Not Found” page instead of returning the requested database file. You can use 404.dll, available at, to accomplish this task. In IIS 6 you do not need to do this, because that version will not allow requests for a file extension unless it already has a MIME mapping for that type of file.

You can specify and remove file extension mappings by clicking the Configuration button of the Web Application properties window, as previously shown in Figure 6.29. This will bring up the Application Configurations window, with the Mappings tab selected by default, as shown in Figure 6.31.

Figure 6.31 Add and Remove Extension Mappings

From this window, you can add mappings between file extensions you want to restrict and the 404.dll that will return the “404- Page Not Found” message. By using the 404.dll, you are not only restricting access to the file, you are also denying even the validation for the existence of the file. An attacker won’t be able to tell the difference between a file that doesn’t exist and file to which he cannot gain access.

Some applications, either directly or indirectly, allow a user to influence the name of the file created or accessed on the Web server’s operating system. This might happen through a Web site that creates a file for the user to download based on the user’s username. This is a security risk because a user can choose a username that may be unsafe for a file system. For example, what if the user chose c:\ for her username? There is a possibility that the Web application will overwrite an important operating system file when it attempts to create a file based on that username. Or maybe your application doesn’t try to write a file, but it reads a file instead based on the username or some other user-controlled input. An attacker may be able to specify any file on the Web application’s operating system and receive its contents through your Web page.

For these reasons, never base any file access on names that a user could influence. Instead, consider using a hash of a username or some other pseudorandom identifier that an attacker could not easily guess or manipulate.

If your Web application creates files on the operating system, take precautions to prevent a DoS attack. Evaluate the conditions that cause the Web application to create a file and what a user could do to abuse this system. For example, many banking applications allow you to download banking data for import into personal finance programs such as MS Money. In this scenario, you could ask yourself questions such as:

 What would happen if a user requests a thousand downloads every minute?

 For every request, does the application create a new file on the file system?

 Are there restrictions on how often a user can request a download?

 Does code automatically erase previous records when a user requests a new record?

 Are there restrictions on how much disk space each user can consume?

 What happens to your application—or platform, for that matter—when there is no disk space available for writing files?

Without a mechanism to prevent users from filling your file system with temporary files, an attacker could not only cause your application to fail but could bring down your entire operating system by consuming all the operating system’s disk space. Solving this problem is relatively easy now that you’re aware of it. First, always implement some kind of automatic notification when disk space is getting low. Second, restrict the quantity of files a user can create in an appropriate time interval. If a user tries to create more files than allowed, either remove the user’s previous files or deny the request.

As we’ve seen, an application that uses data files on their host operating system needs to take extra precautions. Start with locking down the accessed files using both IIS and NTFS permissions. Continue by filtering requests to sensitive file types to a “404- File Not Found” page, and don’t allow users to influence the names of files created on the server. Finally, protect your application and operating system against a file system-based DoS attack by restricting the number of files a user can create.

Security Policy

 Lock down your file system with both IIS and NTFS settings.

 Do not allow users to influence the name of files created on the server.

 Restrict the quantity and/or size of files users can create on the server.

Coding Standards Fast Track

Securing Database Drivers

Limiting the Attack Surface

 Remove or disable unused drivers from your database.

 Periodically check for and remove any new unused drivers, especially after updates or patches.

Securing Database Drivers

 Configure your database drivers to maximum security.

 Configure your database drivers to intelligently log access activity.

Securing Databases

Securing the Database Location

 Review your network topology and security needs to design a firewall layout best suited for your environment.

 Assume worst-case scenarios when designing your firewall layout.

Ensuring Least Privilege

 Always provide and utilize the minimum required access and permission

 Use firewalls to restrict inappropriate access.

 Use IPSec or SSL to restrict who can connect to your database.

Securing the Database

 Keep your database software up to date.

 Disable or remove unused features of your database.

 Strengthen weak default passwords and permissions.

Writing Secure Data Access Code

Connecting to the Data Source

 Use roles to apply the rule of least privileged accounts.

 Use Windows Authentication whenever possible.

 Keep database connection strings secure.

 Set strong passwords for the sa and db_ accounts. Do not use these accounts in your applications.

Preventing SQL Injection

 Code a variety of protections against SQL injection, not just one method.

 Escape user input upon insertion into and retrieval from the database.

 Use SqlParameters to type and length-check user input.

 Process and resolve all errors on the server side.

 Enforce the rule of least privilege in the code and in the database account.

Writing Secure SQL

 Retrieve the minimum required data from the database.

 Check result sets for expected attributes.

 Use coding structures that emphasize security.

Reading and Writing to Data Files

 Lock down your file system with both IIS and NTFS settings.

 Do not allow users to influence the name of files created on the server.

 Restrict the quantity and/or size of files users can create on the server.

Code Audit Fast Track

Securing Database Drivers

Limiting the Attack Surface

 Has either the software engineering or IT team removed all extraneous drivers before the database reaches a production environment?

 Is there a policy in place to periodically check for software security updates and patches?

Securing Database Drivers

 Is the database driver(s) you are using set to run in the most secure context available, such as Sandbox mode for Jet drivers?

 Is IIS set to record Web server activity?

 Are the database drivers recording login attempts?

Securing Databases

Securing the Database Location

 Are you using firewalls to restrict access to your application?

 Have you evaluated whether you should place the data source in the same environment as the Web server or separated from the Web server behind another firewall?

Ensuring Least Privilege

 Do the users, applications, and processes have the minimum required permissions to complete their functions?

 Are firewalls restricting the ports available for communication to the smallest required set?

 Are you using either IPSec or SSL to restrict which computers can communicate with your database?

Securing the Database

 Have you strengthened the sa account’s password?

 Have you removed extended stored procedures and netlibs you are not using?

 Have you removed all sample databases, sample stored procedures, and sample code from the database before using it in a production environment?

Writing Secure Data Access Code

Connecting to the Data Source

 Have you carefully evaluated which authentication method to use and chosen Windows Authentication if feasible?

 Are your connection strings protected using encryption and ACLs where applicable?

 Have you created and applied roles, groups, and permissions to appropriately restrict the access of your database users?

Preventing SQL Injection

 Do the software engineering and programming teams understand the mechanics of an SQL injection attack?

 Are there various overlapping mechanisms in the code to prevent SQL attacks, such as escaping and filtering input, use of SqlParameters, and properly processing errors on the server side?

 Are there policies in place to periodically research the latest SQL injection attacks to ensure that your code is still protected from new attacks?

Writing Secure SQL

 Does query code retrieve the minimum set of required data from the database?

 Depending on the needed security of the module, have additional security checks been applied, such as expected result set size or content parameters?

 Has the software engineer written code structured to maximize security?

Reading and Writing to Data Files

 Has the application’s file system been locked down using both NTFS and IIS permissions?

 If your application creates or reads files on the server, is the user prevented from influencing the name of the file created or read?

 If your application creates files based on user action, have precautions been implemented to prevent a user from using excessive amounts of disk space?

Frequently Asked Questions

The following Frequently Asked Questions, answered by the authors of this book, are designed to both measure your understanding of the concepts presented in this chapter and to assist you with real-life implementation of these concepts. To have your questions about this chapter answered by the author, browse to and click on the “Ask the Author” form. You will also gain access to thousands of other FAQs at

Q: Does SQL injection affect all databases or just SQL Server? Is any database more secure than another.

A: All database servers are vulnerable to SQL injection to some extent. The actual risk depends on many factors, including server features, default configuration, complexity, documentation, etc. SQL injection is more of a code issue than a database issue. If you properly filter input and follow the best practices covered in this chapter, the capabilities or vulnerabilities of the backend database should have little consequence.

Q: Are there any quick black box tests I can run to see if my application is vulnerable to SQL injection?

A:  It is much more difficult to fully identify SQL injection vulnerabilities from the outside, they usually require a thorough code review. However, there are some checks to quickly identify SQL injection vulnerabilities. Find some form of user input, such as a web form, a query string parameter, or a cookie, and try inserting invalid characters such as a single quote or a semicolon. If you see an actual database error, chances are it is vulnerable to SQL injection. Another favorite is entering the string ‘ or 1=1-- into a web login form. Poorly written login code will sometimes accept this and log you in as the first user listed in the database.

Q: When choosing a SQL Server authentication strategy, when should I use Windows Authentication and when should I use Windows and SQL Server Authentication (mixed)?

A: As a general rule, using Windows Authentication provides far more benefits, including a more robust authentication and authorization infrastructure, the ability to keep credentials out of connection strings, and the administrative benefits of not having to maintain a new security model (SQL Server’s native authentication and authorization mechanisms). You can simply grant SQL Server access to any Windows group(s) that need access and apply permissions accordingly. A common argument for using the mixed security model is that connection pooling is defeated when users have their own security context. Connection pooling is the ability to recycle established database connections, thus increasing connection speed for newer connections. However, in Web-based applications, it is likely that all users will share a single user context anyway when performing data access. It makes no difference from a connection pooling perspective whether the account performing the data access is a single Windows account or a native SQL Server account.