Question posted in 
I have written some applications in Delphi for a production company, the purpose of this application is label printing and production data acquisition.All of these applications are Client-Server (Using ZeosLib connecting directly to PostgreSQL through 5432 port).
In recent days, the company has acquired other production sites and wants to install my software in these sites. It’s not possible to make a LAN2LAN connection, and the only way to communicate from external production sites to corporate headquarters is via HTTP (https) server through internet connection.
When I was presented with the problem, the first thing I thought was to convert applications to use REST and write all server side queries making calls to the right endpoints. However, this requires a lot of work: applications have many queries to rewrite.
Therefore, for time reasons, I tried to create a server side PHP script capable of receiving requests written in SQL, connecting to PostgreSQL and responding in JSON data. After that, I have modified my application extending ZeosLib connections with an additional protocol (named websql) able to redirect SQL queries to the PHP script and convert the received JSON data to Delphi DataSet compliant format.
Everything works fine: in all my applications I can now change the connection protocol with the new "websql", and I could quickly install the new versions in the branch offices.
Doing this test was very simple, but now there is a security problem: The Web connection is protected by user/password and by https, but is it still sufficient?
Should a client application be allowed to send queries via https? Before installing the applications in production I would like to know if there is a way to make this architecture as secure as possible or if it is appropriate to rewrite all the applications with REST communication.
Thank you all,
Andrea
2
Answers
HTTPS is enough and secure provided you use certificates at both sides and obviously check for certificate validity.
When using a certificate, a client is sure to connect to the server he think to connect to or he gets a certificate error. Same for server: when a client use a certificate, the server can be sure the client is a legitimate client.
Providing a JSON gateway to run SQL from the client side is not proper REST, and should not be used for any serious work.
Several Reasons:
Anyone on the client side with the credentials could run any kind of SQL statements, which may be pretty unsafe, like DROP TABLE or DROP DATABASE in the middle of a SELECT statement. Or it could access data from other customers.
You still have the logic on the Client side. This is not n-Tier, this is 2-Tier in disguise. The fact that you use HTTPS+JSON instead of direct connection to the DB doesn’t change the global architecture.
HTTPS is safe only if both sides (client and server) forces their certificates for mutual authentication. Otherwise, MiM attacks are very common. Using a VPN or a SSH tunnel may help.
Performance may not be very good, since you don’t cache the data, and network latency is worse over the Internet than over a local network.
If you really want to go this direction, don’t reinvent the wheel, and use a more tuned solution like our Open Source SynDBRemote which works very well with Zeos/ZDBC – or proprietary alternatives.
But I would rather switch to a REST architecture, with the logic on the server side, and the SQL on this server side. It will be easier to maintain and scale, and allow non Delphi clients – e.g. JavaScript clients on Desktop or Mobile.