/*============================================================================
  File:     SQLInjection.sql

  Summary:  This shows the potential for SQL Injection in a dynamically executed
			string used to create a database.

  Date:     May 2006

  SQL Server Version: 9.00.2047.00 (SP1)
------------------------------------------------------------------------------
  Copyright (C) 2005-2006 Kimberly L. Tripp, SYSolutions, Inc.
  All rights reserved.

  For more scripts and sample code, check out 
    http://www.SQLskills.com

  This script is intended only as a supplement to demos, lectures and 
  resources created by Kimberly L. Tripp, President/Founder - SQLskills.com.  
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

-- These three procedures demonstrate how dynamic string execution can be used
-- securely...and how - if not prepared - users can creatively delete/destroy data.

-- This example came up from a newsgroup...someone wanted to use create database
-- from a client application and was concerned about parsing the string properly to
-- support all database names (even somewhat bad ones :). The answer to the 
-- question (which is to use the function QUOTENAME()) led to quite a security 
-- discussion. The answer provides not only the ability to create a database with 
-- any name but to also do so securely - even with dynamic string execution!

-- This is the procedure that started it all - the following procedure fails when
-- spaces are in the database name.... (which is what led them to ask the question).

CREATE PROCEDURE dbo.CreateDBProc
(
	@DBName	sysname
)
AS
DECLARE @ExecStr	nvarchar(2000)
SELECT @ExecStr = 'CREATE DATABASE ' + @DBName -- + all of the other stuff to place the files, etc...
SELECT @ExecStr
--EXEC(@ExecStr)
GO

EXEC dbo.CreateDBProc 'this is my test database name'
EXEC dbo.CreateDBProc '[fakedbname] DROP DATABASE foo --'
GO



-- So, the response to the problem is to add brackets... BUT, this is actually WORSE.
-- The following procedure solves one problem but STILL has the SQL Injection problem.
ALTER PROCEDURE dbo.CreateDBProc
(
	@DBName	sysname
)
AS
DECLARE @ExecStr	nvarchar(2000)
SELECT @ExecStr = 'CREATE DATABASE [' + @DBName + ']' -- + all of the other stuff to place the files, etc...
SELECT @ExecStr
--EXEC(@ExecStr)
GO

EXEC dbo.CreateDBProc 'fakedbname] DROP DATABASE foo--'
GO



-- This version solves both problems bu protecting the parameter.
ALTER PROCEDURE dbo.CreateDBProc
(
	@DBName	sysname
)
AS
DECLARE @ExecStr	nvarchar(2000)
SELECT @DBName = QUOTENAME(@DBName, ']')
SELECT @ExecStr = 'CREATE DATABASE ' + @DBName -- + all of the other stuff to place the files, etc...
--SELECT @ExecStr
EXEC(@ExecStr)
GO

EXEC dbo.CreateDBProc 'This is the most stupid :) name I can think of with [brackets] and all sorts of junk! in the name'
GO

-- The batch separator "go" is not supported within EXEC...but if a creative
-- user adds dynamic string execution within the string... they can even submit
-- more than one batch and drop more databases, add objects, etc...

-- With the middle version of the proc, try this execution
EXEC dbo.CreateDBProc 'fakedbname] EXEC(''CREATE PROC Test AS SELECT * FROM pubs.dbo.authors'') DROP DATABASE foo --'

-- CREATE DATABASE [fakedbname]
-- CREATE PROC Test
-- DROP DATABASE foo

sp_helpdb
GO