Add security audit logging for rejected and failed queries

[sgaunet]

Problem

Failed queries are currently logged with the full query text, but there's no explicit audit trail for security-relevant events like rejected queries or potential attack attempts.

Location: main.go:272 and related logging throughout the codebase

Current State

logger.Log().Error().
    Err(err).
    Str("query", query).
    Msg("Failed to execute query")

Issues

1. No security event tracking: Can't easily identify attack patterns
2. Insufficient context: Don't log source, timestamp, or connection info for forensics
3. No alert mechanism: Security events aren't flagged for monitoring
4. Query obfuscation: Sensitive data in queries may be logged verbatim

Proposed Solution

Add dedicated security audit logging for:

1. Rejected queries (failed validation):

logger.Log().Warn().
    Str("event", "query_rejected").
    Str("reason", err.Error()).
    Str("query_prefix", truncateQuery(query, 100)).
    Str("validation_failed", "sql_injection_attempt").
    Msg("Security: Query validation failed")

2. Failed queries (execution errors):

logger.Log().Error().
    Str("event", "query_failed").
    Str("error", err.Error()).
    Str("query_hash", hashQuery(query)).
    Int("query_length", len(query)).
    Msg("Query execution failed")

3. Suspicious patterns:

• Multiple semicolons detected
• Comment-based injection attempts
• Resource-exhausting queries (recursive CTEs)
• Oversized queries

Benefits

• Security monitoring: Easily grep logs for attack attempts
• Incident response: Better forensics for security investigations
• Privacy protection: Don't log full queries with sensitive data
• Alerting: Can set up monitoring on audit events

Implementation Details

// Add security audit helper
func auditSecurityEvent(event string, query string, reason error) {
    logger.Log().Warn().
        Str("event", event).
        Str("reason", reason.Error()).
        Str("query_prefix", truncateQuery(query, 100)).
        Int("query_length", len(query)).
        Time("timestamp", time.Now()).
        Msg("Security audit event")
}

// Helper to safely truncate queries
func truncateQuery(query string, maxLen int) string {
    if len(query) <= maxLen {
        return query
    }
    return query[:maxLen] + "..."
}

Impact

• Severity: LOW
• Type: Security Enhancement, Observability
• Location: Logging throughout main.go and internal/app/client.go

Checklist

• Design audit event schema (event types, fields)
• Implement security audit logging helper
• Add audit calls for validation failures
• Add audit calls for suspicious patterns
• Ensure sensitive data isn't logged (use query hashing/truncation)
• Document audit log format in README
• Add example queries for security monitoring (e.g., grep patterns)