which executable am I running??

Lately I have had a few occasions where I needed to figure out what version of an executable was running on my windows machine. For executables that stay open, I usually use pslist or a host of other tools. For commands that start and stop quickly Windows has no tool. Having used the *NIX command which, I realized that windows makes me sad for not including this.

I recently took up c# so I decided to write a windows version of which. I’m not much of a developer and don’t have a full spec on which, so I wrote my own spec. Windows uses environment variables PATH and PATHEXT to locate an executable if it is not in the current directory. My version of which does the same thing. It tries the current directory cycling through the possible extensions and then on through the PATH in the same manor until it finds the executable or tries all the combinations. If the file is found, the full path and creation date are returned.

Feel free to fix my code or take the tool.

Binaries

Source

.NET 2.0 is required.

Converting XLS to CSV with VBS

I'm working on a project that requires me to write some data to Active Directory from a group of excel files. I'm automating a process that requires a human to edit the excel files manually, convert them to CSV, then use MS Access to do some work on them, then run a script on the output. All of this is just basic data manipulation, there is just no good reason to have a human start a process, wait for an output and then start another and so on.

I've never worked with Excel in VBS and in VBS comma separated values are really easy to work with, so I was hoping to find an existing script or executable that I could call to get that out of the way. It turns out that there are lots of bits and piece of code that people sell for what should be a simple task. I don't pay for such things, so I did a bit of digging. There is plenty of VBS code out there for working with Excel data. I got what I needed, and don't really need to convert to CSV.

I figured I'd create the script to convert from XLS or XLSX to CSV anyway, cause clearly this should be free.

I came across Greg Thatcher's page that has a script to output an excel document to the screen once cell at a time in a long column. From there its a short trip to putting the data in to a CSV.

The script can be found here or below. It's a reminder that most of scripting is actually error and exception handling. I have left Greg's comments and highly commented my code as well.

Once caveat, I'm sure the CreateObject("Excel.Application") code only runs if you have Excel installed.

http://s3.amazonaws.com:80/markgamache/xls2csv.vbs

Option Explicit
' We use "Option Explicit" to help us check for coding mistakes

'get the current dir. Excel objects dont read from current dir so must create explicit ref
Dim currentDirectory
currentDirectory = Left(WScript.ScriptFullName,(Len(WScript.ScriptFullName)) - (Len(WScript.ScriptName)))


' the Excel Application
Dim objExcel
' the path to the excel file
Dim excelPath
' how many worksheets are in the current excel file
Dim worksheetCount
Dim counter
' the worksheet we are currently getting data from
Dim currentWorkSheet
' the number of columns in the current worksheet that have data in them
Dim usedColumnsCount
' the number of rows in the current worksheet that have data in them
Dim usedRowsCount
Dim row
Dim column
' the topmost row in the current worksheet that has data in it
Dim top
' the leftmost row in the current worksheet that has data in it
Dim leftm

Dim Cells
' the current row and column of the current worksheet we are reading
Dim curCol
Dim curRow
' the value of the current row and column of the current worksheet we are reading
Dim word
'the cell data

Dim objFSO
Const ForAppending = 8

'make sure there is a cmd line arg an only one
If Wscript.Arguments.Count <> 1 Then
WScript.Echo("Only one argument please. An XLS or xlsx file")
ELSE
'get the command line argument
Dim infile
infile = Wscript.Arguments(0)
'get extension. chars after last period
Dim fileext
fileext = Right(infile, Len(infile) - InStrRev(infile, "."))


'make sure the cmd arg is an excel file
'erros etc for filetypes
Select Case fileext
Case "xls"
ctvnow() 'call the convert function
Case "xlsx"
ctvnow() ' call the convert function
Case ELSE
WScript.Echo("The input file must be an .xls or .xlsx file")
End select
End If
'end main loop all functions below


'this is the main conversion sub
Sub ctvnow()
Dim logfile 'this is the output file
logfile = currentDirectory & Left(infile, InStrRev(infile, ".")) & "csv"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Dim objLogFile
Set objLogFile = objFSO.OpenTextFile(logfile, ForAppending, True)

' where is the Excel file located? currentDirectory lets us locate the file where the script was run
' could add chekc to the infile to see if its includes a path and act accordingly
excelPath = currentDirectory & infile

' Create an invisible version of Excel
Set objExcel = CreateObject("Excel.Application")

' don't display any messages about documents needing to be converted
' from old Excel file formats
objExcel.DisplayAlerts = 0

' open the excel document as read-only
' open (path, confirmconversions, readonly)
objExcel.Workbooks.open excelPath, false, true

' How many worksheets are in this Excel documents
workSheetCount = objExcel.Worksheets.Count


'For counter = 1 to worksheetCount would loop thorough all sheets. We only wan tthe first
For counter = 1 to 1

WScript.Echo "-----------------------------------------------"
WScript.Echo "Reading data from worksheet " & counter & vbCRLF
' set to first worksheet
Set currentWorkSheet = objExcel.ActiveWorkbook.Worksheets(counter)

' how many columns are used in the current worksheet
usedColumnsCount = currentWorkSheet.UsedRange.Columns.Count
' how many rows are used in the current worksheet
usedRowsCount = currentWorkSheet.UsedRange.Rows.Count

' What is the topmost row in the spreadsheet that has data in it
top = currentWorksheet.UsedRange.Row
' What is the leftmost column in the spreadsheet that has data in it
leftm = currentWorksheet.UsedRange.Column


' create Array For the row. this assumes a max of 255 columns in the row. for XLSX this could be higher
'should add check below to throw error if the sheet had too many columns
Dim rowa(255)

Set Cells = currentWorksheet.Cells
' Loop through each row in the worksheet
For row = 0 to (usedRowsCount-1)
' Loop through each column in the worksheet

' the location in the array we are at, corresponds to a column place in the current row
Dim arloc
arloc = 0
For column = 0 to usedColumnsCount-1
' only look at rows that are in the "used" range
curRow = row+top
' only look at columns that are in the "used" range
curCol = column+leftm
' get the value/word that is in the cell
word = Cells(curRow,curCol).Value

'covert " to ' this is because quotes are a text qualifier in CSV so cells can contain a comma
If InStr(word, Chr(34)) > 0 Then
word = Replace(word, Chr(34), Chr(39))
End if
'if the cell contains a comma, wrap the cell in " so that the cell isn't artificially split
If InStr(word,Chr(44)) > 0 Then
word = chr(34) & word & chr(34)
End if

'put the cell in to the array one cell at at time, stripping leading and trailing white space
rowa(arloc) = trim(word)
arloc = arloc + 1 'increment the column to get next cell
Next
Dim rline
Dim rline2
'we turn the array into a line of comma separated values
rline = Join(rowa,",")

'removes extra commas at the end of array as it is 255 wide
rline2 = removecomma(rline) 'calls customer function below
objLogFile.writeline(rline2) ' write the line of CSV
Next

' We are done with the current worksheet, release the memory
Set currentWorkSheet = Nothing
Next

objExcel.Workbooks(1).Close
objExcel.Quit

Set currentWorkSheet = Nothing
' We are done with the Excel object, release it from memory
Set objExcel = Nothing
WScript.Echo("File converted")
End sub


Function removecomma(csvline)
Dim lastchar
lastchar = 1
'we'll check to see if the last char of the line is a comma
Do Until lastchar = 0
If right(csvline, 1) = Chr(44) Then ' check for comma
csvline = Left(csvline, (Len(csvline) - 1)) ' strip a comma from the end
Else
lastchar = lastchar - 1 ' no more commas set lastchar to 0 so we can get out of loop
End if
Loop
removecomma = csvline ' return the new line
End Function

Enjoy it, leverage from it, improve it or ignore it...

Business Requirements for Security Controls

Before I start, this is not going to be an exposition on the topic requirements or controls, it's just a look at an interesting and well thought out business case and use of a control.

I was confused by a change in my mortgage the other day so went to my county website to look up what I pay in property taxes. I regularly use King County's I-Map to research such things.

The site had made a change and added a Captcha. Captchas are those screwy pictures of words that are hard to read. A computer can't make sense of them, so it proves that a human actually entered that data. This is used for two main reasons: to stop automated data mining of non-authenticated pages and to stop automated brute force attempts against a page that includes user name and password field. Forcing these things to become manual processes can be very valuable.

A quick aside on how that might be done. Take this URL,
http://www5.kingcounty.gov/KCGISReports/property_report.aspx?PIN=9208900079
if you were to follow it, you'd see on the page that they URL input variable, PIN=9208900079 happens to match the parcel number of the lot. Knowing this, you can write a simple script to query and log each parcel number in the county. This is all public record, so there isn't much of a reason to try and stop this, with the exception of the resource consumption.

Back on track, first the technical part... I clicked on the link to my parcels tax information and was presented with a Captcha where there hadn't been one before.
https://payments.metrokc.gov/metrokc.ecommerce.propertytaxweb/RealProperty.aspx?Parcel=9208900079
I entered the words, received my tax information, and my mortgage confusion went away.

The confusion was immediately replaced by curiosity. Security controls have a way of doing that to me. I noticed that they resulting page address included an input variable.
https://payments.metrokc.gov/metrokc.ecommerce.propertytaxweb/RealProperty.aspx?Parcel=q1iPffuHa3Qx3uPtyG%2fb3A%3d%3d
Parcel=. It seemed odd to me that the variable was used to request the page. I would have expected my session state to be maintained server side or at least in a cookie, where it's not seen.

Looking at the variable data, I immediately noticed the mixed case letters, numbers, and the trailing %3d%3d. The %3d%3d pointed me to URLEncoded data representing ==, which tells me that the whole value is a base64 encoded string. I've seen a lot of silly stuff lately, so I decoded the string. I'd have suspected it no matter what, but == is a dead giveaway.

I decoded it and I got, hex ab 58 8f 7d fb 87 6b 74 31 de e3 ed c8 6f db dc
which is gibberish as ASCII. I took a minute to reverse the nibbles and byte order and a few othe things. There was nothing interesting there.

I decided to see if there was a discernible pattern, so I looked up the next parcel number 9208900080. That came back q1iPffuHa3ScKwwlW92daw%3d%3d, which is ab 58 8f 7d fb 87 6b 74 9c 2b 0c 25 5b dd 9d 6b in hex.

Here I noticed that the first and second value were similar.
ab 58 8f 7d fb 87 6b 74 31 de e3 ed c8 6f db dc
ab 58 8f 7d fb 87 6b 74 9c 2b 0c 25 5b dd 9d 6b

The strings are 128 bits and the first 64 bits are the same, but the last 64 vary considerably, even tough they are only one base ten digit apart. I'm no cryptographer, but after a bit more looking, I've decided that they are using a block cipher that uses 64 bit blocks. As only one byte at the end of the data is going to change, 9208900079 to 9208900080, the first block would not change and thus remain the same, using the same key. If the system encrypting the parcel number and decrypting the number are the only ones in possession of the key, this is a way to securely pass the data between them, via the web browser. It however is subject to replay. I may not know the key, but I don't need it to enter the cipher text again.

I decided to check my assumption that this URL was all I needed to link to the record, so I used cURL to get the URL. This means no session state and no cookies are involved. Sure enough, I got the tax record back.

I decided to email the county, to see if this was intentional. It seemed to me that this was an oversight of some sort.

The county was prompt in responding that this was actually part of the requirement. They wanted to limit data mining of the tax information, but wanted the tax payers to be able to save a link to the page for their records.

My assumption that this was a fumbled hand off between the Captcha and session handling in the back end system was wrong.

This is a great example of a well thought out use of a security control. It meets the requirement of stopping automated data collection while making the system more friendly for the end-users.

Kudos to the King County web team!

Looking Inside Netscaler and CAG Ent SSL Payload

At some point, you may need to see what is happening inside the SSL posted to your Citrix Access Gateway Enterprise or Netscaler appliance. I had that need today and the web was not much help. There was one Citrix Knowledge Center article on capturing traffic, but looking at SSL handshakes isn't very useful in my world.

I put on my ninja hat and went to town, as they say.
The first step is to determine what certificate is being used by your appliance. You may have more than one certificate if you have multiple v-servers. Login to the appliance and go t SSL, under configuration. You will need to know the names of your certificate file and key file.



In the screen above note the certificate file is /nsconfig/ssl/bpcag.cert and the key file is /nsconfig/ssl/bpcag.key

1. SSH into the Netscaler. Putty will work
This is done via the management IP as nsroot. You will need to type shell and hit enter to get a useful shell.

2. change to the /nsconfig/ssl directory

3. Execute the following commands

4. openssl pkcs12 -export -inkey bpcag.key -in bpcag.cert -out bpcagout.pfx
If you already have a pfx from a certificate vendor, you can skip this step and reference the vendor pfx in the command on line 6.

5. You may be asked to enter the key decrypt password. If you do not have this, you are out of luck and will need to get a new cert and keep the password. You will be asked to create an export password. Make sure to remember this.

6. openssl pkcs12 -in bpcagout.pfx -out clearpemout.txt -nodes

7. You will be asked for the password that you created with the command in line 4. The resulting file clearpemout.txt is your certificate and RSA private key in clear form. If this is a production key, make sure to keep a close watch on this file. Your SSL is only as secure as the private key.

8. Use Filezilla or WinSCP to get the file from the netscaler location /nsconfig/ssl, and copy it to your workstation. I like Filezilla better...

9. Create a text file containing only the private key section of clearpemout.txt to be read by Wireshark.
This text is an unencrypted copy of the private key, so handle the file with caution
The key looks like this.
---BEGIN RSA PRIVATE KEY---
MIICXQIBAAKBgQDQVLGU+2OWUkD03PGkHnR1LrnMqKchtEXnNX49+8wrvTBlkisi
+JSnf6XhDoJRQggrC/IIS/K6ptDWaTqDC1hX4kQnRLa42ogSHVZHRSXrmRYjKCR4
R7N+E/sxuhjaf8zmcwf8UxuM4Y1b4UB4/3L48yXDdVBW
---END RSA PRIVATE KEY---

10. Open Wireshark and go to Edit > Preferences.

11. Find SSL under Protocols

12. Make sure the check boxes are checked.

13. In the field RSA keys list, enter your key and server data. A typical string looks like this
70.103.221.144,443,ssl,c:\cert.key;10.0.0.194,443,ssl,c:\cagx.txt;10.0.0.193,443,ssl,c:\cagx.txt;10.0.10.4,443,ssl,c:\cagx.txt

It must reference the IP address of the V-server you are sniffing, the port, the protocol (SSL), and the full path to the decrypted key file. In the example about, each separate entry is delimited by a semi-colon. If you are using alternate ports for SSL make sure to pay attention to the string. Each combination of protocol, IP, port and key file must have its own entry.

Now you are ready to read some data!!!

To sniff the traffic from the CAG/Netscaler, you must SSH in and use the command line

nstrace.sh -sz 1514 -tcpdump 1

Don't use the native tcpdump, as its fought with peril.

The command will create numbered capture files, rolling to the next every 3600 seconds. The files will ve dumped into /var/nstrace

Start your trace, generate your traffic, then stop the trace ctrl+c, then copy the trace files to your machine and open them in wireshark. To view the SSL payload in wireshark, you must find the initial SSL handshake, right click on one of the handshake packets and select Follow SSL Stream. If you don't find the handshake, you may have started your trace after the handshake occurred.

To filter out some of the pesky extraneous data (there will be a lot), you can use the filter bar. A common filter would be (tcp.port eq 443). This only shows traffic on tcp port 443. You may want to constrain it further, if you have many active v-servers with (ip.addr eq 10.0.10.4), using the appropriate v-server IP. You can combine the filters (tcp.port eq 443) && (ip.addr eq 10.0.10.4)

Enjoy...