List of spam IPs in SQL format

Question

I have following table in my MySQL databse :

CREATE  TABLE IF NOT EXISTS `virt100706_wie`.`banned_ips` (
  `idbanned_ips` INT NOT NULL AUTO_INCREMENT ,
  `ip` TEXT NOT NULL ,
  `date` DATE NOT NULL ,
  `reason` TEXT NULL ,
  PRIMARY KEY (`idbanned_ips`) )
ENGINE = InnoDB

I wanna to add to it the list of IPs, which are sending spams, belong to hackers, botnets, etc. etc. and permanently block access for it to my website.

I have found a good list here, but it is published in CSV format and I need that in SQL format (INSERT statements).

My question is : Is there any list of spam IPs publihsed in SQL format ? or Is there any tool to convert CSV to SQL INSERT format ?

Thanks in advance !

Answer 1

I have to do this sort of thing sometimes. It's right on the borderline of being a task worth writing a script for. Usually I don't write a script - I'll use an emacs keyboard macro, but if your editor doesn't support keyboard macros, here's another way:

• Open the CSV in a spreadsheet program

• Write a formula column that substitutes in the values in the correct places (this assumes IP addresses are in column A)

  ="INSERT INTO virt100706_wie.banned_ips (ip,date) VALUES ('" & A1 & "',NOW());"

• fill down the formula

Then copy and paste the result into the editor and save as a .sql file.