Re: [sqlite] csv extension/feature requests: new options for field separator different from default ', ' and skip=N to skip first N lines

[Robert Hairgrove]

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

[Robert Hairgrove]

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

[Robert Hairgrove]

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...

[Robert Hairgrove]

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?

[Robert Hairgrove]

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)

[Robert Hairgrove]

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)

[Robert Hairgrove]

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)

[Robert Hairgrove]

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)

[Robert Hairgrove]

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

[Robert Hairgrove]

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...

[Robert Hairgrove]

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

[Robert Hairgrove]

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

[Robert Hairgrove]

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

[Robert Hairgrove]

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

[Robert Hairgrove]

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=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

[Robert Hairgrove]

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?

[Robert Hairgrove]

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

[Robert Hairgrove]

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

[Robert Hairgrove]

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 )
{
if (pVfs->zName) {
std::cout << "=\n";
std::cout << "Name of VFS:\t" << pVfs

[sqlite] VFS

[Robert Hairgrove]

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

[Robert Hairgrove]

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

[Robert Hairgrove]

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

[Robert Hairgrove]

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

[Robert Hairgrove]

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

[Robert Hairgrove]

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