mORMot Open Source

reiser

Member

Registered: 2012-07-05

Posts: 5

SQLIte3 dynamic vs static linking - issues

Hello,

I am using latest nightly build of mORMot framework with XE2. Few issues I've ran into, and not sure how to fix:

- when I use static linking (by just adding SynSQLite3Static to uses clause), FastMM reports dozens of "Unknown" objects memory leaks upon application shutdown. Otherwise, app runs find and database password encryption works fine. The leak looks like this:

- when I use dynamic linking (sqlite3 := TSQLite3LibraryDynamic.Create('sqlite3.dll') in the code), memory leaks don't happen anymore, but providing password for the database doesn't seem to have any effect. With static linking, it successfully encrypts the database with provided password, but with dynamic linking it doesn't do the encryption at all. I open connection to the DB like this:

TSQLDBSQLite3ConnectionProperties.Create(StringToUTF8(FDatabasePath), '', '', StringToUTF8(ADatabasePassword));

Last edited by reiser (2016-01-13 09:14:08)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: SQLIte3 dynamic vs static linking - issues

You are right: dynamic linking it doesn't do the encryption at all.

What is the SQlite3 version used?
Are you sure you updated the latest .obj from http://synopse.info/files/sqlite3obj.7z ?
See http://synopse.info/files/html/Synopse% … l#TITL_113

reiser

Member

Registered: 2012-07-05

Posts: 5

Re: SQLIte3 dynamic vs static linking - issues

I use 3.10.0 version of SQLite, that comes with mORMot framework. I copied .obj files properly (app wont compile without them anyway), and added these folders to the library path:

mormot\
mormot\sqlite3
mormot\syndbdataset

If I exclude FastMM from uses list, app doesnt leak memory but fails with $C0000005 AV at 0x10171850 address, with following call stack:

:10ac1850 
:76e26d3a USER32.GetThreadDesktop + 0xd7
:76e26ded ; C:\windows\syswow64\USER32.dll
:76e26e4c ; C:\windows\syswow64\USER32.dll
:7750010a ntdll.KiUserCallbackDispatcher + 0x2e
:76e7eeea ; C:\windows\syswow64\USER32.dll
:76e262fa ; C:\windows\syswow64\USER32.dll
:76e4f9ff ; C:\windows\syswow64\USER32.dll
:76e4f7a4 USER32.GetCursor + 0xa4
:76e4f8a9 ; C:\windows\syswow64\USER32.dll
:76e262fa ; C:\windows\syswow64\USER32.dll
:76e26d3a USER32.GetThreadDesktop + 0xd7
:76e2966e ; C:\windows\syswow64\USER32.dll
:76e5208f ; C:\windows\syswow64\USER32.dll
:76e4cf5b USER32.DialogBoxIndirectParamAorW + 0xf7
:76e7f808 ; C:\windows\syswow64\USER32.dll
:76e7fae4 ; C:\windows\syswow64\USER32.dll
:76e7fbe7 USER32.MessageBoxTimeoutW + 0x52
:76e7fc66 USER32.MessageBoxTimeoutA + 0x76
:76e7fdb9 USER32.MessageBoxExA + 0x1b
:76e7fdfe USER32.MessageBoxA + 0x18
:0040476d ScanForMemoryLeaks + $3F9
:00404b1d FinalizeMemoryManager + $29
:00407864 FinalizeUnits + $40
:00407c4a @Halt0 + $A2
:752b337a kernel32.BaseThreadInitThunk + 0x12
:77529882 ntdll.RtlInitializeExceptionChain + 0x63
:77529855 ntdll.RtlInitializeExceptionChain + 0x36

If I do dynamic linking of sqlite, none of these errors occur, but encryption doesn't work.

Here is the code of the unit that does the database stuff:

unit uStorage;

interface

uses
  SynCommons, SynDB, SynCrypto, SynDBSQLite3, superobject, System.SysUtils;

type
  TStorageBlob = (sbSettings = 0, sbAmazonMarketplaces, sbGoogleGeo, sbGoogleCategories);

  TStorage = class
  private
    FDatabasePath: String;
    FEncryptionKey: RawUTF8;
    FConnection: TSQLDBSQLite3ConnectionProperties;
  public
    class procedure Initialize(const ADatabasePath, ADatabasePassword, AEncryptionKey: String);
    class procedure Deinitialize;

    constructor Create(const ADatabasePath, ADatabasePassword, AEncryptionKey: String);
    destructor Destroy; override;

    function GetBlob(const AStorageBlob: TStorageBlob): TBlobData;
    function GetBlobAsJSON(const AStorageBlob: TStorageBlob): ISuperObject;
    procedure PutBlob(const AStorageBlob: TStorageBlob; ABlob: TBlobData); overload;
    procedure PutBlob(const AStorageBlob: TStorageBlob; AJSON: ISuperObject); overload;
  end;

var
  Storage: TStorage;

implementation

uses
  {$IFDEF DEBUG} Forms.Debug, {$ENDIF}
  SynZip, System.Classes, uGlobalVars;

{ TStorage }

class procedure TStorage.Initialize(const ADatabasePath, ADatabasePassword, AEncryptionKey: String);
begin
  Storage := TStorage.Create(ADatabasePath, ADatabasePassword, AEncryptionKey);
end;

class procedure TStorage.Deinitialize;
begin
  FreeAndNil(Storage);
end;

constructor TStorage.Create(const ADatabasePath, ADatabasePassword, AEncryptionKey: String);
begin
  FDatabasePath := ADatabasePath;
  FEncryptionKey := StringToAnsi7(AEncryptionKey);

  FConnection := TSQLDBSQLite3ConnectionProperties.Create(StringToUTF8(FDatabasePath), '', '', StringToUTF8(ADatabasePassword));
  FConnection.ExecuteNoResult('PRAGMA locking_mode = EXCLUSIVE', []);
  FConnection.ExecuteNoResult('PRAGMA page_size = 4096', []);
  FConnection.ExecuteNoResult('CREATE TABLE IF NOT EXISTS data (id INTEGER PRIMARY KEY, data BLOB)', []);

  {$IFDEF DEBUG} DebugLn('Storage opened', ditApplication); {$ENDIF}
end;

destructor TStorage.Destroy;
begin
  FreeAndNil(FConnection);
  {$IFDEF DEBUG} DebugLn('Storage closed', ditApplication); {$ENDIF}
  inherited;
end;

function TStorage.GetBlob(const AStorageBlob: TStorageBlob): TBlobData;
var
  query: TSQLDBStatement;
  size1, size2: Integer;
begin
  result := '';
  query := FConnection.NewThreadSafeStatement;
  try
    query.Prepare('SELECT id, data FROM data WHERE id = ?', TRUE);
    query.Bind(1, Integer(AStorageBlob));
    query.ExecutePrepared;
    if query.Step then
    begin
      result := query.ColumnBlob(1);
      size1 := Length(result);
      if FEncryptionKey <> '' then
        result := TAESCFB.SimpleEncrypt(result, FEncryptionKey, FALSE, TRUE);
      result := UncompressString(result);
      size2 := Length(result);
      {$IFDEF DEBUG} DebugLn(Format('Storage: Blob retrieved [id: %d; size: %db > %db]', [Integer(AStorageBlob), size1, size2]), ditApplication); {$ENDIF}
    end
    else
    begin
      {$IFDEF DEBUG} DebugLn(Format('Storage: Failed to retrieve blob [%d]', [Integer(AStorageBlob)]), ditException); {$ENDIF}
    end;
  finally
    query.Free;
  end;
end;

function TStorage.GetBlobAsJSON(const AStorageBlob: TStorageBlob): ISuperObject;
var
  blob: TBlobData;
  s: String;
begin
  blob := GetBlob(AStorageBlob);
  s := UTF8ToString(blob);
  result := SO(s);
  {$IFDEF DEBUG}
  if not Assigned(result) then
    DebugLn(Format('Storage: Failed to cast blob to JSON [id: %d]', [Integer(AStorageBlob)]), ditApplication, s);
  {$ENDIF}
end;

procedure TStorage.PutBlob(const AStorageBlob: TStorageBlob; ABlob: TBlobData);
var
  query: TSQLDBStatement;
  size1, size2: Integer;
begin
  query := FConnection.NewThreadSafeStatement;
  try
    query.Prepare('INSERT or REPLACE INTO data (id, data) VALUES (?, ?)', FALSE);
    query.Bind(1, Integer(AStorageBlob));
    size1 := Length(ABlob);
    ABlob := CompressString(ABlob);
    if FEncryptionKey <> '' then
      ABlob := TAESCFB.SimpleEncrypt(ABlob, FEncryptionKey, TRUE, TRUE);
    size2 := Length(ABlob);
    query.BindBlob(2, ABlob);
    query.ExecutePrepared;
    {$IFDEF DEBUG} DebugLn(Format('Storage: Blob stored [id: %d; size: %db > %db]', [Integer(AStorageBlob), size1, size2]), ditApplication); {$ENDIF}
  finally
    query.Free;
  end;
end;

procedure TStorage.PutBlob(const AStorageBlob: TStorageBlob; AJSON: ISuperObject);
begin
  PutBlob(AStorageBlob, StringToUTF8(AJSON.AsJSon(FALSE, FALSE)));
end;

end.

Also, one more thing that I noticed, I cannot open the same database file with different platforms of the app (32bit/64bit). If I try to open database file that is made by 32bit app, with 64bit app (and vice versa), it throws "Error SQLITE_CORRUPT (11) using 3.10.0 - 'database disk image is malformed' extended_errcode=11" error.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: SQLIte3 dynamic vs static linking - issues

Under win64, it uses the external sqlite3-64.dll which does not support encryption.

I am not able to reproduce the memory leak here.
I run the TestSQL3 regression tests with EnableMemoryLeak, and no leak was reported.

reiser

Member

Registered: 2012-07-05

Posts: 5

Re: SQLIte3 dynamic vs static linking - issues

Hi,

I figured out the issue. In one part of the code I had something like this:

var
  conn: TSQLDBSQLite3ConnectionProperties;
  query: TSQLDBStatement;
begin
  conn := TSQLDBSQLite3ConnectionProperties.Create...
  try
    query := conn.NewThreadSafeStatement;
    try
      query.Prepare(..., TRUE);
      query.ExecutePrepared;
      if query.Step then
      begin
        query.Reset;
        query.Prepare(..., TRUE);
        query.ExecutePrepared;
      end;
    finally
      query.Free;
    end;
  finally
    conn.Free;
  end;
end;

It seems that I was not resetting query properly. If I had two separate queries instead doing what I did above, it works without leaks. Now, if I may ask two more questions:

- why does it leak with static sqlite, but not with dynamic
- there is no way to enable password encryption under win64 for now?

Thanks

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: SQLIte3 dynamic vs static linking - issues

IMHO it has nothing to do with static or dynamic linked SQLite3 library.
I do not know why the behavior is diverse - perhaps your sqlite3.dll is older than our .obj files...

There is no way to enable password encryption under Win64 now.