Re: [sqlite] Compile time error when compiling extension shathree.c together with sqlite3.c for Android

[Ulrich Telle]

Am 07.06.2019 um 15:34 schrieb Dan Kennedy:


On 7/6/62 20:23, Ulrich Telle wrote:

When I append the source code of the SHA3 extension (shathree.c from
ext/misc directory) to the SQLite3 amalgamation (sqlite3.c), and then 
try to

compile it for Android, I get the following error message:

shathree.c:83:7: error: expected identifier or '('
   u64 B0, B1, B2, B3, B4;
   ^
../toolchains/llvm/prebuilt/windows/sysroot/usr/include\asm-generic/termbits.h 


:118:12: note:
   expanded from macro 'B0'
#define B0 000


Are you using up to date source code? It looks like this was fixed back 
in 2017:


   https://sqlite.org/src/info/3ec7371161


*Ouch*. I simply missed this change and didn't update to the latest 
version of shathree.c. Thanks!


Sorry for the noise on this list.

Regards,

Ulrich


As far as I can tell header file termbits.h is pulled in as a side 
effect of
including  (which is one of the "standard include files" 
used in

sqlite3.c).

As a workaround I could add a preprocessor check

#ifdef B0
   #undef B0
#endif

in front of the source code in shathree.c. However, I would prefer a 
general

solution.

Would it be possible for the SQLite developers to adjust the variable 
names
used in the extension shathree.c to avoid this name clash with the 
macro in

termbits.h? Or what else would be the recommended method to handle this
issue?


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

[sqlite] Compile time error when compiling extension shathree.c together with sqlite3.c for Android

[Ulrich Telle]

When I append the source code of the SHA3 extension (shathree.c from
ext/misc directory) to the SQLite3 amalgamation (sqlite3.c), and then try to
compile it for Android, I get the following error message:

shathree.c:83:7: error: expected identifier or '('
  u64 B0, B1, B2, B3, B4;
  ^
../toolchains/llvm/prebuilt/windows/sysroot/usr/include\asm-generic/termbits.h
:118:12: note:
  expanded from macro 'B0'
#define B0 000
   ^

As far as I can tell header file termbits.h is pulled in as a side effect of
including  (which is one of the "standard include files" used in
sqlite3.c).

As a workaround I could add a preprocessor check

#ifdef B0
  #undef B0
#endif

in front of the source code in shathree.c. However, I would prefer a general
solution.

Would it be possible for the SQLite developers to adjust the variable names
used in the extension shathree.c to avoid this name clash with the macro in
termbits.h? Or what else would be the recommended method to handle this
issue?

Regards,

Ulrich
--
E-Mail privat:  ulrich.te...@gmx.de
World Wide Web: http://www.telle-online.de


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

Re: [sqlite] How to parameterize a loadable extension at runtime

[Ulrich Telle]

> Simon has the correct idea.   If you have a function x(), you are free
> to define another in the same extension called function
> x_config(). 

Yes, of course. In fact, I mentioned this option already in my original post. 
The syntax for the user will be less intuitive than a pragma statement, but 
since it seems to be impossible to intercept pragma handling, it is most 
probably the easiest solution.  

> This x_config() function is free to change global runtime preference
> variables of the x() function based on the  passed into the last
> call of x_config().   SQLITE_DETERMINISTIC is merely a performance
> hint for expression evaluation within a single statement. 
> Deterministic functions may be called multiple times anyway and every
> distinct statement where output depends on a function, deterministic or
> not, will cause that function to be evaluated at least once. 

The functions in my extension will all be deterministic. The purpose of the 
parameters is to initialize the environment of my extension. The alternative 
would be that the user would pass the parameters to each call of one of the 
extension functions, but this is cumbersome and errorprone.  

> The only problem will be if a thread in your process calls the
> x_config() function while the x() function has a different thread
> context.  If overlapping multithreaded usage is anticipated, global
> configuration variable access must be serialized by the sqlite3 mutex
> API or other critical section mechanism. 

That's a valid point. I should better take care that different threads will not 
use different parameter settings. Thanks.  

Regards,  

Ulrich  


> On Tue, Feb 6, 2018 at 1:24 AM, Ulrich Telle wrote:
> 
> > > Simon Slavin wrote:
> > >
> > > On 6 Feb 2018, at 8:33am, Ulrich Telle wrote:
> > >
> > > > Another possibility would be to add a user-defined function for the
> > > > configuration of the extension that could be called from a SELECT
> > > > statement:
> > > >
> > > > SELECT myextension_config('param-name', 'param-value');
> > >
> > > I've seen this done before.  Of course it means that your normal
> > function is not deterministic, so you may no longer use
> > SQLITE_DETERMINISTIC .  This is in contrast to a function where parameters
> > are set during compilation.
> >
> > Well, actually my goal is not to have an extension with non-deterministic
> > functions. The parameters have mostly the purpose to initialize the
> > extension (things similar to what you do to SQLite itself with pragmas
> like
> > "PRAGMA cache_size", or "PRAGMA data_store_directory"). The extension
> would
> > accept changes to the parameters only before the first invocation of the
> > extension functions.
> >
> > 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


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

Re: [sqlite] How to parameterize a loadable extension at runtime

[Ulrich Telle]

Dominique Devienne wrote:
>
> On Tue, Feb 6, 2018 at 11:15 AM, Ulrich Telle <ulrich.te...@gmx.de> wrote:
> 
> > > An alternative is to expose a virtual table with a fixed set of rows, and
> > > accepting updates on the values, which can also then be "typed" too.
> > > But that's a lot more complicated though.
> > > (and refusing inserts/deletes too, of course).
> > >
> > > That vtable could also expose version information for the extension, for
> > > example, and those would be read-only. Just thinking aloud.
> > > Avoids non-deterministic functions.
> >
> > A vtable with a fixed number of rows, one for each config parameter - this
> > approach sounds interesting.
> > I'll have to investigate how complicated it will be to implement such an
> > approach.
> >
> 
> This approach could IMHO be one of the contributed vtable impls in ext/misc
> [1] to be reused by other loadable extension authors, and could become the
> "semi official" way to solve that problem, lacking extension specific
> pragmas that is. My $0.02c. --DD

In case I'll implement the vtable approach, I might consider to make it 
available.

> PS: There's also always environment variables, especially for 1-time at
> startup settings.
>   My main beef against env.vars. though is that they are not discoverable
> and often hidden.

For my purpose environment variables are not suitable. The user should be able 
to alter the configuration parameters for each database connection.

Regards,

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

Re: [sqlite] [EXTERNAL] How to parameterize a loadable extension at runtime

[Ulrich Telle]

> Hick Gunter wrote:
>
> You can pass parameters to the xCreate function in the CREATE VIRTUAL TABLE 
> statement, if the setting you desire remains unchanged during the lifetime of 
> the table.
> 
> CREATE VIRTUAL TABLE  USING  [ ( ,...) ];
> 
> You can declare hidden fields in the call to sqlite3_declare_vtab() call 
> within your xCreate function if the setting you desire are specific to a 
> query. The constraint will be passed to your xBestIndex function, and (if the 
> query plan is selected) the value will be passed to your xFilter function.
> 
> CREATE VIRTUAL TABLE with_foo USING handle_foo;
> 
> Sqlite3_declare_vtab(db_hanlde, "CREATE TABLE x ( ..., foo integer hidden, 
> ...);");
> 
> SELECT  FROM with_foo wf  WHERE wf.foo = 'bar';

I have to admit that I don't have much experience with the vtable concept. My 
extensions consist of a set of functions that can be used in SQL statements. 
During a single database connection the behaviour of the functions will be 
deterministic, but the user should be able to set certain initialization 
parameters.

Dominique Devienne proposed in his answer to implement a vtable with a fixed 
set of rows for the configuration parameters. That approach seems to be a bit 
simpler to implement than a fully fledged vtable solution.

> Or, for "none of the above", create a user defined function that will handle 
> storing/retrieving the settings and provide a C interface for your virtual 
> table implementation to access them directly
> 
> SELECT param('foo','bar') AS p;
> p
> --
> NULL
> 
> SELECT param('foo') AS foo;
> foo
> -
> bar

Yes, that's the approach I already mentioned in my original post. Adding a C 
interface is probably a good idea, too. However, setting parameters should be 
possible without calling a C interface function, for example, if a user loads 
the extension from the default SQLite shell coming with the SQLite distribution.

I would have preferred a more PRAGMA like syntax, but that could probably be 
called "syntactic sugar" - a SELECT with a user-defined function will work and 
the syntax is simple enough.

Regards,

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

Re: [sqlite] How to parameterize a loadable extension at runtime

[Ulrich Telle]

> Dominique Devienne wrote:
> 
> An alternative is to expose a virtual table with a fixed set of rows, and
> accepting updates on the values, which can also then be "typed" too. 
> But that's a lot more complicated though.
> (and refusing inserts/deletes too, of course).
> 
> That vtable could also expose version information for the extension, for
> example, and those would be read-only. Just thinking aloud.
> Avoids non-deterministic functions.

A vtable with a fixed number of rows, one for each config parameter - this 
approach sounds interesting.

I'll have to investigate how complicated it will be to implement such an 
approach.

Regards,

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

Re: [sqlite] How to parameterize a loadable extension at runtime

[Ulrich Telle]

> Simon Slavin wrote:
> 
> On 6 Feb 2018, at 8:33am, Ulrich Telle wrote:
> 
> > Another possibility would be to add a user-defined function for the 
> > configuration of the extension that could be called from a SELECT 
> > statement:
> > 
> > SELECT myextension_config('param-name', 'param-value');
> 
> I've seen this done before.  Of course it means that your normal function is 
> not deterministic, so you may no longer use SQLITE_DETERMINISTIC .  This is 
> in contrast to a function where parameters are set during compilation.

Well, actually my goal is not to have an extension with non-deterministic 
functions. The parameters have mostly the purpose to initialize the extension 
(things similar to what you do to SQLite itself with pragmas like "PRAGMA 
cache_size", or "PRAGMA data_store_directory"). The extension would accept 
changes to the parameters only before the first invocation of the extension 
functions.

Regards,

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

[sqlite] How to parameterize a loadable extension at runtime

[Ulrich Telle]

I have implemented a loadable SQLite extension. The behaviour of the 
extension can be configured by setting various parameters. Currently I select 
the parameter settings at compile time. However this is not very flexible. I 
would like to be able to modify the parameters at runtime.

The most logical way would be to add extension-specific pragmas, but it 
doesn't seem to be possible to intercept the pragma handling of SQLite 
without modifying the SQLite source.

Another possibility would be to add a user-defined function for the 
configuration of the extension that could be called from a SELECT 
statement:

SELECT myextension_config('param-name', 'param-value');

Is there a better (or even recommended) way how to accomplish such 
parameterization at runtime?

Regards,

Ulrich
-- 
E-Mail privat:  ulrich.te...@gmx.de
World Wide Web: http://www.telle-online.de


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

Re: [sqlite] How can i encrypt my database file ?

[Ulrich Telle]

My first steps i made for encyption nowadays is that i read and test some free 
encryptions around the net such as sql cipher and some others but i have some 
doubts if this is correct cause inside the sqlite3.cpp in the section that i 
define SQLITE_HAS_CODEC it says that is not in the public domain.


In the SQLite source code it reads

** The code to implement this API is not available in the public release 
of SQLite. **


That is, the SQLite Encryption Extension (SEE), offered by D.R. Hipp, 
the main SQLite developer, is not publicly available, but a commercial 
license is available at a price of 2000 USD (see 
https://www.hwaci.com/cgi-bin/see-step1).



So what i want to ask is this :

If i want to make my database file encrypted do i have to purchase a license 
and be able to use encryption for sqlite3 or
i can do it with any free program without having to pay for a license ?


Each encryption extension implementation has its own license terms:

System.Data.SQLite (http://system.data.sqlite.org), available from the 
SQLite website, implements a Windows based encryption extension and is 
free of cost. Most of the code is in the public domain (as SQLite 
itself), but certain parts are under the Microsoft Public License (MS-PL).


SQLCipher offers Community Editions 
(https://www.zetetic.net/sqlcipher/open-source/) (free of cost, 
BSD-style license) and Commercial Editions 
(https://www.zetetic.net/sqlcipher/buy/) (not free). It depends on your 
actual requirements whether the Community Edition is good enough for 
your purposes or not.


wxSQLite3 (https://github.com/utelle/wxsqlite3) includes an encryption 
extension under the permissive wxWindows license (you can distribute 
binaries of your application free of cost without having to disclose the 
source code of your own application).


There are other free encryption extensions. You will have to check their 
license terms, whether they allow commercial use or not.


Regards,

Ulrich

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

Re: [sqlite] How can i encrypt my database file ?

[Ulrich Telle]

My first steps i made for encyption nowadays is that i read and test some free 
encryptions around the net such as sql cipher and some others but i have some 
doubts if this is correct cause inside the sqlite3.cpp in the section that i 
define SQLITE_HAS_CODEC it says that is not in the public domain.


In the SQLite source code it reads

** The code to implement this API is not available in the public release 
of SQLite. **


That is, the SQLite Encryption Extension (SEE), offered by D.R. Hipp, 
the main SQLite developer, is not publicly available, but a commercial 
license is available at a price of 2000 USD (see 
https://www.hwaci.com/cgi-bin/see-step1).



So what i want to ask is this :

If i want to make my database file encrypted do i have to purchase a license 
and be able to use encryption for sqlite3 or
i can do it with any free program without having to pay for a license ?


Each encryption extension implementation has its own license terms:

System.Data.SQLite (http://system.data.sqlite.org), available from the 
SQLite website, implements a Windows based encryption extension and is 
free of cost. Most of the code is in the public domain (as SQLite 
itself), but certain parts are under the Microsoft Public License (MS-PL).


SQLCipher offers Community Editions 
(https://www.zetetic.net/sqlcipher/open-source/) (free of cost, 
BSD-style license) and Commercial Editions 
(https://www.zetetic.net/sqlcipher/buy/) (not free). It depends on your 
actual requirements whether the Community Edition is good enough for 
your purposes or not.


wxSQLite3 (https://github.com/utelle/wxsqlite3) includes an encryption 
extension under the permissive wxWindows license (you can distribute 
binaries of your application free of cost without having to disclose the 
source code of your own application).


There are other free encryption extensions. You will have to check their 
license terms, whether they allow commercial use or not.


Regards,

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

Re: [sqlite] How can i encrypt my database file ?

[Ulrich Telle]

My first steps i made for encyption nowadays is that i read and test some free 
encryptions around the net such as sql cipher and some others but i have some 
doubts if this is correct cause inside the sqlite3.cpp in the section that i 
define SQLITE_HAS_CODEC it says that is not in the public domain.


In the SQLite source code it reads

** The code to implement this API is not available in the public release 
of SQLite. **


That is, the SQLite Encryption Extension (SEE), offered by D.R. Hipp, 
the main SQLite developer, is not publicly available, but a commercial 
license is available at a price of 2000 USD (see 
https://www.hwaci.com/cgi-bin/see-step1).



So what i want to ask is this :

If i want to make my database file encrypted do i have to purchase a license 
and be able to use encryption for sqlite3 or
i can do it with any free program without having to pay for a license ?


Each encryption extension implementation has its own license terms:

System.Data.SQLite (http://system.data.sqlite.org), available from the 
SQLite website, implements a Windows based encryption extension and is 
free of cost. Most of the code is in the public domain (as SQLite 
itself), but certain parts are under the Microsoft Public License (MS-PL).


SQLCipher offers Community Editions 
(https://www.zetetic.net/sqlcipher/open-source/) (free of cost, 
BSD-style license) and Commercial Editions 
(https://www.zetetic.net/sqlcipher/buy/) (not free). It depends on your 
actual requirements whether the Community Edition is good enough for 
your purposes or not.


wxSQLite3 (https://github.com/utelle/wxsqlite3) includes an encryption 
extension under the permissive wxWindows license (you can distribute 
binaries of your application free of cost without having to disclose the 
source code of your own application).


There are other free encryption extensions. You will have to check their 
license terms, whether they allow commercial use or not.


Regards,

Ulrich

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

[sqlite] Inconsistent use of precompiler symbol SQLITE_HAS_CODEC

[Ulrich Telle]

In most places in the SQLite source code the check

#ifdef SQLITE_HAS_CODEC

is used. However, at 4 places (1 in btree.c, 1 in ctime.c, and 2 in 
pager.c) the check


#if SQLITE_HAS_CODEC

is used. The latter can fail to be correctly evaluated, if one only 
defines the symbol, but doesn't set a value.


Yes, the problem can be overcome by defining the symbol with a value like

#define SQLITE_HAS_CODEC 1

but obviously not all developers are aware of this (i.e., see projects 
SQLCipher or wxSQLite3).


In fact, I find the inconsistent use a bit dangerous, since defining

#define SQLITE_HAS_CODEC 0

would NOT disable this option completely, but would compile partial 
support for encryption - maybe causing obscure problems.


Therefore I would suggest to either not relying on a symbol value but to 
check always for the definition of the symbol only using


#ifdef SQLITE_HAS_CODEC

or to assume that SQLITE_HAS_CODEC always has a value and check using

#if SQLITE_HAS_CODEC

everywhere.

Regards,

Ulrich

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

Re: [sqlite] Problem with the new pointer-passing interface

[Ulrich Telle]

Gwendal,

> I agree that it's impossible to enforce "static strings" or "strings that 
> live long enough" with the C/C++ type system.
> 
> You chose to force key management down the throat of your users,

Hm, quite a tough expression ... the advantage is that the wrapper does not 
depend on the pointer type being a string literal or static string. For the 
purpose - to allow pointer binding to communicate with existing SQLite 
extension - I think this is a legitimate approach.

Regarding the development of SQLite extensions (UDFs) and using the 
pointer-binding interface to communicate between different extensions (AFAIK 
one of the reasons to introduce the new pointer-binding interface) the advice 
to use string literals is certainly good.

Although my wrapper allows to implement UDFs, it is not meant (and not well 
suited) to implement stand-alone SQLite extensions. It is meant for end-user 
applications where the developer has access to all information, database 
related or not, anyway.

> with two problems :
> 
> - All the potential memory leaks, thread races, etc that may occur if your 
> key management has subtle concurrency bugs.

Using the same prepared statement object in different threads isn't a good idea 
anyway ... if it works at all. If the application is carefully designed in 
respect to SQLite database access I don't see a high risk.

> - Making your users think they can build *stuff* on top of this API, *stuff* 
> that are explicitly
> out of scope of the pointer API as described by the design rationale of 
> SQLite pointers API.

Sorry, since SQLite has no means to absolutely enforce the use of string 
literals for the pointer type parameter, developers can build *stuff* on top of 
the pointer-binding interface anyway. According to the explanation from Richard 
Hipp a main reason for the design decision was to avoid a runtime penalty for 
applications not using the pointer-binding interface at all.

> Plus memory problems if your users think they can feed your API with 
> arbitrary strings built at runtime.

My "users" are software developers. Why should they want to feed the API with 
arbitrary strings built at runtime? I'm pretty sure that in most cases the type 
strings will be string literals, but the API will not fail if a string variable 
is used.

> I think the problem, assuming there is one, is not in the SQLite API.

I ran into the problem that the pointer type string I tried to bind to the new 
interface was a temporary object. And that doesn't work with the current SQLite 
API. I learned that the new SQLite pointer-binding API was designed as it is on 
purpose. I resolved the issue for my wrapper classes. And that's it.

If a malicious developer wants to do evil, he can do so even with the SQLite 
API directly. He doesn't need my wrapper for that.

Regards,

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

Re: [sqlite] Problem with the new pointer-passing interface

[Ulrich Telle]

Gwendal,

> But... why don't you simply ask your users for a static string as well???
> C++ makes it trivial to support this requirement of the C API.

I could do that, of course. But it wouldn't solve the issue. It would push the 
problem just one level up.

> // pointerType should be a static string
> void wxSQLite3Statement::Bind(int paramIndex, void* pointer, char 
> *pointerType, void(*DeletePointer)(void*))

That doesn't enforce a static string. The signature would have to be at least:

void wxSQLite3Statement::Bind(int paramIndex, void* pointer, const char* 
pointerType, void(*DeletePointer)(void*))

However, if not called with a string literal, it would still easily fail to 
work. Simplified example:

void* ptr = ...;
char* pType = malloc(10);
strcpy(pType, "carray");
// ...
stmt.Bind(1, ptr, pType, NULL);
// ...
free(pType);

Yes, I know, this is an artificial example. No one would implement it this way 
in practice. It just demonstrates that even with a method signature asking for 
a const char* it is not guaranteed to work. It would most probably fail at 
runtime.

Additionally, wxWidgets supports various implicit string conversions. So - just 
as SQLite itself - my wrapper methods can't detect whether a static string or 
string literal was passed or whether the compiler constructed a temporary 
string object. And the compiler will not issue error messages, often not even 
warnings.

As mentioned in an earlier post, for my wrapper classes I decided to implement 
the necessary housekeeping. Regarding the use of SQLite and my wrapper classes 
I just quote a sentence which can be found in all SQLite sources:

"May you do good and not evil."

Regards,

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

Re: [sqlite] Problem with the new pointer-passing interface

[Ulrich Telle]

Nico,

> Rhetorical: Why not use the pointer value itself then instead of the
> contents of the string?  After all, the string should just be a .text
> section constant string...

The SQLite developer team chose a string representation for the pointer type to 
make naming collisions less likely.

> Now, suppose that you're building FFI bindings for SQLite3 for some
> programming language, e.g., Python.  So you have a string from the
> run-time for this language, but you can't ensure that it will have the
> necessary lifetime.  So now you have to make a copy.  It's a bit of a
> pain.  SQLite3 could have made a copy itself.

I would have preferred that, too, but I learned that this would have imposed a 
runtime penalty even for applications not using the new pointer interface. 

> On the flip side, it's possible that you have to do some conversions
> anyways because SQLite3 deals with C strings and the target language
> run-time deals with counted-codeunit strings or whatever.

The conversion is not the problem. The problem is that SQLite requires that the 
pointer type string has a life span at least as long as the prepared statement 
referring to it. And this requires that the application takes care of that.

> So even if SQLite3 made a copy, the FFI bindings might have to make
> their own copy anyways.  Thus: who cares :)

The problem is to keep the copy alive long enough. However, the problem is not 
that big. It can be solved in less than 50 lines of code.

> Still, for many cases it will be easier to write code to this API if
> SQLite3 makes its own copy.

Yes, but Richard Hipp made it quite clear that the latter is unlikely to happen.

Regards,

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

Re: [sqlite] Problem with the new pointer-passing interface

[Ulrich Telle]

Richard,

> Can you please provide more details on how having a dynamic string for
> the pointer type would be helpful?  What is it that you are trying to
> do that string constant will not work?  Please be as specific as
> possible, so that I might better understand your problem.

I maintain a C++ wrapper library for SQLite especially for developers of 
wxWidgets based applications. This wrapper library offers access to almost all 
SQLite features (that make sense for an end-user application) through the use 
of C++ classes. That is, the wrapper classes encapsulate all calls to the 
SQlite C API.

With the release of SQLite 3.20.0 the new pointer-passing interface was 
introduced, and I found it quite useful to support extensions like carray. 
Therefore, I implemented a pointer bind method for the prepared SQL statement 
classs. This method makes internally a call to function sqlite3_bind_pointer. 
The signature and implementation of the method looks like this:

void wxSQLite3Statement::Bind(int paramIndex, void* pointer, const wxString& 
pointerType, void(*DeletePointer)(void*))
{
  CheckStmt();
  const char* localPointerType = m_stmt->MakePointerTypeCopy(pointerType);
  int rc = sqlite3_bind_pointer(m_stmt->m_stmt, paramIndex, pointer, 
localPointerType, DeletePointer);
}

The member variable m_stmt is a reference counted reference object to a 
prepared SQL statement (sqlite3_stmt). This makes it possible to pass around 
the SQL statement object and to clean up the SQLite data structures when the 
last reference to the statement is deleted. This reference object now includes 
a dynamic array holding pointer type string duplicates until the reference 
object itself goes out of scope.

However, in my first implementation I converted the pointer type string 
parameter (wxString object) to a local char* variable. Since this local 
variable was destroyed after leaving the method, the select on the carray table 
failed, since the pointer type string was void.

Now, I create a copy of the pointer type string in a data structure that is 
kept alive until the SQL statement object is deleted. The carray extension now 
works flawlessly in the context of my wrapper.

For a C++ wrapper you could argue that using the SQLite API directly is 
feasible. However, for SQLite wrappers for other languages like Python or Lua, 
this might not work out.

Regards,

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

Re: [sqlite] Problem with the new pointer-passing interface

[Ulrich Telle]

Richard,

> On 8/3/17, Ulrich Telle <ulrich.te...@gmx.de> wrote:
> >
> > The description of the new pointer-passing interface gives the impression
> > that restricting the pointer type parameter to static strings or literals
> > prevents misuse of the new feature. And that is definitely not the case. It
> > might be a hurdle for unsophisticated developers, but not for the
> > experienced ones.
> 
> The documentation has now been adjusted to try to make it clear that
> the static string requirement simply makes misuse of the interface
> more difficult, not impossible.

Thanks. Certainly a valid point to not impose a runtime penalty on applications 
not using the pointer interface.

As said I solved the issue for my SQLite wrapper. The solution only affects the 
calls to the pointer interface (like binding a pointer to a SQL parameter).

Regards,

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

Re: [sqlite] Problem with the new pointer-passing interface

[Ulrich Telle]

Peter,

> On 8/3/17, 9:16 AM, "sqlite-users on behalf of Ulrich Telle"wrote:
> > The description of the new pointer-passing interface gives the
> > impression that restricting the pointer type parameter to static
> > strings or literals prevents misuse of the new feature.
> 
> The term I used was “deters”.

I know, but the SQLite documentation uses "prevents":

" ... Thus, the requirement that pointer types be static strings helps to 
prevent misuse of the pointer-passing interfaces."

> > And that is definitely not the case. It might be a hurdle for
> > unsophisticated developers, but not for the experienced ones.
> 
> What experienced, non-malicious developers would read the rationale
> and then go ahead and implement an extension that opened up the
> possibility of a pointer-based exploit from SQL by allowing types
> generated from SQL strings? 

No one, hopefully. At least I'm not going to do that. I just wanted to point 
out that there are legitimate scenarios (like the wrapper I maintain) in which 
the restriction makes it harder to implement support for useful extensions like 
carray.

I learned that the restriction was imposed on purpose, and since I believe - as 
naive as I am - to be a rather experienced developer, I was able to overcome it 
for my use case. That is, I can now live with the restriction.

Regards,

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

Re: [sqlite] Problem with the new pointer-passing interface

[Ulrich Telle]

Peter,

> On 8/3/17, 8:56 AM, "sqlite-users on behalf of Ulrich Telle" wrote:
> > Nevertheless, I think the pointer type string restriction gives a false 
> > sense of security.
> 
> You described creating a malicious extension that would give you the ability 
> to probe pointers by forging strings.

The description of the new pointer-passing interface gives the impression that 
restricting the pointer type parameter to static strings or literals prevents 
misuse of the new feature. And that is definitely not the case. It might be a 
hurdle for unsophisticated developers, but not for the experienced ones.

> From a security point of view, once you have the ability to create
> and deploy a malicious extension into a target host, you’ve already
> got full local code execution access to that host anyway.

Right. And therefore I think that the restriction to static strings or literals 
for the pointer type parameter just makes life a bit harder for honest 
developers of wrappers or the like, but prevents nothing.

Without UDFs I think it is impossible to get access to pointers in pure SQL, so 
if someone wants to do malicious things he has to convince a user to load a 
malicious extension.

Regards,

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

Re: [sqlite] Problem with the new pointer-passing interface

[Ulrich Telle]

Peter,

> On 8/3/17, 8:27 AM, "sqlite-users on behalf of Ulrich Telle" wrote:
> > I really don't think that the latter is true. To overcome the restriction
> > is extremly simple, if you don't care about memory leaks. Just do [...]
> 
> If you’re creating a malicious extension, sure. But if you’re creating
> an exploit this seems like an odd way to go about it. It’d be simpler
> to implement something like “SELECT root_shell_on_port(1337);”

I have no intention at all to create malicious extensions. I just develop and 
maintain a SQLite wrapper and stumbled across this restriction with static 
pointer type strings, when I started to add support for the new pointer-passing 
interface and tested it with the carray extension.

In the meantime I already managed to adjust my wrapper to get along with the 
restriction.

Nevertheless, I think the pointer type string restriction gives a false sense 
of security.

Regards,

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

Re: [sqlite] Problem with the new pointer-passing interface

[Ulrich Telle]

Richard,

> As the desire for dynamic pointer type strings seems to be a recurring
> theme, I have added lots of extra documentation that attempts to
> explain why the pointer-passing interfaces deliberately use a constant
> static string for the pointer type:
> 
>https://sqlite.org/bindptr.html#ptrtyp

As I already wrote in my response to Gwendal, I admittedly haven't read that 
page carefully enough. Sorry.

Nevertheless, as I explained in the above mentioned post this restriction 
doesn't prevent a bad guy from developing a SQL UDF that can construct 
arbitrary pointers from SQL. That is, IMHO the restriction gives a false sense 
of security. Exposing pointers in SQL is potentially dangerous, although there 
are certainly useful applications.

BTW, for my SQLite wrapper I already implemented the required housekeeping 
(which even avoids allocating duplicates of already registered pointer types) 
in less than 40 lines of code in the meantime.

Regards,

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

Re: [sqlite] Problem with the new pointer-passing interface

[Ulrich Telle]

Gwendal,

> > Le 3 août 2017 à 14:27, Peter Da Silva <peter.dasi...@flightaware.com> a 
> > écrit :
> > 
> > On 8/3/17, 4:22 AM, "sqlite-users on behalf of Ulrich Telle" 
> > <sqlite-users-boun...@mailinglists.sqlite.org on behalf of 
> > ulrich.te...@gmx.de> wrote:
> >> IMHO it would be better if the function sqlite3_bind_pointer would make a 
> >> copy of the type string and would thus be independent of the life span of 
> >> the type parameter.
> > 
> > I believe that this was a deliberate choice to deter using temporary 
> > strings for type names.
> 
> The reference (https://www.sqlite.org/bindptr.html) says:

Thanks for the pointer. I have to admit that I referred to 
http://sqlite.org/c3ref/bind_blob.html.

> > Because pointer types must be static strings, and because string
> > values in SQLite are dynamic strings, that means that SQL values
> > cannot be used as a pointer type. This prevents misguided
> > developers from creating a new SQL function that can manufacture
> > pointer values directly from SQL. Such a function, if possible to
> > write, would undermine the security of the pointer-passing APIs.
> > Thus, the requirement that pointer types be static strings helps
> > to prevent misuse of the pointer-passing interfaces.

I really don't think that the latter is true. To overcome the restriction is 
extremly simple, if you don't care about memory leaks. Just do

/* Assume that char* ptrType holds the pointer type string */
/* Create a copy of the pointer type string on the heap
char* ptrTypeCopy = strdup(ptrType)
sqlite3_bind_pointer(pStmt, argIndex, ptr, ptrTypeCopy, NULL);

The function sqlite3_bind_pointer has no means to detect whether the passed 
pointer type ptrTypeCopy is indeed a static variable or a string literal. So at 
the expense of a memory leak it is a no-brainer to overcome the restriction. 
That is, developing SQL UDFs that are able to construct pointers from SQL data 
is not prevented by this restriction.

Conclusion: Beware of loadable extensions that you haven't written (or at least 
compiled) yourself.

Regards,

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

Re: [sqlite] Problem with the new pointer-passing interface

[Ulrich Telle]

Peter,

> On 8/3/17, 4:22 AM, "sqlite-users on behalf of Ulrich Telle" wrote:
> > IMHO it would be better if the function sqlite3_bind_pointer would make a 
> > copy of the type string
> > and would thus be independent of the life span of the type parameter.
> 
> I believe that this was a deliberate choice to deter using temporary strings 
> for type names.

Well, the comment regarding the type parameter ("The T parameter should be a 
static string, preferably a string literal.") just tells that the type string 
"should" be static. It doesn't warn that it doesn't work if the life time of 
the type string is shorter than that of the statement to which the pointer is 
bound. At least the description should be enhanced.

If one uses the SQLite3 API directly, this is most probably not a big deal, 
since most likely string literals will be used to specify the pointer type. But 
as soon as one tries to implement a SQLite wrapper (may it be for C++ or other 
languages like Python etc), this restriction makes things cumbersome. String 
parameters may not be guaranteed to live as long as needed, and it might be 
necessary to convert them to const char* before passing them on to SQLite. Then 
you have to implement some sort of housekeeping (or have to live with memory 
leaks, if you allocate a copy yourself and never free it). Since SQLite already 
does housekeeping regarding the pointer itself, I assume that it wouldn't be 
too difficult to extend this to make a copy of the type string and free it, 
when the pointer value goes out of scope and its destructor is invoked.

Regards,

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

Re: [sqlite] Problem with the new pointer-passing interface

[Ulrich Telle]

Hi Paul,

> To me it seems like that string is very tightly coupled with the actual 
> pointer being bound.

No, not really. The type string is tightly coupled with the extension module 
which uses the pointer. The type string allows the extension module to check 
whether the pointer is really meant to be handled by the module.

For the carray module the pointer just pints to a C++ array of integers, 
doubles or strings. So there is no simple way to attach the type string to this 
data structure.

> I think it's a good idea, in case you cannot make it a literal or static, to 
> keep it with
> an object whose pointer you bind.

The problem is that my component just provides a thin wrapper for SQLite for 
applications based on the wxWidgets library. In most cases the wrapper just 
passes given parameters on to the underlying SQLite functions. That is, the 
wrapper itself doesn't know anything about internals of extension modules and 
pointer objects they might be able to handle.

I could introduce a wrapper object for pointers that additionally contains a 
type string, but then I would have to keep track of wrapper objects, since only 
the raw pointer is passed on to the extension module. In fact, this would not 
solve the problem.

IMHO it should be rather simple to adjust the SQLite functions of the new 
pointer-passing interface to make a copy of the type string on binding a 
pointer and releasing the copy when the pointer value goes out of scope.

Regards,  

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

Re: [sqlite] Problem with the new pointer-passing interface

[Ulrich Telle]

Hi Clemens,

> Ulrich Telle wrote:
> > In the comment of the sqlite3_bind_pointer() routine I found this note:
> >
> > The T parameter should be a static string, preferably a string literal.
> >
> > In my case this is quite cumbersome, since in my wrapper I have to extract
> > the value of parameter T from a temporary string object.
> 
> <http://www.sqlite.org/bindptr.html> says:
> | The "pointer type" string which is the last parameter to each of the
> | pointer-passing interfaces should be a distinct, application-specific
> | string literal that appears directly in the API call. The pointer type
> | should not be a parameter passed in from a higher-level function.
> 
> If you think that you really need to get this string from a higher-level
> function, offer a mechanism to 'register' this string, i.e., make
> a permanent copy that can be referenced by later calls.

Yes, I know I will have to do that to make things work, but it is really 
cumbersome, since I need a global object holding these 'registered' strings and 
have to take care that memory is cleaned up at some point in time. Doing it 
within SQLite would be much easier, since SQLite has to call the destructor 
function for the pointer (if it was given). So SQLite 'knows' when the pointer 
value goes out of scope ... and freeing the type string when checking whether 
the destructor function for the pointer has to be called or not, would probably 
be just one statement more in the SQLite source.

Regards,

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

[sqlite] Problem with the new pointer-passing interface

[Ulrich Telle]

Hi,

I came across a problem with the new pointer-passing interface, when I 
tried to incorporate the carray extension into my wxSQLite3 wrapper for 
SQLite3.


In the comment of the sqlite3_bind_pointer() routine I found this note:

The T parameter should be a static string, preferably a string literal.

This comment is a bit misleading, since it doesn't become clear that the 
string pointer for T must be valid as long as the associated statement 
is active.


In my case this is quite cumbersome, since in my wrapper I have to 
extract the value of parameter T from a temporary string object. That 
is, shortly after having called sqlite3_bind_pointer the temporary 
string goes out of scope. The effect is that later on the function 
sqlite3_value_pointer usually can't see the correct type value anymore. 
Therefore sqlite3_value_pointer returns a NULL pointer ... and the 
carray table is empty.


IMHO it would be better if the function sqlite3_bind_pointer would make 
a copy of the type string and would thus be independent of the life span 
of the type parameter.


Regards,

Ulrich

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

[sqlite] Problem with the new pointer-passing interface

[Ulrich Telle]

Hi,  

I came across a problem with the new pointer-passing interface, when I tried 
to incorporate the carray extension into my wxSQLite3 wrapper for SQLite3.  

In the comment of the sqlite3_bind_pointer() routine I found this note:  

The T parameter should be a static string, preferably a string literal.  

This comment is a bit misleading, since it doesn't become clear that the 
string pointer for T must be valid as long as the associated statement is 
active.  

In my case this is quite cumbersome, since in my wrapper I have to extract 
the value of parameter T from a temporary string object. That is, shortly after 
having called sqlite3_bind_pointer the temporary string goes out of scope. 
The effect is that later on the function sqlite3_value_pointer usually can't 
see 
the correct type value anymore. Therefore sqlite3_value_pointer returns a 
NULL pointer ... and the carray table is empty.  

IMHO it would be better if the function sqlite3_bind_pointer would make a 
copy of the type string and would thus be independent of the life span of the 
type parameter.  

Regards,  

Ulrich  
-- 
E-Mail privat:  ulrich.te...@gmx.de
World Wide Web: http://www.telle-online.de


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

[sqlite] Inconsistent check for symbol SQLITE_DEBUG in amalgamation version 3.16.2

[Ulrich Telle]

In the documentation for the compile time options for debugging

http://sqlite.org/compile.html#debug

the symbol SQLITE_DEBUG is shown without a value. And in most places in
the SQLite source code only the definition status is checked using

#ifdef SQLITE_DEBUG

or

#if defined(SQLITE_DEBUG)

However, at several places the *value* of the symbol is checked using

#if SQLITE_DEBUG

IMHO this is inconsistent with the documentation and probably should be
changed to

#ifdef SQLITE_DEBUG

The following lines are affected in the amalgamation source code of version
3.16.2:

17369
24482
24543
43985
63517
64886
74550
74655
80020
93533
179745

Please consider to fix this (or to adjust the documentation). Thanks.

Regards,

Ulrich

___
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

[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

[sqlite] Using My Own Encryption Possible ?

[Ulrich Telle]

Am 29.02.2016 um 16:44 schrieb a a:
> Thank you for your responces,
>
> I am looking for a non commercial way to make my own encrypted database file 
> if it can be done and if it can be easy by using any kind of encryption i 
> prefer with my own script
> I want to be able to enrypt and manage the database encrypted with my own 
> encryption like SEE commercial use .. thats what i am asking if it is possible

In addition to the official commercial SEE component, there are several 
non-commercial and commercial encryption extensions available for SQLite:

1) SQLCipher - https://www.zetetic.net/sqlcipher/
2) System.Data.SQLite - 
http://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki
3) wxSQLite3 - https://github.com/utelle/wxsqlite3

Just to name a few.

Regards,

Ulrich

[sqlite] Problem with SQLite version 3.9.2 together with user authentication extension

[Ulrich Telle]

Am 26.11.2015 um 01:30 schrieb Simon Slavin:
>
> On 25 Nov 2015, at 8:25pm, Ulrich Telle  wrote:
>
>> SELECT name, rootpage, sql FROM 'main'.sqlite_master ORDER BY rowid
>
> Can you try that again without the quotes around "main" ?
>
> SELECT name, rootpage, sql FROM main.sqlite_master ORDER BY rowid

No. The reason is that this is an SQL statement generated by SQLite 
itself internally while initializing an empty database.

In fact, I finally found out that the lines 379-384 in build.c (or 
93143-93148 in the amalgamation) are the cause of the problem:

#if SQLITE_USER_AUTHENTICATION
   else if( pParse->db->auth.authLeveldb->auth.authLevel!=0 && 
pParse->db->auth.authLeveldb->auth.authLevel==UAUTH_Unknown ){
 sqlite3UserAuthInit(pParse->db);
   }
   if( pParse->db->auth.authLevel

[sqlite] Problem with SQLite version 3.9.2 together with user authentication extension

[Ulrich Telle]

I further tracked the problem down to lines 379 to 384 in build.c:

#if SQLITE_USER_AUTHENTICATION
   else if( pParse->db->auth.authLeveldb->auth.authLevel != 0 && 
pParse->db->auth.authLevel SQLite version 3.8.7 introduced the user authentication extension. This
> extension worked well for all 3.8.x versions.
>
> Now I intended to upgrade to SQLite version 3.9.2. However, if I enable
> the user authentication extension using the compile time define
> SQLITE_USER_AUTHENTICATION I experience problems.
>
> If I start with a new, empty database (using the SQLite shell), it
> should not require user authentication. However, I always get the error
> message
>
> Error: SQL logic error or missing database
>
> as soon as I enter for example a "create table" statement.
>
> I tracked it down to a call of function sqlite3Parser in function
> sqlite3RunParser. The statement to be parsed is
>
> SELECT name, rootpage, sql FROM 'main'.sqlite_master ORDER BY rowid
>
> The call is in line 461 of tokenize.c:
>
> sqlite3Parser(pEngine, TK_SEMI, pParse->sLastToken, pParse);
>
> This function returns with a return code 1 in pParse->rc. And
> pParse->zErrMsg contains "user not authenticated".
>
> Is this a bug in SQLite somewhere? Or am I doing something wrong?
>
> Regards,
>
> Ulrich

[sqlite] Problem with SQLite version 3.9.2 together with user authentication extension

[Ulrich Telle]

SQLite version 3.8.7 introduced the user authentication extension. This 
extension worked well for all 3.8.x versions.

Now I intended to upgrade to SQLite version 3.9.2. However, if I enable 
the user authentication extension using the compile time define 
SQLITE_USER_AUTHENTICATION I experience problems.

If I start with a new, empty database (using the SQLite shell), it 
should not require user authentication. However, I always get the error 
message

Error: SQL logic error or missing database

as soon as I enter for example a "create table" statement.

I tracked it down to a call of function sqlite3Parser in function 
sqlite3RunParser. The statement to be parsed is

SELECT name, rootpage, sql FROM 'main'.sqlite_master ORDER BY rowid

The call is in line 461 of tokenize.c:

sqlite3Parser(pEngine, TK_SEMI, pParse->sLastToken, pParse);

This function returns with a return code 1 in pParse->rc. And 
pParse->zErrMsg contains "user not authenticated".

Is this a bug in SQLite somewhere? Or am I doing something wrong?

Regards,

Ulrich

[sqlite] Temporary table unexpectedly disappears

[Ulrich Telle]

Am 17.08.2015 um 12:32 schrieb Richard Hipp:
> On 8/17/15, Ulrich Telle  wrote:
>> I use version 3.8.11.1 of SQLite in my application. The application
>> opens a database connection on start up. This connection is kept open
>> until the application is terminated.
>>
>> After opening the database a temporary table is created. Accessing this
>> temporary table works as expected directly after it has been created.
>> However, a few seconds (5-10) later I get an error from SQLite on trying
>> to access the temporary table again, stating that the temporary table
>> doesn't exist. My database connection was *not* closed in between, and
>> the temporary table was *not* dropped explicitly.
>>
>> As far as I understood the SQLite documentation, a temporary table is
>> automatically destroyed only on closing the database connection. So I
>> wonder what could cause the temporary table to disappear unexpectedly?
>>
>
> The TEMP tables are reset by both:
>
>   PRAGMA temp_store=;
>   PRAGMA temp_store_directory=;

Thanks a lot. That's the explanation. Indeed, I used "pragma temp_store" 
*after* creating the temporary table. The documentation of the pragma 
clearly states this behaviour. However, I added the use of this pragma 
quite long ago (and forgot about it), and only recently added an 
additional temporary table without paying attention to the fact that the 
application issues "pragma" commands elsewhere later on.

Maybe a small note could be added to this documentation page, that not 
only closing the database connection but also "pragma temp_store" 
deletes all existing temporary tables:

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

Regards,

Ulrich

[sqlite] Temporary table unexpectedly disappears

[Ulrich Telle]

I use version 3.8.11.1 of SQLite in my application. The application 
opens a database connection on start up. This connection is kept open 
until the application is terminated.

After opening the database a temporary table is created. Accessing this 
temporary table works as expected directly after it has been created. 
However, a few seconds (5-10) later I get an error from SQLite on trying 
to access the temporary table again, stating that the temporary table 
doesn't exist. My database connection was *not* closed in between, and 
the temporary table was *not* dropped explicitly.

As far as I understood the SQLite documentation, a temporary table is 
automatically destroyed only on closing the database connection. So I 
wonder what could cause the temporary table to disappear unexpectedly?

Regards,

Ulrich

Re: [sqlite] Encrypted database

[Ulrich Telle]

Am 16.12.2014 17:44, schrieb Keith Medcalf:



Most freely available encryption extensions use a hard coded encryption
method. This is true for System.Data.SQLite (128 bit RSA), SQLCipher
(256 bit AES), and wxSQLite3 (128 or 256 bit AES, decided at compile
time) to name a few. The official commercial SQLite Encryption Extension
(SEE) allows to select an encryption method at runtime, this is done by
using a method prefix in the encryption key passed to function
sqlite3_key.


RSA?  As in Rivest-Shamir-Adleman?  I have seen this on the list multiple times 
now and I doubt very much that RSA is used.  It must be slower than molasses 
running uphill in Siberia in January ... which is why RSA is never used for 
data encryption.  Encryption of the symmetric key maybe, but of the data?  And 
128-bit RSA has a security lifetime of about 38 seconds, so it is little better 
than XOR obfuscation.

Are you sure you do not mean RC4 or RC5?


You are right: the encryption algorithm used by System.Data.SQLite is RC4.

I mixed it up with the PROV_RSA_FULL provider type used in acquiring the 
crypting context. Sorry for any confusion this caused.


Regards,

Ulrich


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

Re: [sqlite] Encrypted database

[Ulrich Telle]

Am 16.12.2014 14:03, schrieb jus...@postgresql.org:

On 2014-12-16 11:42, Harmen de Jong - CoachR Group B.V. wrote:

We use Navicat for SQLite and simply replaced the sqlite.dll file
with our own compiled SQLite dll version that has the proper
encryption included. Maybe this will work for you too and allows you
to use a management tool that is quite feature rich. Maybe this
approach will work for other management tools too.


H... trying to visualise how that works... :)

How do you specify encryption options (eg password, key size, etc),
if the application itself doesn't know to pop open a dialog asking
for them?

Guessing you hard coded the options into the SQLite dll file you
compiled?


The official SQLite version contains stubs for encryption support, 
namely functions sqlite3_key & sqlite3_rekey resp. sqlite3_key_v2 & 
sqlite3_rekey_v2. These functions are used to establish encryption for a 
database (sqlite3_key) or to change the encryption key for a database 
(sqlite3_rekey).


Most management tools supporting encryption use this "official" 
encryption API. If the management tool uses this API and a SQLite DLL to 
connect to an encrypted SQLite database, then the DLL could be exchanged 
for one supporting the required encryption method.


Most freely available encryption extensions use a hard coded encryption 
method. This is true for System.Data.SQLite (128 bit RSA), SQLCipher 
(256 bit AES), and wxSQLite3 (128 or 256 bit AES, decided at compile 
time) to name a few. The official commercial SQLite Encryption Extension 
(SEE) allows to select an encryption method at runtime, this is done by 
using a method prefix in the encryption key passed to function sqlite3_key.


Regards,

Ulrich


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

Re: [sqlite] Encrypted database

[Ulrich Telle]

Am 15.12.2014 11:37, schrieb jus...@postgresql.org:

On 2014-12-13 19:29, Mujtaba Ali Panjwani wrote:

I have created an encrypted database using visual studio plugin of
system.data.sqlite. Now whichever other database management software than
VS, I try to open that database is failing. Can any of you please help me
with issue. I suspect this is some sort of encryption algorithm support
problem but I don't know how to resolve the issue.


We have a similar open issue with DB Browser for SQLite:

   https://github.com/sqlitebrowser/sqlitebrowser/issues/155

Apparently (from that issue), the encryption algorithm used by
System.Data.SQLite is SQLCipher:

   https://www.zetetic.net/sqlcipher/


No, at least the *original* component System.Data.SQLite, available from

https://system.data.sqlite.org

uses its own encryption implementation based on a 128-bit RSA cipher 
(using the Windows Crypto API), while SQLCipher uses a 256-bit AES 
cipher (using the OpenSSL library). SQLCipher provides a .Net interface 
(SQLCipher for ADO.NET), which is *based on System.Data.SQLite*, that 
is, the SQLCipher guys used the .Net wrapper part of System.Data.SQLite, 
but replaced the internal implementation of the encryption algorithm by 
their own implementation.


The page

https://www.zetetic.net/sqlcipher/sqlcipher-ado/

explains how to use SQLCipherfor ADO.Net together with Visual Studio.

Unfortunately the various encryption extensions for SQLite are not 
compatible, so the developer has to take care that the development 
environment and the database management tools intended to be used 
support the same encryption extension.


Regards,

Ulrich


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

[sqlite] Using authorizer callback together with user authentication module in SQLite version 3.8.7

[Ulrich Telle]

I'd like to use the authorizer callback together with the new user 
authentication module introduced in SQLite version 3.8.7. However, I 
experience problems in compiling my application using a SQLite library 
compiled with SQLITE_USER_AUTHENTICATION defined.


sqlite3.h defines the sqlite3_set_authorizer as

SQLITE_API int sqlite3_set_authorizer(
  sqlite3*,
  int (*xAuth)(void*,int,const char*,const char*,const char*,const char*),
  void *pUserData
);

However, if SQLITE_USER_AUTHENTICATION  is defined the xAuth function 
takes an additional 7th parameter to pass the name of the authenticated 
user. My xAuth callback function is now implemented with 7 parameters, 
but I get an error message on compiling due to the - now wrong - 
definition in sqlite3.h:


error C2664: 'sqlite3_set_authorizer' : cannot convert parameter 2 from 
'int (__cdecl *)(void *,int,const char *,const char *,const char *,const 
char *,const char *)' to 'int (__cdecl *)(void *,int,const char *,const 
char *,const char *,const char *)'


How can I overcome this problem?

Shouldn't sqlite3.h define the function pointer argument of 
sqlite3_set_authorizer with an additional const char* parameter, in case 
SQLITE_USER_AUTHENTICATION is defined?


Regards,

Ulrich

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

Re: [sqlite] to encrypt sqlite db

[Ulrich Telle]

Am 02.09.2013 06:11, schrieb Etienne:

wxSQLite3 does implement AES in ECB mode


Wrong. CBC mode is used.

Regards,

Ulrich


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

Re: [sqlite] to encrypt sqlite db

[Ulrich Telle]

Am 01.09.2013 18:40, schrieb Etienne:

wxSQLite is free, while SEE is definitively not.


The original poster searched for a free encryption extension, of which 
there exist several: System.Data.SQLite (RC4), wxSQLite3 (AES-128 or 
AES-256), SQLCipher (AES-256 with nonce) to name a few.


Depending on the security requirements one of the free solutions may be 
good enough for the OP.



wxSQLite means "pseudo" encryption (as formerly discussed), while SEE is real 
encryption.


This statement is nonsense. Certainly SEE provides stronger encryption 
than wxSQLite3, however, the encryption used by wxSQLite3 is also "real" 
AES encryption (not "pseudo", which implies "easy to  break").


Regards,

Ulrich


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

Re: [sqlite] to encrypt sqlite db

[Ulrich Telle]

Am 31.08.2013 22:01, schrieb Etienne:

  On Sat, 31 Aug 2013 17:17:23 +0200
  Etienne  wrote:

  > > On the other hand removing patterns definitely cannot hurt.
  >
  > Precisely.
  >
  > The very first bytes of SQLite files are, AFAIK, well known.

  That's what salt is for, no?


"nonce", "IV", "salt" - call it whatever you want.  Yes.


I simply wanted to warn the OP that wxSQLite, while free, does NOT use
salts:


Well, that's not completely true. The encryption extension coming with 
wxSQLite3 uses a different IV (initial vector) for each database page. 
True is that the IVs are not random, but deduced from the page number. 
However, I don't see much difference between generating an IV 
algorithmic or using a random nonce which is stored at the end of each 
database page as SEE does according to the documentation to be found 
here: http://www.sqlite.org/see/doc/trunk/www/readme.wiki


In both cases you know the IV - at least if you have access to the code 
generating it (which is the case for wxSQLite3 as it is open source, but 
not for SEE as it is closed source).


The weak point of probably all SQLite encryption methods is that the 
unencrypted content of the first 16 bytes of a SQLite database file is 
well known. To get a better encryption maybe the first 16 bytes should 
always be kept unencrypted to not give a clue to a known pattern.



2 SQLite DBs built exactly the same way at different times are
byte-to-byte identical.


That's true. However, in real life it probably matters only for 
read-only databases. As soon as different people start to work with such 
an encrypted database, the database files will certainly deviate from 
each other considerably as it is very unlikely that all users perform 
their actions in exactly the same order.


BTW, you only get identical database files if you use the same 
passphrase. If the passphrase is hardcoded into the software then 
certainly this may impose a problem. However, for an application with 
high security demands you certainly shouldn't hardcode the passphrase 
into the software.


Regards,

Ulrich


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

Re: [sqlite] Problem with sqlite and codeblocks (mingw)

[Ulrich Telle]

Am 28.01.2012 17:07, schrieb Robert Gdula:

No becuase, I need encryption and it is not supported bu wxslite3,


Not true. wxSQLite3 supports encryption using the official SQLite API. 
wxSQLite3 even provides it's own SQLite encryption extension 
implementation (supporting 128 bit and 256 bit AES encryption), but you 
may also use other encryption implementations as long as they use the 
same SQLite API.


Regards,

Ulrich

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

Re: [sqlite] Example/Tutorial for "extension_functions.c" in C/C++ Prog With "sqlite3.c"

[Ulrich Telle]

Am 15.05.2011 20:43, schrieb Amy and Steve:
> Thanks!  I will study the sqlite3_auto_extension closer.  I had trouble
> compiling the extc file, but I can't remember what the errors were
> and I just got back in town, so I will have to work my back to trying to
> compile it.

I remember I had also some problems in compiling the extension 
functions' module, but after applying some minor changes I managed to 
compile it together with my implementation of an encryption extension. 
You find the modified version of extensionfunctions.c here:

http://wxcode.svn.sourceforge.net/viewvc/wxcode/trunk/wxCode/components/wxsqlite3/sqlite3/secure/src/codec-c/

> On 05/10/2011 01:24 PM, Mays, Steve wrote:
>>> Question 1.)  Can "exentension_functions.c" be compiled along with
>>> "sqlite3.c" into one executable?
>>>
>>> Question 2.)  If so, how?
>>>
>>> Question 3.)  If "exentension_functions.c" be compiled into one
>>> executable along with "sqlite3.c", do I need to do anything special or
>>> will sqlite3_exec() know what to do with queries like:
>> You can compile together into one binary but the SQLite main code won't know
>> that your code co-exists.  There are two options available to you:
>>
>> 1 - [Not recommended] Patch the internal openDatabase method to call your
>> init method (this is how the extensions distributed with SQLite are added).
>>
>> 2 - Call sqlite3_auto_extension from your code that does SQLite 
>> initialization.

There is a 3rd option using the C preprocessor to your advantage without 
the need to change the original SQLite amalgamation sources. Take a look 
at file sqlite3secure.c in the above mentioned SVN repository.

Regards,

Ulrich

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

Re: [sqlite] SQLite Explorer (singular) is missing the STDEV function (standard deviation)

[Ulrich Telle]

Am 01.04.2011 15:44, schrieb Mr. Puneet Kishor:
>> See http://www.sqlite.org/contrib  extension-functions.c
> 
> Is there a guide on how to compile the above extension-functions.c
> into sqlite, so it is always available without have to do load the
> extension explicitly?
> 
> Pointers appreciated.

My component wxSQLite3 (a SQLite wrapper for wxWidgets applications)
includes the sources for SQLite with support for the mentioned extension
functions without requiring to load the extension. All what's required
is to compile a single C source file (which in turn includes all other
necessary source files) with the symbol SQLITE_ENABLE_EXTFUNC defined.

The source code can be downloaded from here:

http://sourceforge.net/projects/wxcode/files/Components/wxSQLite3/

And here you find some additional information which might be helpful
for building SQLite:

http://wxforum.shadonet.com/viewtopic.php?t=27217

Regards,

Ulrich

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

Re: [sqlite] SQLite3 and threading

[Ulrich Telle]

Am 03.02.2011 15:53, schrieb Pavel Ivanov:
> It seems that this explanation as well as all other statements in the
> thread you linked are coming from the wrong assumption that SQLite's
> handles cannot be used from any thread other than the one created that
> handle.

The explanation I gave to Stefano is *not* based on the assumption that
SQlite handles can't be passed from one thread to another. But certainly
passing them around *can* lead to problems.

> Although this was true in some earlier versions of SQLite it's
> not true in the current version. So if SQLite is compiled with
> THREADSAFE=1 (as mentioned in that thread) then you can do with it
> whatever you want. Just beware of possible data races and potentially
> uncommitted transactions because of some open statement handles. And
> if as you say there's no simultaneous access to the database from
> different threads then there's no difference in your usage pattern
> from single-threaded one.

I cite from http://sqlite.org/faq.html#q6:

"The restriction on moving database connections across threads was
relaxed somewhat in version 3.3.1. With that and subsequent versions, it
is safe to move a connection handle across threads as long as the
connection is not holding any fcntl() locks. You can safely assume that
no locks are being held if no transaction is pending and all statements
have been finalized."

The problem is that Stefano wants to pass a result set from one thread
to another. The result set has an associated SQLite statement handle
which is inherently *not* finalized. And that might cause trouble.

If access to the database handle is serialized as Stefano claims passing
the statement handle between threads shouldn't cause problems, true, but
in that case I don't see the advantages of having a separate thread over
using a global database access instance.

Regards,

Ulrich

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

Re: [sqlite] Crypto lib for Sqlite - suggest required

[Ulrich Telle]

Hi Marco,

> I am looking for a Crypto lib to encrypt the Sqlite Db in full but I would
> like something less expensive that provided from the Sqlite author.
> I have found some libs on www.sqlite-cript.com and www.sqlite-encrypt.com.
> I would like your opinion about these libs and any other you repute stable
> and fast.

There are several encryption options for free:

1) System.Data.SQLite (http://sqlite.phxsoftware.com)
   For Windows only.

2) SQLCipher (http://sqlcipher.net)
   Portable, depends on OpenSSL's libcrypto

3) wxSQLite3 (http://wxcode.sourceforge.net/components/wxsqlite3)
   Portable, self-contained.
   Although wxSQLite3 is a SQLite wrapper for wxWidgets applications
   the distribution contains the source of an encryption extension
   supporting AES-128 or AES-256 encryption, which can be build
   using the SQLite amalgamation source code. It doesn't depend on
   any external library. Pre-compiled binaries for Windows are included.

All 3 use the native encryption API provided by SQLite.

Regards,

Ulrich

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

Re: [sqlite] Security for SQLite

[Ulrich Telle]

Hi Gerald,

> I have a CD application that a vendor is suggesting that I use SQLite as
> the db.  My concern is security.  Currently we are using Access which is
> password protected, but very slow.  I have not found any information
> about having SQLite password protected.  Is it possible, and I just
> missed it?  And/or do you have another suggestion.

If your application is for Windows systems only, the following wrapper, 
which supports database encryption, might be of interest to you:

http://sqlite.phxsoftware.com

If you have portability in mind you might want to take a look at the 
portable encryption extension included in wxSQLite3 (a SQLite wrapper 
for the wxWidgets GUI library):

http://wxcode.sourceforge.net/components/wxsqlite3

Note: The encryption extension does NOT depend on wxWidgets.

Regards,

Ulrich

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

Re: [sqlite] Looking for a cryptographic library

[Ulrich Telle]

Hi,

> I'm writing an application that uses SQLite to store user's data,
> and need a library to crypt some stuff, including passwords and data.
> The goal is to crypt before insert and decript after extract tha data,
> so this last can't be seen by others who gain access to the SQLite
> dataBase.

I have written a crypt extension for SQLite which uses the same API as the 
commercial solution of D.R. Hipp. This extension crypts the whole database file 
so it's not even possible to analyze the database schema for unauthorized users.

This extension is distributed together with my wxWidgets component wxSQLite3, 
but it can be used without wxSQLite3 and wxWidgets.

It's downloadable from
 
http://wxcode.sourceforge.net/components/wxsqlite3/

You have to get the SQLite sources, too, to be able to build a SQLite 
DLL/library with encryption support.

One SQLite source file needs modification. The latest wxSQLite3 release 
includes the required modified files up to SQLite 3.3.17. If you decide to use 
my extension and need to support SQLite 3.4.x you may contact me by private 
mail.

> The application don't need military security level :-) 

The extension uses 128-Bit-AES encryption.

> The ideal is some freeware library although commercial products can also
> be considered. Of course the final product must be commercially
> distributable without patent issues. 

My extension may be used in commercial projects.

Regards,

Ulrich

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] May I ask why the source distribution mechanism was changed starting with 3.3.14?

[Ulrich Telle]

drh wrote:

> I'm still having trouble trying to understand how managing
> 60 separate code files is perceived to be easier than managing
> just 2 files (sqlite3.c and sqlite3.h).  It seems to me that
> the management problem gets much easier the fewer files there
> are to manage.

In the case someone wants to use SQLite unchanged, it is certainly easier to 
manage only 2 files than 60+. But there are people - like myself - who would 
appreciate to have a source distribution containing the preprocessed source 
files as separate files as it was before. Not instead of the amalgamation 
distribution, but additionally.

I don't know why others want or need the separate files, but I certainly can 
explain why I do: I have written an extension for SQLite using C++ which needs 
the preprocessed header files for compiling. Additionally I have to change the 
SQLite code at one place (essentially adding a single function call in the 
pager). The code change is not a big problem, but extracting the header files 
is quite inconvenient. Unfortunately I can't avoid to extract the header files 
since I can't merge my own code with SQLite code due to the fact, that SQLite 
is written in C and my extension in C++, and SQLite is not compilable in C++ 
mode.

So, I would be grateful if the separate file distribution would return in 
addition to the amalgamation distribution.

Regards,

Ulrich Telle

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite - how to protect the data

[Ulrich Telle]

Hi Roger,

> The problem isn't so much how you generate keys, but how you use them.
> Google shows over a million hits for 'pdf encryption crack'.

You're certainly right. But there is a difference between the PDF encryption 
and the SQLite encryption I implemented.

PDF encryption is designed in such a way that it's easily possible to decrypt a 
document if only access permissions are set but no password(s). For this 
purpose every PDF document contains two values (the U value and the O value) 
corresponding to the user password resp. owner password. But if a password is 
set it takes quite a while to crack it, although it's certainly not uncrackable.

My implementation for SQLite does not store such information into the database. 
So it should be a bit harder to crack the encryption. 

Nevertheless there is a weak point in my current implementation, namely the 
SQLite magic file header. Since this header has 16 bytes and has a (usually 
known) value it could be used to reconstruct the encryption key. I don't think 
it's trivial to do it, but I didn't investigate how much effort it would be to 
crack the encryption key using this information. And probably I'll change my 
implementation in the next version to not give away such a clue.

But, hey, my implementation is for free. If someone has really tight security 
constraints he or she should buy and use a commercial solution.

Regards,

Ulrich

-- 
Ulrich Telle
E-Mail privat: mailto:[EMAIL PROTECTED]
E-Mail Studium: mailto:[EMAIL PROTECTED]
Homepage: http://www.stud.fernuni-hagen.de/q1471341
Fax: +49-(0)12120-203070


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite - how to protect the data

[Ulrich Telle]

Hi Marek,

> Thanks Ulrich, but I think that goes beyond the scope of my knowledge
> and tools I have available to me.
> 
> I wish somebody (who has C++ knowledge) has done it already and
> released version of SQLite with encrypt and decrypt functionality.

Well, there is a prebuilt DLL with encryption support for Windows bundled with 
wxSQLite3.

> You make it sound like it shouldn't be a problem for someone who knows
> C++. But I am expecting a catch somewhere, otherwise why would there be
> commercial versions of SQLite which has this functonality.

The catch is that for non-commercial solutions you don't have a guarantee *how* 
secure your encrypted database will be. The weak point of encryption is how the 
encryption key is generated and whether it's hard to crack or not.

I have no proof how secure my solution is but the encryption key is generated 
in much the same way as it is for Adobe PDF. So I guess what's good enough for 
PDF should be good enough for SQLite. But I won't give you a guarantee.

> If you don't mind one more off topic question. You seem to have
> experience with wxWidgets. In tandem with wxPython is that a better
> tool to use than just Python and Tkinter. Which route is the easier
> one for a beginner in this language?

Although I know wxWidgets quite well I can't give a qualified answer to your 
question since I'm using C++ almost exclusively. But I heard wxPython to be 
very usable.

Regards,

Ulrich

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite - how to protect the data

[Ulrich Telle]

Hi Marek,

> I'll be honest with you I had to google around to find out what is
> wxWidgets (obviously I've seen it before, but never dipped in). Pls
> understand I've been messing around for years with VB (not even the
> .NET version) and it served me quite well. But I decided to move on
> to something rather free of Microsoft.

Well, wxWidgets _is_ free of Microsoft and is available for many different 
platforms. But it wasn't my intent to point you in the direction of wxWidgets.

My encryption solution for SQLite is not bound to wxWidgets in any way. You may 
as well use it completely independent of wxWidgets (or my component wxSQLite3).

You may just take the source code of my encryption extension from the file 
release of wxSQLite3, combine it with the sources of SQLite itself, compile 
with just any C++ compiler to get a SQLite library or DLL supporting database 
encryption. Please read the readme file of wxSQLite3 for further information.

My extension implements two functions of the SQLite API, namely sqlite3_key and 
sqlite3_rekey (see sqlite3.h header file of SQLite), which are not implemented 
in the normal SQLite distribution. To use encryption sqlite3_key has to be 
called immediately after the call to sqlite3_open. That's all.

Regards,

Ulrich

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: SQLite - how to protect the data

[Ulrich Telle]

Adriano schrieb:

> I'm in the same boat too.
> I've just found http://www.sqlcrypt.com/
> not tryed yet
> 
> i need something that can works either on pc or on windoes mobile
> devices

Since I was in need of encrypting my SQLite database, too, and could not 
afford a commercial solution I created my own SQLite encryption 
extension, based on experiences I gained developing encryption support 
for my wxWidgets component wxPdfDocument.

My SQLite encryption extension is written in C++ and should be 
compilable on any platform for which SQLite is available. The API is 
compatible with the commercial encryption solution offered by D.R. Hipp, 
(but not the encrypted database files, since I do not know the 
encryption algorithm used by D.R. Hipp). My algorithm is based on 
128-Bit-AES. The source code of the extension is distributed with the 
latest release (1.7.2) of wxSQLite3, my SQLite wrapper for wxWidgets:

http://sourceforge.net/project/showfiles.php?group_id=51305_id=45182_id=343361

Regards,

Ulrich

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Request for comment: Proposed SQLite API changes

[Ulrich Telle]

D. Richard Hipp wrote:
> "Ulrich Telle" wrote:
>> Sure, it would be nice if SQLITE_SCHEMA errors could be handled
>> internally by SQLite, but I think it's a non-trivial task to handle
>> this kind of error.
>> 
>> When I created my SQLite C++ wrapper wxSQLite3 I thought about handling
>> SCHEMA errors, but decided against it due to the complexities involved. 
>
> Isn't that really the whole point of a wrapper - to deal with
> complexities so that the user doesn't have to.

Of course a wrapper should hide as much of the complexities as possible. And
be assured my wrapper wxSQLite3 hides a lot of them.

> If you are passing all of the complexities up to the user,
> why use you wrapper at all? Just call SQLite directly.

Have I written somewhere my wrapper passes *all* complexities up to the
user??? Definitely not!

If I understood it right each of
sqlite3_prepare/sqlite3_step/sqlite3_finalize may return a SQLITE_SCHEMA
error. At least for a SELECT statement sqlite3_prepare is called *once*
_before_ the data of *each row* are fetched using sqlite3_step and
sqlite3_finalize is called *once* after all rows have been processed. A
wrapper will seldom hide this kind of processing. So the wrapper can not
know whether the application is processing the data of each row or
collecting the data for later processing elsewhere.

If the SQLITE_SCHEMA error occurs for example after already 10 rows were
processed. What should the wrapper - or SQLite (if you decide to handle
SCHEMA errors internally) - do? If you recompile the statement calling
sqlite3_prepare wouldn't you refetch *all* rows again (unless you have
counted the number of rows already fetched and now skip this number of rows
before returning)?

And what does it mean if the SQLITE_SCHEMA error occurs for the first time
when calling sqlite3_finalize? Then you have already processed all rows.

How on earth a wrapper could hide this from the user?

If it is trivial to handle SQLITE_SCHEMA errors then SQLIte should do it. If
not, why and how should a wrapper do it?

>> For INSERT, UPDATE or DELETE a simple retry might be a good choice. But
>> how often should the retry take place? The SQLite FAQ code example
>> contains an endless loop! 
>
> Not.  OK, I guess in theory, if another process were updating the
> schema at just the right rate so that the schema was different every
> times you retried, you could get an infinite loop.  But in practice,
> the loop never runs more than twice - 3 times in the extreme.

Coding infinite loops should be avoided even if there is only a theoretical
possibility it will ever loop forever.

>> In case of a SELECT statement the situation is still more complex. The
>> SCHEMA error could happen after reading several result rows.
>
> No.  SCHEMA errors happen prior to reading any data.

You mean a SQLITE_SCHEMA error can only occur when you try to read the data
of the *first* row of a SELECT query? And if you were able to read the first
row you will be able to read *all* rows?

Hard to believe but if that is truly the case then definitely SQLite should
handle this error internally. At least the documentation should be more
explicit about when a SQLITE_SCHEMA error may occur.

>> When retrying a query another problem arises if the SQL statement
>> contains bind variables. You would have to rebind the variables. To
>> handle this automatically would induce a lot of extra house keeping,
>> wouldn't it?
>
> See the sqlite3_transfer_bindings() API.

If SQLite already keeps track of all bindings it should keep a copy of the
SQL statement string, too.

Regards,

Ulrich Telle

[sqlite] Re: Request for comment: Proposed SQLite API changes

[Ulrich Telle]

> > Suppose this where to change in version 3.3.0 so that the
> > actual error code was returned by sqlite3_step().

That would be a good thing, since it would allow appropriate reaction at the
right time and the right place.

> > Then when a schema change occurred, the statement was automatically 
> > recompiled and rebound.  There would no more SQLITE_SCHEMA errors.
>
> This change should be done.  SQLITE_SCHEMA is all about
> an internal implementation detail in SQLite and shouldn't
> really be exposed to the users of SQLite.  There is only
> action that people take on getting it - rerun the query.
> Pretty much every wrapper does that anyway so it makes
> even more sense to make that the standard code in SQLite.

Sure, it would be nice if SQLITE_SCHEMA errors could be handled internally
by SQLite, but I think it's a non-trivial task to handle this kind of error.

When I created my SQLite C++ wrapper wxSQLite3 I thought about handling
SCHEMA errors, but decided against it due to the complexities involved. For
example which reaction is appropriate depends on the kind of the SQL
statement: 

For INSERT, UPDATE or DELETE a simple retry might be a good choice. But how
often should the retry take place? The SQLite FAQ code example contains an
endless loop! I didn't want to introduce such kind of code into wxSQLite3. -
Additionally the number of columns in a table used in these statements might
have changed, that is the statement would probably fail again.

In case of a SELECT statement the situation is still more complex. The
SCHEMA error could happen after reading several result rows. If you would
then redo the query automatically it would start from scratch delivering the
already read rows again. If your application code gathers the result set in
an array for example you would get duplicate rows. 

When retrying a query another problem arises if the SQL statement contains
bind variables. You would have to rebind the variables. To handle this
automatically would induce a lot of extra house keeping, wouldn't it?

If SQLite would be able to handle all these issues transparently, it would
be ok for me.

> If you are looking at API changes, the most beneficial to
> me would be a unification of sqlite3_value_TYPE and
> sqlite3_column_TYPE.  I have to write identical code to
> do my own type conversion when calling these function
> and duplicate it.  Similar story with sqlite3_result_TYPE
> and sqlite3_bind_TYPE.

Since C++ variables are typed my wrapper wxSQLite3 needs no type conversion.
It's the user's responsibility to use variables of the right type.

There is only one place where I would appreciate to have type information at
hand. sqlite3_get_table returns all values as character strings and there is
no way to find out the original value types.

Regards,

Ulrich Telle

-- 
Ulrich Telle
E-Mail privat: mailto:[EMAIL PROTECTED]
E-Mail Studium: mailto:[EMAIL PROTECTED]
Homepage: http://www.stud.fernuni-hagen.de/q1471341
Fax: +49-(0)89-1488-203070

[sqlite] Re: FYI: Fortran interface to SQLite

[Ulrich Telle]

Hi Arjen,

I would like to test your Fortran SQLite interface with the Lahey LF95
Windows Fortran compiler. Please let me know where I can get your code.

Regards,

Ulrich Telle

[sqlite] blob support

[Ulrich Telle]

SQLite supports the blob datatype, but one can access a blob value only 
as a whole. Is support planned to partially read or write a blob value? 
For example in an Oracle database one can select a blob reference from a 
table and can then operate on this blob reference: reading or writing 
parts of the blob, appending to a blob ... Such a blob interface would 
be nice to have in SQLite.

Kind regards,

Ulrich

-- 
Ulrich Telle
E-Mail privat: mailto:[EMAIL PROTECTED]
E-Mail Studium: mailto:[EMAIL PROTECTED]
Homepage: http://www.stud.fernuni-hagen.de/q1471341
Fax: +49-(0)89-1488-203070