Defend Your Database

asp:feature

LANGUAGES: SQL

TECHNOLOGIES: SQLServer | 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 adeveloper-oriented magazine article. In the early days of developingdatabase-driven Web sites, we coders had the naive luxury of believing databasesecurity to be the province of the administrators. As long as we could connectto the database, write a query, and incorporate the results into our Webapplication logic, we had no reason to worry further - or so we thought.

Those days of innocence are gone. The events of the lastcalendar year prove that how we code to the database directly impacts thesecurity of the entire site. In the last 12 months, we have seen the first "Inthe wild" SQL Server -specific virus, numerous buffer overflows discovered invarious SQL Server technologies, and a plethora of papers detailing how SQLInjection attacks can use our own Web forms as vectors for attacks against theserver. Administrators have their part to play in protecting our sites, but sodo developers.

In this article, I'll discuss the four major mindsets(disciplines) you must acquire to defend your sites' database interactionsadequately. No article or paper can provide every single piece of informationyou need to defend your site. The details of secure programming are in constantflux because attacks evolve with time. Yet if you understand the major conceptsthat underlie your defense plan, you will be able to identify and adapt to newthreats as they arise.

A Trifecta of Risks

Let's take a look at three key risk areas in databasesecurity: virus attacks, buffer overflows, and SQL Injection attacks.

May 2002 saw the first widespread "wild" virus targetingSQL Server. Dubbed SQL Snake, it targets servers exposed to the Internet havingblank passwords for the "sa" account. As viruses go, SQL Snake's effects arenot as pernicious as they might have been. Once it identifies a vulnerable box,the virus adds the guest account to administrator groups, sends securityinformation about the server to an offshore e-mail address, makes registrychanges, and launches Internet port 1433 scans for other vulnerable servers. Iwould not, however, count on future viruses being nearly as benign as SQLSnake.

Some could argue that protecting against exposure to thisvirus is the responsibility of system administrators. After all, if the site isfirewalled properly, the worm should not be able to establish a directconnection to SQL Server's ports. But the truth is the responsibility for thisrisk must be shared between administrators and developers.

Buffer overflows are another risk. Cesar Cerrudo(Application Security Inc.) and David Lichtfield (Next Generation SecuritySoftware Ltd.), among others, have discovered many buffer overflows in SQLServer technologies. You can find a good chronology of announcements at http://www.sqlsecurity.com. Many ofthese issues have been patched (and more patches are undoubtedly on the way).The development and installation configurations we developers specify for ourWeb applications feed directly, however, into the vulnerability of theimplemented site.

What is a SQL Injection attack? Simply put, SQLInjection occurs when a Web site user manipulates a Web form to "inject" his orher own SQL into (or in place of) your ASP page's SQL query. These injectedstatements run all the permissions you've granted to your own SQL. (Stop andthink about this statement for a moment: Just how much damage could yourdatabase login do? What permissions do you need to revoke on your implementedsites?)

Let's look at three examplesof how SQL injection occurs.

Example 1: Validating a user login. Many loginpages accept a user ID and password, then concatenate them into a SQLstatement. This process of building up a SQL string dynamically at run time iscommon coding practice. In fact, you probably have seen this practice in olderASP publications (see Figure 1). It is a coding pattern that is both easilygrasped and applied - but it is also what opens the door to SQL Injectionattacks.

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="Youauthenticated!";

}

else

{

  lblResult.Text=

     "AuthenticationFailure.   Please try again."

}

Figure 1. This code shows a typical example ofdynamically generated SQL. As you will soon see, it is open to attack.

If the user passes values such as myuser and mypassto 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 attackerpassed 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='' or1=1--and Pass=''

As you probably realize, the double hyphen is a commentsymbol in SQL. In this case, it effectively comments out the password checking.SQL Server needs only to look for records where UserID equals an emptystring, or where 1=1. Because 1=1 is always true, all records are returned. Theuser, then, is authenticated.

Let's take this further. SQL Server accepts multiple SQLstatements in a single batch. Assume our malicious user passes this for theuser ID (typed as one line):

' or 1=1 exec master..xp_cmdshell 'net

user myUser myPass /ADD'--

The database interprets the resulting string as atwo-statement batch and processes these two SQL commands:

Select count(UserID) from Users whereUserID='' or 1=1

exec master..xp_cmdshell 'net user myUser myPass /ADD'--

The second command calls the system-stored procedure xp_cmdshellto create a new NT user account! Note that by default, xp_cmdshell isavailable only to logins in SQL Server's System Administrator role. As Imentioned earlier in this article, the permissions developers specify for theirsites' database connections can seriously impact site vulnerability.

Example 2: Querying the database. Another commonlyimplemented page is the database search page. The user typically inputs asearch term and the site returns a list of matching records in a databasetable. If the input isn't reviewed carefully, your site might disclose muchmore 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 =

     newSqlCommand(SQL,oConn);

SqlDataReader myReader=

     oCmd.ExecuteReader(CommandBehavior.CloseConnection);

string Temp;

Temp = "

";

lblOutput.Text = Temp;

myReader.Close();

Figure 2. This code shows common search-page code. Asbefore, it concatenates the user's input into a string and executes theresulting SQL query.

Search pages are great targets for attackers. Figure 2shows how they can reveal information the developer never intended. Forexample, assume the attacker enters this search term:

XX' UNION select name, type, xtype

     from sysobjects wheretype <> 's' --

The results page lists all non-system objects in thedatabase, including the name of every user-created table. Using queries likethis, 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. Asin Example 1, building your SQL dynamically at run time opens the database topotential attack.

Example 3: Hijacking form input with a proxyapplication. As a final example, consider a Web form that gathers its userinput from drop-down controls instead of from text fields. At first glance,this would seem to protect your code from malicious input. How can attackershijack the SQL if they can't input their own query directly? Naive attackersmight indeed be stumped, but more sophisticated enemies will use one of manyfreeware 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 theHTTP 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 tomodify the data (including submitted form values) and send them back to theserver. DigiZen's efforts are designed to improve overall security of theInternet 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 isfully 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 forthe security risks inherent with database-driven Web sites. What can you, thedeveloper, do to protect your site from a database-related attack? Here arefour key security concepts to keep in mind.

1. Set Up a Security-OrientedDevelopment Environment

Security should be coded in from the beginning of thedevelopment project, rather than as an add-on at project's end. Most of us havehad the experience of checking in code we thought was ready only to discover amalfunction when installed under full security requirements. (Or worse yet, theproblem goes unnoticed until the code is deployed.) If your developmentenvironment is configured correctly, you can avoid most of these problems. Tryto adhere to these four practices.

Do not run as Administrator. Your normal loginshould have no more privileges than a power user. On the rare occasion when youneed more privileges, use Run As to invoke thatparticular application with an administrator-level login (see Figure 4).Security guru Keith Brown of DevelopMentor provides recommendations for how todevelop without running as an administrator at http://www.develop.com/kbrown. And inhis book Writing Secure Code, Michael Howard reinforces the need tofollow this precept and gives further recommendations for avoiding the dreaded Administratorlogin.

Figure 4. Right-clicking on the icon makes the Run as... menu option available.Use this to specify a higher-privilege login for specific individualapplications 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. Forthat matter, it would be incredibly unusual if your Web site required dbo(database owner) privileges. Create a separate login specifically for your Webapplication and grant it no more database privileges than it absolutelyrequires. I prefer to use a different connection (generally via SQL QueryAnalyzer) for database development, and I keep my Web application's privilegesas low as possible.

Run your SQL Server service as a low-privilege NTlogin. Many developers' SQL Server installations are running as SYSTEM.This is incredibly dangerous and allows the service far more system access thanit should ever have in a deployed Web application. The SQL Server service runsquite well with only Guest privileges. Chip Andrews' Web site detailsadditional registry tweaks that further limit the privileges assigned to theservice (http://www.sqlsecurity.com;see the tip posted Feb. 10, 2002).

Don't specify a direct database connection over theInternet. Connecting to SQL Server directly over the Internet is incrediblyrisky. As you should recall, the SQL Snake worm spread by connecting directlyto SQL Server via port 1433; responsible system administrators should firewallSQL Server's ports. Do not subvert the efforts of these professionals bydesigning applications that require the firewall to open these ports.

2. Clean Up Database-Related Code

The way you develop your database interactions has adirect impact on your site's vulnerability. The SQL Injection examples Idescribed earlier provide ample illustration of the risks at hand. This sectiondescribes 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 queryinto your query. Thus, the simplest and most important rule is to never usestring-building to create your query. As you research SQL Injectionvulnerabilities, often you will encounter the recommendation that you "buildall your queries in stored procedures." This can be misleading. Yes, storedprocedures are definitely recommended, but it is crucial that you understandnever to use string-building of queries within those stored procedures. Thiscode demonstrates just such an error:

Create procedure BadProcedure

     @User varchar(50),

     @Password varchar(50)

As

Declare @SQL varchar(200)

Select @SQL = 'select count(*) fromUsers where UserID='

     + @User + ' andPassword = ' + @Password

Exec @sql

Go

Note how the stored procedure's code uses stringconcatenation 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 patternsinto the stored procedure. The correct method is to treat the parameters astrue query parameters rather than as pieces to concatenate within a string.

Use parameterized stored procedures. This codeshows a safe version of the same stored procedure logic - no string-buildingoccurs and a truly parameterized query is executed:

Create procedure GoodProcedure

     @User varchar(50),

     @Password varchar(50)

As

Select count(*) from Users

     where UserID = @Userand Password = @Password

Go

Notice the differences between the two stored procedures.The safe version uses a true parameterized query instead of string-buildingtechniques. When SQL Server processes the query, the variables are consideredto 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 acall like this:

"exec dbo.GoodProcedure '" + User.Text

     + "', '" +Password.Text + "'"

In the previous scenario, the attacker could potentiallypass dangerous input into the last parameter. For example, Password.Textcould contain this:

a' exec master..xp_cmdshell 'net user myUser myPass /ADD'--

The "a" value would be passed to GoodProcedure, andthe rest of the string would be considered a second SQL statement in a multistatementbatch and executed accordingly.

How do you avoid this? Use the Parameterscollection to pass data to the stored procedure. Figure 5 shows a safe way tohandle 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="Youauthenticated!";

}

else

{

  UserFeedback.Text="Failed -- not letting you in";

}

Figure 5. This example illustrates interactionsproperly coded with the database. The query is a parameterized storedprocedure, uses regular expressions to validate user input, and sends thequery's parameters via the command object's parameters collection.

The Parameter object's values are passed directlyto the stored procedure's declared parameters. An attacker's attempts to injectSQL into the final parameter will fail so long as the underlying procedure doesnot use string concatenation to build its own SQL (as discussed earlier). Alsonotice that each Parameter object in the collection has an associateddata type, in this case OleDbType.VarChar. If the object receives aparameter value that cannot be coerced into the data type, an error will beraised.

Consider all user input to be evil until provenotherwise. As we have seen, user input could be malicious. The content ofform fields must be proven valid before being used further in the Web site'scode. 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, whichactually is much more difficult.

For each field, identify the appropriate length, datatype, and valid characters it may receive. Only entries meeting the definedcriteria may be used. Regular expressions are quite useful for applying thenecessary validations (see Steve Smith's article CreateNo-Sweat Regular Expressions for a more detailed discussion of using the Regexclass to apply regular expressions to user data). As a brief example, if aninput field is defined as being alphanumeric characters between 1 and 12characters 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="InvalidUserID format";

}

else

//  do processing here -passed the regex test.

3. Tighten Access to DatabaseObjects

Even if your Web site's code is compromised, your databaseserver should remain secure. First, limit the privileges granted to your siteconnection login (note that I have previously discussed the importance of usinga non-dbo login), and secondly, grant login privileges only forparticular databases required by your site.

Limit object access within the database. Tightenthe access further by limiting which objects your site login may access withinthe database. I generally deny direct access to the database's tables. Youshould instead grant execute permissions to stored procedures that accomplishthe data modification tasks required by the site. To protect the database fromfurther inappropriate data disclosure, write SQL views for query purposes andgrant select access to those views. (Again, we are denying direct access to theunderlying tables.) You can write views to limit the rows and columns availableto users. The next code example is exemplary, providing an instance of revokinga login's access to a database table (UserLogins). Note that itspecifically 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 reviewwhat permissions the site's login has for executing system stored procedures.Many exploits (buffer overflows, escalation of privileges, etc.) have beenannounced this year that take advantage of system and extended storedprocedures. 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. Needlessto say, you will not want your site to be able to access those procedures (seeFigure 6).

Figure 6. By default, the public role can execute the xp_mergelineageextended procedure. This procedure is listed as vulnerable to buffer overflowsin Application Security's March 2002 security announcement.

Web developers carefully need to consider what databaseobjects a Web site may use. Site security is layered, much like an onion. If anattacker can compromise some aspect of IIS, you do not want to give him or heran open door to the database server. Code run in the context of the Web siteshould be able to perform actions you specify, and no more. Similarly, you wantto ensure the database cannot be used as the initial staging point for attacksagainst the Web server. Ultimately, it is worth taking the extra time to codedatabase-object security.

4. Keep Error Details Private

Consider the last time you used a Web page that had adatabase error. How did you know an error occurred? If the site didn'timplement good error-handling techniques, the full text of the error likely wasdisplayed to your browser. The displayed information is a potential treasuretrove to attackers. If the error was indeed database-related, it probablyallowed 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 displayedtext often will include the full SQL query, including the name of the columnand/or table involved in the error. Attackers could use SQL Injection to inducesystematic syntax errors, slowly building a storehouse of information about thedatabase structure. Such "footprinting" - identifying the configuration andstructure of the server - is a common first step when deciding how best toattack a site.

Implement exception handling, every time. The firststep in mitigating this risk is by implementing exception handling. Your codeshould always be placed within a try...catch block. If your catchstatement is unable to resolve the problem gracefully, it should log the errorsomewhere safe and provide the user with a generic error message. The event logis often the appropriate place to store the true error details. Regardless ofwhere you log the details, be certain you do not provide structural orconfiguration information to the user's browser.

Note: Properly implementing exception handling is quitedifferent from using exception handling to control the flow of normal executionin your application. Exceptions impact performance significantly and should beused only in true error situations.

I've given you the basic conceptual tools necessary todefend your site's databases, and hopefully these examples will motivate you tolearn more about what you as a developer can do to protect your site. The foursecurity concepts I described are a good starting point, and they should helpyou identify areas where you can make your development practices more secure.

As I mentioned at the beginning, secure coding is inconstant flux. There always will be more to learn and new exploits to defendagainst. Yet by grasping the underlying principles of configuring a securedevelopment environment, tightening how you access the database, limitingdatabase object access, and keeping your error details private, you will bewell on the way to defending your database.

Beth Breidenbach is a product architect for Getronics,a Netherlands-based provider of software and infrastructure solutionsthroughout the world (http://www.getronics.com).Her group develops branch, Internet, and call center applications targeted attop-tier financial institutions. A self-professed "data geek," Beth has anabiding interest in all aspects of data design, storage, transmission, andtranslation. E-mail Beth at mailto:[email protected].

Tell us what you think! Please send any comments about thisarticle to [email protected].Please include the article title and author.

Replace Single Quotes WithDouble Quotes?

Often I am asked whether a developer can mitigate againstSQL Injection attacks by simply replacing all single quotes with double quotes.Replacing single quotes with double quotes certainly helps. Looking at the codein 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=''' or1=1--and Pass=''

The query now looks for records where the UserIDmatches 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? Suchvalues do not require delimiting with single quotes and thus will not requirethe attacker to input a single quote to mount an attack. And, of course, theattacker can enter hex equivalents of the attack characters. Next GenerationSecurity Software's two advanced SQL Injection papers (http://www.nextgenss.com/research.html#papers)provide numerous examples describing why quote replacement is not enough.

AllDynamically Built Queries are Targets

Typical examples of SQL Injection use login forms andsearch screens because they are found on most Web sites. Unfortunately, thissometimes creates a misperception that other database-related forms do notrequire the same level of defense. This is categorically false. Any andall dynamically built queries (for example, queries built using input from Webforms) are potential targets for SQL Injection attacks. You should review anddefend any such form on your Web site.