Qt and MySQL - security concern

[maximus]

Hi guys,

I just set up my Qt app so that It can connect to my MySQL server. I tested a few queries and it works fine, really easy to set up, loving it!

I have some security question though, maybe someone here already did this
My Database is hosted at a fixed IP, and I want all user from any IP (people that downloaded my app) to be able to query my database (at least try to login).

I have set up "remote database access in CPanel":https://www.dropbox.com/s/pxumf5ayc3o5ts9/remoteAccess.png using the % wildcard (all IP can access)
From what I read, this is really risky to do as anyone could attack my Database.

I'm looking for solution to secure my database
1- When a user first sign up, store user login IP address. For all subsequent login, check that the IP address that try to login match the value stored in the login IP field, if not, send email to user account asking to authorize the new IP address, overwrite database login IP address for User.
2- ??

I think solution 1 could be possible, but how can I protect my database from attack if all IP can access it? I can see people who could try to flood it with queries or stuff like that..
If you have any link or suggestion, it is more than welcome !

Max

[samir]

Hi,

if you ask me - never allow anyone to directly use your database. I would suggest the following approach: create a web service*, that will stay between your database and the end users. Thanks to that you can provide a solid security layer (users can perform only what they are allowed to do, they can't execute any query they like). You could even try to implement some roles - like user A can read table T1 and T2, and user B can read T1 and read/write T2.

So the idea is to create sth like that:
DB <--- (only WS can access) ---> WS <---- users

• by web service I mean REST with JSON, but you could also use SOAP or something else - it all depends on your needs.

[maximus]

Thanks Samir, seems like a good option to me!

I was wondering how public applications were dealing with authentication. (for example, free game that let you download their client).
I will try and develop my WS. I have no experience with that, do you have any technology to recommend/avoid? My webserver is a linux server (cPanel) with a mySQL dB.

Thank you very much !

[samir]

I am afraid I don't know what cPanel exactly is and what featues it offers. So far I have been only working on such solutions in Java, where there are many options. I believe it can also be easily done in PHP. It all depends on what you can use.

As far as security is concerned - it all depends on your needs, you may use something simple like Basic Authorization, or something more sophisticated like OAuth. I guess this may be interesting: http://pages.apigee.com/oauth-big-picture-ebook.html (if I remember correclty, you can download it for free). Big players (Twitter, Facebook, ...) use OAuth for the client applications.

PS. Some time ago I started working on a project, that you may find interesting - http://qt-project.org/forums/viewthread/22009. Just start the application, point your web browser to http://localhost:9090/image-gallery/api/images and you should see some JSON data. (Be warned: this is just a hobby project, there is still a long way before it could be regarded as 'done' ;) )

[maximus]

Hey Samir,
My web server is on tomcat linux so I can use Java or php, I have more Java experience but I also like the simplicity of php. I'm researching all the options to make a good choice on the technology now, Language is not important, I want the most flexible/easy to maintain option!

What looks kind of hard if the authentification and I don't get everything on it right now. I think basic Auth with SSL would be enough for my app (not much sensible data, entertainment app...) Anyway I guess i'll learn with experimenting

Interesting your project! I'm working on a hobby project also, it started as a small local app but now I want to integrate a database on top of that so user can keep track of their data wherever they are. Here's my prototype if you are in cycling hehe : https://www.youtube.com/watch?v=_H8zfInshdo&hq=1