{"action":"created","issue":{"url":"https://api.github.com/repos/jsbin/jsbin/issues/2320","labels_url":"https://api.github.com/repos/jsbin/jsbin/issues/2320/labels{/name}","comments_url":"https://api.github.com/repos/jsbin/jsbin/issues/2320/comments","events_url":"https://api.github.com/repos/jsbin/jsbin/issues/2320/events","html_url":"https://github.com/jsbin/jsbin/issues/2320","id":64465181,"number":2320,"title":"URGENT: need script to remove spam [now fixed, but still needs some work]","user":{"login":"remy","id":13700,"avatar_url":"https://avatars.githubusercontent.com/u/13700?v=3","gravatar_id":"","url":"https://api.github.com/users/remy","html_url":"https://github.com/remy","followers_url":"https://api.github.com/users/remy/followers","following_url":"https://api.github.com/users/remy/following{/other_user}","gists_url":"https://api.github.com/users/remy/gists{/gist_id}","starred_url":"https://api.github.com/users/remy/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/remy/subscriptions","organizations_url":"https://api.github.com/users/remy/orgs","repos_url":"https://api.github.com/users/remy/repos","events_url":"https://api.github.com/users/remy/events{/privacy}","received_events_url":"https://api.github.com/users/remy/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/jsbin/jsbin/labels/help%20wanted","name":"help wanted","color":"009800"}],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":76,"created_at":"2015-03-26T08:08:07Z","updated_at":"2015-03-26T18:45:20Z","closed_at":null,"body":"So, here's the situation: spammers have filled the database with crud to 100% capacity. I woke up this morning at 6am to a few tweets and emails from users notifying me of this:\r\n\r\nhttps://twitter.com/eggheadio/status/580925169671127041\r\n\r\nI've got the start of a script that identifies spam users, but there's nothing consistent in the spammy bins (though usually they don't have any CSS or JS).\r\n\r\n**The bigger problem is: I'm solo parenting my two young kids over the next 2 days and I don't have time to fix this. This sucks, but my kids come first.**\r\n\r\nSo, I'm calling on you - can you help? \r\n\r\nI reckon this is one approach:\r\n\r\n1. Flag all spammy users (they come from a small subset of email domains) (script included below).\r\n2. Get all bins from spammy users and delete them\r\n3. Find bins that have HTML over a certain size... 2k? and no JS (so it's roughly fitting a profile) and has anonymous URL structure (`url.length > 6`) and remove.\r\n\r\nThis is a aggressive, but the disk space is at capacity and saving doesn't work at all right now.\r\n\r\nConstraints:\r\n\r\n1. The database is >15million records, so you can't just do a massive delete query, it needs to find records in chunks and needs to ideally remove individually.\r\n2. User should be flagged with `UPDATE ownership SET flagged=1 WHERE name=\"bad-guy\"`\r\n\r\nNotes:\r\n\r\n1. User table is called `ownership`\r\n2. User bin tables is called `owners` - yes, I know, shit names\r\n3. Bins are stored in `sandbox`\r\n\r\n```js\r\nvar Sequelize = require('sequelize');\r\nvar sequelize = new Sequelize('database', 'root', 'password');\r\n\r\nvar spammy = ['spam-email.com'];\r\n\r\nsequelize.query(\"SELECT * FROM `ownership`\", { type: sequelize.QueryTypes.SELECT})\r\n  .then(function(users) {\r\n    var targets = users.filter(function (user) {\r\n      var match = spammy.filter(function (spam) {\r\n        return (user.email.indexOf('@' + spam) !== -1)\r\n      });\r\n      return match.length > 0;\r\n    });\r\n\r\n    targets.forEach(function (spam) {\r\n\r\n    });\r\n\r\nconsole.log(targets.length);\r\n  })\r\n```"},"comment":{"url":"https://api.github.com/repos/jsbin/jsbin/issues/comments/86664436","html_url":"https://github.com/jsbin/jsbin/issues/2320#issuecomment-86664436","issue_url":"https://api.github.com/repos/jsbin/jsbin/issues/2320","id":86664436,"user":{"login":"StoneCypher","id":77482,"avatar_url":"https://avatars.githubusercontent.com/u/77482?v=3","gravatar_id":"","url":"https://api.github.com/users/StoneCypher","html_url":"https://github.com/StoneCypher","followers_url":"https://api.github.com/users/StoneCypher/followers","following_url":"https://api.github.com/users/StoneCypher/following{/other_user}","gists_url":"https://api.github.com/users/StoneCypher/gists{/gist_id}","starred_url":"https://api.github.com/users/StoneCypher/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/StoneCypher/subscriptions","organizations_url":"https://api.github.com/users/StoneCypher/orgs","repos_url":"https://api.github.com/users/StoneCypher/repos","events_url":"https://api.github.com/users/StoneCypher/events{/privacy}","received_events_url":"https://api.github.com/users/StoneCypher/received_events","type":"User","site_admin":false},"created_at":"2015-03-26T18:45:20Z","updated_at":"2015-03-26T18:45:20Z","body":"Can you give us an example of one of the spam pages?"}}