January 13, 2009 at 7:53 am
Actually I've never really seen this done.
I've started a new job, and they want me to revamp their web app. Make it look more professional, write it correctly using 3 tier and remove all inline sql ect...
Well here's my question. In some of the inline sql they are using variables to create the table name.
The code is in vb.net
name = "joesshop"
Example: "Select * from data_" + name
So the end result would be "Select * from data_joesshop"
Is there anyway to put this in a stored proc?
Thanks all,
KS
January 13, 2009 at 7:55 am
You could always use Dynamic SQL to do these kind of things. But be very very careful when using Dynamic SQL since this is one of the best way to get SQL Injection.
Make sure you validate the parameters that are being passed through.
-Roy
January 13, 2009 at 8:44 am
Roy is right, Dynamic SQL is the standard solution for this kind of problem, but Injection is an overriding concern.
Here is how you can recode your simple example into a stored procedure that protects itself from SQL Injection
Create proc spSelectFromDataTable(
@SuffixName Nvarchar(255))
/*Procedure to demonstrate how to safely incorporate client
text parameters into a SQL command.
Note that the key to this technique is to NEVER actually
EXECute any string that has client-supplied text. Rather all
of the client text must be purified by replacing it with the known
valid names of the objects being referenced. Then Dynamic
SQL commands can be safely constructed using ONLY our own text and
these idealized replacement values.
*/
AS
BEGIN
Declare @ActualTableName SYSNAME
--Find the actual table name that matches the clients
-- text parameter:
Select @ActualTableName = TABLE_NAME
From INFORMATION_SCHEMA.TABLES
Where TABLE_SCHEMA = N'dbo'
And TABLE_NAME = N'data_' + @SuffixName
--Note that it is safe to use the clients parameter
-- here because it is only being used as a data value,
-- it is not being EXECuted.
IF @ActualTableName IS NOT NULL
BEGIN
--Here is where we construct and execute the Dynamic SQL
EXEC(N'Select * From '+@ActualTableName)
--Note that this execution string contains no part of
-- the clients text parameter, it has been completely
-- replaced with the known, valid, actual table name.
END
ELSE
BEGIN
Declare @msg as NVarchar(MAX)
Select @msg = @SuffixName+N' is an invalid data table name.'
RAISERROR(@msg, 11, 1)
Return
END
END
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 13, 2009 at 8:50 am
😀 Awesome guy's thanks.
January 13, 2009 at 9:02 am
Glad we could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 13, 2009 at 11:42 am
Be careful not to give the account that run that stored procedures more privileges than "read" or you still may be surprised! 😉
* Noel
January 13, 2009 at 11:51 am
Grant's solution will prevent that particular injection attack, but I'd watch out for more.
Another point on that specific one, is that someone could fairly easily end up with a full list of your tables, which might help in crafting a more dangerous injection attack, just because that proc will return (by negation) what table names are valid. Probably a low probability of successful attack, but it seems like something to look into.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 13, 2009 at 12:03 pm
Right Gus. This proc only implements security against a possible SQL injection attack at this point. It does not implement any kind of Application Security. If user "Charly" should only be able to access the table "data_Charly", this does not insure that.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply