Code

Asynchronous Database Auditing

I needed to create an extensive db audit system for a project at work, little bit of searching lead me to these two articles:

  1. Trigger Based Audit
  2. Centralized Asynchronous Auditing
Initially I tried using just the trigger based auditing but that had some performance issues; so my solution was to create a script (3 scripts actually) that combined the trigger generating code of the first article with the service based asynchronous writing of the second article.

How it works

NOTE: This post will get out of date at some point so it’s best to get the scripts from the bitbucket repo: https://bitbucket.org/ThapLtd/async-database-audit

Setting up the Audit Database

The first script creates the database the records the audit information and sets up a service broker that listens to a queue for a message from another database (we’ll set this up in another script). This script will generate a guid that you’ll need to take note of for later:
USE master

IF DB_ID('[AUDITDB]') IS NULL
	CREATE DATABASE [AUDITDB]		

-- enable service broker
ALTER DATABASE [AUDITDB] SET ENABLE_BROKER
-- set trustworthy on so we don't need to use certificates
ALTER DATABASE [AUDITDB] SET TRUSTWORTHY ON

GO

USE [AUDITDB] 	

GO
-- get service broker guid for [AUDITDB].
-- we must copy/paste this guid to the BEGIN DIALOG
-- in dbo.spSendAuditData stored procedure
SELECT	service_broker_guid
FROM	sys.databases
WHERE	database_id = DB_ID()

GO

IF OBJECT_ID('dbo.MasterAuditTable') IS NOT NULL
	DROP TABLE dbo.MasterAuditTable

GO
-- Master Audit Table
CREATE TABLE MasterAuditTable
(
	AuditID BIGINT IDENTITY(1,1) NOT NULL,
	DMLType char(1) NOT NULL CHECK (DMLType IN ('D', 'U', 'I')),
	DatabaseName nvarchar(128),
	TableName nvarchar(128),
	PrimaryKeyField nvarchar(1000),
	PrimaryKeyValue nvarchar(1000),
	FieldName nvarchar(128),
	OldValue nvarchar(1000),
	NewValue nvarchar(1000),
	UpdateDate datetime DEFAULT (GetDate()),
	UserName nvarchar(128)
)

GO

IF OBJECT_ID('dbo.AuditDialogs') IS NOT NULL
	DROP TABLE dbo.AuditDialogs

GO
-- Table that will hold dialog id's for each database on the server
-- These dialogs will be reused. why this is a good thing is explained here:
-- http://blogs.msdn.com/remusrusanu/archive/2007/04/24/reusing-conversations.aspx
CREATE TABLE dbo.AuditDialogs
(
	DbId INT NOT NULL,
	DialogId UNIQUEIDENTIFIER NOT NULL
)

GO
IF OBJECT_ID('dbo.AuditErrors') IS NOT NULL
	DROP TABLE dbo.AuditErrors

GO
-- create Errors table
CREATE TABLE dbo.AuditErrors
(
	Id BIGINT IDENTITY(1, 1) PRIMARY KEY,
	ErrorProcedure NVARCHAR(126) NOT NULL,
	ErrorLine INT NOT NULL,
	ErrorNumber INT NOT NULL,
	ErrorMessage NVARCHAR(4000) NOT NULL,
	ErrorSeverity INT NOT NULL,
	ErrorState INT NOT NULL,
	AuditedData XML NOT NULL,
	ErrorDate DATETIME NOT NULL DEFAULT GETUTCDATE()
)

GO
IF OBJECT_ID('dbo.usp_WriteAuditData') IS NOT NULL
	DROP PROCEDURE dbo.usp_WriteAuditData

GO

-- stored procedure that writes the audit data from the queue to the audit table
CREATE PROCEDURE dbo.usp_WriteAuditData
AS
BEGIN
	DECLARE @msgBody XML
	DECLARE @dlgId uniqueidentifier

	WHILE(1=1)
	BEGIN
		BEGIN TRANSACTION
		BEGIN TRY

			-- insert messages into audit table one message at a time
			;RECEIVE top(1)
					@msgBody	= message_body,
					@dlgId		= conversation_handle
			FROM	dbo.TargetAuditQueue

			-- exit when the whole queue has been processed
			IF @@ROWCOUNT = 0
			BEGIN
				IF @@TRANCOUNT > 0
				BEGIN
					ROLLBACK;
				END
				BREAK;
			END 

			DECLARE @DatabaseName NVARCHAR(128), @TableName NVARCHAR(128),
					@DMLType CHAR(1),
					@PrimaryKeyField nvarchar(1000), @PrimaryKeyValue nvarchar(1000),
					@FieldName nvarchar(128), @OldValue nvarchar(1000), @NewValue nvarchar(1000),
					@UpdateDate datetime, @UserName nvarchar(128)

			-- xml datatype and its capabilities rock
			SELECT	@DatabaseName = T.c.query('/AuditMsg/DatabaseName').value('.[1]', 'NVARCHAR(128)'),
					@TableName = T.c.query('/AuditMsg/TableName').value('.[1]', 'NVARCHAR(128)'),
					@DMLType = T.c.query('/AuditMsg/DMLType').value('.[1]', 'CHAR(1)'),
					@PrimaryKeyField = T.c.query('/AuditMsg/PrimaryKeyField').value('.[1]', 'NVARCHAR(1000)'),
					@PrimaryKeyValue = T.c.query('/AuditMsg/PrimaryKeyValue').value('.[1]', 'NVARCHAR(1000)'),
					@FieldName = T.c.query('/AuditMsg/FieldName').value('.[1]', 'NVARCHAR(128)'),
					@OldValue = T.c.query('/AuditMsg/OldValue').value('.[1]', 'NVARCHAR(1000)'),
					@NewValue = T.c.query('/AuditMsg/NewValue').value('.[1]', 'NVARCHAR(1000)'),
					@UpdateDate = T.c.query('/AuditMsg/UpdateDate').value('.[1]', 'datetime'),
					@UserName = T.c.query('/AuditMsg/UserName').value('.[1]', 'NVARCHAR(128)')
			FROM	@msgBody.nodes('/AuditMsg') T(c)

			INSERT INTO dbo.MasterAuditTable(DatabaseName, TableName, DMLType, PrimaryKeyField, PrimaryKeyValue, FieldName, OldValue, NewValue, UpdateDate, UserName)
			SELECT @DatabaseName, @TableName, @DMLType, @PrimaryKeyField, @PrimaryKeyValue, @FieldName, @OldValue, @NewValue, @UpdateDate, @UserName

			-- No need to close the conversation because auditing never ends
			-- you can end conversations if you want periodicaly with a scheduled job
			-- END CONVERSATION @dlgId

			IF @@TRANCOUNT > 0
			BEGIN
				COMMIT;
			END
		END TRY
		BEGIN CATCH
			IF @@TRANCOUNT > 0
			BEGIN
				ROLLBACK;
			END
			-- insert error into the AuditErrors table
			INSERT INTO AuditErrors (
					ErrorProcedure, ErrorLine, ErrorNumber, ErrorMessage,
					ErrorSeverity, ErrorState, AuditedData)
			SELECT	ERROR_PROCEDURE(), ERROR_LINE(), ERROR_NUMBER(), ERROR_MESSAGE(),
					ERROR_SEVERITY(), ERROR_STATE(), @msgBody
		END CATCH;
	END
END

GO
IF EXISTS(SELECT * FROM sys.services WHERE NAME = '//Audit/DataWriter')
	DROP SERVICE [//Audit/DataWriter]

IF EXISTS(SELECT * FROM sys.service_queues WHERE NAME = 'TargetAuditQueue')
	DROP QUEUE dbo.TargetAuditQueue

IF EXISTS(SELECT * FROM sys.service_contracts  WHERE NAME = '//Audit/Contract')
	DROP SERVICE [//Audit/Contract]

IF EXISTS(SELECT * FROM sys.service_message_types WHERE name='//Audit/Message')
	DROP MESSAGE TYPE [//Audit/Message]
GO
-- create a message that must be well formed XML
CREATE MESSAGE TYPE [//Audit/Message]
	VALIDATION = WELL_FORMED_XML

-- create a contract for the message
CREATE CONTRACT [//Audit/Contract]
	([//Audit/Message] SENT BY INITIATOR)

-- create the queue to run the spWriteAuditData automaticaly when new messages arrive
-- execute it as dbo
CREATE QUEUE dbo.TargetAuditQueue
	WITH	STATUS=ON,
	ACTIVATION (
		PROCEDURE_NAME = usp_WriteAuditData,	-- sproc to run when the queue receives a message
		MAX_QUEUE_READERS = 50,					-- max concurrently executing instances of sproc
		EXECUTE AS 'dbo' );

-- create a target service that will accept inbound audit messages
-- set the owner to dbo
CREATE SERVICE [//Audit/DataWriter]
		AUTHORIZATION dbo
	ON QUEUE dbo.TargetAuditQueue ([//Audit/Contract])
Just replace all instances of AUDITDB with the name you want to use.

Creating/Modifying the database to Audit

NOTE: If you are modifying an existing database then you may need to kill all connections to run this script, this happened to me a few times.
The second script sets up the database to allow it to talk to the audit database you just setup; creates the store procedure which will send a message to the audit db queue and create a table to log any auditing problems
USE master
GO

IF DB_ID('[DBToAudit]') IS NULL
	CREATE DATABASE [DBToAudit]

-- enable service broker
ALTER DATABASE [DBToAudit] SET ENABLE_BROKER
-- set trustworthy on so we don't need to use certificates
ALTER DATABASE [DBToAudit] SET TRUSTWORTHY ON

GO
USE [DBToAudit]

GO
-- Drop existing service broker items
IF EXISTS(SELECT * FROM sys.services WHERE NAME = '//Audit/DataSender')
	DROP SERVICE [//Audit/DataWriter]

IF EXISTS(SELECT * FROM sys.service_queues WHERE NAME = 'InitiatorAuditQueue')
	DROP QUEUE InitiatorAuditQueue

IF EXISTS(SELECT * FROM sys.service_contracts  WHERE NAME = '//Audit/Contract')
	DROP SERVICE [//Audit/Contract]

IF EXISTS(SELECT * FROM sys.service_message_types WHERE name='//Audit/Message')
	DROP MESSAGE TYPE [//Audit/Message]

GO
-- create a message that must be well formed
CREATE MESSAGE TYPE [//Audit/Message] 
	VALIDATION = WELL_FORMED_XML

-- create a contract for the message
CREATE CONTRACT [//Audit/Contract]
	([//Audit/Message] SENT BY INITIATOR)

-- create the initiator queue 
CREATE QUEUE dbo.InitiatorAuditQueue

-- create an initiator service that will send audit messages to target service
CREATE SERVICE [//Audit/DataSender] 
	AUTHORIZATION dbo
	ON QUEUE dbo.InitiatorAuditQueue	-- no contract means service can only be the initiator

GO
IF OBJECT_ID('dbo.AuditErrors') IS NOT NULL
	DROP TABLE dbo.AuditErrors

GO
-- create Errors table
CREATE TABLE dbo.AuditErrors
(
	Id BIGINT IDENTITY(1, 1) PRIMARY KEY,
	ErrorProcedure NVARCHAR(126) NOT NULL,
	ErrorLine INT NOT NULL,
	ErrorNumber INT NOT NULL,
	ErrorMessage NVARCHAR(4000) NOT NULL,
	ErrorSeverity INT NOT NULL,
	ErrorState INT NOT NULL,
	AuditedData XML NOT NULL,
	ErrorDate DATETIME NOT NULL DEFAULT GETUTCDATE()
)

GO
IF OBJECT_ID('dbo.usp_SendAuditData') IS NOT NULL
	DROP PROCEDURE dbo.usp_SendAuditData

GO
-- stored procedure that sends the audit data to the be audited
CREATE PROCEDURE dbo.usp_SendAuditData
(
	@AuditedData XML
)
AS
BEGIN
	BEGIN TRY
		IF @AuditedData IS NULL
			RETURN
		DECLARE @dlgId UNIQUEIDENTIFIER, @dlgIdExists BIT
		SELECT @dlgIdExists = 1

		-- Check if our database already has a dialog id that was previously used
		-- Why reusing conversation dialogs is a good this is explaind here
		-- http://blogs.msdn.com/remusrusanu/archive/2007/04/24/reusing-conversations.aspx
		-- very well
		SELECT	@dlgId = DialogId
		FROM	[AUDITDB].dbo.AuditDialogs AD 
		WHERE	AD.DbId = DB_ID()
		IF 	@dlgId IS NULL
		BEGIN 
			SELECT @dlgIdExists = 0
		END

		-- Begin the dialog, either with existing or new Id
		BEGIN DIALOG @dlgId
			FROM SERVICE    [//Audit/DataSender]											   
			TO SERVICE      '//Audit/DataWriter', 
							-- this is a [AUDITDB] Service Broker Id (change it to yours)
							'A688B601-A575-4458-9D7C-1607CD7F540D'
			ON CONTRACT     [//Audit/Contract]
		WITH ENCRYPTION = OFF;

		-- add our db's dialog to AuditDialogs table if it doesn't exist yet
		IF @dlgIdExists = 0
		BEGIN 
			INSERT INTO [AUDITDB].dbo.AuditDialogs(DbId, DialogId)
			SELECT	DB_ID(), @dlgId
		END
		--SELECT @AuditedData

		-- Send our data to be audited
		;SEND ON CONVERSATION @dlgId	
		MESSAGE TYPE [//Audit/Message] (@AuditedData)
	END TRY
	BEGIN CATCH
		INSERT INTO AuditErrors (
				ErrorProcedure, ErrorLine, ErrorNumber, ErrorMessage, 
				ErrorSeverity, ErrorState, AuditedData)
		SELECT	ERROR_PROCEDURE(), ERROR_LINE(), ERROR_NUMBER(), ERROR_MESSAGE(), 
				ERROR_SEVERITY(), ERROR_STATE(), @AuditedData
	END CATCH
END

GO
Replace all instances of DBToAudit with the name of your database to audit, replace all instances of AUDITDB with the name of the audit database you set up previously and finally replace the GUID (on line 100 or near it) with the GUID generated by the first script.

Generating Triggers

The final script is generates a trigger for each table that in turn generates a script to pull the changes made during an insert/update/delete operation and sends it to the store procedure generated in the second script. There is nothing to do here but run it 🙂

NOTE: This script hasn’t been adapted to ignore tables that store binary information, if you know how to do this then feel free to fork /submit a patch.


DECLARE @sql varchar(max), @TABLE_NAME sysname
SET NOCOUNT ON

SELECT @TABLE_NAME= MIN(TABLE_NAME) 
FROM INFORMATION_SCHEMA.TABLES 
WHERE 
TABLE_TYPE= 'BASE TABLE' 
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME NOT LIKE 'Audit%'

WHILE @TABLE_NAME IS NOT NULL
 BEGIN
EXEC('IF OBJECT_ID (''' + @TABLE_NAME+ '_ChangeTracking'', ''TR'') IS NOT NULL DROP TRIGGER ' + @TABLE_NAME+ '_ChangeTracking')
SELECT @sql = ''
SELECT @sql = @sql+
'
create trigger ' + @TABLE_NAME+ '_ChangeTracking on [' + @TABLE_NAME+ '] for insert, update, delete

as

SET NOCOUNT ON

  declare @bit           int,
          @field         int,
          @maxfield      int,
          @char          int,
          @fieldname     varchar(128),
          @TableName     varchar(128),
          @PKCols        varchar(1000),
          @sql           varchar(2000),
          @UpdateDate    varchar(21),
          @UserName      varchar(128),
          @Type          char(1),
          @PKFieldSelect varchar(1000),
          @PKValueSelect varchar(1000)

  select @TableName = '''+@TABLE_NAME+'''

  select @UserName = system_user,
         @UpdateDate = convert(varchar(8), getdate(), 112) + '' '' +
                       convert(varchar(12), getdate(), 114)

  if exists (select *
             from   inserted)
    if exists (select *
               from   deleted)
      select @Type = ''U''
    else
      select @Type = ''I''
  else
    select @Type = ''D''

  select *
  into   #ins
  from   inserted

  select *
  into   #del
  from   deleted

  select @PKCols = coalesce(@PKCols + '' and'', '' on'') + '' i.'' + c.COLUMN_NAME +
                   '' = d.'' +
                          c.COLUMN_NAME
  from   INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk,
         INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
  where  pk.TABLE_NAME = @TableName
         and CONSTRAINT_TYPE = ''PRIMARY KEY''
         and c.TABLE_NAME = pk.TABLE_NAME
         and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

  select @PKFieldSelect = COLUMN_NAME
  from   INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk,
         INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
  where  pk.TABLE_NAME = @TableName
         and CONSTRAINT_TYPE = ''PRIMARY KEY''
         and c.TABLE_NAME = pk.TABLE_NAME
         and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

  select @PKValueSelect = coalesce(@PKValueSelect + ''+'', '''') +
                                 ''isnull(convert(nvarchar(100), coalesce(i.'' +
                                                  COLUMN_NAME + '',d.'' +
                          COLUMN_NAME +
                                 '')),'''''''')''
  from   INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk,
         INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
  where  pk.TABLE_NAME = @TableName
         and CONSTRAINT_TYPE = ''PRIMARY KEY''
         and c.TABLE_NAME = pk.TABLE_NAME
         and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

  if @PKCols is null
    begin
        raiserror(''no PK on table %s'',
                  16,
                  -1,
                  @TableName)

        return
    end

  select @field = 0,
         @maxfield = max(ORDINAL_POSITION)
  from   INFORMATION_SCHEMA.COLUMNS
  where  TABLE_NAME = @TableName

  while @field < @maxfield
    begin
        select @field = min(ORDINAL_POSITION)
        from   INFORMATION_SCHEMA.COLUMNS
        where  TABLE_NAME = @TableName
               and ORDINAL_POSITION > @field

        select @bit = ( @field - 1 )% 8 + 1

        select @bit = power(2, @bit - 1)

        select @char = ( ( @field - 1 ) / 8 ) + 1

        if substring(COLUMNS_UPDATED(), @char, 1) & @bit > 0
            or @Type in ( ''I'', ''D'' )
          begin
              select @fieldname = COLUMN_NAME
              from   INFORMATION_SCHEMA.COLUMNS
              where  TABLE_NAME = @TableName
                     and ORDINAL_POSITION = @field

             	SELECT @sql =''
             DECLARE @auditBody XML
             	
             	SELECT  @auditBody =
		''''
			'+DB_NAME()+'
			'' + @TableName + ''
			'' + @Type + ''
			'' + @PKFieldSelect + ''
			''''+'' + @PKValueSelect + ''+''''
			'' + @fieldname + ''
			''''+isnull(convert(nvarchar(1000),d.'' + @fieldname + ''), '''''''') + ''''
			''''+isnull(convert(nvarchar(1000),i.'' + @fieldname + ''), '''''''') + ''''
			'' + @UpdateDate + ''
			'' + @UserName+ ''
		''''
		
	''

    SELECT @sql = @sql + '' from #ins i full outer join #del d''

    SELECT @sql = @sql + @PKCols

    SELECT @sql = @sql + '' where i.'' + @fieldname + '' <> d.'' + @fieldname

    SELECT @sql = @sql + '' or (i.'' + @fieldname + '' is null and  d.'' +
                  @fieldname +
                         '' is not null)''

    SELECT @sql = @sql + '' or (i.'' + @fieldname + '' is not null and  d.'' +
                  @fieldname +
                         '' is null)

				EXEC dbo.usp_SendAuditData @auditBody
                         ''
    exec( @sql)
end
end 
'


SELECT @sql
EXEC(@sql)
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME> @TABLE_NAME
AND TABLE_TYPE= 'BASE TABLE' 
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME NOT LIKE 'Audit%'
END
Code Python

Project Euler: Problem 3

Problem:

The prime factors of 13195 are 5, 7, 13 and 29.

What is the largest prime factor of the number 600851475143?

Source: http://projecteuler.net/index.php?section=problems&id=3


Solution:

Before we can being to solved the problem above we first must answer the questions raised by it. In other words:

  • What is a prime number?
  • What is a factor?
  • What is a prime factor?
  • How do we find the prime factors for a number?

 

What is a prime number?

A prime number is any natural number that can only be divided by 1 or itself. E.G. 7 is a prime number because it can only be divided by 1 or 7 while 10 is not a prime number because it’s divisors are 1,2,5,10.

 

What is a factor?

A factor (also known as a divisor) is any number that that can divide in to another number and not leave a remainder. In the previous section about prime numbers you can see the for 10 it’s factors are:

  • 1
  • 2
  • 5
  • 10

 

What is a prime factor?

A prime factor is any factor that is also a prime number.

 

How do we find the prime factors for a number?

The method of finding prime factors is called Prime Factorisation.  It involves taking a number and dividing it by the smallest prime possible, then taking what’s remaining and dividing that by the smallest prime possible; keep doing this until the remainder is a prime. This Khans Academy video offers a great introduction to this concept.

 

Code:

Source: https://bitbucket.org/TWith2Sugars/project-euler/src/8892d0a39946/python/3.py

def primeNumbers(n):
    # Found this example on Wikipedia: http://en.wikipedia.org/wiki/Sieve_of_Eratosthenes
    # Create a candidate list within which non-primes will be
    # marked as None; only candidates below sqrt(n) need be checked. 
    candidates = list(range(n+1))

    # Square root of n
    fin = int(n**0.5)

    # Loop over the candidates, marking out each multiple.
    for i in xrange(2, fin+1):
        if candidates[i]:
            candidates[2*i::i] = [None] * (n//i - 1)

    # Filter out non-primes and return the list.
    return [i for i in candidates[2:] if i]

def primeFactors(n):
    # Generators all of the prime factors for n

    # The largest number that a prime factor could be
    maxPossiblePrimeNumber = int(n**0.5)

    # List of all prime numbers up to the square root of n
    primes = primeNumbers(maxPossiblePrimeNumber)

    while n > 1:
        for p in primes:
            # This is a prime factor
            if n % p == 0:
                # Set n to be the result of the division of n by the current prime number (p)
                n = n / p
                yield p

maxPrime = max(primeFactors(600851475143))
print(maxPrime)

The code is broken down in to two parts:

  • Generating Prime Numbers (primeNumbers)
  • Performing Prime Factorisation (primeFactors)

 

Generating Prime Numbers

There are many methods of generating prime numbers and in my search I’ve decided to use a method called Sieve of Eratosthenes which is pretty efficient are generating prime numbers below 100. It also comes with a python example that we can use. You’ll see the code below.

 

Performing Prime Factorisation

This function just implemented the method of finding prime factors as mentioned earlier, there is probably a more efficient/mathematical way of doing this but for now it’s a good start.

 

 

Code Python

Project Euler: Problem 2

Right, here is my attempt at problem 2 of project euler.

Problem: http://projecteuler.net/index.php?section=problems&id=2

source: https://bitbucket.org/TWith2Sugars/project-euler/changeset/3ec9f237dbb9

def fib(n):
    evenSum, a, b = 0, 0, 1
    while a < n:
        a, b = b, a+b
        if b % 2 == 0:
		    evenSum += b

    return evenSum;

result = fib(4000000)
print(result)

Update:

Thanks to Mohammad’s advice the code has been update to avoid pointlessly assigning variables.

def fib(n):
    evenSum, a, b = 0, 1, 2
    while a < n:
        if b % 2 == 0:
		    evenSum += b
        a, b = b, a+b
    return evenSum;

result = fib(4000000)
print(result)
Code Python

Project Euler + Python

For a while I’ve been meaning to play with python but I could never think of something to do with it; so I decided to try and solve as many problems in Project Euler as I can.

Here is my mercurial repository on bitbucket: https://bitbucket.org/TWith2Sugars/project-euler

This is my attempt at the first problem so far:

Problem: http://projecteuler.net/index.php?section=problems&id=1
source: https://bitbucket.org/TWith2Sugars/project-euler/src/813d5ec90687/python/1.py

result = 0
for i in range(1000):
    if i % 5 == 0:
	    result += i
    elif i % 3 == 0:
	    result += i

print(result)
Code

How I fixed FluentNHibernate.Cfg.FluentConfigurationException on MVC 3

Just to let you know this may not solve your version of this error (since it’s so vague) but it’s how I solved it in one of my projects.

On a previous post I mention I was using NHibernate in one of the projects I’m working on and as such we’re using FluentNHibernate.

I’ve created a class (let’s call it SessionManager) that handles creating the ISessionFactory instance (and stores it in a static field). The project is built with MVC 3 and makes use of the DI provider along with Ninject so this code snippet should look familiar. (It’s where you set up you DI – usually the AppStart_NinjectMVC3)

kernel.Bind<ISessionFactory>().ToConstant(SessionManager.CreateSessionFactory());

That is the line the caused the problems; not sure how but the aspnet compiler dies. In the end I changed ninject to use “ToMethod” instead:

kernel.Bind<ISessionFactory>().ToMethod(c => SessionManager.CreateSessionFactory());

Since the session manager handles creating the ISessionFactory like I mentioned earlier we don’t get multiple instances of ISessionFactory.

Asp.Net MVC Code

NHibernate leaking connections with UnitOfWork pattern and transactions

Recently I’ve been using NHibernate in a project at work along with the unit of work pattern in an asp.net mvc site. Only 1 instance of the UoW is created per request and disposed of at end of the request.

In fact here is a UoW class we’re using:

public class UnitOfWork : INHibernateUnitOfWork
  {
    /// <summary>
    /// The session factory
    /// </summary>
    private readonly ISessionFactory sessionFactory;

    /// <summary>
    /// The current transaction
    /// </summary>
    private readonly ITransaction transaction;

    /// <summary>
    /// Gets the session.
    /// </summary>
    /// <value>The session.</value>
    public ISession Session { get; private set; }

    /// <summary>
    /// Initializes a new instance of the <see cref="UnitOfWork"/> class.
    /// </summary>
    /// <param name="sessionFactory">The session factory.</param>
    public UnitOfWork(ISessionFactory sessionFactory)
    {
      this.sessionFactory = sessionFactory;

      Session = this.sessionFactory.OpenSession();
      Session.FlushMode = FlushMode.Auto;
      transaction = Session.BeginTransaction();
    }

    /// <summary>
    /// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
    /// </summary>
    public void Dispose()
    {
      transaction.Dispose();
    }

    /// <summary>
    /// Commits this instance.
    /// </summary>
    public void Commit()
    {
      if (!transaction.IsActive)
      {
        throw new InvalidOperationException("No active transation");
      }

     /transaction.Commit();
    }

    /// <summary>
    /// Rollbacks this instance.
    /// </summary>
    public void Rollback()
    {
      if (transaction.IsActive)
      {
        transaction.Rollback();
      }
    }
  }

As you can see in the constructor we create a new NHibernate transaction and dispose of it when the request ends (by calling the dispose method).

The problem with this is that it created a connection with an open transaction but never closed it. Any way to resolve this we removed the use of transactions (until such a time they are working or we’ve figured out a suitable alternative). Two changes where made to the dispose and commit method to clean up the session instance and make sure the data is flushed.

    public void Dispose()
    {
      Session.Dispose();
    }

    public void Commit()
    {
      Session.Flush();
    }

Note: This project also is using the Velocity cache provider and by removing the transactions / replacing the code the caching mechanisms still work.

Code

My first OSS project – DOAP

At work I’m developing a web api for a project that is to be used be several devices and possibly by 3rd parties; I was having a discussion with a few colleagues about the best way to secure this api and this eventually moved on to OAuth.  The choice we faced was do we go with the existing and finalised OAuth protocol or do we take a chance and jump on board with OAuth 2 and all the benefits  it brings, the choice was easy we needed or api to work on mobile devices, desktop apps and a whole host of non-web based apps.

Next step was to see if there was an existing library that allowed us to be a provider. Two that came up (did I mention this was a .Net project?) were DotNetOpenAuth and another one which I’ve just forgotten. I played about with both attempting to get it up and running but had some difficulty. Neither library suited my needs so I decided to try and build one my self; and I did 🙂

I had a read through the PHP implementation and read the protocol draft several times over and what I developed is my  best interpretation of it. The project I was working on required the use of the assertion/password/refresh grants and that’s all I’ve tested so far. Hopefully someone will give the others a try too.

Source: http://github.com/TWith2Sugars/DOAP

License: MIT

Dependencies: WCFRestContrib (only for the WCF helper attributes)

WCF

The project I’m working on has the api provided via WCF and obviously I had to get my OAuth implementation working with WCF (With a huge help from the fantastic WCFRestContrib library). Check out the example in the github repo to see a working version.

Why DOAP?

DotNet OAuth Provider of course.


There is no binaries yet, not until the draft is finalised or someone requests it. It’s easy to build any way as there are barley any dependencies.

I’ll make a post of how to set it up soon but until then hopefully the example is enough to get you going.

Asp.Net MVC Code

How to POST JSON data to an Asp.Net MVC 2 app with ValidateAntiForgeryToken

The title says it all really, earlier this week I was attempting to submit data in the form of JSON to a controller method that implemented the ValidateAntiForgeryTokenAttribute.

On the client side of things  I managed to get the validation input generated by Html.AntiForgeryToken into my JSON object but I just kept on getting the “A required anti-forgery token was not supplied or was invalid” exception.

After a bit of research I found the problem to lie in the Asp.Net MVC 2 source, if you look at the ValidateAntiForgeryTokenAttribute file specifically on line 56 (OnAuthorization method) you’ll see this:

string formValue = filterContext.HttpContext.Request.Form[fieldName];

The validation class always checks the form for the token but we don’t have any form data when we post JSON!

So my solution was to modify like so:

      string value;
      if (filterContext.HttpContext.Request.ContentType.ToLower().Contains("json"))
      {
        var bytes = new byte[filterContext.HttpContext.Request.InputStream.Length];
        filterContext.HttpContext.Request.InputStream.Read(bytes, 0, bytes.Length);
        filterContext.HttpContext.Request.InputStream.Position = 0;
        var json = Encoding.ASCII.GetString(bytes);
        var jsonObject = JObject.Parse(json);
        value = (string)jsonObject[fieldName];
      }
      else
      {
        value = filterContext.HttpContext.Request.Form[fieldName];
      }

What happens here is I do a check to see if the post is json, if so I then pull the string from the input stream and using a JSON parser extract the token.

Since we’re modifying the ValidateAntiForgery class we might as allow it to work  with (or restrict to) any http verb.

This is what I’ve ended up with (sorry about the formatting):

namespace Your.App
{
  using System;
  using System.Linq;
  using System.Text;
  using System.Web;
  using System.Web.Mvc;
  using Newtonsoft.Json.Linq;

  [AttributeUsage(AttributeTargets.Class | AttributeTargets.Method, AllowMultiple = false, Inherited = true)]
  public class ValidateJsonAntiForgeryTokenAttribute : FilterAttribute, IAuthorizationFilter
  {
    private string _salt;
    private AntiForgeryDataSerializer _serializer;
    private readonly AcceptVerbsAttribute _verbs;

    public string Salt
    {
      get
      {
        return _salt ?? String.Empty;
      }
      set
      {
        _salt = value;
      }
    }

    internal AntiForgeryDataSerializer Serializer
    {
      get
      {
        if (_serializer == null)
        {
          _serializer = new AntiForgeryDataSerializer();
        }
        return _serializer;
      }
      set
      {
        _serializer = value;
      }
    }

    public ValidateJsonAntiForgeryTokenAttribute(HttpVerbs verbs = HttpVerbs.Post):this(null, verbs)
    {
    }

    public ValidateJsonAntiForgeryTokenAttribute(string salt, HttpVerbs verbs = HttpVerbs.Post)
    {
      this._verbs = new AcceptVerbsAttribute(verbs);
      this._salt = salt;
    }

    private bool ValidateFormToken(AntiForgeryData token)
    {
      return (String.Equals(Salt, token.Salt, StringComparison.Ordinal));
    }

    private static HttpAntiForgeryException CreateValidationException()
    {
      return new HttpAntiForgeryException("A required anti-forgery token was not supplied or was invalid.");
    }

    public void OnAuthorization(AuthorizationContext filterContext)
    {
      if (filterContext == null)
      {
        throw new ArgumentNullException("filterContext");
      }

      // We only need to validate this if it's a post
      string httpMethodOverride = filterContext.HttpContext.Request.GetHttpMethodOverride();
      if (!this._verbs.Verbs.Contains(httpMethodOverride, StringComparer.OrdinalIgnoreCase))
      {
        return;
      }

      string fieldName = AntiForgeryData.GetAntiForgeryTokenName(null);
      string cookieName = AntiForgeryData.GetAntiForgeryTokenName(filterContext.HttpContext.Request.ApplicationPath);

      HttpCookie cookie = filterContext.HttpContext.Request.Cookies[cookieName];
      if (cookie == null || String.IsNullOrEmpty(cookie.Value))
      {
        // error: cookie token is missing
        throw CreateValidationException();
      }
      AntiForgeryData cookieToken = Serializer.Deserialize(cookie.Value);

      string value;
      if (filterContext.HttpContext.Request.ContentType.ToLower().Contains("json"))
      {
        var bytes = new byte[filterContext.HttpContext.Request.InputStream.Length];
        filterContext.HttpContext.Request.InputStream.Read(bytes, 0, bytes.Length);
        filterContext.HttpContext.Request.InputStream.Position = 0;
        var json = Encoding.ASCII.GetString(bytes);
        var jsonObject = JObject.Parse(json);
        value = (string)jsonObject[fieldName];
      }
      else
      {
        value = filterContext.HttpContext.Request.Form[fieldName];
      }

      if (String.IsNullOrEmpty(value))
      {
        // error: form token is missing
        throw CreateValidationException();
      }
      AntiForgeryData formToken = Serializer.Deserialize(value);

      if (!String.Equals(cookieToken.Value, formToken.Value, StringComparison.Ordinal))
      {
        // error: form token does not match cookie token
        throw CreateValidationException();
      }

      if (!ValidateFormToken(formToken))
      {
        // error: custom validation failed
        throw CreateValidationException();
      }
    }
  }
}

Also I had to copy over 2 classes from the MVC source. The “AntiForgeryData” and “AntiForgeryDataSerializer” since they are interal and my project wouldn’t compile without them.

Final note, if you’re having trouble with the Serializer you copied over (in the Deserialize method and casting to a Triplet) then this may be of some help (.Net 4.0 only):

                dynamic deserializedObj = formatter.Deserialize(serializedToken);
                return new AntiForgeryData() {
                    Salt = deserializedObj[0],
                    Value = deserializedObj[1],
                    CreationDate = (DateTime)deserializedObj[2]
                };
Asp.Net MVC

Minify CSS & JS With MVCContrib

I was doing a bit of research recently trying to find a way to compress, combine and minify css and js in Asp.Net MVC 2 and found a few ideas on the subject. The one that caught my eye was the MvcContrib IncludeHandling; a little hidden gem.

Getting it up a running however wasn’t so straight forward (there isn’t much documentation about it, or at least not much I could find.). The first problem I stumbled upon was the HtmlExtensions (RenderIncludes function) required a dependency resolver which the project I was on didn’t use. To get round this I simply copied the source (from the HtmlExtensions link) and replaced the dependency resolver with the projects IoC framework (in this case we’re using MvcTurbine with Ninject)

Next you need to register the dependency with your IoC of choice, like I said previously we’re using MvcTurbine so this is how I did with with a service registration.

namespace YourProject.MvcSite.Registration
{
  using System.Web;
  using MvcContrib;
  using MvcContrib.IncludeHandling;
  using MvcContrib.IncludeHandling.Configuration;
  using MvcTurbine.ComponentModel;

  /// <summary>
  /// Regisers the minify interfaces
  /// </summary>
  public class MinifyRegistration  : IServiceRegistration 
  {
    /// <summary>
    /// Registers the components with the specified <see cref="T:MvcTurbine.ComponentModel.IServiceLocator"/> instance.
    /// </summary>
    /// <param name="locator">Instance of <see cref="T:MvcTurbine.ComponentModel.IServiceLocator"/> to use.</param>
    public void Register(IServiceLocator locator)
    {
      var httpContext = new HttpContextProvider(HttpContext.Current);
      var handler = new IncludeHandlingSectionHandler();
      var reader = new IncludeReader(httpContext);
      var storage = new StaticIncludeStorage(new KeyGenerator());
 
      locator.Register<IIncludeHandlingSettings>(handler);
      locator.Register<IIncludeCombiner>(new IncludeCombiner(handler, reader, storage, httpContext));
    }
  }
}

The final step is to select the js/css files you want and then combine them. In the example below we’re using the T4MVC template to generate the static file links which and the code is used in a view (in our example it’s the site master view).

    <%
      var scripts = new List<string>
                      {
                        "~" + Links.Scripts.jquery_1_4_2_min_js,
                        "~" + Links.Scripts.jquery_ui_1_8_2_custom_min_js,
                        "~" + Links.Scripts.jquery_localscroll_1_2_7_min_js,
                        "~" + Links.Scripts.jquery_scrollTo_1_4_2_min_js,
                        "~" + Links.Scripts.jquery_serialScroll_1_2_2_min_js,
                        "~" + Links.Scripts.Ajax_js,
                        "~" + Links.Scripts.Custom_js,
                        "~" + Links.Scripts.json2_js,
                        "~" + Links.Scripts.flowplayer_3_2_3_min_js,
                        "~" + Links.Scripts.DragDrop_js,
                        "~" + Links.Scripts.Slider_js
                      };
    %>

    <%=Html.RenderJs(scripts)%>
Code

Short Break

It’s been sometime since I’ve last posted an update to my blog so I thought I’d better get in gear and do something about it. So for now here is a reminder for those who use Memcached with Enyim.

USE THE [Serializable] ATTRIBUTE ON YOUR CLASSES.

I keep forgetting that.