Hacker News Reader

Comment by rusanu | Hacker News Reader

From OP: "SQLite is much faster than direct writes to disk on Windows when anti-virus protection is turned on. Since anti-virus software is and should be on by default in Windows, that means that SQLite is generally much faster than direct disk writes on Windows."

I don't get this. If scanning the content is important (as acknowledged by the author), then bypassing the scan via blob storage is a security issue and the application should go through some extra hoops to scan the content before saving it to blob, and this should be measured and part of the comparison.

Also, if the SQLite files are exempt from AV scan, then the level field should also exempt the uploaded files folder in test. I mean, knowing the dice are loaded and then claiming it as an advantage does not seem professional.

View on HN

Data chunks inside another file are effectively quarantined by default. There is no exempting or security issue, it's safe by design. It will get scanned occasionally but not on every write.

Except when they are not. Just because malcode is encapsulated doesn't mean it is neutralised. The issue is untrusted data, not how it is stored.

Word Docs with embedded content or can trigger code execution on parsing, data structures that break AV parsers, image or font data that causes system libraries to overflow/UAF/etc; There all could potentially be a security issue in a DB, the same as in a FS.

You don't need to worry about a single process writing data to a file and then reading it back and performing malicious actions. That process could just as easily have been exploited entirely in-memory. Scan the database when it exits and you're fine.

Great catch. The whole premise of the article is undermined by that.

The article may be unclear on this point: anti-virus was turned off for these tests. What that paragraph is pointing out is that performance tanks badly (roughly a factor of 10x) when you turn on Windows Defender, and presumably even worse with other anti-virus software.

Credential: I was one of several people who vetted the article before it was published. I argued that Defender should be enabled, since it is a platform default. D. Richard Hipp chose to take the charitable path and not report those results alongside the others.

The test is easily replicated yourself. The source code for it is part of the SQLite source tree, and command lines for building and running the test are given in the article.

Thanks for the clarification.

In any case, I really take issue with that approach. Nearly every windows database vendor, including Microsoft, recommends or requires that you disable most types of scanning in databases. Not only is it ineffective and performance impacting, it potentially undermines integrity of the data.

My contention was that this article is about least-effort methods (i.e. fopen() vs default SQLite) so a lot of Windows SQLite users are going to have Defender (or heaven help them, something much worse) installed.

Once you start getting into optimizations, much of this article starts to sway back and forth. Put enough effort into optimization, and you can outdo SQLite for a specific application, simply because SQLite isn't magic, it's just C. Well-written and highly-optimized C, but purpose-written code always has the potential to do better than general-purpose code.

The point of SQLite in general and this report in specific is that you get a lot of performance, power, and safety for free with SQLite as opposed to `fopen()` and such. Good performance while antimalware interferes is just one way this manifests.

Can you clarify then wether the AV was simply not scanning the SQLite files (ie. the file extension or the file location was exempt in AV config) ?

As I understand it, drh simply disabled Windows Defender entirely for these tests.

SQLite gets an additional advantage with Defender enabled because Defender doesn't try to scan inside the SQLite DB file for the sub-files, whereas with separate files, Defender must at least look at each file's header to determine whether it's a potentially dangerous file type.

While I was vetting this article, I did some of my own tests, and I found that simply excluding the directory containing the test tree was sufficient to get the same benefit.

My point in other posts here is that you don't need to disable Defender to get this benefit when you use SQLite.

But let's be clear: the 35% claimed performance benefit has nothing to do with Windows Defender. That's a wholly separate issue. Defender pays more attention to a pile of individual files than to an amalgamation of that same data into a single file, and this magnifies SQLite's advantages, but it is not the source of the advantage the linked report talks about.

Reply on news.ycombinator.com