Online DB content

[M109uk]

Hi all,

I have an OpenGL application/Game, that users are required to register online for, i want this application to access my DB on my site (MySQL) or access say INI files on my server, the information stored in the DB or INI file will contain the users details, certain settings, and other information, this information will need to be updated almost every time the user runs the application. This information also needs to be updated through the website.

What would be the best way of doing this, my first thought was to use a CGI/ISAPI app, but how can i change the access rights to the file or modify/create a MySQL entry?

Thanx for your help..

[WILL]

You can try it this way for a sort of registration system...

Have a nice PHP script(running on Apache) that will work with your MySQL server and make your client apps/games access that page(PHP script) with the proper POST form data and the script will do all the work.

www.php.net
www.apache.org

Both are opensource, free software. No licencing.

[{MSX}]

Avoid givin uses the right to access directly the database. Instead, make a web page or service to do that.
Remember to crypt in some way the contents, or people will be able change them.

[Lightning]

While i don't know much about PHP and Apache, i do know enough about DB servers and i worked with MySQL and FireBird
If you have any possibility of doing this i would suggest replacing MySQL with PostGres or FireBird because with the latest two you can use Stored Procs and Triggers (and other stuff), but most web companies only allow MySQL
You can connect with any DB server (even MySQL) anywhere in the world, all you have to know is a valid user and a host name wich can be:
- localhost or 127.0.0.1 (for the same machine)
- the server name on a local network(myservpc)
- a web server(www.mywebsite.com or something)
- an IP address
When you access a DB Server with an alias(name) the IP is extracted and used so if MySQL has access to your ports it should work using your website name.
I think it's easier to access you DB than your files but as i said i have more experience with DB Servers

MSX there is no problem in "directly accessing the database" that doesn't happen with most DB Servers anyway, (only FireBird embeded mode) that kind of access is used for local files only.
You can create users and give them what rights you want so they won't mess up your DB , anyway they don't know what the game is doing so it's unlikely they even know how is your DB stored or how to break it :lol:

[{MSX}]

If you have any possibility of doing this i would suggest replacing MySQL with PostGres or FireBird because with the latest two you can use Stored Procs and Triggers (and other stuff),

I agree 100%

MSX there is no problem in "directly accessing the database" that doesn't happen with most DB Servers anyway, (only FireBird embeded mode) that kind of access is used for local files only.
You can create users and give them what rights you want so they won't mess up your DB ,

Yes, you can open it for access by everyone, but it's not a good practice. You sould carefully set the privileges, and btw i don't think that mysql allow per-row privilege, so a client that has access to a table can read all the records (at last, afaik). Also, you cannot have a db user for each application user. And where will you save passwords ?
Also, when you do a web application, the database is inaccessible from the user, it's only accessible by the web application. I've never seen a database that accept connection from outside it's local network (or even localhost)

anyway they don't know what the game is doing so it's unlikely they even know how is your DB stored or how to break it :lol:

Yes, until they start up Ethereal and start loggin (and eventually change) everthing that your application transmit and receive.

[Lightning]

Yes MSX one can't set row privileges using MySQL, i don't see any reason for that anyway, but you can set privileges per field (i think) and certainly per table so one user as root/sysdba/sa(whatever) and another with read-only access, i didn't make any web apps yet, but i had no problems connecting anywhere, let's say i have MySQL on www.mywebsite.org, that website also has an IP wich may change and that is the IP of the host where MySQL runs, you can connect to that MySQL server using a valid user+password and the host name, check the MySQL manual for more details on remote connections.

MSX, if you have any DB server installed on your PC, try connecting using your internet connection's IP, you can obtain it by typing ipconfig in the console(dos command prompt), once a connection is established disconnect from the web and try to send commands to your DB server, you can't, but once you reactivate the connection you can send commands again, now try using your machine's name, localhost, another machine's name/IP, a website, they all work !!!
Be careful when setting privileges to set a user's privilege for any host, many people set privileges for a specific host.

[{MSX}]

Yes MSX one can't set row privileges using MySQL, i don't see any reason for that anyway

You don't see any reason ?! What about data protection or privacy ? Let's say you have a table with your customers ordering. A customer certain can read all it's orders and change them. So he can now read and change all other customers orders ? I don't think so.
At most, they have phpMyAdmin installed. (which again run on the web server)

i had no problems connecting anywhere, let's say i have MySQL on www.mywebsite.org, that website also has an IP wich may change and that is the IP of the host where MySQL runs, you can connect to that MySQL server using a valid user+password and the host name, check the MySQL manual for more details on remote connections.

Ok, but you connect through a PHP page, and that php page runs on the server, so it's a connection from the web server to the database server, which are on the same network (or even the same machine).
I hardly think that mywebsite leave open access to the database. No web provider i've ever used do this.

MSX, if you have any DB server installed on your PC, try connecting using your internet connection's IP, you can obtain it by typing ipconfig in the console(dos command prompt), once a connection is established disconnect from the web and try to send commands to your DB server, you can't, but once you reactivate the connection you can send commands again, now try using your machine's name, localhost, another machine's name/IP, a website, they all work !!!

That doesn't make any sense. DB server and Web server are separate things.
The fact that you can connect to the database in you machine from everywhere is probably becouse it's misconfigured.

[Lightning]

Well i agree it might be easier to use a web app though i think if the DB server supports stored procs it should be possible to use them as a middle layer.

[M109uk]

The reason i wanted to use an online DB is because the app/game i building uses a form of update and currency library if you will, basiclly in the game/app you can collect and earn credits which in turn can be spent on upgrades, additions and other or new features for the app/game, how ever i want to be able to also access this information via internet explorer if a user wants to update their details or spend their credits out side of the app/games.
The DB will also contain the users configuration settings for both the website and for the app/game if the choose to use it on another machine.
As for securing the data i was thinking of using MD5 encryption for the passwords and m own form of crappy encryption with in an encryption for the sensitive data. This is mostly why i thought using a server-side app would be ideal?! This way i wont need to have open privilages on my DB and can use a secure set just for the CGI/ISAPI?..

I tried to use PHP scripts before, i might not of used it correctly but i was downloading the resulted HTM file from my website and reading the data that way, but it might be me but i dont feel that it is secure enough, and it will get annoying because there will be a lot of variables and data be passed to and from the DB.

Unfortantly i am forced to use MySQL my host dont allow any other form of DBs at the mo, but i will be going to my own server when i get a new line for it.

[cairnswm]

In the Tutorials section there is a tutorial on "Web Live". This is a method of calling web pages on a server (ASP, PHP doesn;t matter) - and the web page does all the database access - the user applpication doesn't need to lknow what type of database is being used in the background.

Also in the tutorials section is a tutorial on Web Services -this works in Kylix as well as delphi and therefore could access a mySQL database on a linux server as well.