add lastlogin_ip field to account table submission

[Shin Noir]

It is talked about here: http://www.eqemulator.net/forums/showthread.php?t=28285

Essentially, a lastlogin_ip field contains the last IP the user was known to have logged in as. It is useful for servers that utilize EQEMU's Login server for authentication and wish to have a way to authorize users via a web based tool at least by their IP to allow them to do modifications to their characters (or set options).
It also can be used to discover a player's IP if they try to log off quicker than you can type #iplookup in game.

I used this change, but really you can do this in a number of ways.

First create a patchfile in /utils/sql/svn/REV#_loginip.sql

ALTER TABLE `character_` ADD `lastlogin_ip` varchar(32) NOT NULL DEFAULT '';

in client.cpp, line 803:
From

database.UpdateLiveChar(char_name, GetAccountID());

To

	struct in_addr  in;
			in.s_addr = cle->GetIP();
	database.UpdateLiveChar(char_name, GetAccountID(), inet_ntoa(in));

in database.cpp, line 1805:
From

bool Database::UpdateLiveChar(char* charname,int32 lsaccount_id) {

To

bool Database::UpdateLiveChar(char* charname,int32 lsaccount_id, char* loginip) {

same file, line 808
From

	if (!RunQuery(query, MakeAnyLenString(&query, "UPDATE account SET charname='%s' WHERE id=%i;",charname, lsaccount_id), errbuf)) {

To

	if (!RunQuery(query, MakeAnyLenString(&query, "UPDATE account SET charname='%s', lastlogin_ip='%s' WHERE id=%i;",charname, loginip, lsaccount_id), errbuf)) {

In database.h line 224
from

	bool	UpdateLiveChar(char* charname,int32 lsaccount_id);

To

	bool	UpdateLiveChar(char* charname,int32 lsaccount_id, char* loginip);

That's it.. Pretty sure this would update the IP every time a character logs in, and that's it. Which technically you could update this information when you a char first selects server, but no real reason the above modification is happening even without the ip being utilized.

I may have to release the struct properly, let me know if it's needed I'd like to learn that routine if you do add it.

[Shin Noir]

Edit: My patchfile adds a field into character_ when it was supposed to add to account. Sorry. I can't edit my post, either.

ALTER TABLE `account` ADD `lastlogin_ip` varchar(32) NOT NULL DEFAULT '';

[Shin Noir]

Rogean posted a solution to this on r980 (link). Not sure I agree this needs an entirely new table to get the job done, but it can be useful for seeing a list of IPs a client has used in the past I suppose.

[trevius]

Yeah, personally I would have preferred just a last_used_ip field in the accounts table, but the way he did it should be ok as well. I prefer to use a gui for all MySQL stuff, so having to open the accounts table to find the account ID in it, then checking the new account_ip table for the account ID and entries for it is just 1 extra step. We can always add in the last_used_ip field at any time though. I just don't want to overkill the whole IP tracking idea :P

Really, the best use of tracking every single IP that someone uses would be to tell if it was them cheating or maybe someone else playing their character and cheating with it. But, since we don't log IPs in the hackers table yet, that isn't very useful just yet. I think the only other reason to track all IPs an account has used would be for finding all accounts that this same person has ever used. So, if you wanted to make sure to ban all of their accounts, you could figure out which are theirs fairly easily. Though, you might wind up banning other people's accounts, since people tend to share accounts quite often in EQEmu even though it is a bad habit to get into.

[Rogean]

Originally Posted by trevius

I prefer to use a gui for all MySQL stuff, so having to open the accounts table to find the account ID in it, then checking the new account_ip table for the account ID and entries for it is just 1 extra step.

select a.*, b.name from account_ip a, account b where b.id = a.accid;

looking for someone's ip's:

select a.name, b.* from account a, account_ip b where a.name = "Rogean" and b.accid = a.id;