In this post I will discuss about non-standard string comparison in Certification Authority database queries. This post will focus on my PowerShell PKI module and queries in the CA database. The module contains the following commands that support CA database queries:

Request filters support the following comparison (seek) operators:

  • -eq – equals
  • -ge – greater or equals
  • -gt – greater than
  • -le – less or equals
  • -lt – less than

While these operator usage with scalar filter qualifiers (integers and date/time)  is obvious, it is unobvious when using these operators with string qualifiers. CA database (and not only, other query-based technologies use the same comparison behavior) uses binary string comparison via CompareString function with LOCALE_INVARIANT, NORM_IGNORECASE config parameters.

Since the function uses binary comparison, then you can expect that “A” is less than “B”, “AC” is greater than “AB”, “ABC” is less than “BRC”.

Previous examples were for strings with the equal length. However, CompareString function can compare strings with unequal length.

If first string length is larger than other string, a wild card is virtually added to the first string (in our case it is query qualifier value). For example, column value is “a large string” and qualifier value is “a large”, then column value is greater than qualifier value. In other words, “AA” > “A” and “A” < “AA”.

Consider the following scenario: you need to query issued certificates that were issued to a name that starts with “www”:

Get-IssuedRequest –Filter "CommonName –ge www", "CommonName –lt wwx"

The trick here is that we specify beginning match sequence": any request where CommonName column starts with “www”. This query would return requests where CommonName starts with “xyz”, because first character in “xyz” is greater than “w”. To limit the search only to “www” pattern, we need to add another filter where we replace last character with the next character in the alphabet: w=>x and use “-lt” operator. If we use “-le” operator, the query would return rows where CommonName starts with “wwx”. If we swap operators in both queries:

Get-IssuedRequest –Filter "CommonName –lt www", "CommonName –ge wwx"

the query will exclude all rows where CommonName column starts with “www” (in other words the resulting operator is –ne, Not Equals).

the implementation of Microsoft CA server do not allow any comparison operator except “-eq” with CertificateTemplate column when you want to query against version 2, 3 or 4 templates. This is because CA server stores in database custom template OID. I’m using explicit Template name=>OID conversion in the code, therefore the query will not return desired template. This is known issue and there is no workaround. Just use “-eq” operator for such cases.


Share this article:

Comments:


Post your comment:

Please, solve this little equation and enter result below. Captcha