my new blog spam killing tool
Ok. So a few days ago, my site was hit with a major amount of blog spam. Now I used to be able to just delete all spam coming from a single IP address, but now because my site is going though an ISA server, everything comes from the same IP address. So if I tried that, it would kill real comments too. So, the only way to distinguish the blog spam from real comments is via the comment author URL. So, after some hacking I have this piece of code for you:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
ListBox1.Items.Add(TextBox1.Text)
TextBox1.Text = ""
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim sqltext As String
sqltext = "delete from wp_comments where comment_author_url like ‘"
Dim sqltextend As String
sqltextend = "';"
For i As Integer = 0 To ListBox1.Items.Count – 1
TextBox2.Text = TextBox2.Text & vbCr & sqltext & ListBox1.Items.Item(i).ToString & sqltextend
Next
End Sub
Private Sub TextBox1_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles TextBox1.KeyDown
If e.KeyCode = Keys.Enter Then
ListBox1.Items.Add(TextBox1.Text)
TextBox1.Text = ""
End If
End Sub
Now, what does it do? Firstly, I should tell you what you need. So a form with 2 text boxes (textbox1 and textbox2). Textbox2 should be set to multiline = true. You need 2 buttons and a list box also.
So, the code above allows you to either type or paste a URL you want to delete from your comments into textbox1, and either hit enter or click button1. This will add it to the list box and clear textbox1 for a new URL. Next, the button2 click event.
When this is clicked, a SQL query is generated based around sqltext and sqltextend. So, the final things I do are just copy and paste this query that has been generated directly into the MySQL console application, and it deletes any comments that should be killed. Pretty simple. Hardest part is getting the list of comments. I have no idea how to get the comments directly, because of the way my system is set up. Sorry.
So, for future plans, I am working on a place to store the URLs so you will have a list of URLs you should delete all the time. Also, connection to the SQL server that holds your wordpress DB may be on the cards.
Final idea might be a reporting system where it would keep the list of comments that you have, and each time it is run, assuming it is connected to the SQL DB, it would log how many comments with each URL had been deleted, and you could have some sort of reporting system.
Hopefully this helps someone. Its helping me at the moment. The storing of URLs is handy. Ohh, and a system for checking if a comment has already been added would be on the cards too.