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...

Sneaky Bastards

Yesterday I discovered one of the reasons I wouldn't make a good crook. I'm just not devious enough. Sure, I'm devious in the big things ;-) , but I've never even thought of being devious in the little things, which can add up.

Here's how I discovered this.

A few weeks ago, I read a post in Bruce Schneier's blog called "A Security Market for Lemons". It makes some great points about information security products. Specifically he discusses why bad products often outsell good products. The explanation of how this happens is very good, but the cause is what interested me. The cause being that the consumers of these products often do not have enough information about the type of product they are evaluating, what it does and what its value is. This is compounded by the consumer knowing even less about the specific vendor's implementation of the class of product. The one thing the customer does know is price. From there, its a simple equation... The buyer thinks, "All the products seem the same to me. I'll buy the cheapest".

Here in lies the problem. Few of us really internalize the concept of "you get what you pay for". Almost invariably, the cheapest to buy was the cheapest to design and cheapest to build. i.e. crap. Schneier goes on to paraphrase Nobel Prize winner George Akerlof, describing how the sales of the low price, low quality products cause the vendors with better products to lower prices in order to compete. These cuts lower the average price in the market which can cause a downward spiral, eventually shutting out the good vendors entirely. Akerlof's paper, "The Market for Lemons" makes this point much better than me using an example of used cars.

Back to what interested me. The cause of all this, buyers who are as well informed as the sellers is called Asymmetrical Information. When one party in a transaction has much more or less information, there is a great chance that one party will take advantage of the other. In the insurance industry, a consumer with grave illness can hide it until they are insured and then take the insurance company for a ride. This is why so many carriers exclude preexisting conditions. I remembered reading, in Freakonomics, that real estate agents, when selling their own homes, will leave the home on the market for longer and make more money than comparable homes they have sold for their clients. I realized that I buy things all the time without the benefit of enough information. I decided to read a bit more so I hunted down The Market for Lemons. Turns out its hard to find on the web in its entirety. I won't tell you where to look as I'd hate for the copyright police to take it down.

Now to my point... finally... In Akerlof's work, I came across this tidbit:

In India, for example, under the Export Quality Control and Inspection Act of 1963, "about 85 per cent of Indian exports are covered under one or the other type of quality control." Indian housewives must carefully glean the rice of the local bazaar to sort out stones of the same color and shape which have been intentionally added to the rice.

Get this!!! People would add rocks to rice, which is already the most produced and abundant food source on the planet, just to make a few more bucks!

I realize of course that the great supply of rice drives the price of rice down and thus one needs to sell a lot in order to make money, but that is just more sneaky that I've ever considered being.

I'll never get rich by being sneaky, I just can't compete.

VMWare Static MAC addresses

Today I needed to create a VM where the Ethernet MAC address does not change when the VM is moved to another machine or copied. I'm working with NeoWare's Device and Image Manager software. Their licensing is tied to the machine's MAC address. After a bunch of googling and trial and error, discovered how to set a static MAC address in VMWare workstation 6.

The VMWare document on this is a bit wordy and leaves a little bit to trial and error. http://kb.vmware.com/selfservice/microsites/search.do?cmd=displayKC&externalId=507

Here's what you need to know. The VM must be powered down and VMware must be closed.

You will need to locate and edit (notepad) the VM's .vmx file.
All references to ethernetN should be changed to match the ID of the Ethernet address who's MAC address you are setting. This is probably ethernet0

Remove the two lines that begin with the following from the configuration file:
ethernetN.generatedAddress
ethernetN.generatedAddressOffset

Change the line:
ethernetN.addressType = "generated"

to:
ethernetN.addressType = "static"


Add the following line to the configuration file:

ethernetN.address = 00:50:56:XX:YY:ZZ

In this line, XX must be a valid hexadecimal number between 00h and 3Fh, and YY and ZZ must be valid hexadecimal numbers between 00h and FFh. Because VMware Workstation virtual machines do not support arbitrary MAC addresses, you must use the above format.

Start the VM and you are done.