Re: [sqlite] csv extension/feature requests: new options for field separator different from default ', ' and skip=N to skip first N lines
On 04.02.20 15:42, Simon Slavin wrote: On 4 Feb 2020, at 12:18pm, Robert M. Münch wrote: - sep=';': field separator character (different from default ',') If you provide this facility, please don't add it to anything called 'csv' since the 'c' stands for 'comma'. For those playing along at home, csv files using semi-colon are a result of a bug in Excel. Windows has a setting for a 'list separator'. The two most usual values are ',' and ';'. The CSV export filter in Excel takes its separator from this field rather than always using a comma, because it was written by someone who wasn't aware of, didn't understand, or was intentionally trying to disrupt the standard. Decades after being told about the bug, Microsoft hasn't fixed it. There are a couple of other errors in Excel's CSV filters including how strings are quoted and how a blank value differs from a zero-length string. The best way I've seen to handle this was to add a new filter to your software, similar to 'csv', called something like 'exceltext' which did things the Excel way. Believe it or not, there is no binding standard for the CSV format. The closest anyone has come was RFC 4180. However: According to RFC 4180, section 2: "While there are various specifications and implementations for the CSV format (for ex. [4], [5], [6] and [7]), there is no formal specification in existence, which allows for a wide variety of interpretations of CSV files." https://tools.ietf.org/html/rfc4180#section-2 In section 3, under "Interoperability considerations": "Due to lack of a single specification, there are considerable differences among implementations. Implementors should "be conservative in what you do, be liberal in what you accept from others" (RFC 793 [8]) when processing CSV files." https://tools.ietf.org/html/rfc4180#section-3 That being said, the problem with trying to enforce the comma as the sole delimiter character is due to the fact that over half of the non-English speaking world (or perhaps even more) uses the comma as the decimal separator. The "work-around" for that, of course, would be to enclose all fields in double quote characters. But, as we know, the 800-pound gorilla in the room doesn't necessarily do that... I agree that this would be a very good option to have. In the meantime, check out libcsv on GitHub: https://github.com/rgamble/libcsv It adheres as closely to what standards there are, and you can choose your own delimiter and quote character if you like. Of course, you have to do some programming to use it, but it's really easy to use. And it is very fast since it does just one thing, but does it very well. HTH, Bob Hairgrove ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi layer JSON query
On 20.12.19 04:45, No.1 Perfect wrote: I've read the full page instructions of JSON1, But I don't know how to use JSON1 to query multiple layers JSON data. The Data as follows : { id: 1, data: { customer: 1, goods: [ { id: 1, name: "apple", price: 12, num: 10, amount: 120 }, { id: 2, name: "perl", price: 10, num: 5, amount: 50 } ] } } (snipped...) Two suggestions come to mind: 1. Normally, JSON data is not displayed directly in a web page, so all the (entity references for non-breaking space) shouldn't be in there (and probably is not valid JSON, anyway). 2. If you do need to display the JSON code for some reason, use regular spaces and line feeds and wrap it in HTML tags. Unfortunately, I never used JSON queries with SQLite, so others will have to help after this point. HTH, Bob Hairgrove ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to delete BLOB object from the data base
On 15.07.19 21:38, Zhu, Liang [AUTOSOL/ASSY/US] wrote: ... I am inserting to the BLOB data into the table at every 250ms, I delete the oldest row at every 600ms, also I am reading the data from the database at every 10ms... How do you determine the "oldest" row? I believe the timestamps generated by SQLite are only accurate to the nearest second. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] the sqlite3 documentation would be pretty good if itwasn't tragic...
On 26.06.19 18:58, a...@zator.com wrote: Irreproachable argumentation, which in my humble opinion is little or nothing useful to those who want to enter in the diabolic world of SQL. Especially, if you have not yet managed to change the chip and find out that for example, you must carry out a program without using variables. All the programming gurus I've read, agree that the best way to master a language (SQL is) is to read code from good programmers and I do not remember anyone who says that you ask in the forums when you have any questions, and the sad reality is that it is difficult to find examples of SQL, apart from being attentive to these pages where sometimes you learn a lot in the code of some answers. I understand and empathize absolutely with the O.P. and must add that in the documentation of SQLite, I have always missed examples and comments that, for example, can be found in the PHP doc. Just a thougth. -- A. J. Millan. After working for several years in the field (having worked with dBase, MS-Access, Oracle, DB2 (including DB2 on IBM-AS/400) and MySQL database applications BEFORE doing any work with SQLite), I find myself constantly going back to two books which I consider the "Old" and the "New" Testaments of the SQL bible, if there is such a thing: Old: "An Introduction To Database Systems" by C. J. Date, ISBN 0-201-82458-2 (I have the 6th edition); New: "SQL For Smarties: Advanced SQL Programming" by Joe Celko, ISBN 1-55860-576-2 (I have the 2nd edition). Then there are the SQL ANSI/ISO standards documents themselves, but I wouldn't look for many hands-on examples there. Of the two books I just mentioned, Joe Celko's book has the most abundant real-world examples, but the Date book also has lots of examples albeit on a more basic level. Caveat: I have read that the latest edition(s) of Joe's book unfortunately has many typo's, but you should be able to work around those IMHO. SQL is, IMHO, very much a "learning by doing" language. The vendor or programmer of an SQL RDBMS implementation should not be expected to do much in the way of tutorials except where their implementation might deviate (or expand upon) the functionality required by the standards they claim to implement. HTH, Bob Hairgrove ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Change delimiter with CSV extension?
On 30.05.19 15:16, Richard Hipp wrote: On 5/30/19, Tony Papadimitriou wrote: Is it possible to change the delimiter with the CSV virtual table extension? No, not at run-time. You could, of course, edit the source code and recompile, but I assume you are looking for a run-time parameter, and there is none. The delimiters are hard-coded. I've had some success working with this little library together with SQLite in projects built in C and C++ languages: https://github.com/rgamble/libcsv It allows changing the quote and delimiter characters at runtime, at least. And it adheres to the RFC "standards". But I would imagine that it takes a little more work to use than the SQLite extension. HTH, Bob Hairgrove ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [OT] UTF8-BOM and text encoding detection (was: UTF8-BOM not disregarded in CSV import)
On Tue, 2017-06-27 at 16:38 +0200, Eric Grange wrote: > > > > ASCII / ANSI is a 7-bit format. > ASCII is a 7 bit encoding, but uses 8 bits in just about any > implementation > out there. I do not think there is any 7 bit implementation still > alive > outside of legacy mode for low-level wire protocols (RS232 etc.). I > personally have never encountered a 7 bit ASCII file (as in > bitpacked), I > am curious if any exists? If an implementation "uses" 8 bits for ASCII text (as opposed to hardware storage which is never less than 8 bits for a single C char, AFAIK), then it is not a valid ASCII implementation, i.e. does not interpret ASCII according to its definition. The whole point of specifying a format as 7 bits is that the 8th bit is ignored, or perhaps used in an implementation-defined manner, regardless of whether the 8th bit in a char is available or not. Once an encoding embraces 8 bits, it will be something like CP1252, ISO-8859-x, KOI-R, etc. Just not ASCII. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [OT] UTF8-BOM and text encoding detection (was: UTF8-BOM not disregarded in CSV import)
On Tue, 2017-06-27 at 12:42 +0200, Eric Grange wrote: > In the real world, text files are heavily skewed towards 8 bit > formats, > meaning just three cases dominate the debate: > - ASCII / ANSI > - utf-8 with BOM > - utf-8 without BOM ASCII / ANSI is a 7-bit format. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [OT] UTF8-BOM and text encoding detection (was: UTF8-BOM not disregarded in CSV import)
On Tue, 2017-06-27 at 01:14 -0600, Scott Robison wrote: > The original issue was two of the largest companies in the world > output the > Byte Encoding Mark(TM)(Patent Pending) (or BOM) at the beginning of > UTF-8 > encoded text streams, and it would be friendly for the SQLite3 shell > to > skip it or use it for encoding identification in at least some cases. I would suggest adding a command-line argument to the shell indicating whether to ignore a BOM or not, possibly requiring specification of a certain encoding or list of encodings to consider. Certainly this should not be a requirement for the library per se, but a responsibility of the client to provide data in the proper encoding. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [OT] UTF8-BOM and text encoding detection (was: UTF8-BOM not disregarded in CSV import)
On Tue, 2017-06-27 at 01:14 -0600, Scott Robison wrote: > On Jun 27, 2017 12:13 AM, "Rowan Worth" wrote: > > I'm sure I've simplified things with this description - have I missed > something crucial? Is the BOM argument about future proofing? Are we > worried about EBCDIC? Is my perspective too anglo-centric? Thanks, Scott -- nothing crucial, it is already quite good enough for 99% of use cases. The Wikipedia page on "Byte Order Marks" appears to be quite comprehensive and lists about a dozen possible BOM sequences: https://en.wikipedia.org/wiki/Byte_order_mark Lacking a BOM, I would certainly try to rule out UTF-8 right away by searching for invalid UTF-8 characters within a reasonably large portion of the input (maybe 100-300KB?) before then looking for any NULL bytes (which are also invalid UTF-8 except as a delimiter) or other random control characters. As to having the user specify an encoding when dealing with something which should be text (CSV files, for example) and processing files which the user has specified, there is always the possibility that the encoding is different than what the user says, mainly because they probably clicked on a spreadsheet file with a similar name instead of the desired text file. If the user specifies an 8-bit encoding aside from Unicode, it gets very difficult to trap wrong input unless you write routines to search for invalid characters (e.g. distinguishing between true ISO-8859-x and CP1252). ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to circumvent UNIQUE constraint
On Sat, 2017-01-21 at 11:54 +0100, Cecil Westerhof wrote: > I have the following (work in progress) table: > CREATE TABLE desktops( > nameTEXTNOT NULL PRIMARY KEY, > indexNo INTEGER NOT NULL UNIQUE, > value TEXTNOT NULL UNIQUE, > waitSeconds INTEGER NOT NULL > ); > > I want to insert a record in front of the others, so indexNo has to > be > increased with one for all records. I would think that this would > work: > UPDATE desktops > SET indexNo = indexNo + 1 > > But it does not, it gives: > Error: UNIQUE constraint failed: desktops.indexNo > > How can I make this work? > I don't think this will work in a single SQL statement. If you start with the largest value of indexNo and work in descending order, it should work. However, this would typically be done in a procedural loop where you can depend on the ordering of a cursor. Maybe somebody knows a clever SQL trick to do it in a single statement? The problem is that you could build a subquery to return the "hole", i.e. the next indexNo to update, but you cannot modify the same table which is used in a subquery of the same UPDATE statement. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performing a SELECT on an enormous database...
On Fri, 2014-10-24 at 19:09 -0400, Ross Altman wrote: > I'm currently working with a pretty gigantic database (116 Gb at the > moment, and growing). Performing a simple SELECT routine with one filter > takes between 7 and 15 minutes, which is starting to become a problem. The > command I'm using is the following: > > SELECT NVERTS FROM ToricCY WHERE H11=2; Questions: 1) Are you querying a local database file, or pulling the data in over a network connection? 2) Is there an index defined for the column H11? > Pretty basic. I just want the NVERTS column of the table ToricCY where > another column labeled H11 is 2, nothing fancy. Because of the huge size of > the database, I would expect it to take a while, but I was wondering if you > guys have any recommendations on how to speed it up (if possible). > > Also, because this problem will only get worse as the database grows, I'm > looking for alternative approaches to storing this data. Does anyone have > experience working with databases this big? 116 GB seems to be at odds with a database system named "SQLite". :) But most popular client/server RDBMS's should be able to handle it with no problem. MySQL, for example, supports table partitions which can have an enormous impact on performance. With SQLite, you can approximate partitioning by splitting the database into several smaller ones and use the ATTACH command to run queries over several databases at once. Hope this helps! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] comma-separated string data
You need to normalize the database design. -- On Fri, 2014-04-04 at 14:20 -0400, peter korinis wrote: > A data column in a link table contains comma-separated string data, where > each value represents a value to link to another table. (many-to-many > relationship) > > > > How do you 'parse' a table entry like: "4,66,51,3009,2,678, ." to extract > these values and use them in an SQL statement, perhaps a WHERE id='66'? > > > > Thanks, > > peter > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bullzip ms Access to MySql
On Wed, 2013-03-06 at 21:36 +, ven...@intouchmi.com wrote: > ENGINE=myisam DEFAULT CHARSET=utf8; Try removing the bit in the above quote. This is MySQL-specific code. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fwd: How to prevent View sqlite database structure and contents from database browsers
On Mon, 2013-02-11 at 11:04 +, SR Volatile wrote: > Is there any option to encrypt fields in a table when creating it? There is the SQLite Encryption Extension (SEE): http://www.hwaci.com/sw/sqlite/see.html It is not free. But it looks like it does a pretty good job (I never used it myself). Other than that, you can encrypt/decrypt the data in your application using some 3rd party encryption software. Depending on your security needs, please be aware that no encryption mechanism is 100% safe, and to achieve an adequate level of security through encryption is anything but trivial. Of course, my suggestion to make the file read-only does not prohibit users from reading the data, only from editing it. You'd have to deny read AND write access to any users except for those using the database to prevent all prying eyes. Although setting up such permissions is fairly straightforward on a *nix system, I believe that more recent versions of Windows allow user-level permissions as well. And I still think this is probably the best way to go with any file-based RDBMS because once the file-system security is breached, the database file can be copied and a dedicated intruder can hack away at leisure to remove whatever encryption there is. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fwd: How to prevent View sqlite database structure and contents from database browsers
On Fri, 2013-02-08 at 13:13 +, SR Volatile wrote: > Dear Sir/Madam, > > I am using Sqlite for my project. Currently, Sqlite database browser able > to view / edit sqlite database structure and content. As part of my > project, I don't want anybody to view/edit the database contents from any > database browsers. > Could you please suggest me, how can i achieve this? Since an SQLite database is a file, you could just set the file system privileges to read-only. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [sqlite-dev] Amalgamation code doesn't supoort limit on update / delete
On Sun, 2011-03-13 at 14:53 -0400, Pavel Ivanov wrote: > On Sun, Mar 13, 2011 at 2:46 PM, Steven Hartland > wrote: > > After spending many hours banging my head trying to figure out why > > sqlite-jdbc was erroring on a delete with a limit even when compiled > > with SQLITE_ENABLE_UPDATE_DELETE_LIMIT I finally tracked it down > > to the fact that the Amalgamation download is simply missing half > > the code to support it :( > > > > Compiling from standard source, in this case from FreeBSD ports > > results in a sqlite3.c which has much more code that references > > the options to support update / delete limits than the standard > > Amalgamation version. > > > > I can't find anything that says this is a know limitation so can > > only assume its an oversight / bug, is this the case? > > http://lmgtfy.com/?q=SQLITE_ENABLE_UPDATE_DELETE_LIMIT+site%3Asqlite.org&l=1 > > If you look for description of SQLITE_ENABLE_UPDATE_DELETE_LIMIT on > the resulting page you'll see that it's not a bug or oversight, it's a > documented feature. You could write something like this instead: UPDATE some_table SET some_field = ? WHERE the_id IN ( SELECT the_id FROM some_table WHERE [...] LIMIT [...]); assuming that "the_id" is either the primary key column or else has a unique index. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with a query
On Fri, 2011-03-04 at 11:10 +0100, Marco Bambini wrote: > Hello, > I have a table defined as: > CREATE TABLE MKProperties (id INTEGER PRIMARY KEY AUTOINCREMENT, obj_id > INTEGER, prop_key TEXT, prop_value TEXT, UNIQUE(obj_id, prop_key)) > > In that table there some rows like: > obj_idprop_keyprop_value > 1 PARENTID0 > 1 RESOURCE_ORDER 0 > 2 PARENTID0 > 2 RESOURCE_ORDER 1 > 3 PARENTID0 > 3 RESOURCE_ORDER 3 > > I need a query that returns all the obj_id with prop_key='PARENTID' AND > prop_value='0' but ordered by prop_value WHERE prop_key='RESOURCE_ORDER'. > Any help? Sounds like a job for a self-join. Try this: SELECT T1.obj_id, T2.prop_value FROM MKProperties T1 INNER JOIN MKProperties T2 ON (T1.obj_id = T2.obj_id) WHERE T2.prop_key = 'RESOURCE_ORDER' AND T1.prop_key = 'PARENT_ID' AND T1.prop_value = 0 ORDER BY T2.prop_value; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] adding fdopen to VFS?
On Mon, 2011-02-28 at 14:33 -0500, Richard Hipp wrote: > On Mon, Feb 28, 2011 at 1:00 PM, Paweł Hajdan, Jr. [many interesting things snipped ...] > wrote: > > What do you think about adding a call like below to the VFS interface? > > > > int (*xOpenWrapper)(const char *path, int flags, int mode); > > > > There are 2 billion instances of SQLite in active use today. We don't want > to go changing interfaces unless we absolutely have to. > > And there are additional complications. Based on what we can figure out > about how your sandbox works, you'll also need overloads for access(), > stat(), and unlink(). Presumably you have no need for dlopen() which is > used for run-time loadable extensions to SQLite, but if you use that > features you'll need an overload for it too. We might yet think of others, > but already we see there are at least three interfaces (we could probably > combine access and stat) that need to be overloaded. > > We are also concerned about locking. The documentation on Chromium > sandboxing implies that a separate process does the open() and then sends > over a file descriptor using IPC. (I didn't know you could do that in > unix. In fact, I bet it requires a linux-only extension of some kind. > Correct me if I'm wrong.) But that makes us very concerned about how posix > advisory locking will behave. Posix advisory locks are mis-designed. See > > http://www.sqlite.org/src/artifact/64a2e0ebbb?ln=621,685 > > Are you sure that posix advisory locking will work on a file descriptor that > was allocated in a different process? Have you tested it? > > So this all gets very complicated very fast. And very hard to test. I'm only beginning to delve into the possibilities offered by the VFS API in SQLite. However, from what I have seen so far, it seems like a very complete abstraction to me. Why is it not possible to create a custom VFS for the intended purpose and register it for the connections that need it? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VFS
It appears that Qt (or more accurately, WebKit) had defined SQLITE_OMIT_LOAD_EXTENSION and a couple of other symbols at compile time, and therefore when Qt opens a database, it uses a slightly different VFS than the default VFS contained in sqlite3.c (i.e., no xDl* members are set). I believe now that I should recompile Qt and make SQLite a plug-in ... or else build it separately and let Qt use the system's SQLite instead of their version. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VFS
On Tue, 2011-02-22 at 08:46 -0800, Roger Binns wrote: > On 02/22/2011 05:29 AM, Robert Hairgrove wrote: > > I'm trying to understand how the VFS implementation works. > > What you think you are seeing is not happening. The documentation is correct. OK ... but what about that which the GDB debugger is seeing? > > However, if I open a database and inspect the VFS contained in the sqlite3*, > > That should just point to the VFS used. There is no copying or modification. Or so says the documentation... Maybe some more details will help here: The OS is Linux Ubuntu 10.04 LTS, AKA "Lucid Lynx" running on a Dell Inspiron 1420N notebook (and Dell supplies the drivers etc. for this distro of Linux). GCC version is 4.4.3. SQLite version is 3.6.19 (I know it's not the latest and greatest, but this is what is supplied together with the Qt sources... these are version 4.7.1 -- which is pretty much up-to-date, but not cutting edge). I compiled the Qt libraries from source myself and built SQLite statically into Qt, as opposed to a plug-in. The SQLite sources are all in the amalgamation file "sqlite.c". I created a little console project in QtCreator to test the SQLite VFS functionality. I had to add "sqlite3.c" to the project because I was getting linker errors when trying to call some of the SQLite API functions, such as "sqlite3_vfs_find" ... (hmmm...seems that these symbols aren't exported from the Qt shared libraries). Obviously, SOMETHING is resetting these pointers. Here is a snippet from the test code I wrote. It should compile and run OK if you have a newer version of Qt installed on your system. Here is my Qt .pro file: // my Qt project file: === QT += core sql INCLUDEPATH += \ {wherever the Qt sources are...}/src/3rdparty/sqlite SOURCES += main.cpp \ {wherever the Qt sources are...}/src/3rdparty/sqlite/sqlite3.c HEADERS += \ {wherever the Qt sources are...}/src/3rdparty/sqlite/sqlite3.h // end of Qt project file === And here is the source code of my test app in "main.cpp": // BEGIN CODE: #include #include #include #include void showVFS(const sqlite3_vfs * const &); int main(int argc, char *argv[]) { QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE","default_db"); sqlite3 * pSqliteDb = 0; // // Using an on-disk database seems to give the exact same results: // db.setDatabaseName("/home/bob/code/SQLite_Test_VFS/test.db"); // db.setDatabaseName(":memory:"); if (db.open()) { //--- // Some of the following lines of code are copied and // pasted from the Qt docs: //--- QVariant v = db.driver()->handle(); if (v.isValid() && qstrcmp(v.typeName(), "sqlite3*")==0) { // v.data() returns a pointer to the handle... // // Actually, it seems to return either int // or void* ... (see below): // sqlite3 *pSqliteDb = *static_cast(v.data()); if (pSqliteDb) { // // Interesting that using static_cast<> does not work below; // probably because QSqlDriver::handle() returns void* (???) // although the Qt docs AND the sources say differently: // sqlite3_vfs * pvfs = *reinterpret_cast(pSqliteDb); // // The line below gives a VFS with non-NULL values // for the struct members under discussion: // // sqlite3_vfs * pvfs = sqlite3_vfs_find(0); // if (pvfs) { showVFS(pvfs); } } else { std::cout << "No VFS handle!" << std::endl; } } } return 0; } void showVFS(const sqlite3_vfs * const &pVfs) { if (pVfs->zName) { std::cout << "=\n"; std::cout << "Name of VFS:\t&
[sqlite] VFS
I'm trying to understand how the VFS implementation works. If I fetch the default VFS with sqlite3_vfs_find(NULL), these members: xDlOpen xDlError xDlSym xDlClose all have non-NULL values. However, if I open a database and inspect the VFS contained in the sqlite3*, these four members are all NULL. Which function resets them, and why? I have no extensions loaded, but SQLITE_OMIT_EXTENSIONS is also not defined. Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] update of a blob
On Mon, 2011-02-21 at 15:13 +0100, Dietmar Hummel wrote: > std::string strStatement( "UPDATE persistence SET > name=\"blub\",expiration=\"2011-04-02\",value=\"?\" WHERE id=\"1\"" ); In addition to what Igor said, it isn't really proper (standard?) SQL to put double quotes around the value literals because these should be reserved for identifiers (e.g. schema, column or table names). I know that MS-Access (and probably SQL Server) allows it; perhaps SQLite does, too, but other databases won't -- you need to enclose them in single quotes (but only if the value is a string literal, or a date-time value formatted as a string). With some RDBMS's the character used to enclose identifiers is optional or configurable, e.g. the backtick character (`) used by MySQL. Bob ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Implementing custom xRead() and xWrite() routines for sqlite3_vfs
On Sun, 2011-02-20 at 09:35 +, Philip Graham Willoughby wrote: > For this task I would use AES-256 in counter mode with an appropriate nonce > (the counter is trivially derived from the file offset of the block to be > read/written). The key should be derived from the user's password using > 1-iteration PBKDF2 with the SHA-256 hash algorithm as the pluggable hash > function and a suitably long salt. > > If you are only doing sequential block writes you can use CBC mode rather > than counter mode - either can be used for random reads. Thanks, Phil. This is very helpful to me. AES-256 is an accepted standard, and AFAICT offers the best openly available encryption today. Bob ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Implementing custom xRead() and xWrite() routines for sqlite3_vfs
On Sun, 2011-02-20 at 09:35 +, Philip Graham Willoughby wrote: > On 20 Feb 2011, at 09:10, Robert Hairgrove wrote: > > > I am not starting from scratch doing my own encryption; there are enough > > open source libraries publicly available which are good enough for my > > purposes. > > And all of them offer approximately no security if you use them incorrectly. Thanks, I realize this. Another question: There are certain parts of an SQLite database or page header (the first 100 bytes, for example) which have known values. I think it is perhaps even dangerous to encrypt this data, at least with the same method used for the rest of the file. If I used the same algorithm and key, etc. to encrypt the header data as the rest of the file, it might be trivial to decrypt it, knowing the published file format (which is explained in great detail on the SQLite website). SEE encrypts the entire file, according to the information on the website. But I'm sure they must have taken this into consideration when they designed their library... So if I leave the headers unencrypted, am I disclosing anything I should be (somehow) hiding? Bob ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Implementing custom xRead() and xWrite() routines for sqlite3_vfs
On Sat, 2011-02-19 at 17:08 -0800, Roger Binns wrote: > On 02/19/2011 03:40 PM, Robert Hairgrove wrote: > > But before I "try this at home", I thought I would ask if there are any > > caveats I should be aware of? Thanks for any helpful advice! Thanks, Roger ... those are great links! > Unless your time has no value, I'd suggest using this: > > http://www.hwaci.com/sw/sqlite/see.html I saw that, but I find it a little intrusive, programmatically speaking, from a licensing standpoint. Although SQLite itself is PD licensed, I am using it coupled with the Qt source code which is LGPL licensed. So I'm not really not sure how this encryption add-in would play with that if I have to recompile something I got from Qt in the first place. My application is a closed-source commercial one, and linking dynamically to Qt without changing any of the Qt sources is allowed under the LGPL. If I can hook in my own read/write routines like this at runtime, I won't have to alter and/or recompile any of the library sources. I *could* build the Qt SQLite module with SEE as a plug-in, but would rather link it statically (and the SEE people apparently want it that way, too). In this case, I would need to treat the SQLite sources the same as any of the other Qt sources. But "IANAL", so maybe I am being too cautious here. > (Note that it is supported, tested and cryptographically sound. It would > take you a long time to achieve the same.) I am not starting from scratch doing my own encryption; there are enough open source libraries publicly available which are good enough for my purposes. > If you just want whole file encryption then I'd recommend using an archive > tool and storing/extracting as appropriate. For example 7zip does this well > and is open source. Compression is not the same as encryption. The application is primarily for school teachers (single-user desktop use, which is why SQLite is ideal for this) and the database might contain stuff like confidential student reports, grades, etc. Since I am not trying to protect highly sensitive government secrets or medical data here, although someone could probably use the app for that as well, I do worry that some student might get hold of the file and try to hack it. A clever student would see right away that compression and not encryption was employed -- and a student's time usually DOES come cheap! ;) > If you really want to do your own thing then beware that the encryption key > has to be where the data is encrypted/decrypted. You should carefully study > exactly what it is you a protecting, who you are protecting it from, how > long it is protected etc. These can help: > > http://www.schneier.com/paper-attacktrees-ddj-ft.html > > If you really do still want to proceed then xRead/xWrite are an appropriate > place to do it. Thanks, this is what I needed to know. > http://en.wikipedia.org/wiki/Initialization_vector > > Anyone can design a scheme they themselves cannot break. It requires far > more skill and experience to come up with something that is actually strong. > > Also consider that what you may actually need is just some obfuscation. For > example you could just XOR the database contents with deterministic bytes. > If you did this then seeing the contents would go from costing a few dollars > (load the file into the command line shell) into a few hundred or thousand > (figure out what it is you did). In any event an attacker could always > point a gun or use a hardware keylogger if they don't want to be discovered. > That would workaround any encryption scheme. I would hash the user-supplied passphrase with a value known internally to my program to create the final encryption key, so the hacker would have to have a copy of the executable of my program in addition to whatever data was gleaned by using a key logger. And each licensed copy of my program would have a unique hash value embedded within the executable. As to the gun, Bruce Schneier already pointed out that this is one of the more expensive options in the attack tree. ;) Bob ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Implementing custom xRead() and xWrite() routines for sqlite3_vfs
My application (written in C++ together with the Qt libraries) uses an in-memory database which is stored to disk using the SQLite backup API at the end of a session or at periodic intervals (i.e. auto-save functionality) and loaded from disk into the memory database at program startup. This works fine, but I would like to offer the user the option to encrypt the database before writing it to disk. If it is encrypted, they would need to decrypt it again when it was loaded into memory (duh!) After studying the SQLite sources a bit, it seems that the easiest way to do this would be to replace the function pointers of the sqlite3_vfs struct "xRead" and "xWrite" with my own functions, similar to the way we used to "bend" interrupt routines under MS-DOS -- remember those? :) -- to point to our custom interrupt handlers. I would call sqlite3_vfs_find(NULL) to get a pointer to the default VFS, then copy that to a static object and just replace those two function pointers, then register the new VFS (do I even need to do that, or can I just plug the pointer to my static sqlite3_vfs struct into the sqlite3 object whose pointer is passed to the backup API functions?) My own code would save the original pointers and use them inside the encryption and decryption routines for doing the actual disk I/O. There are open source implementations of a variety of encryption algorithms which work on fixed block sizes; i.e. if I encrypt the entire database instead of just one page at a time (as other encryption routines seem to do), it should be exactly the same size (or perhaps just a few bytes larger due to padding) as the original file. IOW, a block of 8 bytes, when encrypted, would reside at the same offset as the original data. But before I "try this at home", I thought I would ask if there are any caveats I should be aware of? Thanks for any helpful advice! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users