PowerShell Cookbook

Search

Categories

 

On this page

The perils of BCC
Build Instructions for the DIY Cat Feeder
Interacting with SQL Databases in PowerShell: Invoke-SqlCommand
Managing INI files with PowerShell
Using PowerShell and PsExec to invoke expressions on remote computers
Removing Certificates from the Certificate Store
Cmdlets vs Functions
Secret SQL Escape Characters
Invoking Generic Methods on Non-Generic Classes in PowerShell
Obfuscated PowerShell

Archive

Blogroll

Disclaimer
I work for Microsoft.

The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.

RSS 2.0 | Atom 1.0 | CDF

Send mail to the author(s) E-mail

Total Posts: 220
This Year: 20
This Month: 0
This Week: 0
Comments: 533

Sign In

 Wednesday, October 24, 2007
Wednesday, October 24, 2007 9:36:53 PM (Pacific Daylight Time, UTC-07:00) ( )

When you’ve got to deal with as much information as everybody in a large company does, most people write Inbox rules to redirect DLs to their own sub-folders. They then group the folder “By Conversation,” which lets them quickly read the threads of interest. But most importantly, it lets them ignore the ones that do not.

 

If you don’t yet use these techniques to help you manage your own mail, you will love yourself for starting. I may follow up with a post in the future about that, but it’s a staple in email management in any large company.

 

When you BCC a distribution / discussion list, that mail break the inbox rules of everybody subscribed to the DL. That includes your boss, the person managing the sr. exec’s mailbox, and the person just about to jump off a cliff from email overload.

 

So aside from being rude (people set these rules up for a reason,) it doesn’t actually solve the problem you might think it does. Although it is nearly always done with the intent to minimize "noise," noise in a person's mailbox is much worse than noise on a distribution list.  Inbox rules are the single most effective means that we have to manage the massive stream of information that flows through the company.

 

 

BCC to "take Offline":

Please do not use BCC to take a thread offline.  Instead, a reply-all with "taking this offline" will suffice. The alias knows that the thread is being addressed, and further conversation is unlikely to follow. In a follow-up mail to the person, you might provide the information you planned to originally. For those that batch their reading of the alias, they probably don’t even care about this specific thread, and will skim right past it.

 

If the conversation continues (i.e.: despite the intention to take offline,) then it was clearly of interest to the DL. No amount of BCCing will prevent the conversation from ending on its own schedule.

 

When you've resolved the issue, people often appreciate it when you follow up with the main thread with the details of the resolution.

 

BCC to "Redirect to another Alias":

Please do not use BCC to redirect a thread.  Instead, a reply-all with "please take this up on the <more-appropriate-alias> alias." The alias knows that the thread is being addressed, and further conversation is unlikely to follow. For those that batch their reading of the alias, they probably don’t even care about this specific thread, and will skim right past it. Aaron Lerch recently wrote a cool macro to unfortunately automate exactly this scenario.

 

BCC to prevent "Reply All":

Please do not use BCC to prevent Reply-Alls.  There are very few situations when limiting “Reply All” makes sense on a distribution / discussion list. If a conversation starts or continues (i.e.: despite the intention to prevent Reply-All,) then it was clearly of interest to the DL. No amount of BCCing will prevent the conversation from ending on its own schedule.

 

A DL isn’t there to be people’s internal version of the LazyWeb — so I disagree with attempting to prevent Reply All. A thread will continue until it runs out of steam – in which case no amount of prevention will stop it. If it was a boring question in the first place, you don’t have to worry about a reply-all storm anyways.  DLs that are highly populated involuntarily (i.e.: organizational aliases) should have the send-to permissions restricted. But since techniques exist for managing mail to DLs, a Reply-All storm really doesn’t matter.

 

Case in point: An internal alias recently suffered a meltdown. With rules in place, my only comment was “weird, why do I have 3000 mails in that folder all of a sudden?”

 

In the very unlikely chance that you have a good reason to prevent a Reply-All, Scott Hanselman’s suggestion (or an Outlook NoReplyAll form) is probably the best bet.

 

 

BCC for unknown reasons:

Please do not use BCC unless you have a good reason to.  Instead, send a message saying, "Please reply directly to me," if that was your intent.

 

Comments [3] | | # 
 Monday, October 22, 2007
Tuesday, October 23, 2007 6:17:45 AM (Pacific Daylight Time, UTC-07:00) ( )

A few weeks ago, Design News ran an engineering-centric article on the DIY Cat Feeder that entertained my cats while I was on vacation last year. Everybody loves a good hack, but I've been surprised by the attention it received. If you've been wanting to know more about how it was built, the article provides much more detail.

As part of their article preparation, they asked for mechanical and schematic drawings, along with part numbers from anything in the Allied Electronics catalog that the cat feeder used. Since my main goal was to make something out of random junk laying around the house, I thought the question was a little funny – as the parts list ended up being:

Amt Part Description
1 Big box of Pepsi
1-2 Thin phone books
1 Sturdy cardboard box
1 Elastic band
5-6 Bamboo skewers
1 Sheet of cardboard
1 Bowl
1 Wireless-enabled computer
1 Very large box

A few weeks later, a photographer came by for a photo shoot, and the rest was history.
 
The hardest part of the process, by far, was coming up with the mechanical drawing. I first started drawing in Paint.NET, but got slightly annoyed by my inability to keep the lines consistent in a fake 3D drawing. I gave up after I realized that I wanted to show another perspective – and there would be no way I could ever keep the two consistent. After trying out half a dozen free CAD/CAM programs and 3D modeling applications, I finally settled on Google Sketchup. It ultimately solved my needs perfectly. If you're looking for a good, free, low fidelity 3D modeling program, definitely give it a try.

I finally got the chance to clarify something in the write-up, too. The Ubuntu aspect of this story got fully overblown, as Windows is just as capable of ejecting a CDROM tray :) Here is the PowerShell script that does it:

$mediaPlayer = New-Object -Com WMPlayer.OCX
$mediaPlayer.CDRomCollection.Item(0).eject()
Start-Sleep -Milliseconds 300
$mediaPlayer.cdromCollection.Item(0).eject()
Start-Sleep 1
$mediaPlayer.CDRomCollection.Item(0).eject()
Start-Sleep -Milliseconds 300

And to schedule it:

schtasks /create /tn "Feed Cats" /sc DAILY /st 07:00:00 `
    /tr "powershell c:\users\<user>\feedcats.ps1"
schtasks /create /tn "Feed Cats" /sc DAILY /st 17:45:00 `
    /tr "powershell c:\users\<user>\feedcats.ps1"

So now the spare computer has another credit in its already illustrious resume of Cat Feeder and Subversion CVS Source Control Server. That would be, of course, Internet Supermodel.

Comments [0] | | # 
 Thursday, October 18, 2007
Friday, October 19, 2007 6:40:08 AM (Pacific Daylight Time, UTC-07:00) ( )

Jeffrey McManus recently wrote about database queries with PowerShell – a small script that lets you query a SQL data store. This is really powerful. Rather than context switch into SQL Express (or TOAD, or your other favourite administration tool,) you can do what you need from PowerShell.

It goes even further, though. A lot of PowerShell's built-in commands have a set-oriented flavour: Where-Object, Select-Object, Group-Object, and Sort-Object. I also blogged about a set intersection script here: http://www.leeholmes.com/blog/CreatingSQLsJoinlikeFunctionalityInMSH.aspx. After spending a ton of time in Oracle databases during an internship at General Electric, I remember always wishing that DIR supported a WHERE clause. Well, with PowerShell, it does!

One thing Jeffrey's post doesn't fully highlight is that the .NET Framework can also return fully structured objects that represent the results of your query. If your SELECT query returns a recordset with Name and Address columns, you can get back objects with Name and Address properties. With that, you can slice and dice the results even further in the shell.

PS D:\Temp> Invoke-SqlCommand.ps1 -Sql "SELECT TOP 5 * FROM Orders" | Format-Table

    OrderID CustomerID   EmployeeID OrderDate   RequiredDat ShippedDate     ShipVia     Freight
                                                e
    ------- ----------   ---------- ---------   ----------- -----------     -------     -------
      10248 VINET                 5 7/4/1996... 8/1/1996... 7/16/199...           3       32.38
      10249 TOMSP                 6 7/5/1996... 8/16/199... 7/10/199...           1       11.61
      10250 HANAR                 4 7/8/1996... 8/5/1996... 7/12/199...           2       65.83
      10251 VICTE                 3 7/8/1996... 8/5/1996... 7/15/199...           1       41.34
      10252 SUPRD                 4 7/9/1996... 8/6/1996... 7/11/199...           2        51.3

The .NET Framework supports more than just SQL servers, though. It supports Access databases and Excel workbooks, too. Their connection strings are a black-art, so it is natural to wrap all of that magic in a script.

So natural that I include in the upcoming PowerShell Cookbook: Invoke-SqlCommand. (PS: Expect some good news about the book VERY soon.)

##############################################################################
##
## Invoke-SqlCommand.ps1
##
## From Windows PowerShell Cookbook (O'Reilly)
## by Lee Holmes (http://www.leeholmes.com/guide)
##
## Return the results of a SQL query or operation
##
## ie:
##
##    ## Use Windows authentication
##    Invoke-SqlCommand.ps1 -Sql "SELECT TOP 10 * FROM Orders"
##
##    ## Use SQL Authentication
##    $cred = Get-Credential
##    Invoke-SqlCommand.ps1 -Sql "SELECT TOP 10 * FROM Orders" -Cred $cred
##
##    ## Perform an update
##    $server = "MYSERVER"
##    $database = "Master"
##    $sql = "UPDATE Orders SET EmployeeID = 6 WHERE OrderID = 10248"
##    Invoke-SqlCommand $server $database $sql
##
##    $sql = "EXEC SalesByCategory 'Beverages'"
##    Invoke-SqlCommand -Sql $sql
##
##    ## Access an access database
##    Invoke-SqlCommand (Resolve-Path access_test.mdb) -Sql "SELECT * FROM Users"
##    
##    ## Access an excel file
##    Invoke-SqlCommand (Resolve-Path xls_test.xls) -Sql 'SELECT * FROM [Sheet1$]'
##
##############################################################################

param(
    [string] $dataSource = ".\SQLEXPRESS",
    [string] $database = "Northwind",      
    [string] $sqlCommand = $(throw "Please specify a query."),
    [System.Management.Automation.PsCredential] $credential
  )


## Prepare the authentication information. By default, we pick
## Windows authentication
$authentication = "Integrated Security=SSPI;"

## If the user supplies a credential, then they want SQL
## authentication
if($credential)
{
    $plainCred = $credential.GetNetworkCredential()
    $authentication = 
        ("uid={0};pwd={1};" -f $plainCred.Username,$plainCred.Password)
}

## Prepare the connection string out of the information they
## provide
$connectionString = "Provider=sqloledb; " +
                    "Data Source=$dataSource; " +
                    "Initial Catalog=$database; " +
                    "$authentication; "

## If they specify an Access database or Excel file as the connection
## source, modify the connection string to connect to that data source
if($dataSource -match '\.xls$|\.mdb$')
{
    $connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=$dataSource; "

    if($dataSource -match '\.xls$')
    {
        $connectionString += 'Extended Properties="Excel 8.0;"; '

        ## Generate an error if they didn't specify the sheet name properly
        if($sqlCommand -notmatch '\[.+\$\]')
        {
            $error = 'Sheet names should be surrounded by square brackets, and ' +
                       'have a dollar sign at the end: [Sheet1$]'
            Write-Error $error
            return
        }
    }
}

## Connect to the data source and open it
$connection = New-Object System.Data.OleDb.OleDbConnection $connectionString
$command = New-Object System.Data.OleDb.OleDbCommand $sqlCommand,$connection
$connection.Open()

## Fetch the results, and close the connection
$adapter = New-Object System.Data.OleDb.OleDbDataAdapter $command
$dataset = New-Object System.Data.DataSet
[void] $adapter.Fill($dataSet)
$connection.Close()

## Return all of the rows from their query
$dataSet.Tables | Select-Object -Expand Rows


 

Comments [4] | | # 
 Tuesday, October 02, 2007
Tuesday, October 02, 2007 6:24:39 PM (Pacific Daylight Time, UTC-07:00) ( )

The question came up on the newsgroup a few days ago on how to work with INI files from PowerShell.

A lot of great answers came up (using text parsing of the INI files,) but the Windows API actually supports reading and writing of INI file entries directly through its GetPrivateProfileString and WritePrivateProfileString functions. PowerShell doesn’t support P/Invoke to the Win32 API directly, but the Invoke-Win32 script given here does: http://www.leeholmes.com/blog/GetTheOwnerOfAProcessInPowerShellPInvokeAndRefOutParameters.aspx.

Since it’s reusable, that code sits better as a script of its own, named Invoke-WindowsApi (below.)

##############################################################################
##
## Invoke-WindowsApi.ps1
##
## From PowerShell Cookbook (O'Reilly)
## by Lee Holmes (http://www.leeholmes.com/guide)
##
## Invoke a native Windows API call that takes and returns simple data types.
##
## ie:
##
## ## Prepare the parameter types and parameters for the 
## CreateHardLink function
## $parameterTypes = [string], [string], [IntPtr]
## $parameters = [string] $filename, [string] $existingFilename, [IntPtr]::Zero
## 
## ## Call the CreateHardLink method in the Kernel32 DLL
## $result = Invoke-WindowsApi "kernel32" ([bool]) "CreateHardLink" `
##     $parameterTypes $parameters
##
##############################################################################

param(
    [string] $dllName
    [Type$returnType
    [string] $methodName,
    [Type[]] $parameterTypes,
    [Object[]] $parameters
    )

## Begin to build the dynamic assembly
$domain = [AppDomain]::CurrentDomain
$name = New-Object Reflection.AssemblyName 'PInvokeAssembly'
$assembly = $domain.DefineDynamicAssembly($name'Run')
$module = $assembly.DefineDynamicModule('PInvokeModule')
$type = $module.DefineType('PInvokeType'"Public,BeforeFieldInit")

## Go through all of the parameters passed to us.  As we do this,
## we clone the user's inputs into another array that we will use for
## the P/Invoke call.  
$inputParameters = @()
$refParameters = @()

for($counter = 1$counter -le $parameterTypes.Length; $counter++)
{
   ## If an item is a PSReference, then the user 
   ## wants an [out] parameter.
   if($parameterTypes[$counter - 1] -eq [Ref])
   {
      ## Remember which parameters are used for [Out] parameters
      $refParameters += $counter

      ## On the cloned array, we replace the PSReference type with the 
      ## .Net reference type that represents the value of the PSReference, 
      ## and the value with the value held by the PSReference.
      $parameterTypes[$counter - 1] = 
         $parameters[$counter - 1].Value.GetType().MakeByRefType()
      $inputParameters += $parameters[$counter - 1].Value
   }
   else
   {
      ## Otherwise, just add their actual parameter to the
      ## input array.
      $inputParameters += $parameters[$counter - 1]
   }
}

## Define the actual P/Invoke method, adding the [Out]
## attribute for any parameters that were originally [Ref] 
## parameters.
$method = $type.DefineMethod($methodName'Public,HideBySig,Static,PinvokeImpl',
    $returnType$parameterTypes)
foreach($refParameter in $refParameters)
{
   [void] $method.DefineParameter($refParameter"Out"$null)
}

## Apply the P/Invoke constructor
$ctor = [Runtime.InteropServices.DllImportAttribute].GetConstructor([string])
$attr = New-Object Reflection.Emit.CustomAttributeBuilder $ctor$dllName
$method.SetCustomAttribute($attr)

## Create the temporary type, and invoke the method.
$realType = $type.CreateType()

$realType.InvokeMember($methodName'Public,Static,InvokeMethod'$null$null
    $inputParameters)

## Finally, go through all of the reference parameters, and update the
## values of the PSReference objects that the user passed in.
foreach($refParameter in $refParameters)
{
   $parameters[$refParameter - 1].Value = $inputParameters[$refParameter - 1]
}

 

Using this script, you can easily wrap these APIs in a more friendly PowerShell scripts.

##############################################################################
##
## Get-PrivateProfileString.ps1
##
## Get an entry from an INI file.
##
## ie:
##
##  PS >Get-PrivateProfileString.ps1 C:\winnt\system32\ntfrsrep.ini text DEV_CTR_24_009_HELP
##
##############################################################################

param(
    $file,
    $category,
    $key)

## Prepare the parameter types and parameter values for the Invoke-WindowsApi script
$returnValue = New-Object System.Text.StringBuilder 500
$parameterTypes = [string], [string], [string], [System.Text.StringBuilder], [int], [string]
$parameters = [string] $category, [string] $key, [string] ""
   [System.Text.StringBuilder] $returnValue, [int] $returnValue.Capacity, [string] $file

## Invoke the API
[void] (Invoke-WindowsApi "kernel32.dll" ([UInt32]) "GetPrivateProfileString" `
   $parameterTypes $parameters)

## And return the results
$returnValue.ToString()

and

##############################################################################
##
## Set-PrivateProfileString.ps1
##
## Set an entry from an INI file.
##
## ie:
##
##  PS >copy C:\winnt\system32\ntfrsrep.ini c:\temp\
##  PS >Set-PrivateProfileString.ps1 C:\temp\ntfrsrep.ini text `
##  >> DEV_CTR_24_009_HELP "New Value"
##  >>
##  PS >Get-PrivateProfileString.ps1 C:\temp\ntfrsrep.ini text DEV_CTR_24_009_HELP
##  New Value
##  PS >Set-PrivateProfileString.ps1 C:\temp\ntfrsrep.ini NEW_SECTION `
##  >> NewItem "Entirely New Value"
##  >>
##  PS >Get-PrivateProfileString.ps1 C:\temp\ntfrsrep.ini NEW_SECTION NewItem
##  Entirely New Value
##
##############################################################################

param(
    $file,
    $category,
    $key,
    $value)

## Prepare the parameter types and parameter values for the Invoke-WindowsApi script
$parameterTypes = [string], [string], [string], [string]
$parameters = [string] $category, [string] $key, [string] $value, [string] $file

## Invoke the API
[void] (Invoke-WindowsApi "kernel32.dll" ([UInt32]) "WritePrivateProfileString" $parameterTypes $parameters)

Comments [5] | | # 
Tuesday, October 02, 2007 4:02:52 PM (Pacific Daylight Time, UTC-07:00) ( )

While eagerly awaiting PowerShell’s upcoming remoting functionality, many people turn to Sysinternals’ PsExec tool to build their own version. However, PowerShell seems to hang when called via PsExec on the remote machine. This has come up on the SysInternal forum (http://forum.sysinternals.com/forum_posts.asp?TID=10823) among other places, and is caused by the same issue outlined here: http://www.leeholmes.com/blog/UsingMshexeInteractivelyFromWithinOtherPrograms.aspx.

 

To work around this problem, you can give some input to the Powershell process. But to give it input, you need to use cmd.exe:

 

psexec \\server cmd /c "echo . | powershell dir 'c:\program files'"

 

Now, working around quote encoding and two levels of escape characters (cmd.exe and PowerShell) can be quite painful when crafting the PowerShell command this way. For that, you can use the –EncodedCommand parameter, which accepts a Base64-encoded version of your command.

 

$expression = "dir 'c:\program files'"

$commandBytes = [System.Text.Encoding]::Unicode.GetBytes($expression)

$encodedCommand = [Convert]::ToBase64String($commandBytes)

psexec \\server cmd /c "echo . | powershell -EncodedCommand $encodedCommand"

 

And to make it even more PowerShelly, the –OutputFormat of XML lets you get back an XML representation of your command’s output. On your local system, PowerShell converts your output back to deserialized objects. From there, you can continue to manipulate the output with the object-oriented goodness you’ve come to expect of us J In the example below, the server processes the Where-Object query, but the client sorts the result on Handles.

 

PS >$command = { Get-Process | Where-Object { $_.Handles -gt 1000 } }

PS >Invoke-RemoteExpression \\LEE-DESK $command | Sort Handles

 

Handles  NPM(K)    PM(K)      WS(K) VM(M)   CPU(s)     Id ProcessName

-------  ------    -----      ----- -----   ------     -- -----------

   1025       8     3780       3772    32   134.42    848 csrss

   1306      37    50364      64160   322   409.23   4012 OUTLOOK

   1813      39    54764      36360   321   340.45   1452 iTunes

   2316     273    29168      41164   218   134.09   1244 svchost

 

Here’s a script that automates all of this for you:

 

Program: Start a Process on a Remote Machine

Example 21-4 lets you invoke PowerShell expressions on remote machines. It uses PsExec (from http://www.microsoft.com/technet/sysinternals/utilities/psexec.mspx) to support the actual remote command execution.

This script offers more power than just remote command execution, however. As Example 21-3 demonstrates, it leverages PowerShell’s capability to import and export strongly structured data, so you can work with the command output using many of the same techniques you use to work with command output on the local system. Example 21-3 demonstrates this power by filtering command output on the remote system but sorting it on the local system.

Example 21-3. Invoking a PowerShell expression on a remote machine

PS >$command = { Get-Process | Where-Object { $_.Handles -gt 1000 } }

PS >Invoke-RemoteExpression \\LEE-DESK $command | Sort Handles

 

Handles  NPM(K)    PM(K)      WS(K) VM(M)   CPU(s)     Id ProcessName

-------  ------    -----      ----- -----   ------     -- -----------

   1025       8     3780       3772    32   134.42    848 csrss

   1306      37    50364      64160   322   409.23   4012 OUTLOOK

   1813      39    54764      36360   321   340.45   1452 iTunes

   2316     273    29168      41164   218   134.09   1244 svchost

Since this strongly structured data comes from objects on another system, PowerShell does not regenerate the functionality of those objects (except in rare cases). For more information about importing and exporting structured data, see “Easily Import and Export Your Structured Data.”

Example 21-4. Invoke-RemoteExpression.ps1

##############################################################################

##

## Invoke-RemoteExpression.ps1

##

## Invoke a PowerShell expression on a remote machine. Requires PsExec from

## http://www.microsoft.com/technet/sysinternals/utilities/psexec.mspx

##

## ie:

##

##  PS >Invoke-RemoteExpression \\LEE-DESK { Get-Process }

##  PS >(Invoke-RemoteExpression \\LEE-DESK { Get-Date }).AddDays(1)

##  PS >Invoke-RemoteExpression \\LEE-DESK { Get-Process } | Sort Handles

##

##############################################################################

 

param(

  $computer = "\\$ENV:ComputerName",

  [ScriptBlock] $expression = $(throw "Please specify an expression to invoke."),

  [switch] $noProfile

  )

 

## Prepare the command line for PsExec. We use the XML output encoding so

## that PowerShell can convert the output back into structured objects.

$commandLine = "echo . | powershell -Output XML "

 

if($noProfile)

{

    $commandLine += "-NoProfile "

}

 

## Convert the command into an encoded command for PowerShell

$commandBytes = [System.Text.Encoding]::Unicode.GetBytes($expression)

$encodedCommand = [Convert]::ToBase64String($commandBytes)

$commandLine += "-EncodedCommand $encodedCommand"

 

## Collect the output and error output

$errorOutput = [IO.Path]::GetTempFileName()

$output = psexec /acceptEula $computer cmd /c $commandLine 2>$errorOutput

 

## Check for any errors

$errorContent = Get-Content $errorOutput

Remove-Item $errorOutput

if($errorContent -match "Access is denied")

{

    $OFS = "`n"

    $errorMessage = "Could not execute remote expression. "

    $errorMessage += "Ensure that your account has administrative " +

        "privileges on the target machine.`n"

    $errorMessage += ($errorContent -match "psexec.exe :")

 

    Write-Error $errorMessage

}

 

## Return the output to the user

$output

 

 

Edit: Thanks to Otto's suggestion, Added the /acceptEula switch to PsExec to accept the EULA automatically. 

Comments [7] | | # 
 Thursday, August 23, 2007
Thursday, August 23, 2007 2:07:47 PM (Pacific Daylight Time, UTC-07:00) ( )

This has come up twice in as many days... how do you remove certificates from the certificate store in PowerShell?

The certificate provider is ultimately a read-only view of your certificates. It does help you retrieve certificates, however, which is an important step in ultimately removing one from a store.
 
To remove one, you'll need to use the .NET APIs:

[cert:\CurrentUser\TrustedPublisher]
PS:200 > dir

    Directory: Microsoft.PowerShell.Security\Certificate::CurrentUser\TrustedPublisher

Thumbprint                                Subject
----------                                -------
FD48FAA9281A657DBD089B5A008FAFE61D3B32FD  CN=PowerShell User
A25800BB7577F5854B3823B82228D94140D0244E  CN=Microsoft Corporation, O=Microsoft Corporation, L=Redmond, S=Washington...
564E01066387F26C912010D06BD78D3CF1E845AB  CN=Microsoft Corporation, O=Microsoft Corporation, L=Redmond, S=Washington...
 
[cert:\CurrentUser\TrustedPublisher]
PS:201 > $cert = @(dir)[0]
[cert:\CurrentUser\TrustedPublisher]
PS:202 > $store = New-Object System.Security.Cryptography.X509Certificates.X509Store "TrustedPublisher","CurrentUser"
Suggestion: An alias for New-Object is new
[cert:\CurrentUser\TrustedPublisher]
PS:203 > $store.Open("ReadWrite")
[cert:\CurrentUser\TrustedPublisher]
PS:204 > $store.Remove($cert)
[cert:\CurrentUser\TrustedPublisher]
PS:205 > $store.Close()
[cert:\CurrentUser\TrustedPublisher]
PS:206 > dir

    Directory: Microsoft.PowerShell.Security\Certificate::CurrentUser\TrustedPublisher

Thumbprint                                Subject
----------                                -------
A25800BB7577F5854B3823B82228D94140D0244E  CN=Microsoft Corporation, O=Microsoft Corporation, L=Redmond, S=Washington...
564E01066387F26C912010D06BD78D3CF1E845AB  CN=Microsoft Corporation, O=Microsoft Corporation, L=Redmond, S=Washington...
 

Comments [2] | | # 
 Tuesday, July 24, 2007
Tuesday, July 24, 2007 6:02:06 PM (Pacific Daylight Time, UTC-07:00) ( )

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.

Comments [1] | | # 
 Friday, July 20, 2007
Friday, July 20, 2007 8:40:48 PM (Pacific Daylight Time, UTC-07:00) ( )

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

Comments [0] | | # 
 Monday, June 18, 2007
Tuesday, June 19, 2007 3:39:37 AM (Pacific Daylight Time, UTC-07:00) ( )

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