New Posts  All Forums:Forum Nav:

SQL Table variables

post #1 of 8
Thread Starter 
Here's something interesting that has just come up for me.

I have an MSSQL stored procedure and inside I have to create a table variable to EXEC another procedure into. Now, dependant on one of the parameters to the internal call there can be a different result set (number of columns).

I thought I would do this:
Code:
if @parameter in ('a','b','c')
begin
declare @tbl table
(
[Field1] nvarchar(max)
,[Field2] nvarchar(max)
)
end
else
begin
declare @tbl table
(
[Field1] nvarchar(max)
,[Field2] nvarchar(max)
                ,[Field2] nvarchar(max)
)
end
but even though the two paths are mutually exclusive, the sql parser moans saying the table is declared twice!!

SO I thought OK declare the variable then ALTER it if required as so:
Code:
declare @tbl table
(
[Field1] nvarchar(max)
,[Field2] nvarchar(max)
)
if @parameter not in ('a','b','c')
begin
Alter table @tbl ADD
[Field3] nvarchar(max)
end
But, it turns out you can't alter a table variable!!

I know there are other ways to accomplish my goal (and I'll probs go with build up the create or alter script then Exec it) but thought I'd share that cos I found it interesting.
Edited by Sunriselad - 1/14/11 at 9:30am
    
CPUMotherboardGraphicsRAM
I7 920 Gigabyte EX58 XFX Radeon 4870 X2 6GB Corsair 
Hard DriveOptical DriveOSMonitor
500GB Maxtor LG DVDRW Vista Ultimate x64 2 x Edge IO 22" 
KeyboardPowerMouseMouse Pad
Razer Arctosa XFX 800w BTC Cheetah Gaming Mouse None 
  hide details  
Reply
    
CPUMotherboardGraphicsRAM
I7 920 Gigabyte EX58 XFX Radeon 4870 X2 6GB Corsair 
Hard DriveOptical DriveOSMonitor
500GB Maxtor LG DVDRW Vista Ultimate x64 2 x Edge IO 22" 
KeyboardPowerMouseMouse Pad
Razer Arctosa XFX 800w BTC Cheetah Gaming Mouse None 
  hide details  
Reply
post #2 of 8
Remember to stay away from dynamic SQL (Exec scripts) whenever possible. It's hard to debug, less secure, etc., etc.

What is the end result of your procedure? Are you returning a resultset to the client? If that's the case, then just give your two table variables different names and perform the final SELECT within your if..else condition.
Main System
(13 items)
 
  
CPUMotherboardGraphicsRAM
Phenom II X6 1090T MSI 890FXA-GD70 XFX Radeon 5850 16 GB Corsair XMS3 DDR3 1333 
Hard DriveOSMonitorPower
3 X 7200 RPM / 1 TB Win 7 Ultimate 64 2 x ASUS 23" VH232H 1080P SeaSonic X-650 
Case
Corsair 600T 
  hide details  
Reply
Main System
(13 items)
 
  
CPUMotherboardGraphicsRAM
Phenom II X6 1090T MSI 890FXA-GD70 XFX Radeon 5850 16 GB Corsair XMS3 DDR3 1333 
Hard DriveOSMonitorPower
3 X 7200 RPM / 1 TB Win 7 Ultimate 64 2 x ASUS 23" VH232H 1080P SeaSonic X-650 
Case
Corsair 600T 
  hide details  
Reply
post #3 of 8
Thread Starter 
In your opinion how are they less secure tand1?
    
CPUMotherboardGraphicsRAM
I7 920 Gigabyte EX58 XFX Radeon 4870 X2 6GB Corsair 
Hard DriveOptical DriveOSMonitor
500GB Maxtor LG DVDRW Vista Ultimate x64 2 x Edge IO 22" 
KeyboardPowerMouseMouse Pad
Razer Arctosa XFX 800w BTC Cheetah Gaming Mouse None 
  hide details  
Reply
    
CPUMotherboardGraphicsRAM
I7 920 Gigabyte EX58 XFX Radeon 4870 X2 6GB Corsair 
Hard DriveOptical DriveOSMonitor
500GB Maxtor LG DVDRW Vista Ultimate x64 2 x Edge IO 22" 
KeyboardPowerMouseMouse Pad
Razer Arctosa XFX 800w BTC Cheetah Gaming Mouse None 
  hide details  
Reply
post #4 of 8
1. For stored procedures which reference objects within the same database as the stored procedure, permissions granted to execute the stored procedure will suffice. So you are not required to grant permissions to the tables themselves. With dynamic SQL, this concept goes out the window.

2. Dynamic SQL in stored procedures quite often relies on string concatenation in the logic. String concatenation makes SQL injection much easier, which is a bad thing.
Main System
(13 items)
 
  
CPUMotherboardGraphicsRAM
Phenom II X6 1090T MSI 890FXA-GD70 XFX Radeon 5850 16 GB Corsair XMS3 DDR3 1333 
Hard DriveOSMonitorPower
3 X 7200 RPM / 1 TB Win 7 Ultimate 64 2 x ASUS 23" VH232H 1080P SeaSonic X-650 
Case
Corsair 600T 
  hide details  
Reply
Main System
(13 items)
 
  
CPUMotherboardGraphicsRAM
Phenom II X6 1090T MSI 890FXA-GD70 XFX Radeon 5850 16 GB Corsair XMS3 DDR3 1333 
Hard DriveOSMonitorPower
3 X 7200 RPM / 1 TB Win 7 Ultimate 64 2 x ASUS 23" VH232H 1080P SeaSonic X-650 
Case
Corsair 600T 
  hide details  
Reply
post #5 of 8
Thread Starter 
As far as I know, you don't need extra permissions to Exec dynamic sql, permissions are only required on the securables that are referenced within the string.
Since I'm only referencing a self created table variable, I don't think there will be an issue.

As for injection attack, any parameters used are a form of string concatenation so I don't see clearly how that is any different to dynamicSQL. Surely the only way that the injection can get in would be through a proc parameter, and as such would advise to ensure it is taken care of well before getting to the database, probably in a middle tier and not the client which can be subject to alteration.
    
CPUMotherboardGraphicsRAM
I7 920 Gigabyte EX58 XFX Radeon 4870 X2 6GB Corsair 
Hard DriveOptical DriveOSMonitor
500GB Maxtor LG DVDRW Vista Ultimate x64 2 x Edge IO 22" 
KeyboardPowerMouseMouse Pad
Razer Arctosa XFX 800w BTC Cheetah Gaming Mouse None 
  hide details  
Reply
    
CPUMotherboardGraphicsRAM
I7 920 Gigabyte EX58 XFX Radeon 4870 X2 6GB Corsair 
Hard DriveOptical DriveOSMonitor
500GB Maxtor LG DVDRW Vista Ultimate x64 2 x Edge IO 22" 
KeyboardPowerMouseMouse Pad
Razer Arctosa XFX 800w BTC Cheetah Gaming Mouse None 
  hide details  
Reply
post #6 of 8
I must not have explained clearly.

This is a list of things to stay away from in SQL Server. These are industry-accepted best practices and should be used as a last resort.

1. Dynamic SQL
2. Cursors
3. Declaring variables in triggers when used with the assumption that only a single row will be operated on in a trigger. (Don't even use this as a last resort. Always used set-based logic).

Now, the best practice to do is to give the least amount of permissions as possible at all times.

When you have a stored procedure with a statement such as SELECT Column1 FROM Table1 WHERE OwnerColumn = @SomeValue, you don't need to give permissions to Table1 for your user. You only need to give your user permissions to the stored procedure. If you use dynamic SQL for that select statement, you cannot simply grant permissions to the stored procedure and expect it to work. You will most likely end up granting select permissions to Table1. The fact that your stored procedure is not referencing any persistent tables in the database is irrelevant to best practices.

Quote:
As for injection attack, any parameters used are a form of string concatenation so I don't see clearly how that is any different to dynamicSQL.
I'm not sure what you mean by this. Never use string concatenation for parameters.
Command.CommandText = "SELECT * FROM Table1 WHERE OwnerColumn = @Owner" : Command.Parameters.AddWithValue("@Owner", SomeOwnerVariable)

is a lot more secure than

Command.CommandText = "SELECT * FROM Table1 WHERE OwnerColumn = '" & SomeOwnerVariable & "'"


However, if you use dynamic SQL, even if you use named parameters, you will be moving your string concatenation into the stored procedure itself. You might end up with something like SELECT @SomeVarcharVariable = 'SELECT * FROM Table1 WHERE OwnerColumn = ''' + @SomeOtherVarchar + ''''. That is insecure.

My initial point was not to tell you that your one specific procedure was insecure, but to explain how those particular methodologies are contrary to best practices, overall.
Main System
(13 items)
 
  
CPUMotherboardGraphicsRAM
Phenom II X6 1090T MSI 890FXA-GD70 XFX Radeon 5850 16 GB Corsair XMS3 DDR3 1333 
Hard DriveOSMonitorPower
3 X 7200 RPM / 1 TB Win 7 Ultimate 64 2 x ASUS 23" VH232H 1080P SeaSonic X-650 
Case
Corsair 600T 
  hide details  
Reply
Main System
(13 items)
 
  
CPUMotherboardGraphicsRAM
Phenom II X6 1090T MSI 890FXA-GD70 XFX Radeon 5850 16 GB Corsair XMS3 DDR3 1333 
Hard DriveOSMonitorPower
3 X 7200 RPM / 1 TB Win 7 Ultimate 64 2 x ASUS 23" VH232H 1080P SeaSonic X-650 
Case
Corsair 600T 
  hide details  
Reply
post #7 of 8
Thread Starter 
Funny thing is, if I actually did create the variable in script, I'm sure the parser still wouldn't recognise it and allow the procedure creation so I 'm not doing it that way.

But for this discussion:
Are you saying I would need to give extra permissions for my user to access a temporary table (in this case variable) if it is declared in dynamicSQL?

As for the injection I fail to see how:
"SELECT * FROM Table1 WHERE OwnerColumn = @Owner"
is any different, where @owner contains an injection string.

I'm not sure why you use Command.Text as an example, in my case the Command.Text would be the sp name and there would be parameters, as you say are safer!

As for "industry best practices", I have worked in the industry for many years and have yet to come across any systems in the real world which adhere to them. In fact I believe the best practices are there to stop inexperienced errors and can be used correctly in the right situation. For Example your Command.Text example is valid and I agree should be avoided. But building up dynamicSQL that doesn't require elevated permissions or even use variables from parameters is often used within sps.
Sometimes development time can be greatly increased by adhering to "textbook" rules (which there are argued both ways btw) and that costs in reality.

Lastly, (I know its a long one) how would you select from a table that you only know the name of dynamically at runtime in the sp?
    
CPUMotherboardGraphicsRAM
I7 920 Gigabyte EX58 XFX Radeon 4870 X2 6GB Corsair 
Hard DriveOptical DriveOSMonitor
500GB Maxtor LG DVDRW Vista Ultimate x64 2 x Edge IO 22" 
KeyboardPowerMouseMouse Pad
Razer Arctosa XFX 800w BTC Cheetah Gaming Mouse None 
  hide details  
Reply
    
CPUMotherboardGraphicsRAM
I7 920 Gigabyte EX58 XFX Radeon 4870 X2 6GB Corsair 
Hard DriveOptical DriveOSMonitor
500GB Maxtor LG DVDRW Vista Ultimate x64 2 x Edge IO 22" 
KeyboardPowerMouseMouse Pad
Razer Arctosa XFX 800w BTC Cheetah Gaming Mouse None 
  hide details  
Reply
post #8 of 8
Quote:
Are you saying I would need to give extra permissions for my user to access a temporary table (in this case variable) if it is declared in dynamicSQL?
No, I'm not saying that. I'm saying in general, they need permissions to access a database object they do not own if using dynamic sql to reference the object.

Quote:
As for the injection I fail to see how:
"SELECT * FROM Table1 WHERE OwnerColumn = @Owner"
is any different, where @owner contains an injection string.
That is not injectable. If @Owner was set to 1;DROP TABLE [Users];, the query would search for something where the value in the column is 1;DROP TABLE [Users];.

However, SELECT @SqlString = 'SELECT * FROM Table1 WHERE OwnerColumn = ''' + @Owner + ''''; EXEC @SqlString is injectable.

Quote:
I'm not sure why you use Command.Text as an example, in my case the Command.Text would be the sp name and there would be parameters, as you say are safer!
Command.Text is used in .Net. It was my example illustrating actually calling the stored procedure.

Quote:
But building up dynamicSQL that doesn't require elevated permissions or even use variables from parameters is often used within sps.
Sometimes development time can be greatly increased by adhering to "textbook" rules (which there are argued both ways btw) and that costs in reality.
Depends on who you ask. Certain best practices are pretty much just second nature, and I see them as saving me both development and maintenance/debugging time. However, I've used SQL Server almost every day for the past nine years, and I realize that many developers have to concentrate on the application first, where the database is just part of that. I'm giving you this advice as someone who has concentrated on SQL Server. I use best practices everyday--both for my own systems and in working with application developers to provide them the database tier for their applications. It's worked out well.

Quote:
Lastly, (I know its a long one) how would you select from a table that you only know the name of dynamically at runtime in the sp?
I wouldn't. I'd have the data tier in the app call a stored procedure to reference the correct table. Meaning, if there were ten tables possible to select from--I'd have ten select stored procedures.
Main System
(13 items)
 
  
CPUMotherboardGraphicsRAM
Phenom II X6 1090T MSI 890FXA-GD70 XFX Radeon 5850 16 GB Corsair XMS3 DDR3 1333 
Hard DriveOSMonitorPower
3 X 7200 RPM / 1 TB Win 7 Ultimate 64 2 x ASUS 23" VH232H 1080P SeaSonic X-650 
Case
Corsair 600T 
  hide details  
Reply
Main System
(13 items)
 
  
CPUMotherboardGraphicsRAM
Phenom II X6 1090T MSI 890FXA-GD70 XFX Radeon 5850 16 GB Corsair XMS3 DDR3 1333 
Hard DriveOSMonitorPower
3 X 7200 RPM / 1 TB Win 7 Ultimate 64 2 x ASUS 23" VH232H 1080P SeaSonic X-650 
Case
Corsair 600T 
  hide details  
Reply
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: Coding and Programming