Re: [sqlite] Changing ID's to UUID

[Simon Slavin]

On 17 Nov 2016, at 3:13am, Keith Medcalf  wrote:

> You would only get guaranteed collision avoidance if you kept track of every 
> UUID issued and checked for duplicates at time of issue to avoid duplication 
> by keeping a list of all the "used" identifiers.  I do not think that keeping 
> track of all the UUIDs issued to avoid duplication is part of the spec.  They 
> really should be called SRID's (somewhat random identifiers).

The randomness is not part of the intended spec for UUIDs.  The emphasis was on 
the 'Unique', which is why the name had 'U' in, not 'R'.  The first form of 
UUIDs combined the IP address of the originating computer with the time in .1 
microsecond intervals.  The assumption is that no computer would be fast enough 
to need to name two entities in .1 microsecond.  Thus UUIDs would be inherently 
unique without needing any extra programming or needing to consult a database 
of already-used UUIDs.  This was considered very clever.

But this scheme was criticised for giving away too much information.  For 
instance, if you saw a UUID for a car part you could immediately deduce the IP 
address of am important computer belonging to the car company.  That could be a 
good target for hacking attempts.

So number of other options were thought up, one of which can easily be created 
from a URL, one which relates to a domain name, some of which were designed 
from the outset to have the properties of random numbers.  It was a 
random-looking scheme -- known as version 4 -- that I mentioned which set off 
this thread.  Because it looks random it gives away nothing about the 
originating device, and people like that these days.  But perhaps version 1 
UUIDs might suit the OP better.

So yes, randomness is important to the particular type of UUID we're discussing 
here.  But it's not essential to all UUIDs.

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

Re: [sqlite] Changing ID's to UUID

[Keith Medcalf]

> As someone mentioned in a different part of this thread, the issue is
> centralization.  If you have multiple decentralied machines generating
> UUIDs you *could* have collisions, but if you pick your UUID scheme well
> enough collisions will be extremely rare, on par with things like data
> loss from hardware failure.
> 
> Of course if you generate all your UUIDs on one machine (as you do when
> you call getUUID() on a single machine) you get guaranteed collision
> avoidance.

You would only get guaranteed collision avoidance if you kept track of every 
UUID issued and checked for duplicates at time of issue to avoid duplication by 
keeping a list of all the "used" identifiers.  I do not think that keeping 
track of all the UUIDs issued to avoid duplication is part of the spec.  They 
really should be called SRID's (somewhat random identifiers).






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

Re: [sqlite] Changing ID's to UUID

[Nico Williams]

On Wed, Nov 16, 2016 at 02:51:35PM -0600, Jeffrey Mattox wrote:
> I think this discussion is about apples and oranges.  UUID stands for
> universally UNIQUE identifier, so there won't be any collisions.  It
> looks random, but it never repeats.  [...]

No, DRH is right that this depends on how good your entropy source (and,
typically, PRNG fed that entropy) is.  Nothing about putting
"universally unique" in the name makes it so -- only the details of how
the sausage is made can take care of it.

Regardless, using UUIDs to make a distributed DB is not that great.  It
does work though.  Microsoft's Active Directory (AD), for example, uses
96-bit UUID-like values to form "domain SIDs", with user, group, and
other SIDs being formed by adding a 32-bit "relative ID" to the domain
SID.  This has worked rather well for MSFT, and it has allowed the
creation of "forests" of domains and forests of forests.  I do think AD
checks SID uniqueness within each forest, and IIRC there's a way to
handle SID collisions in forests of forests.

Uniqueness checks are not too expensive when they are feasible at all.

In the AD forest case they are feasible, while in the forest of forests
case they are not.

The alternative to randomly-generated IDs would be to have a global
registry (perhaps hierarchical), not unlike DNS, or ASN.1 OID arcs, but
there is a real cost to having to have a global registry.

So in a distributed system roughly like SPARQL, or AD, say, UUIDs will
do.  You might store them as blobs to avoid having to waste space, but,
whatever.

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

Re: [sqlite] Changing ID's to UUID

[David Raymond]

https://xkcd.com/221/

"The generation of random numbers is too important to be left to chance." -I 
forget who


A UUID is basically a 16 byte integer that people agree to type out in a 
standard way. All the properties of UUID's that you've mentioned below depend 
on the random number generator being actually random.

"Guaranteed" means vastly different things to a mathematician, a programmer, 
and the extended warranty folks at Best Buy. From Wikipedia: "In other words, 
only after generating 1 billion UUIDs every second for the next 100 years, the 
probability of creating just one duplicate would be about 50%."

A 1 in 2^128 (1 in 340,282,366,920,938,463,463,374,607,431,768,211,456. 340 
undecillion?) chance of collision for any 2 honestly random UUID's means...

Mathematician: They're not unique at all. If anything then it's absolutely 
guaranteed that there  be collisions... eventually.
Programmer: "For all intents and purposes" unique. Good enough for government 
work.
Best Buy: Would you like to buy extra collision insurance on your new UUID? You 
never know what could happen after all. Or maybe upgrade to the gold plated 
UUID's we have over there?


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Jeffrey Mattox
Sent: Wednesday, November 16, 2016 3:52 PM
To: SQLite mailing list
Subject: Re: [sqlite] Changing ID's to UUID


I think this discussion is about apples and oranges.  UUID stands for 
universally UNIQUE identifier, so there won't be any collisions.  It looks 
random, but it never repeats.  Here's one: 
"cb058c3a-ac3d-11e6-80f5-76304dec7eb7".  I use a UUID [a non-random string 
guaranteed to be unique -- obtained via an iOS getUUID() function call] to 
match records in two databases on different machines.  They are records for 
individuals that occasionally have to be merged.  The UUID is acts as a 
guaranteed unique name for each individual.

Jeff

___
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] Issue with Malformed table

[Paul Sanderson]

If you are unsucessful dro me an email - I might be able to help (not
a commercial proposition - just may help me doing some testing with my
code)

Cheers
Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence


On 16 November 2016 at 15:31, Simon Slavin  wrote:
>
> On 16 Nov 2016, at 3:16pm, Andrew Stewart  wrote:
>
>>   Forgot to ask 1 thing.  What is a quick way to copy an entire table 
>> from database1 to database2?
>
> If they are open in different connections you cannot do it.
>
> If they are open in the same connection using ATTACH then you can do it like 
> this:
>
> open ('new.sqlite')
> ATTACH 'corrupt.sqlite' AS corrupt;
> CREATE TABLE myTable (... same structure as the old one ...);
> INSERT INTO myTable (SELECT * FROM corrupt.myTable)
>
> Simon.
> ___
> 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] Changing ID's to UUID

[Jeremy Brown]

On Wed, Nov 16, 2016 at 02:51:35PM -0600, Jeffrey Mattox wrote:
> On Nov 16, 2016, at 8:46 AM, Richard Hipp  wrote:
> > 
> >> On 11/16/16, Keith Medcalf  wrote:
> >> What I do not
> >> understand is why one would use a UUID (randomly generated bunch of bytes)
> >> as a key in a database.  It is long, every use must be checked for
> >> collisions, and inherently far less efficient than the simple integer
> >> sequence it is replacing.
> > 
> > If you use good randomness to generate the UUID and if the UUID is
> > long enough, then you do not, in fact, need to check for collisions.
> > It doesn't take an excessively long UUID to make the probability of
> > collision become far less than the probability of a random cosmic ray
> > hit on your CPU causing it to give the wrong answer on a collision
> 
> I think this discussion is about apples and oranges.  UUID stands for 
> universally UNIQUE identifier, so there won't be any collisions.  It looks 
> random, but it never repeats.  Here's one: 
> "cb058c3a-ac3d-11e6-80f5-76304dec7eb7".  I use a UUID [a non-random string 
> guaranteed to be unique -- obtained via an iOS getUUID() function call] to 
> match records in two databases on different machines.  They are records for 
> individuals that occasionally have to be merged.  The UUID is acts as a 
> guaranteed unique name for each individual.

As someone mentioned in a different part of this thread, the issue is 
centralization.  If you have multiple decentralied machines generating UUIDs 
you *could* have collisions, but if you pick your UUID scheme well enough 
collisions will be extremely rare, on par with things like data loss from 
hardware failure.

Of course if you generate all your UUIDs on one machine (as you do when you 
call getUUID() on a single machine) you get guaranteed collision avoidance.

Jeremy



> 
> Jeff
> 
> ___
> 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] Changing ID's to UUID

[Jeffrey Mattox]

On Nov 16, 2016, at 8:46 AM, Richard Hipp  wrote:
> 
>> On 11/16/16, Keith Medcalf  wrote:
>> What I do not
>> understand is why one would use a UUID (randomly generated bunch of bytes)
>> as a key in a database.  It is long, every use must be checked for
>> collisions, and inherently far less efficient than the simple integer
>> sequence it is replacing.
> 
> If you use good randomness to generate the UUID and if the UUID is
> long enough, then you do not, in fact, need to check for collisions.
> It doesn't take an excessively long UUID to make the probability of
> collision become far less than the probability of a random cosmic ray
> hit on your CPU causing it to give the wrong answer on a collision

I think this discussion is about apples and oranges.  UUID stands for 
universally UNIQUE identifier, so there won't be any collisions.  It looks 
random, but it never repeats.  Here's one: 
"cb058c3a-ac3d-11e6-80f5-76304dec7eb7".  I use a UUID [a non-random string 
guaranteed to be unique -- obtained via an iOS getUUID() function call] to 
match records in two databases on different machines.  They are records for 
individuals that occasionally have to be merged.  The UUID is acts as a 
guaranteed unique name for each individual.

Jeff

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

Re: [sqlite] Uninitialized memory reads (not likely false positives)

[Nico Williams]

On Wed, Nov 16, 2016 at 12:06:39PM -0600, Nico Williams wrote:
> On Tue, Nov 15, 2016 at 09:38:11PM -0200, Bernardo Sulzbach wrote:
> >   if( s1>7 && s2>7 ){
> > res = s1 - s2;
> >   }else{
> > if( s1==s2 ){
> >   // Accesses to aLen as mentioned above
> > 
> > If s1 > 7 && s2 > 7 is false, then at least one of s1 and s2 is not above 7.
> > If they are equal, then neither s1 nor s2 is above 7.
> 
> 7 is past the end of the array.
> 
> > > and we also know that either s1 or s2 can be 8 or 9,
> > 
> > This is false, unless I am mistaken. See my reasoning above.
> > 
> > The issue is valid, and the message your analyzer (or compiler) wrote is
> > correct: it is not guaranteed to be < 7, which it should be.
> 
> Right.

Er, no, wrong: it has to be < 7 given the assertions and the if
conditions.  I should have read Dan Kennedy's response first.

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

Re: [sqlite] Uninitialized memory reads (not likely false positives)

[Nico Williams]

On Wed, Nov 16, 2016 at 10:52:13PM +0700, Dan Kennedy wrote:
> On 11/16/2016 05:53 AM, Nico Williams wrote:
> > [...]
> >
> > Anyways, the analysis from here is non-trivial, and I can't convince
> > myself that sNC.pNext will not be dereferenced.
> 
> Thanks for taking the time to look into these.
> 
> Some kind of assert() could be helpful there I think. The reason sNC.pNext
> will not be accessed is that generateColumnNames() is only called (a) on a
> top-level SELECT statement and (b) after all references have already
> resolved successfully. Implying that this:
> 
>   http://www.sqlite.org/src/artifact/672b1af237ad2?ln=1406
> 
> is always true.

That's... a bit too convoluted to for my liking.  I'd rather have
sNC.pNext initialized than an assertion that might get compiled out.

This is a function invoked on statement preparation, so I think
initializing sNC.pNext can't negatively affect performance in any
terribly meaningful way.

> > Another one that I find difficult to analyze is a possible
> > out-of-bounds read in vdbeSorterCompareInt():
> >
> > [...]
> >
> > At 85715 we know that (s1 <= 7 || s2 <= 7) && s1 == s2, and we also
> > know that either s1 or s2 can be 8 or 9, so aLen[s1] at 85715 could
> > very well have s1 > 6, which would read past the bounds of aLen[].
> 
> I think ( ( s1<=7 || s2<=7) && s1==s2 ) implies that s1<=7. And we assume
> s1!=7 because there is an assert() that says so.

Oh, I see it now.  Yeah, OK.  This is definitely a false positive.

Thanks for the response,

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

Re: [sqlite] Uninitialized memory reads (not likely false positives)

[Nico Williams]

On Tue, Nov 15, 2016 at 09:38:11PM -0200, Bernardo Sulzbach wrote:
>   if( s1>7 && s2>7 ){
> res = s1 - s2;
>   }else{
> if( s1==s2 ){
>   // Accesses to aLen as mentioned above
> 
> If s1 > 7 && s2 > 7 is false, then at least one of s1 and s2 is not above 7.
> If they are equal, then neither s1 nor s2 is above 7.

7 is past the end of the array.

> > and we also know that either s1 or s2 can be 8 or 9,
> 
> This is false, unless I am mistaken. See my reasoning above.
> 
> The issue is valid, and the message your analyzer (or compiler) wrote is
> correct: it is not guaranteed to be < 7, which it should be.

Right.

> I am unsure whether or not this is actually a bug, but it almost certainly
> is a mistake.

That's how it seems to me, yes.

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

Re: [sqlite] Changing ID's to UUID

[James K. Lowden]

On Wed, 16 Nov 2016 08:59:03 -0600
Paul Egli  wrote:

> > Using the systemid sequence and the recordid sequence directly
> > however, has a 0% probability of collision, so any rational person
> > would use that directly and forgo entirely the introduction of
> > uncertainty and bugs using "UUID" type crappola will cause.
> >
> 
> As Dominique said, the issue here is decentralization... 

Decentralization, you say, but not no centralization.  If the data on 
those disconnected devices never came together, their keys would never
conflict.  

We've handled this before, more than once.  Ethernet cards have unique
addresses.  The Domain Name System supports a certain amount of
wackiness, but doesn't rely on randomness.  

Of course, those systems were designed by competent engineers  

> and i would add, particularly in a disconnected environment and/or
> one with no central authority. The method you describe does not
> handle device rollbacks or cloning.

I don't see how the method you describes solves anything.  If two
devices represent the same data -- or data belonging to the same
entity, or whatever -- they need a shared identifier to represent that
ownership.  If that identifier is chosen randomly or taken from
the devices, it will not join the information.  The owner of the
information will at some point have to assert their commonality: will
have to apply a known, common identifier to both sets of information.  

--jkl

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

Re: [sqlite] Uninitialized memory reads (not likely false positives)

[Dan Kennedy]

On 11/16/2016 05:53 AM, Nico Williams wrote:

I don't normally pay attention to warnings when compiling SQLite3, nor
to Coverity or other static analysis tools' output either, as I'm quite
aware that most of these are false positives and thus unwelcome noise
here.

However, I do sample them occasionally, and though usually such reports
are false positives, here are two that don't quite look like false
positives to me.  I get these from building the SQLite3 3.15.1
amalgamation.

Uninitialized pointer dereference:

 115861 static void generateColumnTypes(
 115862   Parse *pParse,  /* Parser context */
 115863   SrcList *pTabList,  /* List of tables */
 115864   ExprList *pEList/* Expressions defining the result set */
 115865 ){
 115866 #ifndef SQLITE_OMIT_DECLTYPE
 115867   Vdbe *v = pParse->pVdbe;
 115868   int i;

1. var_decl: Declaring variable sNC without initializer.
 115869   NameContext sNC;
 115870   sNC.pSrcList = pTabList;
 115871   sNC.pParse = pParse;

2. Condition i < pEList->nExpr, taking true branch
 115872   for(i=0; inExpr; i++){
 115873 Expr *p = pEList->a[i].pExpr;
 115874 const char *zType;
 115875 #ifdef SQLITE_ENABLE_COLUMN_METADATA
 115876 const char *zOrigDb = 0;
 115877 const char *zOrigTab = 0;
 115878 const char *zOrigCol = 0;
 115879 zType = columnType(, p, , , , 0);
 115880
 115881 /* The vdbe must make its own copy of the column-type and other
 115882 ** column specific strings, in case the schema is reset before 
this
 115883 ** virtual machine is deleted.
 115884 */
 115885 sqlite3VdbeSetColName(v, i, COLNAME_DATABASE, zOrigDb, 
SQLITE_TRANSIENT);
 115886 sqlite3VdbeSetColName(v, i, COLNAME_TABLE, zOrigTab, 
SQLITE_TRANSIENT);
 115887 sqlite3VdbeSetColName(v, i, COLNAME_COLUMN, zOrigCol, 
SQLITE_TRANSIENT);
 115888 #else

CID 12 301 (#1 of 1): Uninitialized pointer read (UNINIT)
3. uni nit_use_in_call: Using uninitialized value sNC.pNext when calling 
columnTypeImpl.
 115889 zType = columnType(, p, 0, 0, 0, 0);

columnType() is a macro expanding to a call to columnTypeImpl().

Anyways, the analysis from here is non-trivial, and I can't convince
myself that sNC.pNext will not be dereferenced.


Thanks for taking the time to look into these.

Some kind of assert() could be helpful there I think. The reason 
sNC.pNext will not be accessed is that generateColumnNames() is only 
called (a) on a top-level SELECT statement and (b) after all references 
have already resolved successfully. Implying that this:


  http://www.sqlite.org/src/artifact/672b1af237ad2?ln=1406

is always true.


The obvious fix is to initialize sNC a bit more before the loop at
115872.  At least setting sNC.pNext = 0 seems like the right thing to
do.

Another one that I find difficult to analyze is a possible out-of-bounds
read in vdbeSorterCompareInt():

  85712 static const u8 aLen[] = {0, 1, 2, 3, 4, 6, 8 };
  85713 int i;
  85714 res = 0;
  85715 for(i=0; i0 && s1<7) || s1==8 || s1==9 );
  85701   assert( (s2>0 && s2<7) || s2==8 || s2==9 );
  85702
  85703   if( s1>7 && s2>7 ){
  85704 res = s1 - s2;
  85705   }else{
  85706 if( s1==s2 ){

At 85715 we know that (s1 <= 7 || s2 <= 7) && s1 == s2, and we also know
that either s1 or s2 can be 8 or 9, so aLen[s1] at 85715 could very well
have s1 > 6, which would read past the bounds of aLen[].


I think ( ( s1<=7 || s2<=7) && s1==s2 ) implies that s1<=7. And we 
assume s1!=7 because there is an assert() that says so.


Dan.

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

Re: [sqlite] Issue with Malformed table

[Simon Slavin]

On 16 Nov 2016, at 3:16pm, Andrew Stewart  wrote:

>   Forgot to ask 1 thing.  What is a quick way to copy an entire table 
> from database1 to database2?

If they are open in different connections you cannot do it.

If they are open in the same connection using ATTACH then you can do it like 
this:

open ('new.sqlite')
ATTACH 'corrupt.sqlite' AS corrupt;
CREATE TABLE myTable (... same structure as the old one ...);
INSERT INTO myTable (SELECT * FROM corrupt.myTable)

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

Re: [sqlite] Issue with Malformed table

[Simon Slavin]

On 16 Nov 2016, at 3:14pm, Andrew Stewart  wrote:
> 
>Thank you for the response.  I will give it a try.  I can do this with 
> 2 different database connections and then just transfer all of the tables 
> from database1 to database2.  Close database1. Remove/rename the file for 
> database1. Close database2.  Rename file for database2 to file for database1. 
>  Open connection to database1.

Yes.  This is a good way to replace a corrupt database.

Good luck with your rescue efforts.

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

Re: [sqlite] Issue with Malformed table

[Andrew Stewart]

Simon,
Forgot to ask 1 thing.  What is a quick way to copy an entire table 
from database1 to database2?

Thanks,
Andrew

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Andrew Stewart
Sent: Wednesday, November 16, 2016 7:16 AM
To: 'SQLite mailing list' 
Subject: Re: [sqlite] Issue with Malformed table

Simon,
Thank you for the response.  I will give it a try.  I can do this with 
2 different database connections and then just transfer all of the tables from 
database1 to database2.  Close database1. Remove/rename the file for database1. 
 Close database2.  Rename file for database2 to file for database1.  Open 
connection to database1.

Andrew

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Simon Slavin
Sent: Wednesday, November 16, 2016 7:07 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Issue with Malformed table


On 16 Nov 2016, at 2:52pm, Andrew Stewart  wrote:

>I have a table that is giving me errors.  I can recover the 
> data from another source, but need a way to clear the table.

This process is very fast and easy, but it may not help with your form of 
corruption.

Open the database in the SQLite Shell Tool (can be downloaded from the SQLite 
download page).
Use the command

.dump 

This sometimes allows you to extract data from corrupt tables.  And sometimes 
does not.

> Read row from table1 - detect error (SQL logic error or missing database).  
> Have been able to read other rows from same table successfully.
> Create temporaryTable - for transferring good data from bad table to.

Never do this.  If one table in a SQLite database is showing corruption, regard 
the entire file as corrupt and do not attempt to make any changes to it at all, 
even deleting stuff from it.  Any changes you make to it may result in making 
more of your original data un-rescuable.

You do this because you may, for example, have one page of the corrupt database 
file marked both as in use by a table and as available for use with new data.  
So writing any new data to it could lose even more data from an existing table.

So how do you rescue data from one database file and write it to another ?  You 
open two different SQLite connections to two different SQLite database files.  
If your library doesn't allow that you can instead allow one SQLite connection 
to access two databases using the ATTACH command:



So my recommendation, if you do want your own program to use the SQLite API to 
extract data from a corrupt database, is that you open two different 
connections to two different databases.  If you can't do that, then make the 
connection to a new database in which you're going to store the rescued data, 
but then ATTACH to it the corrupt database which you will only read from, never 
write to.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Notice: This electronic transmission contains confidential information, 
intended only for the person(s) named above. If you are not the intended 
recipient, you are hereby notified that any disclosure, copying, distribution, 
or any other use of this email is strictly prohibited. If you have received 
this transmission by error, please notify us immediately by return email and 
destroy the original transmission immediately and all copies thereof.
___
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] Issue with Malformed table

[Andrew Stewart]

Simon,
Thank you for the response.  I will give it a try.  I can do this with 
2 different database connections and then just transfer all of the tables from 
database1 to database2.  Close database1. Remove/rename the file for database1. 
 Close database2.  Rename file for database2 to file for database1.  Open 
connection to database1.

Andrew

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Simon Slavin
Sent: Wednesday, November 16, 2016 7:07 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Issue with Malformed table


On 16 Nov 2016, at 2:52pm, Andrew Stewart  wrote:

>I have a table that is giving me errors.  I can recover the 
> data from another source, but need a way to clear the table.

This process is very fast and easy, but it may not help with your form of 
corruption.

Open the database in the SQLite Shell Tool (can be downloaded from the SQLite 
download page).
Use the command

.dump 

This sometimes allows you to extract data from corrupt tables.  And sometimes 
does not.

> Read row from table1 - detect error (SQL logic error or missing database).  
> Have been able to read other rows from same table successfully.
> Create temporaryTable - for transferring good data from bad table to.

Never do this.  If one table in a SQLite database is showing corruption, regard 
the entire file as corrupt and do not attempt to make any changes to it at all, 
even deleting stuff from it.  Any changes you make to it may result in making 
more of your original data un-rescuable.

You do this because you may, for example, have one page of the corrupt database 
file marked both as in use by a table and as available for use with new data.  
So writing any new data to it could lose even more data from an existing table.

So how do you rescue data from one database file and write it to another ?  You 
open two different SQLite connections to two different SQLite database files.  
If your library doesn't allow that you can instead allow one SQLite connection 
to access two databases using the ATTACH command:



So my recommendation, if you do want your own program to use the SQLite API to 
extract data from a corrupt database, is that you open two different 
connections to two different databases.  If you can't do that, then make the 
connection to a new database in which you're going to store the rescued data, 
but then ATTACH to it the corrupt database which you will only read from, never 
write to.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Notice: This electronic transmission contains confidential information, 
intended only for the person(s) named above. If you are not the intended 
recipient, you are hereby notified that any disclosure, copying, distribution, 
or any other use of this email is strictly prohibited. If you have received 
this transmission by error, please notify us immediately by return email and 
destroy the original transmission immediately and all copies thereof.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Issue with Malformed table

[Simon Slavin]

On 16 Nov 2016, at 2:52pm, Andrew Stewart  wrote:

>I have a table that is giving me errors.  I can recover the 
> data from another source, but need a way to clear the table.

This process is very fast and easy, but it may not help with your form of 
corruption.

Open the database in the SQLite Shell Tool (can be downloaded from the SQLite 
download page).
Use the command

.dump 

This sometimes allows you to extract data from corrupt tables.  And sometimes 
does not.

> Read row from table1 - detect error (SQL logic error or missing database).  
> Have been able to read other rows from same table successfully.
> Create temporaryTable - for transferring good data from bad table to.

Never do this.  If one table in a SQLite database is showing corruption, regard 
the entire file as corrupt and do not attempt to make any changes to it at all, 
even deleting stuff from it.  Any changes you make to it may result in making 
more of your original data un-rescuable.

You do this because you may, for example, have one page of the corrupt database 
file marked both as in use by a table and as available for use with new data.  
So writing any new data to it could lose even more data from an existing table.

So how do you rescue data from one database file and write it to another ?  You 
open two different SQLite connections to two different SQLite database files.  
If your library doesn't allow that you can instead allow one SQLite connection 
to access two databases using the ATTACH command:



So my recommendation, if you do want your own program to use the SQLite API to 
extract data from a corrupt database, is that you open two different 
connections to two different databases.  If you can't do that, then make the 
connection to a new database in which you're going to store the rescued data, 
but then ATTACH to it the corrupt database which you will only read from, never 
write to.

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

Re: [sqlite] Changing ID's to UUID

[Paul Egli]

On Wed, Nov 16, 2016 at 7:59 AM, Keith Medcalf  wrote:

> Using the systemid sequence and the recordid sequence directly however,
> has a 0% probability of collision, so any rational person would use that
> directly and forgo entirely the introduction of uncertainty and bugs using
> "UUID" type crappola will cause.
>

As Dominique said, the issue here is decentralization... and i would add,
particularly in a disconnected environment and/or one with no central
authority. The method you describe does not handle device rollbacks or
cloning.

For example, one of your systems is a mobile device with it's own unique
system id. Periodically, this device broadcasts its inserted data to other
devices. Also, the user backs up the device to their PC every now and then.
At some point the mobile device gets lost or damaged. When they restore
from backup, the last few sequential ids from that system id get reused and
collide. It is also possible to restore from backup to a different device,
even if the original is still alive and well, at which point you have two
different devices with the same system id broadcasting colliding keys.

Theoretically a new, unique system id should be generated any time a system
is backed up or copied anywhere. But when the backup/copying logic is
completely independent and unknowing of your systemid, you are left with
needing to detect if the physical device has changed. This may be
unreliable or impossible on some platforms. And i don't think it would be
possible to detect the case where a rollback happened on the same physical
device.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Issue with Malformed table

[Andrew Stewart]

Hi,
I have a table that is giving me errors.  I can recover the 
data from another source, but need a way to clear the table.  The following is 
my process:
Read row from table1 - detect error (SQL logic error or missing database).  
Have been able to read other rows from same table successfully.
Create temporaryTable - for transferring good data from bad table to.
Drop index on table1 - error reported - 'database disk image is malformed'
Create index on temporaryTable - error reported - index already exists - caused 
because Drop Index failed
Transfer rows from table1 to temporaryTable to until error occurs
Drop table1 - error report - 'database disk image is malformed'
Alter table - rename temporaryTable to table1 - error reported - item already 
exists - caused because Drop Table failed

This process would work, if I could get the Drops to work.

I think I know what is causing the original errors and I have 
fixed it, but I want to come up with a way to fix tables so I am testing this.

Thanks,
Andrew Stewart
Software Designer

Argus Control Systems Ltd.
Tel: (604) 536-9100 ext. 108

astew...@arguscontrols.com

#101 - 18445 53rd Avenue | Surrey BC | CANADA | V3S 7A4
(604) 538-3531 | (800) 667-2090 | Fax (604) 538-4728
www.arguscontrols.com

Notice: This electronic transmission contains confidential information, 
intended only for the person(s) named above. If you are not the intended 
recipient, you are hereby notified that any disclosure, copying, distribution, 
or any other use of this email is strictly prohibited. If you have received 
this transmission by error, please notify us immediately by return email and 
destroy the original transmission immediately and all copies thereof.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Changing ID's to UUID

[Richard Hipp]

On 11/16/16, Keith Medcalf  wrote:
> What I do not
> understand is why one would use a UUID (randomly generated bunch of bytes)
> as a key in a database.  It is long, every use must be checked for
> collisions, and inherently far less efficient than the simple integer
> sequence it is replacing.

If you use good randomness to generate the UUID and if the UUID is
long enough, then you do not, in fact, need to check for collisions.
It doesn't take an excessively long UUID to make the probability of
collision become far less than the probability of a random cosmic ray
hit on your CPU causing it to give the wrong answer on a collision
test.

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

Re: [sqlite] Changing ID's to UUID

[Keith Medcalf]

> > I still cannot fathom why anyone would assign random numbers or (even
> more useless) long random blobs to use as psuedo-keys.  It just boggles
> the mind.
 
> I take it you’re not a cryptographer :) All modern ciphers do this. For
> example, an RSA key pair is simply a pair of large random numbers (both
> prime) that meet certain criteria. Or if you use a more modern cipher like
> Curve25519, the private key is quite literally just any 256 bits of random
> data. You generate a key-pair by reading 32 bytes from /dev/random into
> the private key, and then performing a transformation on that to get the
> public key.

I know and understand the uses of random numbers, encryption, and digests when 
used for the purpose for which they were invented.  What I do not understand is 
why one would use a UUID (randomly generated bunch of bytes) as a key in a 
database.  It is long, every use must be checked for collisions, and inherently 
far less efficient than the simple integer sequence it is replacing.

Of course, it is just a fad (like big huge wastes of whitespace and unreadable 
low-contrast ittybitty fonts in current web-page bootifications) adopted by 
those unable to comprehend the consequences of their decisions (and if they 
haven't had any yet, they are very lucky indeed).

> Obviously collisions are possible with long random numbers or digests, but
> secure systems are designed such that random collisions are vanishingly
> unlikely to occur for {insert large power of ten here} years, which makes
> the probability effectively zero.

No, you are incorrect.  A "good hash function" will evenly spread its 
collisions over its digest space. If you feed all possible 512-bit blocks into 
a 512-bit hash to obtain the output digests, when you feed in one more 513-bit 
input, you will get one collision.  If you feed in another 513-bit input you 
will get a different collision.  The "collision" digest will not be predictable 
(that is it will not "just always be the same as the first 512-bit blocks input 
digest with bit 438 flipped).  It is the property of being unable (very complex 
and taking a long time) to generate an input (chosen text) which results in a 
specific digest which is the useful property -- the fact that it can and must 
have a 100% probability of collision when the input space is larger than the 
output space is irrelevant.

THe problem is an inability to properly determine and assess risk.  When using 
a sequence the probability of a collision is 0.  When using a random generated 
number (passing a bunch of random data through a digest function) has a 
probability of collision of 100%.  Only if you have (for example) a sequence 
assigned "systemid" which is used as part of the input to the digest function, 
and use the generated recordid sequence number as input to the digest along 
with the random data does the probability of collision reduce from 100% to some 
small number greater than 0%.  Using the systemid sequence and the recordid 
sequence directly however, has a 0% probability of collision, so any rational 
person would use that directly and forgo entirely the introduction of 
uncertainty and bugs using "UUID" type crappola will cause.

Unfortunately there is a massive shortage of rational life on this planet.

 
> —Jens
> ___
> 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] Database corruption, and PRAGMA fullfsync on macOS

[Richard Hipp]

On 11/16/16, Simon Slavin  wrote:
>
> There's also a tool from the SQLite team which can analyze a corrupt SQLite
> file and tell some things about how it is corrupt.  Unfortunately I can't
> remember what it's called or where to find it.  But I heard about it on this
> list and I hope someone can.
>

make showdb

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

Re: [sqlite] SQLite as a Shell Script

[Rowan Worth]

Not true. You can go ahead and create a database called "ls", but:

a) it will not be executable, and
b) the file will start with "SQLite format 3" rather than the requisite
"#!/bin/busybox ash"

So the sysadmin would still have to manually execute the "script" with an
appropriate shell.
-Rowan

On 16 November 2016 at 18:56, Wout Mertens  wrote:

> well, if you have a sysadmin that has "." in their path, you could write a
> database called "ls" or common misspellings of commands, and that way
> execute arbitrary code if the sysadmin visits the system.
>
> Wout.
>
> On Wed, Nov 16, 2016 at 11:43 AM Rowan Worth  wrote:
>
> > Interesting but doesn't seem overly practical. If you're in a postiion to
> > run 'busybox ash /foo/bar' then you can easily create a shell script
> > through simpler means.
> >
> > Filesystem access via ATTACH DATABASE is worth bringing attention to
> > though, as I think a lot of developers wouldn't expect that. It can be
> > mitigated via something like sqlite3_limit(db, SQLITE_LIMIT_ATTACHED, 1)
> if
> > you don't need ATTACH functionality (maybe with a limit of zero? haven't
> > tested it).
> >
> > Of course if you are paying proper attention to security you should
> already
> > be onto the SQL injection vectors that put ATTACH in user's hands anyway
> :)
> >
> > -Rowan
> >
> > On 16 November 2016 at 07:10, jungle Boogie 
> > wrote:
> >
> > > Hi All,
> > >
> > > Pretty interesting article:
> > > https://www.invincealabs.com/blog/2016/11/sqlite-shell-script/
> > >
> > > This post documents how we were able to create a SQLite database that
> > > can be executed as an ash shell script purely from SQL queries.
> > >
> > >
> > > Found here:
> > >
> > https://www.reddit.com/r/netsec/comments/5cwb07/sqlite_
> as_a_shell_script/
> > >
> > >
> > >
> > > --
> > > ---
> > > inum: 883510009027723
> > > sip: jungleboo...@sip2sip.info
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@mailinglists.sqlite.org
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database corruption, and PRAGMA fullfsync on macOS

[Simon Slavin]

On 16 Nov 2016, at 7:25am, Jens Alfke  wrote:

> The case I’m concerned about happens on Macs, as I said, and the developer 
> says he can trigger it in his app via a power failure or a forced shutdown 
> (holding the power button for 5 seconds.) This version of our library is one 
> I wrote, in Objective-C; it uses a thin Obj-C wrapper around SQLite, but it 
> doesn’t do anything magic, and although it started out as 3rd party code I’ve 
> tweaked it enough that I know it inside and out.

Ah, that's enough information that someone around here should be able to help.  
Can you use the shell tool to run

PRAGMA integrity_check

on one such corrupt file and post what it finds ?  If the output is long, 
posting a summary is fine.

There's also a tool from the SQLite team which can analyze a corrupt SQLite 
file and tell some things about how it is corrupt.  Unfortunately I can't 
remember what it's called or where to find it.  But I heard about it on this 
list and I hope someone can.

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

Re: [sqlite] SQLite as a Shell Script

[Wout Mertens]

well, if you have a sysadmin that has "." in their path, you could write a
database called "ls" or common misspellings of commands, and that way
execute arbitrary code if the sysadmin visits the system.

Wout.

On Wed, Nov 16, 2016 at 11:43 AM Rowan Worth  wrote:

> Interesting but doesn't seem overly practical. If you're in a postiion to
> run 'busybox ash /foo/bar' then you can easily create a shell script
> through simpler means.
>
> Filesystem access via ATTACH DATABASE is worth bringing attention to
> though, as I think a lot of developers wouldn't expect that. It can be
> mitigated via something like sqlite3_limit(db, SQLITE_LIMIT_ATTACHED, 1) if
> you don't need ATTACH functionality (maybe with a limit of zero? haven't
> tested it).
>
> Of course if you are paying proper attention to security you should already
> be onto the SQL injection vectors that put ATTACH in user's hands anyway :)
>
> -Rowan
>
> On 16 November 2016 at 07:10, jungle Boogie 
> wrote:
>
> > Hi All,
> >
> > Pretty interesting article:
> > https://www.invincealabs.com/blog/2016/11/sqlite-shell-script/
> >
> > This post documents how we were able to create a SQLite database that
> > can be executed as an ash shell script purely from SQL queries.
> >
> >
> > Found here:
> >
> https://www.reddit.com/r/netsec/comments/5cwb07/sqlite_as_a_shell_script/
> >
> >
> >
> > --
> > ---
> > inum: 883510009027723
> > sip: jungleboo...@sip2sip.info
> > ___
> > 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] SQLite as a Shell Script

[Rowan Worth]

Interesting but doesn't seem overly practical. If you're in a postiion to
run 'busybox ash /foo/bar' then you can easily create a shell script
through simpler means.

Filesystem access via ATTACH DATABASE is worth bringing attention to
though, as I think a lot of developers wouldn't expect that. It can be
mitigated via something like sqlite3_limit(db, SQLITE_LIMIT_ATTACHED, 1) if
you don't need ATTACH functionality (maybe with a limit of zero? haven't
tested it).

Of course if you are paying proper attention to security you should already
be onto the SQL injection vectors that put ATTACH in user's hands anyway :)

-Rowan

On 16 November 2016 at 07:10, jungle Boogie  wrote:

> Hi All,
>
> Pretty interesting article:
> https://www.invincealabs.com/blog/2016/11/sqlite-shell-script/
>
> This post documents how we were able to create a SQLite database that
> can be executed as an ash shell script purely from SQL queries.
>
>
> Found here:
> https://www.reddit.com/r/netsec/comments/5cwb07/sqlite_as_a_shell_script/
>
>
>
> --
> ---
> inum: 883510009027723
> sip: jungleboo...@sip2sip.info
> ___
> 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