Breaking new ground with Mission Scripting and SQL

[FPF_TraceyG]

From the mission coding laboratories at SFC2.net comes an announcement.

By combining SQL queries with mission scripting, DarkElf and I, today were successfully able to modify the D2 map terrain from directly within a mission script. Out first test was to create a planet, which upon completion of the mission, then appeared on the main campaign map.

Can anyone say "Genesis Device" ?

This opens up a whole new realm of possibilities for mission scripting.

Creating planets out of nebulas. Destroying planets and turning them into asteroid hexes. etc.
Furthermore, not only can the map terrain be changed, but other factors like Econ value as well.  "Sabotage Economic Assets" missions lowering Econ value etc. are all possible.

But here's something that really has potential...
The mission script can also change the location of a player to any hex on the map. Imagine entering a mission and flying into a wormhole, and upon completion of the mission finding yourself on the other side of the map....

The possibilities are limitless..

So where's the catch? Well, acually, there is just one small problem. These mission scripts will only work on a SQL D2  server of course (so single player campaign's are out). So the challenge now is to get the SQL D2 server working and stable for a major online campaign. That part is still a work in progress... the main problem it would appear is server load and gettng it to work with more than 10 people on the server at a time.

On a more general note, the new mission pack (non SQL) for the next major SFC2.net campaign is coming along well and hopefully we'll be putting up a minor campaign very soon to test out our new home at XenoCorps, along with the mission pack and a revised shiplist which may include a lot of ships from OP as well. Its all looking good, so hang in their fellow EAW players.

From the team at SFC2.net.  

[DarkElf]

Ouch...my head hurts.

It took lots of trial and error, and help from Tracey, but we were able to accomplish the task.  Without the help she has given me, this would not be possible...

But we persevered!  (And to think, Im a newbie programmer, LOL)

[Cleaven]

Tell me that you can destroy a base in a neutral hex or hex with DV zero and I will think about putting a cheque in the mail.  

[KATChuutRitt]

You two are absolutely terrific!!!!

The possibilities just boggle the mind,   Convoy raids that affect economy, planets that don't appear on the map unless a suvet mission is done in a particular hex, etc.

The D2 will really feel like a true environment....wow .......what can I say........

Big grin and a bigger salute!!!!

[DarkElf]

Yes, Cleaven, that can be done....entirely possible.

[**DONOTDELETE**]

Well done....

I think I speak for many SFC2 players when I say ...

THANK YOU!

Your continued efforts to improve DV play is outstanding.....It is my firm belief the SFC2 is still coming of age, and will continue to be played by many people.....

Also....a development such as this need to be bumped up the line to the beast or Dave ferrel or even Erik...as this is the very first time I've heard this was possible....and even Taldren might be interested in this technique.....

I doubt they would re-open EAW patching...but there is still an OP patch being worked on....and maybe there are some code  tweaks that would aid in this new development being used.....clearly.....there is a stablity problem with high player numbers and SQL servers that should be addressed .....

[Cleaven]

EAW itself would not need more patching, just the server kit and those aspects that deal with the stabilty and function of the SQL database. Plus writing new mission scripts to take advantage of it.
(And make a Linux version too.)

Also have to add the caveat that a basic SFC2.net flatfile db compatible mission list should be a priority.  

[FireSoul]

can you tell me what kind of SQL SFC server use?

... I can see how this was done, actually..
.. as part of the mission success, a change to the DB was done with the script. Thus the SQL server saw that a planet was then in that hex, etc.

.. interesting work gentlemen.. interesting indeed.
..hey.. idea.. a monster spawning planet?

-- Luc

[Corbomite]

I would say something, but I'm speechless.

[clintk]

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

[FPF_TraceyG]

At this point in time, no, the XenoCorps server will not be running a SQL campaign because of security issues. Encrypting the password seems to be the best solution, however. How might this be done? Well, I could tell you... but then, I'd have to kill you...  

[NuclearWessels]

OUTSTANDING!!!!!!!!!!!!!!!!!!!!!!!!!!

That is the best of news for online campaigns (one the SQL side is reliable),
and would address many of the huge headaches caused by the API to date!

Well done!


dave
 

[**DONOTDELETE**]

Wow, great stuff!  

I'm a little confused though...

You don't need to open the database to the internet to
run the SQL server - only the serverplatform needs to
see the database right? (either root access on the same
machine or another machine on the same LAN behind
a gateway?)

You are discussing opening the db to allow client
side scripts to read / modify the db?

You could always limit the scripts to a single server
by using a password protected connection in the
compiled script? (relatively safe...) Or does an
actual sytem DSN need to be on the client machine
for this to work? (php scripts and java applets can connect
without a MySQL ODBC driver system DSN...)

Another issue with the SQL db, is safely opening
it to a webmap applet. I think this would best be done
by creating a login for the applet with read-only
privileges. This would of course require the db to
be open to the internet and might allow brute-force
password attacks on any logins with full privileges.
(I would hope this kind of attack would be unlikely,
and would result in DoS more than anything...)

I think it can be done safely. I'm sure anyone who plays
on SFC2.Net servers would not try to hack in, it's just
a matter of keeping all those other yahoos out there
on the internet out of it!

We will certainly need to securely open the SQL db to the
internet to get all the potential benefits SQL has to offer.

Regarding stability (processor load)  with high player numbers, I still
suspect that the best performance may be obtained by putting the
MySQL db on another machine on the same LAN (100Mbit or better)
with the serverplatform.

Sorry for rambling but this really got me thinking...
(A little more detail on how this was done might be nice.)

P.S. FireSoul, the EAW serverplatform uses a MySQL db
and connects to it using a MySQL ODBC driver system DSN.

 

[Skawpya]

So when will the server go public again? and if it does, whats to be tested for now?

[Mog]

I am very excited by this. Marvellous work

[FireSoul]

Quote:
P.S. FireSoul, the EAW serverplatform uses a MySQL db
and connects to it using a MySQL ODBC driver system DSN.

Good. Thank you. I have plenty of MySQL experience, and am familiar with MyODBC, how to install it and how to use it.
.. you wouldn't happen to have a dump of the SQL tables and its columns handy, would you?

-- Luc

[DarkElf]

Clint, just for the record, I did throw my original idea out the window.

Instead of using your IP Address (which was a major security risk) we use your Metaverse ID instead.  More than likely they are using the default port for their MySQL server, and if there are more security issues that need to be addressed, we will resolve them.

In this case, not only the ServerPlatform would need a connection to the server but the Client who is running the mission as well.  The connection only lasts a second and connection is terminated immediately when the query is finished.

More details on how this was done can be revealed later.

[clintk]

I'm just a little concerned that we want to open a TCP/IP connection to our database server from a client PC using the internet. This means we will have to expose the database server port on the firewall, one of the big no-no's when designing a secure system.

Might I suggest that we design a secure web service that runs on the database server and that this is the only component that opens a connection to the database. The database server therefore would not have to be exposed on the firewall, negating the security hole. Not only could we easily upgrade this component, we also wouldn't have to keep upgrading the scripts at the same time. This would save on bandwidth as hosting the scripts for downloading would not be an issue.

The web service could expose functions that would facilitate updates and querying, the client scripts would simply have to open a http (https) connection and exchange XML via SOAP. This could easily be wrappered in a component that resided on the client, reducing the size of the client scripts and enabling us to develop the the component in .NET or COM.

I hope this all makes sense, but I'd hate to see a campaign ruined by some 14 year old hacker.

[**DONOTDELETE**]

Quote:

Quote:
P.S. FireSoul, the EAW serverplatform uses a MySQL db
and connects to it using a MySQL ODBC driver system DSN.

Good. Thank you. I have plenty of MySQL experience, and am familiar with MyODBC, how to install it and how to use it.
.. you wouldn't happen to have a dump of the SQL tables and its columns handy, would you?

-- Luc  

Funny you should mention - I just did that today!
(Just mailed it off to the address in your profile)

clintk - great idea to use a "secure web service" -
http (https) connection and exchange XML via SOAP
sounds tricky though (thinking of Java-Webmap).
A php webmap would run serverside though
so no problem security-wise (I'll stay on it...)

Is there an easier way to lock it down?
(I do like your idea though - sounds efficient too.)
 

[FPF_TraceyG]

That makes perfect sense ClintK and it's an excellant suggestion. It is certainly an option.  

[Fluf]

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

[Corbomite]

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

[FireSoul]

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]

Quote:
By combining SQL queries with mission scripting, DarkElf and I, today were successfully able to modify the D2 map terrain from directly within a mission script. Out first test was to create a planet, which upon completion of the mission, then appeared on the main campaign map.

Wait a minute..
.. let me get this straight:

.. are the SQL queries (insert/update) done from the D2 server side to the SQL server, or from the individual playing SFC:EAW clients running a customized script, out there, to the D2 server?


-- Luc

[**DONOTDELETE**]

Quote:


Wait a minute..
.. let me get this straight:

.. are the SQL queries (insert/update) done from the D2 server side to the SQL server, or from the individual playing SFC:EAW clients running a customized script, out there, to the D2 server?


-- Luc  

Normally all queries are performed by the ServerPlatform executable.
What we are discussing is custom mission scripts that can run queries
on the db from the client running the script. (Thus the need to open
the db to the internet - is also necessary for a java-webmap)  

[FireSoul]

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**]

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?  

[FireSoul]

Quote:
Are there known security exploits for MySQL?

Always.. unless you keep up to date. (YAY Debian!)
Ok, so you're thinking hard-compiled SQL-access information.. sensible enough.
.. but packet sniffing for the password is easy these days. I know that I could easily collect the password using "ethercap".

.. a better more convenient method of authentication needs to be thought up.

so the challenges:
1- the SQL server authentication information should be stored in a separate file, to make things easy and modular.
2- the SQL queries should be tunneled through some kind of encrypted method.

I think #1 was possibly solved in a reply, higher up.. but what about #2?

-- Luc

[TOCXOBearslayer]

 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]

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]

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]

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]

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]

Nothing like a bit of brainstorming !

Does anyone know why we're using MySQL ?

[Cleaven]

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]

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**]

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]

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.  

[Kel]

Quote:
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.  

Tracey,

I'm a little confused on your proposed architecture.  You are suggesting a client-side program that resides on each players PC.  This program will pass along info to a server side program regarding mission results.  How does your client-side program get the results to send? Do you intend to create special missions (which again reside on the player PC) that pass this info to the client-side program?  Am I correct so far?

Once on the server side, your dedicated program can report to the database all kinds of 'special' mission results as you have mentioned above.  

If this is all true, then how do you prevent the 'real' D2 server program from reporting traditional mission results which may or may not conflict with your special results?  

If I'm still on the right track so far...is one possibility to effect hex DV's differently if a player is in PvP vs an AI only misison?

Great work, by the way.  

I am available to help test, design or code reviews.  If you need any help in these aeras, let me know.  I graduated with honors from the Gorn Academy of Advanced Computing !

GDA-Kel
Gorn Dragon Alliance

[FPF_TraceyG]

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...  

[UDF_Intruder]

Just out of curiosity, has anyone checked the transaction log to see what and/or how ServerPlatform manages the database?

Excuse me if that was a dumb question, but if you could document what does and does not get altered, that would help figure out what needs to be done.

I haven't gotten around to setting up a SFC Server using SQL, but would love to see the structure.

[**DONOTDELETE**]

Quote:
Just out of curiosity, has anyone checked the transaction log to see what and/or how ServerPlatform manages the database?

Excuse me if that was a dumb question, but if you could document what does and does not get altered, that would help figure out what needs to be done.

I haven't gotten around to setting up a SFC Server using SQL, but would love to see the structure.

Duh, I've been trying to figure out the db structure, and I forgot all about tracing the ODBC driver calls.  
Just enabled tracing on my test server now.

That may help with the details of the db that I havent figured out yet... as well as how to implement
db security. I'll check it out once the log collects enough info...

Thanks for the input!

P.S. My tentative structure of the database can be found here:
tentative db structure
If there are any glaring errors or omissions please let me know.
(aside: so far I have been unable to capture any data from the 'battlesrunning' table but have caught data
in the 'preparedmissions' table...)

 

[Tao]

Didn't realize that was your test server. Tried just about every mission, they all seemed to work well. No PvP, but maybe later when more people are on. Friendly space (0809); monster, quite a few patrol, convoy escort. Didn't get a scan mission. Enemy space(0909); patrols, convoy raid, no scan missions, base assault (BATS). Neutral space; not sure which hex it was, but just south of the above hexes, patrol, convoy raid.
All missions were in a D7 variant or D5K.    

[**DONOTDELETE**]

Had a quick look at the ODBC trace - looks quite arcane to me - not sure it will be much help.
(EDIT: I think I was using the wrong dll to trace with, will try again...)

I went looking for MySQL's transaction log but wasn't sure where to find it...
I checked the manual (not lost! ) and found that:

"MySQL supports two different kinds of tables: transaction-safe tables (InnoDB
and BDB) and not transaction-safe tables (HEAP, ISAM, MERGE, and MyISAM)."

Since all the tables are of the MyISAM type, I don't think a transaction log is produced.
If present, they should be in "/mysql/data" but I only find the error log there.

Please correct me if I have misinterpreted this or missed the obvious.

P.S. Thanks for the report Tao!