Implementing SQL AES ENCRYPTION into SQLite using PHP

Question

is it possible to implement SQL AES_ENCRYPT/AES_DECRYPT into SQLite using PHP ? For example I have a PHP Code:

$SQL = "INSERT INTO parent (Request, Column1, Column2) VALUES ('$Request',AES_ENCRYPT('$Col1','$key'),AES_ENCRYPT('$Col2','$key'))";

and this query works in SQL, but is it possible to use this same query in SQLite?

You meant "This query works in MySQL". SQLite doesn't have built-in AES encryption functions, so you'll have to send in encrypted data. — NullUserException, Oct 03 '12 at 16:23
Yes this query works in MySQL. So I cant use this AES_ENCRYPT in SQLite ? — Acnologia, Oct 03 '12 at 16:25
Encrypt and decrypt data on the PHP end. Store the encrypted data in an SQLite BLOB. — NullUserException, Oct 03 '12 at 16:31

score 2 · Answer 1 · edited Mar 07 '18 at 22:14

Well, about 10 minutes ago I finished installing PHP 7.2.2 on my HP laptop with Ubuntu 16.04 LTS 64bit. You do not need to acquire a license for SQLite Encryption Extension (SEE). On top of existing PHP extension I have added a few C files with AES and SQLite functions for en/decrypting Pager. It works well and now I will try to make it work using Intel i5 built-in AES functionality - to get the best out of hardware itself. Now, to open a sqlite db I can use the following:

class MyDB extends SQLite3 {
      function __construct(){
        $this->open('sqlite3.db',SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE, 'your_password_here');
      }
}

Encrypting the whole database is definitely the best solution. Write me a message for details. I will probably publish this solution soon.

hi. When I use it it says file is not a database. any idea ? — MindRoasterMir, Mar 30 '21 at 13:53

score 1 · Accepted Answer · answered Oct 05 '12 at 12:50

1

I'd say you have 2 options :

encrypt your values at the PHP level and store them as BLOBs or base64 strings
encrypt the whole database executing the following command (just like any other regular SQL command) : PRAGMA hexkey='0x_your_key_in_hex_format' . Don't forget do do the same when you open your database for running SELECT queries. Here is the official documentation.

answered Oct 05 '12 at 12:50

mbarthelemy

12,465
4
41
43

I have made a PHP AEE encryption amd it works but I need to use AES as SQL query so thanks for the reply I will try SQLite Encryption Extension and I will come back if I have any problems. – Acnologia Oct 08 '12 at 16:08
Hi mbarthelemy, do you have any example on how to use this PRAGMA in PHP for sqlite? – Acnologia Jun 01 '13 at 20:28

Implementing SQL AES ENCRYPTION into SQLite using PHP

2 Answers2

Linked