Constructing secure queries in classic ASP

I am familiar with the SqlCommand object available in .NET (VBScript or C#)
which provides a powerful security feature by using a template in conjunction
with SqlParameters. I have been searching without success to determine
whether classic ASP with VBScript has something equivalent. Does it? Failing
that, are there any libraries available that would perform the same
functionality? I simply balk at attempting to use raw dynamic SQL commands
(e.g. "SELECT xyz from t1 where u=" & userName & . . .")

The challenge is that I am inheriting a rather old system, I believe:
VBScript engine is version 5.6
SERVER_SOFTWARE value is "Microsoft-IIS/4.0" (which one reference indicated
means "IIS4.0 with ASP 2.0")

Re: Constructing secure queries in classic ASP

michael sorens wrote:
> I am familiar with the SqlCommand object available in .NET (VBScript
> or C#) which provides a powerful security feature by using a template
> in conjunction with SqlParameters. I have been searching without
> success to determine whether classic ASP with VBScript has something
> equivalent. Does it? Failing that, are there any libraries available
> that would perform the same functionality? I simply balk at
> attempting to use raw dynamic SQL commands (e.g. "SELECT xyz from t1
> where u=" & userName & . . .")
>
With good reason.
Here you go:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e

If you want type safety, which is not really needed in vbscript, you
will need to code the parameter objects by hand.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Constructing secure queries in classic ASP

Thanks for the useful information. I neglected to mention that I am using
SqlServer with this application. Do these techniques work with SqlServer as
well?

Re: Constructing secure queries in classic ASP

michael sorens wrote:
> Thanks for the useful information. I neglected to mention that I am
> using SqlServer with this application. Do these techniques work with
> SqlServer as well?

Absolutely - the OLE DB provider for the database supplies the necessary
datatypes to make it work..

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Constructing secure queries in classic ASP

It took some digging through your posts and from there to some other material
before I was able to cobble a working example together.

I went from this:
============================================================ ==============
sqlS1 = "SELECT SiteName FROM Sites S WHERE IDSite=" & S1
set rsS1 = server.CreateObject("ADODB.Recordset")
rsS1.Open sqlS1, WebconnectString, adOpenStatic, ,adCmdText
Site = rsS1("SiteName")
============================================================ ==============

To this (looks good with a fixed width font:-):
============================================================ ==============
sqlS1 = "SELECT SiteName FROM Sites S WHERE IDSite=?"
arParams = array(S1)
set cmd = createobject("ADODB.Command")
cmd.CommandText = sqlS1
set cmd.ActiveConnection = myConn
set rsS1 = cmd.Execute(,arParams)
Site = rsS1("SiteName")
============================================================ ==============

....resulting in the same Site name in both cases.

Is my revised code above the proper way to do it? You actually did not have
any examples in the reference posts you provided that showed queries
returning data; they were all for INSERT statements.

Re: Constructing secure queries in classic ASP

michael sorens wrote:
> It took some digging through your posts and from there to some other
> material before I was able to cobble a working example together.
>
> I went from this:
>
============================================================ ============
==
> sqlS1 = "SELECT SiteName FROM Sites S WHERE IDSite=" & S1
> set rsS1 = server.CreateObject("ADODB.Recordset")
> rsS1.Open sqlS1, WebconnectString, adOpenStatic, ,adCmdText
> Site = rsS1("SiteName")
>
============================================================ ============
==
>
> To this (looks good with a fixed width font:-):
>
============================================================ ============
==
> sqlS1 = "SELECT SiteName FROM Sites S WHERE
> IDSite=?" arParams = array(S1)
> set cmd = createobject("ADODB.Command")
> cmd.CommandText = sqlS1
> set cmd.ActiveConnection = myConn
> set rsS1 = cmd.Execute(,arParams)
> Site = rsS1("SiteName")
>
============================================================ ============
==
>
> ...resulting in the same Site name in both cases.
>
> Is my revised code above the proper way to do it? You actually did
> not have any examples in the reference posts you provided that showed
> queries returning data; they were all for INSERT statements.

Yes, that's it. I would add the CommandType statement:

cmd.CommandType = 1 'adCmdText

99 times out of a hundred, this will suffice for asp. For the rare
occasions when you need a different cursor type from the default
firehose cursor (perhaps a clientside static cursor), you need to build
the Parameters collection and use the recordset's Open method to open
it:

sqlS1 = "SELECT SiteName FROM Sites S WHERE IDSite=?"
set cmd = createobject("ADODB.Command")
cmd.CommandText = sqlS1
cmd.CommandType = 1 'adCmdText
Const adInteger = 3
Const adParamInput = &H0001
Const adUseClient = 3
cmd.Parameters.Append cmd.CreateParameter("SiteID", _
adInteger, adParamInput,,S1)
Set rs=CreateObject("adodb.recordset")
rs.CursorLocation=adUseClient
rs.Open cmd




--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Constructing secure queries in classic ASP

michael sorens wrote:
> It took some digging through your posts and from there to some other
> material before I was able to cobble a working example together.
>
> I went from this:
>
============================================================ ============
==
> sqlS1 = "SELECT SiteName FROM Sites S WHERE IDSite=" & S1
> set rsS1 = server.CreateObject("ADODB.Recordset")
> rsS1.Open sqlS1, WebconnectString, adOpenStatic, ,adCmdText
> Site = rsS1("SiteName")
>
============================================================ ============
==
>
> To this (looks good with a fixed width font:-):
>
============================================================ ============
==
> sqlS1 = "SELECT SiteName FROM Sites S WHERE
> IDSite=?" arParams = array(S1)
> set cmd = createobject("ADODB.Command")
> cmd.CommandText = sqlS1
> set cmd.ActiveConnection = myConn
> set rsS1 = cmd.Execute(,arParams)
> Site = rsS1("SiteName")
>
============================================================ ============
==
>
> ...resulting in the same Site name in both cases.
>
> Is my revised code above the proper way to do it? You actually did
> not have any examples in the reference posts you provided that showed
> queries returning data; they were all for INSERT statements.

PS. An alternative to using the Open method is to set the Connection's
cursorLocation to adUseClient, thus making a clientside static cursor
the default cursor built by the Execute method.

In the unlikely event that you need a serverside static, keyset or
dynamic cursor, you need to use the Open method as described in my
previous reply

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Constructing secure queries in classic ASP

I appreciate your working through this so readily with me, and for the added
example of setting a parameter. I do not quite follow your details about
cursors and not sure when or why I would want to use one, but I will wait
until the occasion arises before burdening you further. :-)

BTW, I was unable to find a good reference in MSDN for ADO (perhaps too
old?) but for your tidbit collection here is a good one from DevGuru:
http://www.devguru.com/technologies/ado/home.asp. The page on the
command.execute method, for example, provides all the juicy details:
http://www.devguru.com/technologies/ado/8529.asp

Re: Constructing secure queries in classic ASP

michael sorens wrote:
> I appreciate your working through this so readily with me, and for
> the added example of setting a parameter. I do not quite follow your
> details about cursors and not sure when or why I would want to use
> one, but I will wait until the occasion arises before burdening you
> further. :-)

A recordset is a cursor :-)

>
> BTW, I was unable to find a good reference in MSDN for ADO (perhaps
> too old?) but for your tidbit collection here is a good one from
> DevGuru: http://www.devguru.com/technologies/ado/home.asp. The page
> on the command.execute method, for example, provides all the juicy
> details: http://www.devguru.com/technologies/ado/8529.asp

http://msdn2.microsoft.com/en-us/library/ms807498.aspx
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Constructing secure queries in classic ASP

Thanks for the link!

Re: Constructing secure queries in classic ASP

I ran into one minor glitch. If I add in this line as you suggested...

cmd.CommandType=adCmdText

.... I receive this error:

ADODB.Command error '800a0bb9' Arguments are of the wrong type, are out
of acceptable range, or are in conflict with one another.

If, however, I add the type onto the end of the Execute call, as in...

set rsS1 = cmd.Execute(,arParams, adCmdText)

....then it runs without complaint. So two questions:
(1) Are those approaches equivalent?
(2) Why would I see the error indicated?

Re: Constructing secure queries in classic ASP

michael sorens wrote:
> I ran into one minor glitch. If I add in this line as you suggested...
>
> cmd.CommandType=adCmdText
>
> ... I receive this error:
>
> ADODB.Command error '800a0bb9' Arguments are of the wrong type,
> are out of acceptable range, or are in conflict with one another.

Which line throws the error? This line? or the .Execute line?

>
> If, however, I add the type onto the end of the Execute call, as in...
>
> set rsS1 = cmd.Execute(,arParams, adCmdText)
>
> ...then it runs without complaint. So two questions:
> (1) Are those approaches equivalent?

They should be.

> (2) Why would I see the error indicated?

Frankly I'm at a loss. I've got pages and pages using this property
assignment with no error.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Constructing secure queries in classic ASP

The assignment to CommandType throws the error. That is consistent with the
documentation, which states that it will complain upon assignment if it is
incompatible. But it should *not* be incompatible here, right?!

I guess as long as it works when I put it in the Execute call, I can get
by... but I do dislike an anomaly that even stumps you, the expert. Sigh.

Re: Constructing secure queries in classic ASP

michael sorens wrote:
> The assignment to CommandType throws the error. That is consistent
> with the documentation, which states that it will complain upon
> assignment if it is incompatible. But it should *not* be incompatible
> here, right?!

Right
Is the constant defined properly? Try this:

on error resume next
cmd.CommandType = adCmdText
if err<>0 then
response.write err.description & "'
"
response.write "adCmdText contains '" & adCmdText & "'
"
end if
on error goto 0

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Constructing secure queries in classic ASP

Aha! I was fooled into thinking the constant was defined because (a) it
seemed to work in the Execute call (whereas in reality it just did not
complain about its failure) and (b) it was used in several places elsewhere
in the same file (which I inherited:-( ).

If I wanted to include adovbs.inc, what is its path?

Re: Constructing secure queries in classic ASP

michael sorens wrote:
> Aha! I was fooled into thinking the constant was defined because (a)
> it seemed to work in the Execute call (whereas in reality it just did
> not complain about its failure) and (b) it was used in several places
> elsewhere in the same file (which I inherited:-( ).
>
> If I wanted to include adovbs.inc, what is its path?
It can be any place you want it to be. The MDAC installation puts it into
....\program files\common files\system\ado
so you could create a virtual directory in IIS pointing at that location.

A better way is to use the metadata tag in global.asa to reference the type
library:
http://www.aspfaq.com/show.asp?id=2112
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Constructing secure queries in classic ASP

Sorry to pester you further... Reviewing your example of explicitly defining
a Parameters collection, I am wondering if there is any additional security
afforded by that approach vs. just passing a generalized array to the Execute
method?

Re: Constructing secure queries in classic ASP

michael sorens wrote:
> Sorry to pester you further... Reviewing your example of explicitly
> defining a Parameters collection, I am wondering if there is any
> additional security afforded by that approach vs. just passing a
> generalized array to the Execute method?

Nope. They are processed as parameters and therefore not parsed as SQL,
so no sql injection is possible.

The only things gained with the explicit Parameters collection are:
1. Type safety -
a) you will get an "earlier" error if you attempt to set the Value
of a numeric parameter object to a non-numeric value
b) Using the variant array method forces ADO to "guess" at the
correct datatype to be used for the parameter. It's never happened to
me, and I've never seen a report anywhere of it happening, but I suppose
ADO could guess incorrectly leading to a hard-to-debug runtime error.
2. The ability to pass and retrieve output and return parameter values
to and from a stored procedure

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.