THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

Defensive database programming: adding ESCAPE clauses.

In most cases LIKE conditions should by followed by ESCAPE clauses. Let me give you an example. Consider the following table, sample data, and stored procedure:

 

CREATE TABLE Data.Messages(Subject VARCHAR(30), Body VARCHAR(100));

GO

INSERT INTO Data.Messages(Subject, Body)

SELECT 'Next release delayed', 'Still fixing bugs' UNION ALL

SELECT 'New printer arrived', 'By the kitchen area'

GO

CREATE PROCEDURE Readers.SelectMessagesBySubjectPattern

  @SubjectPattern VARCHAR(100)

AS

SELECT Subject, Body

  FROM Data.Messages

  WHERE Subject LIKE @SubjectPattern

GO

 

 Apparently the procedure works, does it not:

 

EXEC Readers.SelectMessagesBySubjectPattern '%printer%'

GO

 

Let me add some more data:

 

INSERT INTO Data.Messages(Subject, Body)

SELECT '[OT] Great vacation in Norway!', 'Pictures already uploaded to our Picasa album' UNION ALL

SELECT '[OT] Great new camera', 'Used it on my vacation, the pictures are great!'

GO

 

The following call returns nothing:

 

EXEC Readers.SelectMessagesBySubjectPattern '[OT] Great%'

GO

 

The reason is simple: because square brackets in a pattern are special characters, this pattern returns true only for subjects beginning with 'O Great' or 'T Great'. See for yourself:

 

INSERT INTO Data.Messages(Subject, Body)

SELECT 'O Great test message', 'test message'

 

EXEC Readers.SelectMessagesBySubjectPattern '[OT] Great%'

GO

O Great test message    test message

 

You have a choice: you can either have a CHECK constraint disallow special characters, or you can fix the procedure. In this case, square brackets in the subject line are considered acceptable, so the procedure must change. To fix the procedure, simply add an ESCAPE clause, as follows:

 

GO

ALTER PROCEDURE Readers.SelectMessagesBySubjectPattern

  @SubjectPattern VARCHAR(100)

AS

SELECT Subject, Body

  FROM Data.Messages

  WHERE Subject LIKE @SubjectPattern ESCAPE '\'

GO

 

You can invoke it and see for yourself that it works:

 

EXEC Readers.SelectMessagesBySubjectPattern '\[OT\] Great%'

 

[OT] Great vacation in Norway!      Pictures already uploaded to our Picasa album

[OT] Great new camera   Used it on my vacation, the pictures are great!

 

Next post in this series:

Avoid mixing old and new styles of error handling.

Published Saturday, November 08, 2008 11:43 AM by Alexander Kuznetsov

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Alejandro Mesa said:

Hi Alex,

Using the ESCAPE clause seems to be easier, but not the only way. You can double the special character to escape it.

EXEC Readers.SelectMessagesBySubjectPattern '[[OT] Great%'

GO

There is no need to double the closing one "]", because there is no meaning for it without the opening one.

On the other hand, using the ESCAPE clause can stop the optimizer from using the string summary statistics, which helps to improve the cardinality estimates for query predicates that use the LIKE operator.

Here is a blog entry from Kalen, talking about this feature.

Geek City: String Statistics

http://sqlblog.com/blogs/kalen_delaney/archive/2008/02/13/string-statistics.aspx

May be this could be a request for future versions.

Cheers,

AMB

November 8, 2008 4:10 PM
 

Alexander Kuznetsov said:

Hi Alejandro,

I cannot make your suggestion work for me. Here are my attempts:

SELECT Subject, Body

 FROM Data.Messages

 WHERE Subject LIKE '[[OT] Great%'

EXEC Readers.SelectMessagesBySubjectPattern '[[OT] Great%'

Both queries return the following:

O Great test message test message

What am I missing?

The version:

Microsoft SQL Server 2005 - 9.00.1406.00 (Intel X86)   Mar  3 2007 18:40:02   Copyright (c) 1988-2005 Microsoft Corporation  Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

November 8, 2008 5:02 PM
 

Adam Machanic said:

Is backslash the best choice for an escape character?  They are relatively more common than a lot of other potential escape characters.  For full defensive programming you might consider using ^ or ` -- which I almost never see in actual text -- or perhaps using one of the characters not on the keyboard?

Also, there is a (very small) chance you might run into the string continuation feature, which I recently discovered much by accident while working with some code that used backslashes (not used for escape characters, though)...

SELECT 'abc\

def'

November 8, 2008 5:27 PM
 

Alejandro Mesa said:

Hi Alex,

Sorry, it was my fault. Use the square brackets to surround the first one, leaving without meaning the second.

SELECT *

FROM Messages

WHERE [Subject] LIKE '[[]OT] Great%'

GO

AMB

November 8, 2008 5:46 PM
 

Alexander Kuznetsov said:

Adam, I like the idea of a non-printable escape character. I have chosen backslash because it is the escape character in C#, which makes T-SQL code more readable for C# developers.

Alejandro, works now, thanks!  Yet because most of our T-SQL is written by C# developers doing database works only part time, the easier seems to be better for our team. I agree that it could be the other way around for other teams.

November 9, 2008 3:43 PM
 

Alexander Kuznetsov said:

The following pattern is quite common in database programming: IF EXISTS(some query) BEGIN DO SOMETHING;

November 27, 2008 10:29 PM
 

Alexander Kuznetsov said:

It is well known that UPDATE ... FROM command does not detect ambiguities. Also it well known that ANSI

December 8, 2008 11:04 AM
 

Alexander Kuznetsov said:

Suppose that you need to implement the following logic: IF(row exists) Update the row ELSE Insert a new

December 14, 2008 8:37 PM
 

Alexander Kuznetsov said:

Comparing SET vs. SELECT is a very popular topic, and much of what I have to say has been said before.

January 25, 2009 5:57 PM
 

Alexander Kuznetsov said:

I have been posting examples of defensive database programming for some time now. I am by no means done

March 8, 2009 9:49 PM
 

Alexander Kuznetsov said:

I have written up two examples when a SET ROWCOUNT command breaks a seemingly working stored procedure

March 21, 2009 11:05 PM
 

Alexander Kuznetsov said:

There are three kinds of triggers: those which blow up and those which fail silently ;). Seriously, there

May 11, 2009 9:19 PM
 

Alexander Kuznetsov said:

My query used to work, but it blows up after I have added an index? The following query is not safe:

July 11, 2009 11:14 PM
 

Alexander Kuznetsov said:

You cannot assume that the conditions in your WHERE clause will evaluate in the left-to-write order -

July 16, 2009 5:41 PM
 

Alexander Kuznetsov said:

If you do not qualify columns in your query, which means that you do not specify from which tables your

October 9, 2009 4:39 PM

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works at DRW Trading Group in Chicago, where he leads a team of developers, practicing agile development, defensive programming, TDD, and database unit testing.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement