SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


using araylist in stored proc


using araylist in stored proc

Author
Message
natigsqlserver
natigsqlserver
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 59
hello i have this stored proc for dynamically insert update delete select and select with id

but hier have a 2 column name and 2 values for this crud operaion
may be more columns and more values for crud opertaion
can i use arraylist
for example i will add 20 column for inserting proc will give me 20 values will insert 20
all in dynamically
can that structure?
thanks for discussion


GO
ALTER Procedure [dbo].[crud_operation]
@TableName VarChar(100),
@id int=null,
@action int,@COL1 sysname=null,@COL2 sysname=null,@cval1 nvarchar(max)=null,@cval2 nvarchar(max)=null
AS

Declare @SQL VarChar(1000)
----simple select--

SELECT @SQL = case
when (@action = 0 and @id is null) then 'SELECT * FROM ' + @TableName
when (@action=0 and @id is not null) then 'SELECT * FROM ' + @TableName +' '+ N'where id=' + rtrim(cast (@id as int))
----insert----
when (@action=1 and @COL1 is not null and @COL2 is null) then 'insert into ' + @TableName + ' ' +'('+@COL1+')' + ' ' + 'select '+ ''''+@cval1+''''
when (@action=1 and @COL1 is not null and @COL2 is not null) then
'insert into ' + @TableName + '('+@COL1+','+@COL2 + ')' + ' ' + 'select '+ ''''+@cval1+'''' +','+''''+@cval2+''''
----delete----
when(@action=2 and @id is not null) then
'delete from ' + @TableName + ' ' + N'where id='+ rtrim(cast(@id as int))
----update ---
when (@action=3 and @id is not null and (@COL1 is not null and @COL2 is null))
then 'update ' + @TableName + ' set ' + @COL1 + '=' + @cval1 +' where id=' + rtrim(cast(@id as int))

else '' end
print @sql
Exec ( @SQL)



Thom A
Thom A
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45650 Visits: 15487
Personally, I see a lot more problems with this than simply the ability to pass an unlimited amount t if columns. SQL injection is a concern with this type of set up.

What is your reason for choosing to have a one size fits all SP? you would be much better splitting this out to start with.


Thom~
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].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
natigsqlserver
natigsqlserver
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 59
Thom A - Saturday, December 30, 2017 7:47 AM
Personally, I see a lot more problems with this than simply the ability to pass an unlimited amount t if columns. SQL injection is a concern with this type of set up.

What is your reason for choosing to have a one size fits all SP? you would be much better splitting this out to start with.

yes this structure is not normally
i want to pass table name columnname dyanically for insert and update

Thom A
Thom A
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45650 Visits: 15487
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];
USE master;
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.


Thom~
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].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
Thom A
Thom A
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45650 Visits: 15487
natigsqlserver - Saturday, December 30, 2017 8:16 AM
Thom A - Saturday, December 30, 2017 7:47 AM
Personally, I see a lot more problems with this than simply the ability to pass an unlimited amount t if columns. SQL injection is a concern with this type of set up.

What is your reason for choosing to have a one size fits all SP? you would be much better splitting this out to start with.

yes this structure is not normally
i want to pass table name columnname dyanically for insert and update

And, as I said in my original post, why a one size fits all? Apart from fixing the injection issues, I'd at least ensure that you have separate SP's to INSERT, UPDATE, SELECT, Etc... Yes, the table can be dynamic, but highly recommend against it being able to do anything.



Thom~
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].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
Thom A
Thom A
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45650 Visits: 15487
I'll start off with an introduction anyway. This version below will avoid injection. I've commented the lines, which should help you understand what it's doing. if however, you don't, please do ask. You'll note this only does a SELECT, nothing else. As I said above, I suggest splitting out the operations:
CREATE PROC dbo.crud_select_operation @TableName varchar(100), @id INT = NULL
AS

DECLARE @SQL nvarchar(4000); --This will hold our Dynamic SQL
DECLARE @parm nvarchar(100); --This hold the definition of our parameter for our D-SQL, if we need it.
--Start off with the basic of the SELECT statement
SELECT @SQL = N'
SELECT *
FROM ' + QUOTENAME([name]) --Quote the table name, incase it has special characters
FROM sys.tables --We use sys.tables, to ensure the table exists. If it doesn't exist, then NULL is returned (and no SQL is run)
WHERE [name] = @TableName;

--Let's do the part where there is an ID
IF @ID IS NOT NULL BEGIN
SET @SQL = @SQL + N'
WHERE id = @dID;'; --This adds a WHERE clause
SET @parm = N'@dID int'; --And that's the definition of our parameter in our Dynamic SQL
PRINT @SQL;
EXEC sp_executesql @SQL, @parm, @dID = @ID; --And the bit that runs it all. We're much safer using sp_executesql
END ELSE BEGIN
--This is is there is no ID
SET @SQL = @SQL + N';'; --Future proofing
PRINT @SQL;
EXEC sp_executesql @SQL;
END
GO



Thom~
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].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
natigsqlserver
natigsqlserver
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 59
Thom A - Saturday, December 30, 2017 8:51 AM
I'll start off with an introduction anyway. This version below will avoid injection. I've commented the lines, which should help you understand what it's doing. if however, you don't, please do ask. You'll note this only does a SELECT, nothing else. As I said above, I suggest splitting out the operations:
CREATE PROC dbo.crud_select_operation @TableName varchar(100), @id INT = NULL
AS

DECLARE @SQL nvarchar(4000); --This will hold our Dynamic SQL
DECLARE @parm nvarchar(100); --This hold the definition of our parameter for our D-SQL, if we need it.
--Start off with the basic of the SELECT statement
SELECT @SQL = N'
SELECT *
FROM ' + QUOTENAME([name]) --Quote the table name, incase it has special characters
FROM sys.tables --We use sys.tables, to ensure the table exists. If it doesn't exist, then NULL is returned (and no SQL is run)
WHERE [name] = @TableName;

--Let's do the part where there is an ID
IF @ID IS NOT NULL BEGIN
SET @SQL = @SQL + N'
WHERE id = @dID;'; --This adds a WHERE clause
SET @parm = N'@dID int'; --And that's the definition of our parameter in our Dynamic SQL
PRINT @SQL;
EXEC sp_executesql @SQL, @parm, @dID = @ID; --And the bit that runs it all. We're much safer using sp_executesql
END ELSE BEGIN
--This is is there is no ID
SET @SQL = @SQL + N';'; --Future proofing
PRINT @SQL;
EXEC sp_executesql @SQL;
END
GO

thank you very much
but i need insert and update
operation

Thom A
Thom A
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45650 Visits: 15487
natigsqlserver - Saturday, December 30, 2017 8:57 AM
Thom A - Saturday, December 30, 2017 8:51 AM
I'll start off with an introduction anyway. This version below will avoid injection. I've commented the lines, which should help you understand what it's doing. if however, you don't, please do ask. You'll note this only does a SELECT, nothing else. As I said above, I suggest splitting out the operations:
CREATE PROC dbo.crud_select_operation @TableName varchar(100), @id INT = NULL
AS

DECLARE @SQL nvarchar(4000); --This will hold our Dynamic SQL
DECLARE @parm nvarchar(100); --This hold the definition of our parameter for our D-SQL, if we need it.
--Start off with the basic of the SELECT statement
SELECT @SQL = N'
SELECT *
FROM ' + QUOTENAME([name]) --Quote the table name, incase it has special characters
FROM sys.tables --We use sys.tables, to ensure the table exists. If it doesn't exist, then NULL is returned (and no SQL is run)
WHERE [name] = @TableName;

--Let's do the part where there is an ID
IF @ID IS NOT NULL BEGIN
SET @SQL = @SQL + N'
WHERE id = @dID;'; --This adds a WHERE clause
SET @parm = N'@dID int'; --And that's the definition of our parameter in our Dynamic SQL
PRINT @SQL;
EXEC sp_executesql @SQL, @parm, @dID = @ID; --And the bit that runs it all. We're much safer using sp_executesql
END ELSE BEGIN
--This is is there is no ID
SET @SQL = @SQL + N';'; --Future proofing
PRINT @SQL;
EXEC sp_executesql @SQL;
END
GO

thank you very much
but i need insert and update
operation


Yes, I know you do; as i said before, are you happy to have these separately? Do you understand what the above is doing, and why it's safer?

The most important thing you need to learn from this post is the danger of SQL injection. I may well solve the problem you intially came here for later, however, what you need to learn is how bad the current SP you have is. You need to be able to come away from this topic knowing to never create a stored procedure like that, and to know how you should do it instead in the future as well.


Thom~
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].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
natigsqlserver
natigsqlserver
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 59
Thom A - Saturday, December 30, 2017 9:02 AM
natigsqlserver - Saturday, December 30, 2017 8:57 AM
Thom A - Saturday, December 30, 2017 8:51 AM
I'll start off with an introduction anyway. This version below will avoid injection. I've commented the lines, which should help you understand what it's doing. if however, you don't, please do ask. You'll note this only does a SELECT, nothing else. As I said above, I suggest splitting out the operations:
CREATE PROC dbo.crud_select_operation @TableName varchar(100), @id INT = NULL
AS

DECLARE @SQL nvarchar(4000); --This will hold our Dynamic SQL
DECLARE @parm nvarchar(100); --This hold the definition of our parameter for our D-SQL, if we need it.
--Start off with the basic of the SELECT statement
SELECT @SQL = N'
SELECT *
FROM ' + QUOTENAME([name]) --Quote the table name, incase it has special characters
FROM sys.tables --We use sys.tables, to ensure the table exists. If it doesn't exist, then NULL is returned (and no SQL is run)
WHERE [name] = @TableName;

--Let's do the part where there is an ID
IF @ID IS NOT NULL BEGIN
SET @SQL = @SQL + N'
WHERE id = @dID;'; --This adds a WHERE clause
SET @parm = N'@dID int'; --And that's the definition of our parameter in our Dynamic SQL
PRINT @SQL;
EXEC sp_executesql @SQL, @parm, @dID = @ID; --And the bit that runs it all. We're much safer using sp_executesql
END ELSE BEGIN
--This is is there is no ID
SET @SQL = @SQL + N';'; --Future proofing
PRINT @SQL;
EXEC sp_executesql @SQL;
END
GO

thank you very much
but i need insert and update
operation


Yes, I know you do; as i said before, are you happy to have these separately? Do you understand what the above is doing, and why it's safer?

yes i was understand
but ned pass column name and values for insert and update process dynamically?

Thom A
Thom A
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45650 Visits: 15487
natigsqlserver - Saturday, December 30, 2017 9:06 AM

yes i was understand
but ned pass column name and values for insert and update process dynamically?

Ok, provided you understand, that's fine. I'll be providing several SP's, which is going to take awhile to write up. I'll post again later.



Thom~
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].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search