Topic: Breaking new ground with Mission Scripting and SQL (Read 19341 times)

Fluf · « **Reply #20 on:** January 21, 2003, 01:29:43 pm »

Fantastic stuff people! Keep up the great work. I love you all!

Corbomite · « **Reply #21 on:** January 21, 2003, 01:39:55 pm »

You know, I'd be really excited.... if I understood a damn thing you guys are talking about!!

FireSoul · « **Reply #22 on:** January 21, 2003, 01:46:48 pm »

Quote:

Are we going to expose the SQL Database Server port on Xenocorp's firewall ?

.. I know it depends on the firewall software, but there are ways to open up the MySQL port accepting only *1* IP (assuming static IP source)..
.. or you could set-up some kind of tunnel between the 2 hosts..
It all depends on the SoftWare, and the OS for that matter.

I'd recommend a linux box for the SQL db.. hopefully locally connected to the windows box running the campaign. The Linux box could easily firewall and protect itself.. in fact, it could act as the gateway/firewall, forwarding the connection data (Dplay) to the windows machine sitting behind it.

... *thinks* .. hmmm..

-- Luc

FireSoul · « **Reply #25 on:** January 21, 2003, 02:24:39 pm »

I thought so.
.. how will you know if someone is cheating or not? Anyone (like me) with a little bit of SQL knowledge could then access this DB and do whatever they wanted.
.. what would be the authentication mechanism? How would it prevent players from cheating?

-- Luc

DONOTDELETE · « **Reply #26 on:** January 21, 2003, 02:34:47 pm »

Now you see the problem!

As I had suggested, the database can be protected using
password protected logins that can be incorporated into
the compiled script (would need to decompile the script or run a
packet sniffer while running it to get the username and password).

Can also create logins with limited privelidges to allow
for connection info to be included in a java applet parameter
specified in the html page it is embedded in.

These were some of my thoughts, but it seems people are
worried that the db location is revealed at all. I don't think
that it is such a risk to allow the db location to be discovered,
unless I am missing some security holes in the MySQL database.
Are there known security exploits for MySQL?

TOCXOBearslayer · « **Reply #28 on:** January 21, 2003, 03:31:57 pm »

You all are just amazing. Thank you for all your work.

I think I will send an email to Taldren to check this out...... You deserve some sort of credit for this.

FPF_TraceyG · « **Reply #29 on:** January 21, 2003, 04:33:20 pm »

ok.. this is what I had had in mind, so all you SQL gurus please assess this for viability.

Instead of sending SQL queries directly from the mission script, a list of 'instructions' is sent to another small program (which I would write) that is running on the localhost where the D2 server is running. We'll call this a D2 mission reporting server. It will use its own port of course and will listen for any incoming mission resuts. The data sent to it can be in a completely customised format. Upon receiving that data, the time the mission was initiated will also be sent along from the client (player) computer and checked against the SQL Db for authentication. (If you really wanted to get fancy, you could also allocate passwords to every D2 player as well). The connection information to the mission reporting server can be stored in a seperate file suitabky encrypted. Any packet sniffers being run during this process will only give you the ip address and port and if by some chance, the data was managed to be decrypted... at best, you would only get the information pertaining to that one mission. Once the mission has been reported back, that information is no longer valid and wont be acceptable to access the reporting server.
The reporting server itself will then pass on the SQL queries to the DB locally. It will have limied capability, only passing on those queries necessary to make alterations to the d2 DB.
The only way the mission reporting server will accept instructions is if you send a mission start time that matches the one in the DB for a currently active mission, along with the mission details. In theory then, unless you can actually get access to the sql DB in the first place to gain that info (unlikely), you wont be able to do much at all. The Sql DB is never open to the internet, no SQL passwords or permissions are granted (other than to the mission reporting server) and the whole thing can be configured completely independantly from any mission script (thus mission scripts wont need recompiling).
In the unlikely event that someone did manage to get the mission scripting server to accept an erroneous instruction, they still wouldnt have access to the SQL DB, only what queries the server was capable of. a range of ports could be used on the mission reporting server to handle several queries at once.

In effect, it acts as a go-between or firewall between the client (player) and the D2 server.

FireSoul · « **Reply #30 on:** January 21, 2003, 04:37:56 pm »

It seems alright.. If there's some kind of unique data tag on both ends that can be used, that's perfect for authentication. That data tag would only be created by a D2 server starting a game? Perfect.

.. it would indeed be a tunnelling protocol that would make sense... and giving each player his/her own password to play makes complete sense. At this point, a separate 'modular' file used for authentication would be awesome.

What's the server-end platform for this "app"?
-- Luc

LonnyE · « **Reply #31 on:** January 21, 2003, 05:15:28 pm »

Alot of problems might be avoided by players only being permitted to act on staging tables. Only permit players to act on a mission results, location tables, etc. Periodically (trial and error to determine frequency) update main db tables with info from staging tables. Server admins and other trusted folk could be given a different front-end.

Parse all incoming queries to ensure that users are only querying against the staging tables. If some a** still somehow fubars the staging tables then it is no great tragedy as only info from the the last update of the main db tables is lost.

This may also allow larger numbers of players to play as there will be fewer (but of course larger) transactions. I think this ok in regards to enemy player locations as the map becomes a reflection of last known enemy location (a bit of fog of war).

I dont know much about MySQL. We use Oracle and Cold Fusion where I work. If I can be helpful let me know.

Lonny

Karnak · « **Reply #32 on:** January 21, 2003, 07:07:28 pm »

I agree with LonnyE. The more functionality you can keep on the back-end SQL server the better. Writing a middle-ware server object creates more system complexity that may not be needed, not to mention a lot more potential bugs and gnashing of teeth. You can have a polling stored procedure call written up to update the main tables from the player "scratch" tables.

clintk · « **Reply #33 on:** January 21, 2003, 07:11:07 pm »

Nothing like a bit of brainstorming !

Does anyone know why we're using MySQL ?

Cleaven · « **Reply #34 on:** January 21, 2003, 07:42:57 pm »

A word of warning about robustness, from a bugs and player crashing point of view. Things still have to work even though one player may detonate as he crosses the line, or AI gets his ship etc. That became my biggest problem with advocating destroyable starbases. Even though they are made big and tough, a bug could make them go pop, and conversely a player who deserves to see a result (eg starbase destroyed) is robbed of it by a bug. Just don't get too complicated to begin with otherwise it may fall in a heap.

SPQR Renegade001 · « **Reply #35 on:** January 21, 2003, 08:44:14 pm »

While were breaking all this new ground with SQL, is some sort of external shipyard in the cards?
If we had a vanilla server set-up, with all the evil playtoys purchased outside of the game, we could eliminate the need for remembering and policing all those nasty C&C rules, and implement some form of OoB.

DONOTDELETE · « **Reply #36 on:** January 21, 2003, 10:13:00 pm »

As FireSoul has indicated we cannot rely on the MySQL server's
own security, so solutions that rely on it are unacceptable.
I think it would be good to secure the database as well as possible.
If it is done using an intermediate server application then it should
work perfectly and be tested to death. I think the threat of the db being
hacked outweighs the additional complications concerns.
Mission scripts that are not server specific would certainly be a good
feature of this approach.

WRT using the mission start time as an authentication
component - as long as the mission start time is definitely
the same one submitted to the db by the serverplatform

Hopefully the same application could be used to provide data to
a webmap java-applet. I am helping a friend to write such an
applet and I believe he has accounted for this by developing
a java servlet to do exactly this. (i.e.: The same job as the
"reporting server" you propose, but I'm not 100% sure yet,
waiting on reply...)

It would be very helpful if anyone knows for sure the
relational structure of the databse to help expedite this
process. I have been working on extracting the relationships
myself, but it would be nice to know for sure...

clintk, with regard to "why are we using MySQL?"
- It's free! (so's PostgreSQL too, I know...) I've never
tested it on another db server, but I seem to recall
reading of someone using the MS-SQL server.

Cleaven, excellent advice. Indeed, Occam's Razor
should be applied!

Renegade, glad you mentioned the shipyards.
An external shipyard shouldn't be too hard
but I can see a number of complications.
I think that it would be more effective to produce an
application to automate the policing of CnC and
population of the shipyards. (any volunteers?

)

FPF_TraceyG · « **Reply #37 on:** January 22, 2003, 01:33:30 am »

Well, it need not necessarily be the start time, but anything really that is unique to that mission will do that is passed to or accessible by the mission script and stored somewhere in the database so it can be used for authentication.

FPF_TraceyG · « **Reply #39 on:** January 22, 2003, 03:03:04 pm »

Actually there would be no need for a client side program. The mission script is doing the results reporting itself. The results it reports are in addition to those reported by the game. They will only only conflict if you havent designed the mission with that in mind.
The aim is to add in some extra features that SQL can offer by modifying the SFC2 server database directly based upon mission results that currently, the Taldren API does not offer. For example, there is no function for instance that allows you to change the Economy Value of a hex within a mission script. However, by accessing the DB with SQL, you can. The update to the DV of a hex is not done with a direct call to a function or setting some parameter either (as far as I can tell). It relies upon the Victory Conditions and whether or not the player won in a mission. As far as I can tell, a 'win' result in an enemy hex must call some function within the game itself (which we dont have the source code for) that changes the DV. I still havent figured out yet why the DV dosnt update in a neutral hex whilst playing a coop mission. Perhaps it just simply doesnt know which way to shift the DV.
In effect, making SQL queries directly to the DB bypasses the API and allows us to do things we wouldnt otherwise be able to do. To be honest, this wouldnt need to be necessary at all if we had the source code for the game, we could just add in any function we liked. Who knows... maybe one day...

News:

Topic: Breaking new ground with Mission Scripting and SQL (Read 19341 times) var addthis_config = {"data_track_clickback":true};

Fluf

Corbomite

FireSoul

FireSoul

**DONOTDELETE**

FireSoul

**DONOTDELETE**

FireSoul

TOCXOBearslayer

FPF_TraceyG

FireSoul

LonnyE

Karnak

clintk

Cleaven

SPQR Renegade001

**DONOTDELETE**

FPF_TraceyG

Kel

FPF_TraceyG

Topic: Breaking new ground with Mission Scripting and SQL (Read 19341 times)

DONOTDELETE

DONOTDELETE

DONOTDELETE