SQLite Encryption Extension For Use with With PHP 7.x Code

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

SQLite Encryption Extension For Use with With PHP 7.x Code

Mark Tomlin
I would like to use SQLite's Encryption Extension with my PHP 7.2 code
base, as well as 7.3 and 7.4 code bases moving forward. My hope is is that
the see-sqlite.c code file can be simply renaming it to sqlite.c and
dropping it into PHP's build directory.Then compiling it as I normally
would. I'm also guessing I would have to modify the (see-)sqlite.c code to
make a call to sqlite3_key from within the sqlite3 function call so
that the encryption is always on and would not require any modifications to
the PHP source code.

Has anyone done this before?

--
Mark Tomlin, CEO.
MimoCAD, Inc.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite Encryption Extension For Use with With PHP 7.x Code

Richard Hipp-3
On 8/20/19, Mark Tomlin <[hidden email]> wrote:
> I would like to use SQLite's Encryption Extension with my PHP 7.2 code
> base, as well as 7.3 and 7.4 code bases moving forward. My hope is is that
> the see-sqlite.c code file can be simply renaming it to sqlite.c and
> dropping it into PHP's build directory.Then compiling it as I normally
> would. I'm also guessing I would have to modify the (see-)sqlite.c code to
> make a call to sqlite3_key from within the sqlite3 function call so
> that the encryption is always on and would not require any modifications to
> the PHP source code.

You do not need to modify any C code, either in SQLite/SEE or in PHP.
You can activate encryption and set the encryption key using a PRAGMA
statement.

>
> Has anyone done this before?

Yes.


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite Encryption Extension For Use with With PHP 7.x Code

Mark Tomlin
Thank you Richard for the extremely quick response.

I found the regular sqlite3::open method has an $encryption_key parameter.

https://www.php.net/manual/en/sqlite3.open.php

As I am using PDO for the interface into SQLite, I wonder if there is a
similar parameter for that interface. Perhaps using the $password
parameter? There is very little documentation on this.

https://www.php.net/manual/en/pdo.construct.php

I see that you mentioned using PRAGMA statement for the activation of
encryption. From my currently unencrypted database, and connecting to the
SQLite database with PDO, how would I enable the encryption and then for
subsequent queries ensure that the database file remains readable by my
code base? I've looked at the PRAGMA statements and there doesn't appear to
be any public documentation on this interface.

https://www.sqlite.org/pragma.html

I do understand this is outside of the scope of SQLite when asking for help
with PHP's PDO interface. I'm just hoping that people on here have
experience with PHP might have a solution.



On Tue, Aug 20, 2019 at 2:56 PM Richard Hipp <[hidden email]> wrote:

> On 8/20/19, Mark Tomlin <[hidden email]> wrote:
> > I would like to use SQLite's Encryption Extension with my PHP 7.2 code
> > base, as well as 7.3 and 7.4 code bases moving forward. My hope is is
> that
> > the see-sqlite.c code file can be simply renaming it to sqlite.c and
> > dropping it into PHP's build directory.Then compiling it as I normally
> > would. I'm also guessing I would have to modify the (see-)sqlite.c code
> to
> > make a call to sqlite3_key from within the sqlite3 function call so
> > that the encryption is always on and would not require any modifications
> to
> > the PHP source code.
>
> You do not need to modify any C code, either in SQLite/SEE or in PHP.
> You can activate encryption and set the encryption key using a PRAGMA
> statement.
>
> >
> > Has anyone done this before?
>
> Yes.
>
>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
Mark Tomlin, CEO.
MimoCAD, Inc.
P: (516) 234-0290
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite Encryption Extension For Use with With PHP 7.x Code

Richard Hipp-3
On 8/20/19, Mark Tomlin <[hidden email]> wrote:
> I've looked at the PRAGMA statements and there doesn't appear to
> be any public documentation on this interface.

The PRAGMAs in question are part of the SEE documentation, as they are
unique to SEE.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite Encryption Extension For Use with With PHP 7.x Code

Simon Slavin-3
In reply to this post by Mark Tomlin
On 20 Aug 2019, at 8:40pm, Mark Tomlin <[hidden email]> wrote:

> I found the regular sqlite3::open method has an $encryption_key parameter.
>
> https://www.php.net/manual/en/sqlite3.open.php

Yep.

> As I am using PDO for the interface into SQLite, I wonder if there is a similar parameter for that interface.

Nope.  The @passwd parameter is for user authentication, not encryption.

If there's way to do it with the PDO module, I'm betting that it's using PRAGMA:

    PRAGMA key='your-secret-key';

Issue this as a SQL command immediately after you've opened the SQLite database using PDO.  It might work in PHP.  I've never tried it.  If it doesn't work there's probably no way to make it work short of compiling your own copy of PHP.

You might use the sqlite3 module to create a tiny test encrypted database, with a row of data, for you to do testing on.  If you can successfully read that database using PDO try using the above PRAGMA with a freshly-created database to see if PDO can be used to encrypt.

Refs:

<https://www.sqlite.org/see/doc/release/www/readme.wiki>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Load extension in a trigger?

Brent Wood
 Hi,

I'm using spatialite via the command line in a script/batch file:

sqlite3 -cmd ".load mod_spatialite.com" %DB%

everything works perfectly...

However, I need to use it via JDBC for a java application and loading the spatialite extension in the jdbc connection is extremely buggy... unusable.

My current hack/workaround is a batch file (or bash script depending on platform) to do a spatial update after the non-spatial insert via JDBC. The only positive thing about this approach is that it works reliably...

Given it seems I'm not going to be able to get a robust JDBC/spatialite working I was trying to use a db trigger to do the update automatically.

I can create a trigger with a spatialite sql function to modify point and line geometry columns quite easily, but the SQL that invokes the trigger also needs the spatial library to be loaded in the connection... so this doesn't work with JDBC - the trigger gives an unknown function error for any spatial function in the trigger sql.

I have tried another approach which would work nicely for this use case, but cannot get it to work - that is, loading the spatialite library inside the trigger, so even a non-spatial connection could load the extension and run the query:

create trigger mk_geom after insert on station
begin
  --.load mod_spatialite  or --
  select load_extension(mod_spatialite);
  <spatial sqls....>;
end;

This fails. Reading the docs suggests this may because in this situation I can't load extensions (but I'm not sure).

My initial question is:
Can anyone tell me how to load the extension inside the trigger (perhaps how to enable extensions in a cli connection)?

Can anyone suggest an alternative way to avoid the use of system calls invoking batch files (from a java application) to run spatial queries?


Thanks

Brent Wood

 
 
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users