SQLite Forum

Possible vulnerabilities from December 2019
Login

Possible vulnerabilities from December 2019

(1) By Simon Slavin (slavin) on 2020-06-13 17:37:14 [source]

Could a cracker take a look at this 46 minute video:

https://media.ccc.de/v/36c3-10701-select_code_execution_from_using_sqlite

and give us an idea what's going on ? It was presented to the Chaos Computer Club in December last year and I don't remember it being discussed here.

"we experimented with the exploitation of memory corruption issues within SQLite without relying on any environment other than the SQL language. [...]

How? We created a rogue SQLite database that exploits the software used to open it.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…"

As far as I can tell, the things it exploits aren't inside SQLite. The vulnerabilities come from an application assuming that anything stored in the database is safe. But I'm retired now and out of touch with current techniques.

(2) By Richard Hipp (drh) on 2020-06-13 18:44:45 in reply to 1 [link] [source]

It is a clever attack. The idea is that if you have an application that uses an SQLite database with a schema like this:

   CREATE TABLE xyzzy(...);

And if your application always

  1. Registers an application-defined SQL function "send_money()" upon start-up and then
  2. Runs "SELECT * FROM xyzzy WHERE..." thereafter

Then the attacker can modify your schema like this:

   ALTER TABLE xyzzy RENAME to xyzzy_real;
   CREATE VIEW xyzzy AS SELECT * FROM xyzzy_real
       WHERE send_money(...) IS NOT NULL OR 5<>11;

Then, when your application naively opens the database file and reads the xyzzy table, it will actually cause the view to run and send money to the attacker.

Features Recently Added to SQLite To Help Avoid This Attack.

  1. You can now specify that application-defined SQL functions are SQLITE_DIRECTONLY. This means that they can only be called from top-level SQL, not from triggers or views. The use of SQLITE_DIRECTONLY is recommended for any application-defined SQL function that has side-effects.

  2. There is the SQLITE_DBCONFIG_ENABLE_VIEW option which can be used to completely disable views, and hence shut down this attack. Recommended if your application does not need or use views. The corresponding SQLITE_DBCONFIG_ENABLE_TRIGGER option has been around for ages already. It too is recommended for security-sensitive applications that do not need or use triggers.

  3. You can run PRAGMA trusted_schema=OFF; to cause all historical application-defined SQL functions to act as if they were SQLITE_DIRECTONLY even if they were not declared as such. On by default for historical compatibility, but recommended for security-sensitive applications.

See the Defense Against Dark Arts page for additional discussion of these ideas.

(3) By anonymous on 2020-06-14 01:38:20 in reply to 2 [link] [source]

Python 2.7 doesn't expose sqlite3_db_config() as far as I can tell. Any tips on defense against these specific vectors?

(4) By Simon Slavin (slavin) on 2020-06-14 05:41:01 in reply to 3 [link] [source]

You're using Python 2.7, which is almost six months past past end-of-life. You have far more serious vulnerability problems than the extremely obscure one discussed in this thread. If you upgrade to a recent version of Python you'll get the security fixes to Python and also get the recent version of SQLite which includes the fixes DRH mentioned.

(5) By anonymous on 2020-06-14 13:48:17 in reply to 4 [link] [source]

Currently Python2 is loading SQLite 3.32.1

I don't think even Python3 exposes sqlite3_db_config either. This "obscure" attack vector is a very reproducible and viable one for a friend of mine. He allows people to upload custom SQLite databases and runs predictable (by the user) select queries on them.

(6) By Richard Hipp (drh) on 2020-06-14 13:51:58 in reply to 5 [link] [source]

Does he create custom SQL functions (or virtual tables) that have side-effects? If not, then I don't see how the attack vector would apply to him.

(7) By anonymous on 2020-06-14 13:56:03 in reply to 6 [link] [source]

No he allows people to upload .SQLite databases of their choosing (or creation), and runs a (known to an attacker) select only query on them. Eg: An attacker knows he will be running SELECT ABC, DEF, YXZ FROM TABLE on their upload then he'll do some short python magic with the results of the select query. Knowing this, is he safe from exploitation?

(8.2) By Larry Brasfield (LarryBrasfield) on 2020-06-14 15:02:11 edited from 8.1 in reply to 7 [link] [source]

The answer is: If he does not "create custom SQL functions (or virtual tables) that have side-effects", then there is no way that Richard Hipp sees for "the attack vector" (mentioned in this thread) to apply in that usage. (And note that this is not the same as the more general "exploitation".)

By "create custom SQL functions", Richard means functions that did not come with SQLite, and are loaded in the course of that usage.

By "create custom ... virtual tables", Richard means virtual tables that did not come with SQLite, and are loaded in the course of that usage.

The possible "side-effects" of concern are actions possibly taken by the custom function or virtual table code when input is other than expected, actions which, if not prevented, might include mischief of various kinds.

I spell the above out because it is obvious (to me, at least) from Richard's reply. It is also clear that his reply was predicated on the stated use of SQLite v3.32.1, and cannot be applied to an arbitrary earlier version.

Your unheard friend/advisee, if such custom code is loaded, needs to evaluate that code for vulnerabilities it may have when an arbitrary database is used.

(Edited to add caveats.)

(9) By anonymous on 2020-06-14 15:00:54 in reply to 8.1 [link] [source]

Right, I'm referring to the Original poster's video https://media.ccc.de/v/36c3-10701-select_code_execution_from_using_sqlite at approximately 4 minutes 32 seconds the attack vector is literally just knowing that the back end of a service will run a specific SELECT query which ends up granting the video creator the ability to write files to the system (or similar). Specifically, the video creator exploits:

"SELECT BodyRich FROM Notes" which is the same surface as my friend who runs a basic known select query on uploaded data.

It is not obvious to me if this more general attack vector is by default prevented in SQLite 3.32.1 and whether or not it is preventable by upgrading to Python3 even though I can't find any evidence that sqlite3_config() / sqlite3_db_config() is exposed in either version of Python.

Unfortunately neither of us are SQLite experts, hence me coming here asking for advice to prevent damages.

(10) By Richard Damon (RichardDamon) on 2020-06-14 15:47:47 in reply to 9 [link] [source]

The basic gist of the exploit described is that if you can provide an arbitrary database that the software will perform a known query on, then even if that system doesn't allow the user to provide an arbitrary SQL statement, you can bypass that through the carefully crafted database using triggers. Most exploits of arbitrary SQL deal with damaging or getting secrets out of the database, but if the attacker provided them, that really isn't an attack.

The issue pointed in the exploit, is that if there is a function that has external side effects, then being able to make a call to that would be an exploit. Mr. Hipp describes how recent versions of SQLite can be configured to protect the built-in functions that might be so exploited, and how to protect custom functions.

(12) By anonymous on 2020-06-14 16:29:42 in reply to 10 [link] [source]

Thank you. In my friend's case, a user can provide an arbitrary database that the software will perform a known query on because the software/python script is open source.

Since neither of us are experts in SQLite we aren't sure how to disable things like SQLITE_DBCONFIG_ENABLE_VIEW/SQLITE_DBCONFIG_ENABLE_TRIGGER unless Python will allow you to do it at runtime which it does not. I also asked one of the other guys if SQLite 3.32.1 disabled those by default which the answer seems to also be "no" therefore the service he is providing will have to be terminated for now.

There are no custom functions he's just running an open source Python script on arbitrary SQLite data provided by users which runs as mentioned, known SELECT statements since it's open source. No SQL queries are provided by users just arbitrary potentially malicious databases.

(15) By Richard Hipp (drh) on 2020-06-14 17:17:43 in reply to 12 [link] [source]

If you have:

  1. An arbitrary attacker-supplied schema
  2. Fixed, well-known, application-defined queries
  3. No custom SQL functions or virtual tables

Then the worst that can happen is an infinite loop and/or using a large amount of RAM.

If you want to defend against even that possibility, then you can scan the database schema for instances VIEWs that have the same names as what the application expects to be tables. Suppose the application is running queries that involve table "tab_abc", "tab_def", and "tab_ghi". Then to look for possibly malicious schemas:

   SELECT 1 FROM sqlite_master
    WHERE type='view'
      AND lower(name) IN ('tab_abc','tab_def','tab_ghi');

(11.1) By Larry Brasfield (LarryBrasfield) on 2020-06-14 15:57:27 edited from 11.0 in reply to 9 [link] [source]

You've gotten a reasonably clear answer to your concern from the world's foremost expert on SQLite.

The specific attack, foreclosed in many cases [a] by the "Features Recently Added to SQLite To Help Avoid This Attack" outlined in post 2 above by said expert, is foreclosed in SQLite 3.32.1 provided that loading of extensions is not explicitly enabled prior to running those known queries on a database of untrustworthy origin and no custom functions or virtual tables are created.

[a. The "in many cases" clause is there because the presence of custom functions or virtual tables in the running SQLite+extensions image may open the same kind of attack foreclosed when running non-extended SQLite of recent vintage. ]

If you look at that video, you can see that there is more to the attack than merely running a SELECT known ahead of time to the attacker. The "more" is to have arranged for that query to actually resolve to evaluation of a malicious (and so necessarily custom) function (or, by logical extension, arbitrary code loaded via the extension mechanism.) That is why Richard added qualification regarding custom functions or virtual tables. Without those possibilities, which are under the application's control, recent versions of SQLite foreclose the form of attack described in that video (and well summarized in Richard's post 2 in this thread.)

(Edit: s/opens the same kind of attack/may open the same kind of attack/)

(13) By anonymous on 2020-06-14 16:47:32 in reply to 11.1 [link] [source]

The specific attack, foreclosed in many cases [a] by the "Features Recently Added to SQLite To Help Avoid This Attack" outlined in post 2 above by said expert

My main question was are those features enabled (well disabled) by default in SQLite out of the box 3.32.1 installations, and if not can I do so at runtime in any version of Python. The answer seems to be no for both. I/we have no idea how to implement section 1.2 #8 and #9 (especially 9) of this page https://www.sqlite.org/security.html so the service will be disabled until such time that he/we figures it out.

The entire service is just running open source Python scripts on user provided databases so the user doesn't have to install tools to do so.

I apologize that this may seem repetitive but the comments on Hacker News from yesterday seem to agree that in some cases like this one, simply running a known SELECT statement on untrusted data can be exploited without those mitigations in the linked page and without any special custom functions created/used by the open source application. In the video, the guy seems to create his own VIEWs and store them within the database.

But then you go on to say:

If you look at that video, you can see that there is more to the attack than merely running a SELECT known ahead of time to the attacker. The "more" is to have arranged for that query to actually resolve to evaluation of a malicious (and so necessarily custom) function (or, by logical extension, arbitrary code loaded via the extension mechanism.) That is why Richard added qualification regarding custom functions or virtual tables. Without those possibilities, which are under the application's control, recent versions of SQLite foreclose the form of attack described in that video (and well summarized in Richard's post 2 in this thread.)

Which makes me think a default out of the box version of SQLite would not be vulnerable in my described case.

(18) By Larry Brasfield (LarryBrasfield) on 2020-06-14 17:39:16 in reply to 13 [link] [source]

... "Features Recently Added to SQLite To Help Avoid This Attack" outlined in post 2 above by said expert

My main question was are those features enabled (well disabled) by default in SQLite out of the box 3.32.1 installations, ...

Those features are operative in SQLite 3.32.1 "out of the box" or built without inactivating those features. For backward compatibility, some builds by SQLite library users might choose the defaults differently from what is most secure.

... and if not can I do so at runtime in any version of Python. The answer seems to be no for both. I/we have no idea how to implement section 1.2 #8 and #9 (especially 9) of this page https://www.sqlite.org/security.html so the service will be disabled until such time that he/we figures it out.

Re 1.2.(#9) Disabling triggers and views add another layer of prevention of arbitrary SQL interpretation. But arbitrary SQL is not a security risk when all it can do is modify or query the potentially malicious database. The security risk arises when interpreting/executing SQL of the attacker's choice resolves to loading extensions such as custom functions and/or virtual tables, which are in turn given inputs (from the attacker-designed SQL) that acts to exploit side effects in the machine code implementing those functions or virtual tables. That code can be problematic, either because it was poorly written and so allows one/some of the traditional buffer overrun exploits, or because it is (somehow) the attacker's own machine code, caused to be loaded via the extension mechanism.

Re 1.2.(#8) This begins by saying "If the application includes any custom SQL functions or custom virtual tables that have side effects". If does not happen, because that was not done intentionally and it is prevented from being done by an attacker, then this section's remedy does not have to be applied. This is why Richard's qualification (or predicate question, really) was stated. Recent versions of SQLite, by default, do not permit loading extensions given only SQL that attempts to do so. Hence, if no custom extension code is loaded (which includes being linked into the application and loaded when the application loads), by the application, and extension loading via SQL execution remains disabled (as it is in recent versions), then the exploit which is the subject of this thread is blocked. If these preconditions do not hold, then a more complex analysis and more context needs to be considered if exploits are to be precluded.

the comments on Hacker News from yesterday seem to agree that in some cases like this one, simply running a known SELECT statement on untrusted data can be exploited ...

Of course, commenters at Hacker News might pull parts of the exploit scenario out of context, dropping the dependence upon extension loading and/or custom code being run. I have seen nothing to indicate that SQLite as released today (and for a few months, at least), built with the recent security provisions having their default behavior, has a vulnerability which can be exploited merely by executing arbitrary SQL chosen by an attacker.

But then you go on to say: ...

and you go on to say:

Which makes me think a default out of the box version of SQLite would not be vulnerable in my described case.

I still believe that, provided that no custom functions or virtual tables are being linked/loaded. (Whether such custom code might be run in your (friend's) application is not evident here.) If such custom code is in the application image as finally loaded, then (as I said before) it will need to be examined for the possibilities arising from execution of arbitrary SQL. If it is well designed, without intentional side effects (such as running shell scripts to process SQL-provided text) and without unintentional side effects due to coding flaws, then it may well be immune to the sort of attack discussed in this thread.

(20) By anonymous on 2020-06-14 18:00:34 in reply to 18 [link] [source]

Keith Medcalf in post #17 has clarified everything I've written and we'll try his solution.

I still believe that, provided that no custom functions or virtual tables are being linked/loaded. (Whether such custom code might be run in your (friend's) application is not evident here.) If such custom code is in the application image as finally loaded, then (as I said before) it will need to be examined for the possibilities arising from execution of arbitrary SQL. If it is well designed, without intentional side effects (such as running shell scripts to process SQL-provided text) and without unintentional side effects due to coding flaws, then it may well be immune to the sort of attack discussed in this thread.

The open source application code literally does nothing more than what the (PHP) code in the video showed, specifically the lines of python in use here are

db = sqlite3.connect(file) cursor = db.cursor() cursor.execute("SELECT ABC, DEF FROM TABLE")

Then it reads the data and does absolutely nothing unusual with it other than convert to utf8/base64 for example.

The proof of concept exploitable lines in the video are literally

$db = new SQLite3($fileDB); $Datax = $db->query('SELECT BodyRich FROM Notes');

Thank you (and everyone else) for the help. I will try post #17's solutions and see if they help him out.

(17) By Keith Medcalf (kmedcalf) on 2020-06-14 17:22:16 in reply to 9 [link] [source]

After you open the database connection issue the command:

pragma trusted_schema=0;

to specify that you do not trust the database.

(16) By Keith Medcalf (kmedcalf) on 2020-06-14 17:18:10 in reply to 4 [link] [source]

It is not past "end-of-life", it still works perfectly and will continue to work as it does now forever into the future.

I think what you mean is that it has entered "the age of stability" in that it is now stable and that as it works today so it will work tomorrow, for all values of today until the heat death of the multiverse.

(21) By Simon Slavin (slavin) on 2020-06-14 18:13:13 in reply to 16 [link] [source]

You're making up your own definitions. Or you think you know more about Python than the Python development team. On the Python download page

https://www.python.org/downloads/

It says that maintenance status of Python 2.7 is 'end-of-life' and the 'End of support' date for Python 2.7 was 2020-01-01.

(22) By Larry Brasfield (LarryBrasfield) on 2020-06-14 18:52:14 in reply to 21 [link] [source]

Let's face the fact, Simon, that "end-of-life" is a misnomer if taken too literally and nearly meaningless otherwise, except as a clue that one should know more before relying on it anew.

While Keith's interpretation of that vague term is whimsical, it is closer to what an accurate (but perhaps unwieldy) term would be. There is no pretension in it of better knowledge than the Python devs (which is another amorphous category.)

(23) By Keith Medcalf (kmedcalf) on 2020-06-14 19:23:26 in reply to 21 [link] [source]

Well, no, I am not doing any such thing. I am merely expressing the real-world view that "Support" does not have any magical value and the lack of "Support" has nothing whatsoever to do with the "Life" of a particular piece of computer code.

This is not to say that "Support" may not have some value on its own, however, "Support" is not required for something to work tomorrow exactly as it works today, for all values of today (and tomorrow). In fact, the entire premise of "Support" is that the supported thing will absolutely NOT work tomorrow as it did today.

Code Defects (bugs and or vulnerabilities to exploit) do not suddenly sprout out of nowhere in unchanging code. They do not "grow" like trees and weeds with the passage of time. They were there from the very first moment. All that happened is that somebody "discovered" their existence and published about them on the front-page of the local rag bringing them to the attention of those who did not have the foresight to protect against the unknown or to take reasonable precautions.

It is like if you went to a glass vendor and decided to buy some one-way glass for your bedroom window. Then eventually nude pictures of you appear on the front page of the local rag taken by a photographer over the road through your window. Does this mean that your one-way window "suddenly" became vulnerable to being looked through? No, the vulnerability existed from the moment the window was installed -- it did not suddenly manifest out of thin air. You simply did not know about it until the nude pictures appeared on the front page of the local rag. Reasonable people might have taken precautions such as hanging drapes and closing them when getting naked, thus avoiding the whole issue (the fact that the seller of the so-called one-way glass offered no indemnity in case they were incorrect in their assertion of one-wayness would have provided a clue by four here).

I would take issue with the "end-of-life" as there is no such thing. The "Support" may be gone, which means exactly and precisely that as the thing works today so shall it be for time eternal. It is not dead. It does not even behave differently next week, next month, next year, or next century to how it behaves today -- it is in fact impossible for the behaviour to change.

End-of-Life is a thing which applies, obviously, only to living things -- obviously something which is not living cannot have an end of life. The correct term for inanimate objects may be "end-of-function" (or kaput). Computer code certainly is never at end-of-life or end-of-function, for that is clearly an impossibility.

(24) By sean (jungleboogie) on 2020-06-14 20:16:51 in reply to 23 [link] [source]

For the record, I enjoyed the imagery of tress and weeds growing more so than the one-way glass analogy. Both are very valid and make a good point of non-living things vs. living things.

(14.1) By Keith Medcalf (kmedcalf) on 2020-06-14 17:24:37 edited from 14.0 in reply to 3 [link] [source]

I think what you mean to say is that the default pysqlite2 (renamed as sqlite3) python wrapper does not expose the sqlite3_db_config interface, which is exactly correct. This is nothing to do with the Python version, but rather the wrapper library.

This is because pysqlite2/sqlite3 is a DB-API wrapper for SQLite3. It is designed to be "compatible" with other DB-API wrappers for other databases and provides no access to anything except the "lowest common denominator" functionality of every database system ever invented.

If you wish to use an SQLite3 wrapper for Python that implements a (nearly) complete interface to the SQLite3 database engine, then they are available. Take a look at the most excellent APSW written and maintained by Roger Binns, Roger is/was also a frequent contributor here.

Full Documentation is also available in contrast to the pysqlite2/sqlite3 documentation, which is rather sparse.

It can be compiled and works for all versions of CPython (not PyPy unfortunately), or you can Download the pre-built versions.

APSW is NOT a DB-API compliant wrapper for the SQLite3 database. It is designed to permit access to SQLite3 features that are specifically SQLite3 features. Be sure to read the description for some of the differences.

(19) By anonymous on 2020-06-14 17:54:36 in reply to 14.1 [link] [source]

Yes everything you said is accurate. I will investigate this option thank you.