Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

simple question about stored procedures 2

Status
Not open for further replies.

OhioSteve

MIS
Mar 12, 2002
1,352
US
I want to create a stored procedure with a varchar input parameter. The parameter will be a view's name. I want the sp to return the results of "Select * from " & [view name]

Some years ago, I recall writing a sp that took a String and executed it as sql statement. I want to use this technique again. I want to initialize a String with the value "Select * from " & [view name]. Then I want the machine to treat the string as a command to execute.

Can you refresh my memory on the syntax??
 
exec Select * from [" & @viewname & "]"

BTW google Sql injection
 
You need to use single quotes instead of double quotes. For example:

Code:
DECLARE @S VARCHAR(255)
DECLARE @TableName VARCHAR(100)
SELECT @TableName = 'INFORMATION_SCHEMA.TABLES'
SELECT @S = 'SELECT * FROM ' + @TableName
EXEC (@S)

But as pwise said, watch out for sql injection. For your particular problem, the dynamic sql approach would probably be my last resort for the solution. Other ideas would be to hard code in IF statements if the number of possible views to select from aren't that great...or you could union the views together in a derived table with an extra virtual column denoting the view name, and select from that derived table. Performance might not be that great if you have lots of views though. Better yet if you are executing this sproc from within an application, let the application handle the view selection.
 
After reading your posts, I was able to write the sp. It is very short:

Code:
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[sp_myproject_test01]
@viewName varchar(50)
AS
exec ('select * from ' + @viewName)

Both of your posts helped me, so you both get stars. Thanks alot!
 
Your concern is that a nefarious user could pass in the names of system tables and learn about my schema. Is that correct?
 
It can get a lot worse than that. You can send multiple statements for SQL Server to execute, and if the account which logs into your connection string has privileges, a user could type in a view name, followed by a semi colon, and then type in a command to drop objects, delete data, etc.

It might not be an issue if you are using a drop down instead of a text box, but sql injection is best handled without having the application's controls provide the security.
 
After reading more about the subject, I agree with your concerns. I will rethink this task. Thanks again for your advice!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top