Dynamic SQL to grant permission but avoid SQL injection

Question

Post reply

Dynamic SQL to grant permission but avoid SQL injection

SQThaal

SSC Rookie

Points: 41
More actions
October 30, 2015 at 1:28 pm

#322219

I was asked to go through stored procedures and make modifications so SQL injections would be impossible.
If it's a select statement, I usually do something like :
SET @cmd = 'select * from testtable WHERE FirstName = @fname AND LastName = @lname'
SET @parameters = '@fname nvarchar(100), @lname nvarchar(100)'
EXEC sp_executesql @cmd, @parameters, @fname = @fname, @lname = @lname
But I stumbled on a stored procedure to dynamicaly grand/remove permissions :
SET @cmd = @protectType + ' ' + @action + ' on ' + @object + ' TO [' + @grantee + ']'
EXEC (@cmd)
It won't let me put @protectType as a parameter, I understand, it's not a 'parameter'
Is there a way to do something like this in a stored procedure and protect myself from SQL injection?
Thank you.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

Probably something like this:

DECLARE @cmd varchar(8000),

@protectType varchar( 6),

@action varchar(8000),

@object varchar(128) = 'sysdbfrag',

@grantee varchar(128);

IF @protectType NOT IN( 'GRANT', 'DENY', 'REVOKE')

RAISERROR('Invalid Protect Type',10,1);

IF EXISTS(SELECT Item FROM dbo.DelimitedSplit8K( @action, ',')

EXCEPT

SELECT permission_name FROM fn_builtin_permissions(DEFAULT))

RAISERROR('Invalid Action',10,1);

IF NOT EXISTS(SELECT name FROM sys.objects WHERE name = PARSENAME( @object, 1))

RAISERROR('Invalid Action',10,1);

--IF NOT EXISTS(SELECT name FROM sys.database_principals WHERE name = @grantee

-- UNION ALL

-- SELECT name FROM sys.server_principals WHERE name = @grantee)

-- RAISERROR('Invalid Grantee',10,1);

SET @cmd = @protectType + ' ' + @action + ' on ' + @object + ' TO ' + QUOTENAME( @grantee)

EXEC (@cmd)

I'm assuming certain things in here, such as this is something to manage permissions. It handles several permissions in a single instruction and for that it used the DelimitedSplit8k found in here: http://www.sqlservercentral.com/articles/Tally+Table/72993/

It might not be exactly what you need, but it should give you an idea.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

SQThaal SSC Rookie Points: 41 More actions · Answer 2

Looks awesome, it's definitely down the path I'm going.

Thank you.