Excel VBA: Macro to Remove Matching Rows

Continuing on the theme of VBA macros in Excel, I will demonstrate a macro that I created today. The task at hand was to match class events in one database with another, and determine if the enrollment counts in database A were less than database B. Since there already exist online reports for both systems, it was decided that it would be easier to just compare the results where applicable.

The steps to do this are simple. First, get both reports. Then, run a simple query to insert the mapping of class ID’s between the two databases (I have an already existing Macro for this). The next step would be to create a VLOOKUP formula using the returned matching column. (For more information on using the VLOOKUP function, I recommend the following going here and here. She does an excellent job of describing the VLOOKUP worksheet function). Where there is an #N/A, delete the row since it is not applicable to our lookup. Below is the completed worksheet with VLOOKUP already plugged in. You can see in the formula where I am referencing another worksheet with the class ID mappings, and it is returning #N/A where there are no results.



There are two paths we can take to delete the rows with no matches. I can sort column G, and delete the grouped items all at once. There are several steps in doing this. I would need to sort by column G, then find the grouping, then select the group, then delete the group, and finally resort by class ID. However, since this is a common task I have to do frequently and I am incredibly lazy, I prefer the 1 button click solution to remove all of the rows with #N/A, so I will write a custom macro to do this for me. I have previously demonstrated how I write Excel macros in my previous article about Excel VBA, so I will skip this. Below is the final macro.

Sub DeleteMismatchFromVLookup()
    'Foundcell is the range returned from a search for a particular matching field, if nothing then search
    'did not return anything
    Dim found_cell As Range
            
    'Start at the top of the worksheet
    Range("A1").Select
    
    'Set the returned cell to the cell that is matching the search for #N/A
    Set found_cell = Cells.Find(What:="#N/A", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
            
    'If there are no matches, dont even bother running the rest of the macro
    If (Not found_cell Is Nothing) Then
        'Loop until no more matches are found in the worksheet
        Do
            'Activate the matching cell, then select the entire row and delete it
            found_cell.Activate
            Rows(ActiveCell.Row).Select
            Selection.Delete Shift:=xlUp
            
            'Do the search again. Do no activate until next loop to prevent trying to run
            'an objects method when no objects are found, causing an error
            Set found_cell = Cells.Find(What:="#N/A", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
                                        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        Loop While (Not found_cell Is Nothing)
    End If
    
    'Set found_Cell to nothing to free up any residually held memory
    Set found_cell = Nothing
End Sub

I hope this demonstrates that there are legitimate uses for VBA other than exposing your system to viruses. I have a whole library of macros such as this I use on a daily basis for working with spreadsheets. There are tons of libraries out there with Office Macros, such as Woody’s Office Power Pack.

Despite my praise for Offices Macro feature, I look forward to trying out the macros in OpenOffice. I am already using OO Calc for my budget, and I would like to break free of my dependence on Excel. Plus it would be nice to show my continuing support for FOSS and write an article about it. It also looks like OpenOffice is starting to catch up with its macro development scene.

FreeBSD 6.0 Released

Thanks to OSNews for advanced notice. FreeBSD 6.0 has been released today (note: OSNews is notorious for posting links to releases early, however I am not complaining). FreeBSD is said to be the most user friendly out of all the BSD family of Operating Systems. I have wanted to try out FreeBSD for some time, and I decided to wait until version 6 to take the plunge. I will write up installation instructions and my experience using FreeBSD when I am complete.

Update: The official announcement has been posted.

Excel VBA Macros

Microsoft recieves a lot of critcism for its inclusion of Visual Basic for Applications in many of its programs. This introduced us to the horrible menace of Macro Virii. However, there is an actual legitimate use for VBA as well. Lets consider this example. I will look at a request that I consistently get, which is to determine if a list of employees is active or not. The list can be anywhere from 100 to 10,000 employees. Obviously in the latter case, going through by hand to confirm their employment status is unfeasible. This is where VBA in Excel can come in handy.

The following Excel VBA macro will prompt the user for the Source Column, then prompt the user for the destination column, run the query for all rows and return the employees active status in the destination column. It utilizes ADO to retrieve the results from an Oracle Database. (Note: there are more efficiency tweaks that could be applied to this function to speed up the retrieval via ADO, however the function in its current form will suffice for 99 percent of all queries I use. I will leave these up to the reader to discover more optimized methods).

Sub get_ActiveStatus()
     'CONSTS-----------------------------------------------------------
     'Replace with sql statement to use. Be sure the return columns is called "Status" for this query
     Const SQL_TO_USE As String = "SELECT 'X' Status FROM employees WHERE cd_emp_type <> 'X' AND no_emp = ?"
     'Define the row to start on to take into account header rows
     Const START_ROW As Integer = 2
     'The database connection string, change for each database used
     Const DATABASE_CONNECTION_STRING As String = "Provider=MSDAORA.1;Password=cxxxxxx;User ID=cxxx;Data Source=ts;Persist Security Info=True"

     'VARIABLES--------------------------------------------------------
     'x is a generic counter, then there are the 3 objects used in ADO
     Dim x As Integer
     Dim con As New ADODB.Connection
     Dim com As New ADODB.Command
     Dim rec As ADODB.Recordset
     'Source and destination columns
     Dim srcColumn As String
     Dim dstColumn As String
     'Variable to store the number of rows to process
     Dim LastRow As Integer

     'CODE-------------------------------------------------------------
     'Get the number of rows in this worksheet by using Excels internal countA function
     'I use this instead of looking for blank cells because this has shown itself to be more accurate
     If WorksheetFunction.CountA(Cells) > 0 Then
          LastRow = Cells.Find(What:="*", After:=[A1], _
          SearchOrder:=xlByRows, _
          SearchDirection:=xlPrevious).Row
     End If

     'If there are no rows, exit out of the function
     If LastRow = 0 Then
          MsgBox "There are no rows in this worksheet", vbOKOnly
          Exit Sub
     End If

     'Set up the connections using the database string defined above
     con.ConnectionString = DATABASE_CONNECTION_STRING
     con.CursorLocation = adUseClient
     con.Mode = adModeRead
     con.Open

     'Set up our command object to do a prepared query using out constant defined above
     com.CommandType = adCmdText
     com.Prepared = True
     com.CommandText = SQL_TO_USE
     com.Parameters.Append com.CreateParameter("EmpID", adVarChar, adParamInput, 50)
     Set com.ActiveConnection = con

     'Prompt the user for the source column containint employee IDs and the destination
     'column indicating that the employee is active
     srcColumn = InputBox("Enter the Source Column: ")
     dstColumn = InputBox("Enter the Destination Column: ")

     'Set our generic counter to the start row defined in the constants section
     x = START_ROW

     'Loop from our starting row to the last row, on error use the general exception handler
     On Error GoTo Error_Occured
     For x = START_ROW To LastRow
          'set the employee ID to look up as the source column and row and execute query
          com("EmpID") = Trim(Range(Trim(srcColumn) & Trim(Str(x))).Value)
          Set rec = com.Execute

          'If there are records returned, set the destination to the returned result
          If (rec.RecordCount > 0) Then
               Range(Trim(dstColumn) & Trim(Str(x))).Value = rec("Status")
          End If

          'Set the recordset to nothing
          Set rec = Nothing

          'Do any background events, such as processing keypresses or switch windows
          DoEvents
     Next

     'Close and clear all objects
     con.Close
     Set com = Nothing
     Set con = Nothing
Exit Sub

'The general exception handler
Error_Occured:
     'close the database connection and clear objects
     con.Close
     Set com = Nothing
     Set con = Nothing
     'Let the user know there is a problem
     MsgBox "An error occured with processing the macro", vbCritical
End Sub

What I would like to do now is have these macros available to me anytime I need them, however I do not want them to appear in Excel files I send out. So I will set up a personal file that will open each time I open Excel. These steps apply to Excel 2000.

First, open up Excel and create a new worksheet.


In your new sheet, delete the tabs for “Sheet 2” and “Sheet 3”.


Then go up to Window, and choose Hide.


Then Go to Tools, Macros, and Visual Basic Editor.


In the Visual Basic Editor, you can see that your new Worksheet is still available for Macro editing. We will create a new Module to house our Macros. So, right mouse click on the name of your worksheet, and choose Insert, and Module.


Under the property editor, name your Module whatever name you feel is appropriate. I have chosen “ExcelMacros” here.


Now paste the Macro into the modules code window. Since this Macro makes use of the Microsoft ActiveX data object, we need to add it as a reference. To do this, Tools, References, and select Microsoft ActiveX Data Objects from the list of libraries.

Once completed, choose File and Save. You will get a File Explorer dialog. Here, navigate to my user profiles section for Excel, which is at “C:\Documents and Settings\jward\ Application Data\Microsoft\Excel\XLSTART\” and I will save the file as “PersonalMacros.XLS”.


Once I exit out of Excel and reenter, I go back to the VBA editor, and I see that my personal macros is still there. The only drawback to this approach is that when I open Excel, I do not get a blank worksheet, which is a sacrifice I am willing to make.

I am lazy by nature, so I do not want to have to open up the Macro editor each time I want to run this script, so I will create a button on a toolbar for easy access. First, go to File and New to create a blank Worksheet. We won’t do anything with this worksheet, however Excel will not allow us to assign a Macro to a button unless a Worksheet is open and visible.

First, I right mouse click in the tool bar area and choose customize.

I then choose the toolbar tab, click the New button, and enter the name of my new Toolbar as “Personal Macros”.

By default, the new toolbars are already visible. With the Customize dialog box still open, I go over to the Commands tab, scroll down to under the Categories list box to Macros, and drag the Custom Menu Item over to my new toolbar.


I right mouse click on the toolbars “Custom Menu Item” to get the properties dialog, and I change the name to “Get Active Employee Status”.


I then right mouse click again, and choose “Assign Macro”.


When the dialog opens up, I can now choose the macro I created.


And that’s it. Now I can see this macro in action. I open up a document with Employee ID’s to test. I click on my button and am greeted with my dialog box asking for the Source Column.


Another dialog box prompting me for the destination column pops up, which I fill in. Once I hit OK, I can watch as the macro fills in my values for me.


You can see where this is a time saver. Almost all objects in Microsoft Office, and a lot in Windows are accessible through VBA. This is one of the reasons that Visual Basic is such a popular language. However, in some cases, it allows a little too much power for an office suite. In fact, I’ve seen things as far out as Port Scanners written in VBA. This is also one of the reasons that Microsoft receives so much criticism for VBA inclusion in so many Microsoft products. Many say this is a vendor lock in tactic, however I take it for what it is, a time saver in many cases. I have dozens of simple macros based off this one that allow me to very easily meet customers request for information at the click of a button. Incidentally, OpenOffice Calc program has in very recent versions incorporated a similar Macro language that I intend to look into. While not as easy to develop in as VBA, I am sure it is every bit just as powerful, if not more.

Windows IR Word Metadata script

I recently came across some interesting articles on Harlan Carveys Incident Response Blog regarding retrieving metadata information from Word documents. He has several posts on the topic, listed below

• -More on Word Metadata


• -Yet, even more on Word metadata


• -Word metadata code posted

In the last article, he provides an example Perl script to demonstrate retrieving various metadata information from Word documents. I thought this would be something interesting to check out.

Since I will be running this from a Windows environment, I will need a Perl interpreter. My preference for this is ActiveStates ActivePerl. This could also be done via the Cygwin Perl package. ActivePerl is available from here. There are two install packages, one is a graphical installer, and the other is a DOS batch installer. The only difference between the two as far as I can tell is that they create different folders under the Windows Start Menu. I will show both installers.

I will cover the graphical installer first, since it is the package used more often. I downloaded the ActiveState ActivePerl MSI package from their website. To run the installer I go to the location to which I downloaded it and double-click on the install file pictured below.



On the option screen, I select all options to be installed on the local hard drive, and set my install directory to C:\ActivePerl.



I keep all options on the next screen, and just click on next until it is complete.

To install with the AS package, I extract the ActivePerl archive file to C:\temp\ap_install. The package comes with a DOS batch file called installer.bat for doing the install. Below is my DOS session of the install process.

C:\TEMP\ap_install\ActivePerl-5.8.7.813-MSWin32-x86-148120>dir *.bat
Volume in drive C is Local Disk
Volume Serial Number is 7BC4-13DA

Directory of C:\TEMP\ap_install\ActivePerl-5.8.7.813-MSWin32-x86-148120

06/06/2005 02:19p 15,757 Installer.bat
1 File(s) 15,757 bytes
0 Dir(s) 2,704,973,312 bytes free

C:\TEMP\ap_install\ActivePerl-5.8.7.813-MSWin32-x86-148120>installer.bat
Welcome to ActivePerl.

This installer can install ActivePerl in any location of your choice.
You do not need Administrator privileges. However, please make sure
that you have write access to this location.

Enter top level directory for install [c:\Perl]: C:\ActivePerl

The typical ActivePerl software installation requires 80 megabytes.
Please make sure enough free space is available before continuing.

ActivePerl 813 will be installed into 'C:\ActivePerl'
Proceed? [y] y

If you have a development environment (e.g. Visual Studio) that you
wish to use with Perl, you should ensure that your environment (e.g.
%LIB% and %INCLUDE%) is set before installing, for example, by running
vcvars32.bat first.
Proceed? [y]

Create shortcuts to the HTML documentation? [y]

Add the Perl\bin directory to the PATH? [y]

Create Perl file extension association? [y]

Create IIS script mapping for Perl? [y] n

Create IIS script mapping for Perl ISAPI? [y] n

Copying files...
3002 File(s) copied
Finished copying files...
Relocating...done (95 files relocated)

Configuring C:\ActivePerl\lib\Config.pm for use in C:\ActivePerl...

Configuring Perl ...

Configuring PPM for use in C:\ActivePerl...

Setting 'tempdir' set to 'C:\DOCUME~1\jward\LOCALS~1\Temp'.

If you are behind a firewall, you may need to set the following
environment variables so that PPM will operate properly:

set HTTP_proxy=http://address:port [e.g. http://192.0.0.1:8080]
set HTTP_proxy_user=username
set HTTP_proxy_pass=password


Building HTML documentation, please wait...

Thank you for installing ActivePerl!

Press return to exit.

Note the line about setting up the HTTP proxy. If you are behind a firewall and are required to go through a proxy server, you will need to set this up. This is not noted in the graphical installation.

I downloaded Harlan’s script from here. The archive contains a single Perl script file titled wmd.pl. I extract the file to C:\ap_install. For testing, I will use the Word version of one of my previous articles, Sguil Reporting with Birt. I copy this file to C:\ap_install for simplicities sake. Eagerly I tried to run the script

c:\activeperl\bin\perl wmd.pl "Sguil reporting with BIRT3.doc”

And I got garbage. I review Harlan’s blog entry, which clues me in on some additional steps I need to take. The script requires 3 packages from the Perl/Programmers Package Manager. I will need to run a series of commands from PPM to install these. The PPM is located as pictured below



Below is the session for trying to install the first package

ppm> install OLE-Storage
Error: No valid repositories:
Error: 500 Can't connect to ppm.ActiveState.com:80 (Bad hostname 'ppm.ActiveStat
e.com')
Error: 500 Can't connect to ppm.ActiveState.com:80 (Bad hostname 'ppm.ActiveStat
e.com')
ppm>

As mentioned before, I need to configure for proxy support. I have to configure an environment variable to point to my proxy. I do this from the Windows Control Panel. I go to settings, Control Panel, and double click on the System icon.



Once the System Properties Window comes up, I select the Advanced tab, then click on the Environment Variables button.



Once in the Environment Variables section, I create a new System Environment Variable by clicking on the New button



And I enter in my proxy info like so:



I exit out and rerun PPM so it can recognize the new environment variables. Below is my install session for the three additional packages (Note: I edited the results for brevity)

ppm> install OLE-Storage
====================
Install 'OLE-Storage' version 0.386 in ActivePerl 5.8.7.813.
====================
Downloaded 99928 bytes.
Extracting 40/40: blib/arch/auto/OLE/Storage/.exists
Installing C:\ActivePerl\html\bin\herbert.html
Installing C:\ActivePerl\html\bin\lclean.html
………..<edited>……………………
Successfully installed OLE-Storage version 0.386 in ActivePerl 5.8.7.813.
ppm> install Startup
====================
Install 'Startup' version 0.103 in ActivePerl 5.8.7.813.
====================
Downloaded 15618 bytes.
Extracting 9/9: blib/arch/auto/Startup/.exists
Installing C:\ActivePerl\html\bin\replace.html
………..<edited>……………………
Successfully installed Startup version 0.103 in ActivePerl 5.8.7.813.
ppm> install Unicode-Map
====================
Install 'Unicode-Map' version 0.112 in ActivePerl 5.8.7.813.
====================
Downloaded 449032 bytes.
Extracting 113/113: blib/arch/auto/Unicode/Map/Map.lib
………..<edited>……………………
Installing C:\ActivePerl\bin\mkmapfile.bat
Successfully installed Unicode-Map version 0.112 in ActivePerl 5.8.7.813.
ppm>exit

With the required packages installed, I again try Harlan’s script. (note: I prefix my command with perl this time to make sure I am running in the Perl environment).

C:\TEMP\ap_install>c:\activeperl\bin\perl wmd.pl "Sguil reporting with BIRT3.doc
"
--------------------
Statistics
--------------------
File = Sguil reporting with BIRT3.doc
Size = 507904 bytes
Magic = 0xa5ec (Word 8.0)
Version = 193
LangID = English (US)

Document has picture(s).

Document was created on Windows.

Magic Created : MS Word 97
Magic Revised : MS Word 97

--------------------
Last Author(s) Info
--------------------
1 : JWard : H:\Blog entries\REporting with BIRT\Sguil reporting with BIRT.doc
2 : JWard : H:\Blog entries\REporting with BIRT\Sguil reporting with BIRT.doc
3 : ***************: E:\Blog entries\REporting with BIRT\Sguil reporting
with BIRT.doc
4 : ***************: C:\Documents and Settings\Administrator\Application
Data\Microsoft\Word\AutoRecovery save of Sguil reporting with BIRT.asd
5 : Bonnie Taylor : C:\Documents and Settings\btaylor\Desktop\Sguil reporting wi
th BIRT.doc
6 : Bonnie Taylor : C:\Documents and Settings\btaylor\Application Data\Microsoft
\Word\AutoRecovery save of Sguil reporting with BIRT.asd
7 : Bonnie Taylor : C:\Documents and Settings\btaylor\Application Data\Microsoft
\Word\AutoRecovery save of Sguil reporting with BIRT.asd
8 : Bonnie Taylor : C:\Documents and Settings\btaylor\Application Data\Microsoft
\Word\AutoRecovery save of Sguil reporting with BIRT.asd
9 : Bonnie Taylor : C:\Documents and Settings\btaylor\Application Data\Microsoft
\Word\AutoRecovery save of Sguil reporting with BIRT.asd
10 : JWard : C:\Documents and Settings\jward\My Documents\Blog entries\REporting
with BIRT\Sguil reporting with BIRT3.doc

--------------------
Summary Information
--------------------
Title : Sguil is a great platform for IDS operations
Subject :
Authress : ***************
LastAuth : JWard
RevNum : 2
AppName : Microsoft Word 9.0
Created : 28.10.2005, 21:18:00
Last Saved : 28.10.2005, 21:18:00
Last Printed :

--------------------
Document Summary Information
--------------------
Organization : ***************

Success. The script is working correctly. I can gather a lot of information by looking at this. By looking at the history, I can see where I created the article under H:\Blog entries\ REporting with BIRT (which is on a USB Drive). I can then guess one of two things, a separate user with different drive mappings than Jward opened the document or it was opened on another machine. This is evident by entry 3 showing the document under E:\ Blog entries\REporting with BIRT\Sguil reporting with BIRT.doc, and I can tell the document was opened at least long enough for one auto-save to occur as indicated by entry 4. Bonnie Taylor, my good friend and editor, then opened the document (Where would I be without her?) from her Windows Desktop. Bonnie had the document open long enough for at least 4 auto-saves to occur showing me that she is actually reading the articles I send her :) . And the document was finally opened by Jward. The date created and last saved entries in the summary must be specific to the date that the document was actually created, since this document was restored from an email archive on this date, it was actually created on Sept. 28th, so this information would be suspect in an actual investigation.

To check this script out further, I open the Word document for A Simple Program in Debug to further test the script:

C:\TEMP\ap_install>c:\activeperl\bin\perl wmd.pl "DOS Debug.doc"
--------------------
Statistics
--------------------
File = DOS Debug.doc
Size = 361472 bytes
Magic = 0xa5ec (Word 8.0)
Version = 193
LangID = English (US)

Document has picture(s).

Document was created on Windows.

Magic Created : MS Word 97
Magic Revised : MS Word 97

--------------------
Last Author(s) Info
--------------------
1 : JWard : C:\Documents and Settings\jward\My Documents\Blog entries\DEBUG\DOS
Debug.doc
2 : JWard : C:\Documents and Settings\jward\My Documents\Blog entries\DEBUG\DOS
Debug.doc
3 : JWard : C:\Documents and Settings\jward\My Documents\Blog entries\DEBUG\DOS
Debug.doc
4 : JWard : C:\Documents and Settings\jward\My Documents\Blog entries\DEBUG\DOS
Debug.doc
5 : JWard : C:\Documents and Settings\jward\My Documents\Blog entries\DEBUG\DOS
Debug.doc
6 : JWard : C:\Documents and Settings\jward\My Documents\Blog entries\DEBUG\DOS
Debug.doc
7 : JWard : C:\Documents and Settings\jward\Application Data\Microsoft\Word\Auto
Recovery save of DOS Debug.asd
8 : : E:\DEBUG\DOS Debug.doc
9 : : C:\Documents and Settings\digiassn\Application Data\Microsoft\Word\AutoR
ecovery save of DOS Debug.asd
10 : : C:\Documents and Settings\digiassn\Application Data\Microsoft\Word\Auto
Recovery save of DOS Debug.asd

--------------------
Summary Information
--------------------
Title : I got a little bored the other day and was feeling a little nosta
lgic for the days of DOS
Subject :
Authress : JWard
LastAuth :
RevNum : 63
AppName : Microsoft Word 9.0
Created : 12.10.2005, 15:06:00
Last Saved : 19.10.2005, 17:21:00
Last Printed :

--------------------
Document Summary Information
--------------------
Organization : ***********

Again, the same kind of information can be gathered by looking at the Last Authors Info. I can see Jward made numerous edits, however, only the edits at entry 6 was open long enough for the auto-save feature to save in entry 7. Entry 8 was opened by a blank user from another location (USB Key), and opened long enough for 2 auto-saves to occur. In this instance, the Created date and date last saved are both correct.

I am impressed with the output of the script. Harlan has proved one of the points I consistently try to make: many times people will downplay the usefulness of scripts because they are not compiled programs, or because they are not written in whatever the programming language of the month is. I disagree with this sentiment. While I try not to judge, I believe the utility provided by a script or a program, regardless of its base language, is a measure of its worth. Take Sguil for example. Sguil provides one of the best, if not the best, platforms for network security analysts to work from, and it is written in TCL/Tk. And I would say that Sguil is more than simply a script.

On a final note, I have to thank Harlan for his help with some small issues I had with running the script. The first document I tried was truncated from my USB key because I did not shut it down correctly when I copied the document. Since the document was corrupt, I was getting some strange errors from the script. Harlan was kind enough to work with me on finding a solution. I will keep ActivePerl around and try out some of the other scripts that Harlan has provided to the community.

Input Validation in ASP.Net

I was reading the SecureMe blog the other day (check out their hilarious avatars) and I came across a number of references to “input validation.” I concur with the assessment that failure to use proper input validations is the source of quite a few software flaws, and the number of Cross Site Scripting and SQL Injection vulnerabilities could be minimized if proper input validation were used. The author mentioned how ASP.Net makes input validation easier, so I will demonstrate how to do basic validation in ASP.Net using the Regular Expression Validation Component. For more information about using .Net validation components, refer to this MSDN Library Article. I also came across a short but interesting academic paper placing blame on the failure of the academic community to instill proper fundamentals in developers. I believe this to be true. Incidentally, Slashdot has an article about individuals who learn to program in Visual Studio not getting proper programming fundamentals.

Input Validation is the process of confirming that the input into an application is valid, and handling cases where the input is not valid. The textbook CS example is checking for type errors. For example, a prompt asking for an integer value and the user providing a text string such as a name would result in an invalid type error. There are other types of validation errors, such as the dreaded buffer overflow. Receiving input that is passed directly to a backend system like a database can lead to unexpected results and subsequently to a SQL Injection vulnerability. Web Applications provide a larger layer of complexity than traditional applications due to the separation of the client interface and the backend servers. For Web applications, input validation has to be done on the client side as well as the server side, and also ensure that the inputs sync to prevent client side modification in the absence of server side checking. This added complexity is one of the many reasons I detest using Web interfaces for applications unless absolutely necessary.

In this example I will build a simple C# ASP.Net page in Visual Studio .Net 2002 that will query the employee table of my local database for an employee record with a matching ID. Results will be put into a listbox. The Employee ID is 10 characters, and can only contain numeric characters. There are no alpha characters or special characters.

Before I start, I want to demonstrate the completed application without input validation. You can see where I used a specially crafted input string to return more than just the one employee (the string I used is “ or nm_emp_last like ‘Wa’ –”), in this case all employees whose last names start with “WA”. Had this been a authentication form, I could log in with invalid credentials, or just create havoc with the database.

Lets start building the application to include the validation from the ground up. I will start by going up to File, New, Project, and creating a new project as illustrated below.



I will create a form with a Label, a Textbox, a Listbox, and a Submit button just like in the picture to the below:



I will then add a Regular Expression Validation component. I have a love/hate relationship with regular expressions. I love how powerful regular expressions are, but I hate how difficult they are to read, especially for someone who is not familiar with them; and in my opinion they violate quite a few rules of proper programming techniques. So I will keep my regular expression simple and avoid using any fancy script-fu. The input validation component is indicated in red in the picture above.

I will change the text to read “Invalid Input”, and use the validation expression of \d{10}, which will allow only 10 numeric characters. See the picture below:




Under the ControlToValidate property, I select the txtEmployeeID control



I will add the code depicted below to the cmdSubmit_Click function. This code will create an ADO.Net connection to an Oracle database, query the database with our filled in value, and add all the results to the results listbox. Exception handling is omitted for brevity:

private void cmdSubmit_Click(object sender, System.EventArgs e)
{
     if (Page.IsValid)
     {
          //Local Variables for working with Oracle to retrieve my data.
          System.Data.OleDb.OleDbConnection oraConnect = new System.Data.OleDb.OleDbConnection();
          System.Data.OleDb.OleDbCommand oraCommand = new System.Data.OleDb.OleDbCommand();;
          System.Data.OleDb.OleDbParameter oraParam = new System.Data.OleDb.OleDbParameter("empId", System.Data.OleDb.OleDbType.VarChar, 10);
          System.Data.OleDb.OleDbDataReader oraResults;

          //Set the database connection string and open up the connection
          oraConnect.ConnectionString = "Provider=OraOLEDB.Oracle.1;Password=test1234;Persist Security Info=True;User ID=test;Data Source=test";
          oraConnect.Open();

          //Setup up the database command. First set the conneciton object for
          //the command, the set the command type to test. Then, set the query
          //for retrieving the employee from the database, finally, add the
          //parameter we will be using
          oraCommand.Connection = oraConnect;
          oraCommand.CommandType = CommandType.Text;
          oraCommand.CommandText = "select nm_emp_last ', ' nm_emp_first Name from employees where no_emp = ?";
          oraCommand.Parameters.Add(oraParam);

          //Set the parameters value then execute the query, closing the connection
          //behind it
          oraCommand.Parameters["EmpID"].Value = txtEmployeeID.Text;
          oraResults = oraCommand.ExecuteReader(CommandBehavior.CloseConnection);

          //Traverse through the results, adding to the listbox all matches
          while (oraResults.Read())
               lstResults.Items.Add(oraResults["Name"].ToString());

          //close the connections
          oraResults.Close();
          oraConnect.Close();

          Response.Write("<script>alert(\"SQL Command: " + oraCommand.CommandText + " \");</script>");
     }
     else
          Response.Write("<script>alert(\"Invalid input found, ignoring request\");</script>");
     }
}


The program is complete. I would like to test my Input Validation control, so I save and run the project. I can see that the value of txtEmployeeID is already filled in the Textbox, so I click cmdSubmit. Once I do, I can see that the Invalid Input message appears due to the alpha characters.



Is this a client side validation at this point, or a server side validation? In order to test this, I will capture a session in Ethereal to try and investigate if is being done client side or server side. Although I can get the WebUIValidaton.js file, I feel it is too much work to try and kludge through it, and instead I instead decide to test by saving the POST session and modifying the parameter for txtEmployeeID with something that should fail validation. In order to do this, I find the POST session header as indicated under the Info column, then I right mouse click on it and select Follow TCP Stream. I make sure that ASCII is selected as the format, and click on save as, and save the session as bypassValidation.txt. I then edit the saved text file, remove all the server response information, and modify the txtEmployeeID to read “’ or 1=1 --”, which if successful, will attempt to pull all data from the table. (Thanks to this site for the SQL Injection example. I put the link to the print version to spare you the advertising). The modified example is below.

POST /SearchForEmployees/WebForm1.aspx HTTP/1.1
Accept: image/gif, image/x-xbitmap, image/jpeg, image/pjpeg, application/vnd.ms-powerpoint, application/vnd.ms-excel, application/msword, */*
Referer: http://john/SearchForEmployees/WebForm1.aspx
Accept-Language: en-us
Content-Type: application/x-www-form-urlencoded
Accept-Encoding: gzip, deflate
User-Agent: Mozilla/4.0 (compatible; MSIE 5.01; Windows NT 5.0)
Host: john
Content-Length: 273
Connection: Keep-Alive
Cache-Control: no-cache
Cookie: ASP.NET_SessionId=gmyrcwf1hhw0porjguqsnqeo

__VIEWSTATE=dDwxOTg4MTczMDcyO3Q8O2w8aTwxPjs%2BO2w8dDw7bDxpPDQ%2BOz47bDx0PHQ8O3A8bDxpPDE%2BO2k8Mj47PjtsPHA8V2FyZCwgSm9objtXYXJkLCBKb2huPjtwPFdhcmQsIEpvaG47V2FyZCwgSm9obj47Pj47Pjs7Pjs%2BPjs%2BPjs%2BJlE8BGSK6UaTCuPYd%2Bdr1fm5whg%3D&txtEmployeeID=' or 1=1 --&cmdSubmit=cmdSubmit

What I am going to do is connect to the Web server via Netcat and pipe in the above text file. Below is the DOS session for that attempt

C:\tmp>type bypassValidation.txt c:\nc localhost 80
HTTP/1.1 100 Continue
Server: Microsoft-IIS/5.0
Date: Thu, 27 Oct 2005 20:50:59 GMT
X-Powered-By: ASP.NET

HTTP/1.1 200 OK
Server: Microsoft-IIS/5.0
Date: Thu, 27 Oct 2005 20:50:59 GMT
X-Powered-By: ASP.NET
Connection: close
X-AspNet-Version: 1.1.4322
Cache-Control: private
Content-Type: text/html; charset=utf-8
Content-Length: 3100

<script>alert("Invalid input found, ignoring request");</script>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
<HEAD>
<title>WebForm1</title>
<meta content="Microsoft Visual Studio 7.0" name="GENERATOR">
<meta content="C#" name="CODE_LANGUAGE">
<meta content="JavaScript" name="vs_defaultClientScript">
<meta content="http://schemas.microsoft.com/intellisense/ie5" na
me="vs_targetSchema">
</HEAD>
<body MS_POSITIONING="GridLayout">
<form name="Form1" method="post" action="WebForm1.aspx" language
="javascript" onsubmit="if (!ValidatorOnSubmit()) return false;" id="Form1">
<input type="hidden" name="__VIEWSTATE" value="dDwxOTg4MTczMDcyO3Q8O2w8aTwxPjs+O
2w8dDw7bDxpPDQ+Oz47bDx0PHQ8O3A8bDxpPDE+O2k8Mj47PjtsPHA8V2FyZCwgSm9objtXYXJkLCBKb
2huPjtwPFdhcmQsIEpvaG47V2FyZCwgSm9obj47Pj47Pjs7Pjs+Pjs+Pjs+JlE8BGSK6UaTCuPYd+dr1
fm5whg=" />

<script language="javascript" src="/aspnet_client/system_web/1_1_4322/WebUIValid
ation.js"></script>


<span id="Label1" style="Z-INDEX: 101; LEFT: 17px; POSIT
ION: absolute; TOP: 16px">Enter the Employee ID:</span><input name="txtEmployeeI
D" type="text" value="' or 1=1 --" id="txtEmployeeID" style="Z-INDEX: 102; LEFT:
165px; POSITION: absolute; TOP: 17px" /><input type="submit" name="cmdSubmit" v
alue="cmdSubmit" onclick="if (typeof(Page_ClientValidate) == 'function') Page_Cl
ientValidate(); " language="javascript" id="cmdSubmit" style="Z-INDEX: 103; LEFT
: 21px; POSITION: absolute; TOP: 51px" /><select name="lstResults" size="4" id="
lstResults" style="height:75px;width:325px;Z-INDEX: 104; LEFT: 345px; POSITION:
absolute; TOP: 16px">
<option value="lstResults">lstResults</option>
<option value="Ward, John">Ward, John</option>
<option value="Ward, John">Ward, John</option>

</select>
<span id="RegularExpressionValidator1" controltovalidate
="txtEmployeeID" errormessage="Invalid Input" isvalid="False" evaluationfunction
="RegularExpressionValidatorEvaluateIsValid" validationexpression="\d{10}" style
="color:Red;Z-INDEX: 105; LEFT: 165px; POSITION: absolute; TOP: 47px">Invalid In
put</span>
<script language="javascript">
<!--
var Page_Validators = new Array(document.all["RegularExpressionValidato
r1"]);
// -->
</script>


<script language="javascript">
<!--
var Page_ValidationActive = false;
if (typeof(clientInformation) != "undefined" && clientInformation.appName.indexO
f("Explorer") != -1) {
if (typeof(Page_ValidationVer) == "undefined")
alert("Unable to find script library '/aspnet_client/system_web/1_1_4322
/WebUIValidation.js'. Try placing this file manually, or reinstall by running 'a
spnet_regiis -c'.");
else if (Page_ValidationVer != "125")
alert("This page uses an incorrect version of WebUIValidation.js. The pa
ge expects version 125. The script library is " + Page_ValidationVer + ".");
else
ValidatorOnLoad();
}

function ValidatorOnSubmit() {
if (Page_ValidationActive) {
return ValidatorCommonOnSubmit();
}
return true;
}
// -->
</script>


</form>
</body>
</HTML>

I can see that the script command to alert that an invalid input occurred is there, highlighted in red. I redirect the output to a file and open in IE.

By the pop-up message that was returned, I can tell that the processing is done server-side and the error message is returned to the client via the resulting HTML page. This is good because it helps to prevent an outsider from manipulating the client page to circumvent my validation routine. Had this been done client side, I would still have the alert message with the SQL that I used for debugging. While there are more in-depth tests, I am fairly confident that the Regular Expression Validation control is working. However, if you are rolling out an application, you should never assume that you are completely secure. Remember that there is always someone out there smarter than you. In time someone is bound to find a flaw in the .Net validation components, but these components are a good start toward securing your web applications from input validation attacks if you are using the .Net platform.

Birt updates from the Birtworld Site

I was jut reading on the BirtWorld blog, which is written by two employees from Actuate, that they are in the process of revamping the examples section of the Birt website. I have written about Birt previously on this site, and given examples of reports and how to deploy them. So far I am impressed with Birts offerings, and am very pleased with Actuates commitment to the project. Also, congratulations to the Birt team for their recent article on TechForge regarding using Birt with Hibernate. It is good to see Birt getting some press. I believe Birt is a powerful project and has the potential to really open eyes towards FOSS as an alternative desktop platform. This article is the first time I have heard of Jason Weathersby refered to as the Birt evangelist. The only other time I have ever heard of someone refered to as an evangelist is Terry Quantrani for Rational Rose, so I will be sure to give him a hard time next time I talk to him.