SQL Injection Prevention Fail

Posted on May 14, 2010 by johnfx

Well, at least they know that SQL Injection is an issue….

I just hope for the sake of the customers who bank at Sacramento Credit Union that the programmers responsible for this web site aren’t relying on blacklisting certain strings and/or characters as the sole means of protecting their system from SQL Injection attacks, but I’m not optimistic.

Regardless, this is also a classic example of taking a programming problem and dumping it in the user’s lap. If I’m a user of this site I would definitely be thinking, “Thanks for the lesson in cyber security, propeller head. Now can I just get on to finding out my checking account balance? I don’t really have time to do your job for you today.”

SQLInjection Fail

Here is the highlighted text, in case it is  difficult to read in the image:

Why are the Security Questions used?
The first time you login and enroll in Protection Plus, you will be asked to enter five Security Questions and corresponding answers. The Security Questions are used if you do not want to register the computer you are currently using. With the Security Questions, we can make sure it is you logging in when you use different computers, such as, a internet bar computer.

The answers to your Security Questions are case sensitive and cannot contain special characters like an apostrophe, or the words “insert,” “delete,” “drop,” “update,” “null,” or “select.”

Why can’t I use certain words like “drop” as part of my Security Question answers?

There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: “select,” “delete,” “update,” “insert,” “drop” and “null”.

Computer Alcoholism

Ignore for a moment the sins of grammar and the promotion of “Security Questions” to a proper noun with all initial caps due thereto. What in the wide world of sports is a “bar computer?”

Are they referring to those video poker machines at bars? Are they implying it is not safe to use those machines to do my banking?

Filed under: Fail, Usability | 1 Comment »

Creating a unique constraint that ignores nulls in SQL Server

Posted on March 26, 2010 by johnfx

Proper constraints and data validation are the cornerstone of robust applications. They enforce explicit contracts between your database and the applications that rely on them reducing the number of contingencies that your application code needs to account for.

In this article, I address a specific variant of the uniqueness constraint that is a little tricky to implement in Microsoft’s SQL Server which has likely discouraged developers from applying this type of validation even when it is indicated in the logical data  model. I’m not aware of a standardized term, so let’s just call it a “Null agnostic Unique  Constraint.”

Null Agnostic Unique Constraint
A constraint that doesn’t allow duplicate values with the twist that Null values are not considered duplicates of other Null values.

A practical example
This table/design goal is a good  example of a situation where you might need a null agnostic unique constraint.

Customers Table
CustID integer, Primary Key, Identity
SSN varChar(11)
CustName varChar(100)

Validation Requirement: For privacy reasons, not every customer is required to provide their SSN, but the table should reject any duplicate values among customers with this field populated.

The Problem

The core issue behind dealing with nulls as part of a unique index is the ambiguity about what the expression NULL=NULL should evaluate to. In database systems NULL is understood to represent a value that is unknown or undefined. Is one unknown the same as another? That is a matter of debate.

Microsoft, by implementing the SET ANSI_NULLS option and defaulting it to OFF, seems to be taking the seemingly more logical position that you can’t say whether two unknown values are equivalent, and by default evaluates NULL=NULL to NULL.

ANSI, however, takes a contrary view in the SQL-92 standard, in which they specify that both NULL=NULL and NULL<>NULL should evaluate to false. This is further codified in their prescribed approach to the treatment of NULL values in the implementation of unique constraints.

If columns with UNIQUE constraints do not also have NOT NULL constraints, then the columns may contain any number of  NULL values.

Unfortunately, Microsoft didn’t true up SQL Server completely to this standard in their implementation of unique constraints/indexes. Regardless of the ANSI_NULLS setting, SQL Server treats Null as a discrete value when checking for duplicates. That is, it  allows at most a single Null value in a unique column.

Oddly, enough this implementation seems to imply the assumption that NULL=NULL evaluates to True. Further, it is a tad befuddling that MS decided to implement ability to allow NULL values in unique columns, which is optional in the standard, but ignored the hard requirement to not treat multiple NULL values as duplicates.

The bottom line  is that implementing a null agnostic unique constraint on our Customers table won’t be quite as easy as it might be with PostgreSQL or MySQL, which handle duplicate checking on NULLS in accordance with SQL-92.

Solution 1: Filtered Indexes

The first approach is clearly the cleanest and the most performant, but requires the use of filtered indexes which were not introduced until SQL2008.

Under this approach you simply add a unique index to the table on the field that you need to be unique, and specify that you only want to include rows in the index where that field is not null using the new WHERE syntax of the CREATE INDEX command.

Here’s how it would work using the Customers table example.

CREATE UNIQUE NONCLUSTERED INDEX [UniqueExceptNulls]
ON [Customers] (SSN)
WHERE [SSN] IS NOT NULL

Any values inserted or updated into the SSN column will be checked for duplicates unless they are NULL, because NULL values are excluded from the index.

Easy, huh?

Solution 2: Constraint on computed column

If you are using a pre-2008 version of SQL Server, it isn’t quite so easy, but here is an alternate approach that is a passable workaround. It should perform reasonably well, but has the downside of adding the clutter of an extra computed column that won’t make much sense to anyone else that looks at the table.

Here, we create a unique constraint on a computed field that forces the null values to look unique. It does this by evaluating to the value you want to check for uniqueness unless that value is a NULL, in which case it evaluates to a unique value to that row that won’t trip the duplicate check. The easiest way is to base the computed field on an identity field.

Here is the implementation of this approach, again using the Customers table.

CREATE TABLE [Customers] (
  [CustId] INT IDENTITY(1,1) NOT NULL,
  [SSN] varchar(11) NULL,
  [CustName] varchar(100) NOT NULL,
  [SSN_UNQ]  AS (CASE WHEN [SSN] IS NULL
                      THEN cast([CustID] as varchar(12))
                      ELSE '~' + [SSN] END
),
CONSTRAINT [UQSSN_Unique] UNIQUE  ([SSN_UNQ])
)

As you can see, the computed field will contain SSN, unless that field is NULL, in which case it will contain the CustID.

The ~ character is prepended to the SSN just to cover the remote possibility that a CustID might match an existing SSN.

Note: If you are using at least SQL 2005, you might be able to squeeze some extra performance out of this by marking the computed column PERSISTED.

Solution 3: UDF Check Constraint (Not recommended)

I’m mentioning this for the sake of completeness, but with the caveat that this approach could create considerable performance issues if used on any table with a significant amount of INSERT or UPDATE traffic.

Typically a check constraint is used for simple validation based only on values from the row being inserted or updated, but there is a loophole that allows you to access other rows in the table through the use of a User Defined function in the check constraint.

To implement this approach you would just write a UDF that checks for duplicates in the table for a specified value, excluding of course, the current row being updated.

There might be situations where this is preferable to the computed column constraint method, but I can’t think of one offhand. Use this approach at your own peril.

[Implementation omitted to discourage its use.]



1 CREATE UNIQUE NONCLUSTERED  INDEX [UniqueExceptNulls]
2  ON [Customers] (SSN)
3 WHERE [SSN] IS NOT NULL

Filed under: Technology Tips | Tagged: , , , , , , | 3 Comments »

Tip: Use delayed service starting to speed up the boot process of your development machine

Posted on March 9, 2010 by johnfx

Waiting on the computerOne of the minor aggravations in my life, right behind lyricists who want to “hold me tight” despite all the homeless adverbs in the world,  is time required to get my Windows development machine from a cold brick to a state where I can do productive work.

This is exacerbated by the fact that I, as a developer,  tend to re-start my machine more often than a typical computer user. Also, I’ve got A number of heavyweight services than run on my development machine including web and database servers so I can work on my projects when disconnected from the mothership.

I’ve been doing my best to set the services that I use sparsely to manual and then start them only when I am ready to use them to minimize boot time. But today I found another option. While I was in services manager starting up a local SQL instance I noticed an unfamiliar value in the Startup Type column: Automatic (Delayed Start).

Automatic (Delayed Start) Service Startup Type

Delayed Start? What's that all about?

After some quick research, I discovered that this new startup option was introduced in Vista to expedite the boot sequence by de-prioritizing services that need to be launched at startup, but for which there is no hurry to get them spun up.

The gist of it is this: Services with this setting will be launched at the end of the start-up process and the initial thread is given a priority of THREAD_PRIORITY_LOWEST to avoid sacrificing UI responsiveness during the start-up sequence just to get things like “Google Updater” running immediately.

Some candidates for delayed start-up immediately come to mind:

  • Local development  instances of Database or Web Servers.
  • Updaters: Windows Update, Google, Windows Search, Any type of indexer.
  • Any of the crapware  from Apple or Adobe that they insist are so important they must run at all times.

Maybe I’m late to the party discovering this feature, but like many companies mine completely ignored Vista and are just now getting around to Windows 7  and in the process discovering a lot of nice “new” features that may have technically been around a while.

I’m not proud. I’m willing to admit my extended ignorance of this feature if it can benefit another developer out there.

Did you guys know about this? Anyone know of other nice goodies in Windows 7/Vista  that are especially handy for tweaking development machines?

Let me know in the comments!

http://blogs.technet.com/askperf/archive/2008/02/02/ws2008-startup-processes-and-delayed-automatic-start.aspx

Filed under: Technology Tips | Tagged: | 1 Comment »

Syntactic sugar can make your application fat and slow

Posted on March 5, 2010 by johnfx

Although some of us might not like the association, programmers are essentially just another class of end-user. Hidden beneath our masochistic penchant for vi and a command line, programmers are just as thirsty for cushy features that simplify their tasks and bend their tools around their own aesthetic preferences.

I’m not so much talking about GUI programming tools or code generators, Lord knows we hate those. I’m referring to those handy language features that don’t necessarily add any extra capabilities, but provide a way to write code that is more readable and usually more succinct.

For example C#’s using statement that not only lets you wrap your code in concise blocks around your IDisposable resources, it also cleans up after you just like your mother did back in the day.

 using (SqlConnection conn=new SqlConnection(connectionString))
  {
     conn.Open();
 string MyNewPet= cmd.ExecuteScalar("SELECT TOP 1 Animal
                                         FROM Zoo"); } //thanks mom!

Of course you can accomplish the same objectives in your code without these handy shortcuts, but once you learn them you probably wouldn’t want to. Peter Landin, who put the Lambda in Lambda-Lambda-Lambda, coined the term ‘Syntactic Sugar‘ to describe these elements of programming languages.

A construct in a language is called syntactic sugar if it can be removed from the language without any effect on what the language can do: functionality and expressive power will remain the same.
-Wikipedia (Omnibus source of all human knowledge)

Great, now I have a name for something I already knew all about.
Thanks John, but I’ve got work to do…

A Rose By Any Other name

ASCII RoseNot so fast, I’m just (finally) getting to the point of this article. Syntactic sugar isn’t always just another entry point for the same functionality. As is true with most things, you can’t delegate the work without giving up some measure of control. Further it is dangerous to assume that the architect of the platform you use must be smart to have that job and probably is better suited to make those calls anyway.

They may know the platform better than you, but remember that they can never understand as well as you what you are ultimately trying to accomplish. Optimizing for an unknown problem requires making assumptions that may not hold in your specific usage.

By no means am I saying that you should avoid using syntactic sugar, just that it is prudent to understand to some degree what is happening under the covers to avoid nasty surprises.

Syntactic sugar can be more fattening than it looks

If you have been following this blog, you know that I recently completed my certification for SQL 2008. While brushing up on the newer features of t-SQL I came across a bit of  previously unfamiliar syntactic sugar, the WHERE CURRENT OF syntax.

In the context of an open updatable Cursor, it is a shorthand way to reference the current row

UPDATE MyTable SET TargetField=2 WHERECURRENTOF myCursor

instead of

UPDATE MyTable SET TargetField=2WHERE MyTable.ID =@FetchedIDOfCurrentRow

Rock on!  I mean, if you n00b enough to be using cursors in the first place, at least you can do it with style, right?

Well, yes. Until my profiling turned up some shocking results. As it happens, the WHERE CURRENT OF syntax performed consistently and considerably slower than the same code using an ‘equivalent’ WHERE clause.

If anything, I expected it to outperform the legacy syntax because it had the row ready and waiting for an update because I was explicitly communicating my intentions about which row would be updated in advance. So SQL should be holding that row at the ready for the update. Bzzzt! Wrong. Fundamentally Wrong. Couldn’t be wrong-er if my name was Wrong Wrongerstien.

Despite anecdotal evidence supporting my initial findings, I was still incredulous that my assumptions could be smashed so soundly. So I set out to get some hard data using the simplest version of  an update inside a cursor that I could muster:

<snip> 
WHILE@@FETCH_STATUS=0  BEGIN UPDATE MyTable
 SET TargetField=@FetchSource+2 WHERECURRENTOF myCursor
     --WHERE ID=@FetchID  FETCHNEXTFROM myCursor
 INTO@FetchID, @FetchSource END<snip>

I tried a number of cursor types and indexing strategies, but in every case the WHERE CURRENT OF syntax was demonstrably slower, and the margin increased in a near linear amount as the  source data got larger.

The Query Execution Plans

I’m still dissecting the execution plans and will come back and post more once I understand the discrepancy, but the high level perspective is pretty telling.

Where ID=@FetchID Plan:
Clustered Index Update -> Update

Where CURRENT OF Plan:
9 additional steps including another Clustered Index Update, and 2 Clustered Index Seeks.

That’s all for now. Be careful out there and watch out for those empty code calories from syntactic sugar!



http://en.wikipedia.org/wiki/Peter_J._Landin

Filed under: Technology Tips | Tagged: | Leave a comment »

Quickly Link SQL Server Tables in Access

Posted on October 15, 2009 by johnfx

Long gone are the days when I did much application development in MS Access.  However, I still find it a darn good tool for querying, reporting and doing quick edits to data stored in other platforms.

I wrote this code ages ago to allow me to quickly create linked tables in MS Access for SQL Server tables/views. It is much faster than the manual UI approach and doesn’t require setting up a DSN on the local system to make the connection

Usage

Use the immediate window (ctrl-G) to enter these commands:

Link every table in a SQL database(“MYDB”) from SQL Server Instance (“MYSQLSERVER”)
? LinkTable(“MYSQLSERVER“,“MYDB“,True)

Link a single table(“MySQLTable”) in a SQL database (“MYSQLDB”)  from SQL Server Instance named “MYSQLSERVER”
? LinkTable(“MyTableAliasInAccess“, “MYSQLSERVER“,“MYDB“,“MYSQLTABLE“,True)

The Code

Just paste this code into a new module in the Access database and you are ready to go.

Sub LinkAllTables(Server As String, database As String, OverwriteIfExists As Boolean)
    'Usage Example (link all tables in database "SQLDB" on SQL Server Instance SQO01, overwriting any existing linked tables.
    'linkalltables "SQL01","SQLDB", true

    'This will also update the link if the underlying table definition has been modified.

    Dim rsTableList As New ADODB.Recordset
    Dim sqlTableList As String

    sqlTableList = "SELECT [name] as tablename FROM sysObjects WHERE (type = 'U')"

    rsTableList.Open sqlTableList, BuildSQLConnectionString(Server, database)
    While Not rsTableList.EOF
        If LinkTable(rsTableList("tableName"), Server, database, rsTableList("tableName"), OverwriteIfExists) Then
            Debug.Print "Linked: " & rsTableList("tableName")
        End If
        rsTableList.MoveNext
    Wend

    rsTableList.Close
    Debug.Print "Done."

End Sub

Function LinkTable(LinkedTableAlias As String, Server As String, database As String, SourceTableName As String, OverwriteIfExists As Boolean)
    'This method will also update the link if the underlying table definition has been modified.

    'The overwrite parameter will cause it to re-map/refresh the link for LinktedTable Alias, but only if it was already a linked table.
    ' it will not overwrite an existing query or local table with the name specified in LinkedTableAlias.

    'Links to a SQL Server table without the need to set up a DSN in the ODBC Console.
    Dim dbsCurrent As database
    Dim tdfLinked As TableDef

    ' Open a database to which a linked table can be appended.
    Set dbsCurrent = CurrentDb()

    'Check for and deal with the scenario ofthe table alias already existing
    If TableNameInUse(LinkedTableAlias) Then

        If (Not OverwriteIfExists) Then
            Debug.Print "Can't use name '" + LinkedTableAlias + "' because it would overwrite existing table."
            Exit Function
        End If

        'delete existing table, but only if it is a linked table
        If IsLinkedTable(LinkedTableAlias) Then
            dbsCurrent.TableDefs.Delete LinkedTableAlias
            dbsCurrent.TableDefs.Refresh
        Else
            Debug.Print "Can't use name '" + LinkedTableAlias + "' because it would overwrite an existing query or local table."
            Exit Function
        End If
    End If

    'Create a linked table
    Set tdfLinked = dbsCurrent.CreateTableDef(LinkedTableAlias)
    tdfLinked.SourceTableName = SourceTableName
    tdfLinked.Connect = "ODBC;DRIVER={SQL Server};SERVER=" & Server & ";DATABASE=" & database & ";TRUSTED_CONNECTION=yes;"

    On Error Resume Next
    dbsCurrent.TableDefs.Append tdfLinked
    If (Err.Number = 3626) Then 'too many indexes on source table for Access
            Err.Clear
            On Error GoTo 0

            If LinkTable(LinkedTableAlias, Server, database, "vw" & SourceTableName, OverwriteIfExists) Then
                Debug.Print "Can't link directly to table '" + SourceTableName + "' because it contains too many indexes for Access to handle. Linked to view '" & "vw" & SourceTableName & "' instead."
                LinkTable = True
            Else
                Debug.Print "Can't link table '" + SourceTableName + "' because it contains too many indexes for Access to handle. Create a view named '" & "vw" & SourceTableName & "' that selects all rows/columns from '" & SourceTableName & "' and try again to circumvent this."
                LinkTable = False
            End If
            Exit Function
    End If
    On Error GoTo 0

    tdfLinked.RefreshLink
    LinkTable = True

End Function

Function BuildSQLConnectionString(Server As String, DBName As String) As String
    BuildSQLConnectionString = "Driver={SQL Server};Server=" & Server & ";Database=" & DBName & ";TRUSTED_CONNECTION=yes;"
End Function

Function TableNameInUse(TableName As String) As Boolean
    'check for local tables, linked tables and queries (they all share the same namespace)
    TableNameInUse = DCount("*", "MSYSObjects", "(Type = 4 or type=1 or type=5) AND [Name]='" & TableName & "'") > 0
End Function

Function IsLinkedTable(TableName As String) As Boolean
    IsLinkedTable = DCount("*", "MSYSObjects", "(Type = 4) AND [Name]='" & TableName & "'") > 0
End Function

Filed under: How To..., Technology Tips | Tagged: , , , | 9 Comments »

Blog Response: Lookup fields in Access are evil?

Posted on October 2, 2009 by johnfx

A comment on one of my answers on StackOverflow brought to my attention a blog article “The Evils of Lookup Fields in Tables” that argues against defining lookup fields in MS Access table definitions. Quite interesting. I don’t do nearly as much Access development as I once did, but I had never encountered any serious issues using Lookup fields.

The article is written by Arvin Meyer and Joan Wild, who appear to be part of the Microsoft MVP program. My experience predisposes me to give a lot of credibility to MVPs even when I am initially skeptical of something they say. However, given the full context of their complete argument. I am calling foul on this one.

Lookup Fields in Access

First, a quick primer on this handy feature of MS Access for anyone that is not familiar with it.

In the design view for a table definition, you can define a display control type for a each field that is used to display it in data-sheet view and also used as a default when the field is added to a form. “Lookup field” in this context of this article and the one it responds to simply refers to a field defined with a “Combo Box” display control that uses a table or query as the row source.

Access Lookup Field Definition

The effect of using a lookup field for the configuration shown above is that the user gets a handy drop-down control in data-sheet view as shown below. In this example, the Company field is actually a numeric foreign key to the companies table, but the user gets to pick a company based on a CompanyName instead of the CompanyID. The CompanyID is still stored in the underlying table.

Lookup Field Datasheet view

In fact, by changing the configuration slightly, you can even make it display BOTH the CompanyID and CompanyName in the drop-down.

Lookup Field Datasheet - 2 Columns

The great thing about this is that it removes one of the excuses novice developers often make for not normalizing their databases sufficiently.

The users don’t want to have to look up the CompanyID to enter a new customer! It’s just easier to put all the company information in the customers table.
-Novice Developer

When you consider that Access is the tool of choice for a lot of novice developers, removing obstacles to good DB design was a good strategy for the Access product team at Microsoft.

Dubious Claims

So back to the assertion that this feature should be avoided.  Let’s tackle the claims one at a time.

1. A Lookup field in a table displays the looked-up value. For instance, if a user opens a table data-sheet and sees a column of company names, what is in the table is, in fact, a numeric CompanyID, and the table is linked with a select statement to the company table by that ID.

Rebuttal: Yes, this is exactly the point of this feature and why it is beneficial.

2a. Any query that uses that lookup field to sort by that company name won’t work.

Rebuttal: Sorting does indeed work. It sorts on the first displayed column in the lookup control and not necessarily the underlying value. It is much more intuitive to the user to have it sort based on what is displayed rather than a hidden underlying value that you are trying to hide from them in the first place.  In any case, how often do you want to sort on a numeric foreign key value anyway? Retracted. I still take issue that sorting in a query on a lookup field does work correctly, but I can see that the original author may have been talking about using the sort feature in table/form view which is admittedly a little wonky. I’ll offer the benefit of the doubt on this one, but would like to see clarification in the original article.

2b. Nor will a query that uses a company name in that field as a criteria.

Rebuttal: When writing queries, you do have to specify the criteria using the underlying value rather than the user-friendly displayed value.  However, you have to assume that someone writing queries is not the target of the end-user abstraction provided by lookups and will know to supply a numeric criteria for “CompanyID.”

2c. If a user creates a combo box to select the company using a value list, the data in the table can be over-written.

Rebuttal: First, one of the reasons for this feature is that it auto-creates the combo-box with the appropriate settings. Although, I have no idea what they mean by “the data in the table can be over-written”, it is true that someone could modify the form design to do things that mess up the data. This is not unique to any specific feature.

3. Another relationship is created which then creates another set of indexes when a Lookup field is created, thus bloating the database unnecessarily.

Rebuttal: You shouldn’t use a feature that simplifies life for the developer and end user because it might bloat the database? You weren’t planning on adding a relationship and/or indexes to your foreign and primary keys anyway?

4. If a combo box based on the lookup is used in a form, and a filter is applied, the persistent filter effect of Access often saves the filter and the next time the form is opened, there will be a prompt for the value (which cannot be provided, thus creating an error).

Rebuttal: I tested this one out in thoroughly and could not reproduce what this person is describing.  I have seen similar behavior in Access before even without lookup fields. It seems unrelated.

5. Reports based on the lookup field need a combo box to display the data, causing them to run more slowly. The underlying record source can also be modified to include the table, however the index, (unless it was set up within a proper relationship) may not be optimized.

Rebuttal: Uhhh. Yeah. If you want to display a value from a related table, the report is going to need to pull it from that other table combo-box or not. Assuming that the combo-box method is slower than adding a join to the underlying query, so what? Adding a lookup to a field doesn’t REQUIRE you to have a combo-box, and not having a lookup field doesn’t stop you from using a combo box.

6. Lookup fields mask what is really happening, and hide good relational methodology from the user.

Rebuttal: The point of your app is not to teach the user about “good relational methodology.” As much as you’d like them to care about the intricacies of your very important job, they just want a way to efficiently work with their data. In fact, this feature encourages the use of good database design by giving the developer a way to hide the technical details behind the scenes.

7. The database cannot be properly upsized to, or queried by, another engine (without removing all the lookup fields) because no other engines use or understand them.

Rebuttal: Wrong, Wrong, Wrong. Other databases might not support this particular feature to display the data automatically with the cool drop downs, but they can use these fields just like any other. The upsizing thing is irrelevant, despite the fact that you configure this in the table design, it is a UI feature not a data format.

8. If security is implemented, permissions to tables[are] usually denied, and [Run with owner’s permissions] queries are used for data access. There will often be errors that there are no permissions on a specific table that isn’t even being used in a query (because the lookup field is). If the queries are nested or complex, it can take some time to track down the lookup that’s causing the error (that is, if it occurs to you).

Rebuttal: So the argument here is that if you set up complicated security, this feature will trip you up because you have to remember to give permissions on the related table. I suppose you don’t have to worry about permissions on the related table if you forgo this feature and manually add that table to the query? Is it really that confusing if you get an error telling you it doesn’t have permissions on {specific table} just because it isn’t explicitly in the query? Sounds like a PICNIC problem.

Update:

As pointed out in the comments (thanks JamesL), in my rebuttal to the weak arguments against using lookup fields, I was remiss in not mentioning that there are some legitimate complaints about the implementation of this feature in MS-Access. I still don’t think the feature should be discarded completely, but it definitely needs to be used with an understanding of its quirks and shortcomings.

The problem with lookup fields

Using the built in sorting and filtering features provided by access in form and table view against a lookup field that is configured to display a value other than the underlying value (as shown in my examples above) is definitely wonky and can be confusing to users. The implementation is textbook case of a leaky abstraction.

Note: I did some experimentation and found that different versions of Access misbehaved in slightly different ways.

Problem 1: The sort feature in the UI (at least in Access 2007)  sorts based on the underlying field value regardless of what is displayed.  This could definitely be confusing to users.

Problem 2: The right-click filter-by-example feature  is REALLY misleading and flat out broken. The filtering options are based on the displayed value, but do nothing when you click them because they don’t match up with the underlying data-type of the field (usually). In Access 2007, this feature simply ignores your click when you try to use the feature whereas in earlier versions of Access it would throw a type mismatch error.

Filter-By-Example screenshot

Oops!

Thanks again to JamesL for pointing out the omission/error in the original article. I definitely want to make sure I am providing accurate and useful information in this blog, and rely on the watchful eye of my readers to keep me in line.

Filed under: Technology Tips | 26 Comments »

Beware of this trap when comparing strings in T-SQL with trailing spaces

Posted on September 9, 2009 by johnfx

Ran across one of those odd-syntax behavior anomalies that tend to send programmers on a one way trip down Wild Goose Chase Lane.

The Trap

All three of the following commands will give the counter-intuitive result of  ‘equal’

if '' = '        '    print 'equal' else  print 'not equal'
if '   ' = '     '    print 'equal' else print 'not equal'
if 'abc' = 'abc  '    print 'equal' else print 'not equal'

I have confirmed through experimentation that this odd behavior only applies to trailing spaces. The following example gives the expected result of ‘not equal’

if 'abc' = '   abc'    print 'equal' else print 'not equal'

Yikes! Scary isn’t it? Makes you wonder if you have code floating out there that depends on the premise that  strings with different lengths are never equal.

What is happening here?

According to Microsoft KB Article 316626, this quirky behavior is prescribed by the ANSI-SQL 92 specification.

SQL Server follows the ANSI/ISO SQL-92 specification (Section 8.2, <Comparison Predicate>, General rules #3) on how to compare strings with spaces. The ANSI standard requires padding for the character strings used in comparisons so that their lengths match before comparing them. The padding directly affects the semantics of WHERE and HAVING clause predicates and other Transact-SQL string comparisons. For example, Transact-SQL considers the strings ‘abc‘ and ‘abc ‘ to be equivalent for most comparison operations.

What to do about it

Although it is a mess when it comes to writing readable code, appending a character on both sides of the comparison seems to be the approach with the least potential for nasty side effects, especially when used with Unicode data.

if @stringA + 'x' = @stringB + 'x' ...

If you are going to do this, for God’s sake please comment the reason why, or better yet wrap it in a string comparison user defined function.

Some other ideas are presented in this blog article by Anthony Bloesch, including:

  1. Convert the strings to varbinary values and then test for equality.  (Has issues with Unicode combining marks)
  2. Replace all the space characters with an unlikely character.  (Difficult to pick character that won’t occur naturally)
  3. Use the like operator to do the equality check. (May cause strings to test equal if they match because of wildcards in the second string)
  4. Use XQuery’s string comparison. (Has issues with Unicode combining marks)

Doing my part

I was shocked to find nary a mention of this quirky, albeit standards-compliant, behavior in the SQL Server BOL article on the = operator despite the fact that every developer I demonstrated this behavior to received it with the same befuddled surprise.

I’ve added a community entry to the SQL BOL article on this operator in the hopes of saving someone a lot of headache tracking down an obscure logic bug someday.

More Information

Stack Overflow: SQL Server Empty String vs Space

Microsoft KB: How SQL Server Compares Strings with Trailing Spaces (316626)

SQL Server Books Online: = (Equals) (Transact-SQL)

Filed under: Technology Tips | Tagged: , , , | 9 Comments »

« Previous Page — Next Page »