How do I implement Internet accessible system with Delphi?

Question

Hello

I am about to start working on a new system which will need to support multiple users and potentially allow the database to be accessed over the Internet.

The system will be win32, not web based, the database will just be in an office and accessible anywhere. I am not sure if this is a dangerous approach security wise, am open to suggestions

The database will be SQL Server and the system will be implemented in Delphi 6

Does anyone know how I go about starting this? I will need to take into account record locking as well.

If anyone could provide links to good articles that would be appreciated.

Cheers

Paul

Answer 1

You probably mean a client server system that communicates trough TCP/IP.

You can create this using the Indy components. Be sure to check the examples because they are not easy to use, but you can create almost anything network related with them.

Answer 2

IMHO, the easiest way for you is to create a VPN exposing securely your database over Internet.

Security will be very good, because access to the database will be available only through a trusted VPN connection.

And your database will be available from anywhere, using the Internet just as a tunnel to transport your database packets safely.

So your Delphi code will connect to the database just as usual, using TCP/IP connection, via the VPN secure tunnel.

No need to add additional Delphi-only artifacts, like Indy components and such. And you will be able to connect to your database for not-Delphi client, which could be a good idea to use some database browsing tool.

Answer 3

Exposing the database on the Internet is a security risk. Security flaws could be easily exploitable remotely.

Solutions are:

1. VPN, as said in other answers. Simple and secure, but requires some setup on both end-ponts (clients and VPN server), and may require proper software on the server - or a VPN router/appliance - and on the client as well if you're not using standard VPN protocols).
2. A n-tier application, where only the application server is exposed to the internet. You still have to protect the application server properly and the transmission channel. May require less setup on the client side. Delphi 6 offers Datasnap as a n-tier library (it also still supports CORBA, but it was dropped since D7). DCOM is not very firewall friendly (but can be configured to work across them) but can secure the channel on its own, the other two options (socket and HTTP) are easier to setup but a little less secure (they work using DCOM proxies, thereby the client identity is lost, and require custom code or certificates to secure the channel).
3. A third solution could be to let user connect remotely via remote desktop, but it requires licenses and a machine able to sustain the remote sessions load.

Record locking is handled by the database itself - read the documentation about SQL Server locking mode carefully to avoid bad surprises later. If the connection is not fast enough you may choose to cache some data on the client side (TClientDataset works well for that) and it can also reduce locking issues, but it can introduce udpate conflicts.

Answer 4

Actually, there are dozens of techniques possible, depending on your experiences, preferences and tools that you have available. I would advise you to use ADO to connect to the database and not the BDE, though. To do this, you can use the ADO components that are part of Delphi or import the msado15.dll type library into your project to use raw ADO API calls. The latter will require a lot more experience!
SQL Server is able to just expose itself to the Internet, although this creates a security risk. Still, someone who wants to access it will need a username and password to get a connection and you would need to open the ports that SQL Server uses. But technically speaking, to use ADO over the Internet, all you need to know is the IP address of a working server, plus login information. It's a security risk, though. And for that reason, most developers will not expose SQL Server to a database but just write web services to wrap around the specific database functions that you want to expose.
Record locking is something SQL Server will do for you, and if you use transactions you can make it even a bit more secure.

In the end, the things you need to learn and read about depend heavily on the things you want to do in your application. So before you even start to write some code, start writing a functional design to get an overview of what you want and what you would need for this. From this document, start writing technical documents to describe more precisely what your code needs to do. Once you have this, you can ask more direct questions about the things you need, yet don't know at the moment.