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;
Advertisements

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.