Hi Everyone.

I wanted to try running my phpbb forum on my Microsoft SQL server, which is a supported configuration by phpbb. However I found the cleantalk plugin is incompatible due to a few uses of "LIMIT" in sql, which is not supported by MSSQL.

Going through the code I found two files which used LIMIT.
acp\main_module.php
model\CleantalkSFW.php

I've tweaked these files a bit so the plugin now at lease works with MySQL and MSSQL here ( https://github.com/acalcutt/phpbb3.1-3.2-antispam )

At first I had just replaced the LIMITS with TOPS, but this would only have been MSSQL compatible. I ended up going through it again and trying to change it to use "sql_query_limit"

These are the files I modified
CleantalkSFW.php.txt
main_module.php.txt

Do you think there is any issue with this change I made to make it more compatible?

Привет!

При добавлении ключа доступа в настройках расширения вылетает исключение:

Uncaught Error: Class 'cleantalk\antispam\acp\CleantalkHelper' not found in /var/www/forum/ext/cleantalk/antispam/acp/main_module.php:70

Аналогично для кнопки "проверить на спам" - Uncaught Error: Class 'cleantalk\antispam\acp\CleantalkHelper' not found in /var/www/forum/ext/cleantalk/antispam/acp/main_module.php:202

Версия 5.5

This button work very strange.
All users in result are real users.

In main_listener.php
if(sizeof($users[$cnt])>450) - this change array shift depends on user counts?
change 450 to 800 and result of "spam" will be less. Change to 300 - and "spam" accounts will be more.

Привет!

После обновления плагина до версии 5.4, страница с настройками расширения крэшится при формировании постраничного списка пользователей из-за неподдерживаемого синтаксиса (LIMIT [offset,] row_count) при использовании postgresql.

Hi,

After upgrading from an earlier version, I now use the latest version 5.7.5 of this extension on a PHPBB 3.3.9 forum.
At some point in using our forum (e.g. register a new user, or using our contact form) I get an error "Data too long for column 'config_value'" that seems to be related to this line of code:

The data exceeds the length of the column "config_value" in the table "phpbb_config", which is of type varchar(255).

I think the data should be better stored in the other table "phpbb_config_text", so the 255-limit doesn't apply on this type of information that the extension is trying to store?

Thanks!

Paying CleanTalk user here.

I discovered a huge list of mainly 0-post spam users found on my forums -- and I need to delete all of them.

But I can't use "Delete all" because 3 of them have what are extremely useful posts (not found elsewhere; not copy pastes; actual direct replies) that were clearly human-written in a relevant way and not bot-written. I want to preserve those posts that have been sitting there for some time now. Even if I delete these users, I want to keep these specific posts.

In a case, One of the email addresses is a long-dormant member hacked by a spammer so it only got blacklisted later; which means the original email address owner had many legitimate posts at first... So I don't want to delete all of their posts;

My option is to delete 1000+ spam users one by one, because the "Delete All" will remove the few non-spam posts I want to keep.

Almost all my obvious spam users are 0 posts, so perhaps a compromise is a "Delete all who has 0 posts" selection feature.

That way I can manually administer the remaining suspicious users who have nonzero posts (e.g. delete/ban while keeping certain posts).

Thank you!

Also would be helpful to have a chekbox at the top-left for "Select/Unselect All On This Screen"

The "Check users for spam" button in ACP has weird behavior,
The line

Why is this Happening ?
is the code in main_module.php OR is there something wrong in CleanTalk API ???

Should not the list be consistent across different values in that line... ???

PS: this is kind of duplicate of #25 but it's closed now...

Best regards 👍

When trying to activate the SpamFireWall it fails with this error :

SQL ERROR [ postgres ]

ERREUR: erreur de syntaxe sur ou près de « ` » LINE 1: TRUNCATE TABLE `phpbb3_cleantalk_sfw` ^ []

SQL

TRUNCATE TABLE `phpbb3_cleantalk_sfw`

BACKTRACE

FILE: (not given by php)
LINE: (not given by php)
CALL: msg_handler()

FILE: [ROOT]/phpbb/db/driver/driver.php
LINE: 1023
CALL: trigger_error()

FILE: [ROOT]/phpbb/db/driver/postgres.php
LINE: 193
CALL: phpbb\db\driver\driver->sql_error()

FILE: [ROOT]/phpbb/db/driver/factory.php
LINE: 345
CALL: phpbb\db\driver\postgres->sql_query()

FILE: [ROOT]/ext/cleantalk/antispam/model/CleantalkSFW.php
LINE: 294
CALL: phpbb\db\driver\factory->sql_query()

FILE: [ROOT]/ext/cleantalk/antispam/model/main_model.php
LINE: 466
CALL: cleantalk\antispam\model\CleantalkSFW::sfw_update()

FILE: [ROOT]/ext/cleantalk/antispam/acp/main_module.php
LINE: 95
CALL: cleantalk\antispam\model\main_model::sfw_update()

FILE: [ROOT]/includes/functions_module.php
LINE: 676
CALL: cleantalk\antispam\acp\main_module->main()

FILE: [ROOT]/adm/index.php
LINE: 81
CALL: p_master->load_active()

I've removed the backquotes "`" around phpbb3_cleantalk_sfw which isn't postgres compatible on line :
https://github.com/CleanTalk/phpbb3.1-3.2-antispam/blob/master/cleantalk/antispam/model/CleantalkSFW.php#L294

And it seams to be fine.

I don't know for sure if phpBB allow spaces in table prefix, but it's unlikely, so that change should be safe for MySQL as well.
(or a properly escaped table name even better)

I had a situation where my server went down for an hour when I clicked "Check users for spam" while there were over 100-200--500+ email addresses listed and not deleted yet. (There was a gap between my subscription renewal, so the spammy users built up for a while). The pattern seems to be having way more emails listed than you have concurrent SQL connections allowed, plus the forum already being busy.

Looking at the logs the server admin gave me, mysqld ran out of connections with massive numbers of "Waiting for table level lock" events (over 100 "Waiting for table level lock" piled up and clogged/deadlocked). For security reasons I can't publicly share the logs here, but a lot of deadlocked (waiting for table level lock) "INSERT INTO XXXXXXXbb_confirm" lines.

I could still ssh in so I found a workaround by "killall --user USERID" via ssh without needing to reboot the server (took a long time for me to figure this workaround out to kill all processes specific to the discussion forum, without needing to call my host sysadmin for emergency root access).

Then I clicked the "Check users for spam again" -- and right on cue (within 1-2 minutes) mysqld jammed completely, bringing down all mysql-dependant servers on my dedicated server.

Now I'll only check a few users at a time, but you might want to add some code to throttle the rate of queries to prevent unusual numbers of multithreaded "Waiting for table level lock" events.

If your forum has plenty of users, a lot of spammers, and you're a bit behind in executing a manual check (e.g. right after reactivating a subscription) -- I just wanted to let you know that Cleantalk phpbb plugin can cause 100% dedicated non-VPS Xeon server mysqld to go down for the count (without manual intervention) if you click "Check users for spam again" with over 100+ listed and keeping that textbox blank.

Temporary Workaround: Manually check only a few emails at a time -- and do it during off hours.

Permanent Workaround: Clealktalk phpbb plugin should code for gentler database access in a situation of being very far behind in spammer check in a "subscription-reactivation" manual-check situation.