Re: [sqlite] Compile time error when compiling extension shathree.c together with sqlite3.c for Android
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
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
> 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
Dominique Devienne wrote: > > On Tue, Feb 6, 2018 at 11:15 AM, Ulrich Telle 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
> 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
> 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
> 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
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 ?
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 ?
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 ?
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
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
Dominique, > I think on the contrary they are more meant to communicate across different > UDFs of the *same* extension, and not across extensions. You are most probably right. It seems very unlikely that unrelated extensions would exchange pointers. However, mainly the new interface will be used to communicate between the aplication and a SQLite extension. AFAIK one of the main reasons for the new interface was to prevent pointer maniulation via pure SQL (what was possible in older SQLite versions). With the new interface this is no longer possible. Instead, an attacker would need to inject a malicious extension - a lot more difficult than just executing some pure SQL statements. > I'm not even sure you can guarantee uniqueness of string literals across > dynamically loaded shared libraries, maybe the runtime linker > merges equivalent read-only "static" text from different libs, but that > seems very platform-dependent and not something to rely on. The string literals used for the pointer types don't have to be unique instances. It is sufficient that their content is identical. Not the string pointer is checked, but the string content. > When you look at it from this lens, then enforcing string literals for the > type-pointer makes sense. My $0.02, FWIW. --DD It's a design decision we will have to live with. I understand the reasoning behind the decision, but as I pointed out it is a low hurdle and does not prevent misuse, it just makes it slightly less likely to happen. > PS: Yes, like others I'd love it if DRH went further into this "custom > type" route, even persistent ones longer term with a format change, > since pointer-binding with type-string-pointer is going in that > direction, at least at runtime-only, and only for "temporaries" across UDFs, > but I'm afraid we're a long way from that, and I'm not holding my breadth > :) I don't believe that extensions of SQLite in this direction are to be expected in the near future. 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
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
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
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
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
Richard, > On 8/3/17, 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. 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
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
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
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
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
Gwendal, > > Le 3 août 2017 à 14:27, Peter Da Silva a > > écrit : > > > > On 8/3/17, 4:22 AM, "sqlite-users on behalf of Ulrich Telle" > > > 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
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
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
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
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
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
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
> > 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
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 ?
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
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
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
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
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
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
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
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
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
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
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
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
Am 01.09.2013 17:50, schrieb Clemens Ladisch: Without a random IV/nonce, every page is guaranteed to encrypt to the same data if the contents and the key have not changed. Thus, wxSQLite3 gives an attacker the ability to determine whether any particular page has changed, by comparing the old and new versions. With SEE, rewriting a page will encrypt to a different value because the IV changes even for otherwise unchanged pages. If SEE rewrites unchanged pages then this is certainly true. For applications with high security requirements SEE is certainly preferrable over wxSQLite3 (or other free SQLite encryption extensions like that in System.Data.SQLite, http://system.data.sqlite.org). Many file formats have fixed parts. However, this is not a problem with properly implemented encryption algorithms. Knowing the plain text corresponding to a certain encrypted part (especially at the beginning of the file) certainly does impose a problem. At least for AES-128 there are certainly chances to break the key based on the knowledge of the plain text of the SQLite database header. 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
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)
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"
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)
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
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
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
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
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?
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
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
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
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
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&package_id=45182&release_id=343361 Regards, Ulrich - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Request for comment: Proposed SQLite API changes
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
> > 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
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
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