Hacker News Reader

Comment by viraptor | Hacker News Reader

So in short... if anyone gets the chance of an sql injection, they have access to all tables and all schemas from any app that can access your database. Privilege separation is a very good idea. Not opening the database file directly is a very good idea too.

Just to be clear, I'm not saying every application needs it. If you actually use it as a replacement for local files, that's great. If you use a multi-user app containing sensitive data - "root password (wtf, everybody hate passwords)" this is so wrong. Reminds me of "chmod 777 everything" - wtf, everybody hates dealing with permissions.

View on HN

The gp point that sqlite gets rid of all the "stuff that has really nothing to do with storing and querying data" still stands.

Sqlite eliminates almost all of the management overhead that other databases force on you, so its trivial to have one database per application. Indeed, I have never seen anyone bother with a multi-application sqlite db, which could also cause serious performance problems. So that renders you sql injection point moot.

As for permissions, its better to let the OS handle that than some half-baked database permissions system. Root accounts/passwords are a wtf in a production environment because they destroy auditability. If you really want permissions, it is trivial to create system user accounts for the application and assign the correct file permissions, or even isolate particularly sensitive information in its own file. Combine this with AppArmor to further lock down individual applications.

All of this is overkill, but so is a full blown RDBMS in many cases. If you are starting out building a new application, just target sqlite until you actually need something bigger. Odds are, YAGNI.

> Sqlite eliminates almost all of the management overhead that other databases force on you

Multi-user support is what forces that overhead on you.

It's just that SQLite consciously aims to be a single-user system.

> As for permissions, its better to let the OS handle that than some half-baked database permissions system

This is nonsensical. There isn't an isomorphic mapping from the Unix permissions model to SQL's permissions model, particularly when you start introducing stuff like row-level permissions.

If you write an application which uses a DB backend, and you require multi-user capability, you can either delegate some of that to the database, or roll your own. In which case, half-baked permissions systems are back! Especially if you can't isolate database connections (because it renders any app-code permissions system moot if you allow 3rd party code execution, such as plugins).

All that overhead is there for a reason. Those reasons are irrelevant in the single-user case. But they are not irrelevant in the multi-user case.

> All that overhead is there for a reason.

SQLite's classic use case is where SQL-like access to data would be convenient but a full RDBMS would be tremendously inconvenient, e.g. embedded systems. People seem to be citing inappropriate environments for SQLite as reasons to say it is of no value. You get the same sort of silliness from NoSQL zealots.

> As for permissions, its better to let the OS handle that than some half-baked database permissions system.

Sorry, but that doesn't make sense. For example: if you want your application to be able to add new rows, but not delete them (for audits actually) you cannot do this with OS permissions. Only database-layer permissions will make it possible.

There are many other examples of such use-cases. You simply cannot use OS/apparmor/selinux/whatever external to the database to grant/revoke per-table access.

> Root accounts/passwords are a wtf in a production environment because they destroy auditability.

On this side however - why would anyone know / have access to your root account? Applications should never have it. Servers should never have it (in a form different than the hash). Only people should have it.

Interestingly, there is SELinux integration for Postgres: http://wiki.postgresql.org/wiki/SEPostgreSQL

AFAIK, one database per application would not protect other databases from a SQL injection exploit.

The "ATTACH DATABASE" command (http://www.sqlite.org/lang_attach.html) can open any other database that the current user has permissions to.

You could, as you mentioned, set up a new user for each database. At a certain point, the permissions systems in a traditional RDBMS may be easier to manage.

The stuff that would get rid of the SQL injection, is the database API, removing the general purpose query(string) function. And forcing some structure on the parameters. First a little breaking to get the easy injections out, and progressively forcing the whole host language mapping of the query, including the result schema. People will scream about the debugging, but they will gain a little bit of stuff in exchange (like projection in a variable, where clause re-use etc).

It's a single user database, hence it doesn't need multiple users. It's great for a single user application (e.g. Firefox) but not at all good for multi-user, multi-session applications.

I don't intend to say its no good, btw. It's fantastic! But you have to use it where it is meant. You aren't comparing apples with apples I'm afraid.

If you are using sqlite for hosting a production scale app, you are doing it wrong.

If you want an database for your mobile app, or a web app use a full fledged database like MySQL or Postgres.

sqlite is for embedded storage applications, especially in the backend. Where the resource utilization foot print is supposed to be minimum, and where you running into situations where are you are reimplementing SQL like DSL's to query and write to files. Or say you just wish to maintain some structured form of inventory.

Best example I can give you is a web browser. There is a requirement to store a good deal of structured information, in a mechanism which requires little resource utilization, which doesn't require additional software like demons or a server to run or complicated deploy and maintain scenarios. Or even a strict configuration.

If you look at this use case you will see tons of backend application would be better off using a tool like sqlite than play with huge xmls and endless rewrite parts of SQL badly.

Reply on news.ycombinator.com