On 21 Dec 2010, at 14:19, Simon Slavin wrote: > > On 21 Dec 2010, at 1:44pm, Philip Graham Willoughby wrote: > >> Implementing an SQLite-based server does not obviously enable this in and of >> itself. If you could open a database on a remote machine using its filename >> as the OP was trying to do it would enable this, but we got into this >> discussion when it was discovered that doing so was a bad idea. > > This was my first thought when I considered implementing an SQLite-over-IP > protocol: that there was no user model and therefore no need for passwords. > Mounting a database on the server would mean that anyone who knew your IP > address, port number and database name could access the data.
Worse than that: SQLite will attempt to open any filename you give it as if it were a database. I do not know what damage could be done by opening non-database files, but I suggest that one could rapidly find out if one deployed this. > So my conclusion was that you would need to implement, in the first case, a > simple user privilege model, nominating which users (or which IP addresses) > could access which databases. I recommend you get the web server to authenticate people and use HTTPS, rather than trying to handle authentication yourself. You'll still need to handle authorisation, but that's the easy bit. Never rely solely on IP security; it is very easy to spoof or usurp an IP address. > Just to throw into the pot: > > It's not that hard to do an extremely simple version of this using HTTP and > either XML or JSON. Set up a web server with some PHP or Python pages which > take the following parameters: > > databasename > command Like this short script does for MySQL, in fact: http://www.bin-co.com/php/scripts/sql2json/ It's probably 20 minutes work at most to adapt that to use an SQLite module instead of a MySQL module. For me, this still does not result in anything near an SQLite server, for the same reasons that the above script does not constitute a MySQL server. To be worth having requires a client embedded in the main SQLite library so that I can sqlite3_open remote databases and use ATTACH DATABASE to use any combination of local and remote databases as one. Short-lived scripts on a web server cannot allow all the API functions to work with a remote database using this mechanism: ATTACH_DATABASE, SAVEPOINT, BEGIN TRANSACTION, CREATE TEMPORARY *, and most of the C API routines cannot work. To do it properly you would cut SQLite in two between the SQL Command Processor and the Virtual Machine. A long-running program on the remote machine would own all the database connections made to that machine, and would appropriately authenticate/authorise remote users. ATTACH/sqlite3_open of a remote DB would need to perform an sqlite3_open on the remote system and that opened database would need to persist until the client used DETACH/sqlite3_close or terminated (and for reference, detecting termination is extremely hard). The client would need to be sent the remote database's schema so that after that point communications from the client are in the form of virtual-machine instructions: the client handles the parsing/generation of VDBE programs and the server runs them. The user-function-execution engine would need extensions to allow user-functions on the client machine to be executed. A globally-installed user-function mechanism for the server would also be good. Some kind of 2-phase commit protocol would need to be used to co-ordinate transactions across multiple machines. It could be done. But it seems pointless to go to all that effort when MySQL/Postgres/DB2/Oracle/MSSQL have the advantages for this problem domain that they are designed to do this, have been well tested in this architecture, are as simple as they can be, and are fully supported. Best Regards, Phil Willoughby -- Managing Director, StrawberryCat Limited StrawberryCat Limited is registered in England and Wales with Company No. 7234809. The registered office address of StrawberryCat Limited is: 107 Morgan Le Fay Drive Eastleigh SO53 4JH _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
