Access SQLite Database on web from Desktop application

Question

I have an SQLite Database on a webserver. I would like to access the database from a typical Java Desktop Application. Presently, I'm doing this thing... Download the Database file to a local directory, perform the queries as necessary.

But, I'm unable to perform any update queries on this. How can I do this. [ On the actual database]

Another question is, to directly access the database from web in java (is this possible), make direct queries, updates anything etc,.

How can I achieve this type?

I've written code for connection of Java to SQLite and is working pretty fine, if the db file is in local directory. What changes or anything I have to do to establish a link to the file on webserver without having to download the database file.?

Thanks in advance...

Federico Zancan · Accepted Answer

CL. is right saying that if you need to access from desktop applications to a web database, SQLite is not an appropriate choice.

Using SQLite is fine in small web sites, applications where your data have to be accessed from and only from the web site itself; but if you need to access your data from - say - your desktop, without downloading the data file, you can't achieve that with SQLite and HTTP.

An appropriate choice for your web application would be MySQL or other client/server database, so that you could be able to connect to the database service from any place other than your web application, provided server access rules set permit that (e.g. firewalls, granted authentication, etc.).

In your usage scenario, you would be facing several orders of problems.

1) Security

You would be forced to violate the safety principle saying that database files must be protected from direct web exposure; in fact, to access your web SQLite database file from your desktop you would be forced to expose it directly, and this is wrong, as anyone would be able to download it and access your data, which by definition must be accessible just by you.

2) Updatability without downloading

Using HTTP to gain access to the database file can only lead to the requested resource download, because HTTP is a stateless protocol, so when you request GET or even POST access to the database, the web server would provide it to you in one solution, full stop. In extreme synthesis, no chance to directly write back changes to the database file.

3) Updatability with downloading

You could download your file with a HTTP GET request, read data, make changes and the rest, but what if your online database changes in the meanwhile? Data consistency would be easily compromised.

There could be a way

If you give up using HTTP for your desktop application access to the database, then you could pick FTP (provided you have access credentials to the resource). FTP lets you read data from and write data to files, so - on Linux - you could use FUSE to mount a remote FTP sharing and access it just like if it was connected to your local file system (see this article, for example).

In synthesis, you:

Create a mount point (i.e. a local directory) for FTP sharing
Use curlftpfs to link the remote FTP resource to your mount point
Access to this directory from your application as if it was a conventional directory

This way you could preserve security, keeping the database file from being exposed on the web, and you would be able to access it from your desktop application.

That said, please consider that concurrent access by several processes (desktop app + webserver instance) to a single database file could lead to problems (see this SO post to have an idea). Keep that in mind before architecting your solution.

Finally, in your usage scenario my suggestion is to program some server-side web service or REST interface that, under authentication, let you interact with the database file performing the key operations you need.

It is safe, reliable and "plastic" enough to let you do whatever you want.

EDIT:

MySQL is widely used for web sites or web applications, as it is fast, quite scalable and reasonably reliable. Activating MySQL server is a little bit OT on StackOverflow and quite long-winded to report, but in that case you could google around to find plenty of articles discussing such topic for your operating system of choice.

Then use MySQL JDBC driver to access the database from your Java desktop application.

If your idea is to stick with SQLite, though, you could basically prepare four web endpoints:

(Notice I specified "http", but you could consider moving to SSL encrypted http connection, a.k.a. "https", find details here and here. I don't know which webserver are you running, but still googling a little bit should point you to a good resource to properly configure https.)

Obviously you could add any endpoint you like for any kind of operation, even a more generic execute, but play my game just for a while.

Requests towards those endpoints are POST, and every endpoint receives proper parameters such as:

table name
fields
where clause

... and the like, but most important is security, so you have to remember 2 things:

1. Sign every request. You could achieve this defining a secret operation key (a string which is known to your client and you server but never travels in clear text), and using it in a hashing function to produce a digest which is sent together with other parameters as an incontrovertible proof for the server that that request it's receiving comes from a genuine source. This avoids you to send username and password in every request, which would introduce the problem of password encryption if you don't use https, and involves that the server has to be able to reconstruct the same signature for the same request using the same algorithm. (I flew over this thing at 400Mph, but the topic is too large to be correctly treated here. Anyways I hope this could point you in the right direction.)

2. Properly escape request parameters. "Sanitize" parameters someone calls it, and I think the metaphor is correct. Generally speaking this process involves some filtering operations performed by the server's endpoint, but it basically could be written as "use prepared statements for your queries". If you don't it could be likely that some malicious attacker injects SQL code in requests to exploit your server in some manner.