Introduction

There is a very good reason for having user acceptance testing over and above unit testing and formal testing of you code. As a developer, you will test your code for the things that you expect to be trapped. A professional tester will take the functional requirement specification and develop a test script to test that the delivered product conformed to the original specification.

The users, however test for "real" life, which is something entirely different. I found this out recently, and came across a "gotcha" with T-SQL

The problem

We had built a medium sized web site using a web content management system. One of the features of the web site was a password reminder facility that e-mailed the password to the user. This facility was used in more than one place within the site.

The user complained that when passwords were retrieved they were being truncated. They provided us with the password that was being truncated and we tried to reproduce the fault in our development environment.

Unfortunately, we could not duplicate the error.

Investigation

As the fault was reported early on in the UAT phase it could have been attributed to a number of things.

• User error
• A knock on effect from an unrelated fault
• E-mail corruption
• SQL coding fault.
• ....etc

To resolve a fault you have to be able to reproduce the steps to reproduce that fault.

Step One.

Firstly we looked at all the pages that could have some affect on the password. It turned out that there was only one page that ever wrote the password into the database. There were no pages that allowed a password to be updated and only one page for triggering functionality to e-mail the password reminder. All database activity was carried out via stored procedures.

It is the nature of the CMS to allow one page to be placed in several places within the site structure but with a different URL and look & feel.

Step two

We had to look carefully at the database.

• Was the password being put into the database correctly? - YES
• Was there anything strange about the table structure in the user's test environment? - NO
• Was there anything different about the database in the user's test environment? - NO
• Would sp_recompile and sp_refreshview resolve the issue? - NO

We checked the structure of the table holding the password Again exhaustive testing showed that it was perfectly OK. At this stage it was looking like the problem was not a SQL problem but either a user error or a problem with the E-mail transmission.

Step three

Before we pass the buck to the poor schmuck who looks after the email server we have to eliminate our code as a source of the problem. This meant that our next step was to write logging lines into our web page code and RAISERROR statements into all stored procedures dealing with our passwords.

Our logging lines were to validate what arguments were being sent to SQL Server. For the particular CMS we use SQL statements have to be built up as a string as there is no ADO facility. This meant that the SQL to be executed was built up as a string so the logging simply meant writing out that string before it was executed. We could simply cut and paste the logged SQL statement from the application log directly into Query Analyzer and run the resulting statement.

Our RAISERROR statement was used to log a string built up from the parameters submitted to the stored procedure to confirm that the correct parameters were being passed. See syntax below.

RAISERROR(@sDebugString,10,1) WITH LOG

If you are going to echo errors into the SQL Server logs then I recommend increasing the registry key

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\NumErrorLogs 

to a suitable number. This prevents your valuable debug data vanishing when the logs roll over.

I stress that all the debug code that I am describing here must be removed prior to go live and therefore all such insertions should be documented.

At this stage we found absolutely nothing. Email transmission problems or User error was looking ever more likely but saying "user error" without being able to prove it is as popular as Michael Moore at a George Bush appreciation society meeting!

Fortunately we didn't say user error because the next day the user provided proof that the system was truncating passwords, though they weren't able to detail the steps they took to reproduce the fault.

Step four

We had established (so we thought) that SQL was not at fault. The stored procedures did exactly what they were supposed to, both when executed within the web site, and when executed by SQL Query Analyzer. The database was storing data correctly.

To cut a long story short we eventually eliminated email transmission errors, encryption/decryption errors and in fact, every error you can think of.

So what the hell was going on!?!?!?!

The solution

The breakthrough came when the user pinned it down to amending a user record. This was odd because there was no facility to amend the password! Or was there?

We write our stored procedures to do many things in many different contexts and in this case our user update stored procedure had the following line in an UPDATE statement.

[Password]=ISNULL(@sPassword,[Password])

Easy and clear enough, if a NULL password parameter is passed (and for this implementation it was ALWAYS NULL) simply update the password with itself. Then we had a look at the user data and saw that the field was being truncated to 20 characters.

Bingo. We looked at the definition of our @sPassword parameter and saw that it was defined as VARCHAR(20)!

BOL says that the ISNULL function must have arguments of the same type. It doesn't say that they should be of the same size though clearly it should!

Post mortem

So how had this fairly rudimentary error occurred?

Well, passwords are stored as encrypted values and the encryption routine is one of many built into the CMS. As the CMS can only execute stored procedures by building them up as text strings we found out, after the initial design, that the encrypted value would break the code if it contained control characters. This meant that the encrypted value had to be escaped to cater for these control characters. This meant that passwords were encrypted using a DES routine and then the resulting value HTML encoded so that it could be included in a SQL command as a string. This bulked the size of the password value up by a factor of 4.

The original database design allowed for a password of up to 20 characters, hence the VARCHAR(20) designation. HTML encoding meant that the password field in the database table and the @sPassword argument in the procedures should have been changed to VARCHAR(80).

This change never got propagated to the user update stored procedure simply because the update procedure was working "correctly" and as the developer knew that the user update facility was not used to update passwords, the procedure was never changed. The developer had already written the code on the understanding that superfluous arguments would be ignored if a NULL value was passed and therefore they coded the web front end so that the SQL String would always pass NULL into the @sPassword variable.

We simply didn't know that ISNULL returns an exact type AND SIZE of its 1st argument.

GOTCHA!!