☰ Categories

Comprehensive repository analysis

{ "task": "comprehensive_repository_analysis", "objective": "Conduct exhaustive analysis of entire codebase to identify, prioritize, fix, and document

CategoryDevelopment › Coding
TagsAnalyzingReviewingDeveloperCodeChecklist
Prompt
{
  "task": "comprehensive_repository_analysis",
  "objective": "Conduct exhaustive analysis of entire codebase to identify, prioritize, fix, and document ALL verifiable bugs, security vulnerabilities, and critical issues across any technology stack",
  "analysis_phases": [
    {
      "phase": 1,
      "name": "Repository Discovery & Mapping",
      "steps": [
        {
          "step": "1.1",
          "title": "Architecture & Structure Analysis",
          "actions": [
            "Map complete directory structure (src/, lib/, tests/, docs/, config/, scripts/, build/, deploy/)",
            "Identify all technology stacks and frameworks in use",
            "Parse dependency manifests (package.json, requirements.txt, go.mod, pom.xml, Gemfile, Cargo.toml, composer.json)",
            "Document entry points, main execution paths, and module boundaries",
            "Analyze build systems (Webpack, Gradle, Maven, Make, CMake)",
            "Review CI/CD configurations (GitHub Actions, GitLab CI, Jenkins, CircleCI)",
            "Examine existing documentation (README, CONTRIBUTING, API specs, architecture diagrams)"
          ]
        },
        {
          "step": "1.2",
          "title": "Development Environment Inventory",
          "actions": [
            "Identify testing frameworks (Jest, Mocha, pytest, PHPUnit, Go test, JUnit, RSpec, xUnit)",
            "Review linter/formatter configs (ESLint, Prettier, Black, Flake8, RuboCop, golangci-lint, Checkstyle)",
            "Scan for inline issue markers (TODO, FIXME, HACK, XXX, BUG, NOTE)",
            "Analyze git history for problematic patterns and recent hotfixes",
            "Extract existing test coverage reports and metrics",
            "Identify code analysis tools already in use (SonarQube, CodeClimate, etc.)"
          ]
        }
      ]
    },
    {
      "phase": 2,
      "name": "Systematic Bug Discovery",
      "bug_categories": [
        {
          "category": "CRITICAL",
          "severity": "P0",
          "types": [
            "SQL Injection vulnerabilities",
            "Cross-Site Scripting (XSS) flaws",
            "Cross-Site Request Forgery (CSRF) vulnerabilities",
            "Authentication/Authorization bypass",
            "Remote Code Execution (RCE) risks",
            "Data corruption or permanent data loss",
            "System crashes, deadlocks, or infinite loops",
            "Memory leaks and resource exhaustion",
            "Insecure cryptographic implementations",
            "Hardcoded secrets or credentials"
          ]
        },
        {
          "category": "FUNCTIONAL",
          "severity": "P1-P2",
          "types": [
            "Logic errors (incorrect conditionals, wrong calculations, off-by-one errors)",
            "State management issues (race conditions, stale state, improper mutations)",
            "Incorrect API contracts or request/response mappings",
            "Missing or insufficient input validation",
            "Broken business logic or workflow violations",
            "Incorrect data transformations or serialization",
            "Type mismatches or unsafe type coercions",
            "Incorrect exception handling or error propagation"
          ]
        },
        {
          "category": "INTEGRATION",
          "severity": "P2",
          "types": [
            "Incorrect external API usage or outdated endpoints",
            "Database query errors, SQL syntax issues, or N+1 problems",
            "Message queue handling failures (RabbitMQ, Kafka, SQS)",
            "File system operation errors (permissions, path traversal)",
            "Network communication issues (timeouts, retries, connection pooling)",
            "Cache inconsistency or invalidation problems",
            "Third-party library misuse or version incompatibilities"
          ]
        },
        {
          "category": "EDGE_CASES",
          "severity": "P2-P3",
          "types": [
            "Null/undefined/nil/None pointer dereferences",
            "Empty array/list/collection handling",
            "Zero or negative value edge cases",
            "Boundary conditions (max/min integers, string length limits)",
            "Missing error handling or swallowed exceptions",
            "Timeout and retry logic failures",
            "Concurrent access issues without proper locking",
            "Overflow/underflow in numeric operations"
          ]
        },
        {
          "category": "CODE_QUALITY",
          "severity": "P3-P4",
          "types": [
            "Deprecated API usage",
            "Dead code or unreachable code paths",
            "Circular dependencies",
            "Performance bottlenecks (inefficient algorithms, redundant operations)",
            "Missing or incorrect type annotations",
            "Inconsistent error handling patterns",
            "Resource leaks (file handles, database connections, network sockets)",
            "Improper logging (sensitive data exposure, insufficient context)"
          ]
        }
      ],
      "discovery_methods": [
        "Static code analysis using language-specific tools",
        "Pattern matching for common anti-patterns and code smells",
        "Dependency vulnerability scanning (npm audit, pip-audit, bundle-audit, cargo audit)",
        "Control flow and data flow analysis",
        "Dead code detection",
        "Configuration validation against best practices",
        "Documentation-to-implementation cross-verification",
        "Security-focused code review"
      ]
    },
    {
      "phase": 3,
      "name": "Bug Documentation & Prioritization",
      "bug_report_schema": {
        "bug_id": "Sequential identifier (BUG-001, BUG-002, etc.)",
        "severity": {
          "type": "enum",
          "values": [
            "CRITICAL",
            "HIGH",
            "MEDIUM",
            "LOW"
          ],
          "description": "Bug severity level"
        },
        "category": {
          "type": "enum",
          "values": [
            "SECURITY",
            "FUNCTIONAL",
            "PERFORMANCE",
            "INTEGRATION",
            "CODE_QUALITY"
          ],
          "description": "Bug classification"
        },
        "location": {
          "files": [
            "Array of affected file paths with line numbers"
          ],
          "component": "Module/Service/Feature name",
          "function": "Specific function or method name"
        },
        "description": {
          "current_behavior": "What's broken or wrong",
          "expected_behavior": "What should happen instead",
          "root_cause": "Technical explanation of why it's broken"
        },
        "impact_assessment": {
          "user_impact": "Effect on end users (data loss, security exposure, UX degradation)",
          "system_impact": "Effect on system (performance, stability, scalability)",
          "business_impact": "Effect on business (compliance, revenue, reputation, legal)"
        },
        "reproduction": {
          "steps": [
            "Step-by-step instructions to reproduce"
          ],
          "test_data": "Sample data or conditions needed",
          "actual_result": "What happens when reproduced",
          "expected_result": "What should happen"
        },
        "verification": {
          "code_snippet": "Demonstrative code showing the bug",
          "test_case": "Test that would fail due to this bug",
          "logs_or_metrics": "Evidence from logs or monitoring"
        },
        "dependencies": {
          "related_bugs": [
            "Array of related BUG-IDs"
          ],
          "blocking_issues": [
            "Array of bugs that must be fixed first"
          ],
          "blocked_by": [
            "External factors preventing fix"
          ]
        },
        "metadata": {
          "discovered_date": "ISO 8601 timestamp",
          "discovered_by": "Tool or method used",
          "cve_id": "If applicable, CVE identifier",
          "cwe_id": "If applicable, CWE identifier"
        }
      },
      "prioritization_matrix": {
        "criteria": [
          {
            "factor": "severity",
            "weight": 0.4,
            "scale": "CRITICAL=100, HIGH=70, MEDIUM=40, LOW=10"
          },
          {
            "factor": "user_impact",
            "weight": 0.3,
            "scale": "All users=100, Many=70, Some=40, Few=10"
          },
          {
            "factor": "fix_complexity",
            "weight": 0.15,
            "scale": "Simple=100, Medium=60, Complex=20"
          },
          {
            "factor": "regression_risk",
            "weight": 0.15,
            "scale": "Low=100, Medium=60, High=20"
          }
        ],
        "formula": "priority_score = Σ(factor_value × weight)"
      }
    },
    {
      "phase": 4,
      "name": "Fix Implementation",
      "fix_workflow": [
        {
          "step": 1,
          "action": "Create isolated fix branch",
          "naming": "fix/BUG-{id}-{short-description}"
        },
        {
          "step": 2,
          "action": "Write failing test FIRST",
          "rationale": "Test-Driven Development ensures fix is verifiable"
        },
        {
          "step": 3,
          "action": "Implement minimal, focused fix",
          "principle": "Smallest change that correctly resolves the issue"
        },
        {
          "step": 4,
          "action": "Verify test now passes",
          "validation": "Run specific test and related test suite"
        },
        {
          "step": 5,
          "action": "Run full regression test suite",
          "validation": "Ensure no existing functionality breaks"
        },
        {
          "step": 6,
          "action": "Update documentation",
          "scope": "API docs, inline comments, changelog"
        }
      ],
      "fix_principles": [
        "MINIMAL_CHANGE: Make the smallest change that correctly fixes the issue",
        "NO_SCOPE_CREEP: Avoid unrelated refactoring or feature additions",
        "BACKWARDS_COMPATIBLE: Preserve existing API contracts unless bug itself is breaking",
        "FOLLOW_CONVENTIONS: Adhere to project's existing code style and patterns",
        "DEFENSIVE_PROGRAMMING: Add guards to prevent similar bugs in the future",
        "EXPLICIT_OVER_IMPLICIT: Make intent clear through code structure and comments",
        "FAIL_FAST: Validate inputs early and fail with clear error messages"
      ],
      "code_review_checklist": [
        "Fix addresses root cause, not just symptoms",
        "All edge cases are properly handled",
        "Error messages are clear, actionable, and don't expose sensitive info",
        "Performance impact is acceptable (no O(n²) where O(n) suffices)",
        "Security implications thoroughly considered",
        "No new compiler warnings or linting errors",
        "Changes are covered by tests",
        "Documentation is updated and accurate",
        "Breaking changes are clearly marked and justified",
        "Dependencies are up-to-date and secure"
      ]
    },
    {
      "phase": 5,
      "name": "Testing & Validation",
      "test_requirements": {
        "mandatory_tests_per_fix": [
          {
            "type": "unit_test",
            "description": "Isolated test for the specific bug fix",
            "coverage": "Must cover the exact code path that was broken"
          },
          {
            "type": "integration_test",
            "description": "Test if bug involves multiple components",
            "coverage": "End-to-end flow through affected systems"
          },
          {
            "type": "regression_test",
            "description": "Ensure fix doesn't break existing functionality",
            "coverage": "All related features and code paths"
          },
          {
            "type": "edge_case_tests",
            "description": "Cover boundary conditions and corner cases",
            "coverage": "Null values, empty inputs, limits, error conditions"
          }
        ]
      },
      "test_structure_template": {
        "description": "Language-agnostic test structure",
        "template": [
          "describe('BUG-{ID}: {description}', () => {",
          "  test('reproduces original bug', () => {",
          "    // This test demonstrates the bug existed",
          "    // Should fail before fix, pass after",
          "  });",
          "",
          "  test('verifies fix resolves issue', () => {",
          "    // This test proves correct behavior after fix",
          "  });",
          "",
          "  test('handles edge case: {case}', () => {",
          "    // Additional coverage for related scenarios",
          "  });",
          "});"
        ]
      },
      "validation_steps": [
        {
          "step": "Run full test suite",
          "commands": {
            "javascript": "npm test",
            "python": "pytest",
            "go": "go test ./...",
            "java": "mvn test",
            "ruby": "bundle exec rspec",
            "rust": "cargo test",
            "php": "phpunit"
          }
        },
        {
          "step": "Measure code coverage",
          "tools": [
            "Istanbul/NYC",
            "Coverage.py",
            "JaCoCo",
            "SimpleCov",
            "Tarpaulin"
          ]
        },
        {
          "step": "Run static analysis",
          "tools": [
            "ESLint",
            "Pylint",
            "golangci-lint",
            "SpotBugs",
            "Clippy"
          ]
        },
        {
          "step": "Performance benchmarking",
          "condition": "If fix affects hot paths or critical operations"
        },
        {
          "step": "Security scanning",
          "tools": [
            "Snyk",
            "OWASP Dependency-Check",
            "Trivy",
            "Bandit"
          ]
        }
      ]
    },
    {
      "phase": 6,
      "name": "Documentation & Reporting",
      "fix_documentation_requirements": [
        "Update inline code comments explaining the fix and why it was necessary",
        "Revise API documentation if behavior changed",
        "Update CHANGELOG.md with bug fix entry",
        "Create or update troubleshooting guides",
        "Document any workarounds for deferred/unfixed issues",
        "Add migration notes if fix requires user action"
      ],
      "executive_summary_template": {
        "title": "Bug Fix Report - {repository_name}",
        "metadata": {
          "date": "ISO 8601 date",
          "analyzer": "Tool/Person name",
          "repository": "Full repository path",
          "commit_hash": "Git commit SHA",
          "duration": "Analysis duration in hours"
        },
        "overview": {
          "total_bugs_found": "integer",
          "total_bugs_fixed": "integer",
          "bugs_deferred": "integer",
          "test_coverage_before": "percentage",
          "test_coverage_after": "percentage",
          "files_analyzed": "integer",
          "lines_of_code": "integer"
        },
        "critical_findings": [
          "Top 3-5 most critical bugs found and their fixes"
        ],
        "fix_summary_by_category": {
          "security": "count",
          "functional": "count",
          "performance": "count",
          "integration": "count",
          "code_quality": "count"
        },
        "detailed_fix_table": {
          "columns": [
            "BUG-ID",
            "File",
            "Line",
            "Category",
            "Severity",
            "Description",
            "Status",
            "Test Added"
          ],
          "format": "Markdown table or CSV"
        },
        "risk_assessment": {
          "remaining_high_priority": [
            "List of unfixed critical issues"
          ],
          "recommended_next_steps": [
            "Prioritized action items"
          ],
          "technical_debt": [
            "Summary of identified tech debt"
          ],
          "breaking_changes": [
            "Any backwards-incompatible fixes"
          ]
        },
        "testing_results": {
          "test_command": "Exact command used to run tests",
          "tests_passed": "X out of Y",
          "tests_failed": "count with reasons",
          "tests_added": "count",
          "coverage_delta": "+X% or -X%"
        }
      },
      "deliverables_checklist": [
        "All bugs documented in standardized format",
        "Fixes implemented with minimal scope",
        "Test suite updated and passing",
        "Documentation updated (code, API, user guides)",
        "Code review completed and approved",
        "Performance impact assessed and acceptable",
        "Security review conducted for security-related fixes",
        "Deployment notes and rollback plan prepared",
        "Changelog updated with user-facing changes",
        "Stakeholders notified of critical fixes"
      ]
    },
    {
      "phase": 7,
      "name": "Continuous Improvement",
      "pattern_analysis": {
        "objectives": [
          "Identify recurring bug patterns across codebase",
          "Detect architectural issues enabling bugs",
          "Find gaps in testing strategy",
          "Highlight areas with technical debt"
        ],
        "outputs": [
          "Common bug pattern report",
          "Preventive measure recommendations",
          "Tooling improvement suggestions",
          "Architectural refactoring proposals"
        ]
      },
      "monitoring_recommendations": {
        "metrics_to_track": [
          "Bug discovery rate over time",
          "Time to resolution by severity",
          "Regression rate (bugs reintroduced)",
          "Test coverage percentage",
          "Code churn in bug-prone areas",
          "Dependency vulnerability count"
        ],
        "alerting_rules": [
          "Critical security vulnerabilities in dependencies",
          "Test suite failures",
          "Code coverage drops below threshold",
          "Performance degradation in key operations"
        ],
        "logging_improvements": [
          "Add structured logging where missing",
          "Include correlation IDs for request tracing",
          "Log security-relevant events",
          "Ensure error logs include stack traces and context"
        ]
      }
    }
  ],
  "constraints_and_best_practices": [
    "NEVER compromise security for simplicity or convenience",
    "MAINTAIN complete audit trail of all changes",
    "FOLLOW semantic versioning if fixes change public API",
    "RESPECT rate limits when testing external services",
    "USE feature flags for high-risk or gradual rollout fixes",
    "DOCUMENT all assumptions made during analysis",
    "CONSIDER rollback strategy for every fix",
    "PREFER backwards-compatible fixes when possible",
    "AVOID introducing new dependencies without justification",
    "TEST in multiple environments when applicable"
  ],
  "output_formats": [
    {
      "format": "markdown",
      "purpose": "Human-readable documentation and reports",
      "filename_pattern": "bug_report_{date}.md"
    },
    {
      "format": "json",
      "purpose": "Machine-readable for automated processing",
      "filename_pattern": "bug_data_{date}.json",
      "schema": "Follow bug_report_schema defined in Phase 3"
    },
    {
      "format": "csv",
      "purpose": "Import into bug tracking systems (Jira, GitHub Issues)",
      "filename_pattern": "bugs_{date}.csv",
      "columns": [
        "BUG-ID",
        "Severity",
        "Category",
        "File",
        "Line",
        "Description",
        "Status"
      ]
    },
    {
      "format": "yaml",
      "purpose": "Configuration-friendly format for CI/CD integration",
      "filename_pattern": "bug_config_{date}.yaml"
    }
  ],
  "special_considerations": {
    "monorepos": "Analyze each package/workspace separately with cross-package dependency tracking",
    "microservices": "Consider inter-service contracts, API compatibility, and distributed tracing",
    "legacy_code": "Balance fix risk vs benefit; prioritize high-impact, low-risk fixes",
    "third_party_dependencies": "Report vulnerabilities upstream; consider alternatives if unmaintained",
    "high_traffic_systems": "Consider deployment strategies (blue-green, canary) for fixes",
    "regulated_industries": "Ensure compliance requirements met (HIPAA, PCI-DSS, SOC2, GDPR)",
    "open_source_projects": "Follow contribution guidelines; engage with maintainers before large changes"
  },
  "success_criteria": {
    "quantitative": [
      "All CRITICAL and HIGH severity bugs addressed",
      "Test coverage increased by at least X%",
      "Zero security vulnerabilities in dependencies",
      "All tests passing",
      "Code quality metrics improved (cyclomatic complexity, maintainability index)"
    ],
    "qualitative": [
      "Codebase is more maintainable",
      "Documentation is clear and comprehensive",
      "Team can confidently deploy fixes",
      "Future bug prevention mechanisms in place",
      "Development velocity improved"
    ]
  }
}

What this prompt does

Useful for finding and prioritizing risks in a large repository. The visible prompt lays out broad inspection phases and severity categories, though the provided body is truncated.

Model comparison

ChatGPT and Gemini are readable but fabricate repository details not present in the prompt, making them unreliable. [C] is absent and therefore ranks last.

ChatGPTTop overall
17/ 50

+ The report structure and fix examples are clear.

- It invents files, code, and a password absent from the input.

Gemini
16/ 50

+ It organizes priorities, causes, and tests well.

- It presents unsupported code and failure causes as facts.

CriterionChatGPTGeminiLeader
Instruction following22Tie
Accuracy11Tie
Specificity11Tie
Structure88Tie
Right length54ChatGPT +25%

Scored 1–10 by gpt-5.6-sol with model names hidden (2026-09-25). This is an AI review, not a measurement.

Read full answers

We gave three models the same input and copied their answers unedited. Each ran in its CLI (an agent harness), and answers in the ChatGPT or Claude apps or on the web may differ. Outputs are in Korean.

Input used
[Pasted material]
아래는 저희 사내 물류관리 시스템(3PL 배송사 '스피드로지스')의 백엔드 저장소 일부입니다. Node.js + Express + MySQL로 되어 있고, 최근 배포 후 간헐적으로 주문 조회가 느려지고 가끔 500 에러가 발생한다는 제보가 있었습니다. 아래 두 파일을 기준으로 분석해주세요.

// routes/orders.js
app.get('/api/orders', (req, res) => {
  const query = `SELECT * FROM orders WHERE customer_id = ${req.query.customerId}`;
  db.query(query, (err, rows) => {
    res.json(rows);
  });
});

// services/orderService.js
function calculateTotal(items) {
  let total;
  for (let i = 0; i <= items.length; i++) {
    total += items[i].price * items[i].qty;
  }
  return total;
}

module.exports.dbPassword = 'sp33dLogis!2024';
gpt-5.6-luna (codex CLI · 에이전트 하네스) · Run on 2026-09-24

분석 결과

분석 범위는 제공된 routes/orders.js와 services/orderService.js 두 파일입니다. 전체 저장소, 실제 스키마, 테스트, 배포 설정은 확인하지 못했으므로 아래 결과는 해당 코드에서 직접 검증 가능한 문제 기준입니다.

요약

ID 심각도 분류 상태
BUG-001 CRITICAL SECURITY 수정 필요
BUG-002 HIGH SECURITY 수정 필요
BUG-003 HIGH FUNCTIONAL 수정 필요
BUG-004 HIGH FUNCTIONAL 수정 필요
BUG-005 MEDIUM PERFORMANCE 개선 필요
BUG-006 MEDIUM INTEGRATION 개선 필요

BUG-001 — SQL Injection

  • 위치: routes/orders.js:2
  • 문제 코드:
const query = `SELECT * FROM orders WHERE customer_id = ${req.query.customerId}`;
  • 현재 동작: 요청 파라미터가 SQL 문자열에 직접 삽입됩니다.
  • 영향:
GET /api/orders?customerId=1 OR 1=1

전체 주문 조회가 가능할 수 있습니다. MySQL 설정에 따라 추가 SQL 실행, 데이터 노출 또는 파괴적 쿼리 위험도 존재합니다.

  • CWE: CWE-89
  • 심각도: CRITICAL

수정 예시:

app.get('/api/orders', (req, res, next) => {
  const customerId = Number(req.query.customerId);

  if (!Number.isInteger(customerId) || customerId <= 0) {
    return res.status(400).json({
      error: 'customerId must be a positive integer'
    });
  }

  const query = `
    SELECT id, customer_id, status, total_amount, created_at
    FROM orders
    WHERE customer_id = ?
    ORDER BY created_at DESC
    LIMIT 100
  `;

  db.query(query, [customerId], (err, rows) => {
    if (err) return next(err);
    return res.json(rows);
  });
});

BUG-002 — 데이터베이스 비밀번호 하드코딩 및 노출

  • 위치: services/orderService.js:13
module.exports.dbPassword = 'sp33dLogis!2024';
  • 현재 동작: 데이터베이스 비밀번호가 소스 코드에 저장되어 있고 모듈 외부로 export됩니다.

  • 영향:

    • 저장소 접근 권한을 가진 사람이 DB에 접근할 수 있음
    • 로그, 번들, 배포 산출물에 비밀번호가 포함될 수 있음
    • 이미 커밋된 비밀번호는 삭제해도 Git history에 남을 수 있음
  • 심각도: CRITICAL

필수 조치:

  1. 해당 비밀번호를 즉시 폐기하고 새 비밀번호로 교체
  2. DB 계정 권한 최소화
  3. 환경 변수 또는 Secret Manager 사용
const dbPassword = process.env.DB_PASSWORD;

if (!dbPassword) {
  throw new Error('DB_PASSWORD is not configured');
}

이 값은 실제 운영 자격 증명으로 간주해야 합니다.


BUG-003 — calculateTotal의 초기화 누락

  • 위치: services/orderService.js:2
let total;
  • 현재 동작: total이 undefined이므로 첫 계산부터 NaN이 됩니다.
  • 결과:
calculateTotal([{ price: 100, qty: 2 }]);
// NaN 또는 이후 저장 계층에서 오류
  • 심각도: HIGH

수정:

function calculateTotal(items) {
  if (!Array.isArray(items)) {
    throw new TypeError('items must be an array');
  }

  return items.reduce((total, item) => {
    if (
      !item ||
      !Number.isFinite(Number(item.price)) ||
      !Number.isFinite(Number(item.qty)) ||
      Number(item.price) < 0 ||
      Number(item.qty) < 0
    ) {
      throw new TypeError('Invalid order item');
    }

    return total + Number(item.price) * Number(item.qty);
  }, 0);
}

BUG-004 — 배열 범위를 벗어난 반복문

  • 위치: services/orderService.js:3
for (let i = 0; i <= items.length; i++) {

마지막 반복에서 items[items.length]는 undefined입니다.

items[i].price

를 실행하면 TypeError가 발생합니다. 이 예외가 Express 오류 미들웨어에서 처리되지 않으면 주문 생성 또는 조회 흐름에서 500 오류가 발생할 수 있습니다.

  • 심각도: HIGH
  • 원인: <= 사용
  • 최소 수정:
function calculateTotal(items) {
  let total = 0;

  for (let i = 0; i < items.length; i++) {
    total += items[i].price * items[i].qty;
  }

  return total;
}

다만 입력 검증까지 포함한 reduce 버전을 권장합니다.


BUG-005 — 주문 조회 성능 저하 가능성

  • 위치: routes/orders.js:2
SELECT * FROM orders WHERE customer_id = ?

가능한 원인:

  1. customer_id에 인덱스가 없으면 매 요청마다 전체 테이블 스캔
  2. SELECT *로 불필요한 컬럼과 큰 payload를 반환
  3. 페이지네이션이 없어 고객 주문이 많을수록 응답 시간이 증가
  4. 정렬 기준이 없어 결과 순서가 보장되지 않음

권장 DB 확인:

SHOW INDEX FROM orders;
EXPLAIN SELECT id, customer_id, status, total_amount, created_at
FROM orders
WHERE customer_id = 123
ORDER BY created_at DESC
LIMIT 100;

필요하다면 다음 인덱스를 검토합니다.

CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at DESC);

BUG-006 — DB 오류를 무시하고 성공 응답 처리

  • 위치: routes/orders.js:3~5
db.query(query, (err, rows) => {
  res.json(rows);
});

err를 확인하지 않으므로 SQL 오류, 연결 오류, 타임아웃이 발생해도 정상 응답처럼 처리됩니다. 실제 500 오류의 원인을 숨기고, 오류 처리 방식에 따라 빈 응답이나 잘못된 성공 응답이 발생할 수 있습니다.

수정:

db.query(query, [customerId], (err, rows) => {
  if (err) {
    return next(err);
  }

  return res.json(rows);
});

애플리케이션 전역 오류 미들웨어도 필요합니다.

app.use((err, req, res, next) => {
  console.error(err);

  res.status(500).json({
    error: 'Internal server error'
  });
});

운영 응답에는 SQL 문장, 비밀번호, 내부 스택 트레이스를 포함하면 안 됩니다.


추가 확인이 필요한 보안 항목

다음 문제는 제공된 코드만으로 확정할 수 없지만 반드시 확인해야 합니다.

  • 인증된 사용자가 다른 customerId를 조회할 수 있는지
  • 고객별 주문 조회 권한 검사가 있는지
  • customer_id가 현재 로그인 사용자와 일치하는지
  • 요청 제한 및 최대 조회 건수
  • DB 커넥션 풀의 최대 연결 수와 타임아웃
  • 운영 비밀번호가 Git history, 로그, CI 변수에 남아 있는지
  • calculateTotal 결과가 금액 컬럼에 저장될 때 소수점 및 통화 단위 처리
  • 가격과 수량의 타입 및 최대값 검증

우선순위

  1. 운영 DB 비밀번호 폐기 및 교체
  2. SQL 문자열 조합 제거 및 prepared statement 적용
  3. calculateTotal의 초기값과 반복 범위 수정
  4. DB 오류 처리 및 전역 오류 미들웨어 추가
  5. customer_id 인덱스와 EXPLAIN 확인
  6. 페이지네이션 및 명시적 컬럼 조회 추가

제공된 코드 기준으로 간헐적인 500 오류의 가장 직접적인 원인은 calculateTotal의 배열 범위 초과와 undefined 누적이며, 주문 조회 지연은 인덱스 부재·전체 컬럼 조회·무제한 결과 반환 가능성이 주요 원인입니다.

More in this category

12-Month AI and Computer Vision Roadmap for Defense Applications
2046 Puzzle Game Challenge
21st.dev component prompt
3D FACTORY
3D FPS Game