추가 컬럼 탐색
#!/usr/bin/env node
'use strict';
const fs = require('fs');
const path = require('path');
const oracledb = require('oracledb');
const STRING_TYPES = new Set([
'CHAR',
'VARCHAR2',
'NCHAR',
'NVARCHAR2',
'CLOB',
'NCLOB'
]);
const EMAIL_REGEX = /^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}$/i;
const PHONE_REGEX = /^(?:(?:\+?\d{1,3}[-.\s]?)?(?:\(?\d{2,4}\)?[-.\s]?)?\d{3,4}[-.\s]?\d{4}|(?:\+?82[-.\s]?)?(?:0?1[016789]|0[2-6][1-5]?|070|080|050\d?)[-.\s]?\d{3,4}[-.\s]?\d{4})$/;
function parseArgs(argv) {
const args = {
config: path.resolve(__dirname, '..', 'db.ini'),
targets: path.resolve(__dirname, 'crypto-targets.json'),
output: path.resolve(__dirname, 'findoutput.txt'),
owners: process.env.SCAN_OWNERS || '',
sampleLimit: Number(process.env.SCAN_SAMPLE_LIMIT || 5),
maxLength: Number(process.env.SCAN_MAX_LENGTH || 4000),
includeSystemOwners: process.env.SCAN_INCLUDE_SYSTEM_OWNERS === 'true'
};
for (let i = 0; i < argv.length; i += 1) {
const arg = argv[i];
const readValue = () => {
if (i + 1 >= argv.length) {
throw new Error(`${arg} option requires a value.`);
}
i += 1;
return argv[i];
};
if (arg === '--config') args.config = path.resolve(readValue());
else if (arg === '--targets') args.targets = path.resolve(readValue());
else if (arg === '--output') args.output = path.resolve(readValue());
else if (arg === '--owners') args.owners = readValue();
else if (arg === '--sample-limit') args.sampleLimit = Number(readValue());
else if (arg === '--max-length') args.maxLength = Number(readValue());
else if (arg === '--include-system-owners') args.includeSystemOwners = true;
else if (arg === '--help' || arg === '-h') {
printHelp();
process.exit(0);
} else {
throw new Error(`Unknown option: ${arg}`);
}
}
if (!Number.isInteger(args.sampleLimit) || args.sampleLimit < 1 || args.sampleLimit > 100) {
throw new Error('--sample-limit must be an integer from 1 to 100.');
}
if (!Number.isInteger(args.maxLength) || args.maxLength < 1 || args.maxLength > 4000) {
throw new Error('--max-length must be an integer from 1 to 4000.');
}
return args;
}
function printHelp() {
console.log(`Usage:
npm install
npm run scan -- [options]
This scans accessible Oracle table columns and writes candidates to findoutput.txt.
Candidates are excluded when tableName/plainColumn already exists in crypto-targets.json
case-insensitively.
Find rules:
1. sample value is an email address
2. sample value is a phone number
3. column name is birth, case-insensitive
Options:
--config DB ini path. Default: ../db.ini
--targets crypto-targets.json path. Default: ./crypto-targets.json
--output output file path. Default: ./findoutput.txt
--owners owner list. Default: all accessible non-system schemas
--sample-limit sample count per column. Default: 5
--max-length max value length for scan. Default: 4000
--include-system-owners include system schemas
`);
}
function readIni(filePath) {
if (!fs.existsSync(filePath)) {
return {};
}
return fs.readFileSync(filePath, 'utf8')
.split(/\r?\n/)
.map((line) => line.trim())
.filter((line) => line && !line.startsWith('#') && !line.startsWith(';'))
.reduce((acc, line) => {
const index = line.indexOf('=');
if (index > -1) {
acc[line.slice(0, index).trim()] = line.slice(index + 1).trim();
}
return acc;
}, {});
}
function buildDbConfig(args) {
const ini = readIni(args.config);
const user = process.env.DB_USER || process.env.ORACLE_USER || ini.id;
const password = process.env.DB_PASSWORD || process.env.ORACLE_PASSWORD || ini.pass;
const host = process.env.DB_HOST || ini.ip || '127.0.0.1';
const port = process.env.DB_PORT || ini.port || '1521';
const sid = process.env.DB_SID || ini.sid;
const serviceName = process.env.DB_SERVICE_NAME || ini.serviceName || ini.service_name;
const connectString = process.env.DB_CONNECT_STRING
|| ini.connectString
|| ini.connect_string
|| `${host}:${port}/${serviceName || sid}`;
if (!user || !password || !connectString) {
throw new Error('DB connection information is missing. Check db.ini or DB_* environment variables.');
}
return { user, password, connectString };
}
function splitCsv(value) {
return String(value || '')
.split(',')
.map((item) => item.trim().toUpperCase())
.filter(Boolean);
}
function addInListPredicate(columnName, values, bindPrefix, binds) {
const placeholders = values.map((value, index) => {
const key = `${bindPrefix}${index}`;
binds[key] = value;
return `:${key}`;
});
return `${columnName} IN (${placeholders.join(', ')})`;
}
function normalizeKey(tableName, columnName) {
return `${String(tableName || '').toUpperCase()}.${String(columnName || '').toUpperCase()}`;
}
function loadExistingTargets(filePath) {
if (!fs.existsSync(filePath)) {
throw new Error(`crypto-targets.json not found: ${filePath}`);
}
const parsed = JSON.parse(fs.readFileSync(filePath, 'utf8'));
if (!Array.isArray(parsed)) {
throw new Error('crypto-targets.json must be a JSON array.');
}
const keys = new Set();
for (const item of parsed) {
if (item && item.tableName && item.plainColumn) {
keys.add(normalizeKey(item.tableName, item.plainColumn));
}
}
return keys;
}
function quoteIdentifier(identifier) {
return `"${String(identifier).replace(/"/g, '""')}"`;
}
function isStringType(dataType) {
return STRING_TYPES.has(dataType);
}
function isLobType(dataType) {
return dataType === 'CLOB' || dataType === 'NCLOB';
}
function sampleExpression(column, dataType, maxLength) {
const quotedColumn = quoteIdentifier(column);
if (isLobType(dataType)) {
return `DBMS_LOB.SUBSTR(${quotedColumn}, ${maxLength}, 1)`;
}
if (isStringType(dataType)) {
return `SUBSTR(${quotedColumn}, 1, ${maxLength})`;
}
return `SUBSTR(TO_CHAR(${quotedColumn}), 1, ${maxLength})`;
}
async function getCandidateColumns(connection, args, existingTargets) {
const owners = splitCsv(args.owners);
const binds = {};
const predicates = [
`((${addInListPredicate('c.DATA_TYPE', Array.from(STRING_TYPES), 'type', binds)}) OR UPPER(c.COLUMN_NAME) = 'BIRTH')`,
't.DROPPED = \'NO\'',
't.TEMPORARY = \'N\'',
't.NESTED = \'NO\''
];
if (owners.length > 0) {
predicates.push(addInListPredicate('c.OWNER', owners, 'owner', binds));
}
if (!args.includeSystemOwners) {
predicates.push(`c.OWNER NOT IN (
'SYS', 'SYSTEM', 'XDB', 'MDSYS', 'CTXSYS', 'ORDSYS', 'OLAPSYS',
'WMSYS', 'OUTLN', 'DBSNMP', 'APPQOSSYS', 'AUDSYS'
)`);
}
const sql = `
SELECT
c.OWNER,
c.TABLE_NAME,
c.COLUMN_NAME,
c.DATA_TYPE
FROM ALL_TAB_COLUMNS c
JOIN ALL_TABLES t
ON t.OWNER = c.OWNER
AND t.TABLE_NAME = c.TABLE_NAME
WHERE ${predicates.join('\n AND ')}
ORDER BY c.OWNER, c.TABLE_NAME, c.COLUMN_ID
`;
const result = await connection.execute(sql, binds, { outFormat: oracledb.OUT_FORMAT_OBJECT });
return result.rows.filter((column) => !existingTargets.has(normalizeKey(column.TABLE_NAME, column.COLUMN_NAME)));
}
async function getSamples(connection, column, args) {
const owner = quoteIdentifier(column.OWNER);
const tableName = quoteIdentifier(column.TABLE_NAME);
const valueExpression = sampleExpression(column.COLUMN_NAME, column.DATA_TYPE, args.maxLength);
const sql = `
SELECT SAMPLE_VALUE
FROM (
SELECT ${valueExpression} AS SAMPLE_VALUE
FROM ${owner}.${tableName}
WHERE ${quoteIdentifier(column.COLUMN_NAME)} IS NOT NULL
AND ROWNUM <= :sampleLimit
)
WHERE SAMPLE_VALUE IS NOT NULL
`;
const result = await connection.execute(
sql,
{ sampleLimit: args.sampleLimit },
{ outFormat: oracledb.OUT_FORMAT_OBJECT }
);
return result.rows.map((row) => String(row.SAMPLE_VALUE));
}
function classifyColumn(column, samples) {
const reasons = [];
const matchedSamples = [];
if (String(column.COLUMN_NAME).toUpperCase() === 'BIRTH') {
reasons.push('COLUMN_NAME_BIRTH');
}
for (const sample of samples) {
const value = sample.trim();
if (EMAIL_REGEX.test(value)) {
reasons.push('EMAIL');
matchedSamples.push({ type: 'EMAIL', value });
} else if (PHONE_REGEX.test(value)) {
reasons.push('PHONE');
matchedSamples.push({ type: 'PHONE', value });
}
}
return {
reasons: Array.from(new Set(reasons)),
matchedSamples
};
}
function maskLineBreaks(value) {
return String(value).replace(/\r/g, '\\r').replace(/\n/g, '\\n');
}
function buildOutput({ args, existingTargetCount, scannedColumns, matches, errors }) {
const lines = [];
lines.push('Additional Crypto Target Candidates');
lines.push(`GeneratedAt=${new Date().toISOString()}`);
lines.push(`TargetsFile=${args.targets}`);
lines.push(`ExistingTargets=${existingTargetCount}`);
lines.push(`ScannedColumns=${scannedColumns}`);
lines.push(`MatchedColumns=${matches.length}`);
lines.push(`ErrorColumns=${errors.length}`);
lines.push('');
for (const match of matches) {
lines.push(`[MATCH] ${match.owner}.${match.table}.${match.column} (${match.dataType})`);
lines.push(`Reasons=${match.reasons.join(', ')}`);
if (match.matchedSamples.length > 0) {
lines.push('Samples=');
for (const sample of match.matchedSamples) {
lines.push(` - ${sample.type}: ${maskLineBreaks(sample.value)}`);
}
}
lines.push('');
}
if (errors.length > 0) {
lines.push('[ERRORS]');
for (const error of errors) {
lines.push(`${error.owner}.${error.table}.${error.column}: ${error.error}`);
}
lines.push('');
}
return lines.join('\n');
}
async function main() {
const args = parseArgs(process.argv.slice(2));
const existingTargets = loadExistingTargets(args.targets);
const dbConfig = buildDbConfig(args);
oracledb.outFormat = oracledb.OUT_FORMAT_OBJECT;
const connection = await oracledb.getConnection(dbConfig);
try {
const columns = await getCandidateColumns(connection, args, existingTargets);
const matches = [];
const errors = [];
for (const [index, column] of columns.entries()) {
const label = `${column.OWNER}.${column.TABLE_NAME}.${column.COLUMN_NAME}`;
process.stderr.write(`[${index + 1}/${columns.length}] ${label}\r`);
try {
const samples = await getSamples(connection, column, args);
const classified = classifyColumn(column, samples);
if (classified.reasons.length > 0) {
matches.push({
owner: column.OWNER,
table: column.TABLE_NAME,
column: column.COLUMN_NAME,
dataType: column.DATA_TYPE,
reasons: classified.reasons,
matchedSamples: classified.matchedSamples
});
}
} catch (error) {
errors.push({
owner: column.OWNER,
table: column.TABLE_NAME,
column: column.COLUMN_NAME,
dataType: column.DATA_TYPE,
error: error.message
});
}
}
process.stderr.write(' '.repeat(120) + '\r');
const output = buildOutput({
args,
existingTargetCount: existingTargets.size,
scannedColumns: columns.length,
matches,
errors
});
fs.writeFileSync(args.output, output, 'utf8');
console.log(`Output written: ${args.output}`);
console.log(`Scanned columns: ${columns.length}`);
console.log(`Matched columns: ${matches.length}`);
console.log(`Error columns: ${errors.length}`);
} finally {
await connection.close();
}
}
main().catch((error) => {
console.error(error.message);
process.exitCode = 1;
});
====================================================================
{
"name": "oracle-string-regex-sampler",
"version": "1.0.0",
"private": true,
"description": "Oracle additional crypto target finder using node-oracledb",
"main": "scan-string-columns.js",
"scripts": {
"scan": "node scan-string-columns.js"
},
"dependencies": {
"oracledb": "^6.9.0"
}
}
댓글
댓글 쓰기