 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion GroupsGeneralPHPASPPerlColdFusionFlashHTML, CSS, ScriptsBrowsers |
Webmaster Forum / ASP / Database Access / August 2007Constructing 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") > I am familiar with the SqlCommand object available in .NET (VBScript > or C#) which provides a powerful security feature by using a template [quoted text clipped - 4 lines] > 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.inetserver.asp.db/msg/72e36 562fee7804e If you want type safety, which is not really needed in vbscript, you will need to code the parameter objects by hand.  SignatureMicrosoft 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. 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? > 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.. SignatureMicrosoft 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. 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. > 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) [quoted text clipped - 3 lines] > 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 SignatureMicrosoft 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. 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? > 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 SignatureMicrosoft 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. > 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) [quoted text clipped - 3 lines] > 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 SignatureMicrosoft 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. 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 > 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 SignatureMicrosoft 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" Thanks for the link! 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? > I ran into one minor glitch. If I add in this line as you suggested... > [quoted text clipped - 4 lines] > 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. SignatureMicrosoft 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. 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. > 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 & "'<BR>" response.write "adCmdText contains '" & adCmdText & "'<BR>" end if on error goto 0 SignatureMicrosoft 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. 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? > 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 SignatureMicrosoft 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" |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.