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);