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.
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.
|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.
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.
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.
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.
|Summary:||Remove unused drivers from your database to reduce the number of attack vectors|
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.
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.
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.
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:
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.
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.
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 http://support.microsoft.com/default.aspx?scid=kb;en-us;Q245243.
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:
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.
|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.
|Summary:||Remove unused features and restrict default settings on your database to prevent successful attacks|
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:
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.
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.
|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.
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.
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.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 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.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 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 http://msdn.microsoft.com/library/en-us/dnnetsec/html/SecNetHT08.asp for more information on using DPAPI from ASP.NET.
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.
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 http://support.microsoft.com/default.aspx?scid=kb;en-us;Q271284.
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.
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.
|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.
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.12 Common Query String [VB.NET]
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.
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:
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.
The database will return the next column in the query, as shown in Figure 6.14.
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:
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.
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:
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.
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 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.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:
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.
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.19 Using SqlParameters in Building SQL Statements (VB.NET)
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.
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.
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.
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.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.
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:
|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.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.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.
|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.
From the Properties window that appears, make sure the Read and Write options are unchecked, as shown in Figure 6.29.
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.
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 www.xato.net/files/404.zip, 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.
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:\ntdetect.com 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:
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.
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 www.syngress.com/solutions and click on the “Ask the Author” form. You will also gain access to thousands of other FAQs at ITFAQnet.com.
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.
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.
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.