VBScript Timer() function precision

C:Amie/ 1st July 20191st July 2019/ Microsoft, Programming

This article explores the numeric precision of the ASP and VBScript Timer() function, outlining that it may be more accurate than it initially appears.

Timer()

Prior to the arrival of PowerShell. The VBScript Timer() function wass the closest thing that script creators had for undertaking high precision timings in the Windows Scripting Host (WSH) environment.

The Timer() function returns a VBSingle – aka a Single Prevision floating point number or a “Real” – value as a representation of the systems real time clock. As a Single precision value, the permitted range is “-3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values“. If you print out the value of Timer() in ASP/VBScript

WScript.Echo Timer()

You will get a value like

52191.62

This value is a representation of the number of seconds that have elapsed on the local executing system since the local system click last hit midnight. Consequently, evaluating Timer() against a DateDiff() evaluation of the number of seconds since Midnight will result in the same answer (save for the decimal point).

WScript.Echo Timer()
WScript.Echo DateDiff("s", #2019-07-01#, Now())

Which results in

52191.62
52191

The hidden detail

Masked in the 2 decimal places default printout. Timer() is significantly more detailed than just two decimal points. Depending on the precision of your systems Real Time Clock (RTC) the precision may be up to 7 decimal places. You can view your systems capability by subtracting the Integer from the timer value:

WScript.Echo Timer() - Int(Timer())

Which may result in a value such as

0.6171875

At 7 decimal places, the precision of Timer() is – floating point number inaccuracy side – considerably better than that of VBDateTime. Under VBDateTime, the second is the atomic value, offering no more precision.

0         - Second
0.6       - Decisecond  / 1 tenth
0.61      - Centisecond / 1 hundredth
0.617     - Millisecond / 1 thousandth
0.6171    -               1 ten thousandth
0.61718   -               1 hundred thousandth
0.617187  - Microsecond / 1 millionth
0.6171875 -               1 ten millionth

This demonstrates that there is flexibility in VBScript for more precise clock operations. But is the resolution high enough?

Fidelity

The resolution and fidelity of the Timer() function is what makes it valuable (or not) to a programmer. On a modern system, with a High Precision Event Timer (HPET), the update interrupt will only fire so many times to update the clock.

Running an imprecise test as follows:

for i = 0 to 999
  WScript.Echo Timer() - Int(Timer())
next

The output value only changed between 10 and 31 cycles (reflective of the CPU scheduler performing other tasks during execution. The counter incrementation was consistent, updating 48 times with an increment of between 0.0117187 and 0.0195313 seconds. Once every 20.8 cycles on average.

In practice this means that the viable, comparable resolution of Timer() is not much better than once aver 0.2 seconds. Over a longer time period, Timer() can offer higher accuracy: provided you aren’t polling for an update more than every 0.2 of a second.

Replace line breaks in PRE tags using RegEx in ASP/VBScript

C:Amie/ 27th June 201927th June 2019/ Programming

This article discusses how to replace unnecessary line breaks ( , ) in HTML pre-formatted ‘PRE‘ tags using RegEx and ASP/VBScript.

The Problem

When I added a [code] tag to the HPC:Factor Community Forums markup some years ago. There was an obvious, but low-priority problem that had niggled at me. The BBS tag renders a pre-formatted ‘PRE‘ tag into the post HTML and styles it using a console font and fixed-size characters for improved legibility. The way that MegaBBS works however is that new lines (vbcrlf, vblf, \r\n, \n, chr(13) & chr(10), chr(10)) are replaced with ‘ ‘ as a batch replace at the beginning of the forum sanitisation and rendering process.

This means that when the browser renders the <pre></pre> tag, it renders both the vblf and the  , leading to double line breaks.

For example

Dim i
i = 0
while (i < 1000)
  Response.Write i
  i = (i + 1)
loop

becomes

<pre><br />
Dim i<br />
i = 0<br />
while (i < 1000)<br />
  Response.Write i<br />
  i = (i + 1)<br />
loop<br />
</pre><br />

leading the browser to render

Dim i

i = 0

while (i < 1000)

Response.Write i

i = (i + 1)

loop

This wastes screen space and reduces legibility.

RegEx Fix

The solution is very simple, use RegEx to re-parse the [code] block after it has globally replaced the line breaks.

I added the following code at the bottom of the MBBS Code loop in the MBBSDecode function in include.asp

if (vBBSDecodeArray(0, index) = "\[code\]") then	' This looks for the PRE tag for the code and then removes the <br />'s from it to return it to pure pre-formatted
	mBBSRegEx.pattern = "\<pre[^>]*\>((.|\n)*)\<\/pre\>"
	for each sNewText in mBBSRegEx.execute(MBBSDecode)
		MBBSDecode = Replace(MBBSDecode, sNewText.Value, (Replace(sNewText.Value, "<br />", "")))
	next
end if

To evaluate what this means line-by-line

When it is parsing the [code] tag from the list of all BBS markup statements
Set a RegEx pattern to search for the opening PRE tag with zero or more attributes e.g. <pre attribute1="one" attribute2="two"> ending with </pre>. The “((.|\n)*)” ensures that the search looks for all characters, including over new lines, for as many characters and new lines as is necessary to encounter the closing </pre> tag.
For every positive match i.e. for every <pre>*</pre> match
In the matches string, replace   with "", then replace the match in the original source string (MBBSDecode) with the fixed string
Move to the next match until there are no more matches

To genericise the example

Dim strHtml
Dim match
Dim matches
Dim regEx

strHtml = "<body><p>hello</p><pre class="">line one" & vbcrlf & "<br />line two" & vbcrlf & "<br />line three" & vbcrlf & "<br /></pre><p>hello</p></body>"

set regEx = New RegExp
    regEx.Pattern = "\<pre[^>]*\>((.|\n)*)\<\/pre\>"
    regEx.IgnoreCase = true
    regEx.Global = true
set matches = regEx.Execute(strHtml)

For Each match in matches
    strHtml =  = Replace(strHtml, match.Value, (Replace(match.Value, "<br />", "")))
Next

' strHtml will now effectively be:
' "<body><p>hello</p><pre class="">line one" & vbcrlf & "line two" & vbcrlf & "line three" & vbcrlf & "</pre><p>hello</p></body>"

Fairly simple, but as with most things RegEx, a headache for most of us – unless you are using it all the time.

Unable to update NuGet or Packages in Powershell due to “WARNING: Unable to download the list of available providers. Check your internet connection.”

C:Amie/ 14th March 2019/ IIS, Microsoft, Programming, Software

When attempting to install or update PowerShell Modules, NuGet or NuGet packages in PowerShell 5. You receive one or more of the following errors

WARNING: Unable to resolve package source 'https://www.powershellgallery.com/api/v2/'.

The underlying connection was closed: An unexpected error occurred on a receive.

WARNING: Unable to download the list of available providers. Check your internet connection.

Equally, you may receive the same error when attempting to run a WGET or an Invoke-WebRequest command e.g.

wget https://www.google.com/

You are unable to install/update the software component or make an outbound internet connection.

This issue may be especially prevalent on IIS installations serving HTTPS websites.

The Fix

Conventional troubleshooting is fairly well documented on-line

Ensure that you are actually able to open a https webpage in a web browser
Ensure that your DNS is working correctly.
Check to see whether wget can connect to a non-https site e.g.
wget http://www.google.com/
Check to see whether or not you need to use a Proxy server. If so, you must configure PowerShell to use your Proxy Server before you proceed. This may require you to to configure PowerShell with your Proxy Server credentials.
$webclient=New-Object System.Net.WebClient
$webclient.Proxy.Credentials = [System.Net.CredentialCache]::DefaultNetworkCredentials

A less obvious issue to explore related to the default operating system security configuration for using SSL.

More Info

By default, Windows Server and Windows client will allow SSL3, TLS 1.0, TLS 1.1 and TLS 1.2. The .net Framework is also configured to allow these protocols, and, by default, any outbound request for a SSL site will attempt to use SSL3/TLS 1.0 as its default protocol.

In secure environments, where system administrators have enabled recommended best practice on Windows systems to disable the use of SSL1, 2,3 and TLS 1.0. PowerShell is not currently clever enough to internally compare its configuration to that of the operating system. consequently, when attempting to make an outbound https request in such an environment. PowerShell will attempt to use one of the older protocols which has been disabled by the operating system’s networking stack. Instead of re-attempting the request using a higher protocol. PowerShell will fail the request with one of the error messages listed at the beginning of the article.

As NuGet and Update-Module both attempt to make connections to Microsoft servers using HTTPS, they too will fail.

Encountering this issue on a SSL enabled IIS install will be more common, as it is more likely that system administrators will have applied best practice and disabled legacy encryption protocols on these servers. their public facing, high visibility should demand such a response.

To fix the issue there are two options:

Reconfigure and reboot the system to re-enable client use of TLS 1.0 (and possibly SSL3) via
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\<protocol>\Client

DisabledByDefault = 0
Enabled = ffffffff (hex)
Alternatively, you must set-up each PowerShell environment so that the script itself knows not to use the legacy protocol versions. This is achieved via the following code which restricted PowerShell to only using TLS 1.1 and TLS 1.2.
[System.Net.ServicePointManager]::SecurityProtocol = [System.Net.SecurityProtocolType]'Tls11,Tls12'

SQL Server combining LIKE with IN (SELECT LIKE IN query)

C:Amie/ 22nd December 201829th December 2018/ Microsoft, Programming, SQL Server

System Requirements

SQL Server

The Problem

This was one of those “it should be obvious” moments.

Dating back to 2004 in its current iteration, the hpcfactor.com membership database has been blighted by SPAM. We are no different to any web community in this regard. A can be a particular problem as many accounts that we may miss are registered and (if they survive) become a valuable commodity to sell as trojan accounts for use in mischief at a later date.

The moderation team at HPC:Factor does a great job of ensuring that these ‘pay per post’ (usually from Indian IP’s) urchins are wasting their time, but it does not stop them from trying. At one point we were having to content with several hundred a day, and had to shutdown registration completely. A custom layer that I wrote to sit on-top of the BBS registration system has done a good job to convert that SPAM torrent into a trickle of a “couple of times a month”.

Over the years, I have added more and more banned email domains to a ban list that is maintained in the user registration database. It occurred to me that as that list has increased, the historic pool of user account has never been re-parsed against the updated banned domains list.

Sounds simple right? Fire up SQL Server Management Studio and create a SQL SELECT LIKE IN query… is it that simple?

The Solution

Yes actually it is.

The obvious logic that you want to follow is to take this syntax

SELECT id, username, emailaddress
FROM members
WHERE emailaddress IN (
SELECT emailaddress FROM bannedemails
)

… and merge it with a LIKE statement to create a SELECT LIKE IN query, something like

SELECT id, username, emailaddress
FROM members
WHERE emailaddress LIKE IN (
SELECT '%' + emailaddress FROM bannedemails
)

Unfortunately however this syntax is completely illegal.

The answer is to perform the LIKE/IN as part of an INNER JOIN

SELECT members.id, members.username, members.emailaddress
FROM members
INNER JOIN bannedemails ON members.emailaddress LIKE '%' + bannedemails.emailaddress

Performance

This is a not a “real time” friendly SQL expression. The inefficiency of using LIKE evaluation becomes very apparent as the record set scales. The HPC:Factor database had 2,271 banned email domains and 30,336 user. Giving a total record iteration and evaluation count of 68,893,056 rows.

The total query execution time for this on was 2 minutes 13 seconds (ouch). Having said that, on reviewing why it took that long, I realised that at some point in time I was being extremely stingy with the SQL Server VM. It only had access to 2 CPU cores and 12 GB of RAM. The hypervisor server itself has access to a 10 core hyper-threaded Xeon.

The Result

I have a lot of respect and sympathy for people who use VPNs and temporary email addresses and I genuinely understand why they feel the need (or in some cases must, depending on what part of the world you are in).

I saying that, I also have to deal with the issue from the other side of the equation. If the website is having a bad day, it’s usually because of something coming from one or both of these camps. As ever in life, a minority will insist on spoiling a good thing for everyone else.

After running the query, we had only missed 8 user accounts – credit to the moderation team. The oldest account was 26/09/2006 and my favourite that we probably should not have missed was “mrputin” from January 2013.