by Al Hetzel

Singles and Doubles

It is somewhat ironic that the most confusing part of creating and executing a SQL statement from ASP is determining where to put the quotes. When is a single quote used? When is a double quote used? When are they used together? Which takes precedence? This article will answer these questions.
February 7, 2001

Basics

In Active Server Pages, all strings are enclosed in double quotes ("). For example, if you wanted to pull in all orders from the database, the SQL string would look like this:

sqlBase = "select * from orders"

If this string is sent to the screen, it will show up without the double quotes.

select * from orders

Numbers

The above is fairly straightforward. The double quotes surround the string so that Active Server Pages can load it into a variable. However, most SQL statements are much more complex. In the example above, suppose only order number 101 was needed. The SQL string would look like this:

sqlBase = "select * from orders where order_id = 101"

If this string is sent to the screen, it will show up like this:

select * from orders where order_id = 101

The number 101 is considered a numeric literal, i.e. a number that never changes. Since it is a literal, it can be put inside the string. Since it is a number, the double quotes are all that is put around it. In actual programming, a simplistic SQL statement like the above never happens. Instead, variables are used so that the number can change.

orderID = 101
sqlBase = "select * from orders where order_id = " & orderID

The code above declares two variables. The first variable, orderID, is a number that is set to the value wanted. Generally, this variable will be set from user input such as order 101 being selected from a list. The second variable, sqlBase, is set to the first part of the SQL statement with the first variable added to the end. This would allow changing the SQL statement based on user input.

If the string above is sent to the screen, it will show up like this:

select * from orders where order_id = 101

This is exactly like the string created with the numeric literal and would produce the same results.

Strings

Numbers are relatively straightforward as the examples above show. Strings are more complicated. Numbers inside of SQL statements are not encased in single quotes. Strings are. Consider the following example.

sqlBase = "select * from orders where order_status = 'open' "

If this string is sent to the screen, it will show up like this:

select * from orders where order_status = 'open'

This is an example of a string literal. The status that is wanted, 'open', is entered directly into the string. The status is enclosed in single quotes ('). The entire string is enclosed in double quotes. I mentioned above that in Active Server Pages, all strings are enclosed in double quotes. In SQL, all strings are enclosed in single quotes. Consider the following:

orderStatus = "open"
sqlBase = "select * from orders where order_status = " & orderStatus

This is basically the same code that was used on the number variable above. And, it yields the same result.

select * from orders where order_status = open

Unfortunately, this is not the string that is needed. Compare the above string to the string literal above. The single quotes are missing. The ASP code will run with no problem. The moment that string is executed, an error will occur. The string inside of the SQL statement must be enclosed in single quotes. The code is as follows.

orderStatus = "open"
sqlBase = "select * from orders where order_status = '" & orderStatus & "'"

This translates to:

select * from orders where order_status = 'open'

The sqlBase variable above is split into three sections. The first section holds the first part of the select statement just as it did above. The only difference is the inclusion of a single quote just inside of the final double quote. That single quote is the start of the SQL string. The second section holds the order status that is wanted. The third section is a single quote with a double quote on each side. This adds a single quote to the end of the string effectively closing the SQL string.

Be careful of spacing. It seems that including a few spaces between the quotes would make it more readable. This will cause problems.

sqlBase = "select * from orders where order_status = ' " & orderStatus & " ' "

This string is more readable but is not usable. The spaces are included in the string like so:

select * from orders where order_status = ' open '

If the actual order_status is 'open', the above SQL statement will not return any rows under any circumstances. That is sacrificing usability for readability. Not generally considered a good trade.

Back Tracking

If all of the above is still a little murky, it might help to back track. That is, start with the ending SQL statement and convert it into an ASP string. Consider the example below.

Using a variation of the examples above, we want all orders where the order id is less than 100 or the order status is closed. The SQL statement would look like this:

select * from orders where order_id < 100 or order_status = 'closed'

In this statement, the order id is not included in single quotes since it is a number. The order status is since it is a string. The first step in back tracking is to surround the statement with double quotes.

sqlBase = "select * from orders where order_id < 100 or order_status = 'closed'"

The second step is to split out all non-literal variables. In this case, the order id (100) and the order status (closed) are the variables wanted. They can be split out by separating them from the rest of the string using the "&" character. Be sure to use the double quotes at the end of every part of the string.

sqlBase = "select * from orders where order_id < " & 100 & " or order_status = '" & closed & "'"

The variables are now split out. Remember this is only an intermediary step. In other words, this code will not work as written. Instead, the variable names need to be put into string.

orderID = 100
orderStatus = "closed"
sqlBase = "select * from orders where order_id < " & orderID & " or order_status = '" & orderStatus & "'"

I have included the values for orderID and orderStatus, although they would normally be set from within the page itself such as:

orderID = Request.Form( "orderID" )
orderStatus = Request.Form( "orderStatus" )

Putting quotes into SQL statements can be confusing at first. With a little practice and following the guidelines above, it should become much easier.

Al Hetzel is an Oracle DBA/Web Developer in Dallas, Texas for KPMG LLP.