Hi,
I encrypted SQLite database but when I querying to tables not able to fetch data from database getting error
sqlite> PRAGMA key = ‘********’;
ok
sqlite> select * from student;
2024-09-04 11:22:23.198: ERROR CORE sqlcipher_license_authorizer: license code invalid or not supplied
Parse error: not authorized (23)
sqlite> PRAGMA cipher_license = ‘OmNpZDowMDFWSjAwMDAwSG’;
0
sqlite> select * from student;
2024-09-04 11:25:20.616: ERROR CORE sqlcipher_page_cipher: hmac check failed for pgno=1
2024-09-04 11:25:20.621: ERROR CORE sqlite3Codec: error decrypting page 1 data: 1
2024-09-04 11:25:20.623: ERROR CORE sqlcipher_codec_ctx_set_error 1
Parse error: file is not a database (26)
When I created database I set below configurations.
sqlite> PRAGMA cipher_page_size = 4096;
sqlite> PRAGMA kdf_iter = 256000;
sqlite> PRAGMA cipher_hmac_algorithm = HMAC_SHA1;
sqlite> PRAGMA cipher_kdf_algorithm = PBKDF2_HMAC_SHA1;
Thanks in advance
Hi @pkumbhar,
Since you are trialing the Commercial edition of SQLCipher, please consider reaching out privately for support at support@zetetic.net, we would be happy to help. In your communication, can you confirm whether the database you are attempting to access is already encrypted with SQLCipher, or is it currently a plaintext SQLite database which needs to be converted?
Hi Zetetic Team,
I am using already encrypted database. I am using “Data.Ado” class library for reference. I am using below technologies and libraries (nuget packages) to communicate with encrypted SQLite database.
-
Microsoft Visual Studio
-
.Net Framework 4.6.1
-
SQLitePCLRaw.bundle_sqlcipher
When I use below code It is throwing an Error
//Batteries.Init();
//string liscense = “Trial Version Liscnece Key”;
//AdoDataService objAdo = new AdoDataService(“FIGMD.db”, liscense, “My Password”);
//string data=objAdo.Query();
It is throwing error for below code snippet
Batteries.Init();
I also tried
Batteries_V2.Init();
I wanted to know nuget packages and Its versions to Integrate the same.
Thanks,
Hi Zatetic Team,
Also using “Microsoft.Data.Sqlite” nuget package.
Hi Zetetic Team,
I tried another option in which, I am using MS Visual Studio 2019 with below nuget packages.
-
System.Data.SQLite
-
SQLitePCLRaw.bundle_sqlcipher
-
.Net Framework 4.7.2
In this I am creating database using .Net and also encrypting the same, but It is not encrypted Can be opened any studio such DBBrowser SQLCipher, DbEvear. Without password.
Below is my code snippet. Please find attached Program.cs File.
I know It is not creating SQLite database version 3 and above but. Can you please share the way which I can use to implement secure and protected communication between .Net & SQLite database
Thanks,
Pandurang
(Attachment Program.cs is missing)
Hello @pkumbhar,
You are trialing SQLCipher for Windows .NET. This library supports several specific .NET client libraries which include:
- A sqlite-net compatible package based on the official praeclarum/sqlite-net library
- Microsoft Entity Framework via Microsoft.EntityFrameworkCore.Sqlite.Core
- Microsoft’s ADO.NET implementation via Microsoft.Data.Sqlite.Core
We have instructions for integrating these libraries within your application here . Additionally, we include a demo application that showcases the usage of these libraries. Can you try running the demo application to verify it works properly on your machine?
If you have a plaintext SQLite database, SQLCipher can convert that to an encrypted database using the sqlcipher_export(...) convenience function. We provide documentation on that function here . For this case, you will want to review example #1 from the documentation.
1 Like
@pkumbhar - one specific thing to add, if you are using a SQLCipher Commercial Edition Trial, you should not be including SQLitePCLRaw.bundle_sqlcipher. SQLCipherPCLRaw.bundle_zetetic will be brought in automatically when you follow the instructions that @developernotes posted.
1 Like
Working on your suggestions. Will update you ASAP.
Hi Team,
Now I can generate SQLite Database using SQLCipher
sqlite> .open Test.db
sqlite> PRAGMA cipher_license = ‘Trial Version Liscnece’;
0
sqlite> PRAGMA key=‘Ganesh05’;
ok
sqlite> PRAGMA cipher_page_size = 4096;
sqlite> PRAGMA kdf_iter = 256000;
sqlite> PRAGMA cipher_hmac_algorithm = HMAC_SHA512;
sqlite> PRAGMA cipher_kdf_algorithm = PBKDF2_HMAC_SHA512;
sqlite> ATTACH DATABASE ‘FIGMD.db’ AS FIGMD KEY ‘Welcome@2024’;
sqlite> SELECT sqlcipher_export(‘FIGMD’);
sqlite> DETACH DATABASE FIGMD ;
sqlite> ATTACH DATABASE ‘FIGMD.db’ AS FIGMD KEY ‘Welcome@2024’;
sqlite> Create table Student(prn integer primary key asc, name text,age text);
sqlite> insert into Student(1,‘testname’,‘32’);
sqlite> insert into Student values(1,‘testname’,‘32’);
sqlite> select * from Student;
1|testname|32
After that I have created .Net Application with .Net Framework 4.6.1
Added below nuget libraries
- NuGet\Install-Package sqlite-net-sqlcipher -Version 1.9.172
- NuGet\Install-Package Microsoft.Data.Sqlite.Core -Version 8.0.8
Added above 2 packages. Now It is throwing exception
Library e_sqlcipher not found
plat: win
suffix: DLL
possibilities (2):
1) C:\Pandurang\SQLCipher\SQLCipherExample\SQLiteDemo\SQLiteDemo\SQLiteDemo\bin\Debug\runtimes\win-x64\native\e_sqlcipher.dll
2) C:\Pandurang\SQLCipher\SQLCipherExample\SQLiteDemo\SQLiteDemo\SQLiteDemo\bin\Debug\e_sqlcipher.dll
win TryLoad: C:\Pandurang\SQLCipher\SQLCipherExample\SQLiteDemo\SQLiteDemo\SQLiteDemo\bin\Debug\runtimes\win-x64\native\e_sqlcipher.dll
thrown: System.ComponentModel.Win32Exception (0x80004005): The specified module could not be found
at SQLitePCL.NativeLibrary.TryLoad(String name, Loader plat, Action1 log, IntPtr& h) win TryLoad: C:\Pandurang\SQLCipher\SQLCipherExample\SQLiteDemo\SQLiteDemo\SQLiteDemo\bin\Debug\e_sqlcipher.dll thrown: System.ComponentModel.Win32Exception (0x80004005): The specified module could not be found at SQLitePCL.NativeLibrary.TryLoad(String name, Loader plat, Action1 log, IntPtr& h)
NOT FOUND
Can you please suggest me I have to add e_sqlcipher nuget also or not?
Hi Team,
It is working now I have to add e_sqlcipher.dll in bin debug folder perform the same commands to encrypt SQLite Database and also use It in .Net Application
Thanks for your help. Now I have to integrate the same with NHibernate for further help I will interact with you.
@pkumbhar you should NOT be using the sqlite-net-sqlcipher or e_SQLCipher packages. Those are unsupported 3rd party builds. Instead, follow the instructions here and use the zetetic-sqlite-net-base-X.X.X.nupkg which is present in the trial package.
https://www.zetetic.net/sqlcipher/sqlcipher-for-dotnet/#sqlite-net
Hi @sjlombardo ,
Thanks for your assistance. Now I am using downloaded “zetetic-sqlcipher-windows.4.6.1.nupkg” with nuget.config and also able to create Encrypted SQLite Database with .Net Framework 4.6.1.
Can you please provide some information to integrate the same with NHibernate
Now Just using
below Nuget Packages.
- zetetic-sqlcipher-windows.4.6.1.nupkg from local
- Microsoft.Data.Sqlite
for Creating POC.
Hi @pkumbhar,
Since you are using NHibernate with SQLCipher for Windows .NET using Microsoft.Data.Sqlite.Core you might consider this NuGet package to assist with the integration. The project has a some documentation here . Would you give that a try and let us know your results?
Hi @developernotes ,
Can you please help me to configure the NHibernate with SQLite using SQLCipher.
I am facing below error.
SQLite Error 23: ‘not authorized’.
I am using below code
cfg.DataBaseIntegration(x =>
{
x.ConnectionString = “Data Source=mydatabase.db;Password=‘testpassword’;”;
x.Driver<NHibernate.Extensions.Sqlite.SqliteDriver>();
x.Dialect<NHibernate.Dialect.SQLiteDialect>();
x.ConnectionProvider<NHibernate.Connection.DriverConnectionProvider>();
});
cfg.AddAssembly(typeof(Student).Assembly);
var factory=cfg.BuildSessionFactory();
Hi @pkumbhar,
Before you configure/initialize NHibernate, can you try to open a temporary in-memory database to apply your trial license code to see if that allows you to proceed with NHibernate. Something like this:
using System.Data;
using Microsoft.Data.Sqlite;
var licenseCode = "YourLicenseCodeHere";
var tempConnection = new SqliteConnection(":memory:");
tempConnection.Open();
using (var command = tempConnection.CreateCommand())
{
command.CommandText = "PRAGMA cipher_license = '{licenseCode}';";
command.ExecuteNonQuery();
}
tempConnection.Close();
HI @developernotes ,
Thanks for information.
Now I am facing below exception
The requested collection ‘DataTypes’ is not defined.Microsoft.Data.Sqlite
Can you please assist me on this issue.
Hi @pkumbhar,
We are taking a look into this.
Hi @pkumbhar,
I was able to recreate the issue you were experiencing with NHibernate and have a fix that will allow it to work. The issue was due to the NHibernate AbstractDataBaseSchema attempting to request data types when querying for reserved words from the Microsoft ADO.NET client which would throw an exception. Let’s start with creating the ISessionFactory using Fluent NHibernate:
private static ISessionFactory CreateSessionFactory(string filename, string key)
{
return Fluently
.Configure()
.Database(SqliteConfiguration.Standard.UsingFileWithPassword(filename, key))
.Mappings(m =>
{
m.AutoMappings.Add(AutoMap.AssemblyOf<User>()
.Where(t => string.CompareOrdinal(t.Namespace, "Data.ADO.Entities") == 0));
})
.ExposeConfiguration(c =>
{
new SchemaExport(c).Create(false, true);
})
.BuildConfiguration()
.BuildSessionFactory();
}
Next we must define a SqliteConfiguration which allows us to specify the driver and dialect for NHibernate to use. Note the dialect references a subclass of the SqliteDialect (from here).
public class SqliteConfiguration : PersistenceConfiguration<SqliteConfiguration>
{
public static SqliteConfiguration Standard => new();
private SqliteConfiguration()
{
Driver<SqliteDriver>();
Dialect<SqliteDialectFix>();
}
public SqliteConfiguration UsingFileWithPassword(string fileName, string password)
{
return ConnectionString(c =>
c.Is($"Data Source={fileName};Password={password};"));
}
}
Defining the SqliteDialectFix allows us to override the metadata behavior:
public class SqliteDialectFix : SqliteDialect
{
public override IDataBaseSchema GetDataBaseSchema(DbConnection connection)
{
return new SqliteMetadataFix(connection, this);
}
}
With our SqliteMetadataFix implementation we can set IncludeDataTypesInReservedWords to false to prevent NHibernate’s AbstractDataBaseSchema from requesting DataTypes when building the reverse word list.
public class SqliteMetadataFix : SQLiteDataBaseMetaData
{
public SqliteMetadataFix(DbConnection connection, Dialect dialect) : base(connection, dialect){}
public override bool IncludeDataTypesInReservedWords => false;
}
Would you give that a try when you get a chance and let us know your results?
@developernotes ,
Yes Thanks,
I will update you ASAP.
Hi @developernotes ,
I have tried this as below
public static ISessionFactory CreateSessionFactory(string filename, string key)
{
Microsoft.Data.Sqlite.SqliteConnection.ClearAllPools();
//var cfg = new Configuration();
//var licenseCode = “YourLicenseCodeHere”;
string lisc = “My Liscense Code”;
var _connection = (IDbConnection)new SqliteConnection($“Data Source=Test.db;”);
_connection.Open();
using (var command = _connection.CreateCommand())
{
command.CommandText = $“PRAGMA cipher_license = ‘{lisc}’;”;
command.ExecuteNonQuery();
}
_connection.Close();
return Fluently
.Configure()
.Database(SqliteConfiguration.Standard().UsingFileWithPassword(filename, key))
.Mappings(m =>
{
m.AutoMappings.Add(AutoMap.AssemblyOf()
.Where(t => string.CompareOrdinal(t.Namespace, “SQLiteNHibernateDemo”) == 0)) ;
})
.ExposeConfiguration(c =>
{
new SchemaExport(c).Create(false, true);
})
.BuildConfiguration()
.BuildSessionFactory();
}
public class SqliteConfiguration : PersistenceConfiguration
{
public static SqliteConfiguration Standard() {
return new SqliteConfiguration();
}
private SqliteConfiguration()
{
Driver<SqliteDriver>();
Dialect<SqliteDialectFix>();
}
public SqliteConfiguration UsingFileWithPassword(string fileName, string password)
{
return ConnectionString(c =>
c.Is($"Data Source={fileName};Password={password};"));
}
}
public class SqliteDialectFix : NHibernate.Dialect.SQLiteDialect
{
public override IDataBaseSchema GetDataBaseSchema(DbConnection connection)
{
return new SqliteMetadataFix(connection, this);
}
}
public class SqliteMetadataFix : SQLiteDataBaseMetaData
{
public SqliteMetadataFix(DbConnection connection, Dialect dialect) : base(connection, dialect) { }
public override bool IncludeDataTypesInReservedWords => false;
}
public class Student
{
public virtual int PRN { get; set; }
public virtual string Name { get; set; }
public virtual string Age { get; set; }
}
Now I am getting below error. while insert
“An invalid or incomplete configuration was used while creating a SessionFactory. Check PotentialReasons collection, and InnerException for more detail.”
Tried to add property ‘DropConstraints’ when already added.
Main Method
ISessionFactory facory=SessionFactory.CreateSessionFactory(“FIGMD.db”, “Welcome@2024”);
using(ISession session=facory.OpenSession())
{
using(ITransaction trans = session.BeginTransaction())
{
Student stud = new Student() { PRN = 1, Name = “Test”, Age = “32” };
session.Save(stud);
trans.Commit();
}
session.Close();
}