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!

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.

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:

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...

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.

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.

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

[Edit: this "just works" in PowerShell V3]

PS C:\Users\Lee> Add-Type -Path C:\temp\generic.cs
PS C:\Users\Lee> $r = New-Object NonGenericClass
PS C:\Users\Lee> $r.GenericMethod("Hello", "Hello")
Hello Multi Generic World: True

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:  

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/2006/10/25/email-quoting-and-wrapping-in-59-bytes/. 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

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.