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>