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
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:
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.
Change the password in Machine.config (usually found at C:\Windows\Microsoft.NET\Framework\<Framework version>\CONFIG) at the processModel element to match. Example:
<processModel userName="machine"
password="wh!t3Rabitt..hop..hop">
Protect the machine.config file from unauthorized access by using ACLs.
Create this same account (a mirrored account) on the database server.
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.
SqlConnection sqlConnection = new SqlConnection("server=apollo;" +
"database= EmployeePersonalInformation;" +
"Integrated Security=SSPI;");
Figure 6.7: Windows Authentication [C#]
Dim sqlConnection = New SqlConnection( _
"server=apollo; database=EmployeePersonalInformation; " + _
"Integrated Security=SSPI;")
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.
string SqlConnectionString = "Server=apollo;" +
"Database=EmployeePersonalInformation;" +
"uid=colsen;pwd=g0ld3n.GREMlin;";
Figure 6.9: SQL Authentication Connection String [C#]
Dim SqlConnectionString = New String("Server=apollo;" + _
"Database=EmployeePersonalInformation;" + _
"uid=colsen;pwd=g0ld3n.GREMlin;")
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 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.
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.
Warning | 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
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).
string queryString = "select
* from Accounts where username =
''''''''''''''''" +
Request.QueryString["username"] +
"'''''''''''''''' and password=''''''''''''''''" +
Request.QueryString["password"] + "''''''''''''''''";
Figure 6.11: Common Query String [C#]
Dim queryString = New String("select *
from Accounts " + _
"where username = ''''''''''''''''" + Request.QueryString
["username"] + _
"'''''''''''''''' and password=''''''''''''''''" + Request.QueryString["password"] +
"''''''''''''''''")
Figure 6.12: Common Query String [VB.NET]
Warning | 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:
select * from Accounts where username = ''''''''''''''''chris'''''''''''''''',
password=''''''''''''''''Gob.stop.er.112''''''''''''''''
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:
hahaha
and for a password:
''''''''''''''''; drop table Accounts
This means that the following query runs against the database:
select * from Accounts where username = ''''''''''''''''hahaha'''''''''''''''',
password=''''''''''''''''''''''''''''''''; drop table Accounts
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:
'''''''''''''''' union select min(username),1 from Accounts where
username > 1 --
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
or
''''''''''''''''; insert into Accounts (username, password) values
(''''''''''''''''hahaha'''''''''''''''', ''''''''''''''''0wn3d'''''''''''''''')
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.
private string escapeQuoteCharacter(string stringToEscape)
{
return stringToEscape.Replace("''''''''''''''''", "''''''''''''''''''''''''''''''''");
}
Figure 6.16: Escaping the ‘ Character [C#]
Private Function escapeQuoteCharacter(ByVal stringToEscape As String) _
As String
Return stringToEscape.Replace("''''''''''''''''", "''''''''''''''''''''''''''''''''")
End Function
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:
select email from emailAddress where username=''''''''''''''''Timebomb'''''''''''''''';
drop table _ Accounts--''''''''''''''''
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.
SqlDataAdapter command =
new SqlDataAdapter("select password from Accounts " +
"where password=@password", conn);
SqlParameter sqlParameter =
command.SelectCommand.Parameters.Add("@password",
SqlDbType.VarChar, 8);
sqlParameter.Value = Request.Form["username"];
Figure 6.18: Using SqlParameters in Building SQL Statements (C#)
Dim command = New SqlDataAdapter("select password " + _
"from Accounts where password=@password", conn)
Dim sqlParameter = command.SelectCommand.Parameters.Add( _
"@password", SqlDbType.VarChar, 8)
sqlParameter.Value = Request.Form["username"]
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.
SqlDataAdapter command = new SqlDataAdapter("AccountInsert", conn);
command.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter sqlParameter =
command.SelectCommand.Parameters.Add("@username",
SqlDbType.DateTime, 8);
sqlParameter.Value = Request.Form["username"];
Figure 6.20: Using SqlParameters in Calling Stored Procedures (C#)
Dim command = New SqlDataAdapter("AccountInsert", conn)
command.SelectCommand.CommandType = CommandType.StoredProcedure
Dim sqlParameter = _
command.SelectCommand.Parameters.Add("@username", _
SqlDbType.DateTime, 8)
sqlParameter.Value = Request.Form["username"];
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:
''''''''''''''''; insert into Accounts (username, password) values
(''''''''''''''''hahaha'''''''''''''''', ''''''''''''''''0wn3d'''''''''''''''')
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.
private bool containsBadData(string stringToCheck)
{
string[] badData = new string[]
{ "drop", "delete", "insert", "update" };
for (int x=0; x < badData.Length; x++)
{
if (stringToCheck.IndexOf(badData[x]) > -1)
return true;
}
return false;
}
Figure 6.22: Filtering Dangerous SQL Commands (C#)
Private Function containsBadData(_
ByVal stringToCheck As String) As Boolean
Dim badData = _
New String() {"drop", "delete", "insert", "update"}
For x As Integer = 10 To badData.Length
If (stringToCheck.IndexOf(badData(x)) > -1) Then
Return True
End If
Next
Return False
End Function
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:
www.nextgenss.com/papersl
www.governmentsecurity.org/articles/_SQLInjectionModesofAttackDefenceandWhyItMatters.php
www.owasp.org
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).
SqlDataAdapter adapter =
new SqlDataAdapter("select username from Accounts " +
"where username=@username and password=@password", cn);
SqlParameter sqlParameter =
adapter.SelectCommand.Parameters.Add("@username",
SqlDbType.VarChar,15);
adapter.SelectCommand.Parameters["@username"].Value =
username.Text;
sqlParameter =
adapter.SelectCommand.Parameters.Add("@password",
SqlDbType.VarChar,8);
adapter.SelectCommand.Parameters["@password"].Value =
password.Text;
DataSet ds = new DataSet();
adapter.Fill(ds, "authenticate");
if (ds.Tables["authenticate"].Rows.Count == 1)
{
authenticated();
}
else
{
rejected();
}
Figure 6.24: Common Authentication Code (C#)
Dim adapter = New SqlDataAdapter("select username " + _
"from Accounts where username=@username and " + _
"password=@password", cn)
Dim sqlParameter = _
adapter.SelectCommand.Parameters.Add("@username", _
SqlDbType.VarChar, 15)
adapter.SelectCommand.Parameters("@username").Value = _
username.Text
sqlParameter = adapter.SelectCommand.Parameters.Add( _
"@password", SqlDbType.VarChar, 8)
adapter.SelectCommand.Parameters("@password").Value = _
password.Text
Dim ds = New DataSet
adapter.Fill(ds, "authenticate")
If ds.Tables("authenticate").Rows.Count = 1 Then
authenticated()
Else
rejected()
End If
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.
SqlDataAdapter adapter =
new SqlDataAdapter("select password from Accounts " +
"where username=@username", cn);
SqlParameter sqlParameter =
adapter.SelectCommand.Parameters.Add("@username",
SqlDbType.VarChar,0);
adapter.SelectCommand.Parameters["@username"].Value =
username.Text;
DataSet ds = new DataSet();
adapter.Fill(ds, "authenticate");
if (ds.Tables["authenticate"].Rows.Count == 1 &&
password.Text == ds.Tables["authenticate"].Rows[0]
["password"].ToString())
{
authenticated();
}
else
{
rejected();
}
Figure 6.26: Improved Authentication Code (C#)
Dim adapter = New SqlDataAdapter("select password " + _
"from Accounts where username=@username", cn)
Dim sqlParameter = _
adapter.SelectCommand.Parameters.Add("@username", _
SqlDbType.VarChar, 15)
adapter.SelectCommand.Parameters("@username").Value = _
username.Text
Dim ds = New DataSet
adapter.Fill(ds, "authenticate")
If ds.Tables("authenticate").Rows.Count = 1 And _
password.Text = ds.Tables("authenticate").Rows(0) _
("password").ToString() Then
authenticated()
Else
rejected()
End If
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
Tip | 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 www.xato.net//image/library/english/10194_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.

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:\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:
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.