Precision Computing

A discussion came up recently in an internal mailing list about the difference between Cmdlets and Functions. What was most interesting was how many hard and fast distinctions and rules came out of the discussion, even though those distinctions don’t really exist.

The key point is that there really isn’t a hard and fast philosophical distinction between cmdlets and functions.

Most differences largely exist because of setup, installation, and supported features. In general, cmdlets (and snapins) are the best way to distribute features, while functions are the easiest way to implement them.

• It is currently much easier for ISVs and developers to package and deploy cmdlets than it is to package libraries of functions or scripts.
• It is currently easier to write and package help for cmdlets.
• Cmdlets are written in a compiled .NET language, while functions (and scripts) are written in the PowerShell language. On the plus side, this makes certain developer tasks (such as P/Invoke calls, working with generics) much easier in a cmdlet. On the minus side, this makes you pay the ‘compilation’ tax — making it slower to implement and evaluate new functionality.
• In V1,  Cmdlets provide the author a great deal of support for parameter validation, and tentative processing (-WhatIf, -Confirm.) This is an implementation artifact, though, and could go away in the future.
• [Various technical points] Functions support scoping, different naming guidelines, management through the function drive, etc. See your favourite scripting reference for these details.

So, really, the differences are really a function of water finding its level -- people tend to one or the other based on what we've currently made easiest. Aside from the implementation language, all of these factors are transient, though, and could change at any time.

I learned of an evil SQL escape sequence today, in the context of a data migration script. The script moves data from one database to another, but the schema changes between databases, so you can't use BCP. As such, the script needs to ensure that it does not modify any of the database content.

The script works well, and it creates insert statements based on the values and the content of the old data. In the VALUES clause, the script single-quotes the data, and then escapes out any single quotes in the data. According to best practices (and  all of the documentation I can find,) that is enough to neutralize any SQL string.

INSERT INTO MyTable ([Column1], [Column2])
VALUES ('Value '' 1', 'Value 2')

But it turns out there were backslash characters getting randomly dropped in the content we were migrating.

After some research, it turns out that only backslash characters before newlines get erased. If you want to include backslash followed by a <cr><lf>, you have to do give the sequence:  \\<cr><lf><cr><lf>. Newlines seem to be the only characters that cause a backslash to become a special character – and the only place I can find that mentions it is http://support.microsoft.com/kb/164291.

As far as I can tell, this must be a relic from ‘Embedded SQL for C and SQL Server’ days: http://msdn2.microsoft.com/En-US/library/aa225198(sql.80).aspx

EXEC SQL INSERT INTO TEXT132 VALUES ('TEST 192 IS THE TEST FOR THE R\
ULE OF THE CONTINUATION OF LINES FROM ONE LINE TO THE NEXT LINE.');

So, now you know – escaping single quotes isn’t enough to neutralize a SQL string.  (And it should be pointed out that you should use SQLCommand and prepared statements whenever possible. In this case, it's not possible :) )