The issue is the same issue I pointed out in the last two chapters, and it’s the same issue in the next chapter: misplaced trust; trusting that the user has given your application well-formed data, when in fact the user has not. Let me give an example.

Many applications include code that looks something like the following. Go on, admit it—you have constructed SQL strings like this:

string sql = "select * from client where name = ’" + name + "‘"

The variable name is provided by the user. The problem with this SQL string is that the attacker can piggyback SQL statements in the name variable. Imagine input where name = Blake, which builds this totally benign SQL statement:

select * from client where name = ’Blake’

However, what if an attacker enters this: Blake’ or 1=1 --. The following malicious statement is built:

select * from client where name = ’Blake’ or 1=1 --

This statement will return all columns in table client for any row where the name column is Blake. It will also return any row that satisfies the 1=1 clause. Unfortunately for the good guys but good news for the bad guys is the fact that 1=1 is true for every row in the table, so the attacker sees all rows in the table. If you don’t think this is bad, imagine that the database table schema looks like that in Figure 12-1.

Figure 12-1. A client table schema containing credit card information.

The last part of the query is the "--" characters. These characters are a comment operator, which makes it easier for an attacker to build a valid, but malicious SQL statement. When the attacker is happy that the SQL statement or statements are complete, he places a comment operator at the end to comment out any characters added by the programmer.

The example I just showed is called SQL injection. This is an attack that changes the logic of a valid SQL statement—in this case, by adding an or clause to the statement. Not only can you alter a single SQL statement with this technique, you can add additional SQL statements and also call functions and stored procedures.

By default, some database servers allow a client application to perform more than one SQL statement at once. For example, in SQL Server, you can issue

select * from table1 select * from table2

and the two SQL select statements execute.

Attackers can have more fun than simply getting two SQL queries to execute; SQL engines include support for data manipulation constructs, such as the ability to create, delete (called drop), and update database objects such as tables, stored procedures, rules, and views. Take a look at the following "name" an attacker could enter:

Blake’ drop table client --

This builds a SQL query that queries for the name, Blake, and then drops or deletes the client table.

While demonstrating how to manipulate databases by using SQL injection at the Professional Developer’s Conference in 2001, I accidentally deleted my core demonstration table. Even though I ruined my demo, I think I made the point!

Now, you’re probably thinking how on earth can a user on the Internet, connecting to a back-end database from a Web server or Web service, possibly delete a table from a database. Well, look at this code:

string Status = "No";
string sqlstring = "";
try {
    SqlConnection sql= new SqlConnection(
        @"data source=localhost;" + 
        "user id=sa;password=password;");
    sql.Open();
    sqlstring="SELECT HasShipped" +
        " FROM detail WHERE ID=‘" + Id + "‘";
    SqlCommand cmd = new SqlCommand(sqlstring,sql);
    if ((int)cmd.ExecuteScalar() != 0)
        Status = "Yes";
} catch (SqlException se) {
    Status = sqlstring + " failed

";
    foreach (SqlError e in se.Errors) {
        Status += e.Message + "

";
    }
} catch (Exception e) {
    Status = e.ToString();
}

Can you spot the security flaws in this C# code? The first is obvious: the code creates SQL statements by using string concatenation, which will lead to SQL injection attacks. But there’s more. The connection identity from this Web service code to the back-end database is sa, the sysadmin account in SQL Server. You should never make a connection to any database server by using such a dangerous account; sa is to SQL Server what SYSTEM is to Windows NT and later. Both are, by far, the most capable and potentially damaging accounts in their respective systems. The same database admin account in Oracle is named internal.

The next error is the password to sa—let’s just say it could be broken by a six-year-old child! In addition, the fact that it’s embedded in the code is even worse. And here’s another error: if the code that handles the SQL connection fails for any reason, a complete description of how the failure occurred is given to the attacker, including what the SQL statement looked like when it failed. This aids the attacker immensely, as he can see the source of his errors.

Now let’s move on to "remedies" for such poor programming, and then we’ll look at real remedies.

Quoting the input is a method often proposed to solve the problem of database input issues, but it is definitely not a remedy. Let’s see how it’s used and why it’s bad. Look at this code fragment:

int age = ...; // age from user

string name = ...; // name from user
name = name.Replace("‘","‘‘");

SqlConnection sql= new SqlConnection(...);
sql.Open();
sqlstring=@"SELECT *" +
            " FROM client WHERE name= ’" + name + "‘ or age=" + age;
SqlCommand cmd = new SqlCommand(sqlstring,sql);

As you can see, the code replaces single quotes with two single quotes in the user’s input. So, if the attacker tries a name such as Michael’ or 1=1 --, the single quote (used by the attacker to close off the name) is escaped, rendering the attack useless because it leads to an invalid SQL statement before the comment operator:

select * FROM client WHERE ID = ’Michael’’ or 1=1 -- ’ or age=35 

However, this does not deter our wily attacker; instead, he uses the age field, which is not quoted, to attack the server. For example, age could be 35; shutdown --. There are no quotes, and the server is shut down. Note that using ";" is optional. 35 shutdown would work just as well, so don’t think parsing out ";" leads to safe SQL statements!

And just when you really thought you could use quotes, the attacker can use the char(0x27) function to hide the single quote in some circumstances. A variation is to use constructs such as this:

declare @a char(20) select @a=0x73687574646f776e exec(@a)

This construct, when added to another SQL query, calls the shutdown command. The hexadecimal sequence is the ASCII hex equivalent of the word shutdown.

Where am I going with this? Simply escaping a series of SQL commands might help, but it probably will not!

Many developers believe that calling stored procedures from an application also makes the application immune to SQL injection attacks. Wrong! Doing so prevents some kinds of attacks and not others. Here’s some sample code that calls a stored procedure named sp_GetName:

string name = ...; // name from user
SqlConnection sql= new SqlConnection(...);
sql.Open();
sqlstring=@"exec sp_GetName ’" + name + "‘";
SqlCommand cmd = new SqlCommand(sqlstring,sql);

Attempting to enter Blake' or 1=1 -- will fail because you cannot perform a join across a stored procedure call. The following is illegal SQL syntax:

exec sp_GetName ’Blake’ or 1=1 -- ’

However, performing data manipulation is perfectly valid:

exec sp_GetName ’Blake’ insert into client values(1005, ’Mike’) -- ’

This SQL command will fetch data about Blake and then insert a new row into the client table! As you can see, using stored procedures doesn’t make your code secure from SQL injection attacks.

I have to admit, the scariest example of using stored procedures for security reasons is a stored procedure that looks like this:

CREATE PROCEDURE sp_MySProc @input varchar(128)
AS
    exec(@input)

Guess what this code does? It simply executes whatever the user provided, even though the code is calling a stored procedure! Luckily, I’ve seen this only a couple of times.

As you can see, you need to be aware of pseudo remedies—they might help a little, but none of them are safe. Now let’s switch tactics and look at real remedies.

Earlier I pointed out the error of making connections to SQL Server, or any other database server, as sysadmin from an application such as Web service or a Web page. If you see a connection string that connects to the database as a sysadmin account, file a bug and get it fixed. You are violating the principles of least privilege and defense in depth if you use a sysadmin-like account to connect from your Web application to the database.

Most Web-based applications do not need the capabilities of sysadmin to run; most database-driven applications allow users to query data and, to a lesser extent, add and update their own data. If the connection is made as sysadmin and there is a bug in the SQL code, such as one that allows injection attacks, an attacker can perform any task sysadmin can, including the following:

The potential for damage is unlimited. One way to mitigate this issue is to support authenticated connections by using native operating system authentication and authorization by setting Trusted_Connection=True in the connection string. If you cannot use native authentication techniques—and sometimes you should not—you should create a specific database account that has just the correct privileges to read, write, and update the appropriate data in the database, and you should use that to connect to the database. This account should be regularly checked to determine what privileges it has in the database and to make sure an administrator has not accidentally given it capabilities that could compromise the system.

Perhaps the most dangerous aspect of running as sysadmin is the possibility that an attack could call any administrative stored procedure. For example, SQL Server includes extended stored procedures such as xp_cmdshell through which an attacker can invoke shell commands. Oracle databases include utl_file, which allows an attacker to read from and write to the file system.

Now let’s look at how to correctly build SQL statements. I’ve already told you how not to do it!

Building SQL strings in code is problematic, as I’ve demonstrated earlier in this chapter. A simple way to remedy this is to leave the completion of the SQL string to the database and to not attempt the SQL string construction in your code. Instead, you should use placeholders, which are often referred to as parameterized commands. When you define the query, you determine which parts of the SQL statement are the parameters. For example, the following is a parameterized version of a query:

SELECT count(*) FROM client WHERE name=? AND pwd=?

Next, we need to define what the parameters are; these are passed along with the skeletal SQL query to the SQL database for processing. The following Visual Basic Scripting Edition (VBScript) function outlines how to use SQL placeholders:

Function IsValidUserAndPwd(strName, strPwd)
    ’ Note I am using a trusted connection to SQL Server.
    ’ Never use uid=sa;pwd=
    strConn = "Provider=sqloledb;" + _
              "Server=server-sql;" + _
              "database=client;" + _
              "trusted_connection=yes"
    Set cn = CreateObject("ADODB.Connection")
    cn.Open strConn

    Set cmd = CreateObject("ADODB.Command")
    cmd.ActiveConnection = cn
    cmd.CommandText = _
        "select count(*) from client where name=? and pwd=?"
    cmd.CommandType = 1    ’ 1 means adCmdText
    cmd.Prepared = true

    ’ Explanation of numeric parameters: 
    ’ data type is 200, varchar string;
    ’ direction is 1, input parameter only;
    ’ size of data is 32 chars max. 
    Set parm1 = cmd.CreateParameter("name", 200, 1, 32, "")
    cmd.Parameters.Append parm1
    parm1.Value = strName

    Set parm2 = cmd.CreateParameter("pwd", 200, 1, 32, "")
    cmd.Parameters.Append parm2
    parm2.Value = strPwd

    Set rs = cmd.Execute
    IsValidUserAndPwd = false
    If rs(0).value = 1 Then IsValidUserAndPwd = true

    rs.Close
    cn.Close
End Function

Additionally, parameterized queries are faster than hand-constructing the SQL query in code. It’s not often you find an approach that’s both more secure and faster!

One prime benefit of using parameters is that you can define the parameter data type. For example, if you define a numeric parameter, the strong type checking will thwart most attacks because a SQL-based attack cannot be made purely from numbers. If your application uses open database connectivity (ODBC) and you want to use parameters, you need to use the SQLNumParams and SQLBindParam functions. If you use OLE DB, you can use the ICommandWithParameters interface. If your code is managed code, you can use the SqlCommand class.

declare @a varchar(20)
set @a=0x74735D27
select @a
set @a=quotename(@a)
select @a

set @a=‘ts]’’’
select @a
set @a=quotename(@a)
select @a

-- Test the code with these variables
declare @name varchar(64)
set @name = N’White’

-- Do the work
exec sp_executesql 
    N’select au_id from pubs.dbo.authors where au_lname=@lname’,
    N’@lname varchar(64)’,
    @lname = @name

Now that we’ve looked at some common mistakes and some best practices for securely building database applications, let’s look at a secure in-depth example. The following code, from a sample Web service written in C#, has multiple layers of defense. If one defensive mechanism fails, at least one other defense will protect the application and the data.

//
// SafeQuery
//

using System;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Security.Principal;
using System.Security.Permissions;
using System.Text.RegularExpressions;
using System.Threading;
using System.Web;
using Microsoft.Win32; 

...

[SqlClientPermissionAttribute(SecurityAction.PermitOnly,
     AllowBlankPassword=false)]
[RegistryPermissionAttribute(SecurityAction.PermitOnly,
     Read=@"HKEY_LOCAL_MACHINESOFTWAREClient")]
static string GetName(string Id) 
{

    SqlCommand cmd = null;

    string Status = "Name Unknown";
    try {
        //Check for valid shipping ID.
        Regex r = new Regex(@"^d{4,10}$");
        if (!r.Match(Id).Success)
            throw new Exception("Invalid ID");

        //Get connection string from registry.
        SqlConnection sqlConn= new SqlConnection(ConnectionString); 

        //Add shipping ID parameter.
        string str="sp_GetName";
        cmd = new SqlCommand(str,sqlConn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@ID",Convert.ToInt64(Id));

        cmd.Connection.Open();
        Status = cmd.ExecuteScalar().ToString();

    } catch (Exception e) {
        if (HttpContext.Current.Request.UserHostAddress == "127.0.0.1")
               Status = e.ToString(); 
            else
                Status = "Error Processing Request";
            } finally {
        //Shut down connection--even on failure.
        if (cmd != null)
            cmd.Connection.Close();
    }
    return Status;
}

//Get connection string.
internal static string ConnectionString {
    get {
        return (string)Registry
            .LocalMachine
            .OpenSubKey(@"SOFTWAREClient")
            .GetValue("ConnectionString");
    }
}

Numerous layers of defense are used here—each is explained in detail later:

At first glance, the code looks more complex, but it really isn’t. Let me explain how this code is more secure than the first example. I’ll hold off on explaining the permission attributes before the function call until the end of this section.

First, this code mandates that a user identity number must be between 4 and 10 digits. This is indicated using the regular expression ^d{4,10}$, which looks only for 4- to 10-digit numbers (d{4,10}) from the start (^) to the end ($) of the input data. By declaring what is valid input and rejecting everything else, we have already made things safer—an attacker cannot simply append SQL statements to the shipping ID. Regular expressions in managed code are exposed through the System.Text.RegularExpressions namespace.

The code includes even more defenses. Note that the SqlConnection object is built from a connection string from the registry. Also, take a look at the accessor function ConnectionString. To determine this string, an attacker would have to not only access the source code to the Web service but also access the appropriate registry key.

The data in the registry key is the connection string:

data source=db007a;
user id=readuser;
password=&ugv4!26dfA-+8;
initial catalog=client

Note that the SQL database is on another computer named db007a. An attacker who compromises the Web service will not gain automatic access to the SQL data. In addition, the code does not connect as sa; instead, it uses a specific account, readuser, with a strong (and ugly) password. And this special account has only read and execute access to the appropriate SQL objects in the client database. If the connection from the Web service to the database is compromised, the attacker can run only a handful of stored procedures and query the appropriate tables; she cannot destroy the master database nor can she perform attacks such as deleting, inserting, or modifying data.

The SQL statement is not constructed using the insecure string concatenation technique; instead, the code uses parameterized queries to call a stored procedure. Calling the stored procedure is faster and more secure than using string concatenation because the database and table names are not exposed and stored procedures are optimized by the database engine.

Note that when an error does occur, the user (or attacker) is told nothing unless the request is local or on the same machine where the service code resides. If you have physical access to the Web service computer, you "own" the computer anyway! You could also add code to limit access to the error message to administrators only by using code like this:

AppDomain.CurrentDomain.SetPrincipalPolicy
    (PrincipalPolicy.WindowsPrincipal);
WindowsPrincipal user = (WindowsPrincipal)Thread.CurrentPrincipal;
if (user.IsInRole(WindowsBuiltInRole.Administrator)) {
    //user is an admin – we can divulge error details.
}

Next, the SQL connection is always closed in the finally handler. If an exception is raised in the try/catch body, the connection is gracefully cleaned up, thereby mitigating a potential denial of service (DoS) threat if connections to the database were not closed.

So far, what I’ve explained is generic and applies to just about any programming language. Now I want to point out a .NET Framework–specific defense outlined in the sample code that uses permission attributes.

Notice the two security attributes at the start of the function call. The first, SQLClientPermissionAttribute, allows the SQL Server .NET Data Provider to ensure that a user has a security level adequate to access a data source—in this case, by setting the AllowBlankPassword property to false the use of blank passwords is forbidden. This code will raise an exception if you inadvertently attempt to connect to SQL Server by using an account that has a blank password.

The second attribute, RegistryPermissionAttribute, limits which registry key or keys can be accessed and to what degree they can be manipulated (read, write, and so on). In this case, by setting the Read property to @"HKEY_LOCAL_MACHINESOFTWAREShipping", only one specific key, which holds the connection string, can be read. Even if an attacker can make this code access other parts of the registry, it will fail.

All these mechanisms together lead to very secure database communication code. You should always use such mechanisms and layer them in such a way that your code is safe from attack.

Database applications are incredibly common, and unfortunately, many of these applications are vulnerable to injection attacks. By following some simple rules, you can eliminate the risk of such attacks from your applications: