Database security protection measures

Database Security Protection Measures

Database security is a critical aspect that cannot be overlooked in system development. Koa2, as a lightweight Node.js framework, requires a combination of multiple strategies to ensure data security when building backend services. From basic authentication to complex encryption mechanisms, each component requires targeted design.

Authentication and Authorization

Authentication is the first line of defense for database security. JWT (JSON Web Token) is commonly implemented in Koa2:

const jwt = require('jsonwebtoken');
const koaJwt = require('koa-jwt');

app.use(koaJwt({
  secret: 'your_secret_key',
  algorithms: ['HS256']
}).unless({
  path: [/^\/public/]
}));

The Role-Based Access Control (RBAC) model can refine permission management:

// Middleware to check user roles
const checkRole = (role) => async (ctx, next) => {
  if (ctx.state.user.role !== role) {
    ctx.throw(403, 'Forbidden');
  }
  await next();
};

// Usage in routes
router.get('/admin', checkRole('admin'), async (ctx) => {
  // Admin-specific logic
});

Encrypted Data Storage

Sensitive data must be stored encrypted. Below is an example of using bcrypt to encrypt passwords:

const bcrypt = require('bcrypt');
const saltRounds = 12;

// Password hashing
const hashPassword = async (plainText) => {
  return await bcrypt.hash(plainText, saltRounds);
};

// Password verification
const comparePassword = async (plainText, hash) => {
  return await bcrypt.compare(plainText, hash);
};

For sensitive information like credit card numbers, AES-256 encryption is recommended:

const crypto = require('crypto');
const algorithm = 'aes-256-cbc';
const key = crypto.randomBytes(32);
const iv = crypto.randomBytes(16);

function encrypt(text) {
  let cipher = crypto.createCipheriv(algorithm, key, iv);
  let encrypted = cipher.update(text);
  encrypted = Buffer.concat([encrypted, cipher.final()]);
  return iv.toString('hex') + ':' + encrypted.toString('hex');
}

SQL Injection Protection

Parameterized queries should always be used in Koa2. Below is an example using knex.js:

const knex = require('knex')({
  client: 'mysql',
  connection: { /* configuration */ }
});

// Secure query
router.get('/users', async (ctx) => {
  const userId = ctx.query.id;
  const users = await knex('users')
    .where('id', userId)  // Parameterized handling
    .select('*');
  
  ctx.body = users;
});

Input validation is equally important. Use the joi library for schema validation:

const Joi = require('joi');

const userSchema = Joi.object({
  username: Joi.string().alphanum().min(3).max(30).required(),
  password: Joi.string().pattern(new RegExp('^[a-zA-Z0-9]{8,30}$')),
  email: Joi.string().email()
});

router.post('/users', async (ctx) => {
  const { error } = userSchema.validate(ctx.request.body);
  if (error) throw new Error(error.details[0].message);
  // Continue processing
});

Audit Logging

Comprehensive audit logs should include operational context:

const fs = require('fs');
const path = require('path');

app.use(async (ctx, next) => {
  const start = Date.now();
  await next();
  const latency = Date.now() - start;

  const logEntry = {
    timestamp: new Date(),
    method: ctx.method,
    path: ctx.path,
    status: ctx.status,
    latency: `${latency}ms`,
    user: ctx.state.user?.id || 'anonymous',
    ip: ctx.ip
  };

  const logPath = path.join(__dirname, 'audit.log');
  fs.appendFileSync(logPath, JSON.stringify(logEntry) + '\n');
});

Database Connection Security

Use SSL to encrypt database connections:

const knex = require('knex')({
  client: 'pg',
  connection: {
    host: 'db.example.com',
    user: 'user',
    password: 'password',
    database: 'mydb',
    ssl: {
      rejectUnauthorized: true,
      ca: fs.readFileSync('/path/to/server-ca.pem'),
      key: fs.readFileSync('/path/to/client-key.pem'),
      cert: fs.readFileSync('/path/to/client-cert.pem')
    }
  }
});

Connection pool configuration prevents resource exhaustion:

{
  pool: {
    min: 2,
    max: 10,
    acquireTimeoutMillis: 30000,
    idleTimeoutMillis: 30000
  }
}

Regular Backup Strategy

Implement automated backup scripts:

const { exec } = require('child_process');
const cron = require('node-cron');

// Daily backup at midnight
cron.schedule('0 0 * * *', () => {
  const backupCmd = `mysqldump -u ${user} -p${pass} ${db} > backup_${Date.now()}.sql`;
  exec(backupCmd, (error) => {
    if (error) console.error('Backup failed:', error);
    else console.log('Backup successful');
  });
});

Sensitive Data Masking

Hide sensitive fields in API responses:

const maskFields = (obj, fields) => {
  const masked = { ...obj };
  fields.forEach(field => {
    if (masked[field]) {
      masked[field] = '******';
    }
  });
  return masked;
};

router.get('/user/:id', async (ctx) => {
  const user = await User.findById(ctx.params.id);
  ctx.body = maskFields(user, ['password', 'ssn', 'creditCard']);
});

Security Headers

Enhance HTTP security headers in Koa2:

const helmet = require('koa-helmet');
app.use(helmet({
  contentSecurityPolicy: {
    directives: {
      defaultSrc: ["'self'"],
      scriptSrc: ["'self'", "'unsafe-inline'", 'cdn.example.com'],
      styleSrc: ["'self'", "'unsafe-inline'"]
    }
  },
  hsts: { maxAge: 31536000, includeSubDomains: true },
  referrerPolicy: { policy: 'same-origin' }
}));

Rate Limiting

Prevent brute-force attacks:

const rateLimit = require('koa2-ratelimit');

const limiter = rateLimit.middleware({
  interval: { min: 1 },
  max: 100,
  message: 'Too many requests, please try again later',
  prefixKey: ctx => ctx.ip + ctx.path  // Differentiate by IP and path
});

app.use(limiter);

Environment Variable Management

Sensitive configurations should be passed via environment variables:

const dotenv = require('dotenv');
dotenv.config();

// Example usage
const dbConfig = {
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASS
};

Database Firewall Rules

Cloud database configuration example (AWS RDS):

// Security group rules allow only specific IPs
const aws = require('aws-sdk');
const rds = new aws.RDS();

const params = {
  DBSecurityGroupName: 'my-db-sg',
  CIDRIP: '203.0.113.12/32'  // Only allow this IP
};

rds.authorizeDBSecurityGroupIngress(params, (err) => {
  if (err) console.error(err);
});

Regular Security Scanning

Integrate vulnerability scanning tools:

// Add security checks in CI/CD pipeline
const { execSync } = require('child_process');

try {
  console.log('Running security scan...');
  execSync('npm audit --production');
  execSync('npx snyk test');
} catch (error) {
  console.error('Security scan detected issues:');
  process.exit(1);
}

Data Minimization Principle

Implement field-level permissions in GraphQL:

const { ApolloServer } = require('apollo-server-koa');

const server = new ApolloServer({
  typeDefs,
  resolvers,
  context: ({ ctx }) => ({
    user: ctx.state.user
  }),
  fieldResolver: (source, args, context, info) => {
    if (info.parentType.name === 'User' && 
        info.fieldName === 'email' &&
        !context.user.isAdmin) {
      return null;  // Hide email field for non-admins
    }
    return source[info.fieldName];
  }
});

Zero Trust Architecture Implementation

Example of continuous verification middleware:

const reauthMiddleware = async (ctx, next) => {
  const sessionAge = Date.now() - ctx.session.lastVerified;
  if (sessionAge > 30 * 60 * 1000) {  // Re-authenticate every 30 minutes
    ctx.throw(401, 'Re-authentication required');
  }
  await next();
};

app.use(reauthMiddleware);