高安全架构 学习笔记（第 12 部分）

//  不安全的 SQL 查询（存在注入风险）
const unsafeQuery = (username) => {
  const sql = `SELECT * FROM users WHERE username = '${username}'`
  // 攻击输入: ' OR '1'='1
  // 实际执行的 SQL: SELECT * FROM users WHERE username = '' OR '1'='1'
  // 结果：返回所有用户数据
}

//  安全的参数化查询
const safeQuery = async (username) => {
  const sql = 'SELECT * FROM users WHERE username = ?'
  const results = await db.query(sql, [username])
  return results
}

// SQL 注入防护类
class SQLInjectionProtection {
  constructor() {
    // 危险关键字黑名单
    this.blacklist = [
      'SELECT', 'INSERT', 'UPDATE', 'DELETE', 'DROP', 'EXEC',
      'UNION', 'OR', 'AND', '--', ';', '/*', '*/'
    ]
    
    // 正则表达式检测
    this.patterns = [
      /(\b(SELECT|INSERT|UPDATE|DELETE|DROP|EXEC)\b)/gi,
      /(UNION.*SELECT)/gi,
      /('|\")\s*(OR|AND)\s*('|\")/gi,
      /(;|--)/g,
      /(\/\*|\*\/)/g
    ]
  }
  
  // 检测 SQL 注入
  detect(input) {
    const detections = []
    
    // 黑名单检测
    for (const keyword of this.blacklist) {
      if (input.toUpperCase().includes(keyword)) {
        detections.push({
          type: 'blacklist',
          keyword,
          position: input.toUpperCase().indexOf(keyword)
        })
      }
    }
    
    // 正则表达式检测
    for (const pattern of this.patterns) {
      const matches = input.match(pattern)
      if (matches) {
        detections.push({
          type: 'pattern',
          pattern: pattern.toString(),
          matches
        })
      }
    }
    
    return {
      safe: detections.length === 0,
      detections
    }
  }
  
  // 清理输入
  sanitize(input) {
    let sanitized = input
    
    // 转义特殊字符
    sanitized = sanitized.replace(/'/g, "''")
    sanitized = sanitized.replace(/"/g, '\\"')
    sanitized = sanitized.replace(/\\/g, '\\\\')
    
    // 移除危险字符
    sanitized = sanitized.replace(/;/g, '')
    sanitized = sanitized.replace(/--/g, '')
    sanitized = sanitized.replace(/\/\*/g, '')
    sanitized = sanitized.replace(/\*\//g, '')
    
    return sanitized
  }
  
  // 防护中间件
  middleware() {
    return (req, res, next) => {
      // 检查请求参数
      const checkInput = (obj) => {
        for (const key in obj) {
          if (typeof obj[key] === 'string') {
            const result = this.detect(obj[key])
            
            if (!result.safe) {
              return res.status(400).json({
                error: 'SQL Injection Detected',
                message: '检测到潜在的 SQL 注入攻击',
                field: key
              })
            }
            
            // 清理输入
            obj[key] = this.sanitize(obj[key])
          } else if (typeof obj[key] === 'object') {
            checkInput(obj[key])
          }
        }
      }
      
      checkInput(req.body)
      checkInput(req.query)
      checkInput(req.params)
      
      next()
    }
  }
}