Creating a sql statement within a Stored Procedure 2

[mikelawrence]

I've just moved from sql server 2000 to 2005 and the following procedure no longer works. The only way i can get the sort to be passed as a parameter is to create a sql string as sorting by a parameter, e.g. sort by @sort does not work. The stored procedure works if i exec it from Management Studio but not from a web asp page which makes me think it is to do with privileges but i have granted access to public and do not know what else to do. Any ideas would be appreciated. Thanks mike

ALTER PROCEDURE [dbo].[RR_Report_Card_Screen]
@sort AS VARCHAR(50)

AS

IF @sort is null OR @sort = '' SET @sort='balance desc'

DECLARE @sqlStr AS VARCHAR(8000)

SET @sqlStr="SELECT " ;
SET @sqlStr=@sqlStr +"a.lastname+', '+a.firstname " ;
SET @sqlStr=@sqlStr +"FROM mem_Members m" ;
SET @sqlStr=@sqlStr +"ORDER BY "+@sort ;

EXEC (@sqlStr)

 

[genomon]

Any chance 'balance desc' should be 'balance_desc'

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."

 

[mikelawrence]

no by default it is sorting by descending balance so balance desc is right, thanks for trying though! mike

 

[genomon]

Not being ASP-literate, my next guess would be that the ASP is not passing the SP the value you think it is. If it were VB/VBA, I would put a breakpoint or msgbox in the code somewhere to see what is really in the @sort var by the time it gets to the sp call..

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."

 

[danvlas]

You do realize, I hope, that you have just opened all your doors to anyone willing to hack your database...

As an example: use a test database[\b], pass the following parameter for @sort (45 characters used):

"1 drop table SomeTable delete from OtherTable"

and execute the procedure.

Then think again and give up the idea of using dynamic SQL (not to mention the PUBLIC rights).

You can create a view, give the appropriate SELECT permissions, then get the desired recordset directly from your asp page through
Set rst=Connection.Execute("Select * from ViewName Order By WhateverField Desc")

It's not a stored proc, but it solves your problem without security risks.


HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[SQLSister]

Read

http://www.unixwiz.net/techtips/sql-injection.html

http://www.sommarskog.se/dynamic_sql.html

I agree, you should not use public rights, you should use roles. Further you must assign rights at the table level when you use dynamic SQL which increases the security risk as users will have rights they should not ever be allowed to have.
BTW you aren't conecting to the database as the sa user from your user interface are you?


"NOTHING is more important in a database than integrity." ESquared

 

[mikelawrence]

Thanks for the security advice above, i've changed the way i do it and will read up more about the ideas above.

i appreciate your time

mike

 

[Leighton21]

Not sure but it kind of sound like what Armando Prato has done here

http://www.mssqltips.com/tip.asp?tip=1455

Example 3