Implementing your own password hash in mORMot

We have to authenticate against a legacy user database already populated, storing passwords encrypted in a proprietary format. The table also use the USERNAME field as the primary index. This means that we can’t use the ORM, as mORMot’s ORM requires a single integer primary key field.

Authentication in mORMot is handled through a dedicates set classes: TSQLAuthUser and TSQLAuthGroup.

For simplicity’s sake we’ll keep the implementation brief and simple.

mORMot ships with a couple of predefined groups: Admin, Supervisor, User and Guest. See the documentation for more information.

In our sample we’ll force all our users to belong to group User.

The first step would be to derive our own User from TSQLAuthUser:

type
  TDummySQLUser = class(TSQLAuthUser)
  protected
    class function ComputeHashedPassword(const aPasswordPlain: RawUTF8): RawUTF8; override;
  public
    constructor Create( aUserName, aPassword : RawUTF8 ); reintroduce;
  end;

The new constructor simply gives us an opportunity to set some of the internals to sane defaults. For one we need to set the internal ID, the encrypted password (as it’s stored in the database) and we need to force the group. Browsing through the mORMot code shows that the User group will be assign the ID 3. This means that the User’s GroupRights property should be set to 3, but because mORMot’s ORM stores referenced objects as their ID’s, 3 needs to be type casted:

  GroupRights := TSQLAuthGroup($3);

This leaves us with the following implementation:

constructor TDummySQLUser.Create( aUserName, aPassword : RawUTF8 );
begin
  inherited Create;
  fID := 1;
  LogonName := aUserName;
  PasswordHashHexa := aPassword;
  GroupRights := TSQLAuthGroup($3);
end;

The magic happens in the overridden method ComputeHashedPassword. This method gets called by the system to calculate the hash of a plain text password. In our version we simply implement it as such:

class function TDummySQLUser.ComputeHashedPassword(const aPasswordPlain: RawUTF8): RawUTF8; 
begin
  Result := EncryptMyPass( Value );
end;

Dependency Injection the mORMot way

Dependency Injection and Inversion of Control for part of the SOLID principles. That’s a lot of gobbledygook, but trust me, it’s a Good Thing™ It will help in keeping things separate and prevent the urge to create cross dependencies.

I haven’t seen any implementation of these patterns that doesn’t make use of interfaces. Interfaces are great as they enforce a contract between provider (whatever implements the interface) and consumer (the thing that uses the interface).

Real world example time:

Let’s say we have a service we want to be able to talk to without knowing the particulars of it’s implementation. For this example we’ll use an application settings provider. Application settings can be stored in many different formats, eg INI files, JSON files, Registry or even in a database. When reading these settings, we really shouldn’t care where these settings are stored.

The contract

The first step would be to declare the IApplicationSettings interface:

type
  IApplicationSettings = interface(IInvokable)
    ['{E1E76D15-B43A-4DF5-91CE-9CAC7AAAF789}']
    function getValue( aSetting : string; aDefault : string ) : string;
    function getValueInt( aSetting : string; aDefault : integer ) : integer;
  end;

This is just a basic example, but good enough to demonstrate the principles.
NB: Your interface needs to descend from IInvokable, or it won’t contain any RTTI. It also needs to be registered with mORMot. It makes sense to register the interface in the unit’s initialization section:

initialization
  TInterfaceFactory.RegisterInterfaces( [TypeInfo(IApplicationSettings)] );
end.

The service provider

A basic class that can provide what the contract demands.

Implementation of our interface:

type
  TINISettings = class( TInterfacedObject, IApplicationSettings )
  private
    fINIFile : TINIFile;
    fSection : string;
  public
    constructor Create( aSettigsFileName : TFileName; aSection : string );
    destructor Destroy; override;
    //Implement the interface
    function getValue( aSetting : string; aDefault : string ) : string;
    function getValueInt( aSetting : string; aDefault : integer ) : integer;
  end;
implementation

{ TINISettings }

constructor TINISettings.Create(aSettigsFileName: TFileName; aSection : string);
begin
  inherited Create;
  fSection := aSection;
  fINIFile := TIniFile.Create( aSettigsFileName );
end;

destructor TINISettings.Destroy;
begin
  fINIFile.Free;
  inherited;
end;

function TINISettings.getValue(aSetting, aDefault: string): string;
begin
  result := fINIFile.ReadString( fSection, aSetting, aDefault );
end;

function TINISettings.getValueInt(aSetting: string; aDefault: integer): integer;
begin
  result := fINIFile.ReadInteger( fSection, aSetting, aDefault );
end;

Simple and easy.

The magic

First we declare our class.

type
 TMyApplication = class(TInjectableObject)
 private
   FSettings: IApplicationSettings;
 public
   procedure PrintSomeSettings;
 published
   property Settings: IApplicationSettings read FSettings write FSettings;
 end;

The magic happens in as we descend from TInjectableObject. Any published interfaces will automagically be provided to our class.

This means that the following code will be executed without error and provide the expected output.

procedure TMyApplication.PrintSomeSettings;
begin
  writeln( 'Value of TestString: ', Settings.getValue('TestString', 'Not found' ) );
end;

All that’s left is to create our class, and call our test method

procedure TestIt;
var
  MyApplication : TMyApplication;
begin
  MyApplication := TMyApplication.CreateInjected( [],[], [TINISettings.Create( ChangeFileExt(ParamStr(0), '.INI'), 'Test' )]);
  MyApplication.PrintSomeSettings;
end;

The first two empty arrays are for stubs and other resolvers respectively, none of which we need in this example.

Why bother

As Stefan Glienke pointed out, in a previous version of this post, we were implementing the Service Locator pattern, which is considered an anti-pattern by many. It’s very close to Dependency Injection, but tends to hide the class’s dependencies. It also breaks the “Tell, Don’t Ask” principle.

Connecting to legacy databases and publishing a RESTful interface to it

Most systems, especially in the DDD area, needs to integrate to a legacy system. In our case our we had to communicate to a Firebird 1.5 database.

The first step was to define our Data Transfer Objects:

type
  TLegacyID = type RAWUTF8;

  TLegacyAccount = class(TSynPersistent)
  private
    fLegacyID: TLegacyID;
    fDateModified: TDateTime;
    fUserCreated: RAWUTF8;
    fDateCreated: TDateTime;
    fName: RAWUTF8;
    fisActive: Boolean;
  public
    constructor Create; overload; override;
    constructor Create( aID : TLegacyID; aName : RAWUTF8; aIsActive : Boolean;
      aDateCreated, aDateModified : TDateTime; aUserCreated : RAWUTF8 ); overload;
  published
    property ID : TLegacyID read fRevelightID write fRevelightID;
    property Name : RAWUTF8 read fName write fName;
    property isActive : Boolean read fisActive write fisActive;
    property DateCreated : TDateTime read fDateCreated write fDateCreated;
    property DateModified : TDateTime read fDateModified write fDateModified;
    property UserCreated : RAWUTF8 read fUserCreated write fUserCreated;
  end;

  TLegacySupplier = class(TLegacyAccount)
  end;

Here we declare a unique type to identify our legacy IDs (strings). We also do a basic map of our data layout, with a customized constructor for ease of creation. One can add other methods later to handle copies and assignments.

The next step was to define our service:

type
  ILegacyStockQuery = interface(IInvokable)
    ['{2BDC9F78-B9C2-4621-A557-F87F02AC0581}']
    function GetSupplier(const aID: TLegacyID; out Supplier: TLegacySupplier): TCQRSResult;
  end;

we’ll publish a service as LegacyStockQuery with a single method GetSupplier. This method will return a JSON encoded representation of our TLegacySupplier, ready to be consumed by a client.

To implement it:

type
  TLegacyStockQuery = class(TInterfacedObject, ILegacyStockQuery)
  private
    fDbConnection : TSQLDBConnectionProperties;
  public
    constructor Create( const aProps: TSQLDBConnectionProperties ); overload;
    function GetSupplier(const aID: TRevelightID; out Supplier: TLegacySupplier): TCQRSResult;

    property DbConnection : TSQLDBConnectionProperties read fDbConnection write fDbConnection;
  end;

We keep a copy of our database connection properties local to our instance to ensure thread safety.

{ TLegacyStockQuery }

constructor TLegacyStockQuery.Create(const aProps: TSQLDBConnectionProperties);
begin
  fDbConnection := aProps;
  inherited Create;
end;

function TLegacyStockQuery.GetSupplier(const aID: TLegacyID; out Supplier: TLegacySupplier): TCQRSResult;
var
  Res, Address : ISQLDBRows;
begin
  Result := cqrsNotFound;
  Res := fDbConnection.Execute( 'select * from SUPPLIERS where SUPPLIER_ID=? ', [aID] );
  if Res.Step then begin
    Result := cqrsSuccess;
    Supplier.ID := Res['SUPPLIER_ID'];
    Supplier.Name := Res['SUPPLIER_NAME'];
    Supplier.isActive := Res['ACTIVE_FLAG'] = 'Y';

    Supplier.DateCreated := Res['DATE_CREATED'];
    Supplier.DateModified := Res['DATE_MODIFIED'];
    Supplier.UserCreated := Res['USER_CREATED'];
  end;
end;

Execute the query against the legacy database and populate the DTO. Using the ISQLDBRows interface means less object maintenance and cleaner code.

To kick the whole thing off we have:

procedure StartServer( aDbURI : RawURF8 );
var
  aDbConnection : TSQLDBConnectionProperties;
  aStockServer  : TSQLRestServerFullMemory;
  aHTTPServer   : TSQLHttpServer;
begin
  aDbConnection := TSQLDBZEOSConnectionProperties.Create( aDbURI, '', '', '' );
  aStockServer  := TSQLRestServerFullMemory.Create([]);
  try
    aStockServer.ServiceDefine( TLegacyStockQuery.Create( aDbConnection ), [ILegacyStockQuery]);
    aHTTPServer := TSQLHttpServer.Create( DEFAULT_HTTP_PORT, [aStockServer] );
    try
      aHttpServer.AccessControlAllowOrigin := '*'; // allow cross-site AJAX queries
      writeln('Background server is running.'#10);
      writeln('Cross-Platform wrappers are available at ',
          DEFAULT_HTTP_PORT ,'/', DEFAULT_SERVER_ROOT );
      write('Press [Enter] to close the server.');
      readln;
    finally
      aHTTPServer.Free;
    end;
  finally
    aStockServer.Free;
  end;
end; 

It would be better to use dependency injection here, but we’ll get in to that later.

When invoking it, we used a Zeos DB URI, like this one:

zdbc:firebird-1.5://tst-db-svr-1/srv/db/EXAMPLE.FDB?username=SYSDBA;password=masterkey

Tips!

Remember to register your Object array types with

TJSONSerializer.RegisterObjArrayForJSON(TypeInfo(<ArrayType>),<ObjType>);

e.g.

TJSONSerializer.RegisterObjArrayForJSON(TypeInfo(TLegacySupplierObjArray),TLegacySupplier);

ORM – Modeling relationships

Being able to populate data in a simple data store is great, but that is usually not good enough for any real world application. Most real world models would have some relationship between classes.

Let’s take our Note example. If you have no idea what example I’m talking about, maybe start here first.

Let’s say we needed to associate our notes to a specific case. The first step would be to modify the NoteORM unit to look like this:

unit NoteORM;
interface

uses Classes, SynCommons, mORMot;

type
  TNoteCase = class(TSQLRecord)
  private
    FDescription: RawUTF8;
  published
    property Description: RawUTF8 read FDescription write FDescription;
  end;

  TNote = class(TSQLRecord)
  private
    FBody: RawUTF8;
    FTitle: RawUTF8;
    FNoteCase: TNoteCase;
  published
    property NoteCase: TNoteCase read FNoteCase write FNoteCase;
    property Body: RawUTF8 read FBody write FBody;
    property Title: RawUTF8 index 80 read FTitle write FTitle;
  end;

implementation

end.

Here we’ve added the TNoteCase class, and added the NoteCase property to TNote. NoteCase is of type TNoteCase.

Next, we add the new TNoteCase class to our model:

RestServer := TSQLRestServerDB.CreateWithOwnModel( [NoteORM.TNote, NoteORM.TNoteCase], 'Test.DB' );

And now we can generate some data:

procedure TestIt(rest:TSQLRest);
var
  aCase : NoteORM.TNoteCase;
  aNote : NoteORM.TNote;
begin
  aCase := NoteORM.TNoteCase.Create;
  try
    aCase.Description := 'Case 1';
    rest.Add( aCase, true );
    aNote := NoteORM.TNote.Create;
    try
      aNote.Title := 'Note 1';
      aNote.Body  := 'Note 1 body. Lots of other stuff too.';
      aNote.NoteCase := aCase.AsTSQLRecord;
      rest.Add( aNote, true );

      aNote.Title := 'Note 2';
      aNote.Body  := 'Note 2 body. Lots of other stuff too. Some more things here.';
      rest.Add( aNote, true );
    finally
      aNote.Free;
    end;
  finally
    aCase.Free;
  end;
end;

The most important bit to notice is:

      aNote.NoteCase := aCase.AsTSQLRecord;

This is because mORMot stores the referential key, and not the actual instance. That means that under normal circumstances you can not access aNote.NoteCase.Description, as this will generate an Access Violation.

Once the records have been committed to the storage engine, however, there are other ways of dealing with these classes.

One way is by using the CreateJoined constructor:

procedure TestIt(rest:TSQLRest);
var
  aNote : NoteORM.TNote;
begin
  aNote := NoteORM.TNote.CreateJoined( rest, 4);
  try
    writeln( 'Case: ', aNote.NoteCase.Description );
    writeln( 'Title: ', aNote.Title );
    writeln( 'Body: ', aNote.Body );
  finally
    aNote.Free;
  end;
end;

This will auto load and populate and manage all the contained instances. This method can be slow, especially if the class graph is big.

The other option is to use lazy loading, where classes are created and managed as and when they’re required:

procedure TestIt(rest:TSQLRest);
var
  aCase : NoteORM.TNoteCase;
  aNote : NoteORM.TNote;
begin
  aNote := NoteORM.TNote.Create( rest, 4);
  try
    aCase := NoteORM.TNoteCase.Create( rest, aNote.NoteCase );
    try
      writeln( 'Case: ', aCase.Description );
      writeln( 'Title: ', aNote.Title );
      writeln( 'Body: ', aNote.Body );
    finally
      aCase.Free;
    end;
  finally
    aNote.Free;
  end;
end;

Investigating the ORM – retrieving data

In a previous post we illustrated how to use the ORM to populate a simple data store. Now, we’ll look how to retrieve the data.

If you have the ID of the record / instance you’re after, you can simply call:

  Note := TNote.Create(Client,ID);

for mORMot to fetch and populate Note.

Being an implementation of the Active Record Pattern, basic operations, such as Update, Retrieve and Delete are part of the basic implementation. TSQLRecord also provides list retrieval methods and lazy loading to all it’s descendants.

For example:

procedure TestIt(rest:TSQLRest);
var
  Note : NoteORM.TNote;
begin
  Note := TNote.CreateAndFillPrepare( rest, 'Body LIKE ?',['Sample 2%']);
  try
    while Note.FillOne do
    begin
      writeln( 'Note ID: ', Note.ID );
      writeln( 'Note Title: ', Note.Title );
      writeln( 'Note Body: ', Note.Body );
    end;
  finally
    Note.Free;
  end;
end;

will produce:

Note ID: 3
Note Title: This is the title!
Note Body: Sample 2. This is a sample note.

Investigating the ORM – persisting data

The ORM (Object Relational Mapper) that ships with mORMot implements the Active Record pattern. All objects that you want to persist must inherit from TSQLRecord, which handles a lot of the marshaling and facilitates querying and verification.

Let’s look at a simple sample:
Let’s say you have an application server that stores notes.

The very first thing we’ll do is create a unit to define our ORM classes. Let’s call this one NoteORM.pas.

unit NoteORM.pas;
interface

uses Classes, SynCommons, mORMot;

type
  TNote = class(TSQLRecord)
  private
    FBody: RawUTF8;
  published
    property Body: RawUTF8 read FBody write FBody;
  end;

implementation

end.

That’s pretty simple! Great, how do we use it?

To illustrate the usage, here’s a basic test application framework:

program TestNotes;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  {$I SynDprUses.inc}
  SysUtils,
  SynSQLite3Static,
  mORMot,
  mORMotSQLite3,
  NoteORM in 'NoteORM.pas';


procedure TestIt(rest:TSQLRest);
var
  Note : NoteORM.TNote;
begin
  Note := TNote.Create;
  Note.Body := 'Sample 1. This is a sample note.';
  rest.Add( Note, true ); 
end;

var RestServer: TSQLRestServerDB;
    RestClient: TSQLRestClientURI;
begin
  RestServer := TSQLRestServerDB.CreateWithOwnModel( 
                    [NoteORM.TNote], 'Test.DB' );
  RestClient := TSQLRestClientURIDll.Create(
                    TSQLModel.Create(RestServer.Model),
                    @URIRequest);
  try
    // initialize URIRequest() with the aStatic database
    RestServer.ExportServer;
    USEFASTMM4ALLOC := true;
    RestServer.CreateMissingTables;
    RestClient.Model.Owner := RestClient;
    TestIt(RestClient);
  finally
    RestClient.Free;
    RestServer.Free;
  end;
end.

Running the application, a new SQLite database will be created, called Test.DB. In it there will be a table called Note, with a two fields: ID and Body. A single row will exist:

ID          Body
----------  --------------------------------
1           Sample 1. This is a sample note.

Let’s extend our Note class to add a title. We’d like the title to be restricted to 80 characters.

We simply modify NoteORM.pas to look like this:

  TNote = class(TSQLRecord)
  private
    FBody: RawUTF8;
    FTitle: RawUTF8;
  published
    property Body: RawUTF8 read FBody write FBody;
    property Title: RawUTF8 index 80 read FTitle write FTitle;
  end;

Notice the “index 80” part: This tells the ORM that we would like to restrict the width of the field in the database to 80 characters.

Running the application now, will yield the following in our database:

ID          Body                              Title
----------  --------------------------------  ----------
1           Sample 1. This is a sample note.
2           Sample 1. This is a sample note.

Great! mORMot automatically added the Title column, but we forgot to add test data. Let’s do that now:

procedure TestIt(rest:TSQLRest);
var
  Note : NoteORM.TNote;
begin
  Note := TNote.Create;
  Note.Body := 'Sample 2. This is a sample note.';
  Note.Title := 'This is the title!';
  rest.Add( Note, true );
end;

Run it and it produces the following in our database:

ID          Body                              Title
----------  --------------------------------  ----------
1           Sample 1. This is a sample note.
2           Sample 1. This is a sample note.
3           Sample 2. This is a sample note.  This is the title!

That was quite easy! Next, we see how we can retrieve data using the ORM.

Some mORMot basics

mORMot’s architecture is based on a modular multi-tier system.

Puzzles

Everything you do in mORMot will follow this pattern. The server component can be embedded in the same physical application, or it can be a proxy, passing the requests and queries along to another instance, which will work with the same pattern again.

Getting started

As mORMot is actively being developed, the best way of getting hold of the library is to grab the latest, bleeding edge, code. We do that by:

  1. Getting and installing git
  2. Grab a copy of the repository
  3. Configuring Delphi to use the library

Step 1: Get git!

This is easy enough. Visit http://msysgit.github.io/ and download and install the appropriate package.

Step 2: Get the code!

Almost as easy! Open a command prompt, change to the drive and directory where you want the library to live and type:

git clone https://github.com/synopse/mORMot.git

I suggest something like

E:
git clone https://github.com/synopse/mORMot.git mORMot

This will create and download a copy of the repository to E:\mORMot\
Next we need to grab a copy of the SQLite 3 static .obj files. This will allow our mORMot based application to embed the SQLite engine in our application.

Grab http://synopse.info/files/sqlite3obj.7z and extract to yourE:\mORMot\ folder. If you plan to develop for 64-bit targets grab http://synopse.info/files/SQLite3-64.7z and place sqlite3-64.dll in your project’s build directory or in your Windows directory.

Step 3: Configure the environment!

Add the following to your Delphi Library Path:

  • E:\mORMot;
  • E:\mORMot\SQLite3;
  • E:\mORMot\SynDBDataset;
  • E:\mORMot\SQLite3\DDD;
  • E:\mORMot\SQLite3\DDD\dom;
  • E:\mORMot\SQLite3\DDD\infra;

Step 4: Test the environment!

Open E:\mORMot\SQLite3\TestSQL3.dpr and run it. Make sure all the tests pass.

You’re done!