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.