Preventing a possibly damaging Select statement

[Karl Blessing]

I am making a DSN navigator(shows the list of DSN on a computer, also shows the settings) and the user has to put in a Username and password to view the Tables and Field types itself, I also want to give them the ability to type in a custom Select statement (right now, it only shows you the field names, and their types underneath), but I also want to check for any thing in the string that might cause the database to change, so far I could only think of Create, Insert, Delete, Update. I know I'll probally be using a lot of InStr on the If..Then statement before processing the Select statement, anyone have any idea how I can do this , and know of any other potentially damaging commands? <p>Karl<br><a href=mailto:kb244@kb244.8m.com>kb244@kb244.8m.com</a><br><a href=

http://kb244.8m.com>

</a><br>Experienced in , or have messed with : VC++, Borland C++ Builder, VJ++6(starting),VB-Dos, VB1 thru VB6, Delphi 3 pro, Borland C++ 3(DOS), Borland C++ 4.5, HTML,Visual InterDev 6, ASP(WebProgramming), QBasic(least i didnt start with COBOL)

 

[JDurbin]

You might want to make your own QBE form on your web page and handle it yourself with the user supplied parameters <p>John Durbin<br><a href=mailto: > </a><br><a href= > </a><br>ICQ VFP ActiveList #73897253

 

[Karl Blessing]

QBE?<br><br>well anyways this is what I have done so far.<br><br><FONT FACE=monospace><br>....<br>elseif Request.Form(&quot;SQLString&quot &lt;&gt; &quot;&quot; then <br>&nbsp;&nbsp;%&gt;<br>&nbsp;&nbsp;&lt;Center&gt;<br>&nbsp;&nbsp;&lt;form id=form1 name=form1&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&lt;INPUT type=&quot;button&quot; value=&quot;Back To Table List&quot; onclick=&quot;Go('&lt;%=Request.Form(&quot;Path&quot%&gt;','', '&lt;%=Request.Form(&quot;Driver&quot%&gt;', '&lt;%=Us%&gt;', '&lt;%=Pw%&gt;', '');&quot; id=button1 name=button1&gt;<br>&nbsp;&nbsp;&lt;/form&gt;<br>&nbsp;&nbsp;&lt;/Center&gt;<br>&nbsp;&nbsp;&lt;%<br>&nbsp;&nbsp;Dim SQ<br>&nbsp;&nbsp;Dim SQL<br>&nbsp;&nbsp;Dim Detected<br>&nbsp;&nbsp;Detected = False<br>&nbsp;&nbsp;SQL = Split(&quot;DELETE CREATE UPDATE INSERT&quot;, &quot; &quot<br>&nbsp;&nbsp;SQ = Ucase(Request.Form(&quot;SQLString&quot)<br>&nbsp;&nbsp;for j = LBound(SQL) to UBound(SQL)<br>&nbsp;&nbsp;&nbsp;&nbsp;if Instr(1, SQ, SQL(j)) &lt;&gt; 0 then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Detected = true<br>&nbsp;&nbsp;&nbsp;&nbsp;end if<br>&nbsp;&nbsp;next<br>&nbsp;&nbsp;if Detected = false then<br>&nbsp;&nbsp;&nbsp;&nbsp;Conn.Open Request.Form(&quot;Path&quot, Us, Pw<br>&nbsp;&nbsp;&nbsp;&nbsp;RS.Open SQ, Conn, adOpenStatic<br>&nbsp;&nbsp;&nbsp;&nbsp;if not RS.EOF then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;%&gt;&lt;Table border=1&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;TR&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;TH colspan=&lt;%=RS.Fields.Count%&gt;&gt; &lt;%=Request.Form(&quot;SQLString&quot & &quot;- Total Records:&quot; & RS.RecordCount%&gt; &lt;/TH&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;/TR&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;TR&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;% for each Field in RS.Fields %&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;TH bgcolor=&quot;Black&quot;&gt;&lt;Font color=&quot;White&quot;&gt; &lt;%=Field.Name%&gt; &lt;/Font&gt;&lt;/TH&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;% next %&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;/TR&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;% RS.MoveFirst <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;While not RS.EOF%&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;TR&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;% for each Field in RS.Fields %&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;TD&gt;&lt;%=RS(Field.Name)%&gt; &lt;/TD&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;% next %&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;/TR&gt;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;%<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;RS.MoveNext <br>&nbsp;&nbsp;&nbsp;&nbsp;Wend %&gt;<br>&nbsp;&nbsp;&lt;/Table&gt;<br>&nbsp;&nbsp;&lt;%<br>&nbsp;&nbsp;RS.Close<br>&nbsp;&nbsp;end if<br>Conn.Close<br>end if<br>end if<br>....<br></font> <p>Karl<br><a href=mailto:kb244@kb244.8m.com>kb244@kb244.8m.com</a><br><a href=

http://kb244.8m.com>

</a><br>Experienced in , or have messed with : VC++, Borland C++ Builder, VJ++6(starting),VB-Dos, VB1 thru VB6, Delphi 3 pro, Borland C++ 3(DOS), Borland C++ 4.5, HTML,Visual InterDev 6, ASP(WebProgramming), QBasic(least i didnt start with COBOL)

 

[wduty]

The script above looks like it would work fine at a glance. You might also want to check for the appearance of single quotes because that would give you an SQL syntax error. If the user enters something like:<br><br>SELECT * FROM thetable WHERE title='sally's an idiot'<br><br>They will get this error:<br><br>Microsoft OLE DB Provider for ODBC Drivers error '80040e14' <br>[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'title='sally's an idiot''. <br><br><br>also (unrelatedly) you use alot of &lt;% %&gt; parsers. Though minimally advantageous in performance, it is supposedly faster for a server to process:<br><br>response.write(&quot;Look! Jim's parachute got caught in the waterfall!&quot<br><br>than<br><br>Look! &lt;% name %&gt;'s &lt;% device %&gt; got caught in the &lt;% landscapefeature %&gt;!<br><br>Of course that's your call. -Later. <p>--Will Duty<br><a href=mailto:wduty@radicalfringe.com>wduty@radicalfringe.com</a><br><a href= > </a><br>

 

[wduty]

sorry, the above example should have &lt;%= %&gt; not &lt;% %&gt; <p>--Will Duty<br><a href=mailto:wduty@radicalfringe.com>wduty@radicalfringe.com</a><br><a href= > </a><br>

 

[Karl Blessing]

thats no prob, your explanation is correct performance wise, but I was on a bit tight of schedule, also the HTML color provided an easier to look at than a bunch of Response.write, I guess I could try to organize it where i can get them into blocks. I have made changes to the function thus far, and will probally post those changes tomarrow so you can see. <p>Karl<br><a href=mailto:kb244@kb244.8m.com>kb244@kb244.8m.com</a><br><a href=

http://kb244.8m.com>

</a><br>Experienced in , or have messed with : VC++, Borland C++ Builder, VJ++6(starting),VB-Dos, VB1 thru VB6, Delphi 3 pro, Borland C++ 3(DOS), Borland C++ 4.5, HTML,Visual InterDev 6, ASP(WebProgramming), QBasic(least i didnt start with COBOL)

 

[Karl Blessing]

This is what I have now, and it seems to work fine, and I used Cursor 3(Static) and Lock 1(Read Only) so that even if there is a command that I didnt catch, it cant do anything.<br><br><FONT FACE=monospace><br> elseif Request.Form(&quot;SQLString&quot &lt;&gt; &quot;&quot; then <br> %&gt;<br> &lt;Center&gt;<br> &lt;form id=form1 name=form1&gt;<br> &lt;INPUT type=&quot;button&quot; value=&quot;Back To Table List&quot; onclick=&quot;Go('&lt;%=Request.Form(&quot;Path&quot%&gt;','', '&lt;%=Request.Form(&quot;Driver&quot%&gt;', '&lt;%=Us%&gt;', '&lt;%=Pw%&gt;', '');&quot; id=button1 name=button1&gt;<br> &lt;/form&gt;<br> &lt;/Center&gt;<br> &lt;%<br> Dim SQ<br> Dim SQL<br> Dim Detected<br> Detected = False<br> SQL = Split(&quot;DELETE CREATE UPDATE INSERT&quot;, &quot; &quot<br> SQ = Ucase(Request.Form(&quot;SQLString&quot)<br> for j = LBound(SQL) to UBound(SQL)<br> if Instr(1, SQ, SQL(j)) &lt;&gt; 0 then<br> Detected = true<br> end if<br> next<br> if Detected = false then<br> Conn.Open Request.Form(&quot;Path&quot, Us, Pw<br> RS.Open SQ, Conn, 3, 1<br> if not RS.EOF then<br> %&gt;&lt;Table border=1 align=center width=&quot;80%&quot;&gt;<br> &lt;TR&gt;<br> &lt;TH colspan=&lt;%=RS.Fields.Count%&gt;&gt; &lt;%=Request.Form(&quot;SQLString&quot & &quot;- Total Records:&quot; & RS.RecordCount%&gt; &lt;/TH&gt;<br> &lt;/TR&gt;<br> &lt;TR&gt;<br> &lt;% for each Field in RS.Fields %&gt;<br> &lt;TH bgcolor=&quot;Black&quot;&gt;&lt;Font color=&quot;White&quot;&gt; &lt;%=Field.Name%&gt; &lt;/Font&gt;&lt;/TH&gt;<br> &lt;% next %&gt;<br> &lt;/TR&gt;<br> &lt;% RS.MoveFirst <br> While not RS.EOF%&gt;<br> &lt;TR&gt;<br> &lt;% for each Field in RS.Fields %&gt;<br> &lt;TD&gt;&lt;%=RS(Field.Name)%&gt; &lt;/TD&gt;<br> &lt;% next %&gt;<br> &lt;/TR&gt;<br> &lt;%<br> RS.MoveNext <br> Wend %&gt;<br> &lt;/Table&gt;<br> &lt;%<br> RS.Close<br> end if<br> Conn.Close<br> end if<br></font> <p>Karl<br><a href=mailto:kb244@kb244.8m.com>kb244@kb244.8m.com</a><br><a href=

http://kb244.8m.com>

</a><br>Experienced in , or have messed with : VC++, Borland C++ Builder, VJ++6(starting),VB-Dos, VB1 thru VB6, Delphi 3 pro, Borland C++ 3(DOS), Borland C++ 4.5, HTML,Visual InterDev 6, ASP(WebProgramming), QBasic(least i didnt start with COBOL)