Data Security

Stop SQL Injection Attacks Before They Stop You

Paul Litwin

Code download available at:SQLInjection.exe(153 KB)

Contents

Good SQL Gone Bad
Equal Opportunity Hacks
All Input is Evil
Avoid Dynamic SQL
Execute with Least Privilege
Store Secrets Securely
Failing Gracefully
Conclusion

Armed with advanced server-side technologies like ASP.NET and powerful database servers such as Microsoft® SQL Server™, developers are able to create dynamic, data-driven Web sites with incredible ease. But the power of ASP.NET and SQL can easily be used against you by hackers mounting an all-too-common class of attack—the SQL injection attack.

The basic idea behind a SQL injection attack is this: you create a Web page that allows the user to enter text into a textbox that will be used to execute a query against a database. A hacker enters a malformed SQL statement into the textbox that changes the nature of the query so that it can be used to break into, alter, or damage the back-end database. How is this possible? Let me illustrate with an example.

Good SQL Gone Bad

Many ASP.NET applications use a form like the one shown in Figure 1 to authenticate users. When a user clicks the Login button of BadLogin.aspx, the cmdLogin_Click method attempts to authenticate the user by running a query that counts the number of records in the Users table where UserName and Password match the values that the user has entered into the form's textbox controls.

Figure 1 BadLogin.aspx.cs

private void cmdLogin_Click(object sender, System.EventArgs e) { string strCnx = "server=localhost;database=northwind;uid=sa;pwd=;"; SqlConnection cnx = new SqlConnection(strCnx); cnx.Open(); //This code is susceptible to SQL injection attacks. string strQry = "SELECT Count(*) FROM Users WHERE UserName='" + txtUser.Text + "' AND Password='" + txtPassword.Text + "'"; int intRecs; SqlCommand cmd = new SqlCommand(strQry, cnx); intRecs = (int) cmd.ExecuteScalar(); if (intRecs>0) { FormsAuthentication.RedirectFromLoginPage(txtUser.Text, false); } else { lblMsg.Text = "Login attempt failed."; } cnx.Close(); }

In most cases, the form works exactly as intended. A user enters a user name and password that matches a record in the Users table. A dynamically generated SQL query is used to retrieve the number of matching rows. The user is then authenticated and redirected to the requested page. Users who enter an invalid user name and/or password are not authenticated. However, here it is also possible for a hacker to enter the following seemingly innocuous text into the UserName textbox to gain entry to the system without having to know a valid user name and password:

' Or 1=1 --

The hacker breaks into the system by injecting malformed SQL into the query. This particular hack works because the executed query is formed by the concatenation of a fixed string and values entered by the user, as shown here:

string strQry = "SELECT Count(*) FROM Users WHERE UserName='" + txtUser.Text + "' AND Password='" + txtPassword.Text + "'";

In the case of the user entering a valid user name of "Paul" and a password of "password", strQry becomes:

SELECT Count(*) FROM Users WHERE UserName='Paul' AND Password='password'

But when the hacker enters

' Or 1=1 --

the query now becomes:

SELECT Count(*) FROM Users WHERE UserName='' Or 1=1 --' AND Password=''

Because a pair of hyphens designate the beginning of a comment in SQL, the query becomes simply:

SELECT Count(*) FROM Users WHERE UserName='' Or 1=1

The expression 1=1 is always true for every row in the table, and a true expression or'd with another expression will always return true. So, assuming there's at least one row in the Users table, this SQL will always return a nonzero count of records.

Not all SQL injection attacks involve forms authentication. All it takes is an application with some dynamically constructed SQL and untrusted user input. Given the right conditions, the extent of damage caused by such an attack may be limited only by the extent of the hacker's knowledge of the SQL language and the database configuration.

Now consider the code shown in Figure 2, taken from BadProductList.aspx. This page displays products from the Northwind database and allows users to filter the resulting list of products using a textbox called txtFilter. Like the last example, the page is ripe for SQL injection attacks because the executed SQL is constructed dynamically from a user-entered value. This particular page is a hacker's paradise because it can be hijacked by the astute hacker to reveal secret information, change data in the database, damage the database records, and even create new database user accounts.

Figure 2 BadProductList.aspx.cs

private void cmdFilter_Click(object sender, System.EventArgs e) { dgrProducts.CurrentPageIndex = 0; bindDataGrid(); } private void bindDataGrid() { dgrProducts.DataSource = createDataView(); dgrProducts.DataBind(); } private DataView createDataView() { string strCnx = "server=localhost;uid=sa;pwd=;database=northwind;"; string strSQL = "SELECT ProductId, ProductName, " + "QuantityPerUnit, UnitPrice FROM Products"; //This code is susceptible to SQL injection attacks. if (txtFilter.Text.Length > 0) { strSQL += " WHERE ProductName LIKE '" + txtFilter.Text + "'"; } SqlConnection cnx = new SqlConnection(strCnx); SqlDataAdapter sda = new SqlDataAdapter(strSQL, cnx); DataTable dtProducts = new DataTable(); sda.Fill(dtProducts); return dtProducts.DefaultView; }

Most SQL-compliant databases, including SQL Server, store metadata in a series of system tables with the names sysobjects, syscolumns, sysindexes, and so on. This means that a hacker could use the system tables to ascertain schema information for a database to assist in the further compromise of the database. For example, the following text entered into the txtFilter textbox might be used to reveal the names of the user tables in the database:

' UNION SELECT id, name, '', 0 FROM sysobjects WHERE xtype ='U' --

The UNION statement in particular is useful to a hacker because it allows him to splice the results of one query onto another. In this case, the hacker has spliced the names of the user tables in the database to the original query of the Products table. The only trick is to match the number and datatypes of the columns to the original query. The previous query might reveal that a table named Users exists in the database. A second query could reveal the columns in the Users table. Using this information, the hacker might enter the following into the txtFilter textbox:

' UNION SELECT 0, UserName, Password, 0 FROM Users --

Entering this query reveals the user names and passwords found in the Users table, as shown in Figure 3.

Figure 3** Querying the Users Table **

SQL injection attacks can also be used to change data or damage the database. The SQL injection hacker might enter the following into the txtFilter textbox to change the price of the first product from $18 to $0.01 and then quickly purchase a few cases of the product before anyone notices what has happened:

'; UPDATE Products SET UnitPrice = 0.01 WHERE ProductId = 1--

This hack works because SQL Server allows you to string together multiple SQL statements separated by either a semicolon or a space. In this example, the DataGrid displays nothing, but the update query runs successfully. This same technique might be used to execute a DROP TABLE statement or to execute a system stored procedure that created a new user account and added that user to the sysadmin role. These hacks are all possible using the BadProductList.aspx page shown in Figure 2.

Equal Opportunity Hacks

It's important to realize that the SQL injection attacks are not limited to SQL Server. Other databases, including Oracle, MySQL, DB2, Sybase, and others are susceptible to this type of attack. SQL injection attacks are possible because the SQL language contains a number of features that make it quite powerful and flexible, namely:

• The ability to embed comments in a SQL statement using a pair of hyphens
• The ability to string multiple SQL statements together and to execute them in a batch
• The ability to use SQL to query metadata from a standard set of system tables

In general, the more powerful the dialect of SQL supported by the database, the more susceptible the database is to attack. Thus, it's no surprise that SQL Server is a popular target for the injection attack.

SQL injection attacks are not limited to ASP.NET applications. Classic ASP, Java, JSP, and PHP applications are equally at risk. In fact, SQL injection attacks can be wielded against desktop applications as well. For example, I have included in the download files for this article (available from the link at the top of this article) a sample Windows® Forms application named SQLInjectWinForm that is also susceptible to SQL injection attacks.

While it's easy to point to one or two key measures for the prevention of the SQL injection attack, it's best to take a layered approach to the problem. This way, if one of your measures is circumvented because of some vulnerability, you are still protected. The recommended layers are summarized in Figure 4.

Figure 4 Preventing SQL Injection Attacks

All Input is Evil

The first principle listed in Figure 4 is extremely important: assume that all user input is evil! You should never use unvalidated user input in a database query. The ASP.NET validation controls—especially the RegularExpressionValidator control—are a good tool for validating user input.

There are two basic approaches to validation: disallow troublesome characters or only allow a small number of required characters. While you can easily disallow a few troublesome characters, such as the hyphen and single quote, this approach is less than optimal for two reasons: first, you might miss a character that is useful to hackers, and second, there is often more than one way to represent a bad character. For example, a hacker may be able to escape a single quote so that your validation code misses it and passes the escaped quote to the database, which treats it the same as a normal single quote character. A better approach is to identify the allowable characters and allow only those characters. This approach requires more work but ensures a much tighter control on input and is more safe. Regardless of which approach you take, you'll also want to limit the length of the entry because some hacks require a large number of characters.

GoodLogin.aspx (also found in the code download) contains two regular expression validator controls, one for user name and the other for password, with the following ValidationExpression value that limits entries to between 4 and 12 characters of digits, alphabetic characters, and the underscore:

[\d_a-zA-Z]{4,12}

Injection Testing

From a test perspective, it's important to understand that the primary goal of a SQL injection attack is to attempt to manipulate queries or information sent to a SQL backend in order to gain control of that SQL server. To test for this vulnerability, you must first understand how the vulnerability is exploited and then try the following tricks as tests of your own:

Look for a Vulnerability

Hackers begin by looking for vulnerability on a site. These vulnerabilities could be just about anything that accepts input including a search string, a form, or ASP, JSP, CGI, or PHP pages. They also include hidden fields—not only fields displayed in the UI. Anything that can be seen in the source is as vulnerable as what is openly displayed.

See if it's Really Vulnerable

To test the vulnerability, the hacker often starts with the most common vulnerability: the single quote or apostrophe character. This character is the string literal delimiter in SQL. If user input is submitted directly to the back-end server without sufficient validation or scrubbing, it then becomes incredibly easy to gain control of the DBMS.

In order to test for this particular vulnerability, the hacker simply inserts something like

jo'hn

into a text field or into the URL (https://sample/index.asp?id=jo'hn).

If the hacker is working with a hidden field in the source, it's only a tiny bit more complicated in that the source needs to be downloaded from the site and saved. Then the URL and the hidden field need to be modified, and the source executed.

If this is a true vulnerability, the additional information after the apostrophe will be treated as a part of the query string submitted to the SQL server and will be executed by the back end.

Evaluate the Errors

If a hacker knows what to look for, error pages are a great tool to diagnose and refine hacking attempts because of the information they can provide. If an ODBC Error page is returned, the hacker knows right away that this is a true vulnerability. These errors are generated by the database system, and that means the single quote inserted in the prior step was successfully passed to the back-end database.

If the hacker gets a different error, he will then look carefully through the source of the error page for any references to "SQL Server", "ODBC", or "syntax". Sometimes the details of the error are hidden in the headers or in comments that are only visible in the source. If an error page is returned that includes links, the hacker will search the links to see if there are more error details.

If a "302 Page Redirect" is called, the hacker will try to capture the page he is redirected from. This may have more error details in it that can't be easily read before the redirect occurs. If a "500 error page" is returned, the hacker knows that the injection may have been successful as this is the default error page for most Web servers. Though the hacker may not be able to determine much from this page, it will encourage greater attempts to exploit this possible weakness.

Continuing to Test

The hacker will continue to try other options for bypassing the site's validation and scrubbing routines, in each case carefully examining the server's response. These attempts include:

• Using a single quote in different places in the string to try to take advantage of any ordered validation routines. For example, an e-mail field may be validated for having an @ symbol and a period. If the hacker entered joe'@mysite.com it would fail that validation, but joe@mysite.com' would succeed and expose the vulnerability.
• Using a single quote at the end of a max length string. If the site is escaping single quotes, an attempt to escape the single quote could result in truncation back to the single quote.
• Using two dashes. In SQL Server, this indicates a single-line comment and may cause the server to ignore the remainder of the line.
• Using a semicolon. This indicates to SQL Server that a new command follows and can allow another query to piggyback the prior query.
• Using high Unicode characters that are often downgraded to ASCII "equivalents," including the dangerous single quote.
• Using all of these techniques not just in string fields, but in all fields in case there is any implicit translation being done, or the only format enforcement is through the UI.
• Using a # character. Sometimes you'll find this used as a date/time delimiter.
• Using char equivalents of the suspicious characters.

You may need to allow the user to enter potentially injurious characters into a textbox. For example, users may need to enter a single quote (or apostrophe) as part of a person's name. In such cases, you can render the single quote harmless by using a regular expression or the String.Replace method to replace each instance of the single quote with two single quotes. For example:

string strSanitizedInput = strInput.Replace("'", "''");

Avoid Dynamic SQL

The SQL injection attacks I have demonstrated in this article are all dependent on the execution of dynamic SQL—that is, SQL statements constructed by the concatenation of SQL with user-entered values. Using parameterized SQL, however, greatly reduces the hacker's ability to inject SQL into your code.

The code in Figure 5 employs parameterized SQL to stop injection attacks. Parameterized SQL is great if you absolutely must use ad hoc SQL. This might be necessary if your IT department doesn't believe in stored procedures or uses a product such as MySQL which didn't support them until version 5.0. If at all possible, however, you should employ stored procedures for the added ability to remove all permissions to the base tables in the database and thus remove the ability to create queries like the one shown in Figure 3. BetterLogin.aspx, shown in Figure 6, uses a stored procedure, procVerifyUser, to validate users.

Figure 6 BetterLogin.aspx.cs

private void cmdLogin_Click(object sender, System.EventArgs e) { string strCnx = ConfigurationSettings.AppSettings["cnxNWindBetter"]; using (SqlConnection cnx = new SqlConnection(strCnx)) { SqlParameter prm; cnx.Open(); string strAccessLevel; SqlCommand cmd = new SqlCommand("procVerifyUser", cnx); cmd.CommandType= CommandType.StoredProcedure; prm = new SqlParameter("@username",SqlDbType.VarChar,50); prm.Direction=ParameterDirection.Input; prm.Value = txtUser.Text; cmd.Parameters.Add(prm); prm = new SqlParameter("@password",SqlDbType.VarChar,50); prm.Direction=ParameterDirection.Input; prm.Value = txtPassword.Text; cmd.Parameters.Add(prm); strAccessLevel = (string) cmd.ExecuteScalar(); if (strAccessLevel.Length>0) { FormsAuthentication.RedirectFromLoginPage(txtUser.Text, false); } else { lblMsg.Text = "Login attempt failed."; } } }

Figure 5 GoodLogin.aspx.cs

private void cmdLogin_Click(object sender, System.EventArgs e) { string strCnx = ConfigurationSettings.AppSettings["cnxNWindBad"]; using (SqlConnection cnx = new SqlConnection(strCnx)) { SqlParameter prm; cnx.Open(); string strQry = "SELECT Count(*) FROM Users WHERE UserName=@username " + "AND Password=@password"; int intRecs; SqlCommand cmd = new SqlCommand(strQry, cnx); cmd.CommandType= CommandType.Text; prm = new SqlParameter("@username",SqlDbType.VarChar,50); prm.Direction=ParameterDirection.Input; prm.Value = txtUser.Text; cmd.Parameters.Add(prm); prm = new SqlParameter("@password",SqlDbType.VarChar,50); prm.Direction=ParameterDirection.Input; prm.Value = txtPassword.Text; cmd.Parameters.Add(prm); intRecs = (int) cmd.ExecuteScalar(); if (intRecs>0) { FormsAuthentication.RedirectFromLoginPage(txtUser.Text, false); } else { lblMsg.Text = "Login attempt failed."; } } }

Execute with Least Privilege

One of the bad practices demonstrated in BadLogin.aspx and BadProductList.aspx is the use of a connection string that employs the sa account. Here's the connection string, which can be found in Web.config:

<add key="cnxNWindBad" value="server=localhost;uid=sa;pwd=;database=northwind;" />

This account runs under the System Administrators role which means it is allowed to do just about anything—creating logins and dropping databases are just a few. Suffice it to say, it is a very bad idea to be using the sa (or any high-privileged account) for application database access. It is a much better idea to create a limited access account and use that instead. The account used in GoodLogin.aspx uses the following connection string:

<add key="cnxNWindGood" value="server=localhost;uid=NWindReader;pwd=utbbeesozg4d; database=northwind;" />

The NWindReader account runs under the db_datareader role, which limits its access to the reading of tables in the database. BetterLogin.aspx improves the situation by using a stored procedure and a login, WebLimitedUser, which only has rights to execute that stored procedure and no rights to the underlying tables.

Store Secrets Securely

The SQL injection attack shown in Figure 3 resulted in the display of user names and passwords from the Users table. This sort of table is commonly used when employing forms authentication, and in many applications the passwords are stored as clear text. A better alternative is to store encrypted or hashed passwords in the database. Hashed passwords are more secure than encrypted passwords because they can't be decrypted. You can harden a hashed password further by adding salt (a cryptographically secure random value) to the hash. BestLogin.aspx contains code that compares the user-entered password with a salted hashed version of the password stored in the SecureUsers table (see Figure 7). The other piece to the hashed puzzle is AddSecureUser.aspx. This page can be used to generate the salted hashed passwords and store them in the SecureUsers table.

Figure 7 BestLogin.aspx.cs

private void cmdLogin_Click(object sender, System.EventArgs e) { try { // Grab the encrypted connection string and decrypt it string strCnx = SecureConnection.GetCnxString("cnxNWindBest"); // Establish connection to database using (SqlConnection cnx = new SqlConnection(strCnx)) { SqlParameter prm; cnx.Open(); // Execute sproc to retrieved hashed password for this user string strHashedDbPwd; SqlCommand cmd = new SqlCommand("procGetHashedPassword", cnx); cmd.CommandType = CommandType.StoredProcedure; prm = new SqlParameter("@username", SqlDbType.VarChar,50); prm.Direction = ParameterDirection.Input; prm.Value = txtUser.Text; cmd.Parameters.Add(prm); strHashedDbPwd = (string) cmd.ExecuteScalar(); if (strHashedDbPwd.Length>0) { // Verify that hashed user-entered password is the same // as the hashed password from the database if (SaltedHash.ValidatePassword(txtPassword.Text, strHashedDbPwd)) { FormsAuthentication.RedirectFromLoginPage( txtUser.Text, false); } else { lblMsg.Text = "Login attempt failed."; } } else { lblMsg.Text = "Login attempt failed."; } } } catch { lblMsg.Text = "Login attempt failed."; } }

BestLogin.aspx and AddSecureUser.aspx both use code from the SaltedHash class library, as shown in Figure 8. This code, which was created by Jeff Prosise, uses the FormsAuthentication.HashPasswordForStoringInConfigFile method from the System.Web.Security namespace to create password hashes and the RNGCryptoServiceProvider.GetNonZeroBytes method from the System.Security.Cryptography namespace to create a random 16-byte salt value (which becomes 24 characters when converted to a string using Convert.ToBase64String).

Figure 8 SaltedHash Class

using System; using System.Web.Security; using System.Security.Cryptography; public class SaltedHash { static public bool ValidatePassword (string password, string saltedHash) { // Extract hash and salt string const int LEN = 24; string saltString = saltedHash.Substring(saltedHash.Length - LEN); string hash1 = saltedHash.Substring(0, saltedHash.Length - LEN); // Append the salt string to the password string saltedPassword = password + saltString; // Hash the salted password string hash2 = FormsAuthentication.HashPasswordForStoringInConfigFile( saltedPassword, "SHA1"); // Compare the hashes return (hash1.CompareTo(hash2) == 0); } static public string CreateSaltedPasswordHash (string password) { // Generate random salt string RNGCryptoServiceProvider csp = new RNGCryptoServiceProvider(); byte[] saltBytes = new byte[16]; csp.GetNonZeroBytes(saltBytes); string saltString = Convert.ToBase64String(saltBytes); // Append the salt string to the password string saltedPassword = password + saltString; // Hash the salted password string hash = FormsAuthentication.HashPasswordForStoringInConfigFile( saltedPassword, "SHA1"); // Append the salt to the hash return hash + saltString; } }

While not directly related to SQL injection attacks, BestLogin.aspx demonstrates another security best practice: the encryption of connection strings. Securing the connection string is especially important if it contains an embedded database account password, as is the case in BestLogin.aspx. Since you will need the decrypted version of a connection string to connect to the database, you can't hash a connection string. You will need to encrypt it, instead. Here's what the encrypted connection string stored in Web.config and used by BestLogin.aspx looks like:

<add key="cnxNWindBest" value="AQAAANCMnd8BFdERjHoAwE/ Cl+sBAAAAcWMZ8XhPz0O8jHcS1539LAQAAAACAAAAAAADZgAAqAAAABAAAABdodw0YhWfcC6+ UjUUOiMwAAAAAASAAACgAAAAEAAAALPzjTRnAPt7/W8v38ikHL5IAAAAzctRyEcHxWkzxeqbq/ V9ogaSqS4UxvKC9zmrXUoJ9mwrNZ/ XZ9LgbfcDXIIAXm2DLRCGRHMtrZrp9yledz0n9kgP3b3s+ X8wFAAAANmLu0UfOJdTc4WjlQQgmZElY7Z8" />

BestLogin calls the GetCnxString method from the SecureConnection class, shown in Figure 9, to retrieve the cnxNWindBest AppSetting value and decrypt it with this code:

string strCnx = SecureConnection.GetCnxString("cnxNWindBest");

Figure 9 SecureConnection.cs

public class SecureConnection { static public string GetCnxString(string configKey) { string strCnx; try { // Grab encrypted connection string from web.config string strEncryptedCnx = ConfigurationSettings.AppSettings[configKey]; // Decrypt the connection string DataProtector dp = new DataProtector(DataProtector.Store.USE_MACHINE_STORE); byte[] dataToDecrypt = Convert.FromBase64String(strEncryptedCnx); strCnx = Encoding.ASCII.GetString(dp.Decrypt(dataToDecrypt,null)); } catch { strCnx=""; } return strCnx; } }

The SecureConnection class in turn calls the DataProtect class library (not shown here but included in the download for this article), which wraps calls to the Win32® Data Protection API (DPAPI). One of the nice features of the DPAPI is that it manages the encryption key for you. For more information on the DataProtect class library, including additional options to consider when using it, see "Building Secure ASP.NET Applications: Authentication, Authorization, and Secure Communication," a Microsoft patterns and practices guide.

Figure 10** EncryptCnxString.aspx **

You can use the EncryptCnxString.aspx page to create the machine-specific encrypted connection string to paste into your configuration file. This page is shown in Figure 10. Of course, there are other secrets besides passwords and connection strings that you may want to encrypt or hash, including credit card numbers and anything else that might cause harm if revealed to the hacker. ASP.NET 2.0 includes a number of features which should simplify the hashing of passwords and the encryption of connection strings.

Failing Gracefully

The poor handling of runtime exceptions is another area that hackers will attempt to exploit. Thus, it is important to include exception handlers in all of your production code. In addition, handled and unhandled exceptions should always offer minimal information that might assist the hacker in his efforts. For handled exceptions, you need to strike a balance in your error messages between being helpful to the naive user and giving away too much information to the unscrupulous hacker.

For unhandled exceptions, you should make sure minimal help is offered to the hacker by setting the debug attribute of the compilation element (in the Web.config file) to false and setting the mode attribute of the customErrors element to either On or RemoteOnly. For example take a look at the following:

<compilation defaultLanguage="c#" debug="false" /> <customErrors mode="RemoteOnly" />

The RemoteOnly setting will ensure that users accessing the site from localhost will get informative error messages while those accessing the site from a remote location will receive generic error messages which reveal no useful information about the exception. Use the On setting to have all users, including local users, see the generic error messages. Never use the Off setting in a production environment.

Conclusion

SQL injection attacks are a serious concern for application developers as they can be used to break into supposedly secure systems and steal, alter, or destroy data. It's all too easy to leave yourself vulnerable to these attacks, regardless of which version of ASP.NET you are using. In fact, you don't even need to be using ASP.NET to be susceptible to SQL injection attacks. Any application that queries a database using user-entered data, including Windows Forms applications is a potential target of an injection attack.

Protecting yourself against SQL injection attacks is not very difficult. Applications that are immune to SQL injection attacks validate and sanitize all user input, never use dynamic SQL, execute using an account with few privileges, hash or encrypt their secrets, and present error messages that reveal little if no useful information to the hacker. By following a multi-layered approach to prevention you can be assured that if one defense is circumvented, you will still be protected. [For information on testing your application for injection vulnerabilities, see the sidebar "Injection Testing".]

Paul Litwin is a lead programmer with Fred Hutchinson Cancer Research Center in Seattle. He is the chair of the Microsoft ASP.NET Connections conference and the owner of Deep Training, a .NET training company. Reach Paul at https://www.deeptraining.com.