On SQL Server and romance

Summary: Quick comment on SQL Server and PostgreSQL.

Quote of the day comes from Rob Sullivan:

"The SQL Server install is born from a truck stop romance between TFS and Sharepoint that someone found in a garbage bag in a dumpster and burned to a DVD."

Ha-ha... so true.

On a separate note, Rob's post raised my interest in PostgreSQL. I wonder, what challenges PostgreSQL presents to user, admins and developers (especially to those coming from the SQL Server camp).

Check T-SQL string for invalid characters

Summary: Transact-SQL user-defined function (UDF) that can help you find illegal characters in user input strings.

When programming in Transact-SQL, it's often needed to verify that input strings do not contain any illegal characters. For example, you may want to make sure that an input string does not contain any single or double quotes. Here is a user-defined function (UDF) that can help you perform this check.

The UDF takes two input parameters:

• Input string that needs to be checked and
• String containing characters that are not allowed in the input string

If no illegal characters are found, it will return an empty string; otherwise, it will return a string containing all illegal characters found in the input string.

------------------------------------------------------
-- Description:  
--  Finds all character from a given set in a string.
--
-- Parameters:
--  @String
--    String to search.
--  @CharSet
--    Character set.
--
-- Returns:
--  Characters found in the string or empty string.
------------------------------------------------------
if exists
(
  select
    1
  from
    sysobjects
  where
    id = Object_ID('[dbo].[CheckChars]')
  and type= 'FN'
)
  drop function [dbo].[CheckChars]
go

-- Create function.
create function [dbo].[CheckChars]
(
  @String  as varchar(8000),
  @CharSet as varchar(255)
)
returns
  varchar(255)
as
begin
  declare @Index int
  declare @Char  varchar
  declare @Chars varchar(255)

  set @Chars = ''

  if (@String is null OR datalength(@String) = 0)
    return @Chars

  if (@CharSet is null OR datalength(@CharSet) = 0)
    return @Chars

  set @Index = 1

  while (@Index <= datalength(@CharSet))
  begin
    set @Char = Substring(@CharSet, @Index, 1)
    
    if (CharIndex(@Char, @String) > 0)
     if (CharIndex(@Char, @Chars) <= 0)
       set @Chars = @Chars + @Char
        
    set @Index = @Index + 1
  end

  return @Chars
end
go

if @@Error = 0
  grant execute on [dbo].[CheckChars] to Public
go

You can call this UDF like this:

if (dbo.CheckChars('Hello, world!', ',!') = '')
  print 'Good input.'
else
  print 'Bad input.'

Here is the source code of the UDF creation script:

• Download CheckChars.sql

Convert a string to a table in T-SQL

Summary: Transact-SQL function that converts a string of comma-separated numbers to a table of integers.

Here is a common SQL programming scenario. Your code receives a string holding comma-separated values (CSVs), such as IDs of records stored in a table (say "98,256,17,34"). Now it needs to retrieve these records from the database. How do you do this?

[Note: For the sake of simplicity, I use a comma-separated value (CSV) holding numbers, but the string can contain other values, such as dates, or other strings, which can be separated by semicolons or other delimeters.]

Option #1. Use EXEC (not recommended).
The obvious option would be to generate a dynamic SQL query and execute it via an EXEC statement. For example, you can do the following:

USE AdventureWorks

DECLARE @ProductIDs varchar(256)
DECLARE @Query      varchar(256)

SELECT @ProductIDs = '1,350,400,440'

SELECT @Query =
'select
  ProductID,
  [Name],
  ProductNumber
FROM
  Production.Product
WHERE
  ProductID in (' + @ProductIDs + ')'

exec(@Query)

There are two major problems with this approach. First, generating the query -- in this example it's a SELECT query, but it can be any query -- as a string will degrade readability: you will lose syntax highlighting and IntelliSense (if you're using a new SQL Server 2008 IntelliSense feature or some other third-party tool, such as SQL Prompt). Second, and even more important, this approach is prone to SQL injection attack. Even if you use a command parameter to pass a comma-separated value (CSV) to the stored procedure, since you're simply appending it to dynamic SQL, you must make sure that it does not contain suspicious characters. Whether you do it in T-SQL code or in the application code (C#, or whatever), it's a hassle.

Option #2. Convert string of values to a table of values (recommended).
A better alternative would be to convert the CSV to a table of numbers (again, I use numbers in this example, but the values can be of any type). You can accomplish this with the help of a user-defined function (UDF), such as this one:

CREATE FUNCTION [dbo].[ConvertCsvToNumbers]
(
  @String AS VARCHAR(8000)
)
RETURNS
  @Numbers TABLE (Number INT)
AS
BEGIN
  SELECT @String = 
    LTRIM(
      RTRIM(
        REPLACE(
          ISNULL(@String, ''), '  ' /* tab */, ' ')))

  IF (LEN(@String) = 0)
    RETURN

  DECLARE @StartIdx       INT
  DECLARE @NextIdx        INT
  DECLARE @TokenLength    INT
  DECLARE @Token          VARCHAR(16)

  SELECT  @StartIdx       = 0
  SELECT  @NextIdx        = 1

  WHILE @NextIdx > 0
  BEGIN
    SELECT @NextIdx = CHARINDEX(',', @String, @StartIdx + 1)

    SELECT @TokenLength =
      CASE WHEN @NextIdx > 0 THEN @NextIdx
      ELSE LEN(@String) + 1
    END - @StartIdx - 1

    SELECT @Token = 
      LTRIM(
        RTRIM(
          SUBSTRING(@String, @StartIdx + 1, @TokenLength)))

    IF LEN(@Token) > 0
      INSERT
        @Numbers(Number)
      VALUES
        (CAST(@Token AS INT))

    SELECT @StartIdx = @NextIdx
  END
  RETURN
END

Now you can reference this UDF in a query, such as:

SELECT
  ProductID,
  [Name],
  ProductNumber
FROM
  Production.Product prod,
  dbo.ConvertCsvToNumbers(@ProductIDs) numbers
WHERE
  prod.ProductID = numbers.Number

In addition to improving readability and security, this method lets you treat data contained in the CSV as a regular table. If you want to try this approach, you can download the script generating the UDF, which is more robust than the listing above (the script sets the permissions and contains code for repeated compilation):

• Download ConvertCsvToNumbers.sql

See also:
How To: Protect From SQL Injection in ASP.NET

Database installer revised

Summary: Overview of changes made to the previously published database installer script (dbsetup.vbs).

I'm not sure what happened to the new and "improved" MSDN Magazine (the web site), but all of a sudden, I started getting messages from readers asking to send them the VBScript file (dbsetup.vbs) accompanying my Data Deployment: Streamline Your Database Setup Process with a Custom Installer article (from the September 2004 issue of the magazine). I checked the site and did not find a download link, so I'm offering* the most recent version of the script here:

[Download dbsetup.vbs]

I'd also like to mention a couple of alternatives which you may like better than my approach, but first, let me describe modifications I made to the script in case you insist on using it. (If you don't know how the script works, please read the article; it explains the script execution logic.)

The most recent update to dbsetup.vbs was triggered by an idiosyncrasy in Visual Studio 2008. For some reason, Visual Studio 2008 saves new SQL source files added to database projects in the UTF-8 format, even if the files only contain regular 7-bit ASCII characters. [I could not find an explanation to this phenomenon, but since new files added to other (non-database) projects get saved in the ASCII format, I suspect that the issue is caused by the default file templates associated with the Visual Studio 8 database projects (someone at Microsoft must have accidentally saved these files encoded in UTF-8). I may be wrong, though.]

UTF-8 encoded files containing English (ASCII) text are almost identical to regular ASCII files, except that the former include three special bytes called byte-order mark (BOM) identifying the encoding format in the beginning of the file. Most programs, such as text editors, can handle UTF-8 encoding (you may not even notice that the file is UTF-8 encoded), but unfortunately, VBScript (the scripting engine) is not one of them.

When opening a text file in VBScript, you can specify whether the file is encoded in ASCII (TristateFalse) or Unicode (TristateTrue), but if you specify the Unicode format, VBScript will assume that the file is encoded as UTF-16, which is not the same as UTF-8. Because VBScript did not (and still does not) support the UTF-8 format, the original version of dbsetup.vbs could not process UTF-8 formatted files (the returned database error complained about invalid characters).

Tip:	To learn about Unicode, read Joel Spolsky's article The Absolute Minimum Every Software Developer Absolutely, Positively Must Know About Unicode and Character Sets (No Excuses!).

To handle UTF-8 encoded files, I added a workaround: when processing text files (SQL scripts and others), dbsetup.vbs now looks for the Unicode byte-order marks. If the script finds the UTF-8 specific bytes, it discards them and processes the rest of the file as regular ASCII text (Note: If you want to include real Unicode characters in text files, use UTF-16 encoding, which VBScript and dbsetup.vbs can handle). This workaround is not very elegant, but it seems to work.

Two other modifications affect repair scripts (scripts executed to repair a database without incrementing its version). To provide support for multiple database versions (in source control), dbsetup.vbs now expects to find repair scripts under the version labels in the Repair folder (the Repair folder is expected to have subfolders named after the database versions to which they apply, similar to the approach used for grouping the upgrade scripts). Also, to make sure that repair scripts added during test cycles get executed during production deployment, dbsetup.vbs will process them after an upgrade (only repair scripts defined for the current version will be processed).

Finally, dbsetup.vbs now supports conditional execution of scripts. If you want to selectively include certain scripts, keep them in a subfolder (under a regular folder) named after a directive you'll use when running setup and/or enclose the file references in the #if DIRECTIVE ... #endif code block (replace DIRECTIVE with a meaningful name) if you explicitly name the scripts in of the Files.txt files. To include the script, launch dbsetup.vbs with the /set command-line parameter followed by a directive you want to include, such as /set:DIRECTIVE. When dbsetup processes a folder, it first handles regular scripts, and then, if launched with the /set command, it will process all conditional files from the subfolders matching the directives included in the /set command, or the conditional sections of the Files.txt file.

I have to say that there may be better database deployment methods than my simple process. For example, you can try using Database Schema Build & Deployment Tools offered by Database Edition and Team Suite of Visual Studio Team System 2008.

Tip:

In addition to Microsoft, other third-party vendors started offering database installation tools, such as: Database Installer by Hive Studios DBGhost tools by Innovartis SQL Packager by Red Gate Software DBDeploy (FREE) by Graham Tackley, Nick Ashley, and Sam Newman ScriptDB open-source console app to script objects in a database

A couple of weeks ago, I read an article Deploying Database Developments** in which Alexander Karmanov describes an alternative database deployment methodology. Although Alexander's approach seems a bit more complicated (then, say, my technique), it may offer a more comprehensive solution. I didn't have much time to delve into Alexander's solution, but you may want to give it a try.

Additional references:
Get Your Database Under Version Control by Jeff Atwood
Evolutionary Database Design by Martin Fowler and Pramod Sadalage
11 Tools for Database Versioning by secretGeek

---

*	I wrote dbsetup.vbs in a rush a few years ago, so now, when I'm looking at the source code, I can see how imperfect it is. Frankly, I'm a bit ashamed that this code has been made public, but I also do not want to waste time rewriting the parts that work. Anyway, if you are eager to criticize the programming style and approach, I'm with you.
**	Does the title Deploying Database Developments sound right to you? Development is a process, so how do you deploy a process? You can deploy an application, code, etc, but development... I don't think so.