Recover from SQL Injection

This script creates a stored proc called SearchAndReplace, once this is created, call it like
SearchAndReplace 'Stringto be searched for','' this will replace the search string with nothing.

CREATE PROC SearchAndReplace
(
@SearchStr nvarchar(100),
@ReplaceStr nvarchar(100)
)
AS
BEGIN

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string and replace it with another string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 2nd November 2002 13:50 GMT

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @SQL nvarchar(4000), @RCTR int
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
SET @RCTR = 0

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
SET @SQL= 'UPDATE ' + @TableName +
' SET ' + @ColumnName
+ ' = REPLACE(' + @ColumnName + ', '
+ QUOTENAME(@SearchStr, '''') + ', ' + QUOTENAME(@ReplaceStr, '''') +
') WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
EXEC (@SQL)
SET @RCTR = @RCTR + @@ROWCOUNT
END
END
END

SELECT 'Replaced ' + CAST(@RCTR AS varchar) + ' occurence(s)' AS 'Outcome'
END

Public Speaking

This weekend just past, I gave a presentation to about 20 odd people on the joys of Nagios, the open source server and network monitor.

This is probably the first bit of proper public speaking that I have done in a very long time.

Before I actually gave the presentation, I was so nervous, looking round, catching glimpses of other peoples presentations, vision based tracking, rapid web service development in rails.

I just thought, wow these people are really serious about this, should I even be here?

The answer was, hell yeah, the people in the room (including the authors of the two topics ^^^) obviously wanted to hear something about it otherwise they would be out getting coffee or something similar.

So a scary experience turned out to be really worthwhile and a great way to meet new people, BarCamp '09 here I come (just need to figure out a topic!)

PS once I started speaking I was fine, the most of the nerves left me, except of course when my remote started to misbehave....

Tech I Touch #1

This is a new series of blog entries about interesting tech (to me anyway) that I come across in my day-to-day life.

They will probably be sporadic entries as most of the tech that I come across, I've either used it already or it doesn't interest or intrigue me.

This one will be about the Dyson hand dryer. Please bear with me on this, I know this sounds like a deathly boring object but I found it interesting as it was a novel twist on existing technology that, to be quite honest, only did a passable job, usually resorting to rubbing hands on your trousers. Or trying to find not quite environmentally friendly paper towels.

They dyson machine is quite different, rather than a straight down heated airflow, it has a semi-enclosed area that you put your hands into and then draw them out slowly.
A high pressure stream of cold air pushes the water off your hands from the base of your palm up to your fingertips, 1 pass, maybe 2 is all that it took for soaking wet hands to be bone dry.

Here's a linky Dyson AirBlade

What I found interesting was that it used cold air rather than the usual heated airstream. This in itself has to be more environmentally friendly and safer as there is no wasteful heating element.

There are more details at the link above, including a cost-saving calculator.

Monitor Dell Servers Running Windows Server Using Nagios and SNMP

Things I use in this entry:

Nagios 2.9
perl:net-snmp
Dell OpenManage Server Administrator
Nagios plugin check_omsa_snmp.pl
Windows Server 2003

At present, the servers that I am responsible for are monitored from a 30,000' view, its getting progressively closer to the ground and services or more precisely groups of services are being monitored. For instance, there are several services that our exchange server depends on but I don't want to and don't think it is necessary to report on each one directly, a better approach IMO is to use nc_net on each windows server to be monitored then have one service to view these as a single point of failure.

I had been asked to look at monitoring the physical hardware of each server and the only real way to do this would be using SNMP and the Dell OpenManage Server Administrator tools.

Installation Instructions

1. Windows Server has SNMP available as an additional built-in component, it can be installed using Add/Remove Windows Components under Management and Monitoring tools.
Feel free to install all of these as most of them are useful but for our purposes, only the installation of the Simple Network Management Protocol component is required.

2. Install the Dell OpenManage Server Administrator software found here

3. Open up services.msc and get the properties for the SNMP agent, click on the security tab, then untick the send authentication trap.
In the upper section, click on add an SNMP community (as read-only), remember this name as you will need it later.
In the lower section add the address of your Nagios as one of the accepted hosts. Make sure to leave in localhost, or if you aren't paranoid (AND WHY NOT????) you can tick the box saying accept from any host.

4. Go to Nagios Exchange and download check_omsa_snmp.pl to your Nagios server into /usr/local/nagios/libexec (default config assumed)

5. chmod +x that file and test your connection to the SNMP enabled server using snmpwalk -v 2c -c COMMUNITYNAME HOSTNAME .1.3.6.1.4.1.674.10892.1.700.20.1.8.1
It should return SNMPv2-SMI::enterprises.674.10892.1.700.20.1.8.1.1 = STRING: "CPU Planar"
SNMPv2-SMI::enterprises.674.10892.1.700.20.1.8.1.2 = STRING: "Ambient"
SNMPv2-SMI::enterprises.674.10892.1.700.20.1.8.1.3 = STRING: "BP Bottom Temp"

If that is successful, run this command
./check_omsa_snmp.pl -H HOSTNAME -C COMMUNITYNAME
and this will return any critical errors on your server, for example
Power Supply 2 is critical

You can then set this up as a service command where you can have the SNMP community set already.

Restart Services as Non-admin User Windows Vista

run

Sc sdset servicename "D:AR(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;BA)(A;;LCRPWP;;;AU)(A;;CCLCSWLOCRRC;;;IU)(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;SY)S:(AU;FA;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;WD)

where servicename is the servicename that you want non-admins to be able to operate

Jailbreak O2 iPhone

Steps for jailbreaking an O2 iPhone
(This is for those who have a legitimate activated O2 iPhone)
1. Connect your iPhone via USB
2. Press the home and wake/sleep buttons and hold them until the iPhone resets and the screen goes black, then release the wake/sleep button.
3. When iTunes pops up the message "ITunes has detected an iPhone in recovery mode" release the home button.
4. 1.1.1 Firmware
Jailbreak
Download both of these, if you are on a mac, don't use safari to download the firmware as it will extract it and you don't want that.
5. After these files have downloaded, hold Option+Alt and click on restore in iTunes, Select the 1.1.1 Firmware ISPW file. It will install and error out at the end with a 1015 error. This is the expected outcome.
6. Kick the iPhone out of this mode by running jailbreak.jar and clicking Boot From Recovery (don't enable any other options)
7. Open safari on the iPhone and go to http://jailbreakme.com scroll down and run Install AppSnapp, safari will exit and download/install AppSnapp, unlock the iPhone and its jailbroken.
8. Launch Installer on iPhone and scroll down to Tweaks (1.1.1) in the install tab. Select OkToPrep and install
9. Click Check For Update in iTunes and it will download and install the 1.1.2 update
10. Quit iTunes, run jailbreak.jar and select jailbreak (and install SSH as well)

Enabling EDGE on O2 again
ssh root@ip_address_of_my_iphone
use either the password alpine or dottie or the password that you set on jailbreak (each line is one command, so 3 in total)

rm /var/root/Library/Preferences/com.apple.carrier.plist

ln -s /System/Library/Frameworks/CoreTelephony.framework/Support/O2_UK.plist /var/root/Library/Preferences/com.apple.carrier.plist

rm -f /var/root/Library/Preferences/SystemConfiguration/preferences.plist

Reboot the iPhone by switching off (hold the wake/sleep button and slide to power off) then starting it again.

Job Done!

Synergy Fix in Kubuntu

Synergy has a problem with displaying the @ symbol on a synergyc session, this is the necessary command to fix this (symptom is displaying OHM symbol instead of '@')
This needs done after every X restart.

echo keycode 24 = q Q at at at at | xmodmap -

AD Replication strangeness

Another very short note, after deploying 3 AD Servers, 1 as PDC and the others as BDCs, I couldnt get a BDC at another site to replicate the AD structures etc.
Tried all the troubleshooting on the MS site (couldn't understand a lot of it but that will come with time and more experience) but I did remember this one tip.
Whatever is the simplest explanation is usually the correct one.

I had the subnet mask set incorrectly on the server that was causing the problem, a quick change of that to be 255.255.0.0 (rather than 255.0.0.0) and we were sorted and replicating happily.

Windows Installer

Is a lying bastard.

That is all.

Kill-9

This is a rap about kill -9 from a CS Student at Stanford U.

The full Lyrics can be got here and the audio track is found here

Cleaning macbook keys

Okay, first off, a bit of advice, DO NOT EAT A TOASTED CHEESE SANDWICH NEAR YOUR MACBOOK WHILST IT IS OPEN!
I simply cannot stress this point enough, if I had paid attention to this it would have made my life much less complicated.

Okay, so you have either a bit of hair, or a stringy bit of cheese under a keycap (in my case the 'B') the way to remove it is quite simple.
Tools required:

Mini swiss army knife (I used this one)
Pin (not needle, you need the enamel head)

Using the screwdriver blade, slide it under the keycap and prise it up very gently, not enough to remove it but enough to see the small scissor mechanism.
Use the tweezers or the toothpick to gently press in the scissor legs on the LEFT hand side of the key (on the edge facing you), it will move slightly and the key will pop up away from you, lift it up and slide it to the right to remove it from the clip.

Now clean out all the crap under the key, I had a bit of cheese (see above) and a hair.

Reinserting the key is a different story, its a hell of a lot more difficult! It took me a few goes at it before I had a brainwave. What you do is insert the pin between the legs of the scissor mechanism to prevent it closing. The head of the pin prevents it falling through so just let it sit whatever way it suits you.
Line up the right hand leg/clip thing and slot it in to the left, then just drop the key in place and then press on it gently, making sure that it is lined up. It should click into place and you are now done!

CFeclipse reporting Wrong J2SE Version

Having reinsatlled several machines I have come across a "feature" of the Oracle 9i Administrative Client, it does not play nice and adds its path variables to the start of the $PATH in windows.

This led to much frustration as I *knew* that I had the right version (j2sdk 1.4.2 r10) and it was reporting 1.3.1. CFEclipse would not start and would bomb out with an error reporting an unsuitable java version.

The simple fix to this was to simply add the correct JRE path before the Oracle JRE path. In the majority of my cases it was "C:\J2SDK1.4.2_13\jre\bin" then just run the CFEclipse.exe program and it works!

Resize NTFS Partitions

Go and get Gparted here and burn it out, its a linux LiveCD with the Gnome partition manager GParted so that you can resize NTFS partitions non-destructively!

DO IT NOW!

KB925398 Security Update for Windows Media Player 6.4

This is another set of patches that has some sort of conflict with CYGWIN running SSHD. it is enough to stop the SSHD service when the updates "hang" on the windows media update.

Fixing orphaned users in SQL 2000

Here it is as I keep forgetting how to do it and every time I google it I get a different result...

Use db_name
go
sp_change_users_login 'update_one', 'user_name', 'user_name'
      

SQL 2005 full transaction log (Or how I almost cried)

Background:

SQL 2005 Express Edition with a full transaction log, the normal procedure that I would use for this (a development environment) would be to detach the DB, rename the transaction log to "trans_log.ldf.old" or similar, reattach the DB and go about my normal business.

Not so today, what happened was that the transaction log hit the limit with users still attached, tried to kill them off a-la SQL 2000 enterprise manager using the detach GUI and then hitting the clear users button without actually detaching the DB (sly dog).

This doesn't work in SQL 2005 (express edition at least) so I restarted the SQL2005EXPRESSservice and found that it had indeed detached the DB anyway.

What I had to do to resolve this:

Open up the "Attach Database" GUI window, select the transaction log in the lower pane and hit the remove button.
Hit the script button at the top
Go back to Management Studio and run the new script.
Bish-bosh your DB is now re-attached.

N.B. For some reason if you try to use the GUI window it barfs saying the transaction log is full, even if you have removed the transaction log .ldf from the database files...

Pound as a Proxy

We have been using Pound as a proxy to protect a ColdFusion Application server from the internet and limit its exposure to malicious traffic.

I think it is a very good system even though we aren't using it to its full potential yet, from what I have read about it it is able to do load-balancing and clustering and other cool stuff.


It can be a bit of a pain to get it to stop and start as a Daemon but other than that it is very good. It also handles the SSL for the servers behind it, as a standard Win32 Apache 2.0 installation doesn't come with the SSL component, any of the other servers that use SSL and Apache2/Win32 has to have an additional module added and configured, which to be honest is a bit of a pain.

To get pound to do SSL, its a matter of installing openssl, generating your keys and CSRs and certificates, then putting the key and Certificate into one file with a .pem extension and pointing the pound config at it! Restart Pound and you are laughing.

A word of warning, make sure to match up the key to the certificate and that the key is not password protected (see this blog post for more info) as well as that you are not allowed to have any trailing spaces in the .pem file, it makes Pound barf and it is not at all easy to debug when you get the config wrong. A bit of a hint (in Debian at least) is that if Pound doesn't start correctly, the bash prompt will be on the same line as Pound Restarting like

Pound Restarting:servername:/etc/init.d#

I found the above useful but other than that Pound is no help when it comes to finding out what is wrong.

Exporting Protected .mov files with Quicktime

As you have probably seen, there are several video clips on this blog of the Apple Ad Spoofs provided courtesy of youTube and the great fuys at www.truenuff.com.

I managed to get the real .mov files by going into the source code for their page and downloading them using wget. I got a copy of Quicktime Pro 7 and realised that I could have these clips on my mobile if i could just export them as I had done plenty of times before for other files.
What happened was a bit of a pain, the export and save as options were greyed out and unselectable. I did a bit of googling and found out about protected .mov files and here is how to make them "unprotected"

1. Download "dumpster" from apple
2. Install it and run from your applications or utilities folder (doesnt matter where you put it) it looks like it doesnt do anything but if you check your dock there is a new icon there
3. make a copy of your .mov file (Justin Casey)
4. Drag the .mov file to the dumpster icon in the dock and a debug window will appear
5. Scroll right to the bottom and there will be a key called NSAV
6. Expand this and there will be two entries on one row $0000 and $0001
   
7. Change the $0001 to $0000 and hit Apple + S or save the file in the menu.
8. Open up your new unprotected .mov file and your export/save options are now enabled!

this does open up the quasi-legal/moral issue of what to do (or not to do) with these files.
I only unprotected it so I could view it on my phone and show other people, not to distribute it or profit from it in anyway, I think this is Fair Use but if anyone has a problem with it I will of course comply with any reasonable requests.

Keep up the good work Truenuff.com!

What your Browser reveals about you

Here
I use a mix of Safari, Firefox 1.5.0.x, Camino, IE 6.0 and Opera, I would try and use Camino normally but sometimes it breaks web pages and I have to open them in Safari/Firefox/Opera. I only use IE when absolutely necessary for testing page compatibility and I had IE 7 installed and took it out because it hurt my eyes. Think Opera V4 with bad icons....

Automating SQL 2005 Express Edition Backups

SQL2005 Express Edition w/ Management Studio Express looks like it is a very nice DB and management console, it falls down in one place (that I can see) and that is it is missing the maintenance Wizard that was found on SQL 2000.

Now I cant complain as it is a free application and a small stored procedure can restore most of the functionality as regards backups.

Not all the procedures that are required are activated by default and it is very simple to re-activate them. simply copy and paste this code into a text editor and save it as c:/enableprocs.sql

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

Now execute this command from a "run" prompt

sqlcmd -S .\SQLExpress -i c:\enableprocs.sql

this enables the Ole Automation Procedures and the XP_CMDshell.

Get the expressmaint.sql script here

extract the script and save the .sql file to c:\expressmaint.sql

execute this command

sqlcmd -S .\SQLExpress -i c:\expressmaint.sql

This installs the expressmaint stored procedure which is necessary for this backup method.

PARAMETERS

@database	Y	NONE	The target database for the maintenance operation. Valid values are a single database name, ALL_USER which will process all user databases and ALL_SYSTEM which will process all system databases
@optype	Y	NONE	The type of maintenance operation to be performed. Valid values are DB - Full Database Backup DIFF - Differential Database Backup LOG - Log Backup CHECKDB - Database Integrity Check REINDEX - Rebuild all indexes REORG - Reorganize all indexes
@backupwith	N	NULL	Specify additional backup options as documented in BOL for the BACKUP WITH command
@backupfldr	N	NULL	The base folder to write the backups to. Sub folders will be created for each database
@verify	N	1	Indicates whether to verify the backup file. Valid values are 1 and 0 with 1 = TRUE and 0 = FALSE
@verifywith	N	NULL	Specify additional verify options as documented in BOL for the VERIFY WITH command
@dbretainunit	N	NULL	The unit of measure for the @dbretainval parameter. Valid values are minutes, hours, days, weeks, months and copies. The combination of these two parameters determines how long or how many copies of old backup files are kept
@dbretainval	N	1	The time period or number of copies of old backups to keep
@report	N	1	Indicates whether to produce a report of the maintenance carried out. Valid values are 1 and 0 with 1 = TRUE and 0 = FALSE
@reportfldr	N	NULL	The folder where maintenance reports are written to if @report = 1
@rptretainunit	N	NULL	The unit of measure for the @rptretainval parameter. Valid values are minutes, hours, days, weeks, months and copies. The combination of these two parameters determines how long or how many copies of old reports are kept
@rptretainval	N	1	The time period or number of copies of old reports to keep
@checkattrib	N	0	Indicates whether to check the archive bit on a backup file before deleting it. This is a safety check to prevent deletion of files that have not been backed up onto tape. Valid values are 1 and 0 with 1 = TRUE and 0 = FALSE
@delfirst	N	0	Indicates whether to delete old backups prior to doing the current backup. This is not advisable but can be useful if disk space is limited. Valid values are 1 and 0 with 1 = TRUE and 0 = FALSE
@debug	N	0	Indicates whether print out debug information such as the commands generated and the contents of the temporary tables used in the procedure. Valid values are 1 and 0 with 1 = TRUE and 0 = FALSE

This is the script that I use to backup all user databases, save it as  fullbackup.sql in c:\
exec expressmaint
   @database      = 'ALL_USER', 
   @optype        = 'DB',
   @backupfldr    = 'd:\backups',
   @reportfldr    = 'd:\reports',
   @verify        = 1,
   @dbretainunit  = 'weeks',
   @dbretainval   = 1,
   @rptretainunit = 'weeks',
   @rptretainval  = 1,
   @report        = 1

If you look at the parameters you will see that it backups all user databases, it puts the backups in d:/backups and the reports in d:/reports
it also retains reports and backups for 1 week.

Then just set up a scheduled task in windows to run

sqlcmd -S .\SQLExpress -i c:\fullbackup.sql

on whatever schedule you like. I recommend daily backups if not every 12hrs.