D.Post (11/22/2012)
Ok, so we have to use dynamic queries for object names.Make sure to check the object against the sys views.
DECLARE@DBName NVarchar(50)
SET @DBName = 'Master1'
IF NOT EXISTS (SELECT * FROM sys.Databases WHERE Name = @DBName)
BEGIN
PRINT 'Database : ' + @DBName + ' does not exist'
RETURN
END
I'm assuming here that no injection is possible using PRINT.
People could inject another valid object name though.
Could cause a bit of a hassle when using DELETE or TRUNCATE.
That is not dynamic SQL...
But if you build an SQL string like that and execute it using sp_executesql or EXEC() command, you can inject terrible code into it. Here is your code implemented as Dynamic SQL:
DECLARE
@DBName NVarchar(50)
SET @DBName = 'Master1'
IF NOT EXISTS (SELECT * FROM sys.Databases WHERE Name = @DBName)
BEGIN
-- example of dynamic sql
DECLARE @sql NVARCHAR(1000)
SET @sql = 'PRINT ''Database : ' + @DBName + ' does not exist'''
EXEC (@sql)
END
Now let's try to inject something to it (taking @DBName is input parameter):
CREATE TABLE check_injection (i int)
GO
-- Check for sql injection
select * from check_injection
go
DECLARE
@DBName NVarchar(50)
-- SET @DBName = 'Master1'
-- Someone injected this bit:
SET @DBName = ''';DROP TABLE check_injection; PRINT ''bb:-)'';--'
IF NOT EXISTS (SELECT * FROM sys.Databases WHERE Name = @DBName)
BEGIN
-- example of dynamic sql
select QUOTENAME(@DBName)
DECLARE @sql NVARCHAR(1000)
SET @sql = 'PRINT ''Database : ' + @DBName + ' does not exist'''
EXEC (@sql)
END
select * from check_injection
With enough rights, you may drop the whole database 🙂