Experimental Playground - SQL

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

Mon, 12 Jul 2010 19:33:00 -0400

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

Thu, 01 Jul 2010 16:20:00 -0400

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

Download one of those two, which ever you want. I'm using 3.0
Extract to a directory, any will do we're going to move the files when we're done anyway
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
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\"
RESTART COLDFUSION

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

Give it a name,
and then select "OTHER" from the drop down list
and click ADD
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
Driver Class will be
com.microsoft.sqlserver.jdbc.SQLServerDriver
Driver Name (you can name it whatever you want) SQL Server 2008 JDBC
Username: database user name
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

Wed, 30 Jun 2010 18:51:00 -0400

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



    SELECT name
    FROM actors
    WHERE id = 






...

...

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!

Thu, 20 May 2010 01:07:00 -0400

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

Sat, 15 May 2010 13:51:00 -0400

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:

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
How long is the string you are constructing?

Knowing this information, we can plug this into the formula n^r 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

36
1296
46656
1679616
60466176
2176782336
78364164096
2821109907456
101559956668416
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?

Fri, 14 May 2010 11:15:00 -0400

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.


CREATE TABLE [dbo].[alphabet](
	[letter] [char](1) NOT NULL,
 CONSTRAINT [PK_alphabet] PRIMARY KEY CLUSTERED 
(
	[letter] ASC)
)

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.


CREATE TABLE [dbo].[dictionary](
	[word] [varchar](50) NOT NULL,
 CONSTRAINT [PK_dictionary] PRIMARY KEY CLUSTERED 
([word] ASC)
)

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.


SELECT
   a.letter + b.letter + c.letter + d.letter + e.letter + f.letter + g.letter as word
FROM
   alphabet as a,
   alphabet as b,
   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

Mon, 01 Feb 2010 21:29:00 -0400

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

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

Thu, 03 Dec 2009 15:43:00 -0400

excerpt from the site...


HAI!
I'M IN UR `table`
PLZ MAKES `column` LIEKS `value`
I CAN HAZ `column` NO WAI LIEK `value`
KTHNXBYE

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

Whacky server restarts...Coldfusion XML

Tue, 28 Jul 2009 02:00:00 -0400

So I've been working on a project for the past couple of days that involves fetching XML from a remote source and parsing it, then throwing it into a SQL Server database. Easy enough right?...wrong! Every few iterations of the processing loop causes the server to do a reboot. I at first thought it might have something to do with Coldfusion 8's XML parsing, however after setting up a virtual environment, I have narrowed it down to SQL Server. The server in question is running SQL Server 2005 SP3 64bit. The plot thickens...