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