Wednesday 25 February 2009

Oracle XmlType and ODAC .NET provider

I have been having some issues today with a method that inserted Xml into an XmlType field in an Oracle database. It had been working fine, but recently the Xml had grew in size considerably. I was now getting the following Oracle error returned from the ODAC component:

ORA-01461: can bind a LONG value only for insert into a LONG column

The Insert Query I was running looked a bit like this....

INSERT INTO myTable
(anId, myXmlField)
VALUES
(:anId, :myXmlField);

When I ran the query I had two parameters, the second (:myXmlField) was an Xml string. It was this that caused the issue. Thanks to this post by Stuart Campbell I found out that in order to pass a long string value to an XmlType field you must:

1) Set the parameter type of the xml parameter (:myXmlField) to Clob;
2) Use the XmlType() call in the query:
INSERT INTO myTable
(anId, myXmlField)
VALUES
(:anId, XmlType(:myXmlField));

This did resolve the issue, but in the end I ended up changing the XmlType field into a Clob! This is because 1)I didn't need to xPath the Xml in the database and 2) I couldn't use XmlType implementations in our SqlServer data access layer as we have to support earlier versions of this database.


Tuesday 24 February 2009

For those in the North West.....

There is a free MSDN event on at the Lowry in Manchester on Thurday afternoon (26-Feb-2009).
The event is covering WPF and Silverlight 2.

See here for further details and registration.

Monday 23 February 2009

Custom authentication

Occasionally you may have to develop a system that, rather then relying on an existing security system (e.g. active directory, novell), requires you to write a custom authentication system.

Usually, in such systems the user data and passwords are stored in a table in the database. These records can then be queried to determine whether the user requesting access has the correct password.

Okay, this is simple enough... but storing a users password in a table is not particularly great. A hacker or other unauthorised user might get access to the table and thus be able to log onto our system as anyone they choose!

It is better practice to encrypt the password before storing it in the database. The software will have to retrieve and decrypt this value before the users password entry can be validated.

Even if encryption is used, the account could still be open to attack as Users might choose an obvious password. Fortunately, extra precautions can be added to the security system to help further protect against unauthorised access. These are:

1) Adding rules for password format - Regular expressions can be used to ensure when a user sets their password it adheres to certain standards (e.g. contains 1+ numbers, is a certain length).

2) Password attempts - Implement code to lock the account if the password is entered incorrectly over a certain number of times. To save on hassle for the system administrators you might want to consider just locking the account for a set period (e.g. 2 hours).

3) Encrypt with Salt values - A salt value is a value that is added to password prior to encryption to make the encryption more difficult to crack for hackers using dictionary attacks. I would strongly recommend using salt values if you are using encryption.

In .NET salt values can be generated by using a class such as RNGCryptoServiceProvider. The code below shows a static method I use to generate salt values.
/// 
/// Generate a random salt value
///
///
public static string GenerateSalt()
{
RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider();
byte[] randomNumber = new byte[16];

rng.GetBytes(randomNumber);
return Convert.ToBase64String(randomNumber);
}

The encryption algorithms in .NET (such as the preferred RijndaelManaged class) allow you to set the salt value when encrypting and decrypting values.

It is important to remember that when you use a salt value that this will also need to be stored somewhere, so that it can be retrieved by the code when it needs to decrypt. Salt values should NEVER be stored in code as a hacker could use a disassembler on the assembly and discover the value.

I always dynamically create a new salt value every time a users password is changed. This value
is then stored in a separate table in the database, linked to the record by the user table primary key. This can then be easily retrieved when the code needs to decrypt the data.


I hope I've managed to cover the main points of creating a custom authentication system. It'll will be interesting to see what the next generation of security systems in WCF 4.0 and Azure will bring. I'm hoping that there will be considerable improvements so that developers will not have to keep reinventing the wheel with their own authentication methods. I am attending DevWeek next month in London, so I'll blog/twitter my discoveries....