Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kbdrand
Starting Member
14 Posts |
 Posted - 2004-04-13 : 16:51:52
|
| Warning: This is a newbie questionI'm trying to develop a stored procedure that will be used as a sort of security cop for the database. The users of the stored procedure will pass a username, password and a table name to the procedure then the sp should dynamically return the appropriate data. The problem I'm having is that it doesn't seem like you can dynamically construct a SELECT statement with a tablename that is passed as a parameter.What I want is 'SELECT * FROM @tablename' with @tablename being a parameter of the sp. Is there a way to do this? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-13 : 16:54:01
|
| That is called dynamic sql. Dynamic sql will not perform well and you will need to grant explicit permissions on the table rather than just EXEC on the stored procedure. So as long as you know the problems with dynamic sql, here you go:http://www.sqlteam.com/item.asp?ItemID=4599Tara |
 |
|
|
Frank Kalis
Constraint Violating Yak Guru
413 Posts |
|
|
kroky
Starting Member
14 Posts |
Posted - 2004-04-14 : 04:04:43
|
| you just have to be carefull what are you executeing and allways have in ming that someone can have password like: 'shutdown -- |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-04-14 : 09:05:48
|
| [code]USE NorthwindGODECLARE @sql varchar(8000), @tablename sysnameSELECT @tablename = 'Order Details'SELECT @sql = 'SELECT * FROM ['+@tablename+']'EXEC(@sql)GO[/code]And why'll you're at it...look up sql injection....search the threads here, and google it..Perhaps if you explain why you want to do this....Brett8-) |
|
|
|
kroky
Starting Member
14 Posts |
Posted - 2004-04-14 : 10:58:25
|
| And why'll you're at it...look up sql injectionthis sentence i cant understand.but if you are asking whiy am i talking about SQL injection simply because the guy wants to execute string that is enterd from the user, and thats kinda dangerous. |
|
|
|
drymchaser
Aged Yak Warrior
552 Posts |
Posted - 2004-04-14 : 11:17:19
|
quote:
Originally posted by kroky
but if you are asking whiy am i talking about SQL injection
He isn't asking it of you.quote:
simply because the guy wants to execute string that is enterd from the user, and thats kinda dangerous.
That's Brett's point. He's advising the poster to consider SQL injection as a hazard to the approach he is taking.You'll need to read many of Brett's posts to realize there are a lot of gems among the stream of "seemingly random"  statements. |
|
|
|
|
|
|
Dynamic SELECT statement in Stored Procedure