Disaster Recovery Procedures in SQL Server 2005 Part 1

Problem
SQL Server 2005 has given us a lot of options on implementing high availability and disaster recovery. More than the technologies themselves, it is important to come up with the proper procedures as we manage different disaster recovery scenarios. How do we come up with procedures for various SQL Server disaster recovery scenarios?

Solution
This series of articles will look at different disaster recovery scenarios and the procedures involved in your recovery plan. When considering disaster recovery options for your SQL Server 2005 database, you should include as many technologies as you can so you'll have a pool of options to choose from if a disaster arises. While having these technologies prove to be important, it is the process that goes with it that makes it effective.  For this article, let's take a simple scenario where a user accidentally dropped or truncated a table about 5 hours after a database backup was taken. Restoring from a database backup would mean losing 5 hours worth of data. And for most companies, they would rather opt for loss of time than loss of data. Plus, if this was a very large database, it would take quite some time to recover and bring it online. We'll take this scenario to build a procedural approach to recover the database as quickly as possible while minimizing data loss. We will use the Northwind database to demonstrate the process. Remember to change Northwind's database recovery model to FULL before working through the steps below.

1) Make sure you have a very good backup

For the scenario above, let's say you have a daily backup running at 6:00 AM and there are no database snapshots created on a regular basis. Your database is configured to use a single MDF and LDF file, which is not quite right for disaster recovery. Let's generate a full database backup for our Northwind database which will be the starting point for database recovery.  Here is that code:
 

USE master  GO  BACKUP DATABASE Northwind      TO DISK = N'D:\DBBackup\NorthwindBackup.bak'     WITH NAME = N'Full Database Backup', DESCRIPTION = 'Starting point for recovery',      INIT, STATS = 10  GO

Looking at the Northwind database schema, you won't easily be able to drop theProducts, Orders and Customers table due to foreign key constraints defined by other tables like the Order Details table. But I bet you can easily drop the Order Detailstable. Let's simulate that disaster by dropping the table at around 11:00AM.

DROP TABLE  [Order Details]

2) Contain the problem

Since the database only has a single MDF and LDF file, we couldn't do much. All we can do is take the database offline by setting it to restricted user mode

USE master  GO  ALTER DATABASE Northwind     SET RESTRICTED_USER     WITH ROLLBACK IMMEDIATE   GO

This will effectively take the database offline, terminating all active connections. This is where the clock starts ticking and we need to take action. Keep in mind your RPO and RTO while proceeding with the next steps.

3) Backup the transaction log

A good DBA would know that the first thing to do when disaster strikes is to backup the transaction log - assuming that your database is set to FULL recovery model. This is to make sure you still have all the active transactions in the log since your last backup. In our scenario, since the last backup - FULL backup, in this case - occurred at 6:00AM.

BACKUP LOG Northwind      TO DISK = N'D:\DBBackup\NorthwindBackupLog.trn'     WITH NAME = N'Transaction Log Backup'     , DESCRIPTION = 'Getting everything to current point in time.',          STATS = 10  GO

4) Restore the database to a known good point-in-time

Now, any user who accidentally dropped a table or caused any damage on a database will not tell you immediately. Sometimes, you may have to dig it up for yourself but that would take quite a lot of time. Since we wanted to bring the database back online as fast as we can, let's just assume a "known good" point-in-time and leave the digging for a later time. In the script below, I have chosen to use 10:42AM in my STOPAT parameter as my "known good" point-in-time for demonstration purposes.

RESTORE DATABASE Northwind  FROM DISK = N'D:\DBBackup\NorthwindBackup.bak'  WITH NORECOVERY, RESTRICTED_USER GO  RESTORE LOG Northwind  FROM DISK = N'D:\DBBackup\NorthwindBackupLog.trn'  WITH RESTRICTED_USER,   STOPAT = '2008-09-23 10:42:44.00', RECOVERY  -- use a "known good" point in time  GO

Although we have restored the database to a "known good" point-in-time, we don't really know how much data we've lost. We need to find out the exact time when the very lastINSERT statement occurred prior to the DROP TABLE statement so that we can recover as much data as we possibly can. But we don't want to do this directly on the server as we need to get the database back online as quickly as we can. This is where the succeeding steps would prove to be very valuable

You can validate whether the dropped table has been restored by running a query against it.

SELECT *  FROM Northwind.dbo.[Order Details] GO

5) Create a snapshot of the restored point

We will create a database snapshot of the restored database for further processing. This database snapshot will be our reference in recovering data to the exact time prior to theDROP TABLE statement.

USE master  GO  CREATE DATABASE Northwind_RestorePointSnapshot  ON  ( NAME = N'Northwind',     FILENAME = N'D:\DBBackup\NorthwindData_RestorePontSnapshot.snap')  AS SNAPSHOT OF [Northwind] GO

Depending on the table schema, we can opt to leave it as it is, as what we will do for theOrder Details table, or do a few more tasks. If the table has an existing IDENTITYcolumn, we need to create a gap between the maximum value for our IDENTITY column and assumed number of rows which we need to recover. This, of course, will depend on the number of transactions that occur on the server. To identify the maximum value of the IDENTITY column, you can run the DBCC CHECKIDENT command as shown below:

DBCC CHECKIDENT ('tableName') --Displays the number of rows you have for the restored table  GO

This will return the maximum value of the IDENTITY column. Let's say that the number of transactions on this table per day is around 4000 records, we can create a gap between the maximum value and the next value. If the maximum value for the IDENTITY column is25000, we need to add 4000 to it and run the DBCC CHECKIDENT command again with the RESEED parameter (we are simply assuming that you can recover the lost data within a day, thus, the value 400):

DBCC CHECKIDENT ('tableName', RESEED, 29000 ) --Creates a gap of for the IDENTITY column to start the next value at 29000  GO

6) Bring the database back online

Once you have managed to do that, change the database option to bring it online and allow users to connect back and run their applications.

USE master  GO  ALTER DATABASE Northwind     SET MULTI_USER GO

Now the database is back online and the dropped table has been restored. Although everyone is happy by now, our job as a DBA does not stop here. Remember that we still need to recover the lost data from the "known good" point-in-time to the time before theDROP TABLE command was executed. That is the only way we can recover as much data as we can. Though there are a few third-party tools we can use to read the transaction log and recover the data by replaying those transactions back, most of us do not have the luxury of playing around with those tools. So our next best bet would be to use the RESTORE with STOPAT option. It's a bit tedious and sometimes very stressful as one mistake would plunge you back into repeating the entire process. All we need here is to find out the times we did our backups until the end of the tail (transaction log) backup. In our scenario, the last backup occurred at 6:00AM and your "known good" point-in-time is at 10:42.44AM. Therefore, you can start doing a RESTORE with the STOPAT option from 10:42.44AM and change the STOPAT time value to maybe a second. If you are not quite sure when the last backup occurred, you can always query theMSDB database.

SELECT     *  FROM msdb.dbo.backupset AS a    INNER  JOIN msdb.dbo.backupmediafamily AS b          ON a.media_set_id = b.media_set_id  WHERE database_name = 'Northwind'  ORDER BY backup_finish_date

Note the backup_finish_date and the type columns as this will give you an idea on the time you need to consider for your STOPAT value in your RESTORE command.

7) Restore another copy of the damaged database with a different name for investigation

Restoring another copy of the damaged database with a different name will allow us to work on the restoration of the data without worrying about the availability as we've already managed to bring up the production database. Just make sure you select a different name and database file location for the restored database or you'll end up damaging the already brought up database. We will just repeat what we did in step #4but with a twist - different name and database file location.

RESTORE DATABASE Northwind_recover   FROM DISK = N'D:\DBBackup\NorthwindBackup.bak'   WITH MOVE N'Northwind' TO N'D:\DBBackup\NorthwindData_recover.mdf',               MOVE N'Northwind_Log' TO N'D:\DBBackup\NorthwindLog_recover.ldf',               STANDBY = N'D:\DBBackup\Northwind_UNDO.bak',             STATS = 10  GO RESTORE LOG Northwind_recover   FROM DISK = N'D:\DBBackup\NorthwindBackupLog.trn'   WITH STANDBY = N'D:\DBBackup\Northwind_UNDO.bak',             STATS = 10, STOPAT = '2008-09-23 10:42:44.00' GO

Document the value of your STOPAT parameter as this will be the most critical parameter you'll ever work with during this process. Since we just repeated the process in step#4, we know for a fact that the DROP TABLE command has not been executed at this time.

8) Restore the transaction log by increasing the value of the STOPAT parameter

We run another RESTORE LOG command, increasing the STOPAT parameter value by a minute - from 10:42:44.00 to 10:43:44.00

RESTORE LOG Northwind_recover   FROM DISK = N'D:\DBBackup\NorthwindBackupLog.trn'   WITH STANDBY = N'D:\DBBackup\Northwind_UNDO.bak',             STATS = 10, STOPAT = '2008-09-23 10:43:44.00' GO

This is the part where it becomes iterative. Don't be frustrated at this point as it will be really tedious. You can increase the value by a minute, 5 minutes, 10 minutes and document the time. Remember to run a test query on the dropped object after running the RESTORE LOG command. I would recommend creating a table for this activity that would look something like this.

TIME	OBJECT EXISTED?
10:43:44.00	YES
10:48:44.00	YES
10:58:44.00	YES
11:03:44.00	NO

With this information, you know for a fact that the table was dropped between10:58:44.00 to 11:03:44.00. You can repeat step #8 and increase the value of theSTOPAT parameter by a minute or even a second if you may since you already have a smaller interval to work with. If you find yourself overshooting the time value of theSTOPAT parameter, go back to step #7 armed with the tabular information you've documented in step #8, making the restore process a bit faster. Just remember to use the WITH RECOVERY option at the last RESTORE LOG statement like this

RESTORE LOG Northwind_recover   FROM DISK = N'D:\DBBackup\NorthwindBackupLog.trn'   WITH STATS = 10, STOPAT = '2008-09-23 11:01:44.00', RECOVERY  GO

Once you've managed to restore the database to the time before the DROP TABLEcommand was executed, you can now do a comparison between what was restored on the production database and what was recovered. You can do this in a number of different ways. Since we already have a database snapshot created earlier, we will use that together with the TableDiff utility. Although the tool was designed for replication, we can use it for disaster recovery as well. A previous tip on SQL Server 2005 tablediff command line utility can give you an overview on how to use this tool but just to highlight that your source database will be the one that you recovered and the destination database will be your database snapshot. This is where your database snapshot would prove to be very important especially if you are dealing with more than one object which is normally the case. If you are not comfortable with command-line utilities, a GUI version was created by the guys from SQLTeam.com. You might want to check that out as well and include it in your DBA toolbox

You can also do an INSERT/SELECT where you insert records on the production database based on a query on the recovered database. Since our Order Details table does not have an IDENTITY column, we can create our own by inserting the records in a temporary table and using the ROW_NUMBER() function:

--This inserts records in a temporary table and assigns a dummy identity value for reference  SELECT  ROW_NUMBER() OVER (ORDER BY OrderID) AS ROWID, *   INTO Northwind_recover.dbo.OrderDetailsRecover  FROM [Order Details]  --This inserts recovered records from the recovered database into the production database based on --the dummy identity valuewe have assigned for reference INSERT INTO Northwind.dbo.[Order Details] (OrderID,ProductId,UnitPrice,Quantity,Discount)  SELECT OrderID,ProductId,UnitPrice,Quantity,Discount   FROM Northwind_recover.dbo.OrderDetailsRecover  WHERE ROWID>                 (                     SELECT COUNT(*)                      FROM Northwind_RestorePointSnapShot.dbo.[Order Details]                 )

Notice that we used our database snapshot to identify the difference between what we managed to restore and what we have recovered.

Next Steps

• It is important to have a disaster recovery plan in place and the procedures necessary for recovery. While it is impossible to come up with procedures for almost every type of disaster, it would help if you start listing the possible disasters that may happen and prepare a disaster recovery plan with procedures and document accordingly.
• Simulate this particular process by going thru the steps outlined above.
• You can download the Northwind database used in the sample here.

Using SQL Server datetime functions GETDATE, DATENAME and DATEPART

Transact-SQL includes a set of functions that let you retrieve the current date and time or retrieve the individual parts of a DATETIME or SMALLDATETIME value. For example, you can extract the day, month or year from a datetime value, as well as the quarter, week, hour or even the millisecond. In this article, I describe each of these functions and provide examples that demonstrate how to use these functions to retrieve datetime data in SQL Server. Note that this article assumes that you have a working knowledge of T-SQL and the DATETIME and SMALLDATETIME data types. For more information about these types, see part one in this series, Basics for working with DATETIME and SMALLDATETIME in SQL Server 2005.

Retrieving the current date and time

One of the handiest datetime functions in T-SQL is GETDATE, which retrieves the current date and time based on the clock settings on the local system. To use GETDATE, simply call the function in your T-SQL statement without specifying any arguments, as in the following example:

SELECT GETDATE() AS [Current Date/Time]

In this case, I use GETDATE in the SELECT list to retrieve the date/time value. (Note that you must include the ending set of parentheses even if you don't pass in any arguments.) The statement returns results similar to the following:

Current Date/Time

2008-07-29 10:45:13.327

By default, the GETDATE function returns the datetime value in the format shown here. However, you can change the format of the results by using the CONVERT function. For information about using CONVERT, refer to part two in this tip.

Another Transact-SQL function that is just as easy to use is GETUTCDATE, which retrieves the current Coordinated Universal Time (UTC) -- also referred to as Greenwich Mean Time. The retrieved value is based on the clock and time zone settings on the local system. As you saw with GETDATE, you call GETUTCDATE within your Transact-SQL statement without including any arguments, as shown in the following example:

SELECT GETUTCDATE() AS [UTC Date/Time]

When you run this statement, you receive results similar to the following:

UTC Date/Time

2008-07-29 17:45:13.327

Notice that the time returned here is seven hours later than the time shown in the previous example. I ran both of these statements at the same time on a system configured for the Pacific time zone (during daylight savings time).

As you've seen in the last two examples, the functions are included within the SELECT list. However, the functions can be especially beneficial when using them to define a default value in your table definition. For example, the following three statements create the Orders table -- including a DATETIME column (OrderDate) -- insert data into the table and retrieve that data:

CREATE TABLE Orders 
(
OrderID INT PRIMARY KEY IDENTITY, 
Product VARCHAR(30) NOT NULL, 
OrderAmt INT NOT NULL, 
OrderDate DATETIME NOT NULL DEFAULT GETDATE()
) 
GO
INSERT INTO Orders (Product, OrderAmt) 
VALUES('Test Product', 12) 
GO
SELECT * FROM Orders

The OrderDate column definition includes a DEFAULT clause that specifies GETDATE as the default value. As a result, when you insert a row into the table, the current date and time are automatically inserted into the column, as shown in the results returned by the SELECT statement:

OrderID	Product	OrderAmt	OrderDate
1	Test Product	12	2008-07-29 10:46:47.420

You can use the information as a timestamp in order to track when records are added and to assist in auditing the data, if necessary. This is also handy for other operations that use the timestamp when retrieving data. For example, an extract, transform and load (ETL) process might reference the timestamp when determining whether to extract or update data.

Retrieving the year, month or day

In some cases, you might want to retrieve the year, month or day from a DATETIME or SMALLDATETIME value. One approach is to use the YEAR, MONTH or DAY function to retrieve the necessary data (as an integer). The following SELECT statement is an example of how this works:

SELECT YEAR(PostTime) AS [Year], 
MONTH(PostTime) AS [Month], 
DAY(PostTime) AS [Day] 
FROM DatabaseLog
WHERE DatabaseLogID = 1

The SELECT clause includes three column expressions. The first one uses the YEAR function to retrieve the year from the PostTime column in the DatabaseLog table (in the AdventureWorks sample database). When you call the YEAR function, you specify the column name (or other expression) as an argument to the function. The MONTH and DAY functions work the same way. The second column expression in the SELECT clause uses the MONTH function to retrieve the month from the PostTime column, and the third expression uses DAY to retrieve the day. The following results show you the type of information that the statement returns:

Year	Month	Day
2005	10	14

Each value is extracted from the PostTime column and returned as an integer. (The value stored in the table is 2005-10-14 01:58:27.567.)

These functions are an easy way to retrieve the year, month or day, but, in some cases, you might want more control over the type of values returned as well as the format of those values. In addition, you might want to extract the time from the date/time value. Fortunately, Transact-SQL supports functions that provide this type of functionality.

Retrieving parts of a date/time value

Like the YEAR, MONTH and DAY functions, the DATEPART function returns an integer representing a specific part of the date/time value. For example, the following SELECT statement returns the same results as the preceding example:

SELECT DATEPART(yy, PostTime) AS [Year], 
DATEPART(mm, PostTime) AS [Month], 
DATEPART(dd, PostTime) AS [Day] 
FROM DatabaseLog
WHERE DatabaseLogID = 1

The first thing to note is that, when you call DATEPART, you specify two arguments. The first argument determines the date/time component to retrieve, and the second argument is the source column. For the first argument, you must use one of the supported abbreviations to specify the datetime part. The following table lists the date/time parts you can retrieve and the abbreviations you must use to retrieve those parts:

Date/time part	Abbreviations
year	yy, yyyy
quarter	qq, q
month	mm, m
day of year	dy, y
day	dd, d
week	wk, ww
weekday	dw
hour	hh
minute	mi, n
second	ss, s
millisecond	ms

For some datetime parts, more than one abbreviation is supported. For example, you can use "yy" or "yyyy" as your first DATEPART argument to retrieve the year from the date/time value. Notice that the table includes abbreviations for date/time parts other than year, month or day. In other words, you can retrieve the quarter, the day of the year, the week of the year, and the weekday as shown in the following SELECT statement:

SELECT DATEPART(qq, PostTime) AS [Quarter], 
DATEPART(dy, PostTime) AS [DayOfYear], 
DATEPART(wk, PostTime) AS [Week], 
DATEPART(dw, PostTime) AS [Weekday] 
FROM DatabaseLog
WHERE DatabaseLogID = 1

As in the preceding example, each instance of DATEPART includes two arguments: the date/time part abbreviation and the source column. The statement returns the following results:

Quarter	DayOfYear	Week	Weekday
4	287	42	6

Notice that the weekday is shown as 6. By default, SQL Server begins the week with Sunday, so weekday 6 is equivalent to Friday.

The preceding two examples retrieved only values related to dates. However, as the table below shows, you can also retrieve data related to time:

SELECT DATEPART(hh, PostTime) AS [Hour], 
DATEPART(mi, PostTime) AS [Minute], 
DATEPART(ss, PostTime) AS [Second], 
DATEPART(ms, PostTime) AS [Millisecond] 
FROM DatabaseLog
WHERE DatabaseLogID = 1

In this case, the statement is retrieving the hour, minute, second and millisecond, as shown in the following results:

Hour	Minute	Second	Millisecond
1	58	27	567

The primary limitation of the DATEPART function is that it returns only integers, which is why Friday is shown as 6. However, if you want to display actual names of days and months, you can use the DATENAME function. The DATENAME function works exactly like the DATEPART function. DATENAME takes the same number of arguments and supports the same abbreviations. For example, if you want to retrieve the year, month and day, as you saw in an earlier example, you simply replace DATEPART with DATENAME:

SELECT DATENAME(yy, PostTime) AS [Year], 
DATENAME(mm, PostTime) AS [Month], 
DATENAME(dd, PostTime) AS [Day] 
FROM DatabaseLog
WHERE DatabaseLogID = 1

Now your results will look like the following:

Year	Month	Day
2005	October	14

The month value is now October, rather than 10. The year and day, however, remain integers because that's the only way to represent them. You can also use the DATENAME function for other date/time components, as in the following example:

SELECT DATENAME(qq, PostTime) AS [Quarter], 
DATENAME(dy, PostTime) AS [DayOfYear], 
DATENAME(wk, PostTime) AS [Week], 
DATENAME(dw, PostTime) AS [Weekday] 
FROM DatabaseLog
WHERE DatabaseLogID = 1

Once again, I've replaced DATEPART with DATENAME, but changed nothing else. The statement returns the following results.

Quarter	DayOfYear	Week	Weekday
4	287	42	Friday

Notice that the quarter, day of the year and week are still integers, but the weekday now says Friday, rather than 6. You can also use DATENAME to retrieve the time components of a date/time value, but the results will always be integers, as you would expect.

 

Monitoring Changes in Your Database Using DDL Triggers

Introduction

Additions, deletions, or changes to objects in a database can cause a great deal of hardship and require a dba or developer to rewrite existing code that may reference affected entities. To make matters worse tracking down the problematic alteration(s) may be synonymous to locating the needle in the haystack. Utilizing a DDL trigger in conjunction with a single user created table, used to document such changes, can considerably minimize the headaches involved in tracking and locating schema changes.

Creating the Table and DDL TRIGGER

The first step in implementing such a tracking strategy is to create a table that will be used to record all DDL actions fired from within a database. The below code creates a table in the AdventureWorks sample database that will be used to hold all such DDL actions:

USE AdventureWorks

GO

CREATE TABLE AuditLog

(ID        INT PRIMARY KEY IDENTITY(1,1),

Command    NVARCHAR(1000),

PostTime   NVARCHAR(24),

HostName   NVARCHAR(100),

LoginName  NVARCHAR(100)

)

GO

After creating the table to hold our DDL events it is now time to create a DDL trigger that will be specific to the AdventureWorks database and will fire on all DDL_DATABASE_LEVEL_EVENTS:

CREATE TRIGGER Audit ON DATABASE

FOR DDL_DATABASE_LEVEL_EVENTS

AS

DECLARE @data XML

DECLARE @cmd NVARCHAR(1000)

DECLARE @posttime NVARCHAR(24)

DECLARE @spid NVARCHAR(6)

DECLARE @loginname NVARCHAR(100)

DECLARE @hostname NVARCHAR(100)

SET @data = EVENTDATA()

SET @cmd = @data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(1000)')

SET @cmd = LTRIM(RTRIM(REPLACE(@cmd,'','')))

SET @posttime = @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'NVARCHAR(24)')

SET @spid = @data.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(6)')

SET @loginname = @data.value('(/EVENT_INSTANCE/LoginName)[1]',

    'NVARCHAR(100)')

SET @hostname = HOST_NAME()

INSERT INTO dbo.AuditLog(Command, PostTime,HostName,LoginName)

 VALUES(@cmd, @posttime, @hostname, @loginname)

GO

The purpose of the trigger is to capture the EVENTDATA() that is created once the trigger fires and parse the data from the xml variable inserting it into the appropriate columns of our AuditLog table. The parsing of the EVENTDATA() is rather straight forward, except for when extracting the command text. The parsing of the command text includes the following code:

SET@cmd = LTRIM(RTRIM(REPLACE(@cmd,'','')))

The need for the LTRIM and RTRIM is to strip all leading and trailing white space while the REPLACE is used to remove the carriage return that is added when if using the scripting wizard from SSMS. This will provide the future ability to use SSRS string functions to further parse the command text to offer greater detail.

Once the table and trigger have been created you can test to assure that it is working properly:

UPDATE STATISTICS Production.Product

GO

CREATE TABLE dbo.Test(col INT)

GO

DROP TABLE dbo.Test

GO

-- View log table

SELECT *

FROM dbo.AuditLog

GO

The results of the above query should are shown below:

Conclusions

By creating a table to hold all DDL actions and a database level DDL trigger we can successfully capture all DDL level changes to our database and provide greater ability to track and monitor any such change.

As performance of any such action(s) is most often the deciding factor as to whether implement such change control, I have limited excessive parsing or formatting in the above trigger. Consider this the first step, documenting. Later I will post how to utilize reporting services to provide reports showing:

1. DDL action, CREATE, ALTER, DELETE, etc

2. The schema and object affected

3. Workstation executing DDL statements

4. Drill down report to show object dependencies

That will use the documenting objects created above to provide greater insight and detail external of your production environment.

 

How To Protect From SQL Injection in ASP.NET

patterns & practices Developer Center

J.D. Meier, Alex Mackman, Blaine Wastell, Prashant Bansode, Andy Wigley

Applies To : ASP.NET version 1.1, ASP.NET version 2.0

Summary

This How To shows a number of ways to help protect your ASP.NET application from SQL injection attacks. SQL injection can occur when an application uses input to construct dynamic SQL statements or when it uses stored procedures to connect to the database. Conventional security measures, such as the use of SSL and IPSec, do not protect your application from SQL injection attacks. Successful SQL injection attacks enable malicious users to execute commands in an application's database.

Countermeasures include using a list of acceptable characters to constrain input, using parameterized SQL for data access, and using a least privileged account that has restricted permissions in the database. Using stored procedures with parameterized SQL is the recommended approach because SQL parameters are type safe. Type-safe SQL parameters can also be used with dynamic SQL. In situations where parameterized SQL cannot be used, consider using character escaping techniques.

Contents

Objectives
Overview
Summary of Steps
Step 1. Constrain Input
Step 2. Use Parameters with Stored Procedures
Step 3. Use Parameters with Dynamic SQL
Additional Considerations
Additional Resources

Objectives

• Learn how SQL injection attacks work.
• Constrain input to prevent SQL injection.
• Use type safe SQL command parameters to prevent SQL injection.
• Use a least privileged account to connect to the database.
• Learn additional countermeasures to further reduce risk.

Overview

A successful SQL injection attack enables a malicious user to execute commands in your application's database by using the privileges granted to your application's login. The problem is more severe if your application uses an over-privileged account to connect to the database. For example, if your application's login has privileges to eliminate a database, then without adequate safeguards, an attacker might be able to perform this operation.

Common vulnerabilities that make your data access code susceptible to SQL injection attacks include:

• Weak input validation.
• Dynamic construction of SQL statements without the use of type-safe parameters.
• Use of over-privileged database logins.

SQL Injection Example

Consider what happens when a user types the following string in the SSN text box, which is expecting a Social Security number of the form nnn-nn-nnnn.

; DROP DATABASE pubs  --

Using the input, the application executes the following dynamic SQL statement or stored procedure, which internally executes a similar SQL statement.

// Use dynamic SQL

 SqlDataAdapter myCommand = new SqlDataAdapter(

          "SELECT au_lname, au_fname FROM authors WHERE au_id = '" + 

          SSN.Text + "'", myConnection);

// Use stored procedures

SqlDataAdapter myCommand = new SqlDataAdapter(

                                "LoginStoredProcedure '" + 

                                  SSN.Text + "'", myConnection);

The developer's intention was that when the code runs, it inserts the user's input and generates a SQL the following statement.

SELECT au_lname, au_fname FROM authors WHERE au_id = '172-32-9999'  

However, the code inserts the user's malicious input and generates the following query.

SELECT au_lname, au_fname FROM authors WHERE au_id = ''; DROP DATABASE pubs --'

In this case, the ' (single quotation mark) character that starts the rogue input terminates the current string literal in the SQL statement. It closes the current statement only if the following parsed token does not make sense as a continuation of the current statement but does make sense as the start of a new statement. As a result, the opening single quotation mark character of the rogue input results in the following statement.

SELECT au_lname, au_fname FROM authors WHERE au_id = ''

The; (semicolon) character tells SQL that this is the end of the current statement, which is then followed by the following malicious SQL code.

; DROP DATABASE pubs

Note   The semicolon is not necessarily required to separate SQL statements. This is dependent on vendor or implementation, but Microsoft SQL Server does not require them. For example, SQL Server parses the following as two separate statements:

SELECT * FROM MyTable DELETE FROM MyTable

Finally, the -- (double dash) sequence of characters is a SQL comment that tells SQL to ignore the rest of the text. In this case, SQL ignores the closing ' (single quotation mark) character, which would otherwise cause a SQL parser error.

--'

Guidelines

To counter SQL injection attacks, you need to:

• Constrain and sanitize input data. Check for known good data by validating for type, length, format, and range.
• Use type-safe SQL parameters for data access. You can use these parameters with stored procedures or dynamically constructed SQL command strings. Parameter collections such as SqlParameterCollection provide type checking and length validation. If you use a parameters collection, input is treated as a literal value, and SQL Server does not treat it as executable code. An additional benefit of using a parameters collection is that you can enforce type and length checks. Values outside of the range trigger an exception. This is a good example of defense in depth.
• Use an account that has restricted permissions in the database. Ideally, you should only grant execute permissions to selected stored procedures in the database and provide no direct table access.
• Avoid disclosing database error information. In the event of database errors, make sure you do not disclose detailed error messages to the user.

Note   Conventional security measures, such as the use of Secure Socket Layer (SSL) and IP Security (IPSec), do not protect your application from SQL injection attacks.

Summary of Steps

To protect your application from SQL injection, perform the following steps:

• Step 1. Constrain input.
• Step 2. Use parameters with stored procedures.
• Step 3. Use parameters with dynamic SQL.

Step 1. Constrain Input

You should validate all input to your ASP.NET applications for type, length, format, and range. By constraining the input used in your data access queries, you can protect your application from SQL injection.

Note   When constraining input, it is a good practice to create a list of acceptable characters and use regular expressions to reject any characters that are not on the list. The potential risk associated with using a list of unacceptable characters is that it is always possible to overlook an unacceptable character when defining the list; also, an unacceptable character can be represented in an alternate format to pass validation.

Constrain Input in ASP.NET Web Pages

Start by constraining input in the server-side code for your ASP.NET Web pages. Do not rely on client-side validation because it can be easily bypassed. Use client-side validation only to reduce round trips and to improve the user experience.

If you use server controls, use the ASP.NET validator controls, such as the RegularExpressionValidator and RangeValidator controls to constrain input. If you use regular HTML input controls, use the Regex class in your server-side code to constrain input.

If in the previous code example, the SSN value is captured by an ASP.NET TextBox control, you can constrain its input by using a RegularExpressionValidator control as shown in the following.

<%@ language="C#" %>

 <form id="form1" runat="server">

    <asp:TextBox ID="SSN" runat="server"/>

    <asp:RegularExpressionValidator ID="regexpSSN" runat="server"         

                                    ErrorMessage="Incorrect SSN Number" 

                                    ControlToValidate="SSN"         

                                     ValidationExpression="^\d{3}-\d{2}-\d{4}$" />

</form>

If the SSN input is from another source, such as an HTML control, a query string parameter, or a cookie, you can constrain it by using the Regex class from the System.Text.RegularExpressions namespace. The following example assumes that the input is obtained from a cookie.

using System.Text.RegularExpressions;

if (Regex.IsMatch(Request.Cookies["SSN"], "^\d{3}-\d{2}-\d{4}$"))

 {

    // access the database

 }

else

{

    // handle the bad input

} 

For more information about how to constrain input in your ASP.NET Web pages, see How To: Protect From Injection Attacks in ASP.NET.

Constrain Input in Data Access Code

In some situations, you need to provide validation in your data access code, perhaps in addition to your ASP.NET page-level validation. Two common situations where you need to provide validation in your data access code are:

• Untrusted clients. If the data can come from an untrusted source or you cannot guarantee how well the data has been validated and constrained, add validation logic that constrains input to your data access routines.
• Library code. If your data access code is packaged as a library designed for use by multiple applications, your data access code should perform its own validation, because you can make no safe assumptions about the client applications.

The following example shows how a data access routine can validate its input parameters by using regular expressions prior to using the parameters in a SQL statement.

using System;

 using System.Text.RegularExpressions;

 public void CreateNewUserAccount(string name, string password)

{

     // Check name contains only lower case or upper case letters, 

    // the apostrophe, a dot, or white space. Also check it is 

    // between 1 and 40 characters long

    if ( !Regex.IsMatch(userIDTxt.Text, @"^[a-zA-Z'./s]{1,40}$"))

      throw new FormatException("Invalid name format");

    // Check password contains at least one digit, one lower case 

    // letter, one uppercase letter, and is between 8 and 10 

     // characters long

    if ( !Regex.IsMatch(passwordTxt.Text, 

                      @"^(?=.*\d)(?=.*[a-z])(?=.*[A-Z]).{8,10}$" ))

       throw new FormatException("Invalid password format");

    // Perform data access logic (using type safe parameters)

     ...

}

Step 2. Use Parameters with Stored Procedures

Using stored procedures does not necessarily prevent SQL injection. The important thing to do is use parameters with stored procedures. If you do not use parameters, your stored procedures can be susceptible to SQL injection if they use unfiltered input as described in the "Overview" section of this document.

The following code shows how to use SqlParameterCollection when calling a stored procedure.

using System.Data;

 using System.Data.SqlClient;

 using (SqlConnection connection = new SqlConnection(connectionString))

{

   DataSet userDataset = new DataSet();

  SqlDataAdapter myCommand = new SqlDataAdapter( 

             "LoginStoredProcedure", connection);

   myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;

  myCommand.SelectCommand.Parameters.Add("@au_id", SqlDbType.VarChar, 11);

  myCommand.SelectCommand.Parameters["@au_id"].Value = SSN.Text;

  myCommand.Fill(userDataset);

 }

In this case, the @au_id parameter is treated as a literal value and not as executable code. Also, the parameter is checked for type and length. In the preceding code example, the input value cannot be longer than 11 characters. If the data does not conform to the type or length defined by the parameter, the SqlParameter class throws an exception.

Review Your Application's Use of Parameterized Stored Procedures

Because using stored procedures with parameters does not necessarily prevent SQL injection, you should review your application's use of this type of stored procedure. For example, the following parameterized stored procedure has several security vulnerabilities.

CREATE PROCEDURE dbo.RunQuery

 @var ntext

AS

        exec sp_executesql @var

GO

An application that uses a stored procedure similar to the one in the preceding code example has the following vulnerabilities:

• The stored procedure executes whatever statement is passed to it. Consider the @var variable being set to:

DROP TABLE ORDERS;

In this case, the ORDERS table will be dropped.

• The stored procedure runs with dbo privileges.
• The stored procedure's name (RunQuery) is a poor choice. If an attacker is able to probe the database, he or she will see the name of the stored procedure. With a name like RunQuery, he can guess that the stored procedure is likely to run the supplied query.

Step 3. Use Parameters with Dynamic SQL

If you cannot use stored procedures, you should still use parameters when constructing dynamic SQL statements. The following code shows how to use SqlParametersCollection with dynamic SQL.

using System.Data;

 using System.Data.SqlClient;

 using (SqlConnection connection = new SqlConnection(connectionString))

{

   DataSet userDataset = new DataSet();

  SqlDataAdapter myDataAdapter = new SqlDataAdapter(

         "SELECT au_lname, au_fname FROM Authors WHERE au_id = @au_id", 

          connection);                

  myCommand.SelectCommand.Parameters.Add("@au_id", SqlDbType.VarChar, 11);

  myCommand.SelectCommand.Parameters["@au_id"].Value = SSN.Text;

   myDataAdapter.Fill(userDataset);

}

Using Parameter Batching

A common misconception is that if you concatenate several SQL statements to send a batch of statements to the server in a single round trip, you cannot use parameters. However, you can use this technique if you make sure that parameter names are not repeated. You can easily do this by making sure that you use unique parameter names during SQL text concatenation, as shown here.

 

using System.Data;

 using System.Data.SqlClient;

. . .

 using (SqlConnection connection = new SqlConnection(connectionString))

{

   SqlDataAdapter dataAdapter = new SqlDataAdapter(

       "SELECT CustomerID INTO #Temp1 FROM Customers " +

       "WHERE CustomerID > @custIDParm; SELECT CompanyName FROM Customers " +

        "WHERE Country = @countryParm and CustomerID IN " +

       "(SELECT CustomerID FROM #Temp1);",

       connection);

  SqlParameter custIDParm = dataAdapter.SelectCommand.Parameters.Add(

                                          "@custIDParm", SqlDbType.NChar, 5);

   custIDParm.Value = customerID.Text;

   SqlParameter countryParm = dataAdapter.SelectCommand.Parameters.Add(

                                      "@countryParm", SqlDbType.NVarChar, 15);

  countryParm.Value = country.Text;

  connection.Open();

  DataSet dataSet = new DataSet();

  dataAdapter.Fill(dataSet);

}

. . .

Additional Considerations

Other things to consider when you develop countermeasures to prevent SQL injection include:

• Use escape routines to handle special input characters.
• Use a least-privileged database account.
• Avoid disclosing error information.

Use Escape Routines to Handle Special Input Characters

In situations where parameterized SQL cannot be used and you are forced to use dynamic SQL instead, you need to safeguard against input characters that have special meaning to SQL Server (such as the single quote character). If not handled, special characters such as the single quote character in the input can be utilized to cause SQL injection.

Note   Special input characters pose a threat only with dynamic SQL and not when using parameterized SQL.

Escape routines add an escape character to characters that have special meaning to SQL Server, thereby making them harmless. This is illustrated in the following code fragment:

private string SafeSqlLiteral(string inputSQL)

 {

  return inputSQL.Replace("'", "''");
 
}

Use a Least-Privileged Database Account

Your application should connect to the database by using a least-privileged account. If you use Windows authentication to connect, the Windows account should be least-privileged from an operating system perspective and should have limited privileges and limited ability to access Windows resources. Additionally, whether or not you use Windows authentication or SQL authentication, the corresponding SQL Server login should be restricted by permissions in the database.

Consider the example of an ASP.NET application running on Microsoft Windows Server 2003 that accesses a database on a different server in the same domain. By default, the ASP.NET application runs in an application pool that runs under the Network Service account. This account is a least privileged account.

To access SQL Server with the Network Service account

1. Create a SQL Server login for the Web server's Network Service account. The Network Service account has network credentials that are presented at the database server as the identity DOMAIN\WEBSERVERNAME$. For example, if your domain is called XYZ and the Web server is called 123, you create a database login for XYZ\123$.
2. Grant the new login access to the required database by creating a database user and adding the user to a database role.
3. Establish permissions to let this database role call the required stored procedures or access the required tables in the database. Only grant access to stored procedures the application needs to use, and only grant sufficient access to tables based on the application's minimum requirements.

For example, if the ASP.NET application only performs database lookups and does not update any data, you only need to grant read access to the tables. This limits the damage that an attacker can cause if the attacker succeeds in a SQL injection attack.

Avoid Disclosing Error Information

Use structured exception handling to catch errors and prevent them from propagating back to the client. Log detailed error information locally, but return limited error details to the client.

If errors occur while the user is connecting to the database, be sure that you provide only limited information about the nature of the error to the user. If you disclose information related to data access and database errors, you could provide a malicious user with useful information that he or she can use to compromise your database security. Attackers use the information in detailed error messages to help deconstruct a SQL query that they are trying to inject with malicious code. A detailed error message may reveal valuable information such as the connection string, SQL server name, or table and database naming conventions.

Additional Resources

• How To: Prevent Cross-Site Scripting in ASP.NET
• How To: Protect From Injection Attacks in ASP.NET
• How To: Use Regular Expressions to Constrain Input in ASP.NET