Java tutorial
/* * Copyright (C) 2012 Chris Neasbitt * Author: Chris Neasbitt * * This program is free software: you can redistribute it and/or modify * it under the terms of the GNU General Public License as published by * the Free Software Foundation, either version 2 of the License, or * (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * * You should have received a copy of the GNU General Public License * along with this program. If not, see <http://www.gnu.org/licenses/>. */ package edu.uga.cs.fluxbuster.db; import java.io.Reader; import java.io.StringReader; import java.net.InetAddress; import java.net.UnknownHostException; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.Date; import java.util.Formatter; import java.util.HashSet; import java.util.List; import java.util.Map; import java.util.Set; import java.text.SimpleDateFormat; import edu.uga.cs.fluxbuster.analytics.ClusterSimilarity; import edu.uga.cs.fluxbuster.analytics.ClusterSimilarityCalculator; import edu.uga.cs.fluxbuster.classification.ClusterClass; import edu.uga.cs.fluxbuster.clustering.CandidateFluxDomain; import edu.uga.cs.fluxbuster.clustering.DomainCluster; import edu.uga.cs.fluxbuster.clustering.StoredDomainCluster; import edu.uga.cs.fluxbuster.utils.DomainNameUtils; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.postgresql.copy.CopyManager; import org.postgresql.core.BaseConnection; import com.jolbox.bonecp.BoneCP; import com.jolbox.bonecp.ConnectionHandle; /** * The implementation of the DBInterface for PostgresSQL. * * @author Chris Neasbitt */ public class PostgresDBInterface extends DBInterface { private static Log log = LogFactory.getLog(PostgresDBInterface.class); private SimpleDateFormat dateFormatTable = new SimpleDateFormat("yyyyMMdd"); private SimpleDateFormat dateFormatStr = new SimpleDateFormat("yyyy-MM-dd"); /** * Instantiates a new postgres db interface. * * @param connectionPool the BoneCP connection pool from * which to generate connections. */ public PostgresDBInterface(BoneCP connectionPool) { super(connectionPool); } /** * @see edu.uga.cs.fluxbuster.db.DBInterface#initSimilarityTables(java.util.Date) */ @Override public void initSimilarityTables(Date logdate) { String logDateTable = dateFormatTable.format(logdate); String logDateStr = dateFormatStr.format(logdate); String clusterIpSimilCreateQuery = "CREATE TABLE cluster_ip_similarity_" + logDateTable + " (CONSTRAINT cluster_ip_similarity_" + logDateTable + "_log_date_check CHECK (log_date = '" + logDateStr + "'::date), " + " CONSTRAINT cluster_ip_similarity_" + logDateTable + "_pkey PRIMARY KEY (cluster_id, candidate_cluster_id, similarity, log_date, candidate_log_date) ) " + " INHERITS (cluster_ip_similarity)"; String clusterDomainnameSimilCreateQuery = "CREATE TABLE cluster_domainname_similarity_" + logDateTable + " (CONSTRAINT cluster_domainname_similarity_" + logDateTable + "_log_date_check CHECK (log_date = '" + logDateStr + "'::date), " + " CONSTRAINT cluster_domainname_similarity_" + logDateTable + "_pkey PRIMARY KEY (cluster_id, candidate_cluster_id, similarity, log_date, candidate_log_date) ) " + " INHERITS (cluster_domainname_similarity)"; String clusterSimilIpCreate = "CREATE INDEX cluster_ip_similarity_" + logDateTable + "_logdate " + " ON cluster_ip_similarity_" + logDateTable + " USING btree (log_date)"; String clusterSimilDomainnameCreate = "CREATE INDEX cluster_domainname_similarity_" + logDateTable + "_logdate " + " ON cluster_domainname_similarity_" + logDateTable + " USING btree (log_date)"; try { this.executeQueryNoResult("SELECT * FROM cluster_ip_similarity_" + logDateTable + " limit 1", true); } catch (Exception e) { this.executeQueryNoResult(clusterIpSimilCreateQuery); this.executeQueryNoResult(clusterSimilIpCreate); } try { this.executeQueryNoResult("SELECT * FROM cluster_domainname_similarity_" + logDateTable + " limit 1", true); } catch (Exception e) { this.executeQueryNoResult(clusterDomainnameSimilCreateQuery); this.executeQueryNoResult(clusterSimilDomainnameCreate); } } /** * @see edu.uga.cs.fluxbuster.db.DBInterface#initClassificationTables(java.util.Date) */ @Override public void initClassificationTables(Date logdate) { String logDateTable = dateFormatTable.format(logdate); String logDateStr = dateFormatStr.format(logdate); String clusterClassesCreateQuery = "CREATE TABLE cluster_classes_" + logDateTable + " (CONSTRAINT cluster_classes_" + logDateTable + "_log_date_check CHECK (log_date = '" + logDateStr + "'::date), " + " CONSTRAINT cluster_classes_" + logDateTable + "_pkey PRIMARY KEY(cluster_id, sensor_name, log_date) ) " + " INHERITS (cluster_classes)"; String clusterClassesIndexCreate = "CREATE INDEX cluster_classes_" + logDateTable + "_logdate " + " ON cluster_classes_" + logDateTable + " USING btree (log_date) "; //if the table doesn't exist, this query should throw an exception try { this.executeQueryNoResult("SELECT * FROM cluster_classes_" + logDateTable + " limit 1", true); } catch (Exception e) { this.executeQueryNoResult(clusterClassesCreateQuery); this.executeQueryNoResult(clusterClassesIndexCreate); } } /** * @see edu.uga.cs.fluxbuster.db.DBInterface#initClusterTables(java.util.Date) */ @Override public void initClusterTables(Date logdate) { String logDateTable = dateFormatTable.format(logdate); String logDateStr = dateFormatStr.format(logdate); String domainsCreateQuery = "CREATE TABLE domains_" + logDateTable + " (PRIMARY KEY(domain_id), UNIQUE(domain_name), " + " CONSTRAINT domains_" + logDateTable + "_log_date_check CHECK (log_date = '" + logDateStr + "'::date)) " + "INHERITS (domains)"; String clustersCreateQuery = "CREATE TABLE clusters_" + logDateTable + " (PRIMARY KEY(domain_id, sensor_name), " + "CONSTRAINT clusters_" + logDateTable + "_log_date_check CHECK (log_date = '" + logDateStr + "'::date)) " + "INHERITS (clusters)"; String resolvedIPSCreateQuery = "CREATE TABLE resolved_ips_" + logDateTable + " (PRIMARY KEY(domain_id, log_date, resolved_ip), " + "CONSTRAINT resolved_ips_" + logDateTable + "_log_date_check CHECK (log_date = '" + logDateStr + "'::date) " + " ) INHERITS(resolved_ips) "; String clusterResolvedIPSCreateQuery = "CREATE TABLE cluster_resolved_ips_" + logDateTable + " ( " + "PRIMARY KEY(cluster_id, sensor_name, log_date, resolved_ip), " + "CONSTRAINT cluster_resolved_ips_" + logDateTable + "_log_date_check CHECK (log_date = '" + logDateStr + "'::date) ) " + "INHERITS (cluster_resolved_ips)"; String clusterFeaturesCreateQuery = "CREATE TABLE cluster_feature_vectors_" + logDateTable + " (CONSTRAINT cluster_feature_vectors_" + logDateTable + "_pkey PRIMARY KEY (cluster_id , sensor_name , log_date ), " + " CONSTRAINT cluster_feature_vectors_" + logDateTable + "_log_date_check CHECK (log_date = '" + logDateStr + "'::date) ) " + " INHERITS (cluster_feature_vectors) "; String domainsIndexCreate = "CREATE INDEX domains_" + logDateTable + "_logdate " + " ON domains_" + logDateTable + " USING btree (log_date)"; String clustersIndexCreate = "CREATE INDEX clusters_" + logDateTable + "_logdate " + " ON clusters_" + logDateTable + " USING btree (log_date)"; String resolvedIPSIndexCreate = "CREATE INDEX resolved_ips_" + logDateTable + "_logdate " + " ON resolved_ips_" + logDateTable + " USING btree (log_date)"; String clusterResolvedIPSIndexCreate = "CREATE INDEX cluster_resolved_ips_" + logDateTable + "_logdate " + " ON cluster_resolved_ips_" + logDateTable + " USING btree (log_date)"; String clusterFeaturesIndexCreate = "CREATE INDEX cluster_feature_vectors_" + logDateTable + "_logdate " + " ON cluster_feature_vectors_" + logDateTable + " USING btree (log_date)"; //create tables try { this.executeQueryNoResult("SELECT * FROM domains_" + logDateTable + " limit 1", true); } catch (Exception e) { this.executeQueryNoResult(domainsCreateQuery); this.executeQueryNoResult(domainsIndexCreate); } try { this.executeQueryNoResult("SELECT * FROM clusters_" + logDateTable + " limit 1", true); } catch (Exception e) { this.executeQueryNoResult(clustersCreateQuery); this.executeQueryNoResult(clustersIndexCreate); } try { this.executeQueryNoResult("SELECT * FROM resolved_ips_" + logDateTable + " limit 1", true); } catch (Exception e) { this.executeQueryNoResult(resolvedIPSCreateQuery); this.executeQueryNoResult(resolvedIPSIndexCreate); } try { this.executeQueryNoResult("SELECT * FROM cluster_resolved_ips_" + logDateTable + " limit 1", true); } catch (Exception e) { this.executeQueryNoResult(clusterResolvedIPSCreateQuery); this.executeQueryNoResult(clusterResolvedIPSIndexCreate); } try { this.executeQueryNoResult("SELECT * FROM cluster_feature_vectors_" + logDateTable + " limit 1", true); } catch (Exception e) { this.executeQueryNoResult(clusterFeaturesCreateQuery); this.executeQueryNoResult(clusterFeaturesIndexCreate); } } /** * @see edu.uga.cs.fluxbuster.db.DBInterface#initAllTables(java.util.Date) */ @Override public void initAllTables(Date logdate) { this.initClusterTables(logdate); this.initSimilarityTables(logdate); this.initClassificationTables(logdate); } /** * @see edu.uga.cs.fluxbuster.db.DBInterface#getClusters(java.util.Date) */ @Override public List<StoredDomainCluster> getClusters(Date logdate) { return this.getClusters(logdate, getClusterIds(logdate)); } /** * @see edu.uga.cs.fluxbuster.db.DBInterface#getClusters(java.util.Date, edu.uga.cs.fluxbuster.classification.ClusterClass) */ public List<StoredDomainCluster> getClusters(Date logdate, ClusterClass cls) { return this.getClusters(logdate, getClusterIds(logdate, cls)); } /** * @see edu.uga.cs.fluxbuster.db.DBInterface#getClusters(java.util.Date, int) */ public List<StoredDomainCluster> getClusters(Date logdate, int minCardinality) { return this.getClusters(logdate, getClusterIds(logdate, minCardinality)); } /** * Get the domain clusters whose cluster id is within the supplied list. * * @param logdate the run date of the cluster * @param clusterIds the list of cluster ids * @return this list of clusters */ private List<StoredDomainCluster> getClusters(Date logdate, List<Integer> clusterIds) { List<StoredDomainCluster> retval = new ArrayList<StoredDomainCluster>(); for (Integer clusterId : clusterIds) { StoredDomainCluster cluster = getCluster(logdate, clusterId); if (cluster != null) { retval.add(cluster); } } return retval; } /** * @see edu.uga.cs.fluxbuster.db.DBInterface#getCluster(java.util.Date, int) */ @Override public StoredDomainCluster getCluster(Date logdate, int clusterId) { try { List<Double> features = getClusterFeatures(logdate, clusterId); return new StoredDomainCluster(clusterId, logdate, getClusterDomains(logdate, clusterId), getClusterIps(logdate, clusterId), getClusterClass(logdate, clusterId), features.get(0), features.get(1), features.get(2), features.get(3), features.get(4), features.get(5)); } catch (SQLException e) { if (log.isErrorEnabled()) { log.error("Unable to load cluster with id " + clusterId, e); } return null; } } /** * Get the features needed for cluster classification. * * @param logdate the run date of the cluster * @param clusterId the cluster's id * @return the cluster features * @throws SQLException */ private List<Double> getClusterFeatures(Date logdate, int clusterId) throws SQLException { List<Double> retval = new ArrayList<Double>(); String tabDateStr = dateFormatTable.format(logdate); String query = "SELECT network_cardinality, ip_diversity, domains_per_network, " + "number_of_domains, ttl_per_domain, ip_growth_ratio FROM " + "cluster_feature_vectors_" + tabDateStr + " WHERE cluster_id = " + clusterId; ResultSet rs = this.executeQueryWithResult(query); try { if (rs.next()) { for (int i = 1; i <= 6; i++) { retval.add(rs.getDouble(i)); } } } catch (SQLException e) { if (rs != null && !rs.isClosed()) { rs.close(); } throw e; } return retval; } /** * Get a clusters classification. * * @param logdate the run date of the cluster * @param clusterId the cluster's id * @return the clusters classification * @throws SQLException */ private ClusterClass getClusterClass(Date logdate, int clusterId) throws SQLException { String logDateTable = dateFormatTable.format(logdate); String query = "select class from cluster_classes_" + logDateTable + " where cluster_id = " + clusterId; ResultSet rs = executeQueryWithResult(query); try { if (rs.next()) { return ClusterClass.valueOf(rs.getString(1).toUpperCase()); } else { return ClusterClass.NONE; } } catch (SQLException e) { if (rs != null && !rs.isClosed()) { rs.close(); } throw e; } } /** * Get the IP addresses that belong to a cluster. * * @param logdate the run date of the cluster * @param clusterId the cluster's id * @return the set of ip addresses belonging to the cluster * @throws SQLException */ private Set<InetAddress> getClusterIps(Date logdate, int clusterId) throws SQLException { Set<InetAddress> retval = new HashSet<InetAddress>(); String logDateTable = dateFormatTable.format(logdate); String query = "select distinct cluster_resolved_ips.resolved_ip from " + "clusters_" + logDateTable + " as clusters, cluster_resolved_ips_" + logDateTable + " as cluster_resolved_ips where clusters.cluster_id = " + clusterId + " and clusters.cluster_id = cluster_resolved_ips.cluster_id"; ResultSet rs = executeQueryWithResult(query); try { while (rs.next()) { try { retval.add(InetAddress.getByName(rs.getString(1))); } catch (UnknownHostException e) { if (log.isErrorEnabled()) { log.error("", e); } } } } catch (SQLException e) { if (rs != null && !rs.isClosed()) { rs.close(); } throw e; } return retval; } /** * Get the domains that belong to a cluster. * * @param logdate the run date of the cluster * @param clusterId the cluster's id * @return the set of domain names addresses belonging to the cluster * @throws SQLException */ private Set<String> getClusterDomains(Date logdate, int clusterId) throws SQLException { Set<String> retval = new HashSet<String>(); String logDateTable = dateFormatTable.format(logdate); String query = "select domains.domain_name from clusters_" + logDateTable + " as clusters, domains_" + logDateTable + " as domains where " + "clusters.cluster_id = " + clusterId + " and clusters.domain_id = " + "domains.domain_id"; ResultSet rs = executeQueryWithResult(query); try { while (rs.next()) { retval.add(DomainNameUtils.reverseDomainName(rs.getString(1))); } } catch (SQLException e) { if (rs != null && !rs.isClosed()) { rs.close(); } throw e; } return retval; } /** * @see edu.uga.cs.fluxbuster.db.DBInterface#storeClusters(java.util.List, java.lang.String, java.util.Date) */ @Override public void storeClusters(List<DomainCluster> clusters, String sensorname, Date logdate) { String logDateTable = dateFormatTable.format(logdate); Connection con = null; PreparedStatement domainsInsertStmt = null; PreparedStatement domainsSelectStmt = null; PreparedStatement clustersInsertStmt = null; PreparedStatement resolvedIPSInsertStmt = null; PreparedStatement clusterResolvedIPSInsertStmt = null; PreparedStatement clusterFeatureVectorsInsertStmt = null; try { con = this.getConnection(); domainsInsertStmt = con .prepareStatement("INSERT INTO domains_" + logDateTable + " VALUES(DEFAULT, ?, ?, ?)"); domainsSelectStmt = con .prepareStatement("SELECT domain_id FROM domains_" + logDateTable + " WHERE domain_name = ?"); clustersInsertStmt = con .prepareStatement("INSERT INTO clusters_" + logDateTable + " VALUES " + "(?, ?, ?, ?)"); resolvedIPSInsertStmt = con .prepareStatement("INSERT INTO resolved_ips_" + logDateTable + " VALUES " + "( ?, ?, inet(?))"); clusterResolvedIPSInsertStmt = con.prepareStatement( "INSERT INTO cluster_resolved_ips_" + logDateTable + " VALUES " + "( ?, ?, ?, inet(?))"); clusterFeatureVectorsInsertStmt = con.prepareStatement("INSERT INTO cluster_feature_vectors_" + logDateTable + "(cluster_id, sensor_name, log_date, network_cardinality, ip_diversity, " + "number_of_domains, ttl_per_domain, ip_growth_ratio, queries_per_domain, avg_last_growth_ratio_single_entry, " + "avg_last_growth_ratio_entries, avg_last_growth_prefix_ratio_entries, last_growth_ratio_cluster," + "last_growth_prefix_ratio_cluster) VALUES( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); int clusterId = 1; for (DomainCluster cluster : clusters) { for (CandidateFluxDomain candidateDomain : cluster.getCandidateDomains()) { String domainName = filterChars(candidateDomain.getDomainName()); String domainNameRev = DomainNameUtils.reverseDomainName(domainName); String secondLevelDomainName = DomainNameUtils.extractEffective2LD(domainName); String secondLevelDomainNameRev = null; if (secondLevelDomainName != null) { secondLevelDomainNameRev = DomainNameUtils.reverseDomainName(secondLevelDomainName); } else { secondLevelDomainNameRev = DomainNameUtils.reverseDomainName(domainName); } domainsInsertStmt.setString(1, domainNameRev); domainsInsertStmt.setDate(2, new java.sql.Date(logdate.getTime())); domainsInsertStmt.setString(3, secondLevelDomainNameRev); executePreparedStatementNoResult(con, domainsInsertStmt); domainsSelectStmt.setString(1, domainNameRev); ResultSet rs = this.executePreparedStatementWithResult(con, domainsSelectStmt); try { if (rs.next()) { int domainId = rs.getInt(1); clustersInsertStmt.setInt(1, clusterId); clustersInsertStmt.setInt(2, domainId); clustersInsertStmt.setString(3, sensorname); clustersInsertStmt.setDate(4, new java.sql.Date(logdate.getTime())); this.executePreparedStatementNoResult(con, clustersInsertStmt); for (InetAddress resolvedIP : candidateDomain.getIps()) { resolvedIPSInsertStmt.setInt(1, domainId); resolvedIPSInsertStmt.setDate(2, new java.sql.Date(logdate.getTime())); resolvedIPSInsertStmt.setString(3, resolvedIP.getHostAddress()); this.executePreparedStatementNoResult(con, resolvedIPSInsertStmt); } } } catch (SQLException ex) { if (log.isErrorEnabled()) { log.error("", ex); } } finally { rs.close(); } } /*String nickname = getNicknames((List<String>)cluster.getDomains()); insertQuery = "INSERT INTO cluster_nicknames_"+ logDateTable +" VALUES" + "("+clusterId+", '"+sensorname+"', '"+logDateStr+"', '"+nickname+"')"; performInsertQuery(insertQuery, clusterNicknamesCreateQuery);*/ for (InetAddress resolvedIP : cluster.getIps()) { clusterResolvedIPSInsertStmt.setInt(1, clusterId); clusterResolvedIPSInsertStmt.setString(2, sensorname); clusterResolvedIPSInsertStmt.setDate(3, new java.sql.Date(logdate.getTime())); clusterResolvedIPSInsertStmt.setString(4, resolvedIP.getHostAddress()); this.executePreparedStatementNoResult(con, clusterResolvedIPSInsertStmt); } clusterFeatureVectorsInsertStmt.setInt(1, clusterId); clusterFeatureVectorsInsertStmt.setString(2, sensorname); clusterFeatureVectorsInsertStmt.setDate(3, new java.sql.Date(logdate.getTime())); clusterFeatureVectorsInsertStmt.setInt(4, cluster.getIps().size()); clusterFeatureVectorsInsertStmt.setDouble(5, cluster.getIpDiversity()); clusterFeatureVectorsInsertStmt.setInt(6, cluster.getDomains().size()); clusterFeatureVectorsInsertStmt.setDouble(7, cluster.getAvgTTLPerDomain()); clusterFeatureVectorsInsertStmt.setDouble(8, cluster.getIpGrowthRatio()); clusterFeatureVectorsInsertStmt.setDouble(9, cluster.getQueriesPerDomain()); Double temp = cluster.getAvgLastGrowthRatioSingleEntry(); if (temp == null) { clusterFeatureVectorsInsertStmt.setNull(10, java.sql.Types.REAL); } else { clusterFeatureVectorsInsertStmt.setDouble(10, temp); } temp = cluster.getAvgLastGrowthRatioEntries(); if (temp == null) { clusterFeatureVectorsInsertStmt.setNull(11, java.sql.Types.REAL); } else { clusterFeatureVectorsInsertStmt.setDouble(11, temp); } temp = cluster.getAvgLastGrowthPrefixRatioEntries(); if (temp == null) { clusterFeatureVectorsInsertStmt.setNull(12, java.sql.Types.REAL); } else { clusterFeatureVectorsInsertStmt.setDouble(12, temp); } temp = cluster.getLastGrowthRatioCluster(); if (temp == null) { clusterFeatureVectorsInsertStmt.setNull(13, java.sql.Types.REAL); } else { clusterFeatureVectorsInsertStmt.setDouble(13, temp); } temp = cluster.getLastGrowthPrefixRatioCluster(); if (temp == null) { clusterFeatureVectorsInsertStmt.setNull(14, java.sql.Types.REAL); } else { clusterFeatureVectorsInsertStmt.setDouble(14, temp); } this.executePreparedStatementNoResult(con, clusterFeatureVectorsInsertStmt); clusterId++; } } catch (SQLException e) { if (log.isErrorEnabled()) { log.error("", e); } } finally { try { if (domainsInsertStmt != null && !domainsInsertStmt.isClosed()) { domainsInsertStmt.close(); } if (domainsSelectStmt != null && !domainsSelectStmt.isClosed()) { domainsSelectStmt.close(); } if (clustersInsertStmt != null && !clustersInsertStmt.isClosed()) { clustersInsertStmt.close(); } if (resolvedIPSInsertStmt != null && !resolvedIPSInsertStmt.isClosed()) { resolvedIPSInsertStmt.close(); } if (clusterResolvedIPSInsertStmt != null && !clusterResolvedIPSInsertStmt.isClosed()) { clusterResolvedIPSInsertStmt.close(); } if (clusterFeatureVectorsInsertStmt != null && !clusterFeatureVectorsInsertStmt.isClosed()) { clusterFeatureVectorsInsertStmt.close(); } if (con != null && !con.isClosed()) { con.close(); } } catch (SQLException e) { e.printStackTrace(); } } } /** * Filter characters out of a domain name. All characters are * filtered except for [a-zA-Z0-9+-.]. * * @param domainName the domain name to filter * @return the filtered domain name */ private String filterChars(String domainName) { String returnValue = ""; for (char val : domainName.toCharArray()) { if (Character.isLetterOrDigit(val) || val == '+' || val == '-' || val == '.') { returnValue += val; } } return returnValue; } /** * Gets the nickname for each of the supplied domain names. NOTE: this * method has not been implemented. * * @param domainNames the domain names * @return the list of nicknames */ //TODO implement or remove if not necessary public List<String> getNicknames(List<String> domainNames) { throw new UnsupportedOperationException(); } /** * @see edu.uga.cs.fluxbuster.db.DBInterface#executeQueryWithResult(java.lang.String) */ @Override public ResultSet executeQueryWithResult(String query) { ResultSet retval = null; Connection con = null; Statement stmt = null; try { con = this.getConnection(); con.setAutoCommit(false); stmt = con.createStatement(); retval = stmt.executeQuery(query); con.commit(); } catch (SQLException e) { retval = null; if (log.isErrorEnabled()) { log.error(query, e); } try { if (con != null && !con.isClosed()) { con.rollback(); } } catch (SQLException e1) { if (log.isErrorEnabled()) { log.error("Error during rollback.", e1); } } } finally { try { if (con != null && !con.isClosed()) { con.setAutoCommit(true); con.close(); } } catch (SQLException e) { if (log.isErrorEnabled()) { log.error("Error during close.", e); } } } return retval; } /** * @see edu.uga.cs.fluxbuster.db.DBInterface#executeQueryNoResult(java.lang.String) */ @Override public void executeQueryNoResult(String query) { try { executeQueryNoResult(query, false); } catch (Exception ex) { } } /** * Execute query with no result. * * @param query the query * @param giveException throws any exception generated if true, * if false all exceptions are consumed * @throws Exception if giveException is true and their is an error executing * the query */ public void executeQueryNoResult(String query, boolean giveException) throws Exception { Connection con = null; Statement stmt = null; SQLException exc = null; try { con = this.getConnection(); con.setAutoCommit(false); stmt = con.createStatement(); stmt.execute(query); con.commit(); } catch (SQLException e) { if (!giveException) { if (log.isErrorEnabled()) { log.error(query, e); } } if (con != null) { try { con.rollback(); } catch (SQLException e1) { if (log.isErrorEnabled()) { log.error("Error during rollback.", e1); } } } if (giveException) { exc = e; } } finally { try { if (con != null && !con.isClosed()) { con.setAutoCommit(true); con.close(); } } catch (SQLException e) { if (log.isErrorEnabled()) { log.error("Error during close.", e); } } if (exc != null && giveException) { throw exc; } } } /** * Executes a PostgresSQL copy command. * * @param query the copy command to execute * @param reader the containing the data to be copied */ private void executeCopyIn(String query, Reader reader) { Connection con = null; CopyManager manager = null; try { con = this.getConnection(); con.setAutoCommit(false); if (con instanceof com.jolbox.bonecp.ConnectionHandle) { ConnectionHandle handle = (ConnectionHandle) con; manager = new CopyManager((BaseConnection) handle.getInternalConnection()); } else { manager = new CopyManager((BaseConnection) con); } manager.copyIn(query, reader); con.commit(); } catch (Exception e) { if (log.isErrorEnabled()) { log.error(query, e); } if (con != null) { try { con.rollback(); } catch (SQLException e1) { if (log.isErrorEnabled()) { log.error("Error during rollback.", e1); } } } } finally { try { if (con != null && !con.isClosed()) { con.setAutoCommit(true); con.close(); } } catch (SQLException e) { if (log.isErrorEnabled()) { log.error("Error during close.", e); } } } } /** * Executes a prepared statement with a result. * * @param con the connection to the database * @param stmt the prepared statement to execute * @return the result of the query */ private ResultSet executePreparedStatementWithResult(Connection con, PreparedStatement stmt) { ResultSet retval = null; try { con.setAutoCommit(false); retval = stmt.executeQuery(); con.commit(); } catch (SQLException e) { if (log.isErrorEnabled()) { log.error("", e); } if (con != null) { try { con.rollback(); } catch (SQLException e1) { if (log.isErrorEnabled()) { log.error("Error during rollback.", e1); } } } } finally { try { if (con != null && !con.isClosed()) { con.setAutoCommit(true); } } catch (SQLException e) { if (log.isErrorEnabled()) { log.error("Error setting auto commit.", e); } } } return retval; } /** * Executes a prepared statement with no result. * * @param con the connection to the database * @param stmt the prepared statement to execute */ private void executePreparedStatementNoResult(Connection con, PreparedStatement stmt) { try { con.setAutoCommit(false); stmt.execute(); con.commit(); } catch (SQLException e) { if (log.isErrorEnabled()) { log.error("", e); } if (con != null) { try { con.rollback(); } catch (SQLException e1) { if (log.isErrorEnabled()) { log.error("Error during rollback.", e1); } } } } finally { try { if (con != null && !con.isClosed()) { con.setAutoCommit(true); } } catch (SQLException e) { if (log.isErrorEnabled()) { log.error("Error setting auto commit.", e); } } } } /** * @see edu.uga.cs.fluxbuster.db.DBInterface#getClusterIds(java.util.Date, edu.uga.cs.fluxbuster.classification.ClusterClass) */ @Override public List<Integer> getClusterIds(Date logdate, ClusterClass cls) { List<Integer> retval = new ArrayList<Integer>(); String logDateTable = dateFormatTable.format(logdate); String query; if (cls != ClusterClass.NONE) { query = "select cluster_id from cluster_classes_" + logDateTable + " where class = '" + cls + "'"; } else { query = "select distinct clusters.cluster_id from clusters_" + logDateTable + " as clusters left outer join cluster_classes_" + logDateTable + " as cluster_classes on clusters.cluster_id = " + "cluster_classes.cluster_id where cluster_classes.class is NULL"; } ResultSet rs = executeQueryWithResult(query); try { while (rs.next()) { retval.add(rs.getInt(1)); } } catch (Exception e) { if (log.isErrorEnabled()) { log.error("Error retrieving cluster ids.", e); } } finally { try { if (rs != null && !rs.isClosed()) { rs.close(); } } catch (SQLException e) { if (log.isErrorEnabled()) { log.error(e); } } } return retval; } /** * @see edu.uga.cs.fluxbuster.db.DBInterface#getClusterIds(java.util.Date, int) */ @Override public List<Integer> getClusterIds(Date logdate, int minCardinality) { List<Integer> retval = new ArrayList<Integer>(); String tabDateStr = dateFormatTable.format(logdate); String query = "SELECT cluster_id FROM cluster_feature_vectors_" + tabDateStr + " WHERE network_cardinality >= " + minCardinality; ResultSet rs = executeQueryWithResult(query); try { while (rs.next()) { retval.add(rs.getInt(1)); } } catch (Exception e) { if (log.isErrorEnabled()) { log.error("Error retrieving cluster ids.", e); } } finally { try { if (rs != null && !rs.isClosed()) { rs.close(); } } catch (SQLException e) { if (log.isErrorEnabled()) { log.error(e); } } } return retval; } /** * @see edu.uga.cs.fluxbuster.db.DBInterface#getClusterIds(java.util.Date) */ @Override public List<Integer> getClusterIds(Date logdate) { ArrayList<Integer> retval = new ArrayList<Integer>(); String logDateTable = dateFormatTable.format(logdate); String query = "select distinct cluster_id from clusters_" + logDateTable; ResultSet rs = executeQueryWithResult(query); try { while (rs.next()) { retval.add(rs.getInt(1)); } } catch (Exception e) { if (log.isErrorEnabled()) { log.error("Error retrieving cluster ids.", e); } } finally { try { if (rs != null && !rs.isClosed()) { rs.close(); } } catch (SQLException e) { if (log.isErrorEnabled()) { log.error(e); } } } return retval; } /** * @see edu.uga.cs.fluxbuster.db.DBInterface#storeIpClusterSimilarities(java.util.List) */ @Override public void storeIpClusterSimilarities(List<ClusterSimilarity> sims) { storeClusterSimilarities(sims, ClusterSimilarityCalculator.SIM_TYPE.IP); } /** * @see edu.uga.cs.fluxbuster.db.DBInterface#storeDomainnameClusterSimilarities(java.util.List) */ @Override public void storeDomainnameClusterSimilarities(List<ClusterSimilarity> sims) { storeClusterSimilarities(sims, ClusterSimilarityCalculator.SIM_TYPE.DOMAINNAME); } /** * Store cluster similarities in the database. * * @param sims the similarities to store * @param type the type of similarity */ private void storeClusterSimilarities(List<ClusterSimilarity> sims, ClusterSimilarityCalculator.SIM_TYPE type) { String format = "%d\t%d\t%f\t\'%s\'\t\'%s\'\n"; StringBuffer databuf = new StringBuffer(); Formatter formatter = new Formatter(databuf); if (sims.size() > 0) { String tabletype = ""; switch (type) { case IP: tabletype = "ip"; break; case DOMAINNAME: tabletype = "domainname"; break; } String tabDateStr = dateFormatTable.format(sims.get(0).getADate()); String copyQuery = "COPY cluster_" + tabletype + "_similarity_" + tabDateStr + " (cluster_id, candidate_cluster_id, " + "similarity, log_date, candidate_log_date ) FROM stdin;"; for (ClusterSimilarity s : sims) { formatter.format(format, s.getAClusterId(), s.getBClusterId(), s.getSim(), dateFormatStr.format(s.getADate()), dateFormatStr.format(s.getBDate())); } this.executeCopyIn(copyQuery, new StringReader(databuf.toString())); } formatter.close(); } /** * @see edu.uga.cs.fluxbuster.db.DBInterface#storeClusterClasses(java.util.Date, java.util.Map, boolean) */ @Override public void storeClusterClasses(Date logdate, Map<ClusterClass, List<StoredDomainCluster>> clusterClasses, boolean validated) { String logDateTable = dateFormatTable.format(logdate); Connection con = null; PreparedStatement clusterClassesInsertStmt = null; try { con = this.getConnection(); clusterClassesInsertStmt = con.prepareStatement( "INSERT INTO cluster_classes_" + logDateTable + " VALUES (?, 'SIE', ?, ?, ?)"); for (ClusterClass clusclass : clusterClasses.keySet()) { for (StoredDomainCluster cluster : clusterClasses.get(clusclass)) { clusterClassesInsertStmt.setInt(1, cluster.getClusterId()); clusterClassesInsertStmt.setDate(2, new java.sql.Date(logdate.getTime())); clusterClassesInsertStmt.setString(3, clusclass.toString()); clusterClassesInsertStmt.setBoolean(4, validated); this.executePreparedStatementNoResult(con, clusterClassesInsertStmt); } } } catch (SQLException e) { if (log.isErrorEnabled()) { log.error("Error storing cluster classes.", e); } } finally { try { if (clusterClassesInsertStmt != null && !clusterClassesInsertStmt.isClosed()) { clusterClassesInsertStmt.close(); } } catch (SQLException e) { if (log.isErrorEnabled()) { log.error("e"); } } try { if (con != null && !con.isClosed()) { con.close(); } } catch (SQLException e) { if (log.isErrorEnabled()) { log.error("e"); } } } } }