
Select Code_execution from * Using SQLite; (2019) - porker
https://media.ccc.de/v/36c3-10701-select_code_execution_from_using_sqlite
======
ysleepy
Combined with the correctness fuzzing results by SQLancer [1] where SQLite had
179 issues revealed (vs. 11 in PostgreSQL), I think the 100% coverage is a lot
less meaningful than I had thought.

Robustness is not necessarily correctness and if the unit tests are just
mirror images of the code, their results may help against regressions but
little for correctness.

[1]: [https://www.manuelrigger.at/dbms-
bugs/](https://www.manuelrigger.at/dbms-bugs/)

~~~
masklinn
> Combined with the correctness fuzzing results by SQLancer [1] where SQLite
> had 179 issues revealed (vs. 11 in PostgreSQL), I think the 100% coverage is
> a lot less meaningful than I had thought.

That's really not a honest view of it, the SQLancer page specifically notes:

> The SQLite3 developers were most responsive and very appreciative of our bug
> reports. They fixed the bugs we reported at an impressive speed, which is
> why we concentrated on testing this DBMS.

They don't provide run times, but they're empathetically stating most of their
runtime is on SQLite. For instance they only have 5 issues for MariaDB
because… they stopped testing it as the devs were not responsive.

~~~
ysleepy
I am aware.

The thought I had could maybe better expressed as:

"If even SQLite, with its almost unsurpassed testing, has so many 'issues',
then that approach of testing cannot be seen as a strong indicator of
correctness."

Which is sort of a truism, but seeing the trend of more and more unit tests
instead of also investing in other strategies made me blurt that out.

That said, I will continue to use sqlite without hesitation, the robustness is
just soothing in a world of terrible libraries.

We can argue about the weak typing though :P

~~~
pdimitar
I love sqlite but I wish they invested in a strict mode. Although thinking
about it that's not really "a strict mode of the existing sqlite3" and it's
more likely to be "an entirely different library altogether, with some shared
code"...

I am basically after an embedded Potgres I suppose. I really wish sqlite went
into that space. Or anyone else, really.

------
yodon
I feel like I just watched the original first presentation of SQL injection -
this is that big a deal. (I can't find the link, but the guy who gave that
presentation thought it bombed because people started getting up and walking
out five minutes into his talk - it turned out they were trying to get to the
pay phones first so they could call back to their offices and alert them that
the world had just been lit on fire).

~~~
dleslie
IIRC it was Jeff Forristal who gave that talk, but I too am unable to find the
video.

------
jiofih
This is surprising. SQLite is known for having 100% code coverage, fuzzy
testing, and a 644:1 tests to code ratio. If even that cannot stop this kind
of attack then we really need to rethink computers from the ground up...

[https://sqlite.org/testing.html](https://sqlite.org/testing.html)

~~~
umvi
I'm not convinced.

If lockpicking and other ancient hobbies are anything to go on, it doesn't
matter how many redesigns you go through; people will just create new
specialized/innovative tools to break/attack the most popular ones.

~~~
Quekid5
Locks are physical devices and so are susceptible to all manner of wonderful
and surprising physical attacks.

Programming languages can artificially limit the attack surface. (Up to
hardware exploits which can subvert the processor itself.)

~~~
c22
I don't understand this reasoning at all. Locks attempt to artificially limit
the (physical) attack surface as well. This could, in fact, be a reasonable
_definition_ of a lock. So people come up with wonderful and surprising
configurations of matter that cause these artificial limitations to fail
providing direct access to the lock's root mechanism (via unanticipated
route).

This is analogous to virtual attacks on information systems which are hardened
against familiar routes of ingress and vulnerable to those which are wonderful
and surprising.

~~~
comex
I don’t know about the GP’s wording, but there _is_ a difference. It is
possible for a piece of software to be perfectly secure against software-based
attacks, i.e. there‘s no possible data you can send it that will cause it to
misbehave. It is not possible for a lock to be perfectly secure against
physical attacks; if nothing else, you can always break it with brute force.

Of course, even perfect software is susceptible to physical attacks on the
hardware it’s running on, but that’s different.

~~~
tsimionescu
> It is possible for a piece of software to be perfectly secure against
> software-based attacks, i.e. there‘s no possible data you can send it that
> will cause it to misbehave.

I don't think this is obvious, even for theoretical software, much less for
practical software. Even formal methods have limitations.

A very public recent example was of the formally proven logic in Intel and
other processors around speculative execution (Spectre, Meltdown etc.). That
logic was formally proven not to leak information, but the formal model wasn't
actually sophisticated enough. Note that these weren't hardware bugs (they
weren't bugs in the electrical components of the processor or in the analog
logic), they were logic bugs in the processor's code (whether that was
microcode or burned into the processor is less relevant).

In general, you can prove that a piece of software adheres to some
specification, but you can't prove that the specification itself is complete
enough.

Of course, realistic software has many more layers of uncertainty, and
realistic formal verification is far to costly to apply to anything but very
short programs.

~~~
anyfoo
Thanks, this is for once in this thread, a real caveat in the application
formal verification (or rigorous type systems). It's hard, it's not unlimited,
and one may have fail to state crucial properties that matter in the first
place.

Still, there are a lot of properties that are easy to state, and a lot of
properties that are feasible to prove, and the intersection of these two can
be very valuable in eradicating common issues in critical software, be it out-
of-bounds pointers or your garden variety type confusion. (Again, not always
possible, thanks to the halting problem, but often.)

The cost may indeed be prohibitive, though.

------
hanche
If I understand this correctly, you need a malicious sqlite database file,
which I don’t think you could create with sqlite itself. Which means, in order
to exploit this, you first have to get this malicious database onto their
computer. So while this seems serious enough, it may not be time to panic
quite yet.

~~~
progval
I'm guessing this submission is a reaction to another post on the frontpage:
"SQLite as an Application File Format"
[https://news.ycombinator.com/item?id=23508923](https://news.ycombinator.com/item?id=23508923)

In this case it's relevant, as untrusted SQLite files would be downloaded and
loaded in this context.

~~~
hanche
I see. I had thought of that more as a format for applications to store their
persistent data than for data interchange. When used for the latter, the
danger seems clear enough. (I also just noticed that the original post is half
a year old.)

~~~
recursivecaveat
If you're using it for persistent storage, its probably inevitable that a user
sends in their copy as part of a bug report and a developer cracks it open.

~~~
hanche
Good point.

------
petters
Seems like the fts3_tokenizer was disabled in a 2016 release:
[https://www.sqlite.org/releaselog/3_11_0.html](https://www.sqlite.org/releaselog/3_11_0.html)
(due to security concerns)

Did PHP7 still use an older version at the time of this talk (late 2019)?

I agree with the speaker that the interface of fts3_tokenizer is mind blowing.

~~~
rurban
But then the intrusion vector got enabled for backwards compatibility in some
distros.
[https://bugs.freebsd.org/bugzilla/show_bug.cgi?id=207924](https://bugs.freebsd.org/bugzilla/show_bug.cgi?id=207924)

~~~
petters
Thanks, that was unfortunate.

------
bob1029
I don't see the practical attack vector. For every use case I have for SQLite,
none of them involve allowing someone to execute arbitrary SQL against one of
my databases. We also don't do any loading of databases from untrusted
parties. Seems like a hypothetical scenario was invented here that could just
as well be applied to any database engine with similar outcomes.

~~~
ysleepy
The exploit works with any query performed on the sqlite db file.

The expected tables are replaced by views that execute the arbitrary sql.

~~~
SQLite
The attack is clever and original. AFAIK, nothing like it has ever been seen
before.

Since this attack came to light, SQLite has added features so that an
application can ensure that views and triggers do not have side-effects
(outside of the database file itself). And if there are no side-effects then
the attack is basically harmless. Sure, the attacker can still exfiltrate or
corrupt data, but the attacker had to have write access to the database file
in order to carry out the attack in the first place, so exfiltrating or
corrupting data is not an issue - they could already do that. See a quick
summary at
[https://sqlite.org/forum/forumpost/8beceed68e](https://sqlite.org/forum/forumpost/8beceed68e)

~~~
Drip33
Was the write a shell php file portion of the video/exploit patched?

Sites like [https://sqliteonline.com/](https://sqliteonline.com/) and
[https://inloop.github.io/sqlite-viewer/](https://inloop.github.io/sqlite-
viewer/) could be perpetually vulnerable if not?

------
saagarjha
Not sure why there are comments on this about SQLite's "correctness" or
"testing": these are "works as intended" oversights where you run a query and
SQLite does as you asked, except if you give an attacker too much control over
the query it gives an unpredicted amount of control over what it lets you do.
It's similar to command injection: bash could be 100% fuzzed and covered, but
if you pass a string into it unescaped it's still going to let an attacker run
code…

~~~
devwastaken
Yeah this appears to apply more to things like websql. But not in situations
where sqlite is used behind a web service that uses prepared statements and
best practices and such. If I'm wrong I'd like to know though.

------
ngneer
A lot of comments on this thread ponder the difference between logical and
physical, between software and hardware bugs. There is absolutely no
difference. As a kind reminder, computation is a physical process, period. Any
proofs of correctness apply only within the confines of the model in which
they are proven. To the folks fond of formal verification, methinks Kripke
agrees. To the folks presenting hardware bugs as a counterexample, it is not.
If you include hardware bugs in your security analysis, and you should, you
are merely taking a wider view of the system.

------
ufo
In the context of the recent discussion[1] about using an SQLite database as
an application file format, is there a way to mitigate these problems?

For example, a way to verify that the SQLite database has the exact schema
that you expect and doesn't have weird views configured to hijack your normal
queries?

[1]
[https://news.ycombinator.com/item?id=23508923](https://news.ycombinator.com/item?id=23508923)

------
thunderbong
>> We created a rogue SQLite database that exploits the software used to open
it.

So, here, they are replacing the whole database and then stealing the
passwords? I would guess that if a malicious actor has that kind of access to
the system, all is lost.

~~~
XMPPwocky
SQLite notably is often used as a file format. For example, for browser cookie
storage.

As you can see in the next sentence after your quote,

> Exploring only a few of the possibilities this presents we’ll pwn password
> stealer backends while they parse credentials files and achieve iOS
> persistency by replacing its Contacts database…

(The major browsers store passwords in SQLite databases, so password stealers
upload those to a backend. iOS makes it hard to run code persistently (if you
have a shell once, can you keep it after a reboot), so the Contacts database
is a target for persistence.)

------
schoen
(2019)

------
dilandau
>have to install a pre-corrupted database.

So basically a nothingburger.

These guys seem like they're just trying to get attention and make a name. I'd
say if someone has access to install a pre-corrupted file, code execution is
already a given.

~~~
ratorx
SQLite as an Application File Format (2014)
[https://news.ycombinator.com/item?id=23508923](https://news.ycombinator.com/item?id=23508923)

