Safe Error Messages

-- SCOPE: defensive learning on the bundled lab app. Authorised testing only.

-- ===== Why errors matter =====
-- Error-based SQLi reveals state via the DB error message.
-- A response that includes 'syntax error near "UNION"' tells attackers exactly where
-- they broke out of the string. Verbose errors are a leak.

-- ===== The defensive shape =====
-- 1. Clients see GENERIC errors (no DB text)
-- 2. Logs capture DETAILED context (template + error class + user + route)
-- 3. DB itself trims verbose hints in production

-- ===== Client-facing =====
// Express / Node example
app.use((err, req, res, next) => {
  // Log the rich context (server-side only):
  logger.error({
    msg: 'unhandled',
    route: req.route?.path,
    user_id: req.user?.id,
    err_class: err.code,        // e.g. '42601'
    err_message: err.message,
    sql_template: err.query?.slice(0, 200),  // template only, never values
  });
  // Send the user a generic message:
  res.status(500).json({ error: 'internal_error', request_id: req.id });
});

-- ===== Postgres: keep error detail OUT of the network =====
-- postgresql.conf
log_min_error_statement = error           -- log to file, not to client
log_min_duration_statement = 500          -- log slow queries

-- Application:
-- Drivers expose SQLSTATE codes (5-char). Use them — they classify shape:
--   class '42'  syntax / access rule violation     <- often probes
--   class '23'  integrity constraint violation
--   class '40'  transaction rollback
--   class '25'  invalid transaction state
--   class '53'  insufficient resources

-- Aggregating spikes of '42' by user + route is a strong probe signal.

-- ===== MySQL: equivalents =====
-- /etc/mysql/my.cnf
log_error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5

-- ===== Stored procedures + sqlstate =====
-- If you write custom error messages in procs, do not echo input verbatim:
RAISE EXCEPTION USING ERRCODE = '22023', MESSAGE = 'Validation failed';
-- Not:
-- RAISE EXCEPTION 'Validation failed for value: %', input;

-- ===== Logging structure =====
{
  "ts": "2024-04-10T03:14:00Z",
  "level": "error",
  "route": "/api/orders/:id",
  "user_id": "u-1",
  "ip_hash": "abc...",
  "sql_template": "SELECT id FROM orders WHERE user_id = $1",
  "err_class": "42601",
  "err_short": "syntax error",
  "deploy_id": "v1.42"
}

-- Never log: parameter values, full SQL with values, secrets.
-- Always include: route, user_id, error class, template, deploy_id.

-- ===== Detection rules =====
-- Alert when:
-- 1. err_class='42' rate from a single user_id or ip_hash exceeds N/min
-- 2. err_class='42' appears on a route that historically had zero
-- 3. Slow-query log on a hot endpoint shows mention of multi-statement patterns
-- 4. Sudden uptick in DISTINCT sql_template values per route (suggests fuzzing)

-- ===== Incident response =====
-- 1. Capture: snapshot recent logs around the spike
-- 2. Contain: deploy WAF rule blocking the offending payload; consider per-user lockout
-- 3. Eradicate: ensure the offending endpoint is parameterised; deploy fix
-- 4. Recover: replay queries with safe parameters; verify no data exfil happened
-- 5. Lessons: add a test that exercises the previously-bad input

-- ===== Patterns to internalise =====
-- - Generic 500 + request_id to clients; detail in logs only
-- - Log shape (template + class), never values
-- - Alert on err_class spikes, not on keywords
-- - Disable verbose DB errors in prod

-- ===== Pitfalls =====
-- - Echoing 'syntax error near X' to the API consumer
-- - Logging full SQL with parameter values -> PII / PCI exposure
-- - Storing raw IPs as permanent identifiers (hash + salt)
-- - No deploy_id in logs -> cannot tell expected DDL from intrusion DDL

// Never show raw DB errors to users.
try { … } catch (err) {
    req.log.error({ err }, 'db');
    res.status(500).send({ error: 'internal_error' });
}