Guild List to sql database Parser

[Valareos]

Ive developed a parser to allow you to upload your guild memberlist to an SQL database. This parser is offered free to anyone who wants to just use it as is, or to develop offline apps for guild management. It supports multiple guilds on the single database

THIS GUILD PARSER contains four files.

To Install:

Create on your server a database to store the info. Use the supplied database.sql to create the tables.

Edit config.php to include your database information

Upload config.php, index.php, and upload.php to your server. It doesnt matter what folder, as long as they are all in the same one.

There, Installed.

Now to get the guild data

Start Istaria. In the Patcher Options, you want to make sure that Support Logs is turned ON. Log onto the character that is in the guild you want to track.

As soon as the game is loaded, open up the guild manager, then click the members tab. Once your members list is shown, close istaria.

Now, go to the upload script. This will be where you placed it on your server, eg www.yourserver.com/guild/update.php

Click browse, and browse to your logs folder located in your Istaria Install. There should be a file there that says client_net_log_TIMESTAMP.txt, where TIMESTAMP is a date and time when the file was made. if you have multiple ones, use the latest one.

Click Upload and watch the program parse the file and upload it to the database!

It uploads all the information that file has for your guild members. In addition, index.php contains a sample output of the information, listing your guild, how long they been offline, and a percentage of your active guild members.

I will be doing no further work on this beyond any bug fixing with what is here. These files are offered free to the community to be modified as they wish. I only ask that credit be given for the parser back to me

Have fun Programmers!

[Valareos]

Be sure to uncheck the support log option when you are done! leaving it on with istaria running for too long will seriously eat up your computer space

[Steelclaw]

Welcome to the club of log file data gleaners!

A note and a few tips:

index.php has an SQL injection vulnerability with the $guild variable, and upload.php similarly while reading the uploaded log file. This isn't cool because an intruder (person or bot) can modify your SQL query by putting the right stuff in the guild form variable, or in the uploaded file -- deleting content that should not be deleted, or reading out or modifying data (from other tables and databases) that should not be accessed by the web app.

Consider using prepared statements with bind variables and the PDO database abstraction layer instead of mysql-native calls. Bind variables replace the user-supplied data in a query with a placeholder (usually '?') and allow the query to be passed to the database separately from the user-supplied data, stopping sql injection in its tracks. PDO has all the framework necessary to do prepared statements, and allows using other database backends (sqlite, postgres, mysql, etc), usually by just changing the connect string.


Input sanitization:

Both index.php and upload.php should check a configurable list of allowed guilds before attempting to do any database activity (this helps prevent junk from getting inserted in the database). You may also want to consider using preg_match() for your parsing and data-sanitization needs -- ensuring that only expected values and characters get inserted. You really don't want someone uploading a log file containing malicious javascript instead of the character's name.

Please let me know if you'd like some help, further explanation, or pointers to documentation.


Finally, for those deploying upload.php, consider password-protecting access to the file so that only authorized people can update the guild list. Contact your web host for help there, as the process is very implementation-specific.

[Valareos]

Steelclaw is correct in his assesment

This is a proof of concept, i did not program in anyting more than what was needed to make it work, though I took a few precautions

First, the file that you upload is not permanently stored. once the file is run in fact, the server removes it automatically, so no building up of useless files.

next, the fopen opens it as read only, and then only looks at one line at a time. It also ignores any line that doesnt start with the proper starting point. Worse thing that can be done is coding stored in the database. This can be rectified by adding a check that allows only alpha numeric characters and space (strips off anything else)

last the index.php is just a sample file. it is expected anyone who is developing a program around this will put in their own protections. This is also why I left it as simple as possible.

of coarse, you can use it as is, and will as it is work to track your members last login and how long they been offline, but as steelclaw said, it is best to put it in a password protected area to ensure that unauthorized uploads are not done.

Im afraid I do not have the time to turn this proof of concept into a fully functional offline guild manager. All my time currently is involved in another project which will be announced soon.