this post was submitted on 16 Mar 2025
166 points (100.0% liked)

Lemmy.ca's Main Community

3183 readers
19 users here now


Welcome to the lemmy.ca/c/main community!

All new users on lemmy.ca are automatically subscribed to this community, so this is the place to read announcements, make suggestions, and chat about the goings-on of lemmy.ca.

For support requests specific to lemmy.ca, you can use [email protected].


founded 4 years ago
MODERATORS
 

Sorry everyone I know how much you love the attention she gives you, but I've implemented some quick and dirty filtering for private messaging.

We now have the ability to automatically mark PM's as deleted or read, depending on content inside of them. If we accidentally filter something you legitimately wanted (ie, not Nicole) please let me know.

If any other instances would like to implement this, here's the code. Note that you'll need to set your hostname at the top here for some reason I haven't exactly identified.

SET lemmy.protocol_and_hostname = 'https://lemmy.ca/';

CREATE TABLE private_message_filters (
    id SERIAL PRIMARY KEY,
    phrase TEXT NOT NULL,
    behavior VARCHAR(10) NOT NULL CHECK (behavior IN ('delete', 'mark_read'))
);

CREATE OR REPLACE FUNCTION filter_private_messages()
RETURNS trigger AS $$
DECLARE
    banned_phrase_record private_message_filters%ROWTYPE;
BEGIN
    FOR banned_phrase_record IN 
        SELECT * FROM private_message_filters
    LOOP
        IF LOWER(TRIM(NEW.content)) ILIKE '%' || LOWER(TRIM(banned_phrase_record.phrase)) || '%' THEN
            IF banned_phrase_record.behavior = 'delete' THEN
                NEW.deleted := true;
                RETURN NEW;
            ELSIF banned_phrase_record.behavior = 'mark_read' THEN
                NEW.read := true;
                RETURN NEW;
            END IF;
        END IF;
    END LOOP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_filter_private_messages
AFTER INSERT ON private_message
FOR EACH ROW
EXECUTE FUNCTION filter_private_messages();

To add filter words:

insert into private_message_filters (behavior, phrase) values ('delete', 'spamtestdelete');
insert into private_message_filters (behavior, phrase) values ('mark_read', 'spamtestread');

If you want to quickly disable / enable filtering while testing:

ALTER TABLE private_message DISABLE TRIGGER trg_filter_private_messages;
ALTER TABLE private_message ENABLE TRIGGER trg_filter_private_messages;

I'll leave it up to you to figure out what phrases to filter on. MAKE SURE YOU TEST. If there's an error, private messaging could break completely. You should not get an error message from the UI while sending a message with a banned word.

all 33 comments
sorted by: hot top controversial new old
[–] [email protected] 58 points 1 week ago (2 children)

But I love her and she loves me.

[–] [email protected] 30 points 1 week ago* (last edited 1 week ago) (2 children)

I saw her send DMs to Not Dr. Wesker, too.

I'm sorry to be the bearer of bad news.

[–] [email protected] 37 points 1 week ago (2 children)

I actually only got one today for the very first time. I was insulted it took her so long to get to me, so I wrote this

[–] [email protected] 4 points 1 week ago

I've only had one, and it was the first ever wave; for some reason, i never got another one. I scared her away :(

[–] [email protected] 2 points 1 week ago (1 children)

Today I received my third pm from Nicole. I feel special!

[–] [email protected] 2 points 1 week ago

I got my third yesterday also. It's not terribly annoying but I could see how spam messages could easily get out of hand.

[–] [email protected] 13 points 1 week ago (2 children)

Please be serious, this is a serious community.

[–] [email protected] 10 points 1 week ago

I will consider your request in 3-5 business days. Thank you for your patience.

[–] [email protected] 9 points 1 week ago* (last edited 1 week ago)

I love her, she loves me

I'm her he, she's my she

Though for looks she's only fair

And her figure isn't there

Her figure is much bigger in the bank, well I declare

Her papa, he has dough, he has dough, and

And she's his only child, and she's awfully wild

So I love her and she loves me

[–] [email protected] 28 points 1 week ago (1 children)

Oh my god! They killed Nicole!

[–] [email protected] 12 points 1 week ago

"You bastards"? #KyleBrovlofski

[–] [email protected] 26 points 1 week ago* (last edited 1 week ago) (1 children)

This is sorta OK for this one case, but we really need more hardened and easy solutions as the spam problem will only get worse. The DMs are very vulnerable, which is why services like bsky disable them for all new accounts for example.

Went ahead and opened some issues

[–] [email protected] 5 points 1 week ago

100% agreed, this was intended as a quick fix and not a long term solution. There should also be rate limiting on both the sending + receiving instance. Maybe the ability to mark an account as an "Authorized bulk sender" but otherwise if you send more than X DMs to Y different people within Z hours, you get blocked.

Both your issues LGTM, thanks for writing them up!

[–] [email protected] 15 points 1 week ago

I still got the notification via Android, but the message was gone and was curious if y'all had finally nuked this problem.

But, finally get Nicole'd and my only chance for a real, meaningful relationship goes up in smoke. Thanks Obama.

[–] [email protected] 9 points 1 week ago (1 children)

Aww I've never even gotten a Dm from her

[–] [email protected] 2 points 1 week ago* (last edited 1 week ago)

Hang in there.

[–] [email protected] 6 points 1 week ago

I’ve got DMs from her for almost two years, every 3 months, like clockwork. The photos changed over time, which I found interesting.

I won’t be sad to not get any more DMs though. They’re annoying.

[–] [email protected] 6 points 1 week ago (1 children)

But... but... no one else pretends to want to fuck me around here.

[–] [email protected] 5 points 1 week ago (1 children)
[–] [email protected] 9 points 1 week ago (1 children)
[–] [email protected] 1 points 1 week ago (2 children)
[–] [email protected] 3 points 1 week ago

She's determined! Now she injects random spaces and is avoiding unicode shenanigans.

[–] [email protected] 2 points 1 week ago

Turns out, "Nicole" had a lemmy.ca account they were using for testing. I see you https://lemmy.ca/u/fujinamilo

[–] [email protected] 4 points 1 week ago* (last edited 1 week ago)

i get my nicole fix as the form of user submitted posts, she doesn't message me anymore

[–] [email protected] 3 points 1 week ago* (last edited 1 week ago) (2 children)

I just got a message from her 20 minutes ago (Mar 16 2025 16:17:08 AM GMT-4) . Is there anything I need to do on my side? Or is the filtering delayed?

Edit: And another one at 7:20:13

Edit 2: I've now had 4 identical messages from her since I posted this comment.

[–] [email protected] 1 points 1 week ago

I think they're just evading our filters. We'll have to keep tweaking.

[–] [email protected] 1 points 1 week ago

I also received one (my first) after this post was made. I blocked the user so I can't go back to the message to check the timestamp.

[–] [email protected] 2 points 1 week ago

Oh I just got the same DM twice from her overnight. LoL

Is she like a test account for these filters?

[–] [email protected] 1 points 1 week ago