OFCP – Offensive Security Certification

I joined a security mailing list some time ago, recently they got to talking about what the ‘best’ certification to get for security training is. A certification to be a professional hacker? Who knew there was such a thing? One of the most notable ones they mentioned was OFCP (Offensive Security Certification). I took a look at their website, there are a couple good demos on there and the price to get certified appears reasonable enough. Apparently passing the test is difficult with “90% of people failing the first time”, or so says one of the folks on the mailing list.

Check it out:
http://www.offensive-security.com/

EDIT:
Here are a couple more sites they shared that look interesting:
Hacker High School
HackBloc

Great ‘geek’ books

I’ve spent some time reading the demo chapters of a number of the books on this site, they’re great! The one book tells you how to build your own IR receiver for hooking up to a serial port, now that I want to try!
http://www.nostarch.com/

How to reset the Administrator password in Windows

Forgot your Administrator password? Or locked it out due to trying the wrong one too many times? This is the place for you.

I worked for a client recently that made a classic personnel mistake. After parting ways with a disgruntled employee they failed to change the Administrator password on their production server. This server is available publicly via remote desktop. As I was working on a issue for them one day I stopped being able to log in. It turns out their disgruntled employee had logged on and re-set their password, Oops!

If you find yourself in such a conundrum here is how I got them back into their machine. There is a great (free) app called PogoStick that you download as an ISO and burn to a CD, it’s available here: http://pogostick.net/~pnh/ntpasswd/. Just download it, burn it to a CD and then insert into the affected machine. Couple of notes here:
1) Be sure the machine your fixing is set up to boot from your CD rom first. This is done by going into your bios and finding the section related to boot devices. Every bios is different so that’s as specific of instructions as I can give.
2) Read through the screens. The app can do a number of things but many of them are experimental and some only work on certain versions of Windows. Play it safe and only reset the password to blank, setting it to a value didn’t work for me. Also be sure to unsuspend the id if it’s not already.
3) This one is important, and not so obvious in my opinion. After you’ve fixed the id you’re not done. You have to exit using the menu. It will eventually ask if you want to commit the changes to which you’ll answer (y)es.

That’s it. In case you’re not paying enough attention, you can now re-set the Administrator password from blank to some useful value in Windows itself. No need to use a command line app as I’ve heard some people have.

NOTE: If you need to reset an admin id on a domain controller that is an entirely different beast. The solution above works for local machine accounts only! If you want more info, or want to try to fix a account for a domain controller these look like useful websites. Caveat emptor though, it is possible to make the situation worse with some of these suggestions.
How to reset the Domain Admin Password under Windows 2003 Server
Unlocking Windows NT/2000/2003 Domain Controllers
Forgot your Windows password? No problems : Password resetting and recovering techniques

Syntax highlighting – Posting Source Code on WordPress

Crikey! I’ve finally found a good way to get syntax highlighting on my code posts on WordPress! Here is the WordPress article on the tag to use for syntax highlighting, thanks WP!

To summarize, to outline your code include this around it:

[sourcecode language="css"]
your code here
[/sourcecode]

where language can be many things (check the link for a full list), these are some:
sql, csharp, vb, xml, javascript, java, html, css, powershell

This code highlighter is pretty neat, it’s client side JavaScript that does the highlighting. Written by Alex Gorbatchev, available on his site here. I have noticed a couple bugs in the sql parser, but overall I quite like it.

SQL Server – Get data between two dates

This is the SQL to get data between two dates, I know lots of people find datediff() confusing so this is a nice cut and paste script to have handy:

select * from
tableName
where datediff(day,'2011-06-27 12:28:34.480',theDate)>=0 and
datediff(day,'2011-06-28 12:28:34.480',theDate)<=0

The first date in that string is the start date and the second is the end date. You can change the ‘day’ parameter to any amount you wish (second, month, year). You should always use datediff() to get the difference between dates in SQL Server as it will handle issues like 30, 31 and 28 day months, leap years, etc. datediff() is odd in that if you run this:

select datediff(day,'2011-06-27 12:28:34.480','2011-06-28 12:28:34.480')

It will give you a whole number, in this case 1. So when the left side is less than the right side we get positive numbers. This seems backwards to the more natural seeming syntax of greater than / less than:
select * where date1 > date2
Unfortunately you can’t use greater than / less than, this is because even though they work, they will compare down to the nanosecond, which is not generally what we want.

If you need to do this in your .Net dataset here are two examples of how to do it. Two because the way you write your query will vary depending on what database driver you’re using.

For SQL Server 2000:

select * from
tableName
where datediff(day,?,theDate)>=0 and
datediff(day,?,theDate)<=0

For SQL Server 2005+:

select * from
tableName
where datediff(day,@StartDate,theDate)>=0 and
datediff(day,@EndDate,theDate)<=0

Remember that if you have issues the default way to use this is with question (?) marks. Question marks were used to denote parameters all the way back to ADO in VB 6. The downside of using question marks is your parameters must be added to your code in the order they are in the query. (In Oracle they always have to be added in the order of parameters in the query, despite being named. Unless they’ve fixed that since I last used the Oracle driver).

Common Solutions for T-SQL Problems – MSDN

There are some great articles on MSDN on solutions to common problems people have (though the array solution would be better done with a Tally table). Here is their page with a list of common questions, I think every good developer should take the time to read through these.
Common Solutions for T-SQL Problems – MSDN

“Tally” tables or “Numbers” tables are pretty great

I recently read an article on ‘Tally’ tables. They are amazingly useful, if you have need to calculate anything in a looping fashion in SQL you really should look into them. The way a Tally table works is you create a table with unique numbers from 1 to x (where x is a very large number, 11,000 is suggested as it is enough numbers to store 365.25 days times 30 years), once you have this table you join it to your query for a super fast tally of numbers.

It seems bizarre at first, but the reason this concept is so fast is it doesn’t have to spend time calculating and waiting. A good example of a practical implementation of a Tally table is for use in situations where you want to pass an array to a sql statement, ever tried? SQL Server does not have the concept of arrays so passing one in requires a bit of a mashup. There are a few methods people will try to do this, one is a comma/semi colon delimited list, eg:
item1,item2,item3,etc
then in the stored proc they will have a loop that finds each comma and execute sql on it.
- or -
They will write dynamic sql, like so:

string sMyList = "'item1','item2','item3'" //in practice this would be constructed in a loop
string sSQL = "select column1, column2 from tableX where column1 in (" + sMyList + ")"

exec sSQL

Both of these methods have issues, the first query one is slow (it’s looping through items), the second query will never result in the SQL engine pre compiling it (the query is different on every run) which also makes it slow. Not to mention that both methods can have SQL injection issues.

So both methods have issues, so what then do we do to make a tally table work?

For example consider this SQL statement:

DECLARE @Parameter VARCHAR(8000) 
    SET @Parameter = ',Element01,Element02,Element03,' --Note the requirement to have commas both on the end and beginning of the parameter
 Set Nocount ON
 SELECT SUBSTRING(@Parameter,N+1,CHARINDEX(',',@Parameter,N+1)-N-1)
   FROM dbo.Tally
  WHERE N < LEN(@Parameter)             
    AND SUBSTRING(@Parameter,N,1) = ',' 

This query is run for each row in our Tally table simultaneously, this means no looping, or waiting for computation and results, instead just instant results. What in essence it’s doing is saying “Get me all the rows in the Tally table where the number is less than the length of the parameter passed in.” At this point we have a list of numbers, 1,2,3,4,etc, the list is long enough that if every entry in it was a comma it would still parse the results. Once it has the list of numbers it uses it on each row simultaneously to figure out “is this character a comma? If so show everything after it up to the next comma”. It does this compare for every character in the string.

It’s blazingly fast, my post does not do the concept justice, but I felt why rewrite a post when it’s already been written so well by others? Here’s an article on the basics of Tally tables The “Numbers” or “Tally” Table: What it is and how it replaces a loop, and another article that builds on the concept, showing how to parse a string into multi dimensional array: Passing Parameters as (almost) 1, 2, and 3 Dimensional Arrays.

Thanks to SQL Server Central, parts of the code in this post are copied from the articles I’ve linked.

Internet Storm Centre

I found this site today, looks like it will be interesting to keep an eye on. The site appears to keep track of when the internet is having issues with malware. They spelled centre wrong though
http://isc.sans.edu/diary.html

How to write a Trigger in SQL Server

Just a quick post with a simple example trigger I put together. I’ve noticed allot of people seem to think that cursors are necessary to write a trigger, not so. In fact cursors are a bad way of doing things in general. They’re slow and run one command at a time, they loose out on the speed increase of running an update in batch. Al-Farooque Shubho touches on this concept in his article “Understanding “Set based” and “Procedural” approaches in SQL”.

Here’s the trigger, it’s written to audit changes on a date field. You need to create a table to handle the insert for this. Remember two things here:
1) The new table needs to have exactly the same data types (and null ability) of the parent table, but NOT the default values.
2) The primary key of the origin table will NOT be the same in the audit table. This is because a row can be changed multiple times, auditing those changes means the same primary key being inserted over and over. In my example I create a new column called EventOccuranceTime which is defaulted to getdate(). I then used the primary tables unique identifier plus the EventOccuranceTime as the PK in the new table. Note that triggers can seriously slow down your data access, putting a primary key on the audit table will slow down updates / inserts into the parent table more yet. If this speed reduction is an issue for you you may not want a primary key on the audit table, just take the hit when reading from the data (no index) instead of writing it with an index. I’ve included the table create script below as well:

CREATE TABLE [dbo].[SomeTable_audit](
	[ROWID] [int] NOT NULL,
	[EventOccuranceTime] [datetime] NOT NULL,
	[FromDATE] [datetime] NULL,
	[ToDATE] [datetime] NULL,
 CONSTRAINT [PK_audit] PRIMARY KEY CLUSTERED 
(
	[ROWID] ASC,
	[EventOccuranceTime] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[SomeTable_audit] ADD  CONSTRAINT [DF_SomeTable_audit_EventOccuranceTime]  DEFAULT (getdate()) FOR [EventOccuranceTime]
GO

Something to know about triggers, they have two special tables available in them. One is the rows inserted, the other the rows deleted. If you made updates then they are tables that hold all the rows that were updated (treating old data as deleted and new data as inserted), even if only one column was updated.
If you change this trigger to include inserts and deletes the join below will thwart that. You will need to change this join to allow left and / or right joins depending on inserts / deletes, or write multiple queries, one for inserts, one for deletes, one for updates. You can write more than one query in a single trigger.

create trigger dbo.audit_SomeTable on dbo.SomeOriginTable for update -- This could include insert and delete, mine only handles updates.
as
if update(desired_date) --if the desired_date field is changing do the insert
begin
--Code thanks to http://anthonystechblog.wordpress.com
--Feel free to use, change and re-distribute the code you find on my site,
--all I ask is you leave these these comments intact!
	insert into dbo.SomeTable_audit (rowid,FromDATE,ToDATE) 
	select i.rowid,d.desired_date,i.desired_date
		from inserted i
	join deleted d on
		i.rowid = d.rowid 
	where datediff(day,d.desired_ship_date,i.desired_ship_date)<>0 --added because update() doesn't always seem to work, 
	--I believe it determines it's being updated if it's passed in the query, not if it's actually different.
end
go

Here’s a great MSDN magazine article with more examples of triggers (including triggers that prevent certain kinds of updates):
Exploring SQL Server Triggers.

Excellent website – catacombae. Find Duplicate files on your computer, get your DiskUsage, etc

I just found an excellent website, www.catacombae.org, on it Eric posts a few different useful utilities and the code is open source. I have used three of his utilities and they work great, the three I’ve used are:

1) DiskUsageAnalyzer
I used to use FolderSize for this, unfortunately Microsoft made a change in Windows 7 that no longer allows extending explorer in a way that allows this add-on to work. /shake fist @ MS. This utility works quite well, it’s great if your disk is getting full and you don’t know where all the space is being used. IMO this is a utility that should be built into windows.

2) FindDuplicates
This is actually an idea I wanted to write for ages, managing all those family photos and backups is a pain (we tend to copy photos we want to get printed to a temp to print folder, and then promptly forget about them). Unfortunately this app shows errors as they occur meaning it stops processing on every error. You’d think this wouldn’t be a issue, but in practice there are lots of reasons for this process to fail on files (locked files, permissions). It would be great if it would just build a list of errors and continue processing instead of making me click ‘Ok’ over, and over, and over. Great app for the price though!

3) HFSExplorer
HFSExplorer is a great little utility for reading Mac based hard drives on a PC. Works great. Though I’m told that Windows 7 will read HFS disks so this utility seems to only be useful on pre Windows 7 machines.

Thanks for the great utilities Erik!