6

Compiling sqlcipher (sqlite encrypted) for Windows using Visual Studio 2022

 2 years ago
source link: https://www.domstamand.com/compiling-sqlcipher-sqlite-encrypted-for-windows-using-visual-studio-2022/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

Compiling sqlcipher (sqlite encrypted) for Windows using Visual Studio 2022

January 4, 2022 | by Dominique St-Amand | 1 Comment
4 min read

I was searching for what was available in terms of encryption for SQLite and stumbled upon the Microsoft SQLite documentation for encryption. It states

SQLite doesn’t support encrypting database files by default. Instead, you need to use a modified version of SQLite like SEE, SQLCipher, SQLiteCrypt, or wxSQLite3.

I looked at all the proposed products and saw that SQLCipher was giving a community (open source) version of their product. However, they do not give the compiled binaries that comes with that. So I said to myself, let me try to compile it. This one was a tough one! I could not get it to work as easily as I thought I would. After a few hours playing, I got it to work and wanted to document the step by step for all that would be in a similar scenario.

Shout out to the following 2 resources that I found that helped me in the right direction.

  • YouTube video on how to compile
  • GitHub repository of a plugin for Gradle that builds sqlcipher for various platforms.

I was able to compile using OpenSSL 3.0.1 and version 4.5.0 of sqlcipher.
Note that I am not a C++ pro here, but used my instincts to try something out and it worked!

Requirements

Visual Studio 2022 C++ tools

You will need to install the compilers for C++. SQLCipher uses nmake (MSVC) and a Makefile.msc. To do that, add the Desktop Development with C++ into your Visual Studio 2022 through the installer.

OpenSSL

Download yourself a copy of OpenSSL. You can find all the 3rd binaries distribution here. I downloaded mine from https://slproweb.com/products/Win32OpenSSL.html.

Make sure to download the none light version (full for developers).

Tcl is required to build SQLite. I downloaded mine from IronTCL. Once you have downloaded it, extract it to a folder and navigate to the bin directory. Copy the file tclsh86t.exe to tclsh.exe. This is because the build looks for tclsh.exe.

Copy the full path of the bin directory. In my case, it was E:\Temp\irontcl-amd64-8.6.7\IronTcl\bin.

Building

We’re now ready to build SQLCipher. Open the developer command prompt for Visual Studio 2022. You can find it in your start menu or just fire up a cmd prompt and type

cmd /k "C:\Program Files\Microsoft Visual Studio\2022\Enterprise\Common7\Tools\VsDevCmd.bat"

We’re going to compile for 64bit. For this, we need to have our command prompt with the 64bit variables. You can do this by executing the following command:

"C:\Program Files\Microsoft Visual Studio\2022\Enterprise\VC\Auxiliary\Build\vcvarsall.bat" x64

If successful, you should see something like

**********************************************************************
** Visual Studio 2022 Developer Command Prompt v17.0.4
** Copyright (c) 2021 Microsoft Corporation
**********************************************************************
[vcvarsall.bat] Environment initialized for: 'x64'

Now we need to put Tcl on our path. Run the following:

SET PATH=%PATH%;E:\Temp\irontcl-amd64-8.6.7\IronTcl\bin

Once that is done, we will set the platform for which we want to build sqlite for, that is 64bit or x64. Run the following:

SET PLATFORM=x64

Download sqlcipher

Clone the repository of sqlcipher into a folder. In my case I cloned it into E:\Temp\GitHub\sqlcipher.

git clone [email protected]:sqlcipher/sqlcipher.git E:\Temp\GitHub\sqlcipher 

Fixing the Makefile

Since we’re compiling for Windows, we need to fix the Makefile.msc located in the root of the repository with the following:

1. Find the string -DSQLITE_TEMP_STORE=1 and change 1 to 2. You should change it in 2 places the TCC and RCC variables.

Once you’ve changed those, add the following right below

# Flags to include OpenSSL
TCC = $(TCC) -DSQLITE_HAS_CODEC -I"C:\Program Files\OpenSSL-Win64\include"

where C:\Program Files\OpenSSL-Win64 is the folder where you installed OpenSSL.

2. Locate the string LTLIBPATHS = $(LTLIBPATHS) /LIBPATH:$(ICULIBDIR)

right after the !ENDIF, add the following:

LTLIBPATHS = $(LTLIBPATHS) /LIBPATH:$(ICULIBDIR) /LIBPATH:"C:\Program Files\OpenSSL-Win64\lib\VC\static"
LTLIBS = $(LTLIBS) libcrypto64MT.lib libssl64MT.lib ws2_32.lib shell32.lib advapi32.lib gdi32.lib user32.lib crypt32.lib kernel32.lib

Create binary folder

Create a folder where you want the binaries to be built. I chose to create a folder at the same level as the cloned repository, located at E:\Temp\GitHub\sqlcipher-build

Compiling

In your binary folder ( cd E:\Temp\GitHub\sqlcipher-build), run the following:

nmake /f E:\Temp\GitHub\sqlcipher-build\sqlcipher\Makefile.msc TOP=E:\Temp\GitHub\sqlcipher

Replace the folders with your appropriate folders. You should now find sqlite3.exe and sqlite3.dll in the directory.

Testing

To test, download yourself a copy of sqlite for Windows. Make sure to get the tools package, that include sqlite.exe etc. Once you downloaded it, extract it to a folder and execute the following commands in a PowerShell prompt to create a database and add data.

.\sqlite3.exe testnoencryption.db
SQLite version 3.37.1 2021-12-30 15:30:28
Enter ".help" for usage hints.
sqlite> CREATE TABLE test(COL1 int);
sqlite> .tables
sqlite> .schema test
CREATE TABLE test(COL1 int);
sqlite> .quit

In the same prompt, let’s look at the hexdump of the database file using the command

Format-Hex testnoencryption.db

You should see something alone the lines of

0000000000000FA0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
0000000000000FB0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
0000000000000FC0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 2F                /
0000000000000FD0 01 06 17 15 15 01 43 74 61 62 6C 65 74 65 73 74 ������Ctabletest
0000000000000FE0 74 65 73 74 02 43 52 45 41 54 45 20 54 41 42 4C test�CREATE TABL
0000000000000FF0 45 20 74 65 73 74 28 43 4F 4C 31 20 69 6E 74 29 E test(COL1 int)
0000000000001000 0D 00 00 00 00 10 00 00 00 00 00 00 00 00 00 00 �    �

Now repeating the same exercise with the sqlcipher binary

.\sqlite3.exe testencryption.db
SQLite version 3.36.0 2021-06-18 18:36:39 (SQLCipher 4.5.0 community)
Enter ".help" for usage hints.
sqlite> PRAGMA key = 'passphrase';
sqlite> CREATE TABLE test(COL1 int);
sqlite> .tables
sqlite> .schema test
CREATE TABLE test(COL1 int);
sqlite> .quit

The command PRAGMA key is to set the password for encryption.

and the hexdump

0000000000000000 E9 57 B4 75 50 6E AB 22 9D 75 19 DD 8C 41 78 D6 éW´uPn«"�u�Ý�AxÖ
0000000000000010 E3 6B E5 D3 F7 04 54 C6 D9 CF 54 A8 CB EC 24 FC ãkåÓ÷�TÆÙÏT¨Ëì$ü
0000000000000020 2D 25 6F B5 62 14 47 36 9B A7 C3 58 B8 5D D7 54 -%oµb�G6�§ÃX¸]×T
0000000000000030 E2 90 13 37 C3 62 B1 1A 0E F1 DA 8C FB 81 0D 8F â��7Ãb±��ñÚ�û���
0000000000000040 62 F4 3C C5 92 66 83 C9 20 30 05 2E B5 1A 82 43 bô<Å�f�É 0�.µ��C
0000000000000050 21 B2 B7 97 AA B2 CF 1D 7A A2 5A E7 27 CF A0 89 !²·�ª²Ï�z¢Zç'Ï �
0000000000000060 25 E4 53 17 EE 24 B2 1A AA 75 BD BC B8 60 E6 36 %äS�î$²�ªu½¼¸`æ6
0000000000000070 46 2D 02 A0 94 81 A1 3E 54 3F 30 4A 4E 98 79 74 F-� ��¡>T?0JN�yt
0000000000000080 CA D9 FE 5E 67 76 B0 A3 34 1F 34 18 5F 33 9D 74 ÊÙþ^gv°£4�4�_3�t
0000000000000090 ED 69 B8 A1 BF 15 7F 9B 18 1B E9 94 57 75 FB 7F íi¸¡¿�����é�Wuû�
00000000000000A0 13 B5 EA 8F 2E 03 EC 8D BA 43 06 C7 39 7D 46 9E �µê�.�ì�ºC�Ç9}F�
00000000000000B0 EA 1A DD 28 E9 95 10 96 D1 C0 30 1A 5E A8 67 25 ê�Ý(é���ÑÀ0�^¨g%
00000000000000C0 32 22 1A E8 F8 49 05 E1 E0 7C 3A 64 80 E4 C1 7A 2"�èøI�áà|:d�äÁz
00000000000000D0 F1 60 23 92 55 31 D0 DE AB BA 70 FD 02 7E B7 42 ñ`#�U1ÐÞ«ºpý�~·B
00000000000000E0 09 71 6C 7C 22 93 39 AA 5B 21 06 2F 66 AC A2 BF �ql|"�9ª[!�/f¬¢¿
00000000000000F0 69 97 E8 2F EE 17 15 99 86 95 D1 B5 81 DB 2D 2D i�è/î�����ѵ�Û--
0000000000000100 29 24 CE 1C 0B F0 B0 42 CE 56 89 AB 59 63 31 63 )$Î��ð°BÎV�«Yc1c
0000000000000110 79 53 47 1C 80 84 83 71 4F 67 C5 43 FB 6C 0B DB ySG����qOgÅCûl�Û

You can see the difference between the encrypted and unencrypted database files.

You can also do the same with the sqlcipher version and not use the PRAGMA Key command, but here it was for demo purposes.

Conclusion

We have now compiled the binary for sqlcipher. If you want to keep only what is necessary, copy the following files wherever you see fit:

sqlite3.dll
sqlite3.exe

Hope this can help some.

Shout out to SQLCipher for the community version!

Views: 204

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK