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 :) )

A question recently came in asking, "How do you invoke a generic method on a non-generic class in PowerShell?"

In an earlier post (http://www.leeholmes.com/blog/CreatingGenericTypesInPowerShell.aspx), we talked about how to create generic types in PowerShell, but classes can contain generic methods even when the classes themselves don't represent generic types.

It is possible to call the generic methods on this type of class, with the complexity of the solution depending on the complexity of the class.

Take, for example, the following class definition:

// csc /target:library GenericClass.cs
// [Reflection.Assembly]::LoadFile("c:\temp\GenericClass.dll")
using System;

public class NonGenericClass
{
    public void SimpleGenericMethod<T>(T input)
    {
        Console.WriteLine("Hello Simple World: " + input);
    }

    public void GenericMethod<T>(T input)
    {
        Console.WriteLine("Hello World: " + input);
    }

    public void GenericMethod<T>(T input, int count)
    {
        for(int x = 0; x < count; x++)
            Console.WriteLine("Hello Multi Partially Generic World: " + input);
    }

    public void GenericMethod<T,U>(T input, U secondInput) where T : IEquatable<T> where U : IEquatable<U>
    {
        Console.WriteLine("Hello Multi Generic World: " + input.Equals(secondInput));
    }

    public static void GenericStaticMethod<T>(T input)
    {
        Console.WriteLine("Hello Static World: " + input);
    }
}

It gives an example of the breadth of the problem. We have a simple generic method, a generic method with overrides, and finally, a static generic method.

If you don't need to worry about naming conflicts (as in the SimpleGenericMethod and GenericStaticMethod methods,) the solution is just a few lines of code:

$nonGenericClass = New-Object NonGenericClass
$method = [NonGenericClass].GetMethod("SimpleGenericMethod")
$closedMethod = $method.MakeGenericMethod([string])
$closedMethod.Invoke($nonGenericClass, "Welcome!")

If you do have to worry about multiple methods with the same name, then the GetMethod() method can no longer help you. In that case, we need to call the GetMethods() method, and then find which method shares the proper parameter types.

To abstract all of this logic, use the following Invoke-GenericMethod script:

## Invoke-GenericMethod.ps1 
## Invoke a generic method on a non-generic type: 
## 
## Usage: 
## 
##   ## Load the DLL that contains our class
##   [Reflection.Assembly]::LoadFile("c:\temp\GenericClass.dll")
##
##   ## Invoke a generic method on a non-generic instance
##   $nonGenericClass = New-Object NonGenericClass
##   Invoke-GenericMethod $nonGenericClass GenericMethod String "How are you?"
##
##   ## Including one with multiple arguments
##   Invoke-GenericMethod $nonGenericClass GenericMethod String ("How are you?",5)
##
##   ## Ivoke a generic static method on a type
##   Invoke-GenericMethod ([NonGenericClass]) GenericStaticMethod String "How are you?"
## 

param( 
    $instance = $(throw "Please provide an instance on which to invoke the generic method"), 
    [string] $methodName = $(throw "Please provide a method name to invoke"), 
    [string[]] $typeParameters = $(throw "Please specify the type parameters"), 
    [object[]] $methodParameters = $(throw "Please specify the method parameters")
    ) 

## Determine if the types in $set1 match the types in $set2, replacing generic
## parameters in $set1 with the types in $genericTypes
function ParameterTypesMatch([type[]] $set1, [type[]] $set2, [type[]] $genericTypes)
{
    $typeReplacementIndex = 0
    $currentTypeIndex = 0

    ## Exit if the set lengths are different
    if($set1.Count -ne $set2.Count)
    {
        return $false
    }

    ## Go through each of the types in the first set
    foreach($type in $set1)
    {
        ## If it is a generic parameter, then replace it with a type from
        ## the $genericTypes list
        if($type.IsGenericParameter)
        {
            $type = $genericTypes[$typeReplacementIndex]
            $typeReplacementIndex++
        }

        ## Check that the current type (i.e.: the original type, or replacement
        ## generic type) matches the type from $set2
        if($type -ne $set2[$currentTypeIndex])
        {
            return $false
        }
        $currentTypeIndex++
    }

    return $true
}

## Convert the type parameters into actual types
[type[]] $typedParameters = $typeParameters

## Determine the type that we will call the generic method on. Initially, assume
## that it is actually a type itself.
$type = $instance

## If it is not, then it is a real object, and we can call its GetType() method
if($instance -isnot "Type")
{
    $type = $instance.GetType()
}

## Search for the method that:
##    - has the same name
##    - is public
##    - is a generic method
##    - has the same parameter types
foreach($method in $type.GetMethods())
{
    # Write-Host $method.Name
    if(($method.Name -eq $methodName) -and 
       ($method.IsPublic) -and 
       ($method.IsGenericMethod))
    {
        $parameterTypes = @($method.GetParameters() | % { $_.ParameterType })
        $methodParameterTypes = @($methodParameters | % { $_.GetType() })
        if(ParameterTypesMatch $parameterTypes $methodParameterTypes $typedParameters)
        {
            ## Create a closed representation of it
            $newMethod = $method.MakeGenericMethod($typedParameters)

            ## Invoke the method
            $newMethod.Invoke($instance, $methodParameters)

            return
        }
    }
}

## Return an error if we couldn't find that method
throw "Could not find method $methodName"

 

For some reason, a surprisingly common (albeit half-joking) remark about PowerShell is that it hasn't "arrived" until you can write obfuscated one-liners like you do in C or Perl. 

They may have missed Adam's obfuscated script (http://www.proudlyserving.com/archives/2005/11/obfuscated_mona.html,) or this email quoting / wrapping one-liner: http://www.leeholmes.com/blog/EmailQuotingAndWrappingIn59Bytes.aspx.

While I normally try to clarify and educate, here's another script to make your eyes burn:

$ofs="";
'"$(0'+
  '..(0'+
    'xa*['+
      'Math'+
        ']::R'+
          'ound'+
            '([Ma'+
              'th]:'+
                ':Pi/'+
                  '2,1)'+
                    ')|%{'+
                      '[cha'+
                        'r][i'+
                          'nt]"'+
                            '"$($'+
                              '("""'+
                                '"0$('+
                                   '1838'+
                                   '1589'+
                                '*726'+
                              '371*'+
                            '60)$'+
                          '(877'+
                        '7365'+
                      '981*'+
                    '263*'+
                  '360)'+
                '$(22'+
              '2330'+
            '793*'+
          '1442'+
        '99)$'+
      '(310'+
    '9*37'+
  ') ""'+        '"")[' + '($_*' + '3)..' + 
'($_*'+        '3+2)' + '])""' + ' })"'|iex

 

[Edit: Added a missing something-or-other]
 

Gerd recently posed a good question on the PowerShell blog:

I'm really surprised to read about another 3rd party book from a member of the PS team. To me PS is the most intriguing MS innovation for years and Bruce's book is really excellent and answered almost all my questions, but maybe I'm not the only one wondering why PS team members write that busy for the bookstore shelf, as long as the official MS documentation on PS is less than adequate. I would expect the essential information needed to master PS from MSDN and not from Manning or O'Reilly, sorry.
http://blogs.msdn.com/powershell/archive/2007/05/12/windows-powershell-the-definitive-guide.aspx#2618740

The answer is that we're trying to tackle this from all angles.

The question considers MSDN as a sole learning resource, but it's just one avenue for most people. PowerShell installs tutorial-style documentation with the product itself, and we are continually working to improve both user-focused and developer-focused help content. But that’s just the start.

What we write goes into the product help, MSDN, and Script Center. It also goes onto blogs, newsgroups, and books. There’s also Podcasts, screen casts, LiveMeetings! And, of course, there’s also training sessions, keynotes, conferences, interviews, and mailing lists.

Books are the preferred information delivery vehicle for a lot of people. They travel well, and have an aesthetic appeal that many desire. In fact, I've heard from several people that they have literally been waiting for the O'Reilly book — filling that need with quality content is important to us as well.

That said, if you see holes in the documentation, please let us know. We’re continually working to improve it, but we may miss areas.  We use the Microsoft Connect website (http://blogs.msdn.com/powershell/archive/2006/05/09/filing-bugs.aspx) to help gauge priority, so we would really appreciate your input (via document bugs and suggestions) there.

With the book getting close to completion, O'Reilly has now posted a "Rough Cut" version of the book here: http://www.oreilly.com/catalog/9780596528492/. The Rough Cuts program gives you early access to the majority of the book's content before publication, with the option for an online and print bundle for a discounted price.

[Edit: Thanks for all of the interest -- the review period is now closed.]

We're getting close to "content complete" of Windows PowerShell: The Definitive Guide. The next step is technical review, where we look for both high-level and low-level feedback on the content and structure.

Also, this is a book focused on administrators. While PowerShell uber-hackers are always appreciated, inexperience with PowerShell is extremely valuable, as well.

If you're interested in being a Technical Reviewer of the book, please let me know and I'll forward your information to O'Reilly. Feel free to leave a comment here, or click on the mail icon in the sidebar to the right.

To give a glimpse into the writing process behind my upcoming "Windows PowerShell - The Definitive Guide" (O'Reilly,) I'll occasionally post entries "as the author sees it." This entry illustrates how to discover registry settings for programs that do not otherwise support scripted automation.

Discover Registry Settings for Programs

Problem

You want to automate the configuration of a program, but that program does not document its registry configuration settings.

Solution

Use Sysinternals' Process Monitor to observe registry access by that program. Process Monitor is available from http://www.microsoft.com/technet/sysinternals/FileAndDisk/processmonitor.mspx.

Discussion

In an ideal world, all programs would fully support command-line administration and configuration through PowerShell cmdlets. Many programs do not, however, so the solution is to look through their documentation in the hope that they list the registry keys and properties that control their settings. While many programs document their registry configuration settings, many still do not.

Although these programs may not document their registry settings, you can usually observe their registry access activity to determine the registry paths they use. To illustrate this, we will use the Sysinternals' Process Monitor to discover PowerShell's execution policy configuration keys. Although PowerShell documents these keys and makes its automated configuration a breeze, it illustrates the general technique.

Launch and configure Process Monitor

Once you've downloaded Process Monitor, the first step is to filter its output to include only the program you are interested in. By default, Process Monitor logs almost all registry and file activity on the system.

First, launch Process Monitor, then press Ctrl-E (or click the magnifying glass icon) to temporarily prevent it from capturing any data. Next, press Ctrl-X (or click the white sheet with an eraser icon) to clear the extra information that it captured automatically. Finally, drag the target icon and drop it on top of the application in question. You can press Ctrl-L (or click the funnel icon) to see the filter that Process Monitor now applies to its output.

Figure 18-2. Process Monitor ready to capture

Prepare to manually set the configuration option

Next, prepare to manually set the program's configuration option. Usually, this means typing and clicking all of the property settings, but just not pressing OK or Apply. For this PowerShell example, type the Set-ExecutionPolicy command line, but do not press Enter.

Figure 18-3. Preparing to apply the configuration option

Tell Process Monitor to begin capturing information

Switch to the Process Monitor window, then press Ctrl-E (or click the magnifying glass icon.) Process Monitor now captures all registry access for the program in question.

Manually set the configuration option

Press OK, Apply, or whatever action it takes to actually complete the program's configuration. For the PowerShell example, this means pressing Enter.

Tell Process Monitor to stop capturing information

Switch again to the Process Monitor window, then press Ctrl-E (or click the magnifying glass icon.) Process Monitor now no longer captures the application's activity.

Review the capture logs for registry modification

The Process Monitor window now shows all registry keys that the application interacted with when it applied its configuration setting.

Press Ctrl-F (or click the binoculars icon), then search for RegSetValue. Process Monitor highlights the first modification to a registry key.

Figure 18-4. Process Monitor's registry access detail

Press Enter (or double-click the highlighted row) to see the details about this specific registry modification. In this example, we can see that PowerShell changed the value of the ExecutionPolicy property (under HKLM:\Software\Microsoft\PowerShell\1\ShellIds\Microsoft.PowerShell) to RemoteSigned. Press F3 to see the next entry that corresponds to a registry modification.

Automate these registry writes

Now that you know all registry writes that the application performed when it updated its settings, judgment and experimentation will help you determine which modifications actually represent this setting. Since PowerShell only performed one registry write (to a key that very obviously represents the execution policy,) the choice is pretty clear in this example.

Once you've discovered the registry keys, properties, and values that the application uses to store its configuration data, you can use the techniques discussed in @TODO Ref Modify or delete a registry key value to automate these configuration settings. For example:

One of the most common tasks when administering a system is working with its files and directories. This is true when you administer the computer at the command line, and is true when you write scripts to administer it automatically.

Fortunately, PowerShell makes scripting files and directories as easy as working at the command line — a point that many seasoned programmers and scripters often miss. A perfect example of this comes when you wrestle with limited disk space, and need to find the files taking up the most space.
A typical programmer might approach this task by writing functions to scan a specific directory of a system. For each file, he or she checks if the file is big enough to care about. If so, they add it to a list. For each directory in the original directory, they repeat this process (until there are no more directories to process.)

As the saying goes, though, "you can write C in any programming language." The habits and preconceptions you bring to a language often directly influence how open you are to advances in that language.

Being an administrative shell, PowerShell directly supports tasks such as "visiting all the files in a subdirectory," or "moving a file from one directory to another." That complicated programmer-oriented script turns into a one-liner:

Another example came up today in our internal mailing list with the question, "how do list all files NOT in a certain directory?" Another long recursive solution bubbled up in response, but it again turns into a one-liner:

Before diving into your favourite programmer's toolkit, check to see what PowerShell supports in that area. In many cases, it can handle it without requiring your programmer's bag of tricks.

(Edit: Changed 'Size' to 'Length' in the example)

One of the gestures that becomes very ingrained when working in a Unix shell is double-tap tab completion. When you press tab at a normal slow speed, the shell cycles through available tab completion possibilities. When you press tab quickly twice in a row, the shell displays all possible completions for your command all at once – and then lets you cycle through those possibilities.

That's not a feature that PowerShell directly supports, but it is possible to get a good approximation to it by customizing your own TabExpansion function. The following script demonstrates a TabExpansion function that illustrates a framework for this. It is terrible as a stand-alone tab completion function (since it only tab completes on filenames – and poorly, at that,) but demonstrates one approach to getting double-tap tab completion in PowerShell.

It comes it two parts:

1) A TabExpansion function, that populates the area just above your prompt with suggestions if you press 'Tab' twice quickly.

function TabExpansion([string] $line, [string] $lastword)
{
    ## Delay for a bit to see if they've pressed a key again
    Start-Sleep -m 200
    if($host.UI.RawUI.KeyAvailable)
    {
        ## Get the list of items to be returned in tab completion. This
        ## is just an example.
        $items = Get-ChildItem $lastWord

        ## Convert those items into a wide string format so that we can display
        ## it concisely
        $content = $items | Format-Wide | Out-String
        $contentLines = $content.Replace("`r","").Split("`n")
        $contentHeight = $contentLines.Length + 2
        $contentWidth = $host.UI.RawUI.BufferSize.Width

        ## If there are more than 100 items (the default in
        ## Unix shells,) it would be courteous to prompt the user to find out if
        ## they actually want to display all of the tab completion information.
        if($contentLines.Length -gt 100)
        {
            return
        }

        ## Create a buffer cell array to hold the string content we plan to
        ## put in the console buffer
        $savedCells = 
        $replacementCells = $host.UI.RawUI.NewBufferCellArray(
            $contentLines,$host.UI.RawUI.ForegroundColor,$host.UI.RawUI.BackgroundColor
            )

        ## Figure out where to put the new bits of buffer
        $coordinates = $host.UI.RawUI.CursorPosition
        $coordinates.Y -= $contentHeight
        $coordinates.X = 0

        ## Bail if we can't fit the replacement content above our prompt
        if($coordinates.Y -le 0)
        {
            return
        }

        ## Create the rectangle that determines which of the old buffer cells
        ## we want to save
        $rectangle = New-Object System.Management.Automation.Host.Rectangle
        $rectangle.Left = 0
        $rectangle.Right = $contentWidth
        $rectangle.Top = $coordinates.Y
        $rectangle.Bottom = $coordinates.Y + $contentHeight

        ## Save the old buffer contents (and their coordinates) into a global
        ## variable so that the next prompt can fix it
        ${GLOBAL:Lee.Holmes.SavedBufferContents} = $host.UI.RawUI.GetBufferContents($rectangle)
        ${GLOBAL:Lee.Holmes.SavedBufferCoordinates} = $coordinates

        ## Put our double-tap tab completion information in an area above the prompt
        $host.UI.RawUI.SetBufferContents($coordinates, $replacementCells)
    }
}

2) A few additional lines in your prompt function to clean up any double-tap output at the next prompt.

    ## Restore tab completion content
    if(Test-Path Variable:\Lee.Holmes.SavedBufferContents)
    {
        $host.UI.RawUI.SetBufferContents(
            ${GLOBAL:Lee.Holmes.SavedBufferCoordinates}, ${GLOBAL:Lee.Holmes.SavedBufferContents}
        )
        ${GLOBAL:Lee.Holmes.SavedBufferContents} = $null
        ${GLOBAL:Lee.Holmes.SavedBufferCoordinates} = $null
    }