/**
* Database module for the ExploitDB MCP server
* Handles SQLite database operations
*/
import { open, Database } from 'sqlite';
import sqlite3 from 'sqlite3';
import config from '../config.js';
import * as fs from 'fs-extra';
import * as path from 'path';
// Database connection
let db: Database | null = null;
// Exploit interface
export interface Exploit {
id: number;
file: string;
description: string;
date: string;
author: string;
type: string;
platform: string;
port?: number;
cve?: string;
verified: boolean;
tags?: string[];
}
/**
* Initialize the database
*/
export const initDatabase = async (): Promise<void> => {
// Ensure the database directory exists
const dbDir = path.dirname(config.dbPath);
await fs.ensureDir(dbDir);
// Open the database
db = await open({
filename: config.dbPath,
driver: sqlite3.Database
});
// Enable foreign keys
await db.exec('PRAGMA foreign_keys = ON');
// Create tables if they don't exist
await createTables();
};
/**
* Create database tables
*/
const createTables = async (): Promise<void> => {
if (!db) throw new Error('Database not initialized');
// Create exploits table
await db.exec(`
CREATE TABLE IF NOT EXISTS exploits (
id INTEGER PRIMARY KEY,
file TEXT NOT NULL,
description TEXT NOT NULL,
date TEXT NOT NULL,
author TEXT NOT NULL,
type TEXT NOT NULL,
platform TEXT NOT NULL,
port INTEGER,
cve TEXT,
verified BOOLEAN NOT NULL DEFAULT 0
)
`);
// Create tags table
await db.exec(`
CREATE TABLE IF NOT EXISTS tags (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE
)
`);
// Create exploit_tags table (many-to-many relationship)
await db.exec(`
CREATE TABLE IF NOT EXISTS exploit_tags (
exploit_id INTEGER NOT NULL,
tag_id INTEGER NOT NULL,
PRIMARY KEY (exploit_id, tag_id),
FOREIGN KEY (exploit_id) REFERENCES exploits (id) ON DELETE CASCADE,
FOREIGN KEY (tag_id) REFERENCES tags (id) ON DELETE CASCADE
)
`);
// Create indexes for faster searching
await db.exec(`
CREATE INDEX IF NOT EXISTS idx_exploits_description ON exploits (description);
CREATE INDEX IF NOT EXISTS idx_exploits_cve ON exploits (cve);
CREATE INDEX IF NOT EXISTS idx_exploits_platform ON exploits (platform);
CREATE INDEX IF NOT EXISTS idx_exploits_type ON exploits (type);
CREATE INDEX IF NOT EXISTS idx_exploits_date ON exploits (date);
`);
};
/**
* Insert or update an exploit in the database
*/
export const upsertExploit = async (exploit: Exploit): Promise<number> => {
if (!db) throw new Error('Database not initialized');
// Check if the exploit already exists
const existingExploit = await db.get('SELECT id FROM exploits WHERE id = ?', exploit.id);
if (existingExploit) {
// Update existing exploit
await db.run(
`UPDATE exploits SET
file = ?,
description = ?,
date = ?,
author = ?,
type = ?,
platform = ?,
port = ?,
cve = ?,
verified = ?
WHERE id = ?`,
exploit.file,
exploit.description,
exploit.date,
exploit.author,
exploit.type,
exploit.platform,
exploit.port || null,
exploit.cve || null,
exploit.verified ? 1 : 0,
exploit.id
);
// Delete existing tags
await db.run('DELETE FROM exploit_tags WHERE exploit_id = ?', exploit.id);
} else {
// Insert new exploit
await db.run(
`INSERT INTO exploits (
id, file, description, date, author, type, platform, port, cve, verified
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
exploit.id,
exploit.file,
exploit.description,
exploit.date,
exploit.author,
exploit.type,
exploit.platform,
exploit.port || null,
exploit.cve || null,
exploit.verified ? 1 : 0
);
}
// Insert tags if provided
if (exploit.tags && exploit.tags.length > 0) {
for (const tag of exploit.tags) {
// Insert tag if it doesn't exist
await db.run('INSERT OR IGNORE INTO tags (name) VALUES (?)', tag);
// Get tag ID
const tagRow = await db.get('SELECT id FROM tags WHERE name = ?', tag);
if (tagRow) {
// Link tag to exploit
await db.run(
'INSERT OR IGNORE INTO exploit_tags (exploit_id, tag_id) VALUES (?, ?)',
exploit.id,
tagRow.id
);
}
}
}
return exploit.id;
};
/**
* Search for exploits in the database
*/
export const searchExploits = async (
query: string = '',
options: {
platform?: string;
type?: string;
cve?: string;
author?: string;
startDate?: string;
endDate?: string;
verified?: boolean;
limit?: number;
offset?: number;
} = {}
): Promise<{ exploits: Exploit[]; total: number }> => {
if (!db) throw new Error('Database not initialized');
const {
platform,
type,
cve,
author,
startDate,
endDate,
verified,
limit = config.maxResults,
offset = 0
} = options;
// Build the WHERE clause
const conditions: string[] = [];
const params: any[] = [];
if (query) {
conditions.push('(description LIKE ? OR cve LIKE ?)');
params.push(`%${query}%`, `%${query}%`);
}
if (platform) {
conditions.push('platform = ?');
params.push(platform);
}
if (type) {
conditions.push('type = ?');
params.push(type);
}
if (cve) {
conditions.push('cve = ?');
params.push(cve);
}
if (author) {
conditions.push('author LIKE ?');
params.push(`%${author}%`);
}
if (startDate) {
conditions.push('date >= ?');
params.push(startDate);
}
if (endDate) {
conditions.push('date <= ?');
params.push(endDate);
}
if (verified !== undefined) {
conditions.push('verified = ?');
params.push(verified ? 1 : 0);
}
const whereClause = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';
// Get total count
const countQuery = `SELECT COUNT(*) as count FROM exploits ${whereClause}`;
const countResult = await db.get(countQuery, ...params);
const total = countResult ? countResult.count : 0;
// Get exploits
const sqlQuery = `
SELECT
e.id, e.file, e.description, e.date, e.author, e.type, e.platform,
e.port, e.cve, e.verified
FROM exploits e
${whereClause}
ORDER BY e.date DESC
LIMIT ? OFFSET ?
`;
const exploits = await db.all(sqlQuery, ...params, limit, offset);
// Get tags for each exploit
const result: Exploit[] = [];
for (const exploit of exploits) {
const tags = await db.all(`
SELECT t.name
FROM tags t
JOIN exploit_tags et ON t.id = et.tag_id
WHERE et.exploit_id = ?
`, exploit.id);
result.push({
...exploit,
verified: Boolean(exploit.verified),
tags: tags.map(t => t.name)
});
}
return { exploits: result, total };
};
/**
* Get an exploit by ID
*/
export const getExploitById = async (id: number): Promise<Exploit | null> => {
if (!db) throw new Error('Database not initialized');
const exploit = await db.get(`
SELECT
id, file, description, date, author, type, platform,
port, cve, verified
FROM exploits
WHERE id = ?
`, id);
if (!exploit) return null;
// Get tags
const tags = await db.all(`
SELECT t.name
FROM tags t
JOIN exploit_tags et ON t.id = et.tag_id
WHERE et.exploit_id = ?
`, id);
return {
...exploit,
verified: Boolean(exploit.verified),
tags: tags.map(t => t.name)
};
};
/**
* Find exploits by CVE
*/
export const findExploitsByCve = async (cve: string, limit: number = config.maxResults): Promise<Exploit[]> => {
if (!db) throw new Error('Database not initialized');
const exploits = await db.all(`
SELECT
id, file, description, date, author, type, platform,
port, cve, verified
FROM exploits
WHERE cve = ?
ORDER BY date DESC
LIMIT ?
`, cve, limit);
// Get tags for each exploit
const result: Exploit[] = [];
for (const exploit of exploits) {
const tags = await db.all(`
SELECT t.name
FROM tags t
JOIN exploit_tags et ON t.id = et.tag_id
WHERE et.exploit_id = ?
`, exploit.id);
result.push({
...exploit,
verified: Boolean(exploit.verified),
tags: tags.map(t => t.name)
});
}
return result;
};
/**
* Get recent exploits
*/
export const getRecentExploits = async (limit: number = config.maxResults): Promise<Exploit[]> => {
if (!db) throw new Error('Database not initialized');
const exploits = await db.all(`
SELECT
id, file, description, date, author, type, platform,
port, cve, verified
FROM exploits
ORDER BY date DESC
LIMIT ?
`, limit);
// Get tags for each exploit
const result: Exploit[] = [];
for (const exploit of exploits) {
const tags = await db.all(`
SELECT t.name
FROM tags t
JOIN exploit_tags et ON t.id = et.tag_id
WHERE et.exploit_id = ?
`, exploit.id);
result.push({
...exploit,
verified: Boolean(exploit.verified),
tags: tags.map(t => t.name)
});
}
return result;
};
/**
* Get statistics about the exploits in the database
*/
export const getStatistics = async (): Promise<any> => {
if (!db) throw new Error('Database not initialized');
// Get total count
const totalCount = await db.get('SELECT COUNT(*) as count FROM exploits');
// Get count by platform
const platformCounts = await db.all(`
SELECT platform, COUNT(*) as count
FROM exploits
GROUP BY platform
ORDER BY count DESC
`);
// Get count by type
const typeCounts = await db.all(`
SELECT type, COUNT(*) as count
FROM exploits
GROUP BY type
ORDER BY count DESC
`);
// Get count by year
const yearCounts = await db.all(`
SELECT substr(date, 1, 4) as year, COUNT(*) as count
FROM exploits
GROUP BY year
ORDER BY year DESC
`);
// Get count of verified exploits
const verifiedCount = await db.get('SELECT COUNT(*) as count FROM exploits WHERE verified = 1');
// Get count of exploits with CVEs
const cveCount = await db.get('SELECT COUNT(*) as count FROM exploits WHERE cve IS NOT NULL');
return {
total: totalCount ? totalCount.count : 0,
verified: verifiedCount ? verifiedCount.count : 0,
withCve: cveCount ? cveCount.count : 0,
byPlatform: platformCounts,
byType: typeCounts,
byYear: yearCounts
};
};
/**
* Close the database connection
*/
export const closeDatabase = async (): Promise<void> => {
if (db) {
await db.close();
db = null;
}
};
export default {
initDatabase,
upsertExploit,
searchExploits,
getExploitById,
findExploitsByCve,
getRecentExploits,
getStatistics,
closeDatabase
};