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....

Tuesday, 27 January 2009

Stored procedure maintenance

One of the nice features of Oracle stored procedures, functions and packages is that you can create or recreate the database side code in the same statement. The advantage of this is that during upgrades/patches you can run ALL database code scripts again, which ensures nothing is missed - and also that the changes you have made haven't caused breaking changes in another procedure!

For example, the following will create or replace the following security package header..
CREATE OR REPLACE PACKAGE PKG_SECURITY
AUTHID DEFINER
AS
/* ------------------------------------------------------
Adds authentication for specified user to system
------------------------------------------------------ */
PROCEDURE proc_AddUserAuthentication(
userId IN USERSECURITY.USER_FK%TYPE,
encryptedPassword IN USERSECURITY.PASSWORD%TYPE,
saltValue IN USERSALT.SALT%TYPE,
auditUserId IN USERSECURITY.AUDITUSERID%TYPE);
END;
/
My recent digging around has revealed that you can also mimic this CREATE or REPLACE functionality in MySQL and Sql Server.

In MySQL you can call the DROP PROCEDURE IF EXISTS method....
DROP PROCEDURE IF EXISTS premierenvoy.proc_AddUserAuthentication;
CREATE
DEFINER = CURRENT_USER
PROCEDURE proc_AddUserAuthentication(
............

...and in Sql Server you can use the OBJECT_ID function and check whether the returning value is not null to determine whether to drop the procedure.
IF OBJECT_ID('proc_AddUserAuthentication') IS NOT NULL
DROP PROCEDURE proc_AddUserAuthentication;
GO
CREATE PROCEDURE proc_AddUserAuthentication(
............

Thursday, 8 January 2009

Using Data Grids in WPF

One of the biggest omissions from the .NET 3.5. WPF control library is a Data Grid. There are many 3rd party implementations for sale of free (just do a Google search on WPF and DataGrid). However, it is also possible to use the standard Windows forms DataGrid within your WPF form.

This excellent article by Rahul Saxen explains how this can be achieved.

Thursday, 20 November 2008

MySQL Triggers

My recent 'baptism of fire' with MySQL triggers has revealed a couple of differences with their cousins in the SQL Server and Oracle domain.

1) You need seperate triggers for UPDATE, INSERT and DELETE. Oracle and SQL Server will let you combine these into one trigger.

2) You cannot create triggers dynamically using a Procedure. The Execute command in the stored procedures works fine when creating a table, but try creating a Trigger. The parser throws an error stating that you can not run these types of commands.

The real problem for me has been 2). I often rely on writing procedures to apply auditing to a table (composed of audit table and trigger to insert the data from the main table into the audit table).

Thursday, 6 November 2008

Creating copies of tables in Oracle, SqlServer and MySQL

As the sub-title of my blog suggests, I am from an Oracle background. But I also have dealings with SQL Server and MySQL databases. In fact the latest project I'm working on is going to have to work with all three databases!

One issue I have encountered recently is how to create copies of tables in SQL Server and MySQL. In Oracle you would use the AS keyword in the CREATE table statement and select from the table that is being copied. For example:

CREATE TABLE myCopyTable AS
SELECT * FROM myOriginalTable;

You can add a WHERE clause to restrict the records that are included in the copy. Making this WHERE clause into a condition that is not achievable (such as WHERE 1 =2) will result in a copy of the table without the data.
Note:
This copy does not include any indexes/constraints on the original table.

In MySQL it is similar but you do not need the AS keyword. For example:

CREATE TABLE myCopyTable
SELECT * FROM myOriginalTable;

Alternatively you can actually create an exact copy including all constraints and indexes. This is achieved by using the LIKE keyword.

CREATE TABLE myCopyTable
LIKE myOriginalTable;

Note: This won't take the data across, you would have to insert that afterwards
(e.g. INSERT INTO myCopyTable SELECT * FROM myOriginalTable;)

Finally, SQL Server... which wins the prize for the strangest implementation. You actually use a SELECT command to create your new table :o

SELECT *
INTO myCopyTable
FROM myOriginalTable;

I really don't like the way this is implemented as there is no mention of a table being created in the SQL statement. Even though this is what the user is trying to achieve.
Anyhow, no point complaining at least they provided a way of copying a table :)