Simple Programming Concepts Implemented in PowerApps, Web Forms, HTML, Windows Forms, and ToolBook

In this video, we build the same simple application in Visual Studio (Windows Forms, Web Forms, HTML), ToolBook, and PowerApps to see the differences in environments in working with properties, events, and methods.

Subscribe to My Channel

My Book: Creating Business Applications with Office 365: Techniques in SharePoint, PowerApps, Power BI, and More


Making a jQueryUI Button

We now use the nice jQueryUI library in Tracker.Net, Exam Engine, and Training Studio. In each case, you link in the correct JavaScript files and style sheet and then use JavaScript to create the button. The nice thing is that you can use various types of HTML objects (input, anchor, ASP.NET buttons, etc.) and they still end up looking like a button. Here is an example from Tracker.Net:

$(function () {
        var optionsBtnId = $("#optionsBtn");

        if (optionsBtnId.length) {
                icons: {
                    primary: "ui-icon-home"
                text: false
   () {
                var returnVal = true;

                if (isOnCoursePage == true) {
                    if (isOneCourse == true) {
                        window.resizeTo(800, 600);
                    else {
                        returnVal = checkIfOkToLeave(LessonOpenMessageLeavingScreen);

                return returnVal;

The $() means that the jQuery will call the function after the page fully loads. We then get our hands on the HTML object that we want to use for our button. The #optionsBtn means that we are looking for an object with an id of “optionsBtn.” We then call the jQueryUI button method to create the button. In this case, we tell it to use the jQueryUI home icon and not to have any text. Next, we define the click function. Here we are doing some logic to determine whether to proceed with the click. If returnVal is false, the server side event will not be processed and we stay on the page.

History and Advantages of Template-Based e-Learning Authoring

My last response on Responsive Web Design with Exam Engine and Training Studio got me to thinking about the advantages of templates in e-Learning. I first wrote on the subject in 1999 with the paper “Extended” Page Templates for Speeding Up CBT Development.  We later included it in 2001’s The ToolBook Companion. The idea behind that paper was the work we did in ToolBook to copy in “template pages” that are already configured rather than creating them by hand or haphazardly copying and pasting pages. We used this successfully in large custom training development efforts for companies like Baker-Hughes Inteq. Here is the dialog box for selecting a template:


Here was my “advantages and disadvantages” statement:

Although there was quite a bit of up-front time to do this, we believe that it paid off nicely later in the project. The biggest advantages in the end were consistency and faster screen creation. Consistency refers to the fact that individual developers can’t introduce bugs on individual pages. They may expose bugs in the shared scripts or editors, but once these are fixed you know that all of the pages work correctly. This reduces the testing burden and allows the developers to focus on the content rather than the programming. The main disadvantage of the approach was the development time and skill needed to create a new extended page template. It wasn’t too bad for a page that was going to be used a number of times, but it was too much work for a unique page. Another disadvantage was more from a business perspective. Although the “Wellbore” approach worked very well for its specific project, it didn’t “scale up” that well to a general authoring solution. The shared scripts and editors were so optimized for efficient screen creation as to be of little use for developers outside of Platte Canyon. Since our focus is on products, we saw this as a disadvantage.

We had some thoughts of making a template-based ToolBook product, but the market looked questionable.

The next time templates came up was late 2002 when I ran an training class for the U.S. Army MANSCEN Schools based on my then new VBTrain.Net book. I quickly found out that their main interest was writing an editor to connect to an Access database. They had “storyboard” contractors that were entering content that was then used by the e-Learning developers. Rather than copying and pasting it, they wanted the content to read directly from the database. The rest of the weeklong class turned into me designing the database structure, writing the editor, and then writing the ToolBook side (templates and ADO code to populate the templates from the database). They were very happy with the solution as it boosted productivity considerably. But what invariably happened was that developers would edit the ToolBook files after populating them from the database. This caused the content to get out of sync with the database. Plus, the content had to published to DHTML each time a change was made.

In the meantime, we had created our .NET Question control and Exam Engine product (2003). The Army brought me back to create an ASP.NET solution that read the storyboard databases directly and dynamically brought in the images and media. Here is a screen shot:


This was a big step up, but the need for ASP.NET was problematic. Technically that kept the resulting training from being SCORM-compliant, since the SCORM package was supposed to be self-contained and an ASP.NET solution would not work on a non-Windows server or without a virtual directory being created.

We went back to the drawing board and created a Flash-based prototype using a more general database structure (similar to what we had created for Exam Engine). This product would become Training Studio in 2007, but before we could finish it, the Army awarded us a GSA contract in 2006 to create a complete set of editors and Flash templates for their same set of databases. Last we heard, they were still using this system.

As our Exam Engine also suffered from the disadvantages of being ASP.NET-based, we created a Silverlight version in early 2009 and a Flex/Flash version in late 2010. We quickly followed that up with a Flex version of Training Studio in 2010. Both products used our own question implementation (the original Training Studio used the Flash ActionScript 2 Question objects).

These products did fairly well, but like most Flash-based authoring tools, the writing was on the wall from the fact that that iPhone and iPad would not support Flash. So we rewrote both products from scratch, releasing version 4 of Exam Engine in May 2012 and version 3 of Training Studio in July 2012. Both of these were completely HTML/JavaScript/jQuery/CSS.

So why the big history lesson? Today we released version 4.5 of Exam Engine and 3.5 of Training Studio. The big change is the introduction of Responsive Web Design. It occurred to me that this ability was one more huge advantage of template-based authoring. So here is an overall list:

Advantages of Template-Based Authoring

  1. Content is independent of your display engine. We have Exam Engine customers who created their questions and media back in version 1 and have successfully updated it with minimal effort through these technologies: ASP.NET, Silverlight, Flash, and HTML. Since images and media are outside the authoring tool, they are easily updated without re-publishing your content. This is similar in concept to what Doc-To-Help offers for help authoring.
  2. e-Learning can be interactive yet still be created by non-programmers. Subject matter experts pick templates and fill in forms for the content. A “guru” can edit or create templates as needed.
  3. Localization is much easier since all content and images/media are stored externally and only brought together at runtime. The same editors used for content creation can also be used for localization.
  4. Pages are consistent. With traditional page-based authoring, authors tend to move titles and other items around. This can be distracting to the end user.
  5. Responsive Web Design is much easier. Imagine a training course of 5 lessons of 50 pages each. In most authoring tools, adjusting the content to the size of the browser is impossible. But even if it were possible, think of having to create CSS media queries for each unique page (potentially 250 pages in our example). That is such a huge job as to not being feasible. Instead, imagine that you are using templates. Even if you used all 30 Training Studio templates or 16 Exam Engine templates, creating/editing the CSS media queries is much more manageable.

For consistency, we should look at the disadvantages as well.

Disadvantages of Template-Based Authoring

  1. Not suited for lots of unique screen designs. To the extent that most screens are unique, creating templates to match can be a lot of work. Templates work best when you have consistent types of interactions such as image on the left and content on right, hotspots down the left side of the page, a video that fills the screen, etc.
  2. Different mindset. Authoring with templates can be frustrating to those authors who like to get it and “fiddle” with each page. As someone who has managed teams of e-Learning developers, I actually like keeping the authors out of the source as creative people tend to want to spend hours on a questionable animation rather than cranking out another five pages of training.
  3. Some technical expertise required. While template-based authoring is good for non-programmers, someone in the organization needs to have some HTML, JavaScript, and/or CSS experience in order to update and edit templates. Without this capability, a traditional authoring tool might be a better fit.

Thanks for sticking around for the history lesson!

Building a Glossary

This feature is a short segment from Jeff’s Programming for e-Learning Developers book.

Building a Glossary

A common e-Learning requirement is to be able to click on a hyperlink and display a popup display of the corresponding definition. We’ll implement a simple version of this capability in this chapter. We’ll build on techniques we learned in the Hyperlinking to a URL chapter in order to create the hyperlinks. To make things interesting and to avoid having to create a custom “data store” of the glossary names and definitions for each environment, We’ll add a GetGlossaryDefinition method to our ASP.NET service and call that to retrieve the actual definition.

ASP.NET Web Service

We first created this service in the Sending Email chapter. To add a glossary lookup capability, we first need to decide how to store the glossary entries. A good choice is a database. We’ll use an Access database adapted from our Training Studio product and locate it in the “App_Data” directory of our web site. This is a good choice since ASP.NET will prevent anything in this directory from being downloaded. We’ll use a simple structure where we look up the value based on the GlossaryName column and store the value in the GlossaryDefinition column.

We’ll include a stored query called ReadGlossaryEntries that We’ll use to load the entire table of glossary entries into memory from the web service. This allows us to “cache” the data on the web server. When a glossary request comes in, the web server will only read the database if it is not already in memory. This dramatically improves performance and is why we can get away with using Access rather than a higher-powered database like SQL Server. We don’t have time to get into the details of how the e-Learning developer would add, edit, or delete glossary entries, but there are options ranging from simple forms in Access to a separate editor application (which is what we use in Training Studio).

Here is the implementation code (Visual Basic) for our new GetGlossaryDefinition web method.

Imports System.Data
Imports System.Data.OleDb

<WebMethod()> Public Function GetGlossaryDefinition(ByVal accessKey _
	As String, ByVal glossaryKey As String) As String

	Dim glossaryDefinition As String = accessKeyInvalidString

	If accessKey = Me.WebServiceAuthorizationKey Then
		Dim viewId As DataView = Me.GlossaryDataView

		viewId.RowFilter = String.Format("GlossaryName = '{0}'", _
			glossaryKey.Replace("'", ""))
		If viewId.Count > 0 Then
			glossaryDefinition = viewId(0)("GlossaryDefinition").ToString
			glossaryDefinition = "Definition not found."
		End If
	End If

	Return glossaryDefinition
End Function

Private ReadOnly Property GlossaryDataView() As DataView
		Dim viewId As DataView

		If Current.Cache("GlossaryDataView") Is Nothing Then
			Dim conStr As String = _
				String.Format("provider=Microsoft.Jet.OLEDB.4.0; Data 
				Source={0}App_Data\Glossary.mdb", _

			Dim conId As New OleDbConnection(conStr)
			Dim commandId As New OleDbCommand("ReadGlossaryEntries", conId)
			Dim adapterId As New OleDbDataAdapter(commandId)
			Dim tableId As New DataTable

			commandId.CommandType = CommandType.StoredProcedure
			viewId = tableId.DefaultView
			Current.Cache("GlossaryDataView") = viewId
			viewId = CType(Current.Cache("GlossaryDataView"), DataView)
		End If

		Return viewId
	End Get
End Property

We start by importing two additional namespaces that we need: System.Data (having to do with general database objects) and System.Data.OleDb (having to do with communicating with Access databases). We then mark GetGlossaryDefinition with the <WebMethod()> attribute to denote that it can be called externally and make it Public. As with our previous examples, we validate against our WebServiceAuthorizationKey, which is stored in the web.config file on the web site. From there we read our GlossaryDataView property to get our viewId object, which is of type DataView. A DataView is a representation of a table that can be sorted and, what we are looking for, filtered. To do this, we set its RowFilter property. We use the fact that the column with our “key” is named “GlossaryName.” So if we are looking for the definition of “bass,” the RowFilter would be: GlossaryName = ‘bass’. We put the value in single quotes to account for the situation where it is multiple words (like “lead guitar”). We remove any single quotes in the key to avoid formatting problems. We now check the Count of our viewId. If it is greater than 0 (it should be 1 but we don’t want it to stop working if we get a duplicate entry for some reason), we get the first row (viewId(0)) and then the “GlossaryDefinition” column. Since this could presumably have other types of data, we call its ToString method to get the text version. This is what we return from our method call.

Let’s now look at the GlossaryDataView property. This is ReadOnly, meaning that we only read the data and never set it. It is also Private, which means that it can only be used by methods within the web service (e.g., we couldn’t read it from our ToolBook, Flash, or other applications). We define our viewId return type and then check the Cache to see if it is holding an object with the “GlossaryDataView” key. If not, we read the database. To do this, we start with our “connection string,” which is the conStr variable. We use Request.PhysicalApplicationPath to get our hands on the actual local file path of our web site. Our provider is “Microsoft.Jet.OLEDB.4.0” and our data source is the complete path to the database. We could add a password if needed as well. Once we have a connection string, we use this to build an OleDbConnection object. This is what actually connects to the database. We use this in turn to build an OleDbCommand object, telling it the name of the query, more generally called a “stored procedure,” (“ReadGlossaryEntries”) and passing it the connection. Still not done, we use this command to create an OleDbDataAdapter object. This is what actually executes the query. We want to read the query into a DataTable object, so that is what we create next. We are now ready to go. We call the Open method of our conId connection object. We set the CommandType property of our command object to tell it that what we passed back in its “constructor” was the name of a stored procedure (query). We then call the Fill method of the adapter to execute the query and write the results to our tableId object. From there, we read its DefaultView property to get what we are looking for, a DataView object. So that we don’t have to keep touching the database (which is a relatively slow operation), we add viewId to the Cache. Until the web server needs the memory for something else, future calls the web service will get viewId directly out of memory. Either way, we return viewId to the calling method.

Programming for e-Learning Developers Information

Order Programming for e-Learning Developers ($29.95 with free shipping in the U.S.)

PCI Compliance and Custom Error Pages

Those of us who accept credit cards are familiar with PCI (Payment Card Industry) compliance. In addition to protecting credit card numbers and so forth, your web sites need to tested for vulnerability to attacks and hackers. As part of compliance, your web sites are automatically scanned for vulnerabilities. Our site has always passed, but the report would show various weird vulnerabilities that I put down to being on a shared hosting service (DiscountASP.Net). But this most recent scan failed repeatedly with messages like this:

News database accessible over web (news.mdb) Impact: Attackers may access (read or destroy) application information, and in worst cases may take administrative control of the application. Data Sent: GET /help/trainingstudio/TrainingStudioContentEditor/news.mdb

When I went to help/trainingstudio/TrainingStudioContentEditor, there was no file named news.mdb or anything similar. I contacted the scan vendor (Security Metrics) and Emily evaluated the situation and came up with this explanation.

I have looked into these issues and I believe that each one is flagging because we are receiving affirmative (200 OK) responses for non-existent pages. Our scanner is sending a GET request for a specific page or file that is associated with a vulnerable program (ie. Guppy). The response to that GET request is a custom error page that you have created – but that customer error page’s status code is a 200 OK. The scanner sees the 200 OK and it flags the issue.

Sure enough, we had set up our site through DiscountASP.Net’s IIS tools (shown below) to show a custom error page (error.htm) if there was a missing page. So if the user put in a bogus link like, that page would display. But unfortunately that page returned the standard 200 status code. I found this nice tool that shows you the actual status code: When the scan looked for various rogue files, the 200 response put up red flags.

So what to do?

My first thought was to edit the error.htm page to set the status code to 404 (missing page). I had no luck finding a way to do that though. The next best thing was to use an ASP.NET page as that had the power to change the status code programmatically. Here is the code for missingPage.aspx.

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
	If IsPostBack = False Then
	    Response.StatusCode = 404
	    Me.UpdateRepeaters(linksRepeater, 41)
	End If
End Sub

The important part is Response.StatusCode = 404. The last step was to make this the custom error page for missing pages. This is done in IIS (Internet Information Services) for non-ASP.NET pages and in web.config for extensions handled by ASP.NET. Here is the DiscountASP.Net interface for the IIS setting.

For the web.config, we used this entry:

<customErrors mode="RemoteOnly" defaultRedirect="errorpage.aspx">
		<error statusCode="404" redirect="missingPage.aspx" />

After making the changes, the site passed on the first try.

I hope this is helpful if any of you run into a similar situation. Note that returning the correct status code is helpful for search engines as well.

Using jQuery UI Along with ASP.NET Web Forms

I like the jQuery UI objects, particularly its Button and Datepicker widgets. But what if you want to use them on a normal ASP.NET web form? Back in the early 2000’s, I wrote the VBTrain Graphical Button control to generate all the JavaScript needed to swap out graphics for the up, down, over, and disabled states. But now jQueryUI does all of that for us. Making the two technologies work together is pretty simple. Here is what the input control looks like:

<input id="SubmitBtn" runat="server" value="Submit" type="submit" />

The only change we made was the runat=”server” part. This is what allows us to recognize it in server-side code.

The next step is to add jQuery UI. We do this with the appropriate .js and css files loaded in the head of the file. We are using the Start theme, which is why the reference is “css/start/.”

<link href="css/start/jquery-ui-1.8.17.custom.css" rel="stylesheet" type="text/css" />
<script src="scripts/jquery-1.7.1.min.js" type="text/javascript"></script>
<script src="scripts/jquery-ui-1.8.17.custom.min.js" type="text/javascript"></script>
<script type="text/javascript">
    $(function () {

You might recognize the “load” function from the previous article. Once the page is fully loaded, it executes. We then use a new type of selector: “input:submit”. This means that we find all input controls that have a type of submit. We then call the button() method on each one. This is what loads all the appropriate graphics and associated scripts. If there had been 10 input buttons (of type submit) on the page, that one line would have configured all of them.

The last step is handling the click in our “code behind” file. That handler is shown below.

Private Sub SubmitBtn_ServerClick(sender As Object, e As System.EventArgs) Handles SubmitBtn.ServerClick
    ' take action here
End Sub

Those of you who are used to normal ASP.NET buttons will see that the event is a bit different: ServerClick rather than the normal Click. But the functionality is exactly the same.

Visual Basic in Reports

I’ve been spending a great deal of time building reports in Visual Studio for our new Tracker Reports product. While SQL Server Reporting Services has a number of great features, one of my favorites is the fact that you can embed Visual Basic code in the report itself. This can be extremely helpful with complex logic. For example, the completion status of a lesson or course can be expired, critical, or alert when it has an expiration date. Here is what the function looks like:

Public Function DetermineCompletionStatus(ByVal completionStatusObj As Object, _
    ByVal expirationDateObj As Object, ByVal currentDays_CriticalObj As Object, _
    ByVal currentDays_AlertObj As Object, Optional ByVal isCourse as Boolean = True) As String

    Dim completionStatus As String
    Dim expirationDateId, currencyCriticalDate, currencyAlertDate As DateTime
    Dim currencyCriticalDays, currencyAlertDays As Integer
    Dim checkExpiration As Boolean = isCourse
    Dim defaultStatus as String = "Not Started"

    If isCourse = False Then
    	defaultStatus = "not attempted"
    End If

    If IsDBNull(completionStatusObj) = True Then
        completionStatus = defaultStatus
        completionStatus = CStr(completionStatusObj)
        If completionStatus = ""
        	completionStatus = defaultStatus
        End If
    End If

    Dim returnString As String = completionStatus

    If IsDBNull(expirationDateObj) = True OrElse expirationDateObj Is String.Empty OrElse _
        IsNothing(expirationDateObj) = True OrElse expirationDateObj.ToString = "" Then

        expirationDateId = DateTime.MinValue
            expirationDateId = Convert.ToDateTime(expirationDateObj)
        Catch ex As Exception
            expirationDateId = DateTime.MinValue
        End Try

        If expirationDateId > DateTime.MinValue Then
            If IsDBNull(currentDays_CriticalObj) = True OrElse IsNothing(currentDays_CriticalObj) = True OrElse _
                currentDays_CriticalObj.ToString = "" OrElse currentDays_CriticalObj.ToString = "-10" Then

                currencyCriticalDays = 0
                currencyCriticalDate = expirationDateId
                currencyCriticalDays = CInt(currentDays_CriticalObj)
                currencyCriticalDate = expirationDateId.AddDays(-(currencyCriticalDays))
            End If

            If IsDBNull(currentDays_AlertObj) = True OrElse IsNothing(currentDays_AlertObj) = True OrElse _
                currentDays_AlertObj.ToString = "" OrElse currentDays_AlertObj.ToString = "0" OrElse _
                currentDays_AlertObj.ToString = "-10" Then

                currencyAlertDays = 0
                currencyAlertDate = expirationDateId
                currencyAlertDays = CInt(currentDays_AlertObj)
                currencyAlertDate = expirationDateId.AddDays(-(currencyAlertDays))
            End If
        End If
    End If

    Select Case completionStatus.ToLower
    Case "completed", "passed"
	' expiration Date

	checkExpiration = True
    End Select

    If checkExpiration = True Then
        If expirationDateId <> DateTime.MinValue Then
            If (expirationDateId <= Now) Then
                Dim expiredString As String = "Expired"

                returnString = expiredString
            ElseIf (currencyCriticalDate <= Now) Then
                returnString = "Critical"
            ElseIf (currencyAlertDate <= Now) Then
                returnString = "Alert"
            End If
        End If
    End If

	returnString = StrConv(returnString, VbStrConv.ProperCase)
    Return returnString
End Function

Within the report itself, we can use an expression for the value for the “status” rather than linking to just a single column from our query. For example, here is an expression for the course status:

=Code.DetermineCompletionStatus(Fields!CompletionStatus.Value, Fields!ExpirationDate.Value, _
    Fields!CurrencyDaysFlag_Critical.Value, Fields!CurrencyDaysFlag_Alert.Value)

Notice how we begin the expression with Code to denote that we are using our own code rather than built-in operators.

Dictionaries in .NET

In the previous post, we looked at a question related to the Dictionary object in ActionScript. Here is some continued information on the equivalent object in .NET.

We are looking at a Silverlight version of Training Studio 2 right now (note: this was true when written. But we have since decided to have the next version be HTML and JavaScript). One advantage in .NET is that we can use Generics to specify the type of objects within the Dictionaries or ArrayCollections. For example, here is how the same objects are defined in ActionScript and Silverlight:


public static var masterContentArray:ArrayCollection = new ArrayCollection();
public static var pageArray:Dictionary;


Friend masterContentDictionary As New Dictionary(Of Integer, Dictionary(Of String, String))
Friend pageArray As Dictionary(Of String, String)

Note that we renamed masterContentArray to masterContentDictionaryand made the “page number” an explicit key. The direct equivalent to the ArrayCollection would be:

Friend masterContentArray As New List(Dictionary(Of String, String))

The advantage of Generics is that we can specify exactly what kind of objects are stored in the Dictionary or List objects. If we try to put an object of the wrong type in it, Visual Studio will tell us. When we pull objects out of the Dictionary or List, we can use them as that type right away, rather than having to use the Dictionary() syntax.

Fixing SQL Divide By Zero Error

We had an issue yesterday with reports failing in Tracker.Net yesterday. The exception was “Divide by Zero Error Encountered.” I first thought it might be an error in a report itself. But searching for the error pointed to the SQL Server query instead. This Stack Overflow article describes the problem pretty well. The problem with my reports was that I was calculating the “percent score” on a test. I was accounting for a null “maximum score” but not for a zero maximum score. I haven’t checked the SCORM specification to see if a zero value is even valid, but it doesn’t really matter as at least one customer has data like that. Here is the original section of a query:

Tracker_SessionLessonStudentLog.LessonTestScore / ISNULL(Tracker_SessionLessonStudentLog.LessonMaxScore, 100) AS SessionPercentScore

The ISNULL() function checks if LessonMaxScore is null and, if so, substitutes 100. But it doesn’t address the situation where LessonMaxScore has an actual value of 0. That’s what caused the divide by zero error. As suggested in the article referenced above, I added the NULLIF() function as shown below.

Tracker_SessionLessonStudentLog.LessonTestScore / NULLIF(ISNULL(Tracker_SessionLessonStudentLog.LessonMaxScore, 100), 0) AS SessionPercentScore

So if the inside expression is 0, the whole divisor becomes null and the SessionPercentScore becomes null.

Hit By SQL Injection Attack

I hate to admit it, but we were hit by this SQL Injection Attack last week. The gist of a SQL Injection Attack is that the attacker hijacks your database query and inserts his own query that displays your data on the screen, wipes out your database, or other action. This particular attack wipes out every row and column in EVERY table in your database and replaces its content with a URL and script tags in the hope that your site will then be filled with links to the rogue web site. The best defense against this type of attack is to use parameterized queries/stored procedures rather than a “dynamic” SQL statement. This keeps the “bad” query from actually executing. Instead, the bad info is passed in as a parameter and the procedure then just fails or returns no data.

If I know how to prevent an attack, how did this attack work on our site? The short answer is that I was dumb. The longer answer is that most explanations of this type of attack demonstrate with a username and password-type page. You enter your username and password on a page. The insecure way to code this is to dynamically have code like this:

Dim sqlStatement As String = String.Format("Select * From Users Where username = '{0}' AND password = '{1}'", usernameField.Text, passwordField.Text)

The attacker then puts in a big set of SQL code in the username field that comments out your own statement and does what he wants with the statement. I went through every page and application on the site that got hit and couldn’t find anywhere where we were building dynamic SQL like this. But then a great support member from our hosting company, DiscountASP.NET, looked through the logs and found the problem. It was with a particular design that we originally used with our VBTrain.NET controls. It passed the productId in as part of the query string. For example: There are no active links to this page, but they were still on the site and working. The attack came when the rogue site sent the bogus SQL Statement (encoded) in place of the 9 above. So the URL looked like this:

productDisplay.aspx id=6%29+declare+%40s+varchar%284000%29+set+%40s%3Dcast%280x73657420616e73695f7761726e696e6773206f6666204445434c41524… rest removed for security reasons.

If I had used stored procedures for the query to get the data, we still would not have been vulnerable. Unfortunately, when I first set these sites up, I was reading data from various tables and decided to have a common method where I passed in the table, column, default value, etc. Here was the original method:

Protected Function RetrieveDataReader(ByVal tableName As String, ByVal columnName As String, ByVal columnValue As String, Optional ByVal sortColumn As String = "notAColumn") As SqlDataReader
Dim conId As SqlConnection = Me.VBTrainConnectionId
Dim dbString As String = String.Format("Select * from {0} where ({1} = {2})", tableName, columnName, columnValue)
If sortColumn <> "notAColumn" Then
dbString &= String.Concat(" order by ", sortColumn)
End If
Dim dbCommand As New SqlCommand(dbString, conId)
Dim dbReader As SqlDataReader = dbCommand.ExecuteReader(CommandBehavior.CloseConnection)
Return dbReader
End Function

We needed to use dynamic SQL in this case since it is hard if not impossible to write a stored procedure where the name of the table and the columns vary. We then called the function like this:

Dim dbReader As SqlDataReader
dbReader = Me.RetrieveDataReader("productInfo", "productID", idVal)

idVal was what we grabbed off the query string (everything after the ?). Those of you who have made it this far can probably see how this was a huge security hole. The other pages using this function had hard-coded values, which were safe. But passing in idVal straight from the URL allowed the hacker to blow away our database. So what to do?

  1. The best thing would be to change the design so that it calls a stored procedure instead. We will do this on the next redesign.
  2. For now, we can eliminate the vulnerability by checking the parameter before passing to the RetrieveDataReader function. We now make sure the parameter is a number AND that it is a very short length before allowing it through. Otherwise, we use a known value such as the 9 above.

Hopefully someone will read this and use it to protect your sites better than we did. I created the pages in question back in 2002. So another lesson is to go back and look at your old sites and applications and check them for security vulnerabilities.

On the positive side, we have the master copies of the databases in question saved locally and were able to recover quickly by using SQL Data Compare from Red Gate Software to update the database that had gotten hacked.