analyze_database_queries – Database Query Security & Performance Analysis

Comprehensive database query analysis for SQL injection detection, performance optimization, and security best practices

Perfect For

SQL Injection Vulnerability Detection
Identify dangerous query construction patterns and injection attack vectors with CWE-89 compliance and remediation guidance.

Database Performance Optimization
Detect N+1 query problems, inefficient joins, and performance bottlenecks with query optimization recommendations.

WordPress Security Auditing
Specialized analysis for WordPress database patterns with wpdb best practices and prepared statement verification.

API Security Assessment
Analyze database access patterns in REST APIs and GraphQL resolvers for security compliance and data exposure risks.

Legacy Code Security Review
Systematic analysis of inherited database code to identify critical security vulnerabilities before modernization.

Quick Start

Analyze PHP file with database queries:

houtini-lm:analyze_database_queries with:
- filePath: "C:/project/api/users.php"
- language: "php"
- analysisType: "security"

WordPress plugin security analysis:

houtini-lm:analyze_database_queries with:
- projectPath: "C:/wordpress-plugin"
- language: "php"
- analysisType: "comprehensive"
- context: {"framework": "WordPress", "wpdb": true}

Security Analysis Output

SQL Injection Detection

• Critical Vulnerabilities: Direct string concatenation and unsanitized user input with exploit scenarios
• Prepared Statement Analysis: Missing parameterized queries and unsafe query construction patterns
• Input Validation Assessment: User input sanitization and validation before database operations

Performance Analysis

• N+1 Query Detection: Identification of inefficient query patterns in loops with optimization strategies
• Index Usage Analysis: Query patterns that may benefit from database indexing improvements
• Join Optimization: Complex query analysis with performance improvement recommendations

WordPress-Specific Analysis

• wpdb Usage Patterns: WordPress database class best practices and security compliance
• Custom Table Security: Analysis of plugin database table creation and data handling
• Capability Integration: Database operations with proper WordPress capability checking

Vulnerability Analysis Example

Vulnerable Code Pattern

// Critical SQL injection vulnerability
$user_id = $_GET['id'];
$query = "SELECT * FROM users WHERE id = " . $user_id;
$result = $wpdb->get_results($query);

// N+1 query problem
foreach ($posts as $post) {
    $comments = $wpdb->get_results(
        "SELECT * FROM comments WHERE post_id = " . $post->id
    );
}

Security Findings

• Critical: SQL Injection (CWE-89) – Direct concatenation of user input enables arbitrary SQL execution
• High: N+1 Query Problem – Loop-based queries causing performance degradation and potential DoS
• Medium: Missing Input Validation – No sanitization or type checking on user input
• Medium: No Error Handling – Database errors may expose system information

Secure Code Recommendations

// Secure implementation with prepared statements
$user_id = intval($_GET['id']); // Input validation
$query = $wpdb->prepare(
    "SELECT * FROM users WHERE id = %d",
    $user_id
);
$result = $wpdb->get_results($query);

// Optimized query with JOIN
$posts_with_comments = $wpdb->get_results(
    "SELECT p.*, COUNT(c.id) as comment_count 
     FROM posts p 
     LEFT JOIN comments c ON p.id = c.post_id 
     GROUP BY p.id"
);

Parameters

Language-Specific Analysis

• PHP: WordPress wpdb patterns, PDO security, mysqli prepared statements
• JavaScript/TypeScript: ORM usage patterns, NoSQL injection detection, async query optimization
• Python: Django ORM security, SQLAlchemy patterns, raw query analysis

Advanced Configuration

WordPress Plugin Security Audit: Comprehensive analysis focused on WordPress security best practices and wpdb usage patterns.

// WordPress plugin analysis
houtini-lm:analyze_database_queries with:
- projectPath: "C:/wp-content/plugins/my-plugin"
- language: "php"
- analysisType: "comprehensive"
- context: {
    "framework": "WordPress",
    "database": "MySQL",
    "wpdb": true,
    "custom_tables": true
  }

// API security focused analysis
houtini-lm:analyze_database_queries with:
- filePath: "C:/api/controllers/UserController.js"
- language: "javascript"
- analysisType: "security"
- context: {
    "framework": "Express",
    "orm": "Sequelize",
    "database": "PostgreSQL"
  }

Database Security Workflow:

1. Run analyze_database_queries with security focus
2. Address critical SQL injection vulnerabilities immediately
3. Implement prepared statements and input validation
4. Optimize performance bottlenecks identified
5. Re-run analysis to verify security improvements

Pro Tips

WordPress Security: Always use wpdb->prepare() for dynamic queries and validate user input with WordPress sanitization functions before database operations.

Performance Monitoring: Focus on queries inside loops first – these typically have the highest performance impact and are easier to optimize.

Security Priority: Address SQL injection vulnerabilities before performance optimizations – security issues can lead to complete system compromise.

Related Functions

• security_audit – Comprehensive security analysis including database security patterns
• analyze_single_file – Detailed code analysis for database-heavy files
• analyze_wordpress_security – WordPress-specific security analysis including database patterns
• suggest_refactoring – Code improvement recommendations for database query optimization