News for hackers. Stuff that matters.
Re:SQLite 3.8.0 Release with next-generation query planner by nraynaud on Monday August 26, 2013 @09:44PM (#6280966)
I'm here to declare my love for sqlite, a database that doesn't asks me to choose a root password (wtf, everybody hate passwords) and an application user password (double wtf), doesn't open a socket (being it unix on TCP), doesn't install a daemon somewhere in the system, doesn't need to be root to start, doesn't asks me to create a new dba user in my system, doesn't require me to be understand how the root daemon starter hands over the control to the dba user when something goes wrong at startup, and generally doesn't bother me with stuff that has really nothing to do with storing and querying data.

I don't run this in production only because I would have a hard time convincing everybody to do it or by cargo-culting my fears (depending on the project). But one day I will.

Re:SQLite 3.8.0 Release with next-generation query planner by simias on Tuesday August 27, 2013 @04:35AM (#6282205)
I love SQLite but I don't think it's fair to compare it directly to other rdbms. As the official website itself puts it:

"Another way to look at SQLite is this: SQLite is not designed to replace Oracle. It is designed to replace fopen()."

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

There are very few instances where I end up in a position where I have to decide whether I need to use postgres or sqlite, they have very different use cases.

Re:SQLite 3.8.0 Release with next-generation query planner by Shorel on Tuesday August 27, 2013 @10:32AM (#6283630)
I don't think it is designed to replace fopen().

They can't say it, but it is designed to replace FoxPro and Access, or more generally: a local ODBC connection.

Re:SQLite 3.8.0 Release with next-generation query planner by viraptor on Monday August 26, 2013 @10:49PM (#6281187)
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.

Re:SQLite 3.8.0 Release with next-generation query planner by abtinf on Monday August 26, 2013 @11:38PM (#6281325)
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.

Re:SQLite 3.8.0 Release with next-generation query planner by jacques_chester on Monday August 26, 2013 @11:59PM (#6281364)
> 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.

Re:SQLite 3.8.0 Release with next-generation query planner by epo on Tuesday August 27, 2013 @05:26AM (#6282383)
> 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.

Re:SQLite 3.8.0 Release with next-generation query planner by viraptor on Tuesday August 27, 2013 @12:52AM (#6281521)
> 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.

Re:SQLite 3.8.0 Release with next-generation query planner by lmz on Tuesday August 27, 2013 @02:47AM (#6281875)
Interestingly, there is SELinux integration for Postgres: http://wiki.postgresql.org/wiki/SEPostgreSQL
Re:SQLite 3.8.0 Release with next-generation query planner by mkching on Tuesday August 27, 2013 @02:20AM (#6281791)
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.

Re:SQLite 3.8.0 Release with next-generation query planner by nraynaud on Tuesday August 27, 2013 @02:48AM (#6281877)
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).
Re:SQLite 3.8.0 Release with next-generation query planner by chris_wot on Tuesday August 27, 2013 @10:44AM (#6283711)
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.

Re:SQLite 3.8.0 Release with next-generation query planner by kamaal on Tuesday August 27, 2013 @03:28AM (#6281992)
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.

Re:SQLite 3.8.0 Release with next-generation query planner by jeltz on Tuesday August 27, 2013 @08:05PM (#6287181)
Most of this is not true for PostgreSQL either. The only two things PostgreSQL needs to run is a daemon (it does not have to be actually daemonized) plus a unix socket, passwords, user accounts, root privileges, etc are all optional.

Sure SQLite (which I love) is easier to get running, but do not exaggerate issues of traditional RDBMs.

In PostgreSQL 9.3 which will be released in a couple of weeks they will also fix the cases where you may need to use sysctl to iincrease shared memory limits.