Troll/ip-tracker, detect multiple accounts/trolls [3.6 compatible]

[TMM-TT]

Continuosly information about updates can also be found here (http://forum.tornevall.net/showthread.php?t=102799).

Edit 2007-08-07

Since the script needs to be rebuilt from scratch due to some problems with it, I've decided to temporarily stop the distribution. On larger sites, this script may cause heavy lagging when active. There has also been reports on "duplicate keys", that should be fixed. To prevent more problems cause by this hack, it's removed and cannot be downloaded.


History

In 2005 a forum was founded, for a short period of time that was intended to work as a service for people with a common interest - a reality show. The forum grew big and the traffic was very intensive. Moderators worked day and night to assist the forum. Traffic and events in the show made people show different kind of feelings and some of them showed this in strange ways. They said "I'd better register 10 nicknames, just to secure that noone can ban me". Sometimes, when they got banned, they registered new nicks and sometimes they switched ips but they didn't post anything the same day. Next day, they switched ip again and it suddenly got much harder to trace their nicks back to the start.

In 2006, the forum opened again (new season) and we was prepared for this. A script, specialized to trace trolls, was written and the main purpose was to trace things that vBulletin missed. This plugin was requested at the vBulletin forums.


Now, it's here, as a beta production - a trolltracker that stamps all visitors even if they don't post anything. It also, partially, checks if an ip-address can be traced from anonymous proxies by reading headers that proxies are sending to he server (HTTP_VIA and HTTP_X_FORWARDED_FOR).


Templates to change (Optional): 3


Attached

trolltracker-2.0.3beta.zip - the main archive with the tracker and extras
iptracker.php - A quickfix for the coming version 2.0.4 that changes the SHOW COLUMNS-behaviour-bug in the tracker


The archive

The zip should contain the following files
The product, currently named product-trolltracker_beta.xml because of it's beta status
iptracker.php, the tool that makes it easier to trace nicknames and ip's
iptracker_search.php, htmlcode - the searchform that iptracker.php is using (old code that never used templates)
trollpost.php, a quite slow script that imports ip's and usernames from the post-table to make it possible to trace older data. (If you run this over a web-interface, it might not work, because of timeouts --screendump-- (http://forum.tornevall.net/images/tracker_30sec.jpg))
How to install
Copy *.php to your forumroot
Install the product code (choose overwrite if you're upgrading)
Check the configuration from your admincp (settings for usergroups that can run the iptracker.php is made from there)Additional information


Track older ips (2.0.x)

If you want to import older posts, you can do that, but because of timeouts (and bad scripting) trollpost.php may timeout if you run the script from a web-browser. This part is, currently, unsupported, but if someone has any ideas here, feel free to notify me.

OPM Support

The ip-tracker supports RBL Monitor (https://vborg.vbsupport.ru/showthread.php?t=96318).


How to use it

Use the tracker by calling http://your-forum.com/iptracker.php


Database

The installation creates a table named user_troll for your forum.
The FQDN-support also adds a table called user_trollresolver for the resolver cache (like the one used in the OPM monitor)

Information and issues
There is no version 1.x - that version was private (this is not an issue)

It's still a beta, so bugs may still annoy us

Testing on vBulletin 3.6.0 rc1: Works fine
Importing posts with trollpost.php might give strange results if you are using the multiple account detector (AE) (https://vborg.vbsupport.ru/showthread.php?t=107566): reports in forum that are posted with a specific userid makes it look like a troll is using other usernames tooTo do

See CHANGELOG (http://forum.tornevall.net/showpost.php?p=1316841&postcount=3) for full details


Show the tracker in navbar (v2.0.1+)

To show the tracker in the navbar (only for the admingroups that can be found in the options), edit your template 'navbar' and add this code where you want it to be shown:


<if condition="$show['trolltracker']"><td class="vbmenu_control"><a href="iptracker.php">Trolltracker</a></td></if>
I chose to put it in the end of navbar, right after this code:


<if condition="$show['member']">
<td class="vbmenu_control"><a href="login.php?$session[sessionurl]do=logout&amp;logouthash=$bbuserinfo[logouthash]" onclick="return log_out()">$vbphrase[log_out]</a></td>
</if>
How to show trolls in the onlinelist

Edit template:
whosonlinebit

Find:
</tr>

Add above:

<if condition="$show['trolldata'] AND $userinfo['trolldata'] != ''">
<td class="alt1">
$userinfo[trolldata]
</td>
</if>
Edit template:
WHOSONLINE

Find:

</tr>
$onlinebits
Add above:


<if condition="$show['trolldata']">
<td class="thead">
<b>Trolldata</b>
</td>
</if>
Support, help, contact

Any questions, requests and bugreports can be sent to me by PM, mail or via threads at http://vbulletin.tornevall.net/


Don't forget to install (https://vborg.vbsupport.ru/vborg_miscactions.php?do=installhack&threadid=119695) this plugin

[TMM-TT]

Fixes and something to wait for...


CHANGELOG

2.0.4 [beta]


Using short-time cache to look for duplicates instead of scanning the whole database (based on cookietimeout)
The above cache is used with the whos online-list too
Added cache for alerts so reports only will be sent on changes in the usernames
2 phrases fixed
Added username exclusions (usernames/userids that won't be blocked)
Fixed the SHOW COLUMNS-problem in iptracker.php
Fixed a serious injection problem


2.0.3 [beta] (bonus release)


Autoblacklist banned users


2.0.2 [beta]


Alerter caused blank page when PM-reporting disabled
Trolltracker now blocks other usernames too if there's a match with the list in options
Trolltracker now blocks linked IP-addresses listed in options


2.0.1 [beta]


Added class="alt1" into tables in iptracker*.php
Added "exclude guests from result" in the iptracker (option that won't count users with id 0 - guests, etc)
Added report/alerter by PM/forum
Added field alert into database
Added autodetection on search (if a searchstring contains XX.XX.XX.XX it will be treated as an ip)
Done some minor changes to the userlink-function (the one that can bind new usernames to their old ones faster)
Fixed the userlink-lookup in iptracker.php (made details)
Added function to block specific nicknames when reaching the trigger limit
Using strftime for date/time-fields (Tested with vB 3.6)
Added search-depth in iptracker.php
Added "exclude rbl-results" in iptracker.php (Won't show proxies if any)
Added $show[trolltracker]-condition so the tracker can be shown for admingroups in templates
Added trolldata to who's online
Added highlighted searchresults for iptracker.php
Admins will now be excluded from the dupe-checker
Blocked users are now shown as blocked in "Who's Online"
Scanner using vbphrases instead
Queries to database rewritten (JOIN)
Added group for administration of the trackerdb (removal of entries)
FQDN Compares on undetected users




To do


Add trolldupes.php - quickscan the database for duplicated nicknames and list them
iptables support (?)
Add exclude userids from scanner (mainly for fixing the AE-Tripper issue)
IRC/Eggdrop support for online-tracing and support
Autoblacklist banned users

[Tyegurl]

thanks so much for this....i happen to have one single member who this will be useful on....not that he hides himself lol but at least i will be able to track him better i hope.

[TMM-TT]

thanks so much for this....i happen to have one single member who this will be useful on....not that he hides himself lol but at least i will be able to track him better i hope.


It will be easier in 2.0.1, with the Who's online part. :)

[Ski-Whiz]

I like this!! Question, will this cause my database to grow to a HUGE size, since it tracks every IP to hit my forums?

[TMM-TT]

I like this!! Question, will this cause my database to grow to a HUGE size, since it tracks every IP to hit my forums?
No, if there's an old matching ip (together with userid/username), the plugin will do an UPDATE instead of INSERT to that entry. :)

[Ski-Whiz]

ah, ok...

That's great, I was kind of worried. :D

Consider this installed. :D

[TMM-TT]

ah, ok...

That's great, I was kind of worried. :D

Consider this installed. :D

Nice! As fast as I'm done with the report-via-PM/forum-function, I'll try to release v2.0.1 - check the 2nd post in this thread for news :)

[Ski-Whiz]

Great!

I had to edit the font colors in the search.php file, but no biggie. Every site will differ..

Thanks!! Great hack!

[TMM-TT]

Great!

I had to edit the font colors in the search.php file, but no biggie. Every site will differ..

Thanks!! Great hack!
In next release, I'll remove all those fonts and sizes, so it follows the CSS classes instead. :)

[PHPGeek2k3]

Support, help, contact

Any questions, requrests and bugreports can be sent to me by PM, mail or via threads at http://vbulletin.tornevall.net/


Just a suggestion/request

how would adding the ability to filter out posts from a certain forum and filter out specified users setup in vbulletin options.

Thanks
- James

[TMM-TT]

Just a suggestion/request

how would adding the ability to filter out posts from a certain forum and filter out specified users setup in vbulletin options.

Thanks
- James
Do you mean the product that filters out posts, now? :)

[Alfa1]

Will there be a function in this hack, that allows us to do a query for double accounts in general? i.e. where one just scans for any IP which has multiple accounts attached to it? That would be the ultimate IMO.

[Alfa1]

I am receiving hundreds of error messages after installing the troll tracker. All are relating to error number 1053

Invalid SQL:
SELECT COUNT(*) AS trolledlinks FROM vb9_user_troll WHERE userlink = '0';

MySQL Error : Server shutdown in progress
Invalid SQL:
SELECT COUNT(*) AS trolledlinks FROM vb9_user_troll WHERE userlink = '0';

MySQL Error : Server shutdown in progress
Error Number : 1053
Invalid SQL:
UPDATE vb9_user_troll SET userlink = '5821' WHERE idx = '7774' LIMIT 1;

MySQL Error : Server shutdown in progress
Error Number : 1053
Invalid SQL:

INSERT IGNORE INTO vb9_session
(sessionhash, userid, host, idhash, lastactivity, location, styleid, languageid, loggedin, inforum, inthread, incalendar, badlocation, useragent, bypass)
VALUES
('cb02a1f7973b190066d3109377098f4d', 0, 'IP removed here', '637bd32a68f483bf605798032a63fddb', 1151510727, '/forum/index.php', 0, 0, 0, 0, 0, 0, 0, 'Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; de) Opera 8.52', 0);

MySQL Error : Server shutdown in progress
Error Number : 1053

And there are many more, but I do not want to hijack the thread. What is the problem?

[TMM-TT]

Will there be a function in this hack, that allows us to do a query for double accounts in general? i.e. where one just scans for any IP which has multiple accounts attached to it? That would be the ultimate IMO.


You mean something like the example shown in the attachment? Yes, there is a single deep-check now. :)




And there are many more, but I do not want to hijack the thread. What is the problem?

MySQL Error : Server shutdown in progress




I found a thread here (http://www.vbulletin.com/forum/showthread.php?t=149831), about this errormessage but that was with vB3.0 and a dedicated server (and this is the first time I see that error-message). Is that happening, only when using this product? The last message you pasted can't be found in the tracker:

INSERT IGNORE INTO vb9_session

Can it be something else, that happens? :surprised:

[Snake]

Thank you very much!

[Tyegurl]

ummmm i need some help....i logged out of my vbulletin and now it won't let me back in lmfao.....how the heck do i get this removed so i can get back in and then set it up again?

[Tyegurl]

okay now its not letting anyone in....and btw these members only have 1 account from one ip....

[TMM-TT]

okay now its not letting anyone in....and btw these members only have 1 account from one ip....


It sounds like something happened, that shouldn't happen and something that must be fixed soon. Was the option for blocking trolls active?

Can you edit your config.php, try add a line temporarily:

define('DISABLE_HOOKS', 1);

After that, you should be able to login, disable the product and get in again (just don't forget to remove the above line later ;)).

I'll attach a modified xml that disables blocking of users until the next version is released, just to be sure that it won't happen again. If it was something else that happened, what messages did you see?

[Tyegurl]

define('DISABLE_HOOKS', 1);

i knew that lol cuz when i upgraded i had to do that

duh lol

disabled and working again thanks for the reminder

[Tyegurl]

the only message that i got and everyone else got was

too many nicknames on this forum blah blah blah

yes it was enabled

[Alfa1]

You mean something like the example shown in the attachment? Yes, there is a single deep-check now. :)
I'm not sure if that is what I mean. Hope so.

I found a thread here (http://www.vbulletin.com/forum/showthread.php?t=149831), about this errormessage but that was with vB3.0 and a dedicated server (and this is the first time I see that error-message). Is that happening, only when using this product? The last message you pasted can't be found in the tracker:

Can it be something else, that happens? :surprised:

Thanks for that thread. It has started right after installing this hack. 99% of the error messages have to do with the troll tracker. So I pretty sure this hack caused it, but I have no clue why or how to fix it. Like in that thread my errors are also on fixed times. Give or take a minute: 00.00 06.00 12.00 18.00

[Tyegurl]

btw i want to thank you for such a quick and helpful reply to my email. that kind of support is so hard to come by. that's just top notch work on your part. thanks again!

you are very easy to find and get in touch with. i wish more coders were that way!

[TMM-TT]

btw i want to thank you for such a quick and helpful reply to my email. that kind of support is so hard to come by. that's just top notch work on your part. thanks again!

you are very easy to find and get in touch with. i wish more coders were that way!
I'm a very supportive person.. It's just fun to help :)

[TMM-TT]

2.0.1 beta has been released. Report bugs, please :)

[TMM-TT]

Due to a smaller bug that caused a blank page when disabled PM-alerting only, there's now a 2.0.2 with some other fixes too

2.0.2 [beta]


Alerter caused blank page when PM-reporting disabled
Trolltracker now blocks other usernames too if there's a match with the list in options
Trolltracker now blocks linked IP-addresses listed in options

[TMM-TT]

Bonus release:

2.0.3 [beta]

Autoblacklist banned users



Meaning, if a user is banned, the user can't even touch your forum anonymously (= not logged in) if there's an ipmatch.

[aveon]

when i search fo ips it gves me this error


<!DOCTYPE HTML PUBLIC>
<HTML><HEAD>
<TITLE>Database Error</TITLE>
</HEAD><BODY>
<H1>Database Error</H1>
The requested URL http://www.mysite.de is not avaliable at the time please try again later.<P>
<HR>
<ADDRESS>Apache/df-exts 1.2 Server at www.mysite.de Port **</ADDRESS>
</BODY></HTML>



also this

You are trying to run this page from the outside of vBulletin. Don't do that.

why do i get this errors sorry im new at this vbulletin stuf thanx...

[kofoid]

I'm getting this error :(
Database error in vBulletin 3.5.4:

Invalid SQL:
SHOW COLUMNS FROM Yadauser_troll WHERE field != 'username';

MySQL Error : You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE field != 'username'' at line 1
Error Number : 1064
Date : Sunday, July 16th 2006 @ 06:39:48 PM
Script : http://lets-yada-yada-yada.com/iptracker.php?q=*&q_t=username&Submit=Ask
Referrer : http://lets-yada-yada-yada.com/iptracker.php?q=&q_t=realip&Submit=Ask
IP Address : 24.8.201.245
Username : Laura
Classname : vb_database

[geppeto]

I'm getting this error :(
I have error number 1064 and 1062 too when a user make login in forum

Database error in vBulletin 3.5.4:

Invalid SQL:
INSERT INTO db_mysqluser_troll (userid, realip, forwarded_for, username, timestamp, http_via, date, time, opm) VALUES ('2', '172.16.0.5', '', 'geppeto', 1153162500, '', '2006-07-17', '20:55:00', '0');

MySQL Error : Duplicate entry '0' for key 1
Error Number : 1062
Date : Monday, July 17th 2006 @ 08:55:00 PM
Script : http://172.16.0.2/~xxxxx/index.php
Referrer : http://172.16.0.2/~xxxxx/xxxxx/index.php?do=head
IP Address : 172.16.0.5
Username : geppeto
Classname : vb_database

cheers

[TMM-TT]

I'm getting this error :(

Invalid SQL:
SHOW COLUMNS FROM Yadauser_troll WHERE field != 'username';


The problem seem to start when this row executes from iptracker.php:

$columns = $vbulletin->db->query("SHOW COLUMNS FROM " . TABLE_PREFIX . "user_troll WHERE field != '$q_t'");

I have a strange feeling that this is a mysql-issue where SHOW-commands isn't allowed (confirmable?). I'm not so good in such errors so this is just a wild guess and I should see if I can change this in the code.


geppeto, I'll get back to you. :)
I got the same error before, but that time I fixed that part.

[MThornback]

Database error in vBulletin 3.5.4:

Invalid SQL:
SHOW COLUMNS FROM vb3_user_troll WHERE field != 'username';

MySQL Error : You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE field != 'username'' at line 1
Error Number : 1064
Date : Monday, July 17th 2006 @ 04:20:33 PM
Script : /forum/iptracker.php?q=Marines101&q_t=username&resolver=true&Submit=Ask
Referrer : /forum/iptracker.php
Username : MThornback
Classname : vb_database



Release looks really promising :) hope you can get the kins worked out :)

[aveon]

i cant ewen search for the ip adresses when i type an user name it says 0 user name but there is a member on this name what im i doing wrong can you help me

[kofoid]

thx tmm

[MThornback]

If the wife and I both try and access my forum...she gets blocked out of the site cause shes not an admin...and she gets an SQL error...

[TMM-TT]

A small fix for iptracker.php, in version 2.0.4

Hopefully the problem with SHOW COLUMNS is fixed here (replaced with an array instead). I've also added the file in the release at the top, if someone misses this post.

Debugging for selfchecking is added, but only for admins. Calling iptracker.php with those parameters should therefore work: ?self=1&selftest=<ip>&selfuser=<username>

Also known as "What the user see in the selfcheck"-debugging.


FQDN-checking in the selfchecker is also removed. The FQDN lookup should only be available for the tracking.


If the wife and I both try and access my forum...she gets blocked out of the site cause shes not an admin...and she gets an SQL error...


I think it's time to add that exclude-list I have planned. :)

By the way, you can turn off the function that automatically blocks all users with more than X nicknames (attached screenie).

[TMM-TT]

Invalid SQL:
SHOW COLUMNS FROM vb3_user_troll WHERE field != 'username';



Did I get rid of this problem after the upgrade of iptracker.php? :)

[sduckie2k5]

I use quick account switch and it detected me as a troll. How do I delete my entry in the log?

[TMM-TT]

I use quick account switch and it detected me as a troll. How do I delete my entry in the log?
Do a lookup on your nickname and mark yourself in the checkbox in the list of usernames. Then choose to delete yourself. In the configuration from admincp, you can decide if the removal from the list should be hard or soft.

[sduckie2k5]

to be a bother but I uninstalled the hack, the reinstalled it and tried again, this time, I am not marked as a troll but I still cant seem to get the same view you have in your screenshot with the delete button. I appear to be in the right area but theres missing text next to one of the check boxes and I cant delete even if I select the names then press the button. What am I doing wrong, it almost looks like part of the old version is still left behind? Thankyou in advance

[49er]

I recieve this error also?

Database error in vBulletin 3.5.2:

Invalid SQL:
SHOW COLUMNS FROM user_troll WHERE field != 'username';

MySQL Error : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE field != 'username'' at line 1
Error Number : 1064
Date : Sunday, July 23rd 2006 @ 07:41:17 AM
Script : http://www.*****.com/forum/iptracker.php?lookupdeleted=1
Referrer : http://www.*****.com/forum/iptracker.php?self=1
IP Address : **.***.**.***
Username : ****
Classname : vb_database

[TMM-TT]

to be a bother but I uninstalled the hack, the reinstalled it and tried again, this time, I am not marked as a troll but I still cant seem to get the same view you have in your screenshot with the delete button. I appear to be in the right area but theres missing text next to one of the check boxes and I cant delete even if I select the names then press the button. What am I doing wrong, it almost looks like part of the old version is still left behind? Thankyou in advance

Ahh, now I see.. The phrase for that function seem to be placed in the wrong product here, so when I exported it that one wasn't included.

There should be a troll_iptracker_deleteselected-phrase included to the hack but that was missing and the text I have added there looks like "Delete selected entries from database (immediately)". I can see in your screenie that there is a button, but with no text. That's the text that was missing and another miss by me..



I recieve this error also?

Database error in vBulletin 3.5.2:

Invalid SQL:
SHOW COLUMNS FROM user_troll WHERE field != 'username';

Try the fix here (https://vborg.vbsupport.ru/attachment.php?attachmentid=50877)

[sduckie2k5]

No error but it still doesnt allow me to delete entries and the button still shows no text, sorry.

[aveon]

why do i always get atabase errors when i tried to search for the ip ad adresses and how can i make aoutoblacklist any help will be appriated thanx

[The Finman]

I installed this today and rotflmao when it said "No soup for you!, and you!, and you! :laugh:

Many thanks my friend!:)

[TMM-TT]

why do i always get atabase errors when i tried to search for the ip ad adresses and how can i make aoutoblacklist any help will be appriated thanx
The autoblacklisting works together with the banlist. If you banned someone you can activate the blocking so the hack blocks all users that are trying to access the forum if they are matched with the banned user:

Automatically block banned users
Block banned users from forum (with deepscan), including they who's not logged in to the forum

... meaning that guests (not logged in users) also will be blocked on match with the banlist ...


You can also use the options for "specific users to block". If so, just enter the names or the userids you want to block if they reach a match of XX usernames (Controlled by the option Specific users trigger). This option only block chosen multiple usernames when they are trying to log in.

why do i always get atabase errors when i tried to search for the ip ad adresses and how can i make aoutoblacklist any help will be appriated thanx

Have you tried the fix (https://vborg.vbsupport.ru/attachment.php?attachmentid=50877), or is there another errors?


I installed this today and rotflmao when it said "No soup for you!, and you!, and you!


That's the other hack - AE Multiple Login Detection. ;)

[aveon]

yea great man fix worked for me but can you tel me how to create a black list you mean is it the list on the vbulletin options>user banning options i really need help on this

[TMM-TT]

yea great man fix worked for me but can you tel me how to create a black list you mean is it the list on the vbulletin options>user banning options i really need help on this

No, the tracker use it's own "blacklist".. :)
For example, if the tracker works as it should:



Joe is banned
The tracker see that Joe is banned and blocks him from the forum
Joe try to log out by cleaning up the cookies
The tracker compares the ip to the banlist and see that there is a guest using Joes ip.. He gets blocked as guest also
Three days later, Joes ip is changed and he can log in as a guest again. He knows that hes banned so he use another nickname instead, that he registered after 'Joe'. He logs in as 'Jim'
The tracker checks the database and find that Joe is linked to Jim
The tracker checks with the userban-list and find that Joe is banned
Jim, Joe and the new ip is now locked out



The blacklisting option means that if any guest is matched with a banned user the ip will be blocked from the forum even before login. If 'Jim' is a shared "troll"-account this also means that all users logged in as Jim, will be locked out of the forum because Joe is linked to the same nick - and banned.

The blacklist in options->userbanning only stops specific ip-addresses, which means that if Joe goes to his parents he's able to see the forum as a guest (until he tries to login as Joe again). If you use the built-in blacklist and using parts of the ip the risk is that you ban a bunch of other users that is using the same subnet. :)

[87accordlxi]

Jumping back to this picture. What else is missing here? There's a checkbox that is missing text on my installation too. I manually added the troll_iptracker_deleteselected phrase mentioned on the previous page, and that fixed the delete button text.

[TMM-TT]

Jumping back to this picture. What else is missing here? There's a checkbox that is missing text on my installation too. I manually added the troll_iptracker_deleteselected phrase mentioned on the previous page, and that fixed the delete button text.
Ouch! Another one?

Phrase: troll_search_RBL
Text: Check RBL-Database (Open Proxy)


The two reported phrases should be fixed in next version. Sorry for the misses...:)

[TMM-TT]

Changes for 2.0.4 that will be released as soon the caches has been tested:


Using short-time cache to look for duplicates instead of scanning the whole database (based on cookietimeout)
The above cache is used with the whos online-list too
Added cache for alerts so reports only will be sent on changes in the usernames
2 phrases fixed
Added username exclusions (usernames/userids that won't be blocked)
Fixed the SHOW COLUMNS-problem in iptracker.php



If someone thinks that I have forgotten something, please tell me. :)

[Alfa1]

Joe is banned
The tracker see that Joe is banned and blocks him from the forum
Joe try to log out by cleaning up the cookies
The tracker compares the ip to the banlist and see that there is a guest using Joes ip.. He gets blocked as guest also
Three days later, Joes ip is changed and he can log in as a guest again. He knows that hes banned so he use another nickname instead, that he registered after 'Joe'. He logs in as 'Jim'
The tracker checks the database and find that Joe is linked to Jim
The tracker checks with the userban-list and find that Joe is banned
Jim, Joe and the new ip is now locked out



The blacklisting option means that if any guest is matched with a banned user the ip will be blocked from the forum even before login. If 'Jim' is a shared "troll"-account this also means that all users logged in as Jim, will be locked out of the forum because Joe is linked to the same nick - and banned.

The blacklist in options->userbanning only stops specific ip-addresses, which means that if Joe goes to his parents he's able to see the forum as a guest (until he tries to login as Joe again). If you use the built-in blacklist and using parts of the ip the risk is that you ban a bunch of other users that is using the same subnet. :)
Wouldn't this block out dial up users and users on providers like aol? (banning one AOL IP affects a whole range of AOL IP's, because of the way AOL's network works)

[Alfa1]

After a month of use, I experienced these errors:
Invalid SQL:
SELECT * from vb9_user_troll WHERE userid = '0' AND realip = 'xxx' ORDER BY timestamp DESC LIMIT 1;

MySQL Error : Lost connection to MySQL server during query
Error Number : 2013

Invalid SQL:
SELECT COUNT(*) AS trolledlinks FROM vb9_user_troll WHERE userlink = '0';

MySQL Error : Server shutdown in progress
Error Number : 1053

Invalid SQL:
UPDATE vb9_user_troll SET userlink = '3423' WHERE idx = '153' LIMIT 1;

MySQL Error : Server shutdown in progress
Error Number : 1053

Invalid SQL:
UPDATE vb9_user_troll SET timestamp = 1153908011, date = '%2006-%07-%26', username = 'Unregistered', userid = '0', time = '%11:%Jul:%th', opm = '0' WHERE idx = '37650' LIMIT 1;

MySQL Error : Server shutdown in progress
Error Number : 1053

Invalid SQL:
SELECT userid,realip,timestamp from vb9_user_troll WHERE userid > 0 AND realip = 'xxx' ORDER by timestamp DESC, userid ASC LIMIT 1;

MySQL Error : Server shutdown in progress
Error Number : 1053

Invalid SQL:
UPDATE vb9_user_troll SET timestamp = 1152565757, date = '%2006-%07-%10', username = 'Jack's Seratonin', userid = '0', time = '%22:%Jul:%th', opm = '0' WHERE idx = '21475' LIMIT 1;

MySQL Error : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's Seratonin', userid = '0', time = '%22:%Jul:%th', opm = '0' WHE
Error Number : 1064Note that the username was jack's serotonine. This error occurs many times where usernames include this symbol: '

I've got many more, but they are variations on the same.

[TMM-TT]

Wouldn't this block out dial up users and users on providers like aol? (banning one AOL IP affects a whole range of AOL IP's, because of the way AOL's network works)


Yes, but blocking users is optional and can be turned off. I only block specific usernames myself but the function is there for paranoid admins ;)


Note that the username was jack's serotonine. This error occurs many times where usernames include this symbol: '

Thanks, it should be fixed in next version. Interesting also, is that entry (time = '%22:%Jul:%th'). The time isn't correct there and I use strftime in the plugin. Maybe I should look for some other function for that part (I remember that vbdate didn't work).

(I still cant reproduce the problem you have there with server shutdowns. If someone have a tip there, why the error shows up, I would be very happy.)

[Ski-Whiz]

Ok I have found a bug:

Invalid SQL:
UPDATE user_troll SET timestamp = 1156627674, date = '2006-08-26', username =
'dabear's are #1', userid = '0', time = '17:27:54', opm = '0', imported = 0 WHERE
idx = '9830' AND deleted = 0 AND fqdn = 0 LIMIT 1;

MySQL Error : You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 's are
#1', userid = '0', time = '17:27:54', opm = '0', imported = 0 WHERE idx = ' at line
1
Error Number : 1064
Date : Saturday, August 26th 2006 @ 05:27:54 PM
Script : http://www.xxxxxxx.com/forums/register.php


Now this error is from anyone who uses an apostrophe in their username. The name I tried to register is "dabear's are #1"

This leaves us all open to a SQL attack..

[TMM-TT]

I apologize for the big delay on this hack. Too much things are happening around me so I've lost a lot of time.. Hopefully I can release 2.0.4 as soon as possible with a bunch of fixes.

As a notice - I've noticed (at least on my own server) that the function for showing trolls in the o Who's Online-listing lags me down a little bit sometimes. Probably that function depends on how many users online and how much the tracker has to look for. Without the extra cache there, I think the online-listing won't survive. :)


Now this error is from anyone who uses an apostrophe in their username. The name I tried to register is "dabear's are #1"

This leaves us all open to a SQL attack..

That should hopefully be fixed too now. :)

[TMM-TT]

Because of the apostrophe-thing, I'll release this one now.. Here's the changelog -

2.0.4 [beta]


Using short-time cache to look for duplicates instead of scanning the whole database (based on cookietimeout)
The above cache is used with the whos online-list too
Added cache for alerts so reports only will be sent on changes in the usernames
2 phrases fixed
Added username exclusions (usernames/userids that won't be blocked)
Fixed the SHOW COLUMNS-problem in iptracker.php
Fixed a serious injection problem

[Alfa1]

After upgrading this I get an error. I have tried uninstalling trolltracker and reinstalling it, but then I get this:

Database error in vBulletin 3.5.4:

Invalid SQL:

### INSERT QUERY GENERATED BY fetch_query_sql() ###
INSERT INTO vb4_plugin
(`active`, `executionorder`, `title`, `hookname`, `phpcode`, `product`)
VALUES
('1', '5', 'trolltracker_register', 'global_start', '// Note, this code has no version 1.0.0 released. The first version was private\r\n// and this one is the conversion.\r\n\r\n$dosavetroll = 1;\r\n\r\n// admincp settings\r\n// v1.0->2.0 and some addons\r\n$trollenabled = $vbulletin->options[\'trollenabled\'];\r\n\r\n// Something went wrong!\r\nif (!$_SERVER[\'REMOTE_ADDR\']) {$trollenabled = 0;}\r\n\r\nif ($trollenabled == 1) {\r\n$followtroll = $vbulletin->options[\'followtroll\'];\r\n$trolluseridlookup = $vbulletin->options[\'trolluserid\'];\r\n$trollunreg = $vbulletin->options[\'trollunreg\'];\r\n$trollignoreopm = $vbulletin->options[\'trollignoreopm\'];\r\n$trollwarnopm = $vbulletin->options[\'trollwarnopm\'];\r\n$trollsession = $vbulletin->options[\'trollsession\'];\r\n$trollblock = $vbulletin->options[\'trollblock\'];\r\n$trollblockuser = strtolower($vbulletin->options[\'trollblockuser\']);\r\n$trollexcludeuser = strtolower($vbulletin->options[\'trollexcludeuser\']);\r\n$trollblockusertrig = $vbulletin->options[\'trollblockusertrig\'];\r\n$trolladmin = $vbulletin->options[\'trolladmin\'];\r\n$trollcandelete = $vbulletin->options[\'trollcandelete\'];\r\n$trolluniqueheaders = $vbulletin->options[\'trolluniqueheaders\'];\r\n$trollalertuser = $vbulletin->options[\'trollalertuser\'];\r\n$trollalertforum = $vbulletin->options[\'trollalertforum\'];\r\n$trollalertafter = $vbulletin->options[\'trollalertafter\'];\r\n$trollonlineusers = $vbulletin->options[\'trollonlineusers\'];\r\n$trolldifftime = $vbulletin->options[\'trolldifftime\']*60*60;\r\n$trollalertfrom = $vbulletin->options[\'trollalertfrom\'];\r\n$trollFQDN = $vbulletin->options[\'trollFQDN\'];\r\n$trolluseFQDN = $vbulletin->options[\'trolluseFQDN\'];\r\n$trollautoblockBans = $vbulletin->options[\'trollautoblockBans\'];\r\n\r\n// Clean cacheResolver\r\n$cleanTrollResolver = TIMENOW - 21600;\r\n$vbulletin->db->query(\"DELETE FROM \" . TABLE_PREFIX . \"user_trollresolver WHERE dateline < $cleanTrollResolver\");\r\n\r\n// Who can use tracker...?\r\nglobal $istrolladmin, $istrolldelete, $vbulletin, $db, $vbphrase, $stylevar, $style;\r\n\r\n$trollgroups = explode(\',\',$vbulletin->options[trolladmin]); \r\nif (is_member_of($vbulletin->userinfo, $trollgroups)) $istrolladmin = true;\r\n\r\n// Who can manipulate the tracker?\r\n$trollcandeletegroups = explode(\',\',$vbulletin->options[trollcandelete]); \r\nif (is_member_of($vbulletin->userinfo, $trollcandeletegroups)) $istrolldelete = true;\r\n\r\nif ($istrolladmin == 1) {\r\n $show[\'trolltracker\'] = 1;\r\n if ($trollonlineusers) {$show[\'trolldata\'] = 1;}\r\n }\r\n\r\n/**** Userdata and browser stuff ****/\r\n// Reliable data to register\r\n// Note, addslashes is only needed on ONE $trollusername if we don\'t want a whole lot of dupes!\r\n$trollusername = addslashes($vbulletin->userinfo[\'username\']);\r\n$trolluserid = $vbulletin->userinfo[\'userid\'];\r\n$trollremote = $_SERVER[\'REMOTE_ADDR\'];\r\n$timestamp = TIMENOW;\r\n$trolldate = strftime(\'%Y-%m-%d\');\r\n$trolltime = strftime(\'%H:%M:%S\');\r\n\r\n\r\n// Unreliable data to save, but sometimes, still effective when trolls are using half-anonymous proxies\r\n$forwarded = $_SERVER[\'HTTP_X_FORWARDED_FOR\'];\r\n$via = $_SERVER[\'HTTP_VIA\'];\r\n\r\n// Don\'t overload the server with extra processes if not needed...\r\nif ($trollalertuser OR $trollalertforum OR $trollonlineusers == 1) {\r\n\r\n/**** COUNTING OF USER\'S NICKNAMES BEGIN (IF SELECTED FROM OPTIONS) ****/\r\n// Should only be enabled on request (alerting, blocking and \"self-notifying\")\r\n\r\n/** Check the cache for nicknames, so we don\'t have to query the big one **/\r\n// Clear old entries...\r\n$clearcachetime = TIMENOW - $vbulletin->options[cookietimeout];\r\n$vbulletin->db->query_write(\"DELETE FROM \" . TABLE_PREFIX . \"user_trollcache WHERE timestamp < \" . $clearcachetime);\r\n$trollcache = $vbulletin->db->query_first(\"SELECT * from \" . TABLE_PREFIX . \"user_trollcache WHERE realip = \'$trollremote\'\");\r\nif ($trollcache[nicks]) {\r\n $trollcachenicks = explode(\"\\n\", $trollcache[nicks]);\r\n foreach ($trollcachenicks as $nicks => $trollcachevalue) {\r\n // Must not be empty!\r\n if ($trollcachevalue) {\r\n $trollnicksUID[$vbulletin->userinfo[userid]] = $vbulletin->userinfo[userid];\r\n $trollnicksNAME[strtolower($trollcachevalue)] = strtolower($trollcachevalue);\r\n $trollnicksIP[$trollremote] = $trollremote;\r\n $trolls .= $trollcachevalue . \"\\n\";\r\n $linked++;\r\n }\r\n }\r\n}\r\n\r\n\r\n/** Check the cache for nicknames, so we don\'t have to query the big one --- DONE HERE **/\r\n// If this var is empty, we don\'t have anything cached.\r\n// Start caching only if this is no guest...\r\nif (!$trolls) {\r\n\r\n// This is a special thing that has to do with the guest vs logged when reporting.\r\n$getusernamedata = \"user.username = \'\" . addslashes($vbulletin->userinfo[username]) . \"\' OR \";\r\n// We don\'t want to include not logged in users in this report (because of the unregistered-name).\r\n// So we\'re removing it here instead of looking for unregged usernames in the database.\r\nif ($vbulletin->userinfo[userid] == 0) {$getusernamedata = \"\";}\r\n\r\n$lcountsql = \"SELECT DISTINCT users.userid,users.username,users.userlink,users.r ealip,users.timestamp,users.fo rwarded_for,users.http_via,user.userid\r\nFROM \" . TABLE_PREFIX . \"user_troll AS users\r\nINNER JOIN \" . TABLE_PREFIX . \"user_troll as user\r\nON (users.username = user.username)\r\nOR (users.userlink = user.userid AND users.userlink > 0)\r\nOR (users.realip = user.realip)\r\nOR (users.forwarded_for LIKE \'%$trollremote%\' AND users.forwarded_for != \'\')\r\nOR (users.http_via LIKE \'%$trollremote%\' AND users.http_via != \'\')\r\nWHERE user.userid > 0 AND users.userid > 0\r\nAND (\r\n $getusernamedata user.realip = \'$trollremote\'\r\n )\r\nAND user.deleted = 0\r\nAND users.deleted = 0\r\nAND user.fqdn = 0\r\nAND users.fqdn = 0\r\nGROUP by username\r\nORDER by timestamp DESC\r\n\";\r\n\r\n// Do not include FQDN-names into this listing - FQDN-names are not duplicate usernames, just identifiers\r\n$lcount = $vbulletin->db->query($lcountsql);\r\n\r\n// Collect nicknames to the dupechecker\r\n while ($trollArray = $vbulletin->db->fetch_array($lcount)) {\r\n\r\n // Must not be empty\r\n if ($trollArray[username]) {\r\n $trollnicksUID[$trollArray[userid]] = $trollArray[userid];\r\n $trollnicksNAME[strtolower($trollArray[username])] = strtolower($trollArray[username]);\r\n $trollnicksIP[$trollArray[realip]] = $trollArray[realip];\r\n\r\n if ($trollArray[userid] > 0) {$trolls .= $trollArray[username] . \"\\n\";}\r\n $linked++;\r\n }\r\n }\r\n\r\n\r\n// Fixes ontheirway! (060919)\r\n$trolls = addslashes($trolls);\r\n\r\n// If the own nick is missing, add it.\r\nif (!$trollnicksNAME[strtolower($trollusername)] AND $vbulletin->userinfo[userid] > 0) {$trolls .= $vbulletin->userinfo[username] . \"\\n\";}\r\n\r\n$vbulletin->db->query_write(\"INSERT INTO \" . TABLE_PREFIX . \"user_trollcache (userid, realip, nicks, timestamp) VALUES (\'\" . $vbulletin->userinfo[userid] . \"\', \'$trollremote\', \'$trolls\', \" . TIMENOW . \")\");\r\n\r\n} // End if cachevar\r\n\r\n}\r\n\r\n/**** COUNTING OF USER\'S NICKNAMES FINISH ****/\r\n\r\n\r\n/**** ALERT SECTION BEGIN ****/\r\n if (($trollalertuser OR $trollalertforum) AND $linked >= $trollalertafter AND $trollalertfrom > 0 AND $vbulletin->userinfo[userid] > 0) {\r\n // Used vars: $trollalertuser, $trollalertforum (Update database)\r\n\r\n $trolldupechanges = $vbulletin->db->query_first(\"SELECT * FROM \" . TABLE_PREFIX . \"user_trolldupes WHERE userid = \'\" . $vbulletin->userinfo[userid] . \"\' AND userid > 0\");\r\n\r\n if (!$trolldupechanges[nicklist]) {\r\n // Do not write guests into this list, that will cause trouble on reporting...\r\n if ($vbulletin->userinfo[userid] > 0) {\r\n $vbulletin->db->query_write(\"INSERT INTO \" . TABLE_PREFIX . \"user_trolldupes (userid, nicklist) VALUES (\'\" . $vbulletin->userinfo[userid] . \"\', \'\" . $trolls . \"\')\");\r\n }\r\n }\r\n\r\n // Report on changes...\r\n if ($trolls != $trolldupechanges[nicklist] AND $trolls) {\r\n $doreporttrolls = 1;\r\n\r\n // If there\'s an old nicklist, it\'s time to update here...\r\n if ($trolldupechanges[userid] == $vbulletin->userinfo[userid] AND $vbulletin->userinfo[userid] > 0 AND $trolldupechanges[userid] > 0) {\r\n $vbulletin->db->query_write(\"UPDATE \" . TABLE_PREFIX . \"user_trolldupes SET nicklist = \'$trolls\' WHERE userid = \'\" . $vbulletin->userinfo[userid] . \"\'\");\r\n }\r\n\r\n }\r\n\r\n$blockstatus = \"\";\r\n\r\n// Report normal blockings\r\nif ($linked >= $trollblock AND $trollblock > 0 AND $istrolladmin != 1 AND THIS_SCRIPT != \'iptracker.php\') {$blockstatus = $vbphrase[troll_report_blocked];}\r\n\r\n// Report bans\r\nif ($trollautoblockBans == 1 AND $forcetrollban == 1 AND $istrolladmin != 1 AND THIS_SCRIPT != \'iptracker.php\') {$blockstatus = $vbphrase[troll_report_banned];}\r\n\r\n if ($doreporttrolls == 1 AND $linked >= $trollalertafter) {\r\n $trollqueryuser = fetch_userinfo($trollalertfrom);\r\n\r\n $trollpm =& datamanager_init(\'PM\', $vbulletin, ERRTYPE_SILENT);\r\n // Send PM if options are set for PM\r\n if ($trollalertuser) {\r\n $trollpm->set(\'fromuserid\', $trollqueryuser[userid]);\r\n $trollpm->set(\'fromusername\', $trollqueryuser[username]);\r\n $trollpm->set_info(\'reciept\', false);\r\n $trollpm->set_info(\'savecopy\', false);\r\n $trollpm->set(\'title\', construct_phrase($vbphrase[troll_alertsubject], $vbulletin->userinfo[username]));\r\n $trollpm->set(\'message\', construct_phrase($vbphrase[troll_alertmessage], $vbulletin->userinfo[username], $trolls, $blockstatus));\r\n $trollpm->set_recipients($trollalertuser, $trollqueryuser[permissions]);\r\n $trollpm->set(\'dateline\', TIMENOW);\r\n $trollpm->save();\r\n }\r\n\r\n if ($trollalertforum) {\r\n $t_smilies = 1;\r\n $t_visible = 1;\r\n require_once(DIR . \'/includes/class_dm_threadpost.php\');\r\n $trollreport = new vB_DataManager_Thread_FirstPost($vbulletin, ERRTYPE_STANDARD);\r\n $trollreport->do_set(\'forumid\', $trollalertforum);\r\n $trollreport->do_set(\'userid\', $trollqueryuser[\'userid\']);\r\n $trollreport->do_set(\'username\', $trollqueryuser[\'username\']);\r\n $trollreport->do_set(\'title\', construct_phrase($vbphrase[troll_alertsubject], $vbulletin->userinfo[username]));\r\n $trollreport->do_set(\'pagetext\', construct_phrase($vbphrase[troll_alertmessage], $vbulletin->userinfo[username], $trolls, $blockstatus));\r\n $trollreport->do_set(\'allowsmilie\', $t_smilies);\r\n $trollreport->do_set(\'visible\', $t_visible);\r\n $sendtrollreport = $trollreport->save();\r\n \r\n require_once(DIR . \'/includes/functions_databuild.php\');\r\n build_forum_counters($trollalertforum);\r\n }\r\n }\r\n\r\n // Update database, so we won\'t get repeating in the forum and PM\'s\r\n $vbulletin->db->query_write(\"UPDATE user_troll SET alert = \" . TIMENOW . \" WHERE userid = \'\" . $vbulletin->userinfo[userid] . \"\' AND deleted = 0 ORDER by alert DESC LIMIT 1\");\r\n\r\n }\r\n/**** ALERT SECTION FINISH ****/\r\n\r\n\r\n/**** BLOCK TROLLS ON REQUEST BEGIN ****/\r\n// Check for special blocks start\r\n$trollblocknames = explode(\"\\n\", preg_replace(\"[\\r]\", \"\", strtolower($vbulletin->options[\'trollblockuser\'])));\r\n$trollexcludenames = explode(\"\\n\", preg_replace(\"[\\r]\", \"\", strtolower($vbulletin->options[\'trollexcludeuser\'])));\r\n\r\n foreach ($trollexcludenames as $texclude => $excludenames) {$exclude[strtolower($excludenames)] = strtolower($excludenames);}\r\n\r\n// Want to block banned users from forum? Array it here...\r\nif ($trollautoblockBans == 1) {\r\n $trollbans = $vbulletin->db->query(\"SELECT * from \" . TABLE_PREFIX . \"userban\");\r\n\r\n while ($trollban = $vbulletin->db->fetch_array($trollbans)) {\r\n $realuserBAN = fetch_userinfo($trollban[userid]);\r\n if ($trollautoblockBans == 1) {\r\n // Force ban if any matches, but only if user is not admin...\r\n if (strtolower($trollnicksUID[$trollban[userid]]) == strtolower($trollban[userid]) AND $istrolladmin != 1) {\r\n $trollbanreason = $trollban[\'reason\'];\r\n if ($trollban[\'liftdate\']) {\r\n $trollbandate = vbdate($vbulletin->options[\'dateformat\'] . \', \' . $vbulletin->options[\'timeformat\'], $trollban[\'liftdate\']);\r\n } else {\r\n $trollbandate = $vbphrase[\'never\'];\r\n }\r\n $forcetrollban = 1;\r\n }\r\n if (strtolower($trollnicksNAME[$realuserBAN[username]]) == strtolower($realuserBAN[username]) AND $istrolladmin != 1) {\r\n $trollbanreason = $trollban[\'reason\'];\r\n if ($trollban[\'liftdate\']) {\r\n $trollbandate = vbdate($vbulletin->options[\'dateformat\'] . \', \' . $vbulletin->options[\'timeformat\'], $trollban[\'liftdate\']);\r\n } else {\r\n $trollbandate = $vbphrase[\'never\'];\r\n }\r\n $forcetrollban = 1;\r\n }\r\n }\r\n\r\n }\r\n if (!$trollbanreason) {$vbphrase[\'none\'];}\r\n}\r\n// End banblock array\r\n\r\nforeach ($trollblocknames as $trollers) {\r\n $counttrollblock++;\r\n if ($trollers > 0 && $vbulletin->userinfo[\'userid\'] == $trollers) {$dotrollblock = 1;}\r\n if ($trollers < 1 && strtolower($vbulletin->userinfo[\'username\']) == strtolower($trollers)) {$dotrollblock = 1;}\r\n\r\n // Linkmatching - Block the current logged in user if there\'s a match with the userlist in options.\r\n // This includes IP from v2.0.2 and up\r\n if (strtolower($trollers) == strtolower($trollnicksUID[$trollers])) {$dotrollblock = 1;}\r\n if (strtolower($trollers) == strtolower($trollnicksNAME[$trollers])) {$dotrollblock = 1;}\r\n if (strtolower($trollers) == strtolower($trollnicksIP[$trollers])) {$dotrollblock = 1;}\r\n}\r\n\r\n// If there is a count in the userlist, the blocklist is active. Of there\'s matches on the current\r\n// logged in userid/username, check how many userlinks we find. Then block if everything is ok\r\nif ($counttrollblock > 0 AND $dotrollblock == 1 AND $linked >= $trollblockusertrig) {\r\n $trollblock = $trollblockusertrig;\r\n }\r\n// Check for special blocks end\r\n\r\nif (strtolower($trollusername) == strtolower($exclude[$trollusername]) OR $vbulletin->userinfo[userid] == $exclude[$trolluserid]) {\r\n $forcetrollban = 0;\r\n $linked = 0;\r\n }\r\n\r\n/** BLOCKING AND BANNING BEGIN **/\r\n// Protect admins from being blocked\r\nif ($trollautoblockBans == 1 AND $forcetrollban == 1 AND $istrolladmin != 1 AND THIS_SCRIPT != \'iptracker.php\') {\r\n eval(\'$headinclude = \"\' . fetch_template(\'headinclude\') . \'\";\');\r\n eval(standard_error(fetch_error(\'nopermission_ban ned\', $trollbanreason, $trollbandate)));\r\n }\r\n\r\nif ($linked >= $trollblock AND $trollblock > 0 AND $istrolladmin != 1 AND THIS_SCRIPT != \'iptracker.php\') {\r\n $vbulletin->userinfo[\'badlocation\'] =1;\r\n $errormessage = construct_phrase($vbphrase[\'trollerror_toomanynicks\'], $linked);\r\n eval(\'$headinclude = \"\' . fetch_template(\'headinclude\') . \'\";\');\r\n eval(\'$header = \"\' . fetch_template(\'header\') . \'\";\');\r\n eval(\'$footer = \"\' . fetch_template(\'footer\') . \'\";\');\r\n eval(\'print_output(\"\' . fetch_template(\'STANDARD_ERROR\') . \'\");\');\r\n}\r\n/** BLOCKING AND BANNING FINISH **/\r\n\r\n\r\n/**** BLOCK TROLLS ON REQUEST FINISH ****/\r\n\r\n\r\n\r\n/******** CONTINUE IF THE USER ISN\'T BLOCKED ********/\r\n\r\n\r\n\r\n/**** RBL MONITORING BEGIN ****/\r\n// Is the rbl monitor installed?\r\nif ($rblInstalled) {\r\n // Are we allowed to look for proxies?\r\n if ($trollignoreopm == 1 OR $trollwarnopm == 1) {\r\n // Just return the value returned from the monitor\r\n $trollIsOPM = rbl_livecheck(1, $trollremote);\r\n }\r\n } else {\r\n // If nothing is correct, just say 0\r\n $trollIsOPM = 0;\r\n }\r\n/**** RBL MONITORING FINISH ****/\r\n\r\n\r\n\r\n\r\n\r\n/**** FQDN RESOLVE BEGIN ****/\r\n if ($trollFQDN AND $trolluseFQDN == 1) {\r\n // First, grab the names and split up with userid\r\n $trollFQnames = explode(\"\\n\", preg_replace(\"[\\r]\", \"\", $trollFQDN));\r\n foreach ($trollFQnames as $t_FQ => $trollresolve) {\r\n $trollrescache = $vbulletin->db->query(\"SELECT * from \" . TABLE_PREFIX . \"user_trollresolver WHERE host = \'$trollresolve\'\");\r\n $trollhost = array();\r\n $countcache = -1;\r\n // Can this host be found in the cache?\r\n while ($rescache = $vbulletin->db->fetch_array($trollrescache)) {$trollhost[$countcache++] = $rescache[ip];}\r\n\r\n // If nothing found in cache, start resolve the host\r\n $docache = 0;\r\n if ($countcache < 1) {\r\n $trollhost = gethostbynamel($trollresolve); // Array for full resolving\r\n $docache = 1; // Also remember the host as register-in-cache\r\n }\r\n\r\n // Now start looking up all resolved ips (yes, we support multiple ips under one FQDN)\r\n foreach ($trollhost as $trollip) {\r\n // If we have a match, save it with the userid where it belong\r\n if ($trollremote == $trollip) {$FQDNresolved[$trollresolve] = true;}\r\n // Don\'t forget to cache this host so we don\'t have to resolve it the next time\r\n if ($docache == 1) {\r\n $vbulletin->db->query_write(\"INSERT INTO \" . TABLE_PREFIX . \"user_trollresolver (host, ip, dateline) VALUES (\'$trollresolve\', \'$trollip\', \" . TIMENOW . \")\");\r\n }\r\n }\r\n\r\n }\r\n\r\n // Start fixing with the trolldb...\r\n foreach ($FQDNresolved as $FQDN => $Fvalue) {\r\n // IP ($trollremote), user ($Fvalue), FQDN ($FQDN)\r\n\r\n $realuserFQDN = fetch_userinfo($Fvalue);\r\n $findhost = $vbulletin->db->query_first(\"SELECT * FROM \" . TABLE_PREFIX . \"user_troll WHERE realip = \'$trollremote\' AND username = \'\" . $FQDN . \"\'\");\r\n\r\n // If this host isn\'t registered to the tracker, it\'s time to register (or update) it now, but do not interfere with the regular data (that\'s why we use the fqdn-field here!)\r\n\r\n if (!$findhost[idx]) {\r\n $vbulletin->db->query_write(\"INSERT INTO \" . TABLE_PREFIX . \"user_troll (userid, realip, forwarded_for, username, timestamp, http_via, date, time, opm, fqdn) VALUES (\'$trolluserid\', \'$trollremote\', \'$forwarded\', \'$FQDN\', \'$timestamp\' , \'$via\', \'$trolldate\', \'$trolltime\', \'$trollIsOPM\', \'1\')\");\r\n } else {\r\n $vbulletin->db->query_write(\"UPDATE \" . TABLE_PREFIX . \"user_troll SET timestamp = $timestamp, date = \'$trolldate\', time = \'$trolltime\', opm = \'$trollIsOPM\', imported = 0 WHERE idx = \'\" . $findhost[idx] . \"\' AND deleted = 0 AND fqdn = 1 LIMIT 1\"); }\r\n\r\n }\r\n }\r\n/**** FQDN RESOLVE FINISH ****/\r\n\r\n\r\n\r\n\r\n\r\n/**** STORAGE PREPARE BEGIN ****/\r\n// How to check the database when inserting new entries\r\nif ($followtroll == 1) \r\n {\r\n $trollquery = \"WHERE realip = \'$trollremote\' AND deleted = 0 AND fqdn = 0 ORDER BY timestamp DESC\";\r\n } else {\r\n $trollquery = \"WHERE userid = \'$trolluserid\' AND realip = \'$trollremote\' AND deleted = 0 AND fqdn = 0 ORDER BY timestamp DESC\";\r\n // Switch lookup type, by request\r\n if ($trolluseridlookup == 0) {\r\n $trollquery = \"WHERE username = \'\" . $trollusername . \"\' AND realip = \'$trollremote\' AND deleted = 0 AND fqdn = 0 ORDER BY timestamp DESC\";\r\n }\r\n }\r\n/**** STORAGE PREPARE FINISH ****/\r\n\r\n\r\n\r\n/**** STORAGE DATA COLLECTING BEGIN ****/\r\n$ftroll = $vbulletin->db->query_first(\"SELECT * from \" . TABLE_PREFIX . \"user_troll $trollquery LIMIT 1\");\r\n\r\n$usedtroll = $ftroll[\'realip\'];\r\n$trollid = $ftroll[\'idx\'];\r\n$trolluser = $ftroll[\'userid\'];\r\n\r\n$trollVIA = $ftroll[\'http_via\'];\r\n$trollFOR = $ftroll[\'forwarded_for\'];\r\n\r\nif ($forwarded != $trollFOR OR $via != $trollVIA) {$trollheadermismatch = 1;}\r\n\r\n// If there is any userid found in database, we should check the first nick used by looking up the host and the userlink data\r\nif ($trolluser > 0 AND $trollremote != \'\') {\r\n $firsttroll = $vbulletin->db->query_first(\"SELECT userid,realip,timestamp from \" . TABLE_PREFIX . \"user_troll WHERE userid > 0 AND realip = \'$trollremote\' AND deleted = 0 AND fqdn = 0 ORDER by timestamp DESC, userid ASC LIMIT 1\");\r\n $firsttrollid = $firsttroll[\'userid\'];\r\n\r\n $followlink = $vbulletin->db->query_first(\"SELECT userid,userlink from \" . TABLE_PREFIX . \"user_troll WHERE userid = \'$trolluserid\' AND userlink > 0 AND deleted = 0 AND fqdn = 0 ORDER by userlink ASC LIMIT 1\");\r\n\r\n // Did it? Then replace this id with the first found in db\r\n if ($followlink[\'userlink\'] != $firsttrollid) {$firsttrollid = $followlink[\'userlink\'];}\r\n\r\n // Save data about any user who possibly used this ip first\r\n if ($firsttrollid > 0) {\r\n $vbulletin->db->query_write(\"UPDATE \" . TABLE_PREFIX . \"user_troll SET userlink = \'$firsttrollid\' WHERE idx = \'$trollid\' AND deleted = 0 AND fqdn = 0 LIMIT 1\");\r\n }\r\n}\r\n/**** STORAGE DATA COLLECTING FINISH ****/\r\n\r\n\r\n\r\n/**** SESSION STORAGE BEGIN ****/\r\n// Force updating of the session-table on request (this is stupid!)\r\nif ($trollsession == 1 AND $trollremote != \'\') {\r\n\r\n // Lookup latest used username from specific IP\r\n $lasttroll = $vbulletin->db->query_first(\"SELECT * from \" . TABLE_PREFIX . \"user_troll WHERE userid > 0 AND realip = \'$trollremote\' AND deleted = 0 ORDER BY timestamp DESC LIMIT 1\");\r\n $trolluser = $lasttroll[\'userid\'];\r\n\r\n if ($trolluser) {\r\n $vbulletin->db->query_write(\"UPDATE \" . TABLE_PREFIX . \"session SET userid = \'$trolluser\', lastactivity = $timestamp WHERE host = \'$trollremote\'\");\r\n // If this is true, troller is already found and forced into sessions...\r\n $dosavetroll = 0;\r\n }\r\n\r\n}\r\n/**** SESSION STORAGE FINISH ****/\r\n\r\n\r\n// Do not register not logged in users on request\r\nif ($trollunreg == 0 AND $trolluserid < 1) {$dosavetroll = 0;}\r\n\r\n// Do not save RBL\'s on request\r\nif ($trollignoreopm == 1 AND $trollIsOPM == 1) {$dosavetroll = 0;}\r\n\r\n// Changes in headers?\r\nif ($trollheadermismatch == 1 AND $trolluniqueheaders == 1) {$dosavetroll = 1;}\r\n\r\n\r\n\r\n/**** TROLLDB STORAGE BEGIN ****/\r\nif ($dosavetroll == 1) {\r\n\r\n if (!$usedtroll) {\r\n // If the user wasn\'t found in the database, we will add it...\r\n $vbulletin->db->query_write(\"INSERT INTO \" . TABLE_PREFIX . \"user_troll (userid, realip, forwarded_for, username, timestamp, http_via, date, time, opm) VALUES (\'$trolluserid\', \'$trollremote\', \'$forwarded\', \'\" . $trollusername . \"\', $timestamp, \'$via\', \'$trolldate\', \'$trolltime\', \'$trollIsOPM\')\");\r\n } else {\r\n // Changes will make the database change selected entry to not imported\r\n // If the user was found in the database, we won\'t duplicate it - instead, we\'re updating the latest login\r\n $vbulletin->db->query_write(\"UPDATE \" . TABLE_PREFIX . \"user_troll SET timestamp = $timestamp, date = \'$trolldate\', username = \'\" . $trollusername . \"\', userid = \'$trolluserid\', time = \'$trolltime\', opm = \'$trollIsOPM\', imported = 0 WHERE idx = \'$trollid\' AND deleted = 0 AND fqdn = 0 LIMIT 1\");\r\n }\r\n}\r\n/**** TROLLDB STORAGE FINISH ****/\r\n\r\n\r\n\r\n} // End of enable check... Turns this plugin completely off or on...', 'trolltracker');

MySQL Error : Unknown column 'executionorder' in 'field list'
Error Number : 1054
Date : Tuesday, September 19th 2006 @ 05:42:28 PM

What now?

[TMM-TT]

Sorry. I exported the hack from vB 3.6 so "executionorder" is an unkown database-field for 3.5. I had the same problem when I forgot to remove that field. Fixed now ;)

[Alfa1]

Many thanks.

Can you please add a small explanation of the functions. I am confused with this:
When I enter a username, select resolve IP address, the tracker finds an amount of entries, but does not display them.

[TMM-TT]

Many thanks.

Can you please add a small explanation of the functions. I am confused with this:
When I enter a username, select resolve IP address, the tracker finds an amount of entries, but does not display them.


How do you mean with does not display? Do you have any screenshot to show as an example?

[bada_bing]

I currently have another duplicate account tracker called dual account tracker that was coded from someones on this site but really like the functions of this hack better. Any issues with running both?

[Alfa1]

I mean that the page does not display the entries of the member, but does display how many entries there are.
I have to add that I did not upload the trollpost.php as i do not want to import IP's from older posts. I also did not upload the changelog, as I have no clue what this is for. I did not see any instructions about what to do with this.

[TMM-TT]

I currently have another duplicate account tracker called dual account tracker that was coded from someones on this site but really like the functions of this hack better. Any issues with running both?


Is it the hack called AE Multiple Login Detector? In that case - I use both. :)


I mean that the page does not display the entries of the member, but does display how many entries there are.

Ahh, the amount it shows is the total amount of registered usernames/ip the database has registered since the install. Maybe the nickname you're looking for hasn't been "catched" yet?


I also did not upload the changelog, as I have no clue what this is for. I did not see any instructions about what to do with this.

Changelog is just a textfile with a list of bugfixes/changes made to the tracker and a to do-list.

[Alfa1]

Ahh, the amount it shows is the total amount of registered usernames/ip the database has registered since the install. Maybe the nickname you're looking for hasn't been "catched" yet?

Yes, it has. I have looked this member up many times before the upgrade. But it seems like all data was lost on the upgrade and now the tracker has started again with adding entries. To bad, but no disaster.

On another note:
What would really help my moderators solving the double accounts is; In the post which warns for multiuser accounts

1. get the usernames linked to the profile like the AE detector does.
2. have the IP address which connects the two member accounts listed in the post
3. include both usernames in the topic title of the post

Any chance on this?

[Alfa1]

Because it is very important to know wether the user is on dial up/proxy or not, it would be handy to see how many other IP's have been used by the member. If these are a lot, then it is reasonably sagfe to assume that it concerns a proxy/dial up and is not the same member.
I'd like to request in the post:

4. amount of IP's used.
5. link for admin to the iptracker results for the found user.

[TMM-TT]

Your requests can be fixed, yes. :)

The first one - act like AE does - is already added, by the way.


I'm also thinking of making ipv6-support to the ipaddress field (by just changing the max lenthg of that field) since I use ipv6 myself. :)

[Alfa1]

The IP tracker gives us many reports about members on the same proxy (like AOL and such). How do I prevent the tracker from listing members on proxy?

[TMM-TT]

The IP tracker gives us many reports about members on the same proxy (like AOL and such). How do I prevent the tracker from listing members on proxy?


If you use the tracker together with the OPM-hack theres an option called "Ignore OPM-users" you can use. Described:

If the OPM-monitor is installed, use this option to avoid register users using proxies (Keeping track of users that using multiple accounts from anonymous proxies might give unreliable results in the tracker). OPM Monitor is required for this option

[redspider]

I getting this error did I miss something?:


Database error in vBulletin 3.5.4:

Invalid SQL:
SELECT user1.username AS un1,user2.username as un2,user1.realip,user2.realip,user1.fqdn,user1.del eted,user1.imported,user2.deleted,user1.userid,use r2.userid
FROM user_troll AS user1
LEFT JOIN user_troll AS user2
ON user2.realip = user1.realip
WHERE user1.fqdn = 0
AND user1.deleted = 0
AND user2.deleted = 0
AND user1.userid > 0
AND user2.userid > 0

AND ();

MySQL Error : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 11
Error Number : 1064
Date : Monday, September 25th 2006 @ 06:22:33 PM
Script : http://www.domaincom/forums/iptracker.php?q=&depth=&q_t=username&resolver=true&opm=1&Submit=Ask
Referrer :
IP Address : xx.xxx.66.1
Username : Unregistered
Classname : vb_database

[Alfa1]

If the OPM-monitor is installed, use this option to avoid register users using proxiesDo i understand it correct that you mean avoid the tracker reporting users using proxy, not that the OPM avoids users using proxy registering at my forum?

[TMM-TT]

Do i understand it correct that you mean avoid the tracker reporting users using proxy, not that the OPM avoids users using proxy registering at my forum?
Hmm, I think you have a point there... The thing I told you about just disables tracking of proxyusers. You just want to skip reports of the proxied ones. That type of function doesn't exist yet. I'll fix that too :)

[Alfa1]

Thanks. I can see this software becoming some really good stuff.
Although somewhat off topic. Is there a list of proxy providers (AOL et al) somewhere? When banning a member on such providers, one also bans a bunch of others on the same provider. Such a list would come in handy. In the past I have even accidentally IP banned one of my major moderators, which gave me a lot of explaining to do. vBulletin should have a function to disable proxyuser banning.

Side note 2: the errors I had before only seem to exist on shared hosting. I do not have them on dedicated hosting. Not even on a low spec one.

[TMM-TT]

MySQL Error : Lost connection to MySQL server during query
Error Number : 2013


I'm just curious about this - are you using innodb and still get those errors?

[Alfa1]

I have no clue what innodb is, but i do not have the errors anymore.

The tracker still doesn't work for me: the page does not display the entries of the member

[Hollanda]

I get this error if i'm trying to register with an anonnimous proxy

Database error in vBulletin 3.6.0:

Invalid SQL:
INSERT INTO proxycache (ip, listed, dateline) VALUES ('208.113.133.5', 0, 1160420400);

MySQL Error : Duplicate entry '208.113.133.5' for key 1
Error Number : 1062
Date : Monday, October 9th 2006 @ 09:00:02 PM
Script : http://forum.xxxxxxx.nl:80/
Referrer : http://forum.xxxxxxx.nl
IP Address : 208.113.133.5
Username : Niet geregistreerd (not registered)
Classname : vb_database

[DannyBoy8406]

Ok, this seems to work for me for so long but then it gives me a DB error:


Database error in vBulletin 3.5.4:

Invalid SQL:
UPDATE user_troll SET alert = 1161039009 WHERE userid = '534' AND deleted = 0 ORDER by alert DESC LIMIT 1;

MySQL Error : Table 'threetas_vbforums.user_troll' doesn't exist
Error Number : 1146
Date : Monday, October 16th 2006 @ 06:50:09 PM
Script : http://www.three-tasks.com/subscription.php
Referrer : http://www.three-tasks.com/subscription.php?do=addsubscription&f=230
IP Address : -----------
Username : Dark Arts
Classname : vB_Database

[TMM-TT]

Ok, this seems to work for me for so long but then it gives me a DB error:

MySQL Error : Table 'threetas_vbforums.user_troll' doesn't exist

Interesting.. Doesn't exit? Try reinstalling the hack :)

[Hollanda]

Another question: If i put users/userid's in de "Specific users to exclude" I still get the multiple account detection pm. Did I something wrong?

[TMM-TT]

Another question: If i put users/userid's in de "Specific users to exclude" I still get the multiple account detection pm. Did I something wrong?
No, that's probably on me. I'll check it out :)

[Hollanda]

No, that's probably on me. I'll check it out :)

Okay thank you, I'll be waiting for it :)

The problem is caused bij two users on the same ip.

[Quiz]

Yes, but blocking users is optional and can be turned off. I only block specific usernames myself but the function is there for paranoid admins ;)

Hi,

I have a question. I tried to turn off the user-blocking function. I just want to recive a message in a forum.

I get the messagge, but the user gets blocked automaticly all the time... can you help me?

greetings
Quiz

[Morning Glory]

This banned everyone this morning. I imported the product-trolltracker_beta_blockdisabled.xml and then tried to overwrite it with the original product and get this error. Now I have no product showing and can't import either of them.


Invalid SQL:
CREATE TABLE user_troll (
`idx` int(11) NOT NULL auto_increment,
`userid` int(11) NOT NULL default '0',
`username` varchar(128) NOT NULL default '',
`userlink` int(11) NOT NULL default '0',
`forwarded_for` varchar(128) NOT NULL default '',
`browser` varchar(255) NOT NULL default '',
`http_via` varchar(128) NOT NULL default '',
`realip` varchar(15) NOT NULL default '',
`owner` varchar(128) NOT NULL default '',
`timestamp` int(11) NOT NULL default '0',
`date` date NOT NULL default '0000-00-00',
`time` time NOT NULL default '00:00:00',
`external` int(11) NOT NULL default '0',
`opm` int(11) NOT NULL default '0',
`imported` int(11) NOT NULL default '0',
PRIMARY KEY (`idx`),
KEY `uscan` (`username`,`realip`)
);
MySQL Error : Table 'user_troll' already exists
Error Number : 1050

[geppeto]

Interesting.. Doesn't exit? Try reinstalling the hack :)

I get the same error. Reinstall de product and the same...

nvalid SQL:
UPDATE user_troll SET alert = 1163771597 WHERE userid = '13' AND deleted = 0 ORDER by alert DESC LIMIT 1;

MySQL Error : Table 'xxxxx_forum.user_troll' doesn't exist Error Number : 1146

One question... my prefix is not in xxxx_forum.(prefix)user_troll, I don't know if this is the problem.

Regards

[Alfa1]

To the posters above: Are you shared hosting? If yes: see my posts in earlier in this thread. All errors sustained until I moved to dedicated hosting. That took care of the problems.

Ahh, the amount it shows is the total amount of registered usernames/ip the database has registered since the install. Maybe the nickname you're looking for hasn't been "catched" yet?
The same problem persists. For some obscure reason the tracker lists certain users and does not catch others. Tracking for members which have been posting for some time does not give the expected results, while I can trace them in the adminCP. For this reason I am considering installing version 2.0.0 again, since that did allow me to track all members.

Hmm, I think you have a point there... The thing I told you about just disables tracking of proxyusers. You just want to skip reports of the proxied ones. That type of function doesn't exist yet. I'll fix that too :)
Is there any update for this? Without this feature you simply get swamped in useless reports of dialup/AOL/proxy users.

If you use the tracker together with the OPM-hack theres an option called "Ignore OPM-users" you can use. Described:

If the OPM-monitor is installed, use this option to avoid register users using proxies (Keeping track of users that using multiple accounts from anonymous proxies might give unreliable results in the tracker). OPM Monitor is required for this option

Looking at the OPM hack and the errors reported, puts the fear in me. I have been trying the troll tracker out from the beginning, as I would love to have this functionality. Some things are just not working yet. I have much respect for the great effort that you have put into both hacks. The following is meant as constructive and not negative in any way:

I think it would be a great idea if you would get help from an advanced vbulletin coder, to work out the issues with both hacks. I really hope that there is someone willing to review the code and work out the bugs with you. This is a very valuable hack, but it needs a full revision.

[fxs158]

can you keep people from accessing iptracker.php? Is there a way to get the entries for the last day or so?

[white_2kgt]

Is it possible for this to just log those 'trolls' and not block them? I installed this and within a few hrs I started getting emails from people that live together (husbands/wifes/roommates) saying they could get to the forum because they are on 2 usersnames. I just want it to log but not block anyone.

[WATRD]

Ok, this seems to work for me for so long but then it gives me a DB error:

Same thing here...

Database error in vBulletin 3.6.0:

Invalid SQL:
UPDATE user_troll SET alert = 1167326811 WHERE userid = '3' AND deleted = 0 ORDER by alert DESC LIMIT 1;

MySQL Error : Table 'xxxxxxx.user_troll' doesn't exist
Error Number : 1146
Date : Thursday, December 28th 2006 @ 10:26:51 AM
Script : http://www.xxxxxxx.com/forum/
Referrer : http://www.xxxxxxx.com/home/
IP Address : xx.xx.xx.xx
Username : xxxxxxx
Classname : vb_database

[WATRD]

Same thing here...

It looks like it decided I was a troll, then did something that caused me to get a database error. It looks like everyone it determines to be a troll was getting that DB error until I disabled the product.

I have "Block Trolls" set to "0" with version "trolltracker-2.0.4" and my user id is included in the "Specific users to exclude" section.

Any suggestions?

[WATRD]

I get the same error. Reinstall de product and the same...



One question... my prefix is not in xxxx_forum.(prefix)user_troll, I don't know if this is the problem.

Regards

Same here. I see that it installed to the DB using the table prefix of "vb_", but for some reason when it runs, it's using the DB name.

[Alfa1]

My forum has become slower and slower until it started crashing(time outs) daily. Then I tried many things, but nothing helped. Then I deactivated the troll tracker and my site was as speedy as ever again. There are quite a lot of entries in the tracker. Is it possible that the amount of entries caused my site to slow down so much that it crashed?

[dutchbb]

My forum has become slower and slower until it started crashing(time outs) daily. Then I tried many things, but nothing helped. Then I deactivated the troll tracker and my site was as speedy as ever again. There are quite a lot of entries in the tracker. Is it possible that the amount of entries caused my site to slow down so much that it crashed?
Looks like I'm in the same boat. I got high loads yesterday and today the load was over 200, shortly after I got a database error. I disabled the script and everything was back to normal...

[parabat]

Hi

Can anyone tell me why I'm getting this error when I run a general report?

Warning: preg_replace(): Compilation failed: nothing to repeat at offset 0 in /iptracker.php on line 9

I'm running vB3.6.4

-Parabat

[Keyser S?ze]

ok my site went down for a few hours, qwhen it came back up i was getting this error

Database error in vBulletin 3.6.4:

Invalid SQL:
SELECT * from user_troll WHERE userid = '2230' AND realip = '71.161.49.82' AND deleted = 0 AND fqdn = 0 ORDER BY timestamp DESC LIMIT 1;

MySQL Error : Can't open file: 'user_troll.MYI' (errno: 145)
Error Number : 1016
Date : Monday, January 15th 2007 @ 09:11:38 AM
Script : http://www.emuasylum.com/forums/index.php
Referrer :
IP Address : 71.161.49.82
Username : Ice
Classname : vb_database

any suggestions?

[UncoderMom]

Is there a way to stop this from counting aol proxie users from being marked as trolls?

[Sadie Frost]

ok my site went down for a few hours, qwhen it came back up i was getting this error

Database error in vBulletin 3.6.4:

Invalid SQL:
SELECT * from user_troll WHERE userid = '2230' AND realip = '71.161.49.82' AND deleted = 0 AND fqdn = 0 ORDER BY timestamp DESC LIMIT 1;

MySQL Error : Can't open file: 'user_troll.MYI' (errno: 145)
Error Number : 1016
Date : Monday, January 15th 2007 @ 09:11:38 AM
Script : http://www.emuasylum.com/forums/index.php
Referrer :
IP Address : 71.161.49.82
Username : Ice
Classname : vb_database

any suggestions?
Try running a repair on your tables from the admincp. I know I've had those MYI errors before and I *think* that's what I did to fix them but you might just want to search on vb.com about it to be sure. :)

As for the whole user_troll file not existing error, I've been trying to help DannyBoy8406 with fixing this, and he too has a table prefix. So it looks like the hack is not calling the table prefix from the config file properly. Not sure how to fix it other than adding it manually. Anyone have any ideas?

[Sadie Frost]

Okay I think I've got it. On line 273 of the xml file

$vbulletin->db->query_write("UPDATE user_troll SET alert = " . TIMENOW . " WHERE userid = '" . $vbulletin->userinfo[userid] . "' AND deleted = 0 ORDER by alert DESC LIMIT 1");

Update user_troll is missing the table prefix. :)

[swatkins]

With this mod track all the IP's a user has used before? Is it stable enough to install now?

[Ignicoccus]

Hi,

I installed the Mod today. It began showing the expected data in "Who is online". I disabled "Block Trolls" (value 0). But some AOL useres were locked out of the forum. I could not see an option to disable this, any hints ?

There were four useres from AOL who go a message like this :

iptracker v2.0.4
225 entries in tracker
Your ip is x.x.x.x.
You have been using the following nicks:
nick1
nick2
nick3
nick4


(IP and nicknames have been changend)

Thy were blocked out, but the system did not send a PN to inform me.

I disabled the mod to let these users back in, any way to fix this problem ?

[Harley-Heaven]

Just wanted to say "THANKS!!", this worked like a dream!!

I have two ID's on my board - it caught me...... plus it alerted us to two husband and wife members, which we knew about. Gives us a real comfort feeling.

Excellent mod!! Great work.

[cheekymonk3y]

My forum has become slower and slower until it started crashing(time outs) daily. Then I tried many things, but nothing helped. Then I deactivated the troll tracker and my site was as speedy as ever again. There are quite a lot of entries in the tracker. Is it possible that the amount of entries caused my site to slow down so much that it crashed?
pity i never read this thread before having my forum totalled for a whole 30 hours. The exact same thing as the above user happened with me, and i never noticed the slowdown after i installed, but members began picking it up after a few hours. The forums were creeping and activity was 95% down, anyway, i eventually figured things out and uninstalled and everything went hyper. The reason for the slowdown i think is because the mod interrupts the page load. Ftp/downloads/non-vb pages worked perfectly.

[TMM-TT]

Yes, I've discovered the bottlenecks myself with this hack after a quite long period now, and due to IRL-delays I haven't been able to answer for a long time. I'm also thinking of making a brand new version in a (hopefully) close future, that should work better than this..umm..crap. :p

I'm sorry for this and all my "lagging"..

[valdet]

Does this work on vB 3.6.5 too. I need this bad..

[Alfa1]

TTM: My database is incredibly huge now. I'd like to get rid of the troll tracker until you have created something better.

Can you please tell me how to un-install this in a way so that my database goes back to normal? Please be specific. I am very scared to do something wrong.

[TMM-TT]

TTM: My database is incredibly huge now. I'd like to get rid of the troll tracker until you have created something better.

Can you please tell me how to un-install this in a way so that my database goes back to normal? Please be specific. I am very scared to do something wrong.

I don't think there will be no problems... Actually, I think the only thing you need to do is to disable the hack from the product manager. If you uninstall it, the table should be dropped, but in case of any possible problems I'd prefer to disable the hack first. After disabling, you can do whatever you want with the table :)

[Alfa1]

Which tables should I delete?

[Karolis_S]

Yes, I've discovered the bottlenecks myself with this hack after a quite long period now, and due to IRL-delays I haven't been able to answer for a long time. I'm also thinking of making a brand new version in a (hopefully) close future, that should work better than this..umm..crap. :p

I'm sorry for this and all my "lagging"..

What about new better version? Can we hope to see it soon?

This was the best sollution I have ever seen, but as I read the forums goes slowly... Please let us know if you are creating something, or we have to look any solusions in other threads...

Big Thank you.

[TMM-TT]

What about new better version? Can we hope to see it soon?

This was the best sollution I have ever seen, but as I read the forums goes slowly... Please let us know if you are creating something, or we have to look any solusions in other threads...

Big Thank you.
The problem with the old script is that every single hit to the forum are controlled and registered to the database. The controlling part should limit the database size, to prevent duplicates, but instead when the database grows larger, it's almost impossible to handle, especially if the board is big. I still have no good solution for this, but I've been working with similar things that still is working quite nice - and is a bit coorporative with the AE-Detector.

Of course, there could be a possibility to make it work as the DNSBL-Plugin does, by caching the lates nicknames, but still, if the database is too large there might be some problems with the speed.

Due to a lot of "side-work", my time is limited, but I'll try to make an upgrade soon. If you have any suggestions on how to make the tracker better, that would be nice too. :)

[twilighthush]

I'm getting errors that look like this:

Connecting to destination database...

Warning: mysql_connect() [function.mysql-connect]: Access denied for user '[usernamehere]'@'localhost' (using password: YES) in /trollpost.php on line 77
Unable to reach forumdatabase

I edited the username and password in the php file when it failed the first time, and it still doesn't work. Any advice?

[bikers]

Ok I have found a bug:

Invalid SQL:
UPDATE user_troll SET timestamp = 1156627674, date = '2006-08-26', username =
'dabear's are #1', userid = '0', time = '17:27:54', opm = '0', imported = 0 WHERE
idx = '9830' AND deleted = 0 AND fqdn = 0 LIMIT 1;

MySQL Error : You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 's are
#1', userid = '0', time = '17:27:54', opm = '0', imported = 0 WHERE idx = ' at line
1
Error Number : 1064
Date : Saturday, August 26th 2006 @ 05:27:54 PM
Script : http://www.xxxxxxx.com/forums/register.php


Now this error is from anyone who uses an apostrophe in their username. The name I tried to register is "dabear's are #1"

This leaves us all open to a SQL attack..



Im still getting this error with 2.0.4. When is there going to be an update or fix for this?

[Alfa1]

There is not. If I understand TMM-TT right, then the hack as it is now will not be continued, but needs a rewrite or a new approach. As it is now it will crash your site once it has gathered enough entries.

[TMM-TT]

That's correct, and to limit any type of damage, the zip is now removed from the project until that happens. :)

I have some ideas, the problem is to make that work without making large sites lag, etc.


This text has been added to the description today:

Since the script needs to be rebuilt from scratch due to some problems with it, I've decided to temporarily stop the distribution. On larger sites, this script may cause heavy lagging when active. There has also been reports on "duplicate keys", that should be fixed. To prevent more problems cause by this hack, it's removed and cannot be downloaded.