Ad Hoc Queries in ASP.NET

What is ad hoc SQL Query?

Ad hoc SQL query is dynamically created when needed to return information like database search results, user profile info or more generally, any information that are related to user specific request.

Simple example of ad hoc query (a.k.a. dynamic query) could be if we need to return all products from one category.

Let say this category has Category ID = 237. The SQL query can look like this:

SELECT * FROM Products WHERE Category_ID = 237

ASP.NET server side code that uses this query could look like this:

[ C# ]

using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
 
public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        // Create connection and read connection string from web.config file
        SqlConnection conn = new SqlConnection(
            ConfigurationManager.ConnectionStrings["My Connection String"].ConnectionString);
        try
        {
            // Open database
            conn.Open();
            // Create command object with SQL query like above
            SqlCommand comm = new SqlCommand("SELECT * FROM Products WHERE Category_ID=237", conn);
            SqlDataReader myReader = comm.ExecuteReader();
 
            // Use data on some way...
 
            // Close connection
            conn.Close();
        }
        catch
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }
    }
}

[ VB.NET ]

Imports System
Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient
 
Partial Class _Default
    Inherits System.Web.UI.Page
 
 
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        ' Create connection and read connection string from web.config file
        Dim conn As SqlConnection = New SqlConnection( _
            ConfigurationManager.ConnectionStrings("My Connection String").ConnectionString)
        Try
            ' Open database
            conn.Open()
            ' Create command object with SQL query like above
            Dim comm As SqlCommand = New SqlCommand("SELECT * FROM Products WHERE Category_ID=237", conn)
            Dim myReader As SqlDataReader = comm.ExecuteReader()
 
            ' Use data on some way...
 
            ' Close connection
            conn.Close()
        Catch
            If conn.State = ConnectionState.Open Then
                conn.Close()
            End If
        End Try
    End Sub
End Class

How to build ad hoc SQL queries by constructing a string

ASP.NET server side code above is useful if we search Category with id = 237. But, web applications usually need to search more categories so we need to build our query dynamically. With ad hoc queries you can do that on two ways: by constructing a string or by using parameters. Constructing a string is potentially more dangerous way because you must to care about SQL injection attacks. In short, you must check for every input that comes from user. Client side validation is useful to reduce traffic to web server and make your application more optimized, but don't depend on it. Client side validation is not safe and you always need to use server side validation too. If user input is integer like in our example, it is easy to validate. If input is a string you can make it safe with code like this:

[ C# ]

string Name = tbName.Text.Replace("'", "''");

[ VB.NET ]

Dim Name As String = tbName.Text.Replace("'", "''")

As you probably suspect, apostrophe is very dangerous character when building SQL queries dynamically because apostrophes (single quotes) is used to surround string values. If user input contains apostrophe you will get incorrect SQL or some malicious instruction. But, if you replace every apostrophe with two apostrophes like in code line above, your SQL query will be correct and will save original string in database.

How to build ad hoc queries by using parameters

Although replacing of single apostrophes can protect you, there is always a question will you forget to do it on some place in code. If you work in large team it is even more possible that some programmer make that mistake. Better solution is to build ad hoc queries by using parameters. If you build your ad hoc query with parameters, you don't need to worry about SQL injections. For even more security it is good practice to set parameter's data type and size. ASP.NET code snippet for building ad hoc queries that uses parameters could look like this:

[ C# ]

// Open database
conn.Open();
// Create command object with SQL query like before, but with parameter instead of hard coding
SqlCommand comm = new SqlCommand("SELECT * FROM Products WHERE Category_ID=@categoryid", conn);
// Add parameter of type of integer
comm.Parameters.Add("@categoryid", SqlDbType.Int);
// Set value to parameter categoryid
comm.Parameters["@categoryid"].Value = Convert.ToInt32(Session["CategoryID"]);
 
SqlDataReader myReader = comm.ExecuteReader();
 
// Use data on some way...
 
// Close connection
conn.Close();

[ VB.NET ]

' Open database
conn.Open()
' Create command object with SQL query like before, but with parameter instead of hard coding
Dim comm As SqlCommand = New SqlCommand("SELECT * FROM Products WHERE Category_ID=@categoryid", conn)
' Add parameter of Int type
comm.Parameters.Add("@categoryid", SqlDbType.Int)
' Set value to parameter categoryid
comm.Parameters("@categoryid").Value = Convert.ToInt32(Session("CategoryID"))
 
Dim myReader As SqlDataReader = comm.ExecuteReader()
 
' Use data on some way...
 
' Close connection
conn.Close()

If parameters are used, user can input apostrophe and try to place malicious query, but that input can't harm your database.

What is better: Ad hoc queries or stored procedures?

An "Ad hoc queries vs. stored procedures" is common topic in many programming forums online. There are several myths about stored procedures repeated from time to time:

Myth 1: Stored procedures execute faster than ad hoc queries

On SQL Server executing of stored procedures is not faster than ad hoc queries with parameters. I found nice test on Speed Test: Dynamic SQL vs. Stored Procedures. The speed of execution is almost the same and ad hoc queries are even faster if SQL contains sub selects, at least on SQL Server 2005.

The reason for this is because stored procedures on SQL Server are compiled at execution time, like ad hoc queries.

Myth 2: Stored procedures are safer from SQL injection attacks

Just use parameters and your ad hoc SQL query will be safe. I don't see how stored procedure is safer than that. Check this code:

[ C# ]

string SQLQuery = "EXEC sp_GetCustomerData '" + tbCustomerName.Text + "'";

[ VB.NET ]

Dim SQLQuery As String = "EXEC sp_GetCustomerData '" & tbCustomerName.Text & "'"

This code calls stored procedure but it is not safe. You always need to use parameters or to check every client input, with or without stored procedure.

You can make your web application safer if you use user roles on SQL Server. In short, don't use admin account if you need only SELECT statement. Make at least two roles, and restrict access to tables and using of INSERT, UPDATE, DELETE and DROP statement. If you need to limit access to some columns or rows in table you can do that with views.

Myth 3: If you need to change SQL, you can do it in stored procedure and don't need to build and re-deploy a code

This sounds good in theory, but I can't remember last time I only changed database structure without making changes in ASP.NET application. In real world with almost every change in SQL you need to make some changes in ASP.NET code so you need to build and re-deploy code anyway. As a result, by using stored procedures in these cases you are working more instead of less.

Another thing worth to mention is portability between different database systems. If your application needs to support multiple databases it is easier to do with ad hoc queries. Standard SQL is always portable and you need to change only parts where specific T-SQL commands are used.

Don't understand me wrong, stored procedures are not evil. They are best choice if you do some complex work, like make temporally tables, do complicated operations on data, long time data processing etc. In that cases stored procedures are better solution, especially if you can reduce traffic between application and database server.

Building ad hoc queries with Search Control

As you see, the best answer on question: "How to make query to database?" is "It depends". Ad hoc queries can be the best solution in some scenarios. However, common problem is building of WHERE clause since writing of dynamic WHERE clauses can be very difficult. Of course, you still want to enable your users to easily find what they looking for. For database search we developed custom ASP.NET Search Control, which supports "All Words", "Any Word", "Exact Phrase" and "Boolean Expression" search, returns WHERE clause safe from SQL injection attacks and works with SQL Server, Access, MySQL and Oracle databases.

Tutorial toolbar:  Tell A Friend  |  Add to favorites  |  Feedback  |   Google