DbxMda, Multi-threaded usage plus MyDAC (SSL) comparison

[CppBuilder]

I'm currently developing a TCP-server (C++ Builder 2009). As part of the authentication process, clients send their username (plain) and password (sha1 hash) via TCP. The server then queries a remote database (MySQL 5.0.19) over the internet for the user record, containing an id, plain username and sha1 hashed password.

Last year around December I bought Devart DbxMda drivers, just upgraded to 4.40.16. First question I got is about the multi threading aspect in this application, as each TCP connection (client) runs in its own thread, is it correct to conclude that each connection should have it's own dedicated database connection? I read something about thread-safety (MyDAC) in this FAQ http://www.devart.com/mydac/index.php?view=faq#q58, is this the same for a dbExpress solution?

Second of all, I was looking into MySQL via SSL. I noticed that DbxMda isn't able to pull this off but MyDAC is. The only question I got with this is, is SSL a must for querying those user records I mentioned before? I also read that it'll have a reasonable impact on the performance of the MySQL server, when you start enabling SSL. What would you guys suggest, just use those DbxMda drivers I already got or start using MyDAC with SSL support?

After all, it is userdata we're talking about, but how big is the risk of serverapplication mysql-server communication being tapped?

Thanks in advance.

[Dimon]

First question I got is about the multi threading aspect in this application, as each TCP connection (client) runs in its own thread, is it correct to conclude that each connection should have it's own dedicated database connection?

Yes, you should use separate SQL connection for every thread in the multi-thread application.

What would you guys suggest, just use those DbxMda drivers I already got or start using MyDAC with SSL support?

SSL protocol is required if you need to secure confidential data in an unsecure connection like Internet. If you really need to use SSL you should use MyDAC components for this.

After all, it is userdata we're talking about, but how big is the risk of serverapplication mysql-server communication being tapped?

It depends on many factors and should be considered in every specific situation. You can find more information about secure connection in the SecureBridge components overview by the following link: http://devart.com/sbridge/

[CppBuilder]

And what would you suggest when using the database purely to validate authentication attempts;

- create a connection per attempt
- create a single connection for all possible attempts

Normally a user saves his credentials so he would authenticate on the very first attempt every time. This is how the two situations would look like in some pseudo-code;

[ connection per attempt ]
1.) authentication attempt
2.) connect to database
3.) query userdata
4.) authenticate
5.) close connection

[ single connection ]
1.) initialize; connect to database
2.) wait for authentication attempt...
3.) authentication attempt
4.) query userdata
5.) authenticate
6.) if (authenticated) { close connection}
else { wait for authentication attempt... }

If I think about how for example PHP works (more scriping language then OOP ofcourse), they create a connection per SQL Query. This way you keep connection time per client limited ofcourse (because you only need that database for authentication).

I'd like to hear your oppinion, thanks in advance.

[Dimon]

When you use a multi thread application you can use one connection for all threads only if you provide thread saving yourself. In this case if you have many threads you will have delays using one connection.
In other cases you should use separate SQL connection for every thread.