database.service.ts•5.86 kB
import { Injectable } from '@nestjs/common';
import { InjectConnection } from '@nestjs/typeorm';
import { Connection } from 'typeorm';
import { ConfigService } from '../config/config.service';
import { PermissionsService } from '../common/permissions.service';
import { AuditLoggerService } from '../common/audit-logger.service';
@Injectable()
export class DatabaseService {
constructor(
@InjectConnection() private connection: Connection,
private configService: ConfigService,
private permissionsService: PermissionsService,
private auditLogger: AuditLoggerService,
) {}
// 验证 SQL 安全性
private validateSql(sql: string): void {
const validation = this.permissionsService.validateSql(sql);
if (!validation.valid) {
throw new Error(validation.error);
}
}
// 验证表名是否可访问
private validateTable(tableName: string): void {
if (!this.permissionsService.canAccessTable(tableName)) {
throw new Error(`无权访问表 '${tableName}'`);
}
}
// 验证表操作权限
private validateOperation(tableName: string, operation: string): void {
if (!this.permissionsService.canPerformOperation(tableName, operation)) {
if (this.permissionsService.isReadOnlyTable(tableName)) {
throw new Error(`表 '${tableName}' 是只读的，不允许 ${operation} 操作`);
}
throw new Error(`无权在表 '${tableName}' 上执行 ${operation} 操作`);
}
}
// 列出所有表
async listTables(): Promise<string[]> {
const result = await this.connection.query('SHOW TABLES');
const dbName = this.configService.dbConfig.database;
return result.map(row => row[`Tables_in_${dbName}`]);
}
// 获取表结构
async getTableSchema(tableName: string): Promise<any> {
this.validateTable(tableName);
const columns = await this.connection.query(
`DESCRIBE ${tableName}`
);
return {
tableName,
columns: columns.map(col => ({
field: col.Field,
type: col.Type,
null: col.Null,
key: col.Key,
default: col.Default,
extra: col.Extra,
})),
};
}
// 执行查询
async query(sql: string, params: any[] = []): Promise<any> {
this.validateSql(sql);
// 添加 LIMIT 限制
const maxLimit = this.permissionsService.getMaxQueryLimit();
if (sql.toUpperCase().includes('SELECT') && !sql.toUpperCase().includes('LIMIT')) {
sql += ` LIMIT ${maxLimit}`;
}
const result = await this.connection.query(sql, params);
// 记录审计日志
if (this.permissionsService.isAuditEnabled()) {
await this.auditLogger.logQuery('unknown', sql, params, result);
}
return result;
}
// 插入记录
async insert(tableName: string, data: Record<string, any>): Promise<any> {
this.validateTable(tableName);
this.validateOperation(tableName, 'INSERT');
const keys = Object.keys(data);
const values = Object.values(data);
const placeholders = keys.map(() => '?').join(', ');
const sql = `INSERT INTO ${tableName} (${keys.join(', ')}) VALUES (${placeholders})`;
const result = await this.connection.query(sql, values);
// 记录审计日志
if (this.permissionsService.isAuditEnabled()) {
await this.auditLogger.logInsert(tableName, data, result);
}
return {
insertId: result.insertId,
affectedRows: result.affectedRows,
insertedData: data,
};
}
// 更新记录
async update(tableName: string, data: Record<string, any>, where: Record<string, any>): Promise<any> {
this.validateTable(tableName);
this.validateOperation(tableName, 'UPDATE');
// 获取修改前的数据
let beforeData = null;
if (this.permissionsService.shouldTrackChanges()) {
const whereClause = Object.keys(where).map(key => `${key} = ?`).join(' AND ');
const selectSql = `SELECT * FROM ${tableName} WHERE ${whereClause}`;
beforeData = await this.connection.query(selectSql, Object.values(where));
}
const setClause = Object.keys(data).map(key => `${key} = ?`).join(', ');
const whereClause = Object.keys(where).map(key => `${key} = ?`).join(' AND ');
const sql = `UPDATE ${tableName} SET ${setClause} WHERE ${whereClause}`;
const params = [...Object.values(data), ...Object.values(where)];
const result = await this.connection.query(sql, params);
// 记录审计日志
if (this.permissionsService.isAuditEnabled()) {
await this.auditLogger.logUpdate(tableName, data, where, beforeData, result);
}
return {
affectedRows: result.affectedRows,
changedRows: result.changedRows,
beforeData,
afterData: data,
};
}
// 删除记录
async delete(tableName: string, where: Record<string, any>): Promise<any> {
this.validateTable(tableName);
this.validateOperation(tableName, 'DELETE');
// 获取删除前的数据
let beforeData = null;
if (this.permissionsService.shouldTrackChanges()) {
const whereClause = Object.keys(where).map(key => `${key} = ?`).join(' AND ');
const selectSql = `SELECT * FROM ${tableName} WHERE ${whereClause}`;
beforeData = await this.connection.query(selectSql, Object.values(where));
}
const whereClause = Object.keys(where).map(key => `${key} = ?`).join(' AND ');
const sql = `DELETE FROM ${tableName} WHERE ${whereClause}`;
const params = Object.values(where);
const result = await this.connection.query(sql, params);
// 记录审计日志
if (this.permissionsService.isAuditEnabled()) {
await this.auditLogger.logDelete(tableName, where, beforeData, result);
}
return {
affectedRows: result.affectedRows,
deletedData: beforeData,
};
}
}