Re: [sqlite] Compiling SQLite Encryption Extension for ARM processor

[Jens Alfke]

> On Feb 13, 2020, at 10:51 AM, Subodh Pathak  wrote:
> 
> I am looking for help to configure machine to compile SEE for ARM. I am
> using Android mobile Samsung G7.

You have to use a cross-compiler, a version of GCC that runs on your platform 
but generates ARM-Linux code. 
Specifically, to build for Android you should be using the Android Native 
Development Kit (NDK). The page Dr. Hipp linked to will show you how to do that.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compiling SQLite Encryption Extension for ARM processor

[Richard Hipp]

On 2/13/20, Subodh Pathak  wrote:
>
> I am trying to compile SEE for ARM processor.

There is a website explain how to compile SQLite for Android here:
https://www.sqlite.org/android/doc/trunk/www/index.wiki

Please review the instructions on that website and write again if they
do not work for you.

D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Compiling SQLite Encryption Extension for ARM processor

[Subodh Pathak]

Team,

I am trying to compile SEE for ARM processor.
I have followed following steps. But was not successful in generating "
*libsqliteX.so*" file which can be used on Android Samsung G7 mobile.

1.  Installed GCC compiler from “http://www.mingw.org/” reference at “
https://gcc.gnu.org/”.

2.  Installed required library specially related to lpthread.

3.  Downloaded another library “libdl.a” from “
https://github.com/dlfcn-win32/dlfcn-win32” to compile using -ldl command.
MinGW-W32 and MinGW-W64 only have dlfcn to provide libdl, but that's not a
regularly installed package. After extracting package. Copied “libdl.a” to
“C:\MinGW\lib”

* gcc -c -fPIC sqlite3.c see-aes256-ofb.c*

* gcc -shared -o libsqliteX.so -fPIC sqlite3.o see-aes256-ofb.c
-ldl –lpthread*

4.  Above mentioned command does generate "libsqliteX.so" file but not
compatible to use on Android Samsugn G7.



References:

https://gitlab.kitware.com/cmake/cmake/merge_requests/1642

https://gcc.gnu.org/install/binaries.html

https://gcc.gnu.org/

https://sqlite.org/android/doc/trunk/www/index.wiki

https://www.sqlite.org/see/doc/release/www/index.wiki

https://www.hwaci.com/sw/sqlite/see.html

https://sqlite.org/src/doc/trunk/README.md




I have also tried to do below configuration before compile but no lock.

./configure --build=x86_64-unknown-linux-gnu --host=arm-linux
--target=arm-linux
I am looking for help to configure machine to compile SEE for ARM. I am
using Android mobile Samsung G7.
Any help is much appreciated.

Regards,
Subodh
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

On 20 Aug 2019, at 8:40pm, Mark Tomlin  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:


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Richard Hipp]

On 8/20/19, Mark Tomlin  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
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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  wrote:

> On 8/20/19, Mark Tomlin  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
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Mark Tomlin, CEO.
MimoCAD, Inc.
P: (516) 234-0290
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Richard Hipp]

On 8/20/19, Mark Tomlin  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
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use SQLite Encryption Extension in Oracle MAF app

[Richard Hipp]

On 10/29/18, smartsanja  wrote:
> My hybrid mobile app built using Oracle MAF framework. I have a requirement
> to encrypt the sqlite db using SQLite Encryption Extension. I could not find
> any documentation on how to integrate SEE in Oracle MAF apps. I want to know
> whether is this can be done or totally not supported yet. Highly appreciated
> expert comments on this. Thanks
>

SEE is delivered as C-language source code.

Is it possible to add C-language source code libraries to an Oracle
MAF application?  I do not know.  You can practice by trying to add
the public-domain "sqlite3.c" source code to your application.  Verify
that your application is using the public-domain "sqlite3.c" source
file, and not some other version of SQLite that is pre-compiled into
Oracle MAF, by looking at the results of "SELECT sqlite_source_id()".
Once you know how to compile public-domain "sqlite3.c" into your
application, switching over to SEE is simple.  After acquiring a
one-time license for your development team, simply substitute the
SEE-enabled "sqlite3.c" source file for the public-domain "sqlite3.c"
source file and recompile.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to use SQLite Encryption Extension in Oracle MAF app

[smartsanja]

My hybrid mobile app built using Oracle MAF framework. I have a requirement
to encrypt the sqlite db using SQLite Encryption Extension. I could not find
any documentation on how to integrate SEE in Oracle MAF apps. I want to know
whether is this can be done or totally not supported yet. Highly appreciated
expert comments on this. Thanks



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Questions about SQLite Encryption Extension (SEE)

[Yuriy M. Kaminskiy]

Jens Alfke  writes:

>> On Jun 17, 2017, at 7:02 AM, Yuriy M. Kaminskiy  wrote:
>> 
>> *) don't appear to be able to keep key in system-provided secure 
>> device/enclave;
>
> In their defense, I think this is out-of-scope for a cross-platform db
> encryption library, as there are so many different APIs for this on
> different platforms, and different valid choices even on one
> platform.

Sure, this feature is very unlikely to be present for /any/ user-level
file/db encryption.
But totally within scope for system-wide full-disk encryption.

> So I see this more as an application responsibility.
I'm not sure application can do a lot here: if you are going to
perform encryption on user-level, key will be in application memory,
not in security enclave.

[...]
>> *) error handling looks problematic in a lot of places (no error
>> checks, there are memory/resource leaks on error paths).
>
> If you have notes on those, could you share them? It would be good to

Take any openssl function that can return error (e.g. if you specify
PRAGMA cipher=aes-128-gcm, EVP_CipherFinal is expected to always return
error on decryption, as sqlcipher does not provide correct tag [or, more
precisely, *any* tag]).
There are no check for this error.

Take sqlcipher_codec_ctx_init:

  if((rc = sqlcipher_cipher_ctx_init(>read_ctx)) != SQLITE_OK) return rc; 
  if((rc = sqlcipher_cipher_ctx_init(>write_ctx)) != SQLITE_OK) return rc;
  
Suppose, first sqlcipher_cipher_ctx_init succeed, but second failed.
Who is going to release ctx->read_ctx (and ctx itself)?
And a lot more similar things.

Sure, nothing *terrible serious* (it is leak on error path, likely only
possible on OOM, so program state is rather fragile and most likely will
crash or terminate anyway), but still shows that hardly anyone seriously
reviewed code.

> get those cleaned up. (I don’t work on SQLCipher, but I do work on a
> library that uses it in some configurations.)

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Questions about SQLite Encryption Extension (SEE)

[Jens Alfke]

> On Jun 17, 2017, at 7:02 AM, Yuriy M. Kaminskiy  wrote:
> 
> *) don't appear to be able to keep key in system-provided secure 
> device/enclave;

In their defense, I think this is out-of-scope for a cross-platform db 
encryption library, as there are so many different APIs for this on different 
platforms, and different valid choices even on one platform. So I see this more 
as an application responsibility.

For example, on iOS you could store the key as a normal Keychain item or put it 
under Touch ID control, or make the user enter a passphrase. Storing or 
accessing the key may require user interaction, which means UI code that likely 
needs to be customized to the application. In some environments you might need 
to request the key from a key-server. Etc.

> *) error handling looks problematic in a lot of places (no error
> checks, there are memory/resource leaks on error paths).

If you have notes on those, could you share them? It would be good to get those 
cleaned up. (I don’t work on SQLCipher, but I do work on a library that uses it 
in some configurations.)

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Questions about SQLite Encryption Extension (SEE)

[Yuriy M. Kaminskiy]

Jens Alfke  writes:
>> And any non-opensource crypto should be taken with triple caution. Or
>> even opensource, but not widely-used or otherwise not known to be
>> carefully peer-reviewed (FWIW, I looked at e.g. wxsqlite crypto code, it
>> looks not exactly promising too).
>
> What do you think of SQLCipher?

Disclaimer: I'm not a real cryptographer ^_^, while I can notice some
outright problematic things, but easily miss others.

From quick overview:

*) they had sense to avoid self-coding crypto primitives, and use
openssl, tomscrypt or (macs?) commoncrypto;
   *) with openssl, they appear to support any cipher supported by
openssl (but they deprecated `PRAGMA cipher` in recent releases, so it
is aes-256-cbc by default);
   *) with other backends, no flexibility at all: only aes-256-cbc
supported;
*) use crypto provider's [strong] random for IV;
*) for (optional) integrity, uses HMAC-SHA1; AFAIK, while SHA1 is
getting more and more broken, HMAC-SHA1 is not broken yet; but it would
be good if they had a plan ahead.
*) don't support AEAD modes (such as AES-{GCM,CCM} or CHACHA20-POLY1305);
*) for kdf, uses PBKDF2-HMAC-SHA1 by default (same: it is not broken
yet, but).
*) don't appear to be able to keep key in system-provided secure
device/enclave;
*) don't appear to be able to easily change passphrase (only by
re-encrypting whole database) or use several passphrases (see LUKS for
comparison).
*) nitpick, but I don't like how they love to constantly re-init cipher
(and hmac) context on reading/writing each page (key setup is not
exactly inexpensive thing; with some cipher [e.g. blowfish], it is
outright SLOW). (And slow crypto is *also* a security problem: if crypto
is expensive, people tends to avoid it).
*) error handling looks problematic in a lot of places (no error
checks, there are memory/resource leaks on error paths).

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Questions about SQLite Encryption Extension (SEE)

[Rowan Worth]

On 9 June 2017 at 22:30, Yuriy M. Kaminskiy  wrote:
>
> Don't know about windows, but on linux no additional "debug privileges"
> needed. You can attach debugger (ptrace syscall) to any process running
> with under same user. Additional privileges needed only for debugging
> processes running under different users (or suid executables).
>

This is generally true, but might not be in the future. The linux kernel
does have an option to limit the processes on which ptrace is effective,
even within processes owned by a specific user. Archlinux at least enables
it by default, I guess time will tell if it sees widespread adoption. I
think it works by allowing ptrace only if invoked by root, or if the target
process is a child of the calling process. I can't find much documentation
on it but here's the arch description:
https://wiki.archlinux.org/index.php/security#ptrace_scope

-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Questions about SQLite Encryption Extension (SEE)

[Matthias-Christian Ott]

On 2017-06-09 00:13, Wout Mertens wrote:
> Isn't it all just obfuscation? Any root user can read your key, if not from
> disk then from memory. Any normal user can't read your key, nor from disk,
> nor from memory; and they can't read your db file either.
> 
> So if the adversary is someone with access to your disk image, disk
> encryption trumps db encryption (unless the disk encryption is vulnerable
> to known-plaintext attacks, but I guess they probably apply to sqlite too).
> 
> If the adversary is another process on the same host, encrypting the db
> just adds obfuscation, which is security against lazy hackers.

When the discussion about DRM and Trusted Computing was more active,
this was widely discussed. Cory Doctorow gave a talk about DRM at
Microsoft that illustrates this misuse of cryptography [1]. Mark Stefik
described a scary vision of DRM over two decades ago [2]. Richard
Stallman has said and written a lot about DRM as well. So perhaps we
should not start another debate on this mailing list and read what has
already been written and said about it at great length.

My personal conclusion from the discussion about DRM and Trusted
Computing is that DRM will never work unless we don't own our computers
but someone else who controls a cryptographic chip in them does.
Unfortunately, this is reality for devices with iOS and other similar
products.

SEE only protects the database if an attacker only has access the
storage medium of the database but not the encryption key. Not more and
not less. You can of course argue about how difficult it is to obtain
the encryption key but has nothing to do with SEE. It depends only
concrete use cases, scenarios and threat models but not SEE. So can we
have the discussion about this on another mailing list?

[1] http://craphound.com/msftdrm.txt
[2]
http://www2.parc.com/istl/groups/uir/publications/items/UIR-1996-10-Stefik-InternetCommerce-IgnitingDreams.pdf
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Questions about SQLite Encryption Extension (SEE)

[Jens Alfke]

> On Jun 9, 2017, at 7:30 AM, Yuriy M. Kaminskiy  wrote:
> 
> On other hand, application-level encryption should be used with great
> caution; it is a way too often designed and implemented by
> non-cryptographers, does not use optimized or hardware-assisted crypto
> primitives (and, for AES, often use naive implementation without
> protection against timing/cache attacks),

Hm. The file-encryption code I’ve seen generally delegates the actual crypto 
primitives to either OpenSSL, libSodium, or an OS-provided subsystem like 
Apple’s CommonCrypto. I agree that I’d have little trust in a library like this 
that tried to write its own implementation of AES, etc.

> does not use protected hardware for keeping keys;

Yeah, key management is generally “left as an exercise for the app developer”, 
who has little knowledge of security. The problem I’ve seen on mobile devices 
is that app developers will often pass the buck to the user, i.e. deriving the 
key from a passphrase. The user then has to type the damn passphrase every time 
they run the app, which heavily incents them to pick something short and 
trivially-crackable.

This has gotten somewhat better with biometric sensors. On iOS it’s pretty easy 
to store a key in the Keychain protected by TouchID, which means the key itself 
resides in the CPU secure enclave, which will only release it when it gets a 
fingerprint. Not that fingerprints are massively secure, but they’re a lot 
better than a four-digit PIN, and require physical access to spoof.

(FYI, Apple has an excellent iOS security white-paper that covers all of this 
stuff in detail.)

> And any non-opensource crypto should be taken with triple caution. Or
> even opensource, but not widely-used or otherwise not known to be
> carefully peer-reviewed (FWIW, I looked at e.g. wxsqlite crypto code, it
> looks not exactly promising too).

What do you think of SQLCipher?

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Questions about SQLite Encryption Extension (SEE)

[Jens Alfke]

> On Jun 8, 2017, at 3:13 PM, Wout Mertens  wrote:
> 
> Isn't it all just obfuscation? Any root user can read your key, if not from
> disk then from memory.

Keys on disk are [or should be!] generally stored by special OS subsystems 
(like the Keychain on Apple platforms) that use encrypted storage, the keys to 
which are in turn managed by a secure enclave in the CPU and/or derived from 
user login passphrase.

I believe (but don’t know the details) that on macOS it’s pretty difficult for 
a process to get access to another process’ address space, even one running as 
the same user. If this capability is covered by System Integrity Protection, 
then it would require more than just(!) root access, involving at least a 
reboot into system recovery mode to turn off SIP; i.e. needing physical access 
to the machine.

On iOS, processes are completely sandboxed from each other, most of the types 
of exploits used to get root are unavailable, and  getting any access to a 
locked or powered-down device is close to impossible, as the FBI found out in 
the San Bernardino case last year.

In any case, regardless of the technical benefits, there can be legal 
requirements for app-level encryption, for example apps storing health data 
which in the US fall under HIPPAA. (It’s actually a bit vague about whether 
encryption is strictly required, but this tends to be interpreted as “if it’s 
feasible, encrypt it”: https://www.sookasa.com/resources/HIPAA-encryption/ )

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Questions about SQLite Encryption Extension (SEE)

[Yuriy M. Kaminskiy]

Eric Grange  writes:

>> Isn't it all just obfuscation?
>
> Not really, the encryption protects the file, wherever it is, as long as
> the attacker does not have access to the application keys or application
> memory.
>
>> If the adversary is another process on the same host, encrypting the db
>> just adds obfuscation, which is security against lazy hackers.
>
> Another process would need debug privileges to access your application's
> memory.

Don't know about windows, but on linux no additional "debug privileges"
needed. You can attach debugger (ptrace syscall) to any process running
with under same user. Additional privileges needed only for debugging
processes running under different users (or suid executables).

> If you rely on disk encryption primarily, then if that encryption
> compromised, or if backups are compromised, or if a root user copies the
> wrong files in the wrong places, or just makes any error, then everything
> on that disk can be compromised.

> With application-level encryption, user error will only compromise that
> app's data, and you otherwise need the root user to be the attacker, which
> makes the problem quite different from the root user making a mistake.
>
> Finally in the grand scheme of things, the likelyhood of any disk
> encryption being broken (as an implementation) is extremely high, given it
> is such a juicy target.

And that's why they attract a lot of attention, and any bugs or even
traces of weakness were weeded out very long ago. I'm not so sure about
semi-closed application-level security solutions, like SEE. All I've
seen in public was not very encouraging (somewhat unusual for disk
encryption crypto constructs [with one of primitives already considered
to be broken], nothing said about KDF, nothing about IV, nothing about
design, etc).

> And when it is broken, automated tools will be
> available for all lazy hackers to download and deploy with a single
> click.
>
> So while you can and should use disk encryption, it can only be seen as an
> added security layer, never as a primary security layer.

I'd say opposite. System-wide encryption is a must have (*especially*,
swap, hibernation and temporary space encryption; there are *nothing*
that can be done about that on application-level!).

On other hand, application-level encryption should be used with great
caution; it is a way too often designed and implemented by
non-cryptographers, does not use optimized or hardware-assisted crypto
primitives (and, for AES, often use naive implementation without
protection against timing/cache attacks), does not use protected
hardware for keeping keys; and in general provides very little or no
additional security over FDE plus file/directory permissions.

And any non-opensource crypto should be taken with triple caution. Or
even opensource, but not widely-used or otherwise not known to be
carefully peer-reviewed (FWIW, I looked at e.g. wxsqlite crypto code, it
looks not exactly promising too).

> On Fri, Jun 9, 2017 at 12:13 AM, Wout Mertens 
> wrote:
>
>> Isn't it all just obfuscation? Any root user can read your key, if not from
>> disk then from memory. Any normal user can't read your key, nor from disk,
>> nor from memory; and they can't read your db file either.
>>
>> So if the adversary is someone with access to your disk image, disk
>> encryption trumps db encryption (unless the disk encryption is vulnerable
>> to known-plaintext attacks, but I guess they probably apply to sqlite too).
>>
>> If the adversary is another process on the same host, encrypting the db
>> just adds obfuscation, which is security against lazy hackers.
>>
>> On Thu, Jun 8, 2017 at 9:04 PM Richard Hipp  wrote:
>>
>> > On 6/8/17, Wout Mertens  wrote:
>> > > Just musing: is an encrypted disk not more reliable? You have to store
>> > the
>> > > key somewhere…
>> >
>> > Maybe.  I guess it depends on your threat model.
>> >
>> > Encrypting the whole disk is a system setting,.  Anybody who has
>> > access to the system can see everything on disk.  You also have to
>> > have administrator privileges to set it up.
>> >
>> > Encrypting a single database file is an application setting.  Some
>> > applications might want to hide there data from other applications on
>> > the same system, or from the user of the system.  Whole disk
>> > encryption won't help there.  And, database encryption requires no
>> > special privileges.
>> >
>> > --
>> > D. Richard Hipp
>> > d...@sqlite.org

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Questions about SQLite Encryption Extension (SEE)

[wout.mertens]

Aha, that does make sense, thinking of each risk in terms in
likelihoods. So encrypting the db as well as the disk seems the safest
route here.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Questions about SQLite Encryption Extension (SEE)

[Eric Grange]

> Isn't it all just obfuscation?

Not really, the encryption protects the file, wherever it is, as long as
the attacker does not have access to the application keys or application
memory.

> If the adversary is another process on the same host, encrypting the db
> just adds obfuscation, which is security against lazy hackers.

Another process would need debug privileges to access your application's
memory.

If you rely on disk encryption primarily, then if that encryption
compromised, or if backups are compromised, or if a root user copies the
wrong files in the wrong places, or just makes any error, then everything
on that disk can be compromised.

With application-level encryption, user error will only compromise that
app's data, and you otherwise need the root user to be the attacker, which
makes the problem quite different from the root user making a mistake.

Finally in the grand scheme of things, the likelyhood of any disk
encryption being broken (as an implementation) is extremely high, given it
is such a juicy target. And when it is broken, automated tools will be
available for all lazy hackers to download and deploy with a single click.

So while you can and should use disk encryption, it can only be seen as an
added security layer, never as a primary security layer.

Eric


On Fri, Jun 9, 2017 at 12:13 AM, Wout Mertens 
wrote:

> Isn't it all just obfuscation? Any root user can read your key, if not from
> disk then from memory. Any normal user can't read your key, nor from disk,
> nor from memory; and they can't read your db file either.
>
> So if the adversary is someone with access to your disk image, disk
> encryption trumps db encryption (unless the disk encryption is vulnerable
> to known-plaintext attacks, but I guess they probably apply to sqlite too).
>
> If the adversary is another process on the same host, encrypting the db
> just adds obfuscation, which is security against lazy hackers.
>
> On Thu, Jun 8, 2017 at 9:04 PM Richard Hipp  wrote:
>
> > On 6/8/17, Wout Mertens  wrote:
> > > Just musing: is an encrypted disk not more reliable? You have to store
> > the
> > > key somewhere…
> >
> > Maybe.  I guess it depends on your threat model.
> >
> > Encrypting the whole disk is a system setting,.  Anybody who has
> > access to the system can see everything on disk.  You also have to
> > have administrator privileges to set it up.
> >
> > Encrypting a single database file is an application setting.  Some
> > applications might want to hide there data from other applications on
> > the same system, or from the user of the system.  Whole disk
> > encryption won't help there.  And, database encryption requires no
> > special privileges.
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Questions about SQLite Encryption Extension (SEE)

[Simon Slavin]

On 8 Jun 2017, at 11:13pm, Wout Mertens  wrote:

> So if the adversary is someone with access to your disk image, disk
> encryption trumps db encryption (unless the disk encryption is vulnerable
> to known-plaintext attacks, but I guess they probably apply to sqlite too).

Your hope is that the database is held on a server but the decryption key is on 
the computers users type on.  Or if you have a web-facing setup with multiple 
servers, your database is on the database computer running PHP and the key is 
in the JavaScript files on the web server.

But in the long run, physical possession always trumps encryption.  Once 
they’re in a situation where they can try keys endlessly it’s just a case of 
how much time and money they’re willing to spend to get access to your data.  
Are you a target of the CIA ?  Don’t rely on encryption.  If you’re someone 
with no money and no interest in politics ?  Then encryption is good at 
preventing casual theft by bored employees and thieves of opportunity.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Questions about SQLite Encryption Extension (SEE)

[Wout Mertens]

Isn't it all just obfuscation? Any root user can read your key, if not from
disk then from memory. Any normal user can't read your key, nor from disk,
nor from memory; and they can't read your db file either.

So if the adversary is someone with access to your disk image, disk
encryption trumps db encryption (unless the disk encryption is vulnerable
to known-plaintext attacks, but I guess they probably apply to sqlite too).

If the adversary is another process on the same host, encrypting the db
just adds obfuscation, which is security against lazy hackers.

On Thu, Jun 8, 2017 at 9:04 PM Richard Hipp  wrote:

> On 6/8/17, Wout Mertens  wrote:
> > Just musing: is an encrypted disk not more reliable? You have to store
> the
> > key somewhere…
>
> Maybe.  I guess it depends on your threat model.
>
> Encrypting the whole disk is a system setting,.  Anybody who has
> access to the system can see everything on disk.  You also have to
> have administrator privileges to set it up.
>
> Encrypting a single database file is an application setting.  Some
> applications might want to hide there data from other applications on
> the same system, or from the user of the system.  Whole disk
> encryption won't help there.  And, database encryption requires no
> special privileges.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Questions about SQLite Encryption Extension (SEE)

[Richard Hipp]

On 6/8/17, Wout Mertens  wrote:
> Just musing: is an encrypted disk not more reliable? You have to store the
> key somewhere…

Maybe.  I guess it depends on your threat model.

Encrypting the whole disk is a system setting,.  Anybody who has
access to the system can see everything on disk.  You also have to
have administrator privileges to set it up.

Encrypting a single database file is an application setting.  Some
applications might want to hide there data from other applications on
the same system, or from the user of the system.  Whole disk
encryption won't help there.  And, database encryption requires no
special privileges.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Questions about SQLite Encryption Extension (SEE)

[Wout Mertens]

Just musing: is an encrypted disk not more reliable? You have to store the
key somewhere…

On Thu, Jun 8, 2017, 7:07 PM Richard Hipp  wrote:

> On 6/8/17, Karl Sanders  wrote:
> > I would like to know if an encrypted database allows hot backups and
> > page sizes different from the default one.
>
> Yes and Yes.
>
> >
> > Is encryption applied to everything that gets written to disk?
> > Including transient indices and materializations of views and subqueries?
> >
>
> The database file and rollback journal or WAL file are all encrypted.
> Actually, in the rollback journal and WAL file, the meta-data is not
> encrypted, just the page images that will be written back into the
> database.
>
> Transient indexes and materializations of views and subqueries are not
> encrypted.  I recommend you set "PRAGMA temp_store=MEMORY" so that
> those objects are never written to disk.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Questions about SQLite Encryption Extension (SEE)

[Richard Hipp]

On 6/8/17, Karl Sanders  wrote:
> I would like to know if an encrypted database allows hot backups and
> page sizes different from the default one.

Yes and Yes.

>
> Is encryption applied to everything that gets written to disk?
> Including transient indices and materializations of views and subqueries?
>

The database file and rollback journal or WAL file are all encrypted.
Actually, in the rollback journal and WAL file, the meta-data is not
encrypted, just the page images that will be written back into the
database.

Transient indexes and materializations of views and subqueries are not
encrypted.  I recommend you set "PRAGMA temp_store=MEMORY" so that
those objects are never written to disk.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Questions about SQLite Encryption Extension (SEE)

[Karl Sanders]

I would like to know if an encrypted database allows hot backups and
page sizes different from the default one.

Is encryption applied to everything that gets written to disk?
Including transient indices and materializations of views and subqueries?

Regards,
Karl
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Encryption

[Richard Andersen]

Monday, November 14, 2016, 4:23:49 PM, you wrote:

Thanks a million Ulrich for all the useful information, I think I got
what I need now to make things work the way I want! :)

Richard

> Richard,

>> Well what I've done is to create an encrypted database with
>> SQLite2009 and then use that in my C# project. I just add the password to
>> the connection string in my app and then it works right away.

> According to the information on the SQLite2009 website
> (http://sqlite2009pro.azurewebsites.net/) SQLite2009 supports 2 encryption 
> methods:

> ** Encryption Method is now compatible with wxSQLite3 (AES-128
> bits) and SQLite3 ADO.NET Provider (RSA-MS Crypt) **

> I don't know SQLite2009 from own experience, but I assume that it
> allows you to choose which enryption method to use, when creating a
> new database. To be compatible with the ADO.NET provider
> System.Data.SQLite (http://system.data.sqlite.org) you obviously
> have to choose the corresponding encryption method in SQLite2009.

>> As far as
>> I understand (I'm new to all this) you can also create a database from
>> within your app if it's based on system.data.sqlite.
>> I just can't edit the table columns or add new ones in SQLite2009 once
>> the database is saved or reopened, I can only edit the record data. I
>> just read that once created, you can't (or only very limited) edit the
>> columns of an SQLite database, you have to create a new database with
>> the desired structure and copy the data over.

> I have a bit the impression that you mixed up the terms 'database'
> and 'table'. A 'database' can contain several tables, and adding new
> tables or removing existing tables is simple (and should be
> supported by any SQLite administration tool). However, changing the
> structure of an existing table in SQLite is more complicated, since
> SQLite only supports a limited set of operations to modify a table
> definition. Therefore, if you want to add or remove columns from a
> table definition, you usually have to create a new table with a
> different name, copy the data from the previous table to the new
> one, remove the previous table, and rename the new table to the previous name.

>> I think that's what DB Browser for SQLite does since you actually can
>> reopen and edit the columns and their parameters etc. with it.

> Under the hood DB Browser for SQLite performs the above mentioned steps for 
> you.

>> It can also create encrypted databases but the encryption scheme it uses
>> is not supported by system.data.sqlite it seems.

> Correct. DB Browser for SQLite supports SQLCipher
> (https://www.zetetic.net/sqlcipher/), an AES-256 encryption scheme.

>> But all in all it works fine, I'm just a bit concerned with the RSA
>> encryption in system.data.lite as I've heard it's slow and easy to
>> crack, so I'd prefer something else.

> The RSA encryption offered by System.Data.SQLite should not be
> used, if security is a concern for you. You should prefer an AES encryption 
> scheme.

>> SQLITE Expert looks interesting but I wonder if it also uses the
>> built-in RSA encryption in system.data.sqlite?

> According to the description on the website
> (http://www.sqliteexpert.com/features.html) SQLiteExpert "Supports
> password protected databases (requires third party SQLite library -
> not included)." That is, you have to provide a SQLite3 DLL
> supporting the encryption scheme of your choice. For example,

> - SQLCipher (https://github.com/sqlcipher/sqlcipher), you have to build the 
> DLL yourself
> - wxSQLite3 (https://github.com/utelle/wxsqlite3/releases), Windows binaries 
> are provided

> Other SQLite3 management tools that support the wxSQLite3 encryption scheme 
> are

> - SQLite Maestro
> (https://www.sqlmaestro.com/en/products/sqlite/maestro/about/)
> - wxSQLitePlus (https://github.com/guanlisheng/wxsqliteplus)

>> > Richard Andersen wrote:
>> > In DB Browser for SQlite I can edit the table but I'm not sure if the
>> > SQLCipher encryption used here can be made to work with
>> > System.Data.SQlite, or how to do if it can. Does anyone know anything
>> > about this?

> In principle, it should be possible to replace the SQLite
> encryption implementation in System.Data.SQLite by the SQLCipher or
> wxSQLite3 implementation, although it might not be trivial. The
> latter should be easier to accomplish, since the wxSQLite3
> encryption implementation is self-contained, while SQLCipher
> requires the OpenSSL library as well.

> Regards,

> Ulrich
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Encryption

[Chris Locke]

Ulrich- a fantastically detailed post.

On Mon, Nov 14, 2016 at 3:23 PM, Ulrich Telle <ulrich.te...@gmx.de> wrote:

> Richard,
>
> > Well what I've done is to create an encrypted database with
> > SQLite2009 and then use that in my C# project. I just add the password to
> > the connection string in my app and then it works right away.
>
> According to the information on the SQLite2009 website (
> http://sqlite2009pro.azurewebsites.net/) SQLite2009 supports 2 encryption
> methods:
>
> ** Encryption Method is now compatible with wxSQLite3 (AES-128 bits) and
> SQLite3 ADO.NET Provider (RSA-MS Crypt) **
>
> I don't know SQLite2009 from own experience, but I assume that it allows
> you to choose which enryption method to use, when creating a new database.
> To be compatible with the ADO.NET provider System.Data.SQLite (
> http://system.data.sqlite.org) you obviously have to choose the
> corresponding encryption method in SQLite2009.
>
> > As far as
> > I understand (I'm new to all this) you can also create a database from
> > within your app if it's based on system.data.sqlite.
> > I just can't edit the table columns or add new ones in SQLite2009 once
> > the database is saved or reopened, I can only edit the record data. I
> > just read that once created, you can't (or only very limited) edit the
> > columns of an SQLite database, you have to create a new database with
> > the desired structure and copy the data over.
>
> I have a bit the impression that you mixed up the terms 'database' and
> 'table'. A 'database' can contain several tables, and adding new tables or
> removing existing tables is simple (and should be supported by any SQLite
> administration tool). However, changing the structure of an existing table
> in SQLite is more complicated, since SQLite only supports a limited set of
> operations to modify a table definition. Therefore, if you want to add or
> remove columns from a table definition, you usually have to create a new
> table with a different name, copy the data from the previous table to the
> new one, remove the previous table, and rename the new table to the
> previous name.
>
> > I think that's what DB Browser for SQLite does since you actually can
> > reopen and edit the columns and their parameters etc. with it.
>
> Under the hood DB Browser for SQLite performs the above mentioned steps
> for you.
>
> > It can also create encrypted databases but the encryption scheme it uses
> > is not supported by system.data.sqlite it seems.
>
> Correct. DB Browser for SQLite supports SQLCipher (
> https://www.zetetic.net/sqlcipher/), an AES-256 encryption scheme.
>
> > But all in all it works fine, I'm just a bit concerned with the RSA
> > encryption in system.data.lite as I've heard it's slow and easy to
> > crack, so I'd prefer something else.
>
> The RSA encryption offered by System.Data.SQLite should not be used, if
> security is a concern for you. You should prefer an AES encryption scheme.
>
> > SQLITE Expert looks interesting but I wonder if it also uses the
> > built-in RSA encryption in system.data.sqlite?
>
> According to the description on the website (http://www.sqliteexpert.com/
> features.html) SQLiteExpert "Supports password protected databases
> (requires third party SQLite library - not included)." That is, you have to
> provide a SQLite3 DLL supporting the encryption scheme of your choice. For
> example,
>
> - SQLCipher (https://github.com/sqlcipher/sqlcipher), you have to build
> the DLL yourself
> - wxSQLite3 (https://github.com/utelle/wxsqlite3/releases), Windows
> binaries are provided
>
> Other SQLite3 management tools that support the wxSQLite3 encryption
> scheme are
>
> - SQLite Maestro (https://www.sqlmaestro.com/en/products/sqlite/maestro/
> about/)
> - wxSQLitePlus (https://github.com/guanlisheng/wxsqliteplus)
>
> > > Richard Andersen wrote:
> > > In DB Browser for SQlite I can edit the table but I'm not sure if the
> > > SQLCipher encryption used here can be made to work with
> > > System.Data.SQlite, or how to do if it can. Does anyone know anything
> > > about this?
>
> In principle, it should be possible to replace the SQLite encryption
> implementation in System.Data.SQLite by the SQLCipher or wxSQLite3
> implementation, although it might not be trivial. The latter should be
> easier to accomplish, since the wxSQLite3 encryption implementation is
> self-contained, while SQLCipher requires the OpenSSL library as well.
>
> Regards,
>
> Ulrich
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Encryption

[Ulrich Telle]

> > On Nov 14, 2016, at 7:23 AM, Ulrich Telle wrote:
> > 
> > The RSA encryption offered by System.Data.SQLite should not be used,
> > if security is a concern for you. You should prefer an AES encryption 
> > scheme.
> 
> I’m surprised anything encrypts databases with RSA, as that algorithm
> is pretty unsuitable for file encryption.

Ouch ... sorry for not resolving the confusion ... I've been taken again by the 
*wrong* citation of RSA encryption.

In fact, System.Data.SQLite acquires an RSA provider, but doesn't use key 
exchange anywhere. Only the supported symmetric encryption algorithm is used. 
And that algorithm is *RC4*.

> It’s an asymmetric (public-key) cipher: you encrypt data with an RSA
> public key, which can then only be decrypted by the owner of the
> matching private key. (This is used by email encryption, for example.)
> Or you can sign data with the private key so that it can be verified
> by anyone who has the public key. Neither of these modes is useful for
> securing a file that you are going to use yourself. Moreover, RSA is
> extremely slow.

True. However, RC4 is fast, but also weak and should therefore be avoided.

> File encryption uses a symmetric-key cipher, of which AES is the most
> common up-to-date one. Both SQLite’s own encryption engine and the
> 3rd party SQLCipher use AES.

The wxSQLite3 encryption extension also uses AES, either AES-128 or AES-256.

Regards,

Ulrich
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Encryption

[Jens Alfke]

> On Nov 14, 2016, at 7:23 AM, Ulrich Telle  wrote:
> 
> The RSA encryption offered by System.Data.SQLite should not be used, if 
> security is a concern for you. You should prefer an AES encryption scheme.

I’m surprised anything encrypts databases with RSA, as that algorithm is pretty 
unsuitable for file encryption. It’s an asymmetric (public-key) cipher: you 
encrypt data with an RSA public key, which can then only be decrypted by the 
owner of the matching private key. (This is used by email encryption, for 
example.) Or you can sign data with the private key so that it can be verified 
by anyone who has the public key. Neither of these modes is useful for securing 
a file that you are going to use yourself. Moreover, RSA is extremely slow.

File encryption uses a symmetric-key cipher, of which AES is the most common 
up-to-date one. Both SQLite’s own encryption engine and the 3rd party SQLCipher 
use AES.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Encryption

[Ulrich Telle]

Richard,

> Well what I've done is to create an encrypted database with
> SQLite2009 and then use that in my C# project. I just add the password to
> the connection string in my app and then it works right away.

According to the information on the SQLite2009 website 
(http://sqlite2009pro.azurewebsites.net/) SQLite2009 supports 2 encryption 
methods:

** Encryption Method is now compatible with wxSQLite3 (AES-128 bits) and 
SQLite3 ADO.NET Provider (RSA-MS Crypt) **

I don't know SQLite2009 from own experience, but I assume that it allows you to 
choose which enryption method to use, when creating a new database. To be 
compatible with the ADO.NET provider System.Data.SQLite 
(http://system.data.sqlite.org) you obviously have to choose the corresponding 
encryption method in SQLite2009.

> As far as
> I understand (I'm new to all this) you can also create a database from
> within your app if it's based on system.data.sqlite.
> I just can't edit the table columns or add new ones in SQLite2009 once
> the database is saved or reopened, I can only edit the record data. I
> just read that once created, you can't (or only very limited) edit the
> columns of an SQLite database, you have to create a new database with
> the desired structure and copy the data over.

I have a bit the impression that you mixed up the terms 'database' and 'table'. 
A 'database' can contain several tables, and adding new tables or removing 
existing tables is simple (and should be supported by any SQLite administration 
tool). However, changing the structure of an existing table in SQLite is more 
complicated, since SQLite only supports a limited set of operations to modify a 
table definition. Therefore, if you want to add or remove columns from a table 
definition, you usually have to create a new table with a different name, copy 
the data from the previous table to the new one, remove the previous table, and 
rename the new table to the previous name.

> I think that's what DB Browser for SQLite does since you actually can
> reopen and edit the columns and their parameters etc. with it.

Under the hood DB Browser for SQLite performs the above mentioned steps for you.

> It can also create encrypted databases but the encryption scheme it uses
> is not supported by system.data.sqlite it seems.

Correct. DB Browser for SQLite supports SQLCipher 
(https://www.zetetic.net/sqlcipher/), an AES-256 encryption scheme.

> But all in all it works fine, I'm just a bit concerned with the RSA
> encryption in system.data.lite as I've heard it's slow and easy to
> crack, so I'd prefer something else.

The RSA encryption offered by System.Data.SQLite should not be used, if 
security is a concern for you. You should prefer an AES encryption scheme.

> SQLITE Expert looks interesting but I wonder if it also uses the
> built-in RSA encryption in system.data.sqlite?

According to the description on the website 
(http://www.sqliteexpert.com/features.html) SQLiteExpert "Supports password 
protected databases (requires third party SQLite library - not included)." That 
is, you have to provide a SQLite3 DLL supporting the encryption scheme of your 
choice. For example,

- SQLCipher (https://github.com/sqlcipher/sqlcipher), you have to build the DLL 
yourself
- wxSQLite3 (https://github.com/utelle/wxsqlite3/releases), Windows binaries 
are provided

Other SQLite3 management tools that support the wxSQLite3 encryption scheme are

- SQLite Maestro (https://www.sqlmaestro.com/en/products/sqlite/maestro/about/)
- wxSQLitePlus (https://github.com/guanlisheng/wxsqliteplus)

> > Richard Andersen wrote:
> > In DB Browser for SQlite I can edit the table but I'm not sure if the
> > SQLCipher encryption used here can be made to work with
> > System.Data.SQlite, or how to do if it can. Does anyone know anything
> > about this?

In principle, it should be possible to replace the SQLite encryption 
implementation in System.Data.SQLite by the SQLCipher or wxSQLite3 
implementation, although it might not be trivial. The latter should be easier 
to accomplish, since the wxSQLite3 encryption implementation is self-contained, 
while SQLCipher requires the OpenSSL library as well.

Regards,

Ulrich
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Encryption

[Richard Andersen]

Sunday, November 13, 2016, 2:16:40 PM, you wrote:

Thanks. Well what I've done is to create an encrypted database with
SQLite2009 and then use that in my C# project. I just add the password to
the connection string in my app and then it works right away. As far as
I understand (I'm new to all this) you can also create a database from
within your app if it's based on system.data.sqlite.

I just can't edit the table columns or add new ones in SQLite2009 once
the database is saved or reopened, I can only edit the record data. I
just read that once created, you can't (or only very limited) edit the
columns of an SQLite database, you have to create a new database with
the desired structure and copy the data over. I think that's what DB
Browser for SQLite does since you actually can reopen and edit the
columns and their parameters etc. with it. It can also create
encrypted databases but the encryption scheme it uses is not supported
by system.data.sqlite it seems.

But all in all it works fine, I'm just a bit concerned with the RSA
encryption in system.data.lite as I've heard it's slow and easy to
crack, so I'd prefer something else. I haven't notice any speed
problems so far though, I can insert 5500+ records each with 14
columns in a second or so.

SQLITE Expert looks interesting but I wonder if it also uses the
built-in RSA encryption in system.data.sqlite?

> I am not sure about sqlite2009, but you edit DBs that are encrypted using
> the encryption in system.data.sqlite.  I use SQLITE Expert. It allows you
> to replace the standard library which does not support encryption to one
> from the system.data.sqlite that does. It is the interop dll that you
> replace. Then after entering the password, the encryption and decryption
> becomes transparent to you.

> Richard Andersen wrote:
> In DB Browser for SQlite I can edit the table but I'm not sure if the
> SQLCipher encryption used here can be made to work with
> System.Data.SQlite, or how to do if it can. Does anyone know anything
> about this?
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Encryption

[Taosoft]

Friday, November 11, 2016, 9:00:36 PM, you wrote:

Ok, thanks!

> Encryption in system.data.sqlite is legacy encryption, only used within
> itself, and not with other applications.

> On Fri, Nov 11, 2016 at 6:24 PM, Richard Andersen  wrote:

>>
>>
>> I'm using the ADO.NET version (System.Data.SQlite).
>>
>> I've created an RSA encrypted database using SQLite2009 and that is
>> working fine, but I can't find any tools for editing the table in
>> SQLite2009 once it's been created. Is it possible at all?
>>
>> In DB Browser for SQlite I can edit the table but I'm not sure if the
>> SQLCipher encryption used here can be made to work with
>> System.Data.SQlite, or how to do if it can. Does anyone know anything
>> about this?
>>
>> thanks,
>> Richard
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Encryption

[Denis Burke]

I am not sure about sqlite2009, but you edit DBs that are encrypted using
the encryption in system.data.sqlite.  I use SQLITE Expert. It allows you
to replace the standard library which does not support encryption to one
from the system.data.sqlite that does. It is the interop dll that you
replace. Then after entering the password, the encryption and decryption
becomes transparent to you.

Richard Andersen wrote:
In DB Browser for SQlite I can edit the table but I'm not sure if the
SQLCipher encryption used here can be made to work with
System.Data.SQlite, or how to do if it can. Does anyone know anything
about this?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Encryption

[Chris Locke]

Encryption in system.data.sqlite is legacy encryption, only used within
itself, and not with other applications.

On Fri, Nov 11, 2016 at 6:24 PM, Richard Andersen  wrote:

>
>
> I'm using the ADO.NET version (System.Data.SQlite).
>
> I've created an RSA encrypted database using SQLite2009 and that is
> working fine, but I can't find any tools for editing the table in
> SQLite2009 once it's been created. Is it possible at all?
>
> In DB Browser for SQlite I can edit the table but I'm not sure if the
> SQLCipher encryption used here can be made to work with
> System.Data.SQlite, or how to do if it can. Does anyone know anything
> about this?
>
> thanks,
> Richard
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Encryption

[Richard Andersen]

I'm using the ADO.NET version (System.Data.SQlite).

I've created an RSA encrypted database using SQLite2009 and that is
working fine, but I can't find any tools for editing the table in
SQLite2009 once it's been created. Is it possible at all?

In DB Browser for SQlite I can edit the table but I'm not sure if the
SQLCipher encryption used here can be made to work with
System.Data.SQlite, or how to do if it can. Does anyone know anything
about this?

thanks,
Richard

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] PhoneGap with SQLite Encryption Extension?

[Ribeiro, Glauber]

Thank you

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp
Sent: Friday, December 04, 2015 16:50
To: SQLite mailing list
Cc: Santinello, Anthony
Subject: Re: [sqlite] PhoneGap with SQLite Encryption Extension?

On 12/4/15, Ribeiro, Glauber  wrote:
> We have an Android application built on PhoneGap, which uses the built-in
> SQLite to store data.
>
> Does anybody here know if it's possible to replace the built-in SQLite in
> PhoneGap (Android) with a version that supports encryption (S.E.E. or other
> kind), so that the application information would be transparently
> encrypted?
>

You'll have to compile in your own copy of SQLite using the JNI.
Instructions here:
http://www.sqlite.org/android/doc/trunk/www/index.wiki

-- 
D. Richard Hipp
drh at sqlite.org
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] PhoneGap with SQLite Encryption Extension?

[Ribeiro, Glauber]

We have an Android application built on PhoneGap, which uses the built-in 
SQLite to store data. 

Does anybody here know if it's possible to replace the built-in SQLite in 
PhoneGap (Android) with a version that supports encryption (S.E.E. or other 
kind), so that the application information would be transparently encrypted?

Thanks,

glauber

[sqlite] PhoneGap with SQLite Encryption Extension?

[Richard Hipp]

On 12/4/15, Ribeiro, Glauber  wrote:
> We have an Android application built on PhoneGap, which uses the built-in
> SQLite to store data.
>
> Does anybody here know if it's possible to replace the built-in SQLite in
> PhoneGap (Android) with a version that supports encryption (S.E.E. or other
> kind), so that the application information would be transparently
> encrypted?
>

You'll have to compile in your own copy of SQLite using the JNI.
Instructions here:
http://www.sqlite.org/android/doc/trunk/www/index.wiki

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Security vulnerability in the SQLite Encryption Extension

[Richard Hipp]

Part of the interface design to the SQLite Encryption Extension makes
it possible for an application to accidentally supply weak keys.  This
only happens if the keying interface is misused.  Though to be fair,
the documentation could be clearer about how to avoid misusing the
interface.

If an application does misuse the keying interface and supplies a weak
key, then SEE offers no warning.  The database appears to be fully
encrypted.  But an attacker will be able to easily guess the
encryption key.

I will publish patches to SEE in about two weeks that better document
how to avoid misusing the keying interface and perhaps also to provide
feedback (errors) in the case where the keying interface is misused in
a way that results in weak keys.

In the meantime, if you are an SEE licensee and are concerned that you
might be using weak keys in your application, you can contact me
privately (via email to drh at sqlite.org or by phone at +1.704.948.4565)
to learn more about the problem and how you can mitigate the problem
before it is disclosed.

Note that the details of the problem will only be disclosed to other
SEE licensees and not to the general public.

-- 
D. Richard Hipp
drh at sqlite.org

Re: [sqlite] Encryption of SQLite database using jquery

[Richard Hipp]

On Mon, Sep 22, 2014 at 3:00 PM, Prava Kafle <pka...@mapcom.com> wrote:

> Hi,
>
> I have been looking for a product that can encrypt Sqlite database using
> JavaScript  and came across Sqlite Encryption  extension.
> Can I use this with my application that uses jquery mobile and
> sqlitedatabase? Before purchasing the product, if I could get some
> information on it or a trial copy of it, I would really appreciate it.
>

I have been in private communication with your VP of Technology (Mr.
Spiller) and have worked this out with him last night.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Encryption of SQLite database using jquery

[Prava Kafle]

Hi,

I have been looking for a product that can encrypt Sqlite database using 
JavaScript  and came across Sqlite Encryption  extension.
Can I use this with my application that uses jquery mobile and sqlitedatabase? 
Before purchasing the product, if I could get some information on it or a trial 
copy of it, I would really appreciate it.

Thanks,
Prava Kafle
Mapcom Systems

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Whats the SQLite Encryption Algo?

[Simon Slavin]

On 8 Sep 2014, at 9:16am, Tom Ashley  wrote:

> I noticed that in C# I can set a password using SetPassword. Does this
> encrypt the entire database? If so, What algorithm precisely does it use?

See this



and get back to us if you have questions.  The basic answer is that you have to 
pay extra to be able to encrypt your databases.  But if anyone has encrypted 
their database you don't have to pay extra for the bit that does decryption.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Whats the SQLite Encryption Algo?

[Tom Ashley]

I noticed that in C# I can set a password using SetPassword. Does this
encrypt the entire database? If so, What algorithm precisely does it use?

Thanks
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Encryption

[Raheel Gupta]

Yes, I did test SQLCipher and it slows down a bit.
Now, I would like to go with SEE if its available for the latest version.
SQLCipher is available for 3.8.0.2 while 3.8.1 is out.


On Thu, Dec 5, 2013 at 9:34 PM, Simon Slavin  wrote:

>
> On 5 Dec 2013, at 3:02pm, Raheel Gupta  wrote:
>
> > The only problem is that I dont have access to SEE by Dr. Richard. So how
> > should I test it ?
>
> Why should you test it ?  Have you tested the solutions you do have access
> to and found that they make your app too slow to use ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Encryption

[Raheel Gupta]

Hi,

The only problem is that I dont have access to SEE by Dr. Richard. So how
should I test it ? Does he give test licenses ? Also I am assuming it will
always be supported by the latest version of SQLite right ?


On Thu, Dec 5, 2013 at 7:09 PM, Simon Slavin  wrote:

>
> On 5 Dec 2013, at 9:15am, Raheel Gupta  wrote:
>
> > Yes, I agree. But it should not make the inserts and read too slow as
> well.
>
> The key word here is 'too'.  If there's only 5% difference in speed
> between the two systems then it doesn't matter which one you use.
>
> So you have to write your application using one system, try it out on the
> kind of hardware you expect it to be used on, then figure out if one or
> both systems are 'too slow' for your specific use.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Encryption

[Simon Slavin]

On 5 Dec 2013, at 9:15am, Raheel Gupta  wrote:

> Yes, I agree. But it should not make the inserts and read too slow as well.

The key word here is 'too'.  If there's only 5% difference in speed between the 
two systems then it doesn't matter which one you use.

So you have to write your application using one system, try it out on the kind 
of hardware you expect it to be used on, then figure out if one or both systems 
are 'too slow' for your specific use.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Encryption

[Raheel Gupta]

Yes, I agree. But it should not make the inserts and read too slow as well.
I just want to support 256 Bit AES.


On Thu, Dec 5, 2013 at 1:50 PM, Klaas V  wrote:

> When you'use the word 'perfornance' you might be interested not jonly in
> speed, but in strength of protection, privacy of the employees, your
> company as a whole and above all your clients.
> One of the goals of encryption is to avoid e.g. the government(s and the
> spies they hired to peek into your own business and with this behavior
> those of - again - your clients, their clients, etcetera.
>
> There is more in the world than speed and size.
>
> On Wed, Dec 4, 2013 at 6:18 PM, Simon Slavin  wrote:
>
>
> On 4 Dec 2013, at 10:45am, Raheel Gupta  wrote:
>
>
> I wanted to know which is the best in performance ?
>
>
> Season's greetings and cheers.
> Klaas `Z4us` V
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Encryption

[Klaas V]

When you'use the word 'perfornance' you might be interested not jonly in speed, 
but in strength of protection, privacy of the employees, your company as a 
whole and above all your clients.
One of the goals of encryption is to avoid e.g. the government(s and the spies 
they hired to peek into your own business and with this behavior those of - 
again - your clients, their clients, etcetera.

There is more in the world than speed and size.

On Wed, Dec 4, 2013 at 6:18 PM, Simon Slavin  wrote:

> 
> On 4 Dec 2013, at 10:45am, Raheel Gupta  wrote:
> 
>> I wanted to know which is the best in performance ?

> 

Season's greetings and cheers.
Klaas `Z4us` V


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Encryption

[Raheel Gupta]

>> The answer may depend on what storage system you were using

7200 RPM Sata Drives

>> whether you have small or big databases,

Big which may even reach TBs of data.

>> and whether you have just one user at a time or many users at once.
Multi user. I do use WAL mode.



On Wed, Dec 4, 2013 at 6:18 PM, Simon Slavin  wrote:

>
> On 4 Dec 2013, at 10:45am, Raheel Gupta  wrote:
>
> > I wanted to know which is the best in performance ?
>
> There would probably not be a simple definite answer that applied to all
> setups.  The answer may depend on what storage system you were using,
> whether you have small or big databases, and whether you have just one user
> at a time or many users at once.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Encryption

[Raheel Gupta]

Hi,

I wanted to implement Encryption on my sqlite DB with least load on
performance.
I have come across Sqlcipher.net and SQLite Encryption Extension (By Dr.
Hipp)

I wanted to know which is the best in performance ? Has anyone tested both
of these and can provide some feedback ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A few questions about SQLite Encryption Extension

[Sqlite Dog]

OK. Thank you.


2013/7/19 Richard Hipp 

> On Fri, Jul 19, 2013 at 9:20 AM, Sqlite Dog  wrote:
>
> > >
> > > There is no way to detect which encryption algorithm is used.  Indeed,
> > the
> > > encryption is so thorough that there is no way to tell whether or not
> the
> > > file you are trying to open is an encrypted database file or just a
> file
> > of
> > > white noise.
> > >
> >
> >
> > > The default algorithm is the fastest algorithm (AES-128).  I suggest
> you
> > > stick to that one algorithm unless you have a compelling reason to use
> > > another.  That way, you never need to worry which algorithm is being
> > used.
> > >
> >
> >
> > Suppose there are two databases, one is RC-4 encrypted and the other is
> > AES-256 encrypted.
> > What happens on open? SEE will use default algorithm and fail? Or it will
> > try all algorithms in cycle?
> >
>
> It will use the default algorithm and succeed.  But then later when you try
> to query the database you'll get back an SQLITE_CORRUPT error.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Regards,
SqliteDog support team
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A few questions about SQLite Encryption Extension

[Sqlite Dog]

2013/7/19 RSmith 

> Being a Delphi Developer (mostly) and using SQLite quite often, I can tell
> you some things about it:
>
> You can easily wrap an SQLite DLL in Delphi, there are many free
> implementations of this, if you dont have one, send me a mail and I will
> supply one. SEE will work as easily and I don't know of any restriction
> about it being used as a DLL, excepting to disguise it for obvious reasons.
> (not least among which, the security of your own system).
>

We already have a wrapper on SQLite.dll (otherwise we won't be able to
develop a SQLite database manager :) It's a .pas file with function calls
declared like:

...
function sqlite3_open_v2(const Filename: PAnsiChar; var Base: PSQLite3;
Flags: Integer; const Vfs: PAnsiChar = nil): Integer; cdecl; external
'sqlite3.dll';
...

It just wasn't clear how can we use SEE in this case.


>
> Further to this, DISQLite works really well as a native implementation in
> specifically Delphi, and will work very well for your purpose. It is
> however commercial so there is a cost consideration. (Yes it has a
> free-for-personal-use option, but any form of commercial application needs
> the commercial version). The only possible disadvantage I can level at it
> is that to use new adaptations of the SQLite system requires a recompile
> and update as opposed to just shipping another dll - but then if the dll
> supports new API calls, much the same is true. If security is a concern and
> it is a wholly Delphi-integrated product, I would suggest going with
> DISQLite if feasible.
>

I'm unsure about advantages of using DISQLite vs calling directly
SQLite.dll.

The main advantage is statically linking but we'll try to manage it via
compiling amalgamation to .obj and linking.

As for performance. There can't be a significant difference between these
solutions, in my opinion. 99% of the time will be spent inside SQLite.

Thanks, anyway. We'll give it a try.



>
> Lastly, it seems that either you wish to use different forms of
> encryptions to further subterfuge or obscure database content, or that you
> expect to be needing to open datasets with hitherto unknown encryptions. if
> it is the former, I can honestly say it is not needed, just use whatever
> you like, most of the supported encryptions have ratings way above military
> specification when using proper keys etc. If it is the latter case, you
> can't open Databases of which you don't know exactly the encryption and
> keys... there is no way to do this, else the whole encryption racket would
> be rather useless.


I guess you're right.



>
>
>
>
> On 2013/07/19 15:52, Ralf Junker wrote:
>
>> On 19.07.2013 15:27, Sqlite Dog wrote:
>>
>>  * Statically link SQLite to your Delphi application. My DISQLite3
enables you to do just that and has numerous extensions: One is a
custom encryption algorithm. This is not compatible with SEE,
but if you like I can replace it with your original SEE code for you.


 http://www.yunqa.de/delphi/**doku.php/products/sqlite3/**index

>>> Is it a pascal wrapper around SQLite or something bigger?
>>>
>> DISQLite3 is the only Delphi product which includes the _complete_
>> SQLite API, AFAIK. Using register calling conventions and the Delphi
>> memory manager, DISQLite3 surprised many users to perform noticeably
>> faster than other implementations. Features include:
>>
> etc...
>
>
>
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>



-- 
Regards,
SqliteDog support team
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A few questions about SQLite Encryption Extension

[RSmith]

Being a Delphi Developer (mostly) and using SQLite quite often, I can tell you 
some things about it:

You can easily wrap an SQLite DLL in Delphi, there are many free implementations of this, if you dont have one, send me a mail and I 
will supply one. SEE will work as easily and I don't know of any restriction about it being used as a DLL, excepting to disguise it 
for obvious reasons. (not least among which, the security of your own system).


Further to this, DISQLite works really well as a native implementation in specifically Delphi, and will work very well for your 
purpose. It is however commercial so there is a cost consideration. (Yes it has a free-for-personal-use option, but any form of 
commercial application needs the commercial version). The only possible disadvantage I can level at it is that to use new 
adaptations of the SQLite system requires a recompile and update as opposed to just shipping another dll - but then if the dll 
supports new API calls, much the same is true. If security is a concern and it is a wholly Delphi-integrated product, I would 
suggest going with DISQLite if feasible.


Lastly, it seems that either you wish to use different forms of encryptions to further subterfuge or obscure database content, or 
that you expect to be needing to open datasets with hitherto unknown encryptions. if it is the former, I can honestly say it is not 
needed, just use whatever you like, most of the supported encryptions have ratings way above military specification when using 
proper keys etc. If it is the latter case, you can't open Databases of which you don't know exactly the encryption and keys... there 
is no way to do this, else the whole encryption racket would be rather useless.




On 2013/07/19 15:52, Ralf Junker wrote:

On 19.07.2013 15:27, Sqlite Dog wrote:


* Statically link SQLite to your Delphi application. My DISQLite3
   enables you to do just that and has numerous extensions: One is a
   custom encryption algorithm. This is not compatible with SEE,
   but if you like I can replace it with your original SEE code for you.

   http://www.yunqa.de/delphi/doku.php/products/sqlite3/index

Is it a pascal wrapper around SQLite or something bigger?

DISQLite3 is the only Delphi product which includes the _complete_
SQLite API, AFAIK. Using register calling conventions and the Delphi
memory manager, DISQLite3 surprised many users to perform noticeably
faster than other implementations. Features include:

etc...



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A few questions about SQLite Encryption Extension

[Ralf Junker]

On 19.07.2013 15:27, Sqlite Dog wrote:

>> * Statically link SQLite to your Delphi application. My DISQLite3
>>   enables you to do just that and has numerous extensions: One is a
>>   custom encryption algorithm. This is not compatible with SEE,
>>   but if you like I can replace it with your original SEE code for you.
>> 
>>   http://www.yunqa.de/delphi/doku.php/products/sqlite3/index
> 
> Is it a pascal wrapper around SQLite or something bigger?

DISQLite3 is the only Delphi product which includes the _complete_
SQLite API, AFAIK. Using register calling conventions and the Delphi
memory manager, DISQLite3 surprised many users to perform noticeably
faster than other implementations. Features include:

* Complete SQLite API.

* Supports Win32 and Win64.

* Delphi class wrapper.

* TDataSet descendant.

* Delphi smart linking for smallest possible binaries.

* Full Text Search (FTS) with customizable tokenizer, prefix matching,
and optional word stemming for 15 languages.

* Custom encryption (not SEE compatible).

* Async IO Backend.

* SQLite virtual table extensions: rtree, spellfix, fuzzer, closure,
wholenumber, amatch.

* SQLite SQL function extensions: ieee754, nextchar.

* Extensive documentation and lots of demo projects.

Ralf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A few questions about SQLite Encryption Extension

[Richard Hipp]

On Fri, Jul 19, 2013 at 9:20 AM, Sqlite Dog  wrote:

> >
> > There is no way to detect which encryption algorithm is used.  Indeed,
> the
> > encryption is so thorough that there is no way to tell whether or not the
> > file you are trying to open is an encrypted database file or just a file
> of
> > white noise.
> >
>
>
> > The default algorithm is the fastest algorithm (AES-128).  I suggest you
> > stick to that one algorithm unless you have a compelling reason to use
> > another.  That way, you never need to worry which algorithm is being
> used.
> >
>
>
> Suppose there are two databases, one is RC-4 encrypted and the other is
> AES-256 encrypted.
> What happens on open? SEE will use default algorithm and fail? Or it will
> try all algorithms in cycle?
>

It will use the default algorithm and succeed.  But then later when you try
to query the database you'll get back an SQLITE_CORRUPT error.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A few questions about SQLite Encryption Extension

[Sqlite Dog]

2013/7/19 Ralf Junker 

> On 19.07.2013 09:21, Sqlite Dog wrote:
>
> > Our database manager is developed using Delphi (Pascal). Thus it is
> > not possible to statically link SQLite library, SQLite.dll is used.
> > Is there some other way to support SEE in our project?
>
> You have two options:
>
> * Create your own sqlite.dll and compile SEE into it.
>

The question was - is it against the rules or not.


>
> * Statically link SQLite to your Delphi application. My DISQLite3
>   enables you to do just that and has numerous extensions: One is a
>   custom encryption algorithm. This is not compatible with SEE,
>   but if you like I can replace it with your original SEE code for you.
>
>   http://www.yunqa.de/delphi/doku.php/products/sqlite3/index


Is it a pascal wrapper around SQLite or something bigger?



>
>
> Ralf
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Regards,
SqliteDog support team
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A few questions about SQLite Encryption Extension

[Sqlite Dog]

2013/7/19 Richard Hipp 

> On Fri, Jul 19, 2013 at 3:21 AM, Sqlite Dog  wrote:
>
> >
> > Our database manager is developed using Delphi (Pascal). Thus it is not
> > possible to statically link SQLite library, SQLite.dll is used. Is there
> > some other way to support SEE in our project?
> >
>
> Yes.  You can ship it as a DLL and use a special pragma to enable the
> encryption feature.  We ask that you rename the DLL to something that does
> not contain the words "SQLite" or "SEE" or anything similar, to disguise
> its purpose.
>
>
OK


>
>
> >
> > SEE supports several various encryption algorithms. If a database is
> > already encrypted does it automatically choose needed algorithm? How to
> > specify algorithm for a new database if a version of SEE is used which
> > supports all of them? Or is it always using most "powerful" one
> (AES-256)?
> >
>
> There is no way to detect which encryption algorithm is used.  Indeed, the
> encryption is so thorough that there is no way to tell whether or not the
> file you are trying to open is an encrypted database file or just a file of
> white noise.
>


> The default algorithm is the fastest algorithm (AES-128).  I suggest you
> stick to that one algorithm unless you have a compelling reason to use
> another.  That way, you never need to worry which algorithm is being used.
>


Suppose there are two databases, one is RC-4 encrypted and the other is
AES-256 encrypted.
What happens on open? SEE will use default algorithm and fail? Or it will
try all algorithms in cycle?





> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Regards,
SqliteDog support team
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A few questions about SQLite Encryption Extension

[kyan]

On Fri, Jul 19, 2013 at 3:49 PM, Sqlite Dog  wrote:

> That's interesting. What about pas file with function declarations to
> SQLite API? Should it be modified somehow?
>
>
If you use static dll loading and linking, Instead of declaring API
functions as external 'sqlite3.dll' you declare them as plain external. The
linker will automatically link to their implementation from the object file
sqlite3.obj by name.

If you have dynamic dll loading then you must have declared them as
function prototypes and assign them by hand using GetProcAddress() after
calling LoadLibrary() for SqLite3.dll. In this case the sqlite header API
file will require more modifications. For instance this:

var
  sqlite3_open: function(filename: PUTF8Char; var DB: Pointer): integer;
cdecl;

will have to be changed to this:

function sqlite3_open(filename: PUTF8Char; var DB: Pointer): integer;
cdecl; external;

but if the names of the function pointer variables are as in the
amalgamation then every unit that uses the API unit and calls its functions
will not need any modifications. For example the following code:

  Rslt := sqlite3_open(FileName, db);

compiles with both declarations.

HTH.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A few questions about SQLite Encryption Extension

[Richard Hipp]

On Fri, Jul 19, 2013 at 8:49 AM, Sqlite Dog <sqlite...@gmail.com> wrote:

> That's interesting. What about pas file with function declarations to
> SQLite API? Should it be modified somehow?
>

No need to.  The SQLite Encryption Extension is controlled using PRAGMA
statements.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A few questions about SQLite Encryption Extension

[Sqlite Dog]

That's interesting. What about pas file with function declarations to
SQLite API? Should it be modified somehow?


2013/7/19 kyan 

> On Fri, Jul 19, 2013 at 10:21 AM, Sqlite Dog  wrote:
>
> >
> > Our database manager is developed using Delphi (Pascal). Thus it is not
> > possible to statically link SQLite library, SQLite.dll is used. Is there
> > some other way to support SEE in our project?
> >
>
> It is possible to compile the SQLite amalgamation with Embarcadero CBuilder
> and statically link the object file in a Delphi program using the $L
> directive; I have
> successfully
> done this
> with Delphi XE
> . So
> -although I haven't tried it-
> I suppose it is possible to do the same with SEE since it is open-source.
>
> --
> Constantine Yannakopoulos
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Regards,
SqliteDog support team
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A few questions about SQLite Encryption Extension

[Ralf Junker]

On 19.07.2013 09:21, Sqlite Dog wrote:

> Our database manager is developed using Delphi (Pascal). Thus it is 
> not possible to statically link SQLite library, SQLite.dll is used. 
> Is there some other way to support SEE in our project?

You have two options:

* Create your own sqlite.dll and compile SEE into it.

* Statically link SQLite to your Delphi application. My DISQLite3
  enables you to do just that and has numerous extensions: One is a
  custom encryption algorithm. This is not compatible with SEE,
  but if you like I can replace it with your original SEE code for you.

  http://www.yunqa.de/delphi/doku.php/products/sqlite3/index

Ralf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A few questions about SQLite Encryption Extension

[Richard Hipp]

On Fri, Jul 19, 2013 at 3:21 AM, Sqlite Dog  wrote:

>
> Our database manager is developed using Delphi (Pascal). Thus it is not
> possible to statically link SQLite library, SQLite.dll is used. Is there
> some other way to support SEE in our project?
>

Yes.  You can ship it as a DLL and use a special pragma to enable the
encryption feature.  We ask that you rename the DLL to something that does
not contain the words "SQLite" or "SEE" or anything similar, to disguise
its purpose.



>
> SEE supports several various encryption algorithms. If a database is
> already encrypted does it automatically choose needed algorithm? How to
> specify algorithm for a new database if a version of SEE is used which
> supports all of them? Or is it always using most "powerful" one (AES-256)?
>

There is no way to detect which encryption algorithm is used.  Indeed, the
encryption is so thorough that there is no way to tell whether or not the
file you are trying to open is an encrypted database file or just a file of
white noise.

The default algorithm is the fastest algorithm (AES-128).  I suggest you
stick to that one algorithm unless you have a compelling reason to use
another.  That way, you never need to worry which algorithm is being used.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A few questions about SQLite Encryption Extension

[kyan]

On Fri, Jul 19, 2013 at 10:21 AM, Sqlite Dog  wrote:

>
> Our database manager is developed using Delphi (Pascal). Thus it is not
> possible to statically link SQLite library, SQLite.dll is used. Is there
> some other way to support SEE in our project?
>

It is possible to compile the SQLite amalgamation with Embarcadero CBuilder
and statically link the object file in a Delphi program using the $L
directive; I have
successfully
done this
with Delphi XE
. So
-although I haven't tried it-
I suppose it is possible to do the same with SEE since it is open-source.

--
Constantine Yannakopoulos
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] A few questions about SQLite Encryption Extension

[Sqlite Dog]

Hi,

documentation on SEE (http://www.hwaci.com/sw/sqlite/see.html) states that

"You can ship as many compiled, binary copies of SQLite with your
commercial product as long as each copy is attached to your product in such
a way that it cannot be separated from your product. Normally this means
that you should statically link SEE with your product, thought exceptions
to this rule can be made as circumstances require."

Our database manager is developed using Delphi (Pascal). Thus it is not
possible to statically link SQLite library, SQLite.dll is used. Is there
some other way to support SEE in our project?

SEE supports several various encryption algorithms. If a database is
already encrypted does it automatically choose needed algorithm? How to
specify algorithm for a new database if a version of SEE is used which
supports all of them? Or is it always using most "powerful" one (AES-256)?

Thank you.

--
Regards,
SqliteDog support team
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Encryption in Windows 8 Metro App

[Simon Slavin]

On 23 Apr 2013, at 2:11pm, "Jay, Jayavasanthan John"  
wrote:

> Or, is there any other package which doesn't have these dependencies for
> encrypting and decrypting SQLite databases.

I cannot answer your questions about SQLCipher, but the main authors of SQLite 
produce two encryption systems for SQLite:





SEE does live encryption and decryption.  CEROD is for cases where you want to 
distribute a large encrypted compressed read-only database.  Both of them are 
complete in their C source code: neither have any dependencies on anything 
external apart from SQLite itself.  The $2,000 fee is basically permission to 
use all versions of the product forever, with good sample code and support 
included.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite Encryption in Windows 8 Metro App

[Jay, Jayavasanthan John]

Hi Guys,

I tried out SQLCipher and have successfully integrated in testing with
SQLite for encrypting and decrypting my database for a Windows 8 Metro App.

But as per Windows 8 Metro App compatibility guidelines, openssl (which is
the crypto package used by SQLCipher) fails on the usage of libraries such
as advapi32.lib, gdi32.lib and user32.lib.

So, question, can this be resolved by continuing to use SQLCipher without
openssl, but a different crypto package,

Or, is there any other package which doesn't have these dependencies for
encrypting and decrypting SQLite databases.

PS: The encryption/decryption is only done for the content database of the
app and not the user database.

Thanks in advance to the community for the help and guidance they may be
able to provide me for this,

Best Regards,
Jay

Jayavasanthan J
Skype: jai_vasanthan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Encryption using System.Data.SQLite.dll

[Bishwa Shrestha]

On 08/20/2012 11:08 AM, Bishwa Shrestha wrote:

Hi,

I am creating an sqlite3 interface to an existing data model. For that 
purpose sqlite3 tables are dynamically loaded. I'm using 
/sqlite3_column_count/ and /sqlite3_column_table_name/ to load data 
into relevant tables only.


Now, I am having problems with queries such as:

SELECT a.column1 FROM table1 AS a, table2 AS b;

however this works:
SELECT a.column1, b.column1 FROM table1 AS a, table2 AS b;

because /sqlite3_column_table_name /only returns columns in the result 
set.


Is there a way to get the table names that were referenced in the 
query itself?


Thanks in advance,
bishwa

Sorry,  wrong thread. I'll create a new one.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Encryption using System.Data.SQLite.dll

[Bishwa Shrestha]

Hi,

I am creating an sqlite3 interface to an existing data model. For that 
purpose sqlite3 tables are dynamically loaded. I'm using 
/sqlite3_column_count/ and /sqlite3_column_table_name/ to load data into 
relevant tables only.


Now, I am having problems with queries such as:

SELECT a.column1 FROM table1 AS a, table2 AS b;

however this works:
SELECT a.column1, b.column1 FROM table1 AS a, table2 AS b;

because /sqlite3_column_table_name /only returns columns in the result set.

Is there a way to get the table names that were referenced in the query 
itself?


Thanks in advance,
bishwa
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Encryption using System.Data.SQLite.dll

[Richard Hipp]

On Fri, Aug 17, 2012 at 1:20 PM, John Grasmeder
<jgrasme...@ves-network.com>wrote:

> I agree, I am trying to find a way of getting the System.Data.SQLite.dll
> to use AES-128 in CCM mode which is the type used by Adobe AIR.
>

The only way to do that is to purchase an SEE license, giving you access to
the SEE source code, then recompile System.Data.SQLite.dll using the SEE
version of "sqlite3.c".


> Currently all I can find is that it uses "Encrypted databases are fully
> encrypted and support both binary and cleartext password types" from
> http://system.data.sqlite.org/index.html/doc/trunk/www/features.wiki.
>
> I have downloaded the fossil application, cloned the repository and
> opened a full source tree from sds.fossil but I am having no luck
> finding anything about the encryption used on SQLite databases. :(
>
>
>  John G.
>
> > -Original Message-
> > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > boun...@sqlite.org] On Behalf Of Richard Hipp
> > Sent: Friday, August 17, 2012 12:09 PM
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] Encryption using System.Data.SQLite.dll
> >
> > On Fri, Aug 17, 2012 at 11:58 AM, Carlos Milon Silva
> > <mi...@terra.com.br>wrote:
> >
> > > John,
> > >
> > > The internal Adobe Air SQLite is built to not accept extensions,
> > >
> >
> > Yes, but Adobe Air comes with the SQLite Encryption Extension (SEE)
> > built in.  There is nothing that needs to be extended with Adobe Air.
> >
> >
> >
> > > so, either the System.Data.SQLite.dll or the SQLite Encryption
> > > Extension from hwaci could only be used with a an external native
> > > extension application.
> > >
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Encryption using System.Data.SQLite.dll

[John Grasmeder]

I agree, I am trying to find a way of getting the System.Data.SQLite.dll
to use AES-128 in CCM mode which is the type used by Adobe AIR.
Currently all I can find is that it uses "Encrypted databases are fully
encrypted and support both binary and cleartext password types" from
http://system.data.sqlite.org/index.html/doc/trunk/www/features.wiki.

I have downloaded the fossil application, cloned the repository and
opened a full source tree from sds.fossil but I am having no luck
finding anything about the encryption used on SQLite databases. :(


 John G.

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Richard Hipp
> Sent: Friday, August 17, 2012 12:09 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Encryption using System.Data.SQLite.dll
> 
> On Fri, Aug 17, 2012 at 11:58 AM, Carlos Milon Silva
> <mi...@terra.com.br>wrote:
> 
> > John,
> >
> > The internal Adobe Air SQLite is built to not accept extensions,
> >
> 
> Yes, but Adobe Air comes with the SQLite Encryption Extension (SEE)
> built in.  There is nothing that needs to be extended with Adobe Air.
> 
> 
> 
> > so, either the System.Data.SQLite.dll or the SQLite Encryption
> > Extension from hwaci could only be used with a an external native
> > extension application.
> >
> 
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Encryption using System.Data.SQLite.dll

[Carlos Milon Silva]

John,

The internal Adobe Air SQLite is built to not accept extensions,
so, either the System.Data.SQLite.dll or the SQLite Encryption Extension 
from hwaci

could only be used with a an external native extension application.

Carlos.

Em 17/08/2012 09:48, John Grasmeder escreveu:

Carlos,

My statements must have misled you, Adobe Air already contains an
implementation of SQLite. I am currently using it with several 'tool'
applications along with my client application that my customer will use
which all process encrypted databases the same way. I am hoping not to
get into a discussion with the 'sqlite-users' list of the merits of who
is implementing database encryption correctly or incorrectly. It doesn't
look like I can change the methods used from the Adobe side of things so
I was hoping that the System.Data.SQLite.dll group allowed some way of
overriding the encryption used on the database file.

Joe Mistachkin has also replied with a reference to SQLite Encryption
Extension (http://www.hwaci.com/sw/sqlite/see.html) which I will look
into today and see if it can be added to my Dotnet code.



Thanks,

John G.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
boun...@sqlite.org] On Behalf Of Carlos Milon Silva
Sent: Thursday, August 16, 2012 06:01 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Encryption using System.Data.SQLite.dll

if you really need to use System.Data.SQLite.dll, from Adobe Air, then
you need to follow something like to:

http://blogs.adobe.com/globalization/invoking-icu-from-adobe-air-
applications-part-2-using-flash-builder-4-6/

and call System.Data.SQLite.dll as an external native extension

Carlos.





___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Encryption using System.Data.SQLite.dll

[John Grasmeder]

Carlos,

My statements must have misled you, Adobe Air already contains an
implementation of SQLite. I am currently using it with several 'tool'
applications along with my client application that my customer will use
which all process encrypted databases the same way. I am hoping not to
get into a discussion with the 'sqlite-users' list of the merits of who
is implementing database encryption correctly or incorrectly. It doesn't
look like I can change the methods used from the Adobe side of things so
I was hoping that the System.Data.SQLite.dll group allowed some way of
overriding the encryption used on the database file.

Joe Mistachkin has also replied with a reference to SQLite Encryption
Extension (http://www.hwaci.com/sw/sqlite/see.html) which I will look
into today and see if it can be added to my Dotnet code.



Thanks,

John G.
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Carlos Milon Silva
> Sent: Thursday, August 16, 2012 06:01 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Encryption using System.Data.SQLite.dll
> 
> if you really need to use System.Data.SQLite.dll, from Adobe Air, then
> you need to follow something like to:
> 
> http://blogs.adobe.com/globalization/invoking-icu-from-adobe-air-
> applications-part-2-using-flash-builder-4-6/
> 
> and call System.Data.SQLite.dll as an external native extension
> 
> Carlos.
> 
> Em 16/08/2012 16:17, John Grasmeder escreveu:
> > Hello List,
> >
> >
> >
> > I am currently using System.Data.SQLite.dll version 1.0.79.0. I am
> > creating  a SQLite database file from data stored in MS-SQL which
> will
> > be pushed to client applications running an Adobe Air application on
> > Macs and Windows PCs.  Everything is progressing nicely but I am now
> > at the step where I need to 'Lock up' the SQLite database from
prying
> eyes.
> >
> >
> >
> >
> > How do I go about changing the encryption method/methods that are
> used
> > in the SQLite.SQLiteConnection class? Do I need to implement my own
> > version of SQLiteConnection and override some methods or is there
> some
> > other code I have not found as of date?
> >
> >
> >
> > Creating both Encrypted and non Encrypted databases on both sides
> > (client Adobe ActionScript/server don.net framework) and being able
> to
> > read them from the side that created them leads me to believe the
> > Encryption methods are not the same.
> >
> >
> >
> > If this knowledge is available on the sqlite.org web site could
> > someone please point me in the right direction.
> >
> >
> >
> > Thanks,
> >
> >
> >
> > John G.
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Encryption using System.Data.SQLite.dll

[Carlos Milon Silva]

if you really need to use System.Data.SQLite.dll, from Adobe Air,
then you need to follow something like to:

http://blogs.adobe.com/globalization/invoking-icu-from-adobe-air-applications-part-2-using-flash-builder-4-6/

and call System.Data.SQLite.dll as an external native extension

Carlos.

Em 16/08/2012 16:17, John Grasmeder escreveu:

Hello List,

  


I am currently using System.Data.SQLite.dll version 1.0.79.0. I am
creating  a SQLite database file from data stored in MS-SQL which will
be pushed to client applications running an Adobe Air application on
Macs and Windows PCs.  Everything is progressing nicely but I am now at
the step where I need to 'Lock up' the SQLite database from prying eyes.


  


How do I go about changing the encryption method/methods that are used
in the SQLite.SQLiteConnection class? Do I need to implement my own
version of SQLiteConnection and override some methods or is there some
other code I have not found as of date?

  


Creating both Encrypted and non Encrypted databases on both sides
(client Adobe ActionScript/server don.net framework) and being able to
read them from the side that created them leads me to believe the
Encryption methods are not the same.

  


If this knowledge is available on the sqlite.org web site could someone
please point me in the right direction.

  


Thanks,

  


John G.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Encryption using System.Data.SQLite.dll

[Joe Mistachkin]

John Grasmeder wrote:
> 
> How do I go about changing the encryption method/methods that are used
> in the SQLite.SQLiteConnection class? Do I need to implement my own
> version of SQLiteConnection and override some methods or is there some
> other code I have not found as of date?
> 

The encryption functionality provided by System.Data.SQLite is written in
native code and contained in the interop assembly.

> 
> Creating both Encrypted and non Encrypted databases on both sides
> (client Adobe ActionScript/server don.net framework) and being able to
> read them from the side that created them leads me to believe the
> Encryption methods are not the same.
> 

Right, they are not the same.  The encryption in System.Data.SQLite uses
the Win32 CryptoAPI, which is not used by the standard, cross-platform
encryption module.

> 
> If this knowledge is available on the sqlite.org web site could someone
> please point me in the right direction.
> 

Information on the standard encryption module can be found here:

http://www.hwaci.com/sw/sqlite/see.html

--
Joe Mistachkin

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Encryption using System.Data.SQLite.dll

[John Grasmeder]

Hello List,

 

I am currently using System.Data.SQLite.dll version 1.0.79.0. I am
creating  a SQLite database file from data stored in MS-SQL which will
be pushed to client applications running an Adobe Air application on
Macs and Windows PCs.  Everything is progressing nicely but I am now at
the step where I need to 'Lock up' the SQLite database from prying eyes.


 

How do I go about changing the encryption method/methods that are used
in the SQLite.SQLiteConnection class? Do I need to implement my own
version of SQLiteConnection and override some methods or is there some
other code I have not found as of date?

 

Creating both Encrypted and non Encrypted databases on both sides
(client Adobe ActionScript/server don.net framework) and being able to
read them from the side that created them leads me to believe the
Encryption methods are not the same.

 

If this knowledge is available on the sqlite.org web site could someone
please point me in the right direction.

 

Thanks,

 

John G.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Encryption Extension Performance?

[Adolfo J . Millan]

>
>  Mensaje original 
> De: Simon Slavin <slav...@bigfraud.org>
> Para:  a...@zator.com,General Discussion of SQLite Database 
> <sqlite-users@sqlite.org>
> Fecha:  Thu, 28 Jun 2012 15:43:26 +0200
> Asunto:  Re: [sqlite] SQLite Encryption Extension Performance?
>
> 
>
>
>On 28 Jun 2012, at 12:12pm, ajm@zatorcom wrote:
>

>I hope Richard's answer tells you what you want to know.  He runs the
>team that makes SEE.
>

Of course I know, DRH is the pope in this matter :-)

>Just in case you're actually looking for this because you're used to it
>with other DBMSen, I wondered whether you might be asking not about
>encrypted storage on disk but about the communications between your app
>and the database server.  Having these communications encrypted is a
>requirement for some installations and I'm used to being asked this
>question.
>

No I,m using SQLite from some years ago, and mine is just a desktop app. Any 
way thanks for your advice.

I must think about the solution proposed in the DRH response.

--
Adolfo


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Encryption Extension Performance?

[Simon Slavin]

On 28 Jun 2012, at 12:12pm, a...@zator.com wrote:

> Assuming using SEE, do is there the ability to decide what I/O operations are 
> to be performed encrypted or with the plain (as is) content?

I hope Richard's answer tells you what you want to know.  He runs the team that 
makes SEE.

Just in case you're actually looking for this because you're used to it with 
other DBMSen, I wondered whether you might be asking not about encrypted 
storage on disk but about the communications between your app and the database 
server.  Having these communications encrypted is a requirement for some 
installations and I'm used to being asked this question.

SQLite does not have a client/server architecture or anything like it.  Unless 
you make special provision for it, all access to the data is done inside the 
process of your application which asks for the operation.  Any communication 
between your app and a database server or service takes place inside your CPU.  
The only server-related concern would be if you are accessing a database file 
using file-sharing over a network.  In that case, if you are using SEE and the 
database is encrypted, any data passing over your network will be encrypted at 
the page level, which makes it difficult even to identify database records and 
fields, let alone what was requested or changed by an operation.  It's as 
secure as AES-256 (or whatever you're using) can be.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Encryption Extension Performance?

[Richard Hipp]

On Thu, Jun 28, 2012 at 7:12 AM, <a...@zator.com> wrote:

> Hi:
>
> Assuming using SEE, do is there the ability to decide what I/O operations
> are to be performed encrypted or with the plain (as is) content?
>

Encryption is turned on and off at the database level.  So either an entire
database file is completely encrypted or none of it is.  You cannot encrypt
some rows and leave others plaintext.

But you can use the ATTACH <http://www.sqlite.org/lang_attach.html> command
to connect two or more database files to your database connection at the
same time, so that they look to your application as if they are a single
file, and some subset of those files can be encrypted while another subset
is plaintext.  So if you can arrange your schema such that encrypted
content goes in one set of tables, and you put those tables in one database
file, and your plaintext data goes in a separate set of tables and those
tables go in a different database file, then you can have some content
encrypted and other content not encrypted.


>
> I'm planning an application with only selected data encripted (in each row
> of certain tables, there is a flag showing the encrypted/plain state).
>
> Thanks in advance.
>
> >
> >  Mensaje original 
> > De: Richard Hipp <d...@sqlite.org>
> > Para:  Paul Vercellotti <pverce...@yahoo.com>, General Discussion of
> SQLite Database <sqlite-users@sqlite.org>
> > Fecha:  Wed, 27 Jun 2012 21:28:30 +0200
> > Asunto:  Re: [sqlite] SQLite Encryption Extension Performance?
> >
> >
> >SEE is a drop-in replacement for public-domain SQLite.  In other words, it
> >will read and write ordinary unencrypted database files, and it will do so
> >with no speed penalty.
> >
> >For performance sensitive applications, what developers sometimes do is
> >break up their content into sensitive and non-sensitive, storing each in
> >separate databases, and only encrypt the sensitive content.  SEE is able
> to
> >open both databases at once (using the ATTACH command) and do joins on
> >tables between the two databases, even though only one of the two is
> >encrypted.
> >
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Encryption Extension Performance?

[ajm]

Hi:

Assuming using SEE, do is there the ability to decide what I/O operations are 
to be performed encrypted or with the plain (as is) content?

I'm planning an application with only selected data encripted (in each row of 
certain tables, there is a flag showing the encrypted/plain state).

Thanks in advance.

>
>  Mensaje original 
> De: Richard Hipp <d...@sqlite.org>
> Para:  Paul Vercellotti <pverce...@yahoo.com>, General Discussion of SQLite 
> Database <sqlite-users@sqlite.org>
> Fecha:  Wed, 27 Jun 2012 21:28:30 +0200
> Asunto:  Re: [sqlite] SQLite Encryption Extension Performance?
>
>
>SEE is a drop-in replacement for public-domain SQLite.  In other words, it
>will read and write ordinary unencrypted database files, and it will do so
>with no speed penalty.
>
>For performance sensitive applications, what developers sometimes do is
>break up their content into sensitive and non-sensitive, storing each in
>separate databases, and only encrypt the sensitive content.  SEE is able to
>open both databases at once (using the ATTACH command) and do joins on
>tables between the two databases, even though only one of the two is
>encrypted.
>


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Encryption Extension Performance?

[Richard Hipp]

On Wed, Jun 27, 2012 at 2:55 PM, Paul Vercellotti <pverce...@yahoo.com>wrote:

>
>
> Hi there,
>
> We are considering using the SQLite Encryption Extension in one of our
> products, and are wondering what the performance characteristics of it are?
>   Does the encryption algorithm affect performance?   Any stats on this you
> might have would be useful.
>

SEE is a drop-in replacement for public-domain SQLite.  In other words, it
will read and write ordinary unencrypted database files, and it will do so
with no speed penalty.  If you enable encryption, however, SQLite has to
run your chosen encryption algorithm whenever content is read from disk, or
written to disk.  Whether or not this effects performance, and by how much,
depends heavily on your application (how much it uses the database), which
encryption algorithm you choose, and on the relative speeds of CPU versus
I/O on your target platform.

Your worst-case performance hit is probably going to be about 50%.  In
other words, a query that used to take 100us would now take 150us with AES
128-bit encryption enabled, the extra fifty microseconds being time spend
running the encryption/decryption algorithms.  This worst case is for
queries that have to do actual disk I/O.  Queries out of SQLite's internal
cache use pre-decrypted content and do not slow down at all.  A typical
application will sometimes hit the cache and sometimes go to disk,
resulting in a performance hit somewhere in between.

For performance sensitive applications, what developers sometimes do is
break up their content into sensitive and non-sensitive, storing each in
separate databases, and only encrypt the sensitive content.  SEE is able to
open both databases at once (using the ATTACH command) and do joins on
tables between the two databases, even though only one of the two is
encrypted.



>
> Thanks!
>
> -Paul
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Encryption Extension Performance?

[Etienne]

Hi Paul,

Are you speaking of CEROD?

FYI, I use to link sqlite3.c with the free wxSQLite3 encryption (AES128/256) 
module (http://wxcode.sourceforge.net/components/wxsqlite3).

I didn't notice any significant slowing down (3-4% max.), but of course there 
is no compression involved...

Hope this helps.

Regards,
Etienne


- Original message -
From: Paul Vercellotti <pverce...@yahoo.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Subject: [sqlite] SQLite Encryption Extension Performance?
Date: Wed, 27 Jun 2012 11:55:21 -0700 (PDT)

Hi there,

We are considering using the SQLite Encryption Extension in one of our 
products, and are wondering what the performance characteristics of it are?   
Does the encryption algorithm affect performance?   Any stats on this you might 
have would be useful.

Thanks!

-Paul
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite Encryption Extension Performance?

[Paul Vercellotti]

Hi there,

We are considering using the SQLite Encryption Extension in one of our 
products, and are wondering what the performance characteristics of it are?   
Does the encryption algorithm affect performance?   Any stats on this you might 
have would be useful.

Thanks!

-Paul
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Encryption Extension (SEE) and Public Domain Sqlite

[Black, Michael (IS)]

If you hash the username too you don't have that problem.

Passwords should always be a 1-way hash.

It's actually best to collapse username/password into a single one-way hash.

That way it's very difficult to crack it.

You can use a user "account#" value that users would have to track for support.





Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Simon Slavin [slav...@bigfraud.org]
Sent: Thursday, July 07, 2011 2:16 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] SQLite Encryption Extension (SEE) and Public Domain 
Sqlite


On 7 Jul 2011, at 7:41pm, Prakash Reddy Bande wrote:

> I wanted to get an understanding of SQLite Encryption Extension. I am 
> currently using sqlite-3.7.2, and if I want to use SEE, would it be equally 
> compatible. Well, thehttp://www.hwaci.com/sw/sqlite/see.html page does not 
> give much information, i.e. do still build sqlite from public and inject 
> encryption or it is a parallel code base.

SEE is delivered as C source code the same way SQLite is.  If you currently 
compile SQLite into your application you just remove the SQLite .c and .h 
files, put the SEE ones in their place, and recompile.  You can then ignore the 
extra functions entirely (it'll read unencrypted SQLite databases) or use the 
extra functions by C calls or PRAGMAs.

I'm not familiar with the release schedule for SEE.  I doubt it's far behind 
the current release of SQLite.

> Well, all I want to actually do is store passwords in a column and hence it 
> should be encrypted. Of course, alternatively I can do encryption for that 
> value in my application. But I guess it is a nice option to keep the entire 
> db encrypted so that users cannot explore and accidently mess with it using 
> sqlite CLI.

The SEE set will work very well for that.

The problem with encrypting just columns of a database is that it allows people 
to mess with it.  For example, suppose I want to break into someone's account 
and I can see their username and encrypted password in a row.  I copy the 
contents of my own password field into their password field.  Then I can log 
into their account using my password.

Encrypting the entire database prevents this sort of thing.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Encryption Extension (SEE) and Public Domain Sqlite

[Simon Slavin]

On 7 Jul 2011, at 7:41pm, Prakash Reddy Bande wrote:

> I wanted to get an understanding of SQLite Encryption Extension. I am 
> currently using sqlite-3.7.2, and if I want to use SEE, would it be equally 
> compatible. Well, thehttp://www.hwaci.com/sw/sqlite/see.html page does not 
> give much information, i.e. do still build sqlite from public and inject 
> encryption or it is a parallel code base.

SEE is delivered as C source code the same way SQLite is.  If you currently 
compile SQLite into your application you just remove the SQLite .c and .h 
files, put the SEE ones in their place, and recompile.  You can then ignore the 
extra functions entirely (it'll read unencrypted SQLite databases) or use the 
extra functions by C calls or PRAGMAs.

I'm not familiar with the release schedule for SEE.  I doubt it's far behind 
the current release of SQLite.

> Well, all I want to actually do is store passwords in a column and hence it 
> should be encrypted. Of course, alternatively I can do encryption for that 
> value in my application. But I guess it is a nice option to keep the entire 
> db encrypted so that users cannot explore and accidently mess with it using 
> sqlite CLI.

The SEE set will work very well for that.

The problem with encrypting just columns of a database is that it allows people 
to mess with it.  For example, suppose I want to break into someone's account 
and I can see their username and encrypted password in a row.  I copy the 
contents of my own password field into their password field.  Then I can log 
into their account using my password.

Encrypting the entire database prevents this sort of thing.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite Encryption Extension (SEE) and Public Domain Sqlite

[Prakash Reddy Bande]

Hi,

I wanted to get an understanding of SQLite Encryption Extension. I am currently 
using sqlite-3.7.2, and if I want to use SEE, would it be equally compatible. 
Well, the http://www.hwaci.com/sw/sqlite/see.html page does not give much 
information, i.e. do still build sqlite from public and inject encryption or it 
is a parallel code base.

Well, all I want to actually do is store passwords in a column and hence it 
should be encrypted. Of course, alternatively I can do encryption for that 
value in my application. But I guess it is a nice option to keep the entire db 
encrypted so that users cannot explore and accidently mess with it using sqlite 
CLI.

Regards,

Prakash


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite encryption extension

[Richard Hipp]

On Tue, Jun 7, 2011 at 11:41 AM, NewsGroupSpam  wrote:

> Can anyone comment about whether the SEE is delivered as an amalgamation,
> or
> whether it comes as pre-amalgamation files?
>

SEE is a single file of C code that you append to a standard "sqlite3.c"
amalgamation file and then compile as a single unit.



>
> Thanks
>
> Doug
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite encryption extension

[NewsGroupSpam]

Can anyone comment about whether the SEE is delivered as an amalgamation, or
whether it comes as pre-amalgamation files?

Thanks

Doug

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Question about SQLite Encryption Extension (SEE)

[Daniel Camps Mas]

Hi, I have a question about SQLite Encryption Extension (SEE) add-on.

I am using for develop a Visual Basic .NET v2010 with ADO.NET 2.0 and
*System.Data.SQLite
1.0.72.0* for work with DB.

If I adquire SEE add-on, it works correctly with *System.Data.SQLite
1.0.72.0*.

Thanks for all!

-- 
*Daniel Camps
*d13061...@gmail.com

(+34) 618.631.119


*Abans d'imprimir aquest e-mail pensa bé si és realment necessari.*
**
*Antes de imprimir este e-mail piensa bien si es realmente necesario.*

**

*Avís de Confidencialitat de correu electrònic*
Aquest missatge pot contenir informació privilegiada i / o confidencial. Si
vostè no és el destinatari indicat en aquest missatge (*o el responsable de
fer arribar aquest missatge al destinatari*) no està autoritzat per a
copiar o entregar aquest missatge a cap persona. En aquest cas, haurà de
destruir aquest missatge i se li prega que avisi al destinatari per e-mail. Si
us plau, avisi'ns immediatament si vostè o la seva empresa no admet la
utilització del correu electrònic per Internet per a missatges d'aquest
tipus. *No s'autoritza la utilització amb fins comercials o per a incorporar
a fitxers automatitzats les adreces de l'emissor o del destinatari*.

**
*Aviso de Confidencialidad de e-mail*
Este mensaje puede contener información privilegiada y/o confidencial. Si
Ud. no es el destinatario indicado en este mensaje (*o el responsable de
hacer llegar este mensaje al destinatario*) no está autorizado para copiar o
entregar este mensaje a ninguna persona. En este caso, deberá destruir este
mensaje y se le ruega que avise al destinatario por e-mail. Por favor,
avísenos de inmediato si Ud. o su empresa no admite la utilización del
correo electrónico por Internet para mensajes de este tipo. *No se autoriza
la utilización con fines comerciales o para su incorporación a ficheros
automatizados de las direcciones del emisor o del destinatario*.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug: SQLite Encryption Extension And Backup API

[Ulric Auger]

No it did work, but I found the problem.

 

The problem is caused by no nonce / nonce situation.

 

If source database is created with nonce then there is no problem (the
pragma are not even needed) the backup database is properly encrypted.

 

If source database is created with no nonce (my case) then the backup
database get encrypted improperly.

 

Hope It can be fixed.

 

Thanks

 

Ulric

 

 

 

From: drhsql...@gmail.com [mailto:drhsql...@gmail.com] On Behalf Of Richard
Hipp
Sent: April 7, 2011 13:13
To: General Discussion of SQLite Database
Cc: Ulric Auger
Subject: Re: [sqlite] Bug: SQLite Encryption Extension And Backup API

 

 

On Wed, Apr 6, 2011 at 8:21 AM, Ulric Auger <ul...@gtechna.com> wrote:

Hi,

I think I found a bug creating an encrypted backup.



The source database is encrypted using key:

aes256:A94A8FE5CCB19BA61C4C0873D391E987982FBBD3



The source database is readable, I can run select queries and insert data
with no problem.



When I use the following function to create a backup of the database, the
backup database becomes unreadable.

I used the same encryption key as the source database for the backup one,
but when I try to read something from the backed up database I get an error
that the database is encrypted.



Thanks



int backupDb(sqlite3 *pDb, const char *zFilename, const char *zKey)

{

 int rc;

 sqlite3 *pBackupDb;

 sqlite3_backup *pBackup;



 rc = sqlite3_open(zFilename, );

 if( rc==SQLITE_OK )

 {

   if (zKey != NULL)

   sqlite3_key(pBackupDb, zKey, strlen(zKey));


Please try doing something with pBackupDb here, after setting the key but
before overwriting with the backup.  Something like (for example):

sqlite3_exec(pBackupDb, "PRAGMA secure_delete=OFF", 0, 0, 0);

We have lots and lots of test cases running backups into encrypted
databases, all of which work.  But after further investigation, I see that
we also run a series of pragmas (such as the one above) against the
destination database after the encryption key is set but before the backup
starts.

In the case I just now looked into, the following pragmas run:

PRAGMA recursive_triggers=ON;
PRAGMA foreign_keys=ON;
PRAGMA secure_delete=OFF;

But probably any PRAGMA or any other statement (perhaps:  "SELECT * FROM
sqlite_master") will serve.

Please try this in your code and let me know if it clears the problem.

 




   pBackup = sqlite3_backup_init(pBackupDb, "main", pDb, "main");

   if( pBackup )

   {

 do

 {

   rc = sqlite3_backup_step(pBackup, 100);

   if( rc==SQLITE_OK || rc==SQLITE_BUSY || rc==SQLITE_LOCKED ){

 sqlite3_sleep(10);

   }

 }

 while( rc==SQLITE_OK || rc==SQLITE_BUSY || rc==SQLITE_LOCKED );



 sqlite3_backup_finish(pBackup);

   }

   rc = sqlite3_errcode(pBackupDb);

 }



 sqlite3_close(pBackupDb);

 return rc;

}



Ulric Auger
--
Groupe Techna Inc.
 <mailto:ul...@gtechna.com> ul...@gtechna.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users




-- 
D. Richard Hipp
d...@sqlite.org

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug: SQLite Encryption Extension And Backup API

[Richard Hipp]

On Wed, Apr 6, 2011 at 8:21 AM, Ulric Auger  wrote:

> Hi,
>
> I think I found a bug creating an encrypted backup.
>
>
>
> The source database is encrypted using key:
>
> aes256:A94A8FE5CCB19BA61C4C0873D391E987982FBBD3
>
>
>
> The source database is readable, I can run select queries and insert data
> with no problem.
>
>
>
> When I use the following function to create a backup of the database, the
> backup database becomes unreadable.
>
> I used the same encryption key as the source database for the backup one,
> but when I try to read something from the backed up database I get an error
> that the database is encrypted.
>
>
>
> Thanks
>
>
>
> int backupDb(sqlite3 *pDb, const char *zFilename, const char *zKey)
>
> {
>
>  int rc;
>
>  sqlite3 *pBackupDb;
>
>  sqlite3_backup *pBackup;
>
>
>
>  rc = sqlite3_open(zFilename, );
>
>  if( rc==SQLITE_OK )
>
>  {
>
>if (zKey != NULL)
>
>sqlite3_key(pBackupDb, zKey, strlen(zKey));
>

Please try doing something with pBackupDb here, after setting the key but
before overwriting with the backup.  Something like (for example):

sqlite3_exec(pBackupDb, "PRAGMA secure_delete=OFF", 0, 0, 0);

We have lots and lots of test cases running backups into encrypted
databases, all of which work.  But after further investigation, I see that
we also run a series of pragmas (such as the one above) against the
destination database after the encryption key is set but before the backup
starts.

In the case I just now looked into, the following pragmas run:

PRAGMA recursive_triggers=ON;
PRAGMA foreign_keys=ON;
PRAGMA secure_delete=OFF;

But probably any PRAGMA or any other statement (perhaps:  "SELECT * FROM
sqlite_master") will serve.

Please try this in your code and let me know if it clears the problem.



>
>
>
>pBackup = sqlite3_backup_init(pBackupDb, "main", pDb, "main");
>
>if( pBackup )
>
>{
>
>  do
>
>  {
>
>rc = sqlite3_backup_step(pBackup, 100);
>
>if( rc==SQLITE_OK || rc==SQLITE_BUSY || rc==SQLITE_LOCKED ){
>
>  sqlite3_sleep(10);
>
>}
>
>  }
>
>  while( rc==SQLITE_OK || rc==SQLITE_BUSY || rc==SQLITE_LOCKED );
>
>
>
>  sqlite3_backup_finish(pBackup);
>
>}
>
>rc = sqlite3_errcode(pBackupDb);
>
>  }
>
>
>
>  sqlite3_close(pBackupDb);
>
>  return rc;
>
> }
>
>
>
> Ulric Auger
> --
> Groupe Techna Inc.
>   ul...@gtechna.com
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug: SQLite Encryption Extension And Backup API

[Ulric Auger]

This is so strange, key/rekey before starting the backup didn't work for me,
what work is to call sqlite3_rekey after sqlite3_backup_finish, but what I
don't like is that the backup database is not encrypted until the backup
finishes.

Can SQLite team comment on this?

Thanks

Ulric

int backupDb(sqlite3 *pDb, const char *zFilename, const char *zKey)
{
  int rc;
  sqlite3 *pBackupDb;
  sqlite3_backup *pBackup;

  rc = sqlite3_open(zFilename, );
  if( rc==SQLITE_OK )
  {
pBackup = sqlite3_backup_init(pBackupDb, "main", pDb, "main");
if( pBackup )
{
  do
  {
rc = sqlite3_backup_step(pBackup, 100);
if( rc==SQLITE_OK || rc==SQLITE_BUSY || rc==SQLITE_LOCKED ){
  sqlite3_sleep(10);
}
  }
  while( rc==SQLITE_OK || rc==SQLITE_BUSY || rc==SQLITE_LOCKED );

  sqlite3_backup_finish(pBackup);
}
rc = sqlite3_errcode(pBackupDb);

if (zKey != NULL)
   sqlite3_rekey(pBackupDb, zKey, strlen(zKey));
  }
  
  sqlite3_close(pBackupDb);
  return rc;
}

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Marcus Grimm
Sent: April 7, 2011 10:13
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Bug: SQLite Encryption Extension And Backup API

Hi,

sorry.. my fault. It should be:


if (zKey != NULL)
{
sqlite3_key(pBackupDb, NULL, 0);
sqlite3_rekey(pBackupDb, zKey, strlen(zKey));
}

Thats works for me..

Marcus


On 07.04.2011 15:58, Ulric Auger wrote:
> It didn't change anything to call sqlite3_key twice as you suggested.
>
> I'm using SQLite 3.7.4 with SEE (SEE v3.7.5 is not available from the SEE
> download page, that is strange)
>
> I hope SQLite team can look into this.
>
> Ulric
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

-- 
Marcus Grimm
MedCom GmbH Darmstadt, Rundeturmstr. 12, 64283 Darmstadt
Tel: +49(0)6151-95147-10
Fax: +49(0)6151-95147-20
web: www.medcom-online.de
--
MedCom slogans of the month:
"Vacation ? -- Every day at MedCom is a paid vacation!"
"Friday I have monday in my mind."
"MedCom -- Every week a vacation, every day an event, every hour a
cliffhanger,
every minute a climax."
"Damned, it's weekend again!"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug: SQLite Encryption Extension And Backup API

[Marcus Grimm]

Hi,

sorry.. my fault. It should be:


if (zKey != NULL)
{
sqlite3_key(pBackupDb, NULL, 0);
sqlite3_rekey(pBackupDb, zKey, strlen(zKey));
}

Thats works for me..

Marcus


On 07.04.2011 15:58, Ulric Auger wrote:
> It didn't change anything to call sqlite3_key twice as you suggested.
>
> I'm using SQLite 3.7.4 with SEE (SEE v3.7.5 is not available from the SEE
> download page, that is strange)
>
> I hope SQLite team can look into this.
>
> Ulric
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

-- 
Marcus Grimm
MedCom GmbH Darmstadt, Rundeturmstr. 12, 64283 Darmstadt
Tel: +49(0)6151-95147-10
Fax: +49(0)6151-95147-20
web: www.medcom-online.de
--
MedCom slogans of the month:
"Vacation ? -- Every day at MedCom is a paid vacation!"
"Friday I have monday in my mind."
"MedCom -- Every week a vacation, every day an event, every hour a cliffhanger,
every minute a climax."
"Damned, it's weekend again!"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug: SQLite Encryption Extension And Backup API

[Ulric Auger]

It didn't change anything to call sqlite3_key twice as you suggested.

I'm using SQLite 3.7.4 with SEE (SEE v3.7.5 is not available from the SEE
download page, that is strange)

I hope SQLite team can look into this.

Ulric

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug: SQLite Encryption Extension And Backup API

[Marcus Grimm]

On 06.04.2011 14:21, Ulric Auger wrote:
> Hi,
>
> I think I found a bug creating an encrypted backup.

..

>
>
>
> When I use the following function to create a backup of the database, the
> backup database becomes unreadable.

...
>
>
>
>rc = sqlite3_open(zFilename,);
>
>if( rc==SQLITE_OK )
>
>{
>
>  if (zKey != NULL)
>
>  sqlite3_key(pBackupDb, zKey, strlen(zKey));

I can't recall why but when you create a new DB you need to call
sqlite3_key twice. Try making it:
if (zKey != NULL)
{
sqlite3_key(pBackupDb, NULL, 0);
sqlite3_key(pBackupDb, zKey, strlen(zKey));
}

However, I'm surprised to see that the backup API doesn't
create an exact copy of the source DB when SEE is involved.
I tend to argue that a backup should be encrypted automatically
if the source DB uses SEE as well. In my case I generated some
nice backup DBs which are now not encrypted at the customers side.
Sigh...

Anyway, hope this helps.

Marcus


>
>
>
>  pBackup = sqlite3_backup_init(pBackupDb, "main", pDb, "main");
>
>  if( pBackup )
>
>  {
>
>do
>
>{
>
>  rc = sqlite3_backup_step(pBackup, 100);
>
>  if( rc==SQLITE_OK || rc==SQLITE_BUSY || rc==SQLITE_LOCKED ){
>
>sqlite3_sleep(10);
>
>  }
>
>}
>
>while( rc==SQLITE_OK || rc==SQLITE_BUSY || rc==SQLITE_LOCKED );
>
>
>
>sqlite3_backup_finish(pBackup);
>
>  }
>
>  rc = sqlite3_errcode(pBackupDb);
>
>}
>
>
>
>sqlite3_close(pBackupDb);
>
>return rc;
>
> }
>
>
>
> Ulric Auger
> --
> Groupe Techna Inc.
>     ul...@gtechna.com
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug: SQLite Encryption Extension And Backup API

[Ulric Auger]

Hi,

I think I found a bug creating an encrypted backup.

 

The source database is encrypted using key:

aes256:A94A8FE5CCB19BA61C4C0873D391E987982FBBD3

 

The source database is readable, I can run select queries and insert data
with no problem.

 

When I use the following function to create a backup of the database, the
backup database becomes unreadable.

I used the same encryption key as the source database for the backup one,
but when I try to read something from the backed up database I get an error
that the database is encrypted.

 

Thanks

 

int backupDb(sqlite3 *pDb, const char *zFilename, const char *zKey)

{

  int rc;

  sqlite3 *pBackupDb;

 sqlite3_backup *pBackup;

 

  rc = sqlite3_open(zFilename, );

  if( rc==SQLITE_OK )

  {

if (zKey != NULL)

sqlite3_key(pBackupDb, zKey, strlen(zKey));



pBackup = sqlite3_backup_init(pBackupDb, "main", pDb, "main");

if( pBackup )

{

  do

  {

rc = sqlite3_backup_step(pBackup, 100);

if( rc==SQLITE_OK || rc==SQLITE_BUSY || rc==SQLITE_LOCKED ){

  sqlite3_sleep(10);

}

  }

  while( rc==SQLITE_OK || rc==SQLITE_BUSY || rc==SQLITE_LOCKED );

 

  sqlite3_backup_finish(pBackup);

}

rc = sqlite3_errcode(pBackupDb);

  }

  

  sqlite3_close(pBackupDb);

  return rc;

}

 

Ulric Auger
--
Groupe Techna Inc.
  ul...@gtechna.com 

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Encryption

[Mark Schonewille]

Hi Simon,

Yes, I thought of that and was hoping I woudn't have to do it this  
way. Thanks.

--
Best regards,

Mark Schonewille

Economy-x-Talk Consulting and Software Engineering
Homepage: http://economy-x-talk.com
Twitter: http://twitter.com/xtalkprogrammer
KvK: 50277553

Download the Installer Maker plugin for Runtime Revolution at http://qurl.tk/ce

On 25 jul 2010, at 11:06, Simon Slavin wrote:
>
> The only solution left by those considerations would be to encrypt  
> each field separately.  It would be slow and it would expand the  
> filesize but there's no reason why it wouldn't work as long as  
> Revolution is strong enough to implement a good encryption routine.   
> You'll need to write your own front end to some of the SQLite  
> functions.
>
> Simon.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Encryption

[Simon Slavin]

On 25 Jul 2010, at 10:00am, Mark Schonewille wrote:

> Thanks for your reply. Unfortunately, if I let Revolution encrypt the  
> entire database file, I have to save a decrypted file to disk before I  
> can access it with SQLite. I was hoping there were a trick to let  
> SQLite do the encryption without having to recompile the add-on  
> mentioned in me earlier post and without having to decrypt the entire  
> file before accessing it.

The only solution left by those considerations would be to encrypt each field 
separately.  It would be slow and it would expand the filesize but there's no 
reason why it wouldn't work as long as Revolution is strong enough to implement 
a good encryption routine.  You'll need to write your own front end to some of 
the SQLite functions.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Encryption

[Mark Schonewille]

Thanks for your reply. Unfortunately, if I let Revolution encrypt the  
entire database file, I have to save a decrypted file to disk before I  
can access it with SQLite. I was hoping there were a trick to let  
SQLite do the encryption without having to recompile the add-on  
mentioned in me earlier post and without having to decrypt the entire  
file before accessing it.

--
Best regards,

Mark Schonewille

Economy-x-Talk Consulting and Software Engineering
Homepage: http://economy-x-talk.com
Twitter: http://twitter.com/xtalkprogrammer
KvK: 50277553

Download the Installer Maker plugin for Runtime Revolution at http://qurl.tk/ce

On 25 jul 2010, at 03:10, Timothy A. Sawyer wrote:

> I would think that you can take any third party encryption routine  
> and use it to encrypt the entire database.
>
> I would be remiss if I didn't ask how you would store and retrieve  
> the encryption key, what algorithm you plan to use... All rhetorical  
> questions of course.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Encryption

[Timothy A. Sawyer]

I would think that you can take any third party encryption routine and use it 
to encrypt the entire database.

I would be remiss if I didn't ask how you would store and retrieve the 
encryption key, what algorithm you plan to use... All rhetorical questions of 
course. 
--Original Message--
From: Mark Schonewille
Sender: sqlite-users-boun...@sqlite.org
To: General Discussion of SQLite Database
ReplyTo: General Discussion of SQLite Database
Subject: [sqlite] Encryption
Sent: Jul 24, 2010 05:55

Hello,

I creates a project with Runtime Revolution. I am using an add-on  
(called an external) that comes with RR. I don't have the source for  
the external and can't recompile it to include an encryption library.  
Does anyone know about a way to encrypt an SQLite database without  
having to re-compile the source?

--
Best regards,

Mark Schonewille

Economy-x-Talk Consulting and Software Engineering
Homepage: http://economy-x-talk.com
Twitter: http://twitter.com/xtalkprogrammer
KvK: 50277553

Download the Installer Maker plugin for Runtime Revolution at http://qurl.tk/ce

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


** Sent from my mobile device with the tiny keys **
Timothy A. Sawyer, CISSP
Managing Director
MBD Consulting, LLC
55 Madison Av., Suite 400 
Morristown, NJ 07960
Phone: (973) 285-3261 | Fax: (973) 538-0503
Web: http://www.mybowlingdiary.com
Email: tsaw...@mybowlingdiary.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users