Running dynamic stored procedure in ASP

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Running dynamic stored procedure in ASP

Author

Topic

Huligan
Yak Posting Veteran

66 Posts

Posted - 2004-09-24 : 09:25:24

Hello. I have a dynamic stored procedure that is working in Query Analyzer, but I keep running into road blocks when I try to call it in ASP. The dynamic stored procedure just returns a set of records. Below is a trimmed down version of my VBScript. I have other stored procedures running with this code, but when I call a dynamic stored procedure I get errors. Here's my code.

<%
1 vStartDate = "1/1/2000"
2 vEndDate = "1/1/2005"
3 vWhereStatement = ""
4
5 'Build connection
6 Set THISONE = server.CreateObject ("ADODB.Connection")
7 THISONE.Open Application("THISONE_ConnectionString")
8
9 Set objCmd = Server.CreateObject("ADODB.Command")
10 objCmd.ActiveConnection = THISONE
11 objCmd.CommandType = adCmdStoredProc
12 objCmd.CommandText = "sp_DynamicStoredProc"
13
14 'Establish parameters
15 objCmd.Parameters.Append = objCmd.CreateParameter("@StartDate",adDate,adParamInput, ,vStartDate)
16 objCmd.Parameters.Append = objCmd.CreateParameter("@EndDate",adDate,adParamInput, ,vEndDate)
17 objCmd.Parameters.Append = objCmd.CreateParameter("@WhereStatement",adVarChar,adParamInput,6000,vWhereStatement)
18
19 'Build recordset
20 Set RS1 = Server.CreateObject("ADODB.Recordset")
21 RS1.CursorLocation = adUseClient
22 RS1.Open objCmd,,adOpenForwardOnly,adLockReadOnly
23 Set RS1.ActiveConnection = Nothing
24 Set objCmd = Nothing
25
26 IF RS1.EOF THEN
27 ELSE
28 vData = RS1.Fields("Data").Value
29 END IF
30
31 'Close recordset
32 RS1.Close()
33 Set RS1 = Nothing
34
35 'Close connection
36 THISONE.Close()
37 Set THISONE = Nothing
%>

When I run the code displayed above, I get this error.

Cannot change the ActiveConnection property of a Recordset object which has a Command object as its source. (Line 23)

I comment out Line 23 and I get this error.

Operation is not allowed when the object is closed. (Line 26)

So now it looks like the recordset is closed because this error appears at the first reference of the recordset. I can't seem to figure out why my recordset is closing. I would appreciate any suggestions. Thanks for you time.

Huligan

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-24 : 09:30:07

This is roughly what i use...


Set SqlSvr = openDB() -- get db connection
sqlstr = "dbo.mySP " & sqlText(param1,"null") & ", " & sqlInt(param2,"null")
Set Rs = SqlSvr.execute(sqlstr)
While not (Rs.Bof or Rs.Eof)
	'blah blah
	rs.movenext
Wend
Set Rs = nothing
Set SqlSvr = nothing

Whats all that other stuff you got in there??

Corey

Huligan
Yak Posting Veteran

66 Posts

Posted - 2004-09-24 : 11:06:09

Hello. I don't know, but I think we are building are stored procedures differently. I need for my ASP page to play well with the dynamic stored procedure already created. Here is a minimal version of my dynamic stored procedure. Thanks again.

----------------

CREATE PROCEDURE [sp_DynamicStoredProc]
(@StartDate datetime,
@EndDate datetime,
@WhereStatement varchar(6000))
AS
-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(8000)

-- Enter the dynamic SQL statement into the variable @SQLStatement
SELECT @SQLStatement = "SELECT ... FROM ... WHERE (Table.Date >= '" + Convert(varchar(20), @StartDate, 101) + "') AND (Table.Date <= '" + Convert(varchar(20), @EndDate, 101) + "')" + @WhereStatement + " GROUP BY ... ORDER BY ... "

-- Execute the SQL statement
EXEC(@SQLStatement)

GO

Huligan
Yak Posting Veteran

66 Posts

Posted - 2004-09-24 : 13:31:26

Hey Corey. Forgive my ignorance, but what does this line mean?

sqlstr = "dbo.mySP " & sqlText(param1,"null") & ", " & sqlInt(param2,"null")

Thanks.

Huligan
Yak Posting Veteran

66 Posts

Posted - 2004-09-24 : 13:38:27

Hello Corey. What's the difference between inserting a SQL string in 'sqlstr' with and without using a stored procedure. When you include the storedproc, it basically acts as a shell. What's the benefit of using the storedproc if I feed it the entire SQL string? Thanks for the help.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-24 : 14:34:55

i assume you have figured out what that line means. I am generating the SQL i wish to execute, which in this case was a SP and some parameters.

Notice the functions I called sqlText and sqlInt. Those are homegrown functions I use to make sure the parameter contains the right type of data before I pass it. This is related to the benefits of using a SP instead of the actual code. The SP is a shell, that protects the sql server by only accepting certain datatypes.

Read up on SQL injection for more detail as to why to use SPs.

Quick example...
assume you want to check someone's username and password in your userlist table...
In the username field I supply: ' or 1=1 --

So when you build your string:

"Select * From userlist where username = '" & username & "' and password = '" & password & "'"

then put in the supplied info..

"Select * From userlist where username = '' or 1=1 --' and password = 'anything'"

Result... all of the userlist records...

Corey

Kristen
Test

22859 Posts

Posted - 2004-09-25 : 05:44:39

quote:

Originally posted by Seventhnight


sqlstr = "dbo.mySP " & sqlText(param1,"null") & ", " & sqlInt(param2,"null")
Set Rs = SqlSvr.execute(sqlstr)

Possible risk of SQL Injection? (I assume sqlText is taking care of doubling single-quotes etc., so perhaps not - what about if param1 is a mega long string, could that break it and allow param2 to be "injected"?

Using objCmd.Parameters.Append is about 5% faster in our tests here (but more of a pain to impliment!)

By the by, we do have string commands like yours, and we have a variant of sqlText that prepends a comma - sort of sqlText1("FirstParameter", "NULL") and sqlText2("FirstParameter", "NULL") - where sqlText2 prepends the comma. All of our SProcs take the Session Number as their first parameter, so in effect all application parameters are "second" parameters in our code. The Session Number is used by the SProc if it needs to log an error, or somesuch.

Kristen
Kristen

slacker
Posting Yak Master

115 Posts

Posted - 2004-09-25 : 06:11:22

sp_executesql. protect your code even further and speed it up.

Kristen
Test

22859 Posts

Posted - 2004-09-25 : 06:50:38

Performance should improve, not sure that sp_executesql provides much protection though, does it?

Kristen

slacker
Posting Yak Master

115 Posts

Posted - 2004-09-25 : 08:36:20

quote:
Originally posted by Kristen

Performance should improve, not sure that sp_executesql provides much protection though, does it?

Kristen

well it allows you to pass in parameters to the string. Consider the following.


create procedure ViewUser
(

@userid nvarchar(100)   
)
as
declare @sqlString nvarchar(1000)

set @sqlString = 
'select userid, ..  from users where userid=' + @userid

a poorly written php script or asp script could allow a hacker
to pass something like 5 OR 1=1
all users on the system are returned. Now a good programmer isnt going to allow this to happen and probably would have sent @userid in as an int. This is an extreme case. But the code would be safe if you used sp_executesql and you get a boost in performance.


create procedure ViewUser
(
@userid nvarchar(100)
)
as
declare @sqlString nvarchar(1000)

set @sqlString = 
'select userid, ..  from users where userid=@userid' 
-- using parameter not string concatination.. #1 cause of sql injection attacks is concatinating strings...   even the best programmers are victims.

exec sp_executesql 
@sqlString,
'@userid nvarchar(100)',  -- declare parameter list as you would stored proc
@userid=@userid -- pass your parameters.

You would still need to concatinate some stuff... but not the main ones like userid, search criteria, things like this are normally placed onto a querystring or spoofed into the program. And if the front end coder doesnt put proper places in check your left insecure.

Kristen
Test

22859 Posts

Posted - 2004-09-25 : 10:00:19

Ah OK, except I wouldn't do that - I'd write an SProc for that.

Either way, same as with dynamic SQL from VB or whatever, you've got to check for embedded single quotes and so on. Easiest way is just to wrap the parameter in single quotes, and double any embedded single quotes:

set @sqlString =
'select userid, .. from users where userid=' + '''' + REPLACE(CONVERT(varchar(100), @userid), '''', '''''') + ''''

SQL doesn't care that an INT/DATETIME/etc parameter is provided as a VARCHAR string, of course

For me the more likely usage of sp_executeSQL is:

DECLARE @strSQL varchar(8000)
SELECT @strSQL = 'SELECT * FROM MyTable WHERE MyColumn = @PARAM1 ORDER BY ' + @strUserChoosenColumnName
EXEC sp_executeSQL @strSQL, '@PARAM1 varchar(100)', @PARAM1

... and then all the same permissions issues arise for the dynamic ORDER BY string handling :-(

Kristen

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-25 : 10:05:19

quote:
you've got to check for embedded single quotes and so on. Easiest way is just to wrap the parameter in single quotes, and double any embedded single quotes

Thats what my sqlText() does...

Corey

slacker
Posting Yak Master

115 Posts

Posted - 2004-09-25 : 10:15:53

quote:
Originally posted by Kristen

... and then all the same permissions issues arise for the dynamic ORDER BY string handling :-(

Kristen

I hate replacing single quotes. It bothers me for some reason. Even if I know that it'll probably work.. it seems to me like too much is left open to the user.. I remember awhile back when i was still hard coding sql statements in my code ( uggh. ) that I ran across a situation where simply replacing the single quotes didnt work.. certain user input could get around it. But since then ive learned how to use parameters in asp. which takes care of that.

Kristen
Test

22859 Posts

Posted - 2004-09-25 : 11:28:22

"Thats what my sqlText() does..."

Get away with you ... those are VB functions ... we're within a SQL Sproc now ... keep up at the back! [:-D]

"certain user input could get around it"

I can't think of anything - unless its being shipped via a URL where spaces and hyphens etc. need URLEncoding too ...

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources