Removing database spam

Question:
What’s the best way to purge any row where the length of path is greater than 10 and visits is less than 3 from my postgresql table? Someone just spammed my website, and now I need to remove several thousand spam entries. (In case it matters, I’m not using Replit’s paid thing)
Here’s what the table I need to remove data from is like:

CREATE TABLE IF NOT EXISTS top_routes(path text PRIMARY KEY, id serial, first_seen timestamp DEFAULT NOW(), last_seen timestamp, visits integer DEFAULT 1)

Repl link:
https://replit.com/@TheWebArmy/user-agent-counter
Here’s the source for the program:

import sql from './db.js'
import express from 'express'
import express_ws from 'express-ws'
const app=express();
app.set("view engine", "ejs");
app.set("trust proxy", ["172.16.0.0/12"]);
let q={};
let hotBans=[];
setInterval(async function() {
    //if(q[req.ip])
    let pendingBans=[];
    Object.keys(q).forEach(function(item) {
        if(q[item]>10) pendingBans.push(item);
        else q--;
    });
    while(pendingBans.length>0) {
        await sql`INSERT INTO bans(ip) VALUES (${pendingBans.pop()})`;
    }
}, 1000 * 5);
app.use("/", async (req, res, next) => {
    if(hotBans.includes(req.ip)) {
        console.log(`Dropping abuser ${req.ip} for ${req.originalUrl}`); // mk
        if(Math.random() < 0.1) {
            console.log("gzip bomb!");
            res.set("Content-Encoding", "gzip");
            return res.sendFile("/home/runner/user-agent-counter/gzbomb.gz");
        }
        else {
            return res.redirect(301, process.env.rickroll);
        }
        //return res.sendFile("/home/runner/gzbomb.gz");
    }
    let ban_check=(await sql`SELECT ip FROM bans WHERE ip = ${req.ip}`);
    if(ban_check.length>0) {
        hotBans.push(req.ip);
        console.log(`Dropping abuser ${req.ip} for ${req.originalUrl}`);
        return req.socket.end();
    }
    if(q[String(req.ip)]==undefined) q[String(req.ip)]=1;
    else q[String(req.ip)]+=1;
    if(q[String(req.ip)]>=10) await sql`INSERT INTO bans(ip) VALUES (${req.ip})`;
    //if(q[req.ip])
    console.log(`${req.originalUrl} - ${req.ip}`);
    await sql`INSERT INTO top_routes(path, last_seen) VALUES (${req.originalUrl}, ${new Date()})
    ON CONFLICT(path) DO
    UPDATE SET (visits, last_seen) = (top_routes.visits + 1, ${new Date()})`
    await sql`INSERT INTO user_agents(user_agent) VALUES (${req.headers["user-agent"]||"N/A"}) ON CONFLICT(user_agent) DO UPDATE SET visits = user_agents.visits + 1`
    if(req.ip=="216.128.144.66"||req.ip=="34.82.147.176") return req.socket.end();
    next();
})
app.get("/", async (req, res, next) => {
    const user_agents=await sql`SELECT visits, user_agent FROM user_agents ORDER BY visits`;
    const top_routes=await sql`SELECT path, first_seen, last_seen, visits FROM top_routes ORDER BY visits`;
    res.render("index", {user_agents, top_routes});
});
app.listen(80);

Does the following work for you?

DELETE FROM top_routes
WHERE LENGTH(path) > 10
AND visits < 3;
2 Likes

Seems like it’d work, I’ll try it!

~/user-agent-counter$ PAGER="tail -n 10" psql ua-counter
psql (14.6)
Type "help" for help.

ua-counter=# SELECT path FROM top_routes
ua-counter-# WHERE LENGTH(path) > 10
ua-counter-# AND visits < 3;
 /bbd4604c7b2bd369992a64dadcfe80c7cb953db459c8afffbd0b36d99a50e59e13fcfcc38b45346ccf2fd293c49e010ef2a29f145340d56f97042adb1914
 /7a412ad9a228c0222facaff720a652d1bb62eb1a363efd547ed4a01a8ca66aca3597d67558c1b358658e743ec890ec4e92b3bb9a68bce67aae6075abce3e
 /dcbf9b0667393f6b8bc6878fb6c09838eb683d75bad57ea25428da7d1ab9a69840f6138d80760d652e010efcc1af01396f134b4e9c6062a472dd03e0df1a
 /91b611ce38e1367cb9d67a992a80b684a9ba59ccfcced57563d9a916eb2b7fb02f69a4275d57225684f7f84a8005ea4f18c6db3ee3370145049f6d3df445
 /12e87206c94848595583c8ad5e0b6e682f471983a83469b4f732c7b47ce1118434db3cb8ba9a0beaaad49eaf938ebf1730cbd9fe61d1f504fa7db22d76aa
 /23fe693640be9d36cd63453a0c584641029e55cfd97c09573c7de4d8608cb755ff1be0f526de9155f973f324d75a91a376b94291a12753c9a4e6889f978c
 /2201c2c4019958584e8c26eb5f219e7b7e8656cc367dbbe6f5efd3b46bc40cfa4a3a3c0abf31301c3f38bde3ad19ac2d44d4ad91f2c5f5cf74ef92003934
 /6b7e5f6aaee31b448f4e09d54f97eb565171e861078f94c8aeb7321437e2af2b61483118e6336e09d8d8aa9544565e63a03ca205e22687d4253dd51ea1a8
(3241 rows)

ua-counter=# \q
~/user-agent-counter$ 
2 Likes
~/user-agent-counter$ echo 'DELETE FROM top_routes WHERE LENGTH(path) > 10 AND visits < 3;'|time psql ua-counter
DELETE 3241
0.00user 0.00system 0:00.03elapsed 11%CPU (0avgtext+0avgdata 6516maxresident)k
0inputs+0outputs (0major+421minor)pagefaults 0swaps
~/user-agent-counter$ 

Seemed to work! Now I just need to purge a few dozen spam items manually and handle user-agent spam! Thanks @youngchief

4 Likes

You are welcome! Happy to help!

2 Likes

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.