Congratulations to Quentin Ran, an independent consultant in Houston, Texas, and Anith Sen, an independent database consultant in Cordova, Tennessee. Quentin won first prize of $100 for the best solution to the March Reader Challenge, "Restoring a Database." Anith won second prize of $50. Here’s a recap of the problem and the solution to the March Reader Challenge.
Problem:
Keith is the DBA for a company that runs several SQL Server 2000 data warehouses. He has a crucial database that contains the fact and dimension tables for the data warehouse. Keith performs full backups every week and periodic log backups after the database backup. The company’s development team has requested the latest copy of the database along with any log backups for testing purposes. The team needs to run ad hoc queries against the database at different points in time to collect statistics. Keith uses the following sequence of steps to create the database and log backups:
CREATE DATABASE DW
ALTER DATABASE DW SET recovery bulk_logged
RAISERROR ('-- Performing full backup...', 0, 1) WITH nowait
-- Full backup of database
BACKUP DATABASE DW TO DISK = 'c:\temp\DW.bak' WITH init
GO
-- Create table t1
CREATE TABLE DW..t1 ( i int IDENTITY )
INSERT INTO DW..t1 DEFAULT VALUES
-- Initial log backup
RAISERROR ('-- Initial log backup...', 0, 1) WITH nowait
BACKUP log DW TO DISK = 'c:\temp\DW.trn.1' WITH init
-- Create table t2 for bulk loading
CREATE TABLE DW..t2 ( c char( 8000 ) DEFAULT 'x' )
INSERT DW..t2 DEFAULT VALUES
-- Add new log file on a different volume because of space constraints
ALTER DATABASE DW ADD log FILE ( name = 'DW_TempLog' , filename =
'c:\temp\DW_TempLog.ldf' )
-- Bulk inserts and other operations here
-- Log backup after first ALTER DATABASE command
RAISERROR ('-- Log backup after first ALTER DATABASE...', 0, 1) WITH nowait
BACKUP log DW TO DISK = 'c:\temp\DW.trn.2' WITH init
-- Remove temporary log file
ALTER DATABASE DW REMOVE FILE 'DW_Templog'
-- Log backup after second ALTER DATABASE command
RAISERROR ('-- Log backup after second ALTER DATABASE...', 0, 1) WITH nowait
BACKUP log DW TO DISK = 'c:\temp\DW.trn.3' WITH init
DROP DATABASE DW
GO
Keith also needs to provide the commands for restoring the database (in read-only format) up to and including the latest log backup, DW.trn.3. Help Keith write the script to restore a read-only copy of the database after different backups have been restored on a development server.
Solution:
Keith can use the STANDBY clause of the RESTORE DATABASE or RESTORE LOG statements to create a read-only copy of the database on a different server. The STANDBY clause requires a file that stores the UNDO actions resulting from uncommitted transactions. To restore the read-only copy of the initial database, Keith can use the following statement:
-- Initial database state
RESTORE DATABASE DW1
FROM DISK = 'c:\temp\DW.bak'
WITH MOVE 'DW' TO 'c:\temp\DW1.mdf'
, MOVE 'DW log' TO 'c:\temp\DW1.ldf'
, STANDBY = 'c:\temp\DW1.undo'
The MOVE option in the RESTORE statement must specify an alternate path for the database files if they’re in a different location than the original database. Keith can also restore the first log backup that DW.trn.1 contains by using the following statement:
-- Database after first log backup RESTORE LOG DW1 FROM DISK = 'c:\temp\DW.trn.1' WITH STANDBY = 'c:\temp\DW1.undo'
Note that the MOVE option isn’t required in the RESTORE LOG statement because the database’s structure hasn’t changed since the last restore.
To restore the next log backup that DW.trn.2 contains, Keith needs to use the MOVE option again because a new log file was added to the database before the backup. Here is the RESTORE statement that restores the DW.trn.2 backup file:
-- Database after second log backup
RESTORE LOG DW1
FROM DISK = 'c:\temp\DW.trn.2'
WITH MOVE 'DW_TempLog' TO 'c:\temp\DW1_TempLog.ldf
, STANDBY = 'c:\temp\DW1.undo'
Keith can restore the last log backup, which DW.trn.3 contains, without using the MOVE option because the restore will remove the log file that he previously added.
-- Database after third log backup RESTORE LOG DW1 FROM DISK = 'c:\temp\DW.trn.3' WITH STANDBY = 'c:\temp\DW1.undo'
Alternatively, Keith can use the STOP AT clause with the RESTORE statements to restore a database to a specific point in time.
APRIL READER CHALLENGE:
Now, test your SQL Server savvy in the April Reader Challenge, "Protecting Against SQL Injection" (below). Submit your solution in an email message to [email protected] by March 18. Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We’ll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.
Jeff is a database architect, responsible for designing and developing database solutions using SQL Server 2000. Jeff’s main concern when performing code reviews is the use of dynamic SQL in stored procedures that his Web application uses for search purposes. He’s worried that the dynamic SQL will put his system at risk for SQL injection attacks, in which an attacker compromises the system’s security by executing unauthorized code.
The example below uses the sample Northwind database objects to show how a user exploits the dynamic SQL construction in a stored procedure. The Web page in this example lets the user specify searches for customers or suppliers based on relationship, city, company, or contact name. The Web page executes the following stored procedure, SearchCustomersAndSuppliers:
CREATE PROCEDURE SearchCustomersAndSuppliers
(@Relationship varchar(9) = NULL
,@City nvarchar(30) = NULL
,@CompanyName nvarchar(80) = NULL
,@ContactName nvarchar(60) = NULL
,@Debug bit = 0)
AS
BEGIN
DECLARE @SQL nvarchar(4000), @WhereClause nvarchar(500)
SET @SQL = N'SELECT City, CompanyName, ContactName, Relationship FROM "Customer
AND Suppliers BY City"'
SET @WhereClause = N' WHERE 1=1'
IF @Relationship IS NOT NULL
SET @WhereClause = @WhereClause + ' AND Relationship = ''' + @Relationship + ''''
IF @City IS NOT NULL
SET @WhereClause = @WhereClause + ' AND City LIKE N''' + @City + ''''
IF @CompanyName IS NOT NULL
SET @WhereClause = @WhereClause + ' AND CompanyName LIKE N''' + @CompanyName + ''''
IF @ContactName IS NOT NULL
SET @WhereClause = @WhereClause + ' AND ContactName LIKE N''' + @ContactName + ''''
IF @Debug = 1
PRINT @SQL
IF @Debug = 1
PRINT @WhereClause
EXEC(@SQL + @WhereClause)
END
GO
The parameters for city, company, and contact name let users conduct wildcard searches by using LIKE patterns, meaning they can search for words “like” cty and still get city. The @Relationship parameter limits the search to a specific value.
Using the SearchCustomersAndSuppliers stored procedure, Jeff issues the following command to return the generated queries and a list of customers from the Customers table, which demonstrates the security danger of forming dynamic SQL without specific checks:
EXEC SearchCustomersAndSuppliers @CompanyName = N'%'';SELECT * FROM customers;PRINT ''', @Debug = 1
By injecting SQL code into the search parameters an attacker can potentially perform unauthorized actions depending on the permissions of the user account, the Web page, or application executing the stored procedure.
Help Jeff write the dynamic SQL to prevent SQL injection attacks. Ultimately, he wants to suggest to the developers a more secure dynamic SQL approach as a standard technique for stored procedures that require dynamic SQL.
