asp:feature
LANGUAGES: SQL
TECHNOLOGIES: SQL Server | Database Security
Defend Your Database
Data protection is not only the admin's job anymore.
By Beth Breidenbach
Database defense might seem like an odd topic for a developer-oriented magazine article. In the early days of developing database-driven Web sites, we coders had the naive luxury of believing database security to be the province of the administrators. As long as we could connect to the database, write a query, and incorporate the results into our Web application logic, we had no reason to worry further - or so we thought.
Those days of innocence are gone. The events of the last calendar year prove that how we code to the database directly impacts the security of the entire site. In the last 12 months, we have seen the first "In the wild" SQL Server -specific virus, numerous buffer overflows discovered in various SQL Server technologies, and a plethora of papers detailing how SQL Injection attacks can use our own Web forms as vectors for attacks against the server. Administrators have their part to play in protecting our sites, but so do developers.
In this article, I'll discuss the four major mindsets (disciplines) you must acquire to defend your sites' database interactions adequately. No article or paper can provide every single piece of information you need to defend your site. The details of secure programming are in constant flux because attacks evolve with time. Yet if you understand the major concepts that underlie your defense plan, you will be able to identify and adapt to new threats as they arise.
A Trifecta of Risks
Let's take a look at three key risk areas in database security: virus attacks, buffer overflows, and SQL Injection attacks.
May 2002 saw the first widespread "wild" virus targeting SQL Server. Dubbed SQL Snake, it targets servers exposed to the Internet having blank passwords for the "sa" account. As viruses go, SQL Snake's effects are not as pernicious as they might have been. Once it identifies a vulnerable box, the virus adds the guest account to administrator groups, sends security information about the server to an offshore e-mail address, makes registry changes, and launches Internet port 1433 scans for other vulnerable servers. I would not, however, count on future viruses being nearly as benign as SQL Snake.
Some could argue that protecting against exposure to this virus is the responsibility of system administrators. After all, if the site is firewalled properly, the worm should not be able to establish a direct connection to SQL Server's ports. But the truth is the responsibility for this risk must be shared between administrators and developers.
Buffer overflows are another risk. Cesar Cerrudo (Application Security Inc.) and David Lichtfield (Next Generation Security Software Ltd.), among others, have discovered many buffer overflows in SQL Server technologies. You can find a good chronology of announcements at http://www.sqlsecurity.com. Many of these issues have been patched (and more patches are undoubtedly on the way). The development and installation configurations we developers specify for our Web applications feed directly, however, into the vulnerability of the implemented site.
What is a SQL Injection attack? Simply put, SQL Injection occurs when a Web site user manipulates a Web form to "inject" his or her own SQL into (or in place of) your ASP page's SQL query. These injected statements run all the permissions you've granted to your own SQL. (Stop and think about this statement for a moment: Just how much damage could your database login do? What permissions do you need to revoke on your implemented sites?)
Let's look at three examples of how SQL injection occurs.
Example 1: Validating a user login. Many login pages accept a user ID and password, then concatenate them into a SQL statement. This process of building up a SQL string dynamically at run time is common coding practice. In fact, you probably have seen this practice in older ASP publications (see Figure 1). It is a coding pattern that is both easily grasped and applied - but it is also what opens the door to SQL Injection attacks.
StringBuilder myBuilder = new StringBuilder();
string SQL;
SqlConnection oConn = new SqlConnection(Conn);
oConn.Open();
// building the sql statement
// dynamically, concatenating the
// user-supplied information into my query!
myBuilder.Append("select count(UserID) from Users ");
myBuilder.Append("where UserID='");
myBuilder.Append(UserID.Text);
myBuilder.Append("' and Pass='");
myBuilder.Append(Password.Text);
myBuilder.Append("'");
mySQL = myBuilder.ToString();
// execute the query and see if we find any records
SqlCommand oCmd = new SqlCommand (SQL,oConn);
int Count = (int) oCmd.ExecuteScalar();
if (Count > 0)
{
lblResult.Text="You authenticated!";
}
else
{
lblResult.Text=
"Authentication Failure. Please try again."
}
Figure 1. This code shows a typical example of dynamically generated SQL. As you will soon see, it is open to attack.
If the user passes values such as myuser and mypass to the query in Figure 1, this is what the generated SQL would be:
select count(UserID) from Users
where UserID='myuser' and Pass='mypass'
This query is safe enough. Now, let's assume the attacker passed this for his or her user ID and nothing at all for the password:
' or 1=1--
The dynamically generated SQL would read like this:
Select count(UserID) from Users
where UserID='' or 1=1--and Pass=''
As you probably realize, the double hyphen is a comment symbol in SQL. In this case, it effectively comments out the password checking. SQL Server needs only to look for records where UserID equals an empty string, or where 1=1. Because 1=1 is always true, all records are returned. The user, then, is authenticated.
Let's take this further. SQL Server accepts multiple SQL statements in a single batch. Assume our malicious user passes this for the user ID (typed as one line):
' or 1=1 exec master..xp_cmdshell 'net
user myUser myPass /ADD'--
The database interprets the resulting string as a two-statement batch and processes these two SQL commands:
Select count(UserID) from Users where UserID='' or 1=1
exec master..xp_cmdshell 'net user myUser myPass /ADD'--
The second command calls the system-stored procedure xp_cmdshell to create a new NT user account! Note that by default, xp_cmdshell is available only to logins in SQL Server's System Administrator role. As I mentioned earlier in this article, the permissions developers specify for their sites' database connections can seriously impact site vulnerability.
Example 2: Querying the database. Another commonly implemented page is the database search page. The user typically inputs a search term and the site returns a list of matching records in a database table. If the input isn't reviewed carefully, your site might disclose much more information than you intended.
string SQL;
StringBuilder myBuilder = new StringBuilder();
SqlConnection oConn = new SqlConnection(Conn);
oConn.Open();
// concatenating the user-supplied information
// into the SQL string
myBuilder.Append("select FirstName, LastName, Title ");
myBuilder.Append("from employees where ");
myBuilder.Append("City like '%" + City.Text);
myBuilder.Append("%'");
SQL = myBuilder.ToString();
// run the query
System.Data.SqlClient.SqlCommand oCmd =
new SqlCommand(SQL,oConn);
SqlDataReader myReader=
oCmd.ExecuteReader(CommandBehavior.CloseConnection);
string Temp;
Temp = "
| ";
Temp = Temp + myReader.GetString(i) + " | ";
lblOutput.Text = Temp;
myReader.Close();
Figure 2. This code shows common search-page code. As before, it concatenates the user's input into a string and executes the resulting SQL query.
Search pages are great targets for attackers. Figure 2 shows how they can reveal information the developer never intended. For example, assume the attacker enters this search term:
XX' UNION select name, type, xtype
from sysobjects where type <> 's' --
The results page lists all non-system objects in the database, including the name of every user-created table. Using queries like this, an attacker can gradually learn the names of all tables in the database, the data types of all table columns, and the text of all stored procedures. As in Example 1, building your SQL dynamically at run time opens the database to potential attack.
Example 3: Hijacking form input with a proxy application. As a final example, consider a Web form that gathers its user input from drop-down controls instead of from text fields. At first glance, this would seem to protect your code from malicious input. How can attackers hijack the SQL if they can't input their own query directly? Naive attackers might indeed be stumped, but more sophisticated enemies will use one of many freeware applications (or scripts) to bypass the form's restrictions.
DigiZen Security Group (http://www.digizen-security.com/projects.html) provides a penetration-testing application called Achilles that intercepts the HTTP stream sent from the browser to the Web server and back (see Figure 3). The submitted text is displayed in an editable text field, allowing the user to modify the data (including submitted form values) and send them back to the server. DigiZen's efforts are designed to improve overall security of the Internet and "reduce risk through awareness." They are some of the "good guys," but many more advanced tools are available to would-be attackers.
Figure 3. Here is an example HTTP capture in Achilles. The text field is
fully editable. You can modify any values in the submitted HTTP stream,
including data submitted in form fields.
Hopefully by this time you've gained an appreciation for the security risks inherent with database-driven Web sites. What can you, the developer, do to protect your site from a database-related attack? Here are four key security concepts to keep in mind.
1. Set Up a Security-Oriented Development Environment
Security should be coded in from the beginning of the development project, rather than as an add-on at project's end. Most of us have had the experience of checking in code we thought was ready only to discover a malfunction when installed under full security requirements. (Or worse yet, the problem goes unnoticed until the code is deployed.) If your development environment is configured correctly, you can avoid most of these problems. Try to adhere to these four practices.
Do not run as Administrator. Your normal login should have no more privileges than a power user. On the rare occasion when you need more privileges, use Run As to invoke that particular application with an administrator-level login (see Figure 4). Security guru Keith Brown of DevelopMentor provides recommendations for how to develop without running as an administrator at http://www.develop.com/kbrown. And in his book Writing Secure Code, Michael Howard reinforces the need to follow this precept and gives further recommendations for avoiding the dreaded Administrator login.
Figure 4. Right-clicking on the icon makes the Run as... menu option available.
Use this to specify a higher-privilege login for specific individual
applications when needed.
Connect to the database with a low-privilege login. I have never yet seen a Web site that needed to connect as the "sa" user. For that matter, it would be incredibly unusual if your Web site required dbo (database owner) privileges. Create a separate login specifically for your Web application and grant it no more database privileges than it absolutely requires. I prefer to use a different connection (generally via SQL Query Analyzer) for database development, and I keep my Web application's privileges as low as possible.
Run your SQL Server service as a low-privilege NT login. Many developers' SQL Server installations are running as SYSTEM. This is incredibly dangerous and allows the service far more system access than it should ever have in a deployed Web application. The SQL Server service runs quite well with only Guest privileges. Chip Andrews' Web site details additional registry tweaks that further limit the privileges assigned to the service (http://www.sqlsecurity.com; see the tip posted Feb. 10, 2002).
Don't specify a direct database connection over the Internet. Connecting to SQL Server directly over the Internet is incredibly risky. As you should recall, the SQL Snake worm spread by connecting directly to SQL Server via port 1433; responsible system administrators should firewall SQL Server's ports. Do not subvert the efforts of these professionals by designing applications that require the firewall to open these ports.
2. Clean Up Database-Related Code
The way you develop your database interactions has a direct impact on your site's vulnerability. The SQL Injection examples I described earlier provide ample illustration of the risks at hand. This section describes how to code your interactions with the database correctly.
Never use string concatenation to build queries. SQL Injection attacks rely upon string concatenation to insert the attacker's query into your query. Thus, the simplest and most important rule is to never use string-building to create your query. As you research SQL Injection vulnerabilities, often you will encounter the recommendation that you "build all your queries in stored procedures." This can be misleading. Yes, stored procedures are definitely recommended, but it is crucial that you understand never to use string-building of queries within those stored procedures. This code demonstrates just such an error:
Create procedure BadProcedure
@User varchar(50),
@Password varchar(50)
As
Declare @SQL varchar(200)
Select @SQL = 'select count(*) from Users where UserID='
+ @User + ' and Password = ' + @Password
Exec @sql
Go
Note how the stored procedure's code uses string concatenation to build the query in the same fashion many insecure ASP pages do - this is a dangerously coded stored procedure.
Do not fall into the trap of moving insecure code patterns into the stored procedure. The correct method is to treat the parameters as true query parameters rather than as pieces to concatenate within a string.
Use parameterized stored procedures. This code shows a safe version of the same stored procedure logic - no string-building occurs and a truly parameterized query is executed:
Create procedure GoodProcedure
@User varchar(50),
@Password varchar(50)
As
Select count(*) from Users
where UserID = @User and Password = @Password
Go
Notice the differences between the two stored procedures. The safe version uses a true parameterized query instead of string-building techniques. When SQL Server processes the query, the variables are considered to be value parameters. The user's input is not considered executable code.
Use the Command object's Parameters collection. When calling the stored procedures for your site, you want to avoid writing a call like this:
"exec dbo.GoodProcedure '" + User.Text
+ "', '" + Password.Text + "'"
In the previous scenario, the attacker could potentially pass dangerous input into the last parameter. For example, Password.Text could contain this:
a' exec master..xp_cmdshell 'net user myUser myPass /ADD'--
The "a" value would be passed to GoodProcedure, and the rest of the string would be considered a second SQL statement in a multistatement batch and executed accordingly.
How do you avoid this? Use the Parameters collection to pass data to the stored procedure. Figure 5 shows a safe way to handle procedure parameters.
string mySQL;
OleDbConnection oConn = new OleDbConnection(Conn);
oConn.Open();
// double up any apostrophes
string User, Pass;
mySQL = "LoginUser";
// Build up the command object and its parameters.
System.Data.OleDb.OleDbCommand oCmd = new
OleDbCommand(mySQL,oConn);
oCmd.CommandType = System.Data.CommandType.StoredProcedure;
oCmd.Parameters.Add("UID",OleDbType.VarChar,50);
oCmd.Parameters.Add("PWD",OleDbType.VarChar,50);
oCmd.Parameters["UID"].Value=User;
oCmd.Parameters["PWD"].Value=Pass;
int iCount = (int) oCmd.ExecuteScalar();
if (iCount > 0)
{
UserFeedback.Text="You authenticated!";
}
else
{
UserFeedback.Text="Failed -- not letting you in";
}
Figure 5. This example illustrates interactions properly coded with the database. The query is a parameterized stored procedure, uses regular expressions to validate user input, and sends the query's parameters via the command object's parameters collection.
The Parameter object's values are passed directly to the stored procedure's declared parameters. An attacker's attempts to inject SQL into the final parameter will fail so long as the underlying procedure does not use string concatenation to build its own SQL (as discussed earlier). Also notice that each Parameter object in the collection has an associated data type, in this case OleDbType.VarChar. If the object receives a parameter value that cannot be coerced into the data type, an error will be raised.
Consider all user input to be evil until proven otherwise. As we have seen, user input could be malicious. The content of form fields must be proven valid before being used further in the Web site's code. Any input not meeting the criteria for valid input should be rejected. This is not the same as trying to identify all possible "bad" inputs, which actually is much more difficult.
For each field, identify the appropriate length, data type, and valid characters it may receive. Only entries meeting the defined criteria may be used. Regular expressions are quite useful for applying the necessary validations (see Steve Smith's article Create No-Sweat Regular Expressions for a more detailed discussion of using the Regex class to apply regular expressions to user data). As a brief example, if an input field is defined as being alphanumeric characters between 1 and 12 characters long, you could validate the field with this:
Regex myReg = new Regex("^[a-zA-Z0-9]{1,12}$",
System.Text.RegularExpressions.RegexOptions.Multiline);
if (! myReg.IsMatch(User))
{
UserFeedback.Text="Invalid UserID format";
}
else
// do processing here - passed the regex test.
3. Tighten Access to Database Objects
Even if your Web site's code is compromised, your database server should remain secure. First, limit the privileges granted to your site connection login (note that I have previously discussed the importance of using a non-dbo login), and secondly, grant login privileges only for particular databases required by your site.
Limit object access within the database. Tighten the access further by limiting which objects your site login may access within the database. I generally deny direct access to the database's tables. You should instead grant execute permissions to stored procedures that accomplish the data modification tasks required by the site. To protect the database from further inappropriate data disclosure, write SQL views for query purposes and grant select access to those views. (Again, we are denying direct access to the underlying tables.) You can write views to limit the rows and columns available to users. The next code example is exemplary, providing an instance of revoking a login's access to a database table (UserLogins). Note that it specifically grants execute permissions to a stored procedure, AuthenticateUser, which accesses the UserLogin table only to authenticate login IDs:
use Northwind
go
revoke all on UserLogins from WebSiteUser
go
grant execute on AuthenticateUser to WebSiteUser
go
Deny access to unnecessary system stored procedures. Finally, if you have control of the database server, you should also review what permissions the site's login has for executing system stored procedures. Many exploits (buffer overflows, escalation of privileges, etc.) have been announced this year that take advantage of system and extended stored procedures. A March 2002 posting on Application Security's Web site (http://www.appsecinc.com/resources/alerts/mssql/02-0000.html) details many stored procedures that are at risk for buffer overflows. Needless to say, you will not want your site to be able to access those procedures (see Figure 6).
Figure 6. By default, the public role can execute the xp_mergelineage
extended procedure. This procedure is listed as vulnerable to buffer overflows
in Application Security's March 2002 security announcement.
Web developers carefully need to consider what database objects a Web site may use. Site security is layered, much like an onion. If an attacker can compromise some aspect of IIS, you do not want to give him or her an open door to the database server. Code run in the context of the Web site should be able to perform actions you specify, and no more. Similarly, you want to ensure the database cannot be used as the initial staging point for attacks against the Web server. Ultimately, it is worth taking the extra time to code database-object security.
4. Keep Error Details Private
Consider the last time you used a Web page that had a database error. How did you know an error occurred? If the site didn't implement good error-handling techniques, the full text of the error likely was displayed to your browser. The displayed information is a potential treasure trove to attackers. If the error was indeed database-related, it probably allowed the viewer to deduce the type of database engine (Oracle, SQL Server) supporting the site. If the user can force a SQL syntax error, the displayed text often will include the full SQL query, including the name of the column and/or table involved in the error. Attackers could use SQL Injection to induce systematic syntax errors, slowly building a storehouse of information about the database structure. Such "footprinting" - identifying the configuration and structure of the server - is a common first step when deciding how best to attack a site.
Implement exception handling, every time. The first step in mitigating this risk is by implementing exception handling. Your code should always be placed within a try...catch block. If your catch statement is unable to resolve the problem gracefully, it should log the error somewhere safe and provide the user with a generic error message. The event log is often the appropriate place to store the true error details. Regardless of where you log the details, be certain you do not provide structural or configuration information to the user's browser.
Note: Properly implementing exception handling is quite different from using exception handling to control the flow of normal execution in your application. Exceptions impact performance significantly and should be used only in true error situations.
I've given you the basic conceptual tools necessary to defend your site's databases, and hopefully these examples will motivate you to learn more about what you as a developer can do to protect your site. The four security concepts I described are a good starting point, and they should help you identify areas where you can make your development practices more secure.
As I mentioned at the beginning, secure coding is in constant flux. There always will be more to learn and new exploits to defend against. Yet by grasping the underlying principles of configuring a secure development environment, tightening how you access the database, limiting database object access, and keeping your error details private, you will be well on the way to defending your database.
Beth Breidenbach is a product architect for Getronics, a Netherlands-based provider of software and infrastructure solutions throughout the world (http://www.getronics.com). Her group develops branch, Internet, and call center applications targeted at top-tier financial institutions. A self-professed "data geek," Beth has an abiding interest in all aspects of data design, storage, transmission, and translation. E-mail Beth at mailto:[email protected].
Tell us what you think! Please send any comments about this article to [email protected]. Please include the article title and author.
Replace Single Quotes With Double Quotes?
Often I am asked whether a developer can mitigate against SQL Injection attacks by simply replacing all single quotes with double quotes. Replacing single quotes with double quotes certainly helps. Looking at the code in Figure 1, if a user entered this:
' or 1=1--
the dynamically generated SQL would read like this:
Select count(UserID) from Users
where UserID=''' or 1=1--and Pass=''
The query now looks for records where the UserID matches the string ' or 1=1--and Pass=. It is not likely that many records match this string.
But what if the expected user input is numeric? Such values do not require delimiting with single quotes and thus will not require the attacker to input a single quote to mount an attack. And, of course, the attacker can enter hex equivalents of the attack characters. Next Generation Security Software's two advanced SQL Injection papers (http://www.nextgenss.com/research.html#papers) provide numerous examples describing why quote replacement is not enough.
All Dynamically Built Queries are Targets
Typical examples of SQL Injection use login forms and search screens because they are found on most Web sites. Unfortunately, this sometimes creates a misperception that other database-related forms do not require the same level of defense. This is categorically false. Any and all dynamically built queries (for example, queries built using input from Web forms) are potential targets for SQL Injection attacks. You should review and defend any such form on your Web site.
