Removing database spam

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:
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", [""]);
let q={};
let hotBans=[];
setInterval(async function() {
    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) {
        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})`;
    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==""||req.ip=="") return req.socket.end();
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});

Does the following work for you?

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

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;
(3241 rows)

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

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


You are welcome! Happy to help!


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