database like file archive

classic Classic list List threaded Threaded
15 messages Options
Reply | Threaded
Open this post in threaded view
|

database like file archive

Peng Yu
Hi,

I haven't found an archive format that allows in-place delete (I know
that .zip, .7z and .tar don't). This means that whenever delete is
needed, the original archive must be copied first. This can be
problematic when the archive is large and the file to delete is small.

Something along the line of the ability of sqlite3 to perform in-place
delete might be a useful feature for archives. But I haven't found any
such archive format. Does anybody know one? Thanks.

--
Regards,
Peng
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: database like file archive

Philip Bennefall
There is the sqlar archive format, which you can test using the official
sqlite3 command line shell. There is also a library for it as part of
the Sqlite3 repository.


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

Kind regards,

Philip Bennefall
On 8/27/2019 3:56 PM, Peng Yu wrote:

> Hi,
>
> I haven't found an archive format that allows in-place delete (I know
> that .zip, .7z and .tar don't). This means that whenever delete is
> needed, the original archive must be copied first. This can be
> problematic when the archive is large and the file to delete is small.
>
> Something along the line of the ability of sqlite3 to perform in-place
> delete might be a useful feature for archives. But I haven't found any
> such archive format. Does anybody know one? Thanks.
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: database like file archive

Dominique Devienne
In reply to this post by Peng Yu
https://sqlite.org/sqlar/doc/trunk/README.md

On Tue, Aug 27, 2019 at 3:57 PM Peng Yu <[hidden email]> wrote:

> Hi,
>
> I haven't found an archive format that allows in-place delete (I know
> that .zip, .7z and .tar don't). This means that whenever delete is
> needed, the original archive must be copied first. This can be
> problematic when the archive is large and the file to delete is small.
>
> Something along the line of the ability of sqlite3 to perform in-place
> delete might be a useful feature for archives. But I haven't found any
> such archive format. Does anybody know one? Thanks.
>
> --
> Regards,
> Peng
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: database like file archive

Peng Yu
In reply to this post by Philip Bennefall
> There is the sqlar archive format, which you can test using the official
> sqlite3 command line shell. There is also a library for it as part of
> the Sqlite3 repository.
>
> https://www.sqlite.org/sqlar.html

> https://sqlite.org/sqlar/doc/trunk/README.md

This is good to know.

How to install it? In homebrew's sqlite package, I don't find sqlar. I
use Mac OS X.

--
Regards,
Peng
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: database like file archive

Richard Hipp-3
On 8/27/19, Peng Yu <[hidden email]> wrote:
>
> How to install it? In homebrew's sqlite package, I don't find sqlar. I
> use Mac OS X.
>

The standard "sqlite3" command-line tool will read and write SQLite
archive files.  See the documentation at
https://www.sqlite.org/sqlar.html#managing_an_sqlite_archive_from_the_command_line
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: database like file archive

Philip Bennefall
In reply to this post by Peng Yu
The earliest version of the shell which ships with the archive support
is 3.22.0, according to the page I linked to. If you have an earlier
version you could simply grab the Mac OSX precompiled binaries from the
download page on sqlite.org and you'll be good to go.


Kind regards,


Philip Bennefall


On 8/27/2019 4:18 PM, Peng Yu wrote:

>> There is the sqlar archive format, which you can test using the official
>> sqlite3 command line shell. There is also a library for it as part of
>> the Sqlite3 repository.
>>
>> https://www.sqlite.org/sqlar.html
>> https://sqlite.org/sqlar/doc/trunk/README.md
> This is good to know.
>
> How to install it? In homebrew's sqlite package, I don't find sqlar. I
> use Mac OS X.
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: database like file archive

Peng Yu
In reply to this post by Richard Hipp-3
> The standard "sqlite3" command-line tool will read and write SQLite
> archive files.  See the documentation at
> https://www.sqlite.org/sqlar.html#managing_an_sqlite_archive_from_the_command_line

OK. So there is basically no need to install the sqlar command since
all features from the sqlar command is accessible via the sqlite3
command? Thanks.

--
Regards,
Peng
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: database like file archive

Jens Alfke-2
In reply to this post by Philip Bennefall


> On Aug 27, 2019, at 7:06 AM, Philip Bennefall <[hidden email]> wrote:
>
> There is the sqlar archive format, which you can test using the official sqlite3 command line shell. There is also a library for it as part of the Sqlite3 repository.

"An SQLite Archive is an ordinary SQLite database file that contains the following table as part of its schema…"

Archive files often get transferred between people. Using this format for that purpose would involve opening and reading untrusted SQLite database files. Is that safe? Could maliciously corrupting the schema or other metadata of a database cause security problems for the client accessing the database?

(I'm thinking not just of a separate `sqlite3` process accessing the archive, but also of the archiving code running inside some other process — consider a web browser or file manager extracting a sqlar archive.)

There were some security issues that came up recently involving the Chrome browser allowing untrusted JS code to run SQLite queries on local database files. But the scenario I'm thinking of is kind of the reverse — the queries are trusted but the database itself isn't.

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: database like file archive

Simon Slavin-3
On 27 Aug 2019, at 7:47pm, Jens Alfke <[hidden email]> wrote:

> Archive files often get transferred between people. Using this format for that purpose would involve opening and reading untrusted SQLite database files. Is that safe? Could maliciously corrupting the schema or other metadata of a database cause security problems for the client accessing the database?

You're thinking of an exploit like a ZIP bomb.  This is a small, maliciously-constructed ZIP file which expands into a huge amount of contents.  A well-known example is a 42 kilobyte zip file which unzips into 4.5 petabytes of contents.

Other problems include overwriting in-archive filenames with illegal characters like a colon and a slash, then relying on oversights in OS routines to do nasty things to your file structure.

I'm going to let the devs handle this one.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: database like file archive

Keith Medcalf
In reply to this post by Jens Alfke-2

On Tuesday, 27 August, 2019 12:47, Jens Alfke <[hidden email]> wrote:

>Archive files often get transferred between people. Using this format
>for that purpose would involve opening and reading untrusted SQLite
>database files. Is that safe? Could maliciously corrupting the schema
>or other metadata of a database cause security problems for the
>client accessing the database?

Everything that has been touched by a third-party is inherently untrustworthy.  Thus it is and thus it has always been.  Even ZIP files have a database schema that can be manipulated as does everything else.  There is no difference other than the misplaced assumption of trust.

>(I'm thinking not just of a separate `sqlite3` process accessing the
>archive, but also of the archiving code running inside some other
>process — consider a web browser or file manager extracting a sqlar
>archive.)

And how is this in anyway different from a zip process, or a rar processess or an uncompress process or any or a number of possibly trustworthy programs processing data coming from an untrustworthy source?  (which includes things like Web Browsers, Video Players, and on and on)

>There were some security issues that came up recently involving the
>Chrome browser allowing untrusted JS code to run SQLite queries on
>local database files. But the scenario I'm thinking of is kind of the
>reverse — the queries are trusted but the database itself isn't.

Chrome is a Google product.  Google's only revenue source is selling information that they have obtained from third-parties by clandestine means.  As such, nothing which bears a Google (or Alphabet) name can be considered in any way trustworthy.  One must assign trust having an eye to this fact and evaluate all statements made in light of this truth.  So Google Chrome permitting untrusted JS to run SQLite queries on local database files should be expected and is not a security problem.  How else would Google make money?

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: database like file archive

Jens Alfke-2


> On Aug 27, 2019, at 12:21 PM, Keith Medcalf <[hidden email]> wrote:
>
> Everything that has been touched by a third-party is inherently untrustworthy.  Thus it is and thus it has always been.  

Yes. I have a lot of experience with network coding and security, so I'm aware of this, thanks. My question was simply whether SQLite itself is considered safe when operating on an untrusted database file.

> Even ZIP files have a database schema that can be manipulated as does everything else.

The layout of a Zip file is vastly simpler than a SQLite database.
A Zip codec does not include an interpreter for a sophisticated programming language.
Zip files do not contain program code that runs when the file is read; SQLite databases can.

> And how is this in anyway different from a zip process, or a rar processess or an uncompress process or any or a number of possibly trustworthy programs processing data coming from an untrustworthy source?  (which includes things like Web Browsers, Video Players, and on and on)

Codecs used in such apps are considered attack surfaces and are screened for vulnerabilities. (For example, Google found and fixed some security holes in the TrueType font renderer when they added web-font support to Chrome.) This is precisely what I'm asking about SQLite — is it engineered with the assumption that a database file may be malicious, or is the assumption "garbage in, garbage out"?

> Chrome is a Google product.  Google's only revenue source is selling information that they have obtained from third-
[anti-Google ranting removed]

This is not only off-topic and inaccurate (Google has many other revenue sources), it's the sort of scenery-chewing conspiracy theorizing that's beneath someone with your level of expertise. Check yo'self.

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

SQLite vulns

Simon Slavin-3
On 27 Aug 2019, at 9:40pm, Jens Alfke <[hidden email]> wrote:

> My question was simply whether SQLite itself is considered safe when operating on an untrusted database file.

A worthwhile question which I discussed a little in an earlier post.

The SQLite devs take this question extremely seriously.  SQLite is not meant to crash.  SQLite is not meant to execute untrusted code.  It should not be possible for the contents of any file SQLite opens to influence the way SQLite works.

You will see a fast, high-quality response to identification of any such vulnerability, as has happened in the past.

I'll add a note here about what 'untrusted' means.  A programmer can tell SQLite to execute external code.  This can be done using external functions, external collations, external routines (e.g. the busy handler callback), and other things.  These things must be possible for SQlite to work properly.  However having SQLite execute external code is something that only the programmer should be able to do.  It should not be possible to make it happen using a carefully-crafted database file, or even through some action by the user.  And as far as we know, it isn't.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: database like file archive

Keith Medcalf
In reply to this post by Jens Alfke-2

On Tuesday, 27 August, 2019 14:40, Jens Alfke <[hidden email]> wrote:

>> On Aug 27, 2019, at 12:21 PM, Keith Medcalf <[hidden email]> wrote:

>> Everything that has been touched by a third-party is inherently
>> untrustworthy.  Thus it is and thus it has always been.

> Yes. I have a lot of experience with network coding and security, so
> I'm aware of this, thanks. My question was simply whether SQLite
> itself is considered safe when operating on an untrusted database
> file.

Considered by whom -- and what is the trustworthiness of that "whom" to be making that assessment?

Personally speaking I find the developers of SQLite to be trustworthy and generally trust that SQLite3 will operate in accordance with its design.  Furthermore, I trust that any issues where SQLite does not operate in accordance with its design will be fixed by said developers.  

I also trust that when presented with a trustworthy database file the library will operate in a trustworthy fashion.  The necessary inference therefore is that if presented with an untrustworthy database file, then the whole shebang is untrustworthy.

This inference would stand no matter what trustworthy party may claim that SQLite3 is trustworthy in the face of untrustworthy input.  (And this applies to everything not just SQLite3).  To assume otherwise is fraught with peril and is the cause of much security problems in the world today.

>> Even ZIP files have a database schema that can be manipulated as
>> does everything else.

> The layout of a Zip file is vastly simpler than a SQLite database.
> A Zip codec does not include an interpreter for a sophisticated
> programming language.
> Zip files do not contain program code that runs when the file is
> read; SQLite databases can.

In fact, if one forgets about ZIP (presumably meaning something trustworthy like Info-ZIP) then there are in fact a great many things that are capable of executing arbitrary code contained within what is ostensibly data, often by design.  Can one trust that any given program when presented with untrustworthy input will not execute arbitrary code?  No.  One should expect that any given program when presented with untrustworthy input may operate in an untrustworthy manner, unless it is manifestly impossible for this to occur.  Since determination of "manifestly impossible" is huge in scope, it is only done in very rare instances.

Also, I do not think that SQLite, by design, executes any code that is stored within the database, assuming a trustworthy database file.  However, if you are asking if one can craft an (untrustworthy) database file that may execute code contained within the database when it is opened, then this again devolves into the fact that the database file is itself untrustworthy, and as such all such bets are off.

>> And how is this in anyway different from a zip process, or a rar
>> processess or an uncompress process or any or a number of possibly
>> trustworthy programs processing data coming from an untrustworthy
>> source?  (which includes things like Web Browsers, Video Players, and
>> on and on)

> Codecs used in such apps are considered attack surfaces and are
> screened for vulnerabilities. (For example, Google found and fixed
> some security holes in the TrueType font renderer when they added
> web-font support to Chrome.) This is precisely what I'm asking about
> SQLite — is it engineered with the assumption that a database file
> may be malicious, or is the assumption "garbage in, garbage out"?

And this is a known security problem (untrustworthy font files) that has existed for as long as I can recall (at least since GOOEYs were invented).  The solution is, of course, to not permit the processing of files (including font files) that originate from untrustworthy sources.  Just because one such hole has been found and fixed does not mean that others do not exist.  In fact, it means that it is more likely that such holes exist but merely have not been published in the local tabloid yet.  Nonetheless, it is again a matter of assigning trust and trustworthiness and the boundaries that apply to this trust.

So, directly to your question "SQLite — is it engineered with the assumption that a database file may be malicious, or is the assumption "garbage in, garbage out"?  The answer of course is a matter of trust.  Yes, SQLite is engineered to operate in a trustworthy fashion even when presented with untrustworthy input and in fact the developers endeavour to fix any such issues found/reported.  This however does not preclude the possible existence of situations where this is not the case or have not otherwise yet been reported and fixed.

So again, we come back to the fact that no matter how trustworthy something is when presented with trusted inputs, its behaviour when presented with untrustworthy input cannot be guaranteed.  That is to say that for all pipelines A -> B -> C where either of A or B is untrustworthy, then C must be untrustworthy as well (unless, of course, it is manifestly impossible for B to produce an untrustworthy result, which is extremely rare).

>> Chrome is a Google product.  Google's only revenue source is
>> selling information that they have obtained from third-
>> [anti-Google ranting removed]

> This is not only off-topic and inaccurate (Google has many other
> revenue sources), it's the sort of scenery-chewing conspiracy
> theorizing that's beneath someone with your level of expertise. Check
> yo'self.

Perhaps they do have other sources of revenue but none come to mind, it is, I believe, an accurate assessment.

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: database like file archive

Richard Hipp-3
In reply to this post by Jens Alfke-2
On 8/27/19, Jens Alfke <[hidden email]> wrote:
> is [SQLite] engineered with the
> assumption that a database file may be malicious, or is the assumption
> "garbage in, garbage out"?

https://www.sqlite.org/security.html
https://www.sqlite.org/testing.html

Our intent is that SQLite database files are secure in the sense that
reading a maliciously corrupted database file is harmless.  I spend
most of my time testing for this sort of thing, and dreaming up new
defenses against yet undiscovered attacks.

Research out of the University of Buffalo shows that every Android
phone has about 200 SQLite database files and about 14% of those are
used for transfer purposes only.  In other words, content is
downloaded from the cloud as an SQLite database then becomes read-only
on the phone.  That's about 70 billion databases used as containers.
So lots of people are using SQLite as a container.  And those numbers
are for Android only.  Indications are the iOS is even bigger.

We work very hard to ensure that those billions and billions of data
containers in circulation are not a security risk.

That said, Jens remarks made me realize that the SQLite archive code
has not been updated recently to implement the best practices outlined
in the first document above.  The existing code is safe.  But I'll get
busy and add the extra layers of defense to make it even safer.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: database like file archive

Rowan Worth-2
In reply to this post by Peng Yu
On Tue, 27 Aug 2019 at 21:57, Peng Yu <[hidden email]> wrote:

> I haven't found an archive format that allows in-place delete (I know
> that .zip, .7z and .tar don't). This means that whenever delete is
> needed, the original archive must be copied first. This can be
> problematic when the archive is large and the file to delete is small.
>
> Something along the line of the ability of sqlite3 to perform in-place
> delete might be a useful feature for archives. But I haven't found any
> such archive format. Does anybody know one? Thanks.
>

Note that you wouldn't actually reclaim any disk space by deleting a file
in-place from an archive backed by sqlite -- unless you issue a VACUUM,
which rewrites the whole DB. Without a VACUUM, the free pages within the DB
would only be taken advantage of by future additions to the archive.

That may be an acceptable compromise for the
delete-small-file-from-large-archive use case you're presenting, but the
fact that archive formats are generally designed to minimise storage
requirements goes a long way towards explaining why the scenario is not
well-catered for. It also raises the question of whether an archive is the
correct tool for the job!

-Rowan
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users