Running Ad-Hoc SQL Queries in ASP

Have you ever wished you can run ad-hoc queries on your database from a Web page? This article shows you how a simple Web form and some ASP code can execute SQL statements against a database and display the results in your Web browser.

Step 1: Keep it secure!

The first thing to do is to secure the page you’re about to create! (In this article, we’ll call the page runsql.asp.) There are a number of ways to secure your Web page using IIS (which we won’t get in to here). If you don’t secure the page, you’re taking the chance that anyone can execute SQL statements on your server – something you likely don’t want to do – so be sure to start off with security in mind! You should also consider using the HTTPS protocol, as your SQL statements and results can be intercepted if they are sent in clear text.

Another suggestion is to use a read-only account in the connection string when connecting to a SQL Server database. Although this prohibits your from executing INSERT, UPDATE, and other SQL statements, it will eliminate a significant security risk.

Step 2: ASP Necessities

To keep things clean and proper, we’re going to use Option Explicit at the top of our ASP page. To trap errors later on, we’ll use On Error Resume Next. We’ll also declare the variables we’ll be using later on in the page, and set three ADO constants (which is much cleaner than importing the entire ADOVBS.INC file!).

Option Explicit
On Error Resume Next
Dim objConn, objRec, strSQL, i, iFields
Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Const adCmdText = &H0001

Step 3: Create the HTML form

The HTML form has a TEXTAREA field where we’ll type our SQL statements, and the typical submit and reset buttons. The form submits to itself (i.e. runsql.asp). Note that the TEXTAREA’s initial value is set to Request.Form("sql") – so after we submit the form, we’ll easily be able to see the query we just submitted and make changes to it.

<form action="runsql.asp" method="post">
<textarea name="sql" cols="60" wrap rows="10">
<%= Request.Form("sql") %>
</textarea><br>
<input type="submit">&nbsp;&nbsp;<input type="reset">
</form>

Step 4: Executing the SQL Query

We check to see if the form was submitted by examining Request.ServerVariables("REQUEST_METHOD"), which equals POST when our form is submitted. (Remember, we used the POST method in our form.)

If the form is submitted, we’ll be nice and display the SQL query received in Request.Form("sql"), then create our ADO Connection and Recordset objects. Be sure to open the Connection object with the connection string of your datasource.

Finally, we can open our Recordset. Note the use of adOpenForwardOnly and adLockReadOnly – since we don’t need any special recordset cursors or locking, specifying these options gives the best performance.

If Request.ServerVariables("REQUEST_METHOD") = "POST" Then
 
    Response.Write "Query:<br><b>" & Request.Form("sql") & "</b><br>"
 
    Set objConn = Server.CreateObject("ADODB.Connection")
    Set objRec = Server.CreateObject("ADODB.Recordset")
 
    strSQL = Request.Form("sql")
    objConn.Open "dsn=mydsn"
    objRec.Open strSQL, objConn, adOpenForwardOnly, adLockReadOnly, adCmdText

Step 5: Checking for Errors

Remember when we specified On Error Resume Next at the top of our code? We can trap the error here. Chances are if anything failed up to this point, the objRec.Open call will fail, too, generating an error. If there’s an error, we’ll display it to the user.

If Err.Number <> 0 Then
    Response.Write "An error has occurred!<br>"
    Response.Write "Error number:      " & Err.number & "<br>"
    Response.Write "Error description: " & Err.description & "<br>"

Step 6: Displaying the Results

Continuing right after the error checking, we have to handle two situations: if the recordset is empty, or if it is not empty. If it’s empty, we’ll simply tell the user.

ElseIf objRec.EOF Then
    Response.Write "Empty recordset returned."

If objRec is not empty, we need to display it in an HTML table. First, we want to display the field names as a header row. Recordset field names are stored in the Fields collection, which is the default member of the Recordset object. We’ll read the number of fields (objRec.Fields.Count) into a variable, then use a For loop to output the Name parameter of each field (which contains the column name for the field).

Note that we could have specified objRec(i).Name instead of objRec.Fields(i).Name. Since the Fields collection is the default member of the Recordset object, it isn’t necessary to specify it, assuming it’s what you want.

Else
    iFields = objRec.Fields.Count
    Response.Write "<table border=1><tr>"
    For i = 0 To iFields - 1
        Response.Write "<td><b>" & objRec.Fields(i).Name & "</b></td>"
    Next
    Response.Write "</tr>"

Next, to display the rows in the recordset, we’ll loop through the Recordset using a While loop, moving from record to record using Recordset.MoveNext. Using the same technique as above, we’ll loop through the Fields collection and return the Value parameter for each field, which contains the data in the field.

Again, we could have substituted objRec(i).Value for of objRec.Fields(i).Value, since Fields is the default member of the Recordset object.

    While Not objRec.EOF
        Response.Write "<tr>"
        For i = 0 To iFields - 1
            Response.Write "<td>" & objRec.Fields(i).Value & "</td>"
        Next
        Response.Write "</tr>"
        objRec.MoveNext
    Wend
    Response.Write "</table>"
End If

Step 7: Cleaning Up

As always, you should close your Connection and Recordset objects when you’re done with them, and set their values to Nothing. Don’t rely on the script engine to do this for you. One memory leak can do considerable damage to your server’s performance!

objRec.Close
Set objRec = Nothing
objConn.Close
Set objConn = Nothing

Summary: RUNSQL.ASP

Being able to execute SQL statements from a Web page can come in very handy, especially when you’re not able to connect to the SQL server directly (if it’s inaccessible through a firewall or proxy server). If you secure this properly, you’ll have a powerful and easy to use tool in your Web development toolbox.


COMPLETE SOURCE CODE FOLLOWS:


<%

Option Explicit

On Error Resume Next

Dim objConn, objRec, strSQL, i, iFields

Const adOpenForwardOnly = 0

Const adLockReadOnly = 1

Const adCmdText = &H0001

%>

<html>

<head>

<title>RUNSQL</title>

</head>

<body>

<form action="runsql.asp" method="post">

<textarea name="sql" cols=60 wrap rows=10>

<%= Request.Form("sql") %>

</textarea>

<input type="submit">&nbsp;&nbsp;<input type="reset">

</form>

<%

If Request.ServerVariables("REQUEST_METHOD") = "POST" Then

  Response.Write "Query:<br><b>" & Request.Form("sql") & "</b><br>"

  Set objConn = Server.CreateObject("ADODB.Connection")

  objConn.Open "dsn=mydsn"

  strSQL = Request.Form("sql")

  Set objRec = Server.CreateObject("ADODB.Recordset")

  objRec.Open strSQL, objConn, adOpenForwardOnly, adLockReadOnly, adCmdText

  If Err.Number <> 0 Then

   Response.Write "An error has occurred!<br>"

   Response.Write "Error number:      " & Err.number & "<br>"

   Response.Write "Error description: " & Err.description & "<br>"

  ElseIf objRec.EOF Then

   Response.Write "Empty recordset returned."

  Else

   iFields = objRec.Fields.Count

   Response.Write "<table border=1><tr>"

   For i = 0 To iFields – 1

    Response.Write "<td><b>" & objRec.Fields(i).Name & "</b></td>"

   Next

   Response.Write "</tr>"

   While Not objRec.EOF

    Response.Write "<tr>"

    For i = 0 To iFields – 1

     Response.Write "<td>" & objRec.Fields(i).Value & "</td>"

    Next

    Response.Write "</tr>"

    objRec.MoveNext

   Wend

  End If

  objRec.Close

  Set objRec = Nothing

  objConn.Close

  Set objConn = Nothing

End If

%>

</body>

</html>

A few rounds of Froogle

The big news in technology today is Google’s IPO. To celebrate, I went to Google’s shopping site, Froogle. One of the fun things about Froogle is that it displays on the home page “A few of the items recently found with Froogle.” I refreshed the home page a few times and looked for fun an interesting items. Here’s a few:

  • kung fu hamster: Yes, you can own your very own Kung Fu Dancing Hamster! Apparently there are many different versions of this fun toy/killing machine. It runs for $8 to $10. No information if this search was originally submitted to Froogle by my friends over at AngryHamster.
  • spork: The legendary school cafeteria utensil and weapon, the spork clearly still has fans. Incredibly, the Froogle search for spork found not the garden-variety plastic version, but a titanium weapon for attacking your potatoes and flinging your peas. Priced around $9.
  • moon shoes: Just the pictures of these things bring back memories of Kiss. How safe does this sound: Put your pre-teen children (or adults) on 5 1/2-inch platform shoes that are super-bouncy then hop around like a moron. Not recommended for those weighing over 180 pounds, which sadly eliminates a significantly population considering the American obesity trends.
  • sb-50dx: Apparently it only took about ten refreshes of the Froogle page before finding someone who searches like I do: by entering the exact manufacturer’s part number of the item to ensure you only get matches for exactly what you’re looking for. This is apparently nothing more than a camera light, but it sure sounds futuristic with a name like “sb-50dx.”
  • rat zapper: If you (like me) were expecting some tazer-like device that zaps rats, you’ll be disappointed (like me). Apparently, Rat Zapper is a brand name for plain old rat poison.
  • pot rack: Like many of you, my first thought was some type of odd 60’s throwback marijuana doohickey. No such like; it’s just a pot rack.

Happy shopping!

Article to appear in September 2004 DNDJ

I received word that my third article for .Net Developer’s Journal will appear in the September 2004 issue. It’s the third in the series of articles entitled, “C# and the .Net Framework: Tying It All Together”. The first article was in the January 2004 issue, the second article was in the May 2004 issue. Why such a delay between articles? They never told me they were publishing the first article, so I didn’t get the second article to them until after it printed. They never received the third article (which was originally sent in May 2004) and we didn’t catch up with each other until last month. I’m working on the fourth article now and hopefully it’ll be in the October or November issue.