Tame Those Strings Part 9 - Quoting

This is a continuation of string manipulation techniques. If you are interested, you can read the other articles,

though you do not need to read it before this one. These are mostly beginning

programming articles, but advanced T-SQL programmers may still find something useful here.

• Part 1 deals with SUBSTRING and how it can be used to extract some information

  from a field of data

• Part 2 deals with CHARINDEX and how it can be used to extract some information

  from a field of data when the data is delimited rather than stored in a particular format.

• Part 3 deals with REPLACE and how it can be used to remove unwanted information

  from a field of data when the data is not in a known format.

• Part 4 deals with numeric conversions.
• Part 5 deals with STUFF.
• Part 6 works with concatenating results.
• Part 7 works with proper casing strings.
• Part 8 works with adding quotes to a string.

Introduction

More and more I see people using dynamic sql to solve their problems. While I am not a fan of dynamic sql and think it should be avoided where possible, I understand it can be helpful and solve some problems. Recently I ran across an interesting problem where someone was creating a string of dynamic sql that appeared to work fine, but cause problems when executed.

The Problem

Suppose you wanted to build a dynamic string to select names from a table. For example, let's assume you have the following table:

create table Users
(UserName varchar( 100)
, Password varchar( 100)
)

A simple table, however let's now add some data to this table:

insert Users select 'sjones', 'mypassword'
insert Users select 'bgates', 'richguy'
insert Users select 'to''reilly', 'writer'
insert Users select 'lellison', 'karatechop'

Now, suppose that we have a stored procedure that will verify the password for a given username using dynamic sql. Why use dyanmic sql? No real reason in this case, but I tried to make a simple example to show the problem.

So what is the problem? Take a look at this procedure

alter procedure spValidateUser
@user varchar( 100)
, @pwd varchar( 100)
as
declare @sql nvarchar( 1000)
select @sql = 'select 1 from Users where [username] = ''' + @user + ''' and password = ''' + @pwd + ''' '
exec sp_executesql @sql
return

If I execute this with my own information, I get:

exec spValidateUser 'sjones', 'mypassword'
(no column name)
----------------
1

which my client application can use to process the validation. However, what if I look for Tim O'Reilly.

exec spValidateUser 'to''reilly', 'mypassword'
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'reilly'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ' '.

Why would I get an error here? After all I escaped out the quote in O'Reilly. Apparently this is not enough.

The Second Solution

The problem is that when building a string for dyanmic sql, I must re-escape the quote character. Or any other reserver words because the new string will now have only a single quote inside. Since dynamic sql is executed as it's own batch, all reserver characters must be escaped. Dynamic sql is executed just as if you connected to SQL Server using Query Analyzer, pasted the sql in the window and executed it. If we examine the dynamic sql string for the last submission, it looks like:

select 1 from Users where [username] = 'to'reilly' and password = 'mypassword'

Note that the username only has a single quote in it.

So how can we fix this? The easy way is to use the same string manipulation technique previously discussed in Part 3, REPLACE. In this case, however, we need to escape the characters we are searching for as well as the replacement, so the REPLACE function looks like:

replace( @pwd, '''', '''''')

with each single quote we want to match replaced with two single quotes, plus the opening and closing single quotes. Note that we only want to replace the parameters passed in because these are the items we have no control over. If we were to run this replace on the dynamic string, the opening and closing quotes for each term of the where clause would get replaced and cause additional formatting problems.

Here's new new procedure:

alter procedure spValidateUser
@user varchar( 100)
, @pwd varchar( 100)
as
declare @sql nvarchar( 1000)
select @sql = 'select 1 from Users where [username] = ''' + replace( @user, '''', '''''') + ''' and password = ''' + replace( @pwd, '''', '''''') + ''' '
exec sp_executesql @sql
return
go

This will return the following results:

exec spValidateUser 'to''reilly', 'writer'
(no column name)
----------------
1

We now get the expected result.

Conclusions

Once again, this isn't anything that is tremndously enlightening, but it is something that you might miss and spend some time tracking down. As I mentioned at the start of this article, I don't like dynamic sql, but if you are building dynamic sql based on parameters passed into the procedure, this is something you should be aware of.

As always I welcome feedback on this article using the "Your Opinion" button below. Please also

rate this article.

Steve Jones

©dkRanch.net March 2002

Return to Steve Jones Home