PDA

View Full Version : Online DB content



M109uk
04-12-2004, 12:06 AM
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
04-12-2004, 12:19 AM
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}
04-12-2004, 08:45 AM
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
04-12-2004, 08:48 AM
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}
04-12-2004, 09:31 AM
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% :P



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 ? :P
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 (http://www.ethereal.com) and start loggin (and eventually change) everthing that your application transmit and receive.

Lightning
04-12-2004, 12:39 PM
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}
04-12-2004, 01:04 PM
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 ? :P 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
04-12-2004, 01:40 PM
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
04-12-2004, 07:23 PM
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
06-12-2004, 04:04 AM
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.

M109uk
07-12-2004, 05:36 PM
Thanx for that cairnswm,

I have written a simple web service, i have got it sort of working with MySQL, except for some reason it doesnt execute a procedure anymore, but that doesnt matter yet.

Is it at all possible to call a procedure in the webserive via PHP from the webserver, what i have is a User account that has a unique account number, similar to ICQ, i have created a function that generates the account number in my web service and results it, but i want to be able to register an account from my website too, so idealy i would like to have in a PHP script:
$userID = cgi-bin/xshell.exe/createUserID();

Of course i doubt it'l be that simple but is it possible?

cairnswm
08-12-2004, 05:22 AM
Unfortunatly I am not a php programmer so I am not sure.

In ASP (Which I use for web development) you can use the XMLHTTP activeX control to do the same thing as WebLive does. This then returns the results of the call as a string that can be used in the program.

Here is some php code I found (this will, I presume, only work on a windows server):
http://www.webpronews.com/webdevelopment/basicdevelopment/wpn-37-20041201PHPOnTheFly.html

Aaahh!!! I found what you need!
http://hacks.oreilly.com/pub/h/175

Let me know if you get it working.[/code]

cairnswm
08-12-2004, 05:33 AM
Also look at:

http://magpierss.sourceforge.net/

Its an RSS reader but an RSS reader means it needs to get info from another web site. Therefor you should be able to modify it so you can use it to call your service.

M109uk
09-12-2004, 12:08 AM
Hi cairnswm,



Aaahh!!! I found what you need!
http://hacks.oreilly.com/pub/h/175


Thanx for that, i have managed to get it to work, had to do a bit of guess work but i got it with the following code:

<?php
require_once&#40;'nusoap/lib/nusoap.php'&#41;;

$soapclient = new soapclient&#40;'http&#58;//localhost/cgi-bin/XShell.exe/wsdl/IXShellService', 'wsdl'&#41;;
$parameters = array&#40;'XSID'=>'0000/AAA0/AA-00'&#41;;
$username = $soapclient->call&#40;'GetUsername', $parameters&#41;;
$password = $soapclient->call&#40;'GetPassword', $parameters&#41;;

$xsid = $soapclient->call&#40;'GenerateXSID'&#41;;
echo 'Username&#58; <b>' . $username . '</b><br>Password&#58; <b>' . $password . '</b><br>';
echo 'XSID&#58; <b>' . $xsid . '</b><br>';
?>

I hav'nt seemed to test it with other functions yet, specialy to see if it works with a class :s if you have any ideas?

cairnswm
09-12-2004, 03:59 AM
What do you mean to see if it works for a class?

The wsdl wraps the web service class in a way that any language should be able to read it. (If the language is web service enabled)

M109uk
09-12-2004, 07:13 PM
i mean by when you call a function you recieve a class, like in the example provided when you first create a web service, you call a function to recieve the TEmployee class, i have a similar class and function, but i call it i get an array back from it, how ever i have tried several ways of getting the propertise from the resulting class..

Example class being resulted:

TXShellUser = Class(TRemotable)
public
Username: String;
Password: String;
XSID: String;
end;

TXShellService = Class(.. , ..)
....
function GetUser(XSID: String): TXShellUser; stdcall;
end;


i use the following to call the function in PHP:


<?php
require_once&#40;'nusoap/lib/nusoap.php'&#41;;

$soapclient = new soapclient&#40;'http&#58;//localhost/cgi-bin/XShell.exe/wsdl/IXShellService', 'wsdl'&#41;;
$user= $soapclient->call&#40;'GetUser'&#41;;
echo 'Username&#58; <b>' . $user&#91;0&#93; . '</b><br>';
echo 'Username&#58; <b>' . $user&#91;"0"&#93; . '</b><br>';
echo 'Username&#58; <b>' . $user&#91;"Username"&#93; . '</b><br>';
echo 'Username&#58; <b>' . $user&#91;'username'&#93; . '</b><br>';
echo 'Result&#58; <b> . $user . </b><br>';
?>


But all i get back is


Username:
Username:
Username:
Username:
Result: Array


This might be a PHP question more than a delphi/web service one, but a have accessed arrays like above before with out any problems, and i couldnt find any alternatives on the web. I hope i make sense lol

Harry Hunt
09-12-2004, 09:51 PM
First of, using SOAP is probably a pretty good idea.

I'm just gonna lose a few words on security, in case you choose your client app to communicate with a script on your server:

You would be passing information to your script through GET or POST variables. I recommend using POST variables and have your script ignore the GET variables completely. If you're using PHP, make sure register_globals is set to "off" in your php.ini as having it set to "on" is a major security risk.

The first security measure you should take is, to restrict access to your script with a password. This password would have to be sent to the script whenever you're requesting something. The script would validate the password and do nothing unless the password is correct.

The second measure you should take is to encrypt any data transfered between your client and your script. The easiest way to achieve that is to simply use SSL. Alternatively, you could RSA-encode all data manually. Since the exe would only need the public key, it can be hard-coded into yoru EXE or even be located in a text-file.

The third and probably the most important measure is to validate anything you write into the database (for example using regular expressions). If for example you're adding a new user to your database, remove all special characters form the information provided by the client app. If you do something like

UPDATE users SET username='$username', password='$password' WHERE id='$id'

you probably can imagine how easy this can be exploited to reset other people's passwords to whatever you want, as long as the information from the client app is inserted into your queries unfiltered.


Of course this still isn't absolutely safe. You might also want to give your client a specific useragent which is then checked for by the script and you could use some kind of authorization procedure where the script sends you a string of numbers and letters that you somehow process and send back to the server. There's really a lot you can do to secure the system, the information above is really just a guideline of what you definitely should be aware of.

cairnswm
10-12-2004, 03:56 AM
Why doesn't your call to 'GetUser' send an ID through? My initial guess is that it is returning a valid (blank) record.

Unfortunatly I have so far only used the web services with Delphi so I can't really help you with php. Do you have a Delphi Client created that returns the correct information? If so then the problem is definitly lying in your php somewhere.

Harry Hunt: When using SOAP its effectivly a POST with the information stored in the calls data area (as opposed to the command headers).

M109uk
10-12-2004, 07:15 PM
Harry Hunt:
At the moment im only testing the SOAP, but when i have finished with it then each call to update or modify a user will require the users ID and their password (which is MD5 encoded before it is passed to the script and compared to the DBs MD5 password).

Im not too sure what information i will need to get encrypted yet, i probably want the credit information encrypted. I cant encrypt ALL the information because i want the user to be able to view and update their account and profile in their web browser too. I probably want the users Credit account encrypted so that they or other users or hackers can not mess with the amount of credits, etc. But i probably dont if this is too important at the moment because the credits are not real money or anything just a form of keeping the play/use time up, and to get updates and other features for the app/game.

Im not sure encrypting the other information would be required because the information will be displayed in HTML when the user wants to view their stats and profile out of the game/app.

As for setting the global register, im not sure if i can turn it on or off cause its on another server and they dont give the option, but i will remember to turn it off when i get my own server up and running.



Cairnswm:
I will update my test app later when i get back and let you know. Im hoping its a code problem otherwise im lost :lol:.

Im not great using PHP, i'v pretty much only used it with choosing which page to display and MySQL. The simple stuff :lol: i'l have another look on the web too, maybe i have to initialize an array type or something?!

Another question, would it be better or easier to use an ISAPI than a CGI?
Of course im aware that the DLL has to be unloaded before it can be updated and all, but will using an ISAPI over a period of time course problems or anything. And of course i will probably need to update it at some point, so that will probably cause problems if i cant unload it.. So not sure would be the better option?!?

M109uk
10-12-2004, 08:09 PM
Ah i tested using my Client app and it worked fine, but i was still getting the problems in PHP so i tested a few random print functions in PHP, and found out that the data was in the the variable how ever it was a multiple array, and i had to access it like:



<?php
require_once&#40;'nusoap/lib/nusoap.php'&#41;;

$soapclient = new soapclient&#40;'http&#58;//localhost/cgi-bin/XShell.exe/wsdl/IXShellService', 'wsdl'&#41;;
$user = $soapclient->call&#40;'GetUser', $parameters&#41;;
echo 'Username&#58; <b>' . $user&#91;TXShellUser&#93;&#91;Username&#93; . '</b><br>';
echo 'Password&#58; <b>' . $user&#91;TXShellUser&#93;&#91;Password&#93; . '</b><br>';
echo 'XSID&#58; <b>' . $user&#91;TXShellUser&#93;&#91;XSID&#93; . '</b><br>';
echo 'ID&#58; <b>' . $user&#91;TXShellUser&#93;&#91;ID&#93; . '</b><br>';
?>


Of course TXShellUser is the resulting class and the second are the propertise :)
lol just awaiting the next lot of problems :D

cairnswm
11-12-2004, 12:24 PM
I'd only use an ISAPI dll if I had full access to the server.

Another reason for ISAPI is if you want to maintain state (ie the user logs into an application running in your Web Service Server).

Well donw on getting it working anyway. It real nice to know that one of my tutorials was useful :)

M109uk
11-12-2004, 11:12 PM
Ok thanx for the note, i will probably stick to using CGI then, even when i get my own server, and if i want a login state then just use it in the Users table of my DB.

Thanx for the tutorial too, was more than useful, i didnt think it would be that simple :) i believe it should go in that book you guys where thinking about doing!

Cheers,