I have a problem with my laptop where I connect to my home network and my work network, and my school network … and I have to use different proxy settings for Windows internet connection settings (which are used by virtually all .Net apps, including PowerShell) by default…

Of course, there are a lot of different apps that can manage those settings (and more) automatically using Windows NLA to trigger. However, I have an extra special requirement: when I plug in at work or at home, I want to run Synergy on the laptop as a client … but the name and ip of the host is different, of course — so I need to stop synergy and restart it with a different server name … something I can easily accomplish from a script or batch file … but changing proxies is a bit trickier.

I thought it would a simple call to a .Net library method, but after much searching, the only way of setting or retrieving Internet options seems to be through the old WinInet API, using InternetSetOptions (or InternetQueryOption to read them). Of course, to use these, you have to map the INTERNET_PER_CONN_OPTION_LIST and INTERNET_PER_CONN_OPTION structures to C#, and do whole lot of marshalling and manual memory management.

I did find some C# mappings for the Option structure on PInvoke.net but not for the others. I actually started writing them all by hand and then found a “recipe” in the (see the examples link) C# 3.0 Cookbook for retrieving Internet settings which had versions of all the structures I needed, as well as definitions for the flags I wanted. So… without further ado, let me share the code with you … (more…)

My “getting started” series ran out of steam a bit partly because I didn’t get much feedback on them — maybe you’re not interested, or maybe it wasn’t easy enough, or was just too confusing. In any case, I want to put up at least this one last post to suggest that you get the PowerShell Code Repository set up, and to show you the final version of my profile script and how it loads the various pieces it needs, and then I’ll send you on your way.

Once you’ve got your PowerShell all installed and have set up your first profile to auto-load … you’re going to want some scripts (well, maybe you’ll want to learn more about how to use PowerShell, but go with me on this)!

One of the best places to look for scripts is the PowerShell Code Repository, and although you can browse and search on the website, you can also do it using the PoshCode module (or the version 1 compatible script). These scripts include a Get-PoshCode cmdlet which you can use with search terms to get a list of scripts and cmdlets back, or with numeric IDs to download scripts (you’ll see what I mean later on, for now, go ahead and grab the appropriate version of that script).

I’m going to assume you put it into your AutoModules folder. If you grabbed the module, it should be saved to WindowsPowerShell\AutoModules\PoshCode\PoshCode.psm1 otherwise, to WindowsPowerShell\AutoModules\PoshCode.ps1 … but you may have run into a minor problem if you load the .ps1 version ). Both the module and the script are signed, but they are signed by my self-issued code signing certificate which your computer almost certainly doesn’t trust… You can use the signature to verify that the file hasn’t been modified since I signed it, but that’s about all (and even that’s a bit of a trick). To actually use the script (module), you’ll need to sign the script yourself (see the steps and how to get a certificate in part 2).

If you’re on CTP2, this would be a good time to get my Authenticode script module to help with signing, and to learn a little about those PoshCode cmdlets … (more…)

Man! See if I ever mention these things again. They’ve changed the date for the UK PowerShell user group meeting from July 29th to July 31st …

But Marco Shaw will still be presenting about the /n software NetCmdlets. These are the (free for personal non-commercial use) cmdlets which let you tackle everything from IMAP to SSH from PowerShell …

Anyway, if you’ve been curious about them, the presentation will be at 7pm UK (UTC) time and 2pm New York time … and will be available via Live Meeting — I attended the last of the UK user group meetings via live meeting and they did a fairly good job of making the presentations accessible to us virtually, and it was definitely worth the effort.

Have you ever written a program or a script where there’s a setting which has to be set by the user the first time the app is run? With applications, we just show the configuration UI when it’s run for the first time, but how about with PowerShell scripts? Well, in PowerShell 2 (CTP2), there is a way…

Back Story (or, An Example)

I’ve written a Authenticode.psm1 script module for PowerShell which is primarily a wrapper which causes the Set-AuthenticodeSignature and Get-AuthenticodeSignature cmdlets to work the way I think they should. Basically, they take files from the pipeline without complaining, and if they accidentally hit a folder, they throw an error but keep on processing other pipeline elements (the built-in cmdlets throw an exception and die in such an ugly way that they stop the whole pipeline).

Anyway, I also added a couple of features: a Test-Signature filter which returns true if a cert has a valid hash-match — even if the certificate can’t be traced to a valid CA(Certificate Authority), and a Select-Signed filter which has about 7 different switches to let you pick out scripts and files which are signed by you, by other, signed but broken, signed by trusted certs, signed by untrusted certs, etc. I use that filter to re-sign the scripts in my AutoModules directory when I’ve touched a bunch of files, so I can re-sign just the ones that I’ve previously signed, but which are currently broken.

Of course, on my computer, I don’t want to have to specify the certificate path every time, so in my wrapper scripts, I created a $DefaultCertificate variable, which I need each user to to set when they take my script to their computer. In the previous version I just set it as a variable, and considered that good enough, but it occurred to me that if people forgot to change it, they would see scripts signed by me as “valid” — which was not my intention.

Localizable Scripts

In version 2 (CTP2), PowerShell introduced the concept of a subset of the languages called the PowerShell Data language. Basically, it’s just like PowerShell script, except you’re only allowed to use a couple of cmdlets, and non-expanding variables. Essentially, this means you can set some variables to predefined values, and not a whole lot else.

To make this data language localizable, there are a couple of data cmdlets: ConvertFrom-StringData and Import-LocalizedData … I’ll leave it up to you to read the help on those (use “Get-Help” in the PowerShell command line) and also the About_Script_Internationalization and About_Data_Section ...

The simplest way to explain it is this: if your script uses Import-LocalizedData, PowerShell attempts to load a data file with the same name as your script, from a subfolder named the same as the output of (Get-UICulture).Name (for US English, that is “en-US”), or (Get-UICulture).Parent.Name (again, for US English, that is “en”) ... if no file can be found, then it writes an error explaining where the file should have been located (but doesn’t throw an exception).

We can use this to create translatable text messages in our scripts, but we can also abuse it to require a settings file to be created, although we’ll need to explain to the users what should go into that file.

Wrapping it up

In my Authenticode script module, I ended up forcing Import-LocalizedData to throw an error by setting it’s ErrorAction to “Stop” and then trapping the error to write a more verbose description of what the setting(s) are that the script needs. I’m pretty sure there’s a better way to do this using the PSD1 data files, I just don’t know what it is.

As a postscript, I should mention: Although I only had a single value I wanted in the settings file, it’s simple to put more, just read those help files I mentioned earlier and you’ll see a lot of examples of it. It’s basically a matter of using the ConvertFrom-StringData cmdlet with a sort of hashtable …

I’ve been having problems with the search functionality on the PoshCOde repository, and I just thought I’d throw this up here because I just now solved the biggest problem: ranking. Up until now, the results have not been returned in order of relevance — this is because the search works using MySQL’s FULLTEXT BOOLEAN search, which doesn’t return in relevance order, nor does it return an extra ‘score’ column.

I’ve fixed that, and weighted the search so that words in the title count more than words in the code by creating a relevance column by hand:

Incidentally, the FULLTEXT index means that words shorter than 4 characters don’t count (I’m going to try to get this changed, but it’s an option for MySQL, so it has to be changed in the config file) in the meantime you can search for words using the wildcard character, like: SQL* and it sort-of works. The PoshCode cmdlet actually was adding *‘s to the query (although I’ve just decided that’s not a good idea, because it means that queries from the cmdlet appear to have different results than queries on the website.

MySQL’s FULLTEXT BOOLEAN search has all sorts of features (and limitations): there is a stopword list, maximum and minimum word lengths, and all sorts of operators for setting word precedence or negating words, or weighting them negatively … to REQUIRE that a word be present, it must have a + in front, and in order to mark a word as more important, you have to put > in front, not just put it first… I’ve been thinking about trying to apply a few of those tricks myself (eg: put * on words under four characters, and put > on the first 30% of words and < on the last 30% to try to simulate weighting them …) but my original feeling was that the search is more powerful if you just know that it’s a fulltext boolean search and can write your queries accordingly.

If anyone has any ideas for how to improve search in MySQL … or opinions on whether I should try to apply boolean operators to queries which don’t already have them … please let me know.

So I’ve been asked to add a feature to one of the apps that I nominally took over when my former manager left … they want a management pane where certain authorized super users (to be decided later) can add data to the main lookup tables, and must be able to do clean up by deleting data which has been entered erroneously … including cleaning up any references to the now missing data.

I’m currently trying to figure out what database tables I need to be concerned with, and I just have to vent, because this system is the worst mess I’ve ever seen.

• There’s one database supporting five or more applications…
• There are 87 tables (with names like tblAQ_DcSs, tblAQ_SwNw, tblSFM, and tblSW_PWSOS, tblTestCaseTestLayout )
• There are three duplicate user tables: tblPeopleLookup, tblUser, and tblUser3 — tblUser2 is a view onto an external user database which is what is supposedly being used … and apparently, tblPeopleLookup is some sort of mapping from tblUser2 to tblUser3 … and tblUser1 is the original user table. I don’t know why these are all still here — I can only hope none of these others are still being used.
• There are 144 stored procedures (with names like sp_Fix, sp_Fix2, sp_Fix3, spLeftToTestMulti, spLeftToTestMulti2, spLeftToTestMulti3, sp_Whatever, and the awesome spTestCaseTestLayoutTestsUpdate, spTestCaseTestLayoutTestsSelect, etc.)
• There are no Foreign Keys. Yeah. None.

Technically, there are lots of foreign keys — it’s just that none of them are declared as such, so there’s no referential integrity (did I mention that there’s an access database floating around out there with linked tables and a hard-coded login which the end-users pass around to each other so they can insert data into some of the tables by hand because the original developers didn’t get around to writing this management app that I’ve been asked to write now?)

You can tell that some of the columns should be Foreign Keys, because obviously a column in a “tblReq_Tag” table named “Feature_ID” must be an external lookup of some sort … but there’s 86 other tables … and at least two of them have Primary Keys called “Feature_ID” ...

So, I’m spending a lot of time searching the source code and the 144 stored procedures … An astonishing number of these stored procedures involve cursors and multiple nested case statements. I just picked one at random which I thought sounded simple: spEnterGroupResults ... it’s about 150 lines of SQL, and it uses a single cursor variable “crsUnit” which it redefines three separate times onto three different queries which it iterates over. Each of these queries involves joins onto nested subqueries, and I count myself lucky because the tricky part is actually enclosed in a transaction, and at least this one isn’t doing all of that just to dynamically generate a further SQL query to execute.

So yeah, I’m literally looking through source code to try to understand the database design. The problem is that there are more than five different applications, each using slightly different technologies.

• One of them which has never been migrated from classic ASP ... with the business logic written entirely in Javascript, and the data handling performed entirely by sending huge XML files back and forth to a “do all” webservice.
• One of them was written in VB.NET in VS 2003, and has never been upgraded.
• The rest are in C# — with most in VS 2005, and at least one in VS 2008 and C# 3.0 — some are Asp.Net, some are rich client …

The tables I’m most concerned with right now (for this app) have some ahem ... impressive design decisions of their own. Of the 8 tables that I’m looking at directly (I think these are the only ones I need to modify as part of this app), five of them have multi-column primary keys that involve more than half the columns in the table, including columns which are, in fact, unconstrained foreign keys. And there are so far 5 foreign key looking columns which I haven’t been able to find the primary key column for …

Edit: Oh yeah, and half of these tables have columns like [Enabled] [char](1) NULL … That’s a boolean value folks, stored in the database as a y or a n … and it’s nullable even though a null (or any value other than y or n, really) will most likely blow up some code somewhere. And no, there’s no script constraint or trigger to ensure that this doesn’t happen (I checked). For extra fun, the other half of the tables use ‘bit’ columns for things like this — because they were written after I started working with this team (on a different project) and happened across one of these char columns during our one and only code review ever and wondered aloud why we needed to pretend it was still 1992. Why they just switched, without changing the others, I’ll never know…

Well, after some extensive hacking this long weekend, I’m happy to announce the opening of the refurbished (and renamed) PowerShell Code repository at it’s new domain: PoshCode.org with a few new features and a slightly overhauled look.

I expect that soon we’ll be replacing the back end with something which has a few enhancements over this version (at least in terms of tracking the many versions of a particular code contribution), but even without that, we hope that the current feature set will be enough to push the repository into mainstream usage as the primary place to share PowerShell code.

The new PoshCode site is still based on the old pastebin GPL source code, but I’ve hacked it up so much you’ll hardly recognize it. Sadly, I haven’t kept it as generically clean as I could have, so although I’m making the source code available, you’ll have to clean up the “layout” files if you want to run your own repository.

New Features

The most obvious new feature is that the front page now prominently features the most recent contributions. This list is filtered to only show the last in a series of contributions (so if you make a new contribution based on a previous one, the front page won’t be swamped with iterations of a single script the way the old one was).

The best new feature is that you can now browse by author (currently, you simply get a list of their 25 most recent contributions, in the future we’ll add some paging functionality as the need arises).

Finally, I’ve changed the description field (which previously allowed any html) to use only a restricted subset of Textile. I’ll put up some documentation about this later, but for now: you can make paragraphs by leaving a blank line, and can use asterisks for bold and underscores for italics … and starting each line with an asterisk or a pound sign will get you bulleted or numbered lists. Any links will be rel=“nofollow” and you can’t use raw HTML at all.

Earlier Enhancements

In addition to these new features, there are a few other enhancements that I added on to the original code: a couple of extra fields, permanent storage, search functionality, and a recent contributions RSS feed.

Incidentally, the search functionality allows you to search the author, description, title, and main content fields using MySQL’s full-text indexing search, and is accessible via the new PoshCode script functions (available as both a PowerShell v2 CTP-compatible PoshCode module and a v1 PoshCode script).

There’s been a lot of discussion on the #PowerShell IRC channel about code signing lately — mostly in response to my earlier posts (which I won’t link to here, because I should probably retract some of it after the discussions we’ve had). The discussion mostly revolves around how code signing applies to the PowerShell script repository and users’ ability to trust scripts that they download.

In order to keep the conversation going, and hopefully, include some of the Microsoft MVPs and PowerShell developers that don’t participate in the IRC channel at all, I’m going to post a summary of the conclusions we’ve drawn so far, and then outline a proposal for the repository.

In case you don’t know, PowerShell implements code-signing for scripts using x509 Certificates (basically, SSL certs with “Extended” use properties), but the signatures are just embedded in the script as a comment, much like PGP signatures in email. Depending on your settings, the engine will actually check the signature on a script before executing it, and refuse to execute it if the signature isn’t valid, for instance, if the script has been modified, or the certificate used to sign it can’t be traced to a trusted Certificate Authority.

PowerShell code-signing is inconvenient, and expensive. Scripts are only Valid if they are signed, unmodified, and the certificate traces back to a CA(Certificate Authority) that is in your Trusted Roots store (by default, basically, Microsoft’s, VeriSign, Thawte, and Comodo).

You can create your own “test” certificate (that is, one that you sign yourself), but scripts signed by that certificate will only work on that computer. This system works fine for large companies which typically have either a subscription for purchasing certificates, or their own in-house trusted CA, but not for small private developers or open source script sharing communities without financial motivation.

Because of this, to take advantage of PowerShell’s code-signing on your computer (that is, to set your ExecutionPolicy to “AllSigned” or “RemoteSigned”) you must do one of these things:

1. Make sure that all script authors rent and use code-signing certs which cost more than $150 a year.
2. Add a non-commercial root CA certificate (like CACert) to (all of) your computer(s), and offer your authors the option of going through the process of getting one of those instead.
3. Create a “community” CA … and try to figure out how to make sure the certificate is secure enough to convince people to use it (yeah, ok, this was partly my idea, and it’s really a bad idea).
4. Generate your own code-signing certificate (which can be self-signed) and just sign every script that you trust.

So far, it seems like that last option is the only one that’s going to fly, because although some developers are willing to buy certificates to sign software they’re going to sell … most scripters and sys-admins aren’t willing to buy one to share their certificates for free. (more…)