19 package org.sleuthkit.autopsy.centralrepository.datamodel;
21 import java.sql.Connection;
22 import java.sql.DriverManager;
23 import java.sql.PreparedStatement;
24 import java.sql.ResultSet;
25 import java.sql.SQLException;
26 import java.sql.Statement;
27 import java.util.List;
28 import java.util.Properties;
29 import java.util.logging.Level;
30 import java.util.regex.Pattern;
69 if (host == null || host.isEmpty()) {
75 if (portString == null || portString.isEmpty()) {
78 port = Integer.parseInt(portString);
79 if (port < 0 || port > 65535) {
83 }
catch (NumberFormatException ex) {
88 if (dbName == null || dbName.isEmpty()) {
94 if (bulkThresholdString == null || bulkThresholdString.isEmpty()) {
95 this.bulkThreshold = AbstractSqlEamDb.DEFAULT_BULK_THRESHHOLD;
97 this.bulkThreshold = Integer.parseInt(bulkThresholdString);
98 if (getBulkThreshold() <= 0) {
99 this.bulkThreshold = AbstractSqlEamDb.DEFAULT_BULK_THRESHHOLD;
102 }
catch (NumberFormatException ex) {
103 this.bulkThreshold = AbstractSqlEamDb.DEFAULT_BULK_THRESHHOLD;
107 if (userName == null || userName.isEmpty()) {
112 if (password == null || password.isEmpty()) {
118 LOGGER.log(Level.WARNING,
"Failed to convert password from hex text to text.", ex);
133 LOGGER.log(Level.SEVERE,
"Failed to convert password from text to hex text.", ex);
145 String getConnectionURL(
boolean usePostgresDb) {
146 StringBuilder url =
new StringBuilder();
147 url.append(getJDBCBaseURI());
151 url.append(
"postgres");
156 return url.toString();
168 String url = getConnectionURL(usePostgresDb);
169 Properties props =
new Properties();
173 Class.forName(getDriver());
174 conn = DriverManager.getConnection(url, props);
175 }
catch (ClassNotFoundException | SQLException ex) {
178 LOGGER.log(Level.SEVERE,
"Failed to acquire ephemeral connection to postgresql.");
212 String sql =
"SELECT datname FROM pg_catalog.pg_database WHERE lower(datname) = lower(?) LIMIT 1";
213 PreparedStatement ps = null;
216 ps = conn.prepareStatement(sql);
218 rs = ps.executeQuery();
222 }
catch (SQLException ex) {
223 LOGGER.log(Level.SEVERE,
"Failed to execute database existance query.", ex);
257 String sql =
"CREATE DATABASE %s OWNER %s";
260 stmt = conn.createStatement();
262 }
catch (SQLException ex) {
263 LOGGER.log(Level.SEVERE,
"Failed to execute create database statement.", ex);
278 String sql =
"DROP DATABASE %s";
281 stmt = conn.createStatement();
282 stmt.execute(String.format(sql,
getDbName()));
283 }
catch (SQLException ex) {
284 LOGGER.log(Level.SEVERE,
"Failed to execute drop database statement.", ex);
310 StringBuilder createOrganizationsTable =
new StringBuilder();
311 createOrganizationsTable.append(
"CREATE TABLE IF NOT EXISTS organizations (");
312 createOrganizationsTable.append(
"id SERIAL PRIMARY KEY,");
313 createOrganizationsTable.append(
"org_name text NOT NULL,");
314 createOrganizationsTable.append(
"poc_name text NOT NULL,");
315 createOrganizationsTable.append(
"poc_email text NOT NULL,");
316 createOrganizationsTable.append(
"poc_phone text NOT NULL,");
317 createOrganizationsTable.append(
"CONSTRAINT org_name_unique UNIQUE (org_name)");
318 createOrganizationsTable.append(
")");
322 StringBuilder createCasesTable =
new StringBuilder();
323 createCasesTable.append(
"CREATE TABLE IF NOT EXISTS cases (");
324 createCasesTable.append(
"id SERIAL PRIMARY KEY,");
325 createCasesTable.append(
"case_uid text NOT NULL,");
326 createCasesTable.append(
"org_id integer,");
327 createCasesTable.append(
"case_name text NOT NULL,");
328 createCasesTable.append(
"creation_date text NOT NULL,");
329 createCasesTable.append(
"case_number text,");
330 createCasesTable.append(
"examiner_name text,");
331 createCasesTable.append(
"examiner_email text,");
332 createCasesTable.append(
"examiner_phone text,");
333 createCasesTable.append(
"notes text,");
334 createCasesTable.append(
"foreign key (org_id) references organizations(id) ON UPDATE SET NULL ON DELETE SET NULL,");
335 createCasesTable.append(
"CONSTRAINT case_uid_unique UNIQUE (case_uid)");
336 createCasesTable.append(
")");
339 String casesIdx1 =
"CREATE INDEX IF NOT EXISTS cases_org_id ON cases (org_id)";
340 String casesIdx2 =
"CREATE INDEX IF NOT EXISTS cases_case_uid ON cases (case_uid)";
342 StringBuilder createDataSourcesTable =
new StringBuilder();
343 createDataSourcesTable.append(
"CREATE TABLE IF NOT EXISTS data_sources (");
344 createDataSourcesTable.append(
"id SERIAL PRIMARY KEY,");
345 createDataSourcesTable.append(
"case_id integer NOT NULL,");
346 createDataSourcesTable.append(
"device_id text NOT NULL,");
347 createDataSourcesTable.append(
"name text NOT NULL,");
348 createDataSourcesTable.append(
"datasource_obj_id integer,");
349 createDataSourcesTable.append(
"md5 text DEFAULT NULL,");
350 createDataSourcesTable.append(
"sha1 text DEFAULT NULL,");
351 createDataSourcesTable.append(
"sha256 text DEFAULT NULL,");
352 createDataSourcesTable.append(
"foreign key (case_id) references cases(id) ON UPDATE SET NULL ON DELETE SET NULL,");
353 createDataSourcesTable.append(
"CONSTRAINT datasource_unique UNIQUE (case_id, device_id, name)");
354 createDataSourcesTable.append(
")");
356 String dataSourceIdx1 =
"CREATE INDEX IF NOT EXISTS data_sources_name ON data_sources (name)";
357 String dataSourceIdx2 =
"CREATE INDEX IF NOT EXISTS data_sources_object_id ON data_sources (datasource_obj_id)";
359 StringBuilder createReferenceSetsTable =
new StringBuilder();
360 createReferenceSetsTable.append(
"CREATE TABLE IF NOT EXISTS reference_sets (");
361 createReferenceSetsTable.append(
"id SERIAL PRIMARY KEY,");
362 createReferenceSetsTable.append(
"org_id integer NOT NULL,");
363 createReferenceSetsTable.append(
"set_name text NOT NULL,");
364 createReferenceSetsTable.append(
"version text NOT NULL,");
365 createReferenceSetsTable.append(
"known_status integer NOT NULL,");
366 createReferenceSetsTable.append(
"read_only boolean NOT NULL,");
367 createReferenceSetsTable.append(
"type integer NOT NULL,");
368 createReferenceSetsTable.append(
"import_date text NOT NULL,");
369 createReferenceSetsTable.append(
"foreign key (org_id) references organizations(id) ON UPDATE SET NULL ON DELETE SET NULL,");
370 createReferenceSetsTable.append(
"CONSTRAINT hash_set_unique UNIQUE (set_name, version)");
371 createReferenceSetsTable.append(
")");
373 String referenceSetsIdx1 =
"CREATE INDEX IF NOT EXISTS reference_sets_org_id ON reference_sets (org_id)";
376 StringBuilder createReferenceTypesTableTemplate =
new StringBuilder();
377 createReferenceTypesTableTemplate.append(
"CREATE TABLE IF NOT EXISTS %s (");
378 createReferenceTypesTableTemplate.append(
"id SERIAL PRIMARY KEY,");
379 createReferenceTypesTableTemplate.append(
"reference_set_id integer,");
380 createReferenceTypesTableTemplate.append(
"value text NOT NULL,");
381 createReferenceTypesTableTemplate.append(
"known_status integer NOT NULL,");
382 createReferenceTypesTableTemplate.append(
"comment text,");
383 createReferenceTypesTableTemplate.append(
"CONSTRAINT %s_multi_unique UNIQUE (reference_set_id, value),");
384 createReferenceTypesTableTemplate.append(
"foreign key (reference_set_id) references reference_sets(id) ON UPDATE SET NULL ON DELETE SET NULL");
385 createReferenceTypesTableTemplate.append(
")");
388 String referenceTypesIdx1 =
"CREATE INDEX IF NOT EXISTS %s_value ON %s (value)";
389 String referenceTypesIdx2 =
"CREATE INDEX IF NOT EXISTS %s_value_known_status ON %s (value, known_status)";
391 StringBuilder createCorrelationTypesTable =
new StringBuilder();
392 createCorrelationTypesTable.append(
"CREATE TABLE IF NOT EXISTS correlation_types (");
393 createCorrelationTypesTable.append(
"id SERIAL PRIMARY KEY,");
394 createCorrelationTypesTable.append(
"display_name text NOT NULL,");
395 createCorrelationTypesTable.append(
"db_table_name text NOT NULL,");
396 createCorrelationTypesTable.append(
"supported integer NOT NULL,");
397 createCorrelationTypesTable.append(
"enabled integer NOT NULL,");
398 createCorrelationTypesTable.append(
"CONSTRAINT correlation_types_names UNIQUE (display_name, db_table_name)");
399 createCorrelationTypesTable.append(
")");
401 String createArtifactInstancesTableTemplate = getCreateArtifactInstancesTableTemplate();
403 String instancesCaseIdIdx = getAddCaseIdIndexTemplate();
404 String instancesDatasourceIdIdx = getAddDataSourceIdIndexTemplate();
405 String instancesValueIdx = getAddValueIndexTemplate();
406 String instancesKnownStatusIdx = getAddKnownStatusIndexTemplate();
407 String instancesObjectIdIdx = getAddObjectIdIndexTemplate();
411 Connection conn = null;
417 Statement stmt = conn.createStatement();
419 stmt.execute(createOrganizationsTable.toString());
421 stmt.execute(createCasesTable.toString());
422 stmt.execute(casesIdx1);
423 stmt.execute(casesIdx2);
425 stmt.execute(createDataSourcesTable.toString());
426 stmt.execute(dataSourceIdx1);
427 stmt.execute(dataSourceIdx2);
429 stmt.execute(createReferenceSetsTable.toString());
430 stmt.execute(referenceSetsIdx1);
432 stmt.execute(createCorrelationTypesTable.toString());
439 stmt.execute(
"CREATE TABLE db_info (id SERIAL, name TEXT UNIQUE NOT NULL, value TEXT NOT NULL)");
440 stmt.execute(
"INSERT INTO db_info (name, value) VALUES ('" + AbstractSqlEamDb.SCHEMA_MAJOR_VERSION_KEY +
"', '" + CURRENT_DB_SCHEMA_VERSION.getMajor() +
"')");
441 stmt.execute(
"INSERT INTO db_info (name, value) VALUES ('" + AbstractSqlEamDb.SCHEMA_MINOR_VERSION_KEY +
"', '" + CURRENT_DB_SCHEMA_VERSION.getMinor() +
"')");
442 stmt.execute(
"INSERT INTO db_info (name, value) VALUES ('" + AbstractSqlEamDb.CREATION_SCHEMA_MAJOR_VERSION_KEY +
"', '" + CURRENT_DB_SCHEMA_VERSION.getMajor() +
"')");
443 stmt.execute(
"INSERT INTO db_info (name, value) VALUES ('" + AbstractSqlEamDb.CREATION_SCHEMA_MINOR_VERSION_KEY +
"', '" + CURRENT_DB_SCHEMA_VERSION.getMinor() +
"')");
448 String reference_type_dbname;
449 String instance_type_dbname;
454 stmt.execute(String.format(createArtifactInstancesTableTemplate, instance_type_dbname, instance_type_dbname));
455 stmt.execute(String.format(instancesCaseIdIdx, instance_type_dbname, instance_type_dbname));
456 stmt.execute(String.format(instancesDatasourceIdIdx, instance_type_dbname, instance_type_dbname));
457 stmt.execute(String.format(instancesValueIdx, instance_type_dbname, instance_type_dbname));
458 stmt.execute(String.format(instancesKnownStatusIdx, instance_type_dbname, instance_type_dbname));
459 stmt.execute(String.format(instancesObjectIdIdx, instance_type_dbname, instance_type_dbname));
463 stmt.execute(String.format(createReferenceTypesTableTemplate.toString(), reference_type_dbname, reference_type_dbname));
464 stmt.execute(String.format(referenceTypesIdx1, reference_type_dbname, reference_type_dbname));
465 stmt.execute(String.format(referenceTypesIdx2, reference_type_dbname, reference_type_dbname));
469 }
catch (SQLException ex) {
470 LOGGER.log(Level.SEVERE,
"Error initializing db schema.", ex);
473 LOGGER.log(Level.SEVERE,
"Error getting default correlation types. Likely due to one or more Type's with an invalid db table name.");
488 static String getCreateArtifactInstancesTableTemplate() {
490 StringBuilder createArtifactInstancesTableTemplate =
new StringBuilder();
491 createArtifactInstancesTableTemplate.append(
"CREATE TABLE IF NOT EXISTS %s (");
492 createArtifactInstancesTableTemplate.append(
"id SERIAL PRIMARY KEY,");
493 createArtifactInstancesTableTemplate.append(
"case_id integer NOT NULL,");
494 createArtifactInstancesTableTemplate.append(
"data_source_id integer NOT NULL,");
495 createArtifactInstancesTableTemplate.append(
"value text NOT NULL,");
496 createArtifactInstancesTableTemplate.append(
"file_path text NOT NULL,");
497 createArtifactInstancesTableTemplate.append(
"known_status integer NOT NULL,");
498 createArtifactInstancesTableTemplate.append(
"comment text,");
499 createArtifactInstancesTableTemplate.append(
"file_obj_id integer,");
500 createArtifactInstancesTableTemplate.append(
"CONSTRAINT %s_multi_unique_ UNIQUE (data_source_id, value, file_path),");
501 createArtifactInstancesTableTemplate.append(
"foreign key (case_id) references cases(id) ON UPDATE SET NULL ON DELETE SET NULL,");
502 createArtifactInstancesTableTemplate.append(
"foreign key (data_source_id) references data_sources(id) ON UPDATE SET NULL ON DELETE SET NULL");
503 createArtifactInstancesTableTemplate.append(
")");
504 return createArtifactInstancesTableTemplate.toString();
515 static String getAddCaseIdIndexTemplate() {
517 return "CREATE INDEX IF NOT EXISTS %s_case_id ON %s (case_id)";
528 static String getAddDataSourceIdIndexTemplate() {
530 return "CREATE INDEX IF NOT EXISTS %s_data_source_id ON %s (data_source_id)";
541 static String getAddValueIndexTemplate() {
543 return "CREATE INDEX IF NOT EXISTS %s_value ON %s (value)";
554 static String getAddKnownStatusIndexTemplate() {
556 return "CREATE INDEX IF NOT EXISTS %s_value_known_status ON %s (value, known_status)";
567 static String getAddObjectIdIndexTemplate() {
569 return "CREATE INDEX IF NOT EXISTS %s_file_obj_id ON %s (file_obj_id)";
584 boolean isChanged() {
592 return !host.equals(hostString) || !Integer.toString(port).equals(portString)
593 || !dbName.equals(dbNameString) || !Integer.toString(bulkThreshold).equals(bulkThresholdString)
594 || !userName.equals(userNameString) || !password.equals(userPasswordString);
608 if (null != host && !host.isEmpty()) {
626 if (port > 0 && port < 65535) {
629 throw new EamDbException(
"Invalid port. Must be a number greater than 0.");
640 return dbName.toLowerCase();
647 if (dbName == null || dbName.isEmpty()) {
648 throw new EamDbException(
"Invalid database name. Cannot be empty.");
649 }
else if (!Pattern.matches(DB_NAMES_REGEX, dbName)) {
650 throw new EamDbException(
"Invalid database name. Name must start with a lowercase letter and can only contain lowercase letters, numbers, and '_'.");
653 this.dbName = dbName.toLowerCase();
659 int getBulkThreshold() {
667 if (bulkThreshold > 0) {
685 if (userName == null || userName.isEmpty()) {
687 }
else if (!Pattern.matches(DB_USER_NAMES_REGEX, userName)) {
688 throw new EamDbException(
"Invalid user name. Name must start with a letter and can only contain letters, numbers, and '_'.");
704 if (password == null || password.isEmpty()) {
705 throw new EamDbException(
"Invalid user password. Cannot be empty.");
713 String getValidationQuery() {
727 String getJDBCBaseURI() {
static final Logger LOGGER
void setHost(String host)
static String correlationTypeToInstanceTableName(CorrelationAttributeInstance.Type type)
Connection getEphemeralConnection(boolean usePostgresDb)
static boolean schemaVersionIsSet(Connection conn)
final String VALIDATION_QUERY
static boolean executeValidationQuery(Connection conn, String validationQuery)
final String DEFAULT_DBNAME
boolean insertDefaultDatabaseContent()
boolean verifyDatabaseSchema()
final String JDBC_BASE_URI
final String DEFAULT_USERNAME
static void closeResultSet(ResultSet resultSet)
static void closeStatement(Statement statement)
static List< CorrelationAttributeInstance.Type > getDefaultCorrelationTypes()
final String DEFAULT_HOST
final String DB_NAMES_REGEX
void setPassword(String password)
final String DEFAULT_PASSWORD
static void closeConnection(Connection conn)
static synchronized void setConfigSetting(String moduleName, String settingName, String settingVal)
boolean initializeDatabaseSchema()
boolean verifyDatabaseExists()
static boolean insertDefaultCorrelationTypes(Connection conn)
static String getConfigSetting(String moduleName, String settingName)
boolean verifyConnection()
void setUserName(String userName)
void setDbName(String dbName)
synchronized static Logger getLogger(String name)
void setBulkThreshold(int bulkThreshold)
static String correlationTypeToReferenceTableName(CorrelationAttributeInstance.Type type)
static String convertTextToHexText(String property)
final String DB_USER_NAMES_REGEX
static final int FILES_TYPE_ID
static String convertHexTextToText(String property)