Recent

Author Topic: Simple SQLite3 Demo with Encryption and PRAGMA - Updated  (Read 19712 times)

jdlinke

  • Jr. Member
  • **
  • Posts: 62
  • Just old me
Simple SQLite3 Demo with Encryption and PRAGMA - Updated
« on: July 09, 2014, 06:09:52 pm »
I put together a very simple demonstration for creating a SQLite3 Database and using a few of its features.

For this test application, I wanted to very simply try the following capabilities which I'll be using in a large application:
- Creation of a SQLite3 Database
- Creation of a database table
- Setting various database metadata (PRAGMA)
- Optionally encrypt the database using a key
- Change (or set if not initially set) the encryption key for the database at any time

Edit: Added
- Creating an Index
- Adding a row of data to the table
- Performing a very basic query
- Setting and reading various database metadata (Pragma)



The code is below and is heavily commented with notes and resources, and I've attached the demo as well.

Hope some of you beginners find this helpful. If any of you Lazarus Veterans have tips or corrections, please share.

If there's a more appropriate way to share this, please let me know.


LATEST CODE IS FURTHER DOWN THE THREAD HERE

Code: [Select]
unit Unit1;

// For this test application, I wanted to very simply try the following
// capabilities which I'll be using in a large application:
// - Creation of a SQLite3 Database
// - Creation of a database table
// - Setting various database metadata (PRAGMA)
// - Optionally encrypt the database using a key
// - Change (or set if not initially set) the encryption key for the database

// The application makes a new database file "new.db" within the local directory



// Using the following link, you can find a few options for versions
// of SQLite that provide support for encryption. Since I'm working
// mainly on Windows, I opted for the Open Source System.Data.SQLite
// http://wiki.freepascal.org/sqlite#Support_for_SQLite_encryption

// I used the "Precompiled Binaries for 32-bit Windows (.NET Framework 3.5 SP1)"
// and renamed SQLite.Interop.dll to sqlite3.dll, but you should be able to
// use any version you want as long as you have the required dependancies.
// http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

// Make sure the sqlite3.dll is in the same directory as your application, or you
// *will* have errors, and your application will not work.



// MORE HELPFUL SQLITE INFO

// To read more about the SQLite Encryption Extension (SEE),
// use the following URL (Section: How To Compile And Use SEE)
// http://www.sqlite.org/see/doc/trunk/www/index.wiki

// Details about the SQLite File Format (and particularly about
// the Database Header) can be found at:
// http://www.sqlite.org/fileformat2.html#database_header

// Information about the various database PRAGMA (metadata) statements
// can be found at:
// http://www.sqlite.org/pragma.html


{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, db, sqldb, sqlite3conn, FileUtil, Forms, Controls,
  Graphics, Dialogs, StdCtrls;

type

  { TForm1 }

  TForm1 = class(TForm)
    Button1: TButton;
    Button2: TButton;
    DataSource1: TDataSource;
    Label3: TLabel;
    txtNew: TEdit;
    txtOld: TEdit;
    Label1: TLabel;
    Label2: TLabel;
    SQLite3Connection1: TSQLite3Connection;
    SQLQuery1: TSQLQuery;
    SQLTransaction1: TSQLTransaction;
    txtPass: TEdit;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
  private
    { private declarations }
  public
    { public declarations }
  const
    // More information on the use of these values is below.
    // They need not be set as constants in your application. They can be any valid value
    application_id = 1189021115; // must be a 32-bit Unsigned Integer (Longword 0 .. 4294967295)
    user_version = 23400001;  // must be a 32-bit Signed Integer (LongInt -2147483648 .. 2147483647)

  end;

var
  Form1: TForm1;

implementation

{$R *.lfm}

{ TForm1 }

procedure TForm1.Button1Click(Sender: TObject);
var
  newFile : Boolean;
begin

  SQLite3Connection1.Close; // Ensure the connection is closed when we start


  // Set the password initially.
  // Could probably be done with a PRAGMA statement, but this is so much simpler
  // and once set, doesn't need to be reset every time we open the database.
  // txtPass can be left blank if you want an unencrypted database.
  SQLite3Connection1.Password := txtPass.Text;

  SQLite3Connection1.DatabaseName := 'new.db'; // Set the path to the database

  try

    // Since we're making this database for the first time,
    // check whether the file already exists
    newFile := not FileExists(SQLite3Connection1.DatabaseName);

    if newFile then
    begin

      // Make the database and the tables
      try
        SQLite3Connection1.Open;
        SQLTransaction1.Active := True;


        // Per the SQLite Documentation (edited for clarity):
        // The pragma user_version is used to set or get the value of the user-version.
        // The user-version is a big-endian 32-bit signed integer stored in the database header at offset 60.
        // The user-version is not used internally by SQLite. It may be used by applications for any purpose.
        // http://www.sqlite.org/pragma.html#pragma_schema_version
        SQLite3Connection1.ExecuteDirect('PRAGMA user_version = ' + IntToStr(user_version) + ';');


        // Per the SQLite Documentation:
        // The application_id PRAGMA is used to query or set the 32-bit unsigned big-endian
        // "Application ID" integer located at offset 68 into the database header.
        // Applications that use SQLite as their application file-format should set the
        // Application ID integer to a unique integer so that utilities such as file(1) can
        // determine the specific file type rather than just reporting "SQLite3 Database".
        // A list of assigned application IDs can be seen by consulting the magic.txt file
        // in the SQLite source repository.
        // http://www.sqlite.org/pragma.html#pragma_application_id
        SQLite3Connection1.ExecuteDirect('PRAGMA application_id = ' + IntToStr(application_id) + ';');


        // Here we're setting up a table named "USERS" in the new database
        SQLite3Connection1.ExecuteDirect('CREATE TABLE "USERS"('+
                    ' "id" Integer NOT NULL PRIMARY KEY AUTOINCREMENT,'+
                    ' "DTCreated" DateTime NOT NULL,'+
                    ' "Username" Text NOT NULL,'+
                    ' "PasswordSalt" Text NOT NULL,'+
                    ' "PasswordHash" Text NOT NULL,'+
                    ' "SecurityQues1" Text,'+
                    ' "SecurityAnsw1" Text);');


        SQLTransaction1.Commit;

      except
        ShowMessage('Unable to Create new Database');
      end;

    end;

  except
    ShowMessage('Unable to check if database file exists');
  end;


end;

procedure TForm1.Button2Click(Sender: TObject);
var
  newFile : Boolean;
begin

  SQLiteLibraryName := 'sqlite3.dll';

  SQLite3Connection1.Close; // Ensure the connection is closed when we start

  SQLite3Connection1.Password := txtOld.Text; // The current password

  SQLite3Connection1.DatabaseName := 'new.db'; // Set the path to the database

  // Update the database key
  try
    SQLite3Connection1.Open;
    SQLTransaction1.Active := True;


    // Here we change the key.
    // We use double-quotes here so that a blank key (IE: "") can be provided if
    // you want to remove encryption from the database.
    // This is a very simplistic demonstration. Ideally, we would take a stronger cryptographic approach
    // Some helpful info on this topic can be found at:
    // https://www.owasp.org/index.php/Cheat_Sheets
    // Per SQLite Documentation:
    // Note that the hexkey, rekey and hexrekey pragmas only work with SQLite version 3.6.8 and later.
    // http://www.sqlite.org/see/doc/trunk/www/readme.wiki
    // Section: Using the "key" PRAGMA
    SQLite3Connection1.ExecuteDirect('PRAGMA rekey = "' + txtNew.Text + '";');


    SQLTransaction1.Commit;
    SQLTransaction1.Active := False;
    SQLite3Connection1.Close;


    // Update the SQLite3Connection with the new password for future database transactions
    SQLite3Connection1.Password := txtNew.Text;


  except
    ShowMessage('Unable to set the new key using: PRAGMA rekey = ' + txtNew.Text + ';');
  end;

end;

end.


LATEST CODE IS FURTHER DOWN THE THREAD HERE
« Last Edit: July 11, 2014, 05:30:13 pm by jdlinke »
Lazarus 1.2.4 32-bit version on Windows 8.1 64-bit, Windows 7 64-bit, and Windows XP 32-bit

Currently developing TimberLog and the VirtualDBScroll Component Package

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Simple SQLite3 Demo with Encryption and PRAGMA
« Reply #1 on: July 10, 2014, 11:22:24 am »
Hi jdlinke,

Thanks a lot for writing and posting this!

If there's a more appropriate way to share this, please let me know.

Haven't looked at the code but it looks promising; depending on quality it might be a nice addition to the Lazarus examples in $(lazarusdir)\examples\database\; if you're happy with the example you can submit a bugtracker issue with the files attached so somebody can commit it to Lazarus.


Thanks
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

JZS

  • Full Member
  • ***
  • Posts: 184
Re: Simple SQLite3 Demo with Encryption and PRAGMA
« Reply #2 on: July 10, 2014, 12:24:09 pm »
jdlinke,
I tried your demo and it creates the "new.db" as expected, but the data is not encrypted. Am I missing anything here?

BTW: Thanks for the tip. It will be very helpful if it works.
« Last Edit: July 10, 2014, 12:28:52 pm by JZS »
I use recent stable release

jdlinke

  • Jr. Member
  • **
  • Posts: 62
  • Just old me
Re: Simple SQLite3 Demo with Encryption and PRAGMA
« Reply #3 on: July 10, 2014, 12:51:40 pm »
Assuming you set a key in the Editbox, are you using a version of SQLite3.dll that supports encryption? I've successfully used this on computers running Windows 7 and Windows 8.1 using a DLL from http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki


You may also try adding

Code: [Select]
SQLiteLibraryName := 'sqlite3.dll';
at the beginning of the buttonclick events to ensure you're using the local DLL which supports encryption, and not an older unsupported version that may be elsewhere on your system (Windows/System32 or something like that if I recall)
« Last Edit: July 10, 2014, 01:06:20 pm by jdlinke »
Lazarus 1.2.4 32-bit version on Windows 8.1 64-bit, Windows 7 64-bit, and Windows XP 32-bit

Currently developing TimberLog and the VirtualDBScroll Component Package

JZS

  • Full Member
  • ***
  • Posts: 184
Re: Simple SQLite3 Demo with Encryption and PRAGMA
« Reply #4 on: July 10, 2014, 01:13:57 pm »
Which one to download?
Can't find appropriate subject in that page referencing to what you mentioned.

The only thing I know is additional features have to be licensed. Does that come under "Proprietary SQLite Extensions"?
http://www.sqlite.org/support.html
« Last Edit: July 10, 2014, 01:16:44 pm by JZS »
I use recent stable release

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Simple SQLite3 Demo with Encryption and PRAGMA
« Reply #5 on: July 10, 2014, 01:17:26 pm »
Which one to download?
Can't find appropriate subject in that page referencing to what you mentioned.
See http://wiki.lazarus.freepascal.org/SQLite#Support_for_SQLite_encryption
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

jdlinke

  • Jr. Member
  • **
  • Posts: 62
  • Just old me
Re: Simple SQLite3 Demo with Encryption and PRAGMA
« Reply #6 on: July 10, 2014, 01:33:18 pm »
Which one to download?
Can't find appropriate subject in that page referencing to what you mentioned.

Please check my sourcecode for some comments on this topic (along with the link BigChimp posted):

// Using the following link, you can find a few options for versions
// of SQLite that provide support for encryption. Since I'm working
// mainly on Windows, I opted for the Open Source System.Data.SQLite
// http://wiki.freepascal.org/sqlite#Support_for_SQLite_encryption

// I used the "Precompiled Binaries for 32-bit Windows (.NET Framework 3.5 SP1)"
// and renamed SQLite.Interop.dll to sqlite3.dll, but you should be able to
// use any version you want as long as you have the required dependancies.
// http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

// Make sure the sqlite3.dll is in the same directory as your application, or you
// *will* have errors, and your application will not work.


For instance, if you have the latest version of the .NET framework installed on your computer (4.5.1) and the Visual Studio 2013 Redistributable Package, then you should be able to use this: http://system.data.sqlite.org/downloads/1.0.93.0/sqlite-netFx451-binary-x64-2013-1.0.93.0.zip. Just extract the SQLite.Interop.dll and rename it to sqlite3.dll and place it in the local directory. Again, you'll want to download a version that matches the version of .NET and Visual C++ Runtime on your computer (you may have many/all the versions of .NET and VC++ Redists installed).

You can download various versions of the .NET framework at http://msdn.microsoft.com/en-us/vstudio/aa496123.aspx
You can download various versions of the Visual C++ Redistributables at http://support.microsoft.com/kb/2019667
« Last Edit: July 10, 2014, 01:44:10 pm by jdlinke »
Lazarus 1.2.4 32-bit version on Windows 8.1 64-bit, Windows 7 64-bit, and Windows XP 32-bit

Currently developing TimberLog and the VirtualDBScroll Component Package

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Simple SQLite3 Demo with Encryption and PRAGMA
« Reply #7 on: July 10, 2014, 01:43:39 pm »
One suggestion: I'd suggest adding a readme.txt with a short description of what the demo does and installation instructions or a link to the relevant wiki page (which you could move out of the comments in the code).

(Especially nice when adding to the Lazarus examples as it makes browsing the examples much easier)
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

jdlinke

  • Jr. Member
  • **
  • Posts: 62
  • Just old me
Re: Simple SQLite3 Demo with Encryption and PRAGMA
« Reply #8 on: July 10, 2014, 01:45:34 pm »
Thanks BigChimp. That's probably a really good idea. I'll work on modifying this.
Lazarus 1.2.4 32-bit version on Windows 8.1 64-bit, Windows 7 64-bit, and Windows XP 32-bit

Currently developing TimberLog and the VirtualDBScroll Component Package

JZS

  • Full Member
  • ***
  • Posts: 184
Re: Simple SQLite3 Demo with Encryption and PRAGMA
« Reply #9 on: July 10, 2014, 02:01:59 pm »
Many thanks jdlinke. Should have read all the comments thoroughly first.
I use recent stable release

jdlinke

  • Jr. Member
  • **
  • Posts: 62
  • Just old me
Re: Simple SQLite3 Demo with Encryption and PRAGMA
« Reply #10 on: July 10, 2014, 02:36:31 pm »
I'm currently at work, but I'm going to extend this demo a bit this evening and then upload the update here for comments before submitting for inclusion in the examples.

I'm adding:
- Example of reading the application_id and user_version back out of the database pragma for use in the application
- Readme.txt at BigChimp's suggestion with plenty of additional info
- Adding an index to the database
- Adding a row of data to the table we created
- Performing a very basic query
- Make the UI a bit more clear

I want to keep it simple, and these few items along with the functionality already present should set anybody up for success in getting started with the basics of SQLite3.
Lazarus 1.2.4 32-bit version on Windows 8.1 64-bit, Windows 7 64-bit, and Windows XP 32-bit

Currently developing TimberLog and the VirtualDBScroll Component Package

jdlinke

  • Jr. Member
  • **
  • Posts: 62
  • Just old me
Re: Simple SQLite3 Demo with Encryption and PRAGMA
« Reply #11 on: July 11, 2014, 03:45:54 am »
I updated the demonstration. The attached file includes a Readme.txt with a good amount of information.

This application very simply demonstrates the following capabilities:
- Creation of a SQLite3 Database
- Encrypting the database using a key
- Changing (or setting if not initially set) the encryption key for the
  database after it has been created
- Creation of a database table
- Creating an Index
- Adding a row of data to the table
- Performing a very basic query to pull all rows into a DBGrid
- Performing a very basic query to count all rows
- Setting and reading various database metadata (Pragma)

Please let me know if you have any recommendations, questions, etc.


Code: [Select]
unit Unit1;

////////////////////////////////////////////////////////////////////////////////
//                                                                            //
//   This is free and unencumbered software released into the public domain.  //
//                                                                            //
//   Anyone is free to copy, modify, publish, use, compile, sell, or          //
//   distribute this software, either in source code form or as a compiled    //
//   binary, for any purpose, commercial or non-commercial, and by any        //
//   means.                                                                   //
//                                                                            //
//   In jurisdictions that recognize copyright laws, the author or authors    //
//   of this software dedicate any and all copyright interest in the          //
//   software to the public domain. We make this dedication for the benefit   //
//   of the public at large and to the detriment of our heirs and             //
//   successors. We intend this dedication to be an overt act of              //
//   relinquishment in perpetuity of all present and future rights to this    //
//   software under copyright law.                                            //
//                                                                            //
//   THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,          //
//   EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF       //
//   MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.   //
//   IN NO EVENT SHALL THE AUTHORS BE LIABLE FOR ANY CLAIM, DAMAGES OR        //
//   OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE,    //
//   ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR    //
//   OTHER DEALINGS IN THE SOFTWARE.                                          //
//                                                                            //
//   For more information, please refer to <http://unlicense.org/>            //
//                                                                            //
////////////////////////////////////////////////////////////////////////////////


// For this test application, I wanted to very simply try the following
// capabilities which I'll be using in a large application:
// - Creation of a SQLite3 Database
// - Creation of a database table
// - Setting various database metadata (PRAGMA)
// - Optionally encrypt the database using a key
// - Change (or set if not initially set) the encryption key for the database

// The application makes a new database file "new.db" within the local directory



// Using the following link, you can find a few options for versions
// of SQLite that provide support for encryption. Since I'm working
// mainly on Windows, I opted for the Open Source System.Data.SQLite
// http://wiki.freepascal.org/sqlite#Support_for_SQLite_encryption

// I used the "Precompiled Binaries for 32-bit Windows (.NET Framework 3.5 SP1)"
// and renamed SQLite.Interop.dll to sqlite3.dll, but you should be able to
// use any version you want as long as you have the required dependancies.
// http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

// Make sure the sqlite3.dll is in the same directory as your application, or you
// *will* have errors, and your application will not work.



// MORE HELPFUL SQLITE INFO

// To read more about the SQLite Encryption Extension (SEE),
// use the following URL (Section: How To Compile And Use SEE)
// http://www.sqlite.org/see/doc/trunk/www/index.wiki

// Details about the SQLite File Format (and particularly about
// the Database Header) can be found at:
// http://www.sqlite.org/fileformat2.html#database_header

// Information about the various database PRAGMA (metadata) statements
// can be found at:
// http://www.sqlite.org/pragma.html


{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, db, sqldb, sqlite3conn, FileUtil, Forms, Controls,
  Graphics, Dialogs, StdCtrls, ExtCtrls, DBGrids;

type

  { TForm1 }

  TForm1 = class(TForm)
    btnMakeNewDB: TButton;
    btnReKeyDB: TButton;
    btnViewAppID: TButton;
    btnSetAppID: TButton;
    btnViewUserVersion: TButton;
    btnSetUserVersion: TButton;
    btnAddToDB: TButton;
    btnUpdateGrid: TButton;
    btnCountRows: TButton;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    Label12: TLabel;
    Shape4: TShape;
    Shape5: TShape;
    txtUser_Name: TEdit;
    txtInfo: TEdit;
    Label10: TLabel;
    Label11: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    Label6: TLabel;
    Label7: TLabel;
    Label8: TLabel;
    Label9: TLabel;
    Shape1: TShape;
    Shape2: TShape;
    Shape3: TShape;
    txtNew: TEdit;
    txtApplication_ID: TEdit;
    Label2: TLabel;
    SQLite3Connection1: TSQLite3Connection;
    SQLQuery1: TSQLQuery;
    SQLTransaction1: TSQLTransaction;
    txtUser_Version: TEdit;
    txtPass: TEdit;
    procedure btnAddToDBClick(Sender: TObject);
    procedure btnMakeNewDBClick(Sender: TObject);
    procedure btnReKeyDBClick(Sender: TObject);
    procedure btnSetAppIDClick(Sender: TObject);
    procedure btnSetUserVersionClick(Sender: TObject);
    procedure btnViewAppIDClick(Sender: TObject);
    procedure btnViewUserVersionClick(Sender: TObject);
    procedure btnUpdateGridClick(Sender: TObject);
    procedure btnCountRowsClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
  private
    { private declarations }
  public
    { public declarations }
  const
    // More information on the use of these values is below.
    // They need not be set as constants in your application. They can be any valid value
    application_id = 1189021115; // must be a 32-bit Unsigned Integer (Longword 0 .. 4294967295)
    user_version = 23400001;  // must be a 32-bit Signed Integer (LongInt -2147483648 .. 2147483647)

  end;

var
  Form1: TForm1;

implementation

{$R *.lfm}

{ TForm1 }

procedure TForm1.btnMakeNewDBClick(Sender: TObject);
var
  newFile : Boolean;
begin

  SQLite3Connection1.Close; // Ensure the connection is closed when we start

  // Set the password initially.
  // Could probably be done with a PRAGMA statement, but this is so much simpler
  // and once set, doesn't need to be reset every time we open the database.
  // txtPass can be left blank if you want an unencrypted database.
  SQLite3Connection1.Password := txtPass.Text;

  try

    // Since we're making this database for the first time,
    // check whether the file already exists
    newFile := not FileExists(SQLite3Connection1.DatabaseName);

    if newFile then
    begin

      // Make the database and the tables
      try
        SQLite3Connection1.Open;
        SQLTransaction1.Active := True;


        // Per the SQLite Documentation (edited for clarity):
        // The pragma user_version is used to set or get the value of the user-version.
        // The user-version is a big-endian 32-bit signed integer stored in the database header at offset 60.
        // The user-version is not used internally by SQLite. It may be used by applications for any purpose.
        // http://www.sqlite.org/pragma.html#pragma_schema_version
        SQLite3Connection1.ExecuteDirect('PRAGMA user_version = ' + IntToStr(user_version) + ';');


        // Per the SQLite Documentation:
        // The application_id PRAGMA is used to query or set the 32-bit unsigned big-endian
        // "Application ID" integer located at offset 68 into the database header.
        // Applications that use SQLite as their application file-format should set the
        // Application ID integer to a unique integer so that utilities such as file(1) can
        // determine the specific file type rather than just reporting "SQLite3 Database".
        // A list of assigned application IDs can be seen by consulting the magic.txt file
        // in the SQLite source repository.
        // http://www.sqlite.org/pragma.html#pragma_application_id
        SQLite3Connection1.ExecuteDirect('PRAGMA application_id = ' + IntToStr(application_id) + ';');


        // Here we're setting up a table named "DATA" in the new database
        SQLite3Connection1.ExecuteDirect('CREATE TABLE "DATA"('+
                    ' "id" Integer NOT NULL PRIMARY KEY AUTOINCREMENT,'+
                    ' "Current_Time" DateTime NOT NULL,'+
                    ' "User_Name" Char(128) NOT NULL,'+
                    ' "Info" Char(128) NOT NULL);');


        // Creating an index based upon id in the DATA Table
        SQLite3Connection1.ExecuteDirect('CREATE UNIQUE INDEX "Data_id_idx" ON "DATA"( "id" );');


        SQLTransaction1.Commit;

      except
        ShowMessage('Unable to Create new Database');
      end;

    end;

  except
    ShowMessage('Unable to check if database file exists');
  end;

end;

procedure TForm1.btnAddToDBClick(Sender: TObject);
begin

  SQLite3Connection1.Close; // Ensure the connection is closed when we start

  SQLite3Connection1.Password := txtPass.Text; // The current password

  if (txtUser_Name.Text = '') OR (txtInfo.Text = '') then
  begin
    ShowMessage('Please enter both a Name and Info');
  end
  else
  begin

    // Attempt to add txtUser_Name and txtInfo to the database
    try
      SQLite3Connection1.Open;
      SQLTransaction1.Active := True;

      // Insert the values into the database
      // We're using ParamByName which prevents SQL Injection
      // http://wiki.freepascal.org/Working_With_TSQLQuery#Parameters_in_TSQLQuery.SQL
      SQLQuery1.SQL.Text := 'Insert into DATA (Current_Time,User_Name,Info) values (:Current_Time,:User_Name,:Info)';
      SQLQuery1.Params.ParamByName('Current_Time').AsDateTime := Now;
      SQLQuery1.Params.ParamByName('User_Name').AsString := txtUser_Name.Text;
      SQLQuery1.Params.ParamByName('Info').AsString := txtInfo.Text;
      SQLQuery1.ExecSQL;

      SQLTransaction1.Commit;
      SQLTransaction1.Active := False;
      SQLite3Connection1.Close;

      // Update the SQLite3Connection with the new password for future database transactions
      SQLite3Connection1.Password := txtNew.Text;

      // Clear Edit boxes
      txtUser_Name.Text := '';
      txtInfo.Text := '';

    except
      ShowMessage('Unable to add User_Name: ' + txtUser_Name.Text + ' and Info: ' + txtInfo.Text + ' to the database. Ensure database exists and password is correct.');
    end;

  end;

end;

procedure TForm1.btnReKeyDBClick(Sender: TObject);
begin

  SQLite3Connection1.Close; // Ensure the connection is closed when we start

  SQLite3Connection1.Password := txtPass.Text; // The current password

  // Update the database key
  try
    SQLite3Connection1.Open;
    SQLTransaction1.Active := True;


    // Here we change the key.
    // We use double-quotes here so that a blank key (IE: "") can be provided if
    // you want to remove encryption from the database.
    // This is a very simplistic demonstration. Ideally, we would take a stronger cryptographic approach
    // Some helpful info on this topic can be found at:
    // https://www.owasp.org/index.php/Cheat_Sheets
    // Per SQLite Documentation:
    // Note that the hexkey, rekey and hexrekey pragmas only work with SQLite version 3.6.8 and later.
    // http://www.sqlite.org/see/doc/trunk/www/readme.wiki
    // Section: Using the "key" PRAGMA
    SQLite3Connection1.ExecuteDirect('PRAGMA rekey = "' + txtNew.Text + '";');


    SQLTransaction1.Commit;
    SQLTransaction1.Active := False;
    SQLite3Connection1.Close;

    // Transfer the password to txtPass and erase txtNew
    txtPass.Text := txtNew.Text;
    txtNew.Text := '';

  except
    ShowMessage('Unable to set the new key using: PRAGMA rekey = ' + txtNew.Text + ';');
  end;

end;

procedure TForm1.btnSetAppIDClick(Sender: TObject);
begin

  SQLite3Connection1.Close; // Ensure the connection is closed when we start

  SQLite3Connection1.Password := txtPass.Text; // The current password

  // Try to set the application_id Pragma
  try
    SQLite3Connection1.Open;
    SQLTransaction1.Active := True;


    SQLQuery1.SQL.Text := 'PRAGMA application_id = ' + txtApplication_ID.Text + ';';
    SQLQuery1.ExecSQL;


    SQLTransaction1.Commit;
    SQLTransaction1.Active := False;
    SQLite3Connection1.Close;

  except
    ShowMessage('Unable to set new application_id: ' + txtApplication_ID.Text + ';');
  end;

end;

procedure TForm1.btnSetUserVersionClick(Sender: TObject);
begin

  SQLite3Connection1.Close; // Ensure the connection is closed when we start

  SQLite3Connection1.Password := txtPass.Text; // The current password

  // Try to set the user_version Pragma
  try
    SQLite3Connection1.Open;
    SQLTransaction1.Active := True;


    SQLQuery1.SQL.Text := 'PRAGMA user_version = ' + txtUser_Version.Text + ';';
    SQLQuery1.ExecSQL;


    SQLTransaction1.Commit;
    SQLTransaction1.Active := False;
    SQLite3Connection1.Close;

  except
    ShowMessage('Unable to set user_version: ' + txtUser_Version.Text + ';');
  end;

end;

procedure TForm1.btnViewAppIDClick(Sender: TObject);
begin

  SQLite3Connection1.Close; // Ensure the connection is closed when we start

  SQLite3Connection1.Password := txtPass.Text; // The current password

  // Try to query database for application_id Pragma
  try
    SQLite3Connection1.Open;

    SQLQuery1.SQL.Text := 'PRAGMA application_id;';
    SQLQuery1.Open;

    // Display the resulting value
    ShowMessage(SQLQuery1.fields[0].asString);

  except
    ShowMessage('Unable to display application_id');
  end;

end;

procedure TForm1.btnViewUserVersionClick(Sender: TObject);
begin

  SQLite3Connection1.Close; // Ensure the connection is closed when we start

  SQLite3Connection1.Password := txtPass.Text; // The current password

  // Try to query database for user_version Pragma
  try
    SQLite3Connection1.Open;

    SQLQuery1.SQL.Text := 'PRAGMA user_version;';
    SQLQuery1.Open;

    // Display the resulting value
    ShowMessage(SQLQuery1.fields[0].asString);

  except
    ShowMessage('Unable to display user_version');
  end;

end;

procedure TForm1.btnUpdateGridClick(Sender: TObject);
begin

  SQLite3Connection1.Close; // Ensure the connection is closed when we start

  SQLite3Connection1.Password := txtPass.Text; // The current password

  // Try to perform query
  try
    SQLite3Connection1.Connected := True;

    // Set SQL text to select everything from the DATA table
    SQLQuery1.SQL.Clear;
    SQLQuery1.SQL.Text := 'Select * from DATA';
    SQLQuery1.Open;

    // Allow the DBGrid to view the results of our query
    DataSource1.DataSet := SQLQuery1;
    DBGrid1.DataSource := DataSource1;
    DBGrid1.AutoFillColumns := true;

  except
    ShowMessage('Unable to query the database');
  end;

end;

procedure TForm1.btnCountRowsClick(Sender: TObject);
begin

  SQLite3Connection1.Close; // Ensure the connection is closed when we start

  SQLite3Connection1.Password := txtPass.Text; // The current password

  // Try to perform query
  try
    SQLite3Connection1.Connected := True;

    // Set SQL text to count all rows from the DATA table
    SQLQuery1.SQL.Clear;
    SQLQuery1.SQL.Text := 'Select Count(*) from DATA';
    SQLQuery1.Open;

    // Allow the DBGrid to view the results of our query
    DataSource1.DataSet := SQLQuery1;
    DBGrid1.DataSource := DataSource1;
    DBGrid1.AutoFillColumns := true;

  except
    ShowMessage('Unable to query the database');
  end;

end;

procedure TForm1.FormCreate(Sender: TObject);
begin

  // Ensure we're using the local sqlite3.dll
  SQLiteLibraryName := 'sqlite3.dll';

  // Set the path to the database
  SQLite3Connection1.DatabaseName := 'new.db';

end;

end.

« Last Edit: July 12, 2014, 08:37:28 am by jdlinke »
Lazarus 1.2.4 32-bit version on Windows 8.1 64-bit, Windows 7 64-bit, and Windows XP 32-bit

Currently developing TimberLog and the VirtualDBScroll Component Package

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Simple SQLite3 Demo with Encryption and PRAGMA - Updated
« Reply #12 on: July 12, 2014, 10:48:39 am »
Thanks a lot for that!
I'm not much of a GUI guy, but I've made some changes which I hope you'll like...
Comments/remarks below.

Readme:
1. I wonder whether you need the .Net framework if you use System.Data.SQLite? Obviously yes if you use .Net to program but I would hope that the MSVC++ runtimes would be enough to use the dll?? Haven't tested this of course.

2. Hmmm, I would expect that user_version would be used to track metadata changes, not application_id. I do understand both would work so more of a comment really...

3. Shouldn't
SQLite3Connection1.ExecuteDirect('PRAGMA rekey = "' + txtNew.Text + '";');
be something like (untested)
SQLite3Connection1.ExecuteDirect('PRAGMA rekey = "' + QuotedStr(txtNew.Text) + '";');

Program:
1. Moved non-visual controls out of the way
2. btnAddToDBClick: Why are you closing the sqlite connection? To free the default transaction and allow 3rd party programs to access the db? I've removed it...
3. btnAddToDBClick: Removed left over line  SQLite3Connection1.Password := txtNew.Text;
4. SQLTransaction1.Commit; makes the transaction.active property false so specifying that is redundant; removed it.
5. Add to db button a call to update grid automatically so user sees what happened
6. Cleaned up sql3connection.password handling a bit
7. Added some showmessages to show succesful completion

Exact changes can be seen in
https://bitbucket.org/reiniero/fpc_laz_patch_playground/commits/all
in commits
77331cd
and
344cd31

Thanks
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

Mike.Cornflake

  • Hero Member
  • *****
  • Posts: 1251
Re: Simple SQLite3 Demo with Encryption and PRAGMA - Updated
« Reply #13 on: July 12, 2014, 12:44:56 pm »
Code: [Select]
SQLite3Connection1.ExecuteDirect('PRAGMA rekey = "' + txtNew.Text + '";');
be something like (untested)
SQLite3Connection1.ExecuteDirect('PRAGMA rekey = "' + QuotedStr(txtNew.Text) + '";');
You're double double-quoting txtNew.Text if you do that. 

Suspect you mean
Code: [Select]
SQLite3Connection1.ExecuteDirect('PRAGMA rekey = ' + QuotedStr(txtNew.Text) + ';');
But if so, this is only a cosmetic change...
Lazarus Trunk/FPC Trunk on Windows [7, 10]
  Have you tried searching this forum or the wiki?:   http://wiki.lazarus.freepascal.org/Alternative_Main_Page
  BOOKS! (Free and otherwise): http://wiki.lazarus.freepascal.org/Pascal_and_Lazarus_Books_and_Magazines

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Simple SQLite3 Demo with Encryption and PRAGMA - Updated
« Reply #14 on: July 12, 2014, 12:57:39 pm »
Suspect you mean
Code: [Select]
SQLite3Connection1.ExecuteDirect('PRAGMA rekey = ' + QuotedStr(txtNew.Text) + ';');
But if so, this is only a cosmetic change...
Yes, you're right about the double-double quoting ;)
It shouldn't be cosmetic only though, IIRC, quotedstr does deal with doubling up any quotes found inside it... but I may be misremembering again...
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

 

TinyPortal © 2005-2018