/**
* Copyright 2011 Marco Berri - marcoberri@gmail.com
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and limitations under the License.
**/
package com.cron.job.BlackList;
import com.utils.BigFile;
import java.io.File;
import java.io.IOException;
import java.sql.SQLException;
import java.sql.Statement;
import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;
import com.utils.DateTimeUtil;
import com.utils.StringUtil;
/**
* - Scarica le info da http://www.dnsbl.manitu.net/download/nixspam-ip.dump.gz in una cartella temporanea settata nelle conf<br/>
* - Scarica anche da http://spam-ip.com/csv_dump/spam-ip.com_03-02-2011.csv<br/>
* - Volendo ma da implementare può scaricare anche da http://urlblacklist.com/cgi-bin/commercialdownload.pl?type=download&file=bigblacklist<br/>
* - Elimina il contenuto della tabella blacklist.<br/>
* - Elabora il file scaricato e inserisce nella tabella blacklist gli ip trovati nel file di testo precedente.<br/>
*<br/>
* @author Marco Berri marcoberri@gmail.com
*/
public class ImportDB extends com.cron.job.Base {
/**
*
* @param context
* @throws JobExecutionException
*/
@Override
public void execute(JobExecutionContext context) throws JobExecutionException {
super.execute(context);
debug("Start execute job " + this.getClass().getName());
try {
//scarico il file
String name = "nixspam-ip.dump.gz";
String f = this.path_app_root + "/" + this.properties.get("dir") + "/";
try {
org.apache.commons.io.FileUtils.forceMkdir(new File(f));
} catch (IOException ex) {
fatal("IOException", ex);
}
f += "/" + name;
String url = "http://www.dnsbl.manitu.net/download/" + name;
debug("(1) - start download: " + url);
com.utils.HttpUtil.downloadData(url, f);
com.utils.IOUtil.unzip(f, f.replace(".gz", ""));
File file_to_read = new File(f.replaceAll(".gz", ""));
BigFile lines = null;
try {
lines = new BigFile(file_to_read.toString());
} catch (Exception e) {
fatal("Excpetion", e);
return;
}
try {
Statement stat = conn_url.createStatement();
stat.executeUpdate(properties.get("query_delete"));
stat.close();
} catch (SQLException e) {
fatal("SQLException", e);
}
try {
conn_url.setAutoCommit(false);
} catch (SQLException e) {
fatal("SQLException", e);
}
boolean ok = true;
int i = 0;
for (String line : lines) {
if (StringUtil.isEmpty(line) || line.indexOf(" ") == -1) {
continue;
}
//eseguo la insert
try {
line = line.substring(line.indexOf(" "));
line = line.trim();
if (getIPException(line)) {
continue;
}
Statement stat = this.conn_url.createStatement();
stat.executeUpdate("insert into blacklist(url) values('" + line + "')");
stat.close();
i++;
} catch (SQLException e) {
fatal("SQLException", e);
try {
conn_url.rollback();
} catch (SQLException ex) {
fatal("SQLException", ex);
}
ok = false;
break;
}
}
//elimino il file originale per risparmiare spazio su disco
boolean del = file_to_read.delete();
debug("File " + file_to_read + " del:" + del);
//altro file da scaricare...
//scarico il file
name = "spam-ip.com_" + DateTimeUtil.getNowWithFormat("MM-dd-yyyy") + ".csv";
f = this.path_app_root + "/" + this.properties.get("dir") + "/";
org.apache.commons.io.FileUtils.forceMkdir(new File(f));
f += "/" + name;
url = "http://spam-ip.com/csv_dump/" + name;
debug("(2) - start download: " + url);
com.utils.HttpUtil.downloadData(url, f);
file_to_read = new File(f);
try {
lines = new BigFile(file_to_read.toString());
} catch (Exception e) {
fatal("Exception", e);
return;
}
try {
conn_url.setAutoCommit(false);
} catch (SQLException e) {
fatal("SQLException", e);
}
ok = true;
for (String line : lines) {
if (StringUtil.isEmpty(line) || line.indexOf(" ") == -1) {
continue;
}
//eseguo la insert
try {
line = line.split(",")[1];
line = line.trim();
if (getIPException(line)) {
continue;
}
Statement stat = this.conn_url.createStatement();
stat.executeUpdate("insert into blacklist(url) values('" + line + "')");
stat.close();
i++;
} catch (SQLException e) {
fatal("SQLException", e);
try {
conn_url.rollback();
} catch (SQLException ex) {
fatal("SQLException", ex);
}
ok = false;
break;
}
}
//elimino il file originale per risparmiare spazio su disco
del = file_to_read.delete();
debug("File " + file_to_read + " del:" + del);
if (ok) {
debug("Import della BlackList Concluso tot righe: " + i);
try {
conn_url.commit();
} catch (SQLException e) {
fatal("SQLException", e);
}
} else {
fatal("Problemi con la Blacklist");
}
try {
conn_url.setAutoCommit(true);
} catch (SQLException e) {
fatal("SQLException", e);
}
try {
Statement stat = this.conn_url.createStatement();
stat.executeUpdate("VACUUM");
stat.close();
} catch (SQLException e) {
fatal("SQLException", e);
}
} catch (IOException ex) {
fatal("IOException", ex);
}
debug("End execute job " + this.getClass().getName());
}
//da portare nelle conf oppure in un file a parte modificabile a mano.
/**
*
* @param ip
* @return boolean
*/
protected boolean getIPException(String ip) {
if (ip.equals("127.0.0.1")) {
return true;
}
if (ip.equals("localhost")) {
return true;
}
return false;
}
}