[FilterScript] [MySQL R41-4] IP Logger (dialogs)
#1

IP Logger
(MySQL R41-4)
Introduction:

What it does is basically, every time someone connects to the server, when he disconnects, even if he doesn't register, it saves: The username, the IP, the date and exact time he connected, and disconnected. So four fields. How can this be useful? Well, an example is: You can detect multi-accounts. (manually ofc, I can release an automated one if requested.)

Screenshots:









Dependencies :

Source code:
Reply
#2

brilliant !
Reply
#3

Nice
Reply
#4

It's AMAZING!!
Reply
#5

Provide a .sql file about the table structure please.

Few tips to consider:
  • Save dates as TIMESTAMP OR DATETIME datatype and not as string. There is mysql function to format a readable form and an integrated system can even allow you retrieving total online time and many more benefits.
  • Avoid non-threaded queries as it can lead to memory leaks (you forgot to delete the cache instance).
  • Do not copy strings directly as this:
    pawn Code:
    g_ConnectionDate[playerid] = ReturnDate();
    g_pIP[playerid] = ReturnIP(playerid);

    g_DisconnectionDate[playerid] = ReturnDate();
    There is `strcpy` (macro of `strcat`) or `memcpy` function. Avoid `format` function.
  • Select columns you want to retrieve and not everything. `Name` or `Disconnected` columns are never retrieved.
  • Set appropriate keys to table structure. Searching by a string is slower than an integer. Searching by a non-indexed string is even slower.
Reply
#6

Quote:
Originally Posted by Calisthenics
View Post
Provide a .sql file about the table structure please.

Few tips to consider:
  • Save dates as TIMESTAMP OR DATETIME datatype and not as string. There is mysql function to format a readable form and an integrated system can even allow you retrieving total online time and many more benefits.
  • Avoid non-threaded queries as it can lead to memory leaks (you forgot to delete the cache instance).
  • Do not copy strings directly as this:
    pawn Code:
    g_ConnectionDate[playerid] = ReturnDate();
    g_pIP[playerid] = ReturnIP(playerid);

    g_DisconnectionDate[playerid] = ReturnDate();
    There is `strcpy` (macro of `strcat`) or `memcpy` function. Avoid `format` function.
  • Select columns you want to retrieve and not everything. `Name` or `Disconnected` columns are never retrieved.
  • Set appropriate keys to table structure. Searching by a string is slower than an integer. Searching by a non-indexed string is even slower.
Hey, thanks a lot for the tips, I really appreciate it, I've done everything you mentioned, (Commit) except the datetime, do you have an example or something? And I want to keep the same actual form displayed in the dialogs, if possible.
Reply
#7

Well really good job!
Reply
#8

wow nice bro
Reply
#9

Quote:
Originally Posted by TheToretto
View Post
Hey, thanks a lot for the tips, I really appreciate it, I've done everything you mentioned, (Commit) except the datetime, do you have an example or something? And I want to keep the same actual form displayed in the dialogs, if possible.
pawn Code:
INSERT INTO yourtable (time_column) VALUES (NOW())
To show it:

pawn Code:
new date[36], string[40];
cache_get_value_name(i,"time_column", date, 36);
format(string, sizeof(string), "Date: %s", date);
Reply
#10

Quote:
Originally Posted by KinderClans
View Post
pawn Code:
INSERT INTO yourtable (time_column) VALUES (NOW())
To show it:

pawn Code:
new date[36], string[40];
cache_get_value_name(i,"time_column", date, 36);
format(string, sizeof(string), "Date: %s", date);
Thanks, but what if I want to just store the time? Because, I send only one query, to save both the connection date & disconnection. I don't want to use multiple queries.
Reply
#11

Nice one man!
Reply
#12

Thanks, appreciate it
Reply
#13

Quote:
Originally Posted by TheToretto
View Post
Hey, thanks a lot for the tips, I really appreciate it, I've done everything you mentioned, (Commit) except the datetime, do you have an example or something? And I want to keep the same actual form displayed in the dialogs, if possible.
Yes, it is possible: https://dev.mysql.com/doc/refman/8.0...on_date-format
pawn Code:
SELECT IP,
       DATE_FORMAT(Connected, '%%Y %%M %%e - %%T') AS Connected
  FROM iplogger
 WHERE Name = '%e'
Thank you for taking into consideration my suggestions. Re-consider about the use of threaded SELECT queries.

When your table does not have a primary key and searches by name, it filters about 10 percent -- it scans the whole table for looking any matches. A name cannot be unique as a player can connect more than once but a pair can be. A player joins a specific time, this pair is unique and we can also take advantage of it.

pawn Code:
CREATE TABLE `iplogger` (
  `Name` varchar(24) NOT NULL,
  `IP` varchar(15) NOT NULL,
  `Connected` datetime NOT NULL,
  `Disconnected` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`Name`,`Connected`)
)
Now it filters 100 percent.

Store unix timestamp on connect for a player and use FROM_UNIXTIME when a player disconnects.
pawn Code:
INSERT INTO iplogger (Name, IP, Connected)
VALUES ('%s', '%s', FROM_UNIXTIME(%d))
There is one last suggestion: IPv4 can be converted to integer and take less space. If you are interested:
https://dev.mysql.com/doc/refman/8.0...tion_inet-aton
https://dev.mysql.com/doc/refman/8.0...tion_inet-ntoa

Store IP as unsigned int and the use is very simply INET_ATON('127.0.0.1') and INET_NTOA(IP)
Reply
#14

You may not save personal data from EU citizens according to GDPR.
Reply
#15

Awesome but can you release a sqlite version too
Plz
Reply
#16

Thanks Calisthenics, I understood what you meant, but even with examples I didn't really succeed employing them. Could you please apply a pull request with the changes? Would be very nice from you.

Quote:
Originally Posted by Mellnik
Посмотреть сообщение
You may not save personal data from EU citizens according to GDPR.
Ew, idgaf lmao

Quote:
Originally Posted by GameOvr
Посмотреть сообщение
Awesome but can you release a sqlite version too
Plz
I've never used those "lite" versions of MySQL, so I can't, but it seems like you're onto SQLite, why don't you do?
Reply
#17

Tried but I faild xD

Idk how to check that the player connected ip is already in the db and more such things
Reply
#18

Quote:
Originally Posted by TheToretto
Посмотреть сообщение
Thanks Calisthenics, I understood what you meant, but even with examples I didn't really succeed employing them. Could you please apply a pull request with the changes? Would be very nice from you.
I noticed a few more issues so I addressed them all in the pull request I sent. Already tested to confirm everything works as expected.

I like how the pagination was done, well done.
Reply
#19

Quote:
Originally Posted by Calisthenics
Посмотреть сообщение
I noticed a few more issues so I addressed them all in the pull request I sent. Already tested to confirm everything works as expected.

I like how the pagination was done, well done.
Thanks a lot dude I really appreciate your big contribution, added your name in the top of the script, didn't knew what else I can do to thank you
Reply
#20

You should also remove both includes zcmd and sscanf because its only used one time and not necessary, you can use classic samp OnPlayerCommandText
Reply


Forum Jump:


Users browsing this thread: 2 Guest(s)