Cross Site Request Forgery in Chi with Cockroachdb

Cross-site request forgery (CSRF) in a Chi application that uses CockroachDB arises when an authenticated session can be tricked into issuing unauthorized database actions. Chi is a lightweight HTTP routing library for Common Lisp, and when it exposes endpoints that perform mutations without requiring anti-CSRF tokens or strict origin checks, an attacker can craft malicious requests from a victim’s browser. Because CockroachDB is often used in production services for strong consistency and SQL compliance, the backend typically executes parameterized SQL via placeholders. If those endpoints rely solely on session cookies for authentication and do not validate the request origin or use per-request anti-CSRF tokens, an attacker can induce the victim’s browser to perform actions such as updating or deleting records owned by other users.

Consider a Chi route that updates a user’s email without verifying the request source:

(defun update-email-handler (request)
  (let* ((db (getf (chi:request) :db-connection))
         (user-id (hunchentoot:session-value * Hunchentoot:*session*))
         (new-email (cdr (assoc :email (chi:request :parameters)))))
    (postmodern:query db "UPDATE users SET email = $1 WHERE id = $2" (list new-email user-id))
    (chi:ok "Email updated")))

If this route is reachable via a GET request or does not validate the HTTP Referer/Origin, an attacker can embed an image or script tag pointing to this endpoint. When the victim’s browser loads the attacker’s page, the browser sends credentials (cookies) with the request, and CockroachDB executes the update under the victim’s privileges. The same risk applies to any state-changing operation such as inserting records, changing permissions, or invoking transactions that rely only on cookie-based sessions.

In a typical architecture, Chi routes may call helper functions that open a CockroachDB SQL connection using a connection pool. Even when SQL placeholders are used to avoid injection, the absence of CSRF protections means the database operation is authenticated by session context rather than a per-request token. Because CockroachDB does not inherently understand web session boundaries, the backend must enforce CSRF defenses at the application layer. This means validating the request origin, synchronizer token patterns, or leveraging SameSite cookie attributes in combination with secure, HttpOnly flags.

Additionally, developers might use middleware in Chi to inspect headers before routing to handlers. Without explicit checks, a crafted request from a third-party site can exploit the trust relationship between the browser and the API endpoints backed by CockroachDB. The vulnerability is not in CockroachDB itself but in how the Chi application authenticates requests and binds database actions to the intended user and origin.

To remediate CSRF in Chi when interacting with CockroachDB, enforce anti-CSRF tokens on all mutating routes and validate HTTP Referer and Origin headers. Below is a concrete example using Hunchentoot session management and a per-request token stored in the session, verified before any database mutation.

(defun ensure-csrf-token (request)
  (let ((session (hunchentoot:session *))
        (token (cdr (assoc :csrf-token (chi:request :parameters)))))
    (unless (and session token (string= token (hunchentoot:session-value 'csrf-token session)))
      (error "invalid-csrf-token"))))

(defun update-email-secure-handler (request)
  (ensure-csrf-token request)
  (let* ((db (getf (chi:request) :db-connection))
         (user-id (hunchentoot:session-value 'user-id (chi:request)))
         (new-email (cdr (assoc :email (chi:request :parameters)))))
    (postmodern:query db "UPDATE users SET email = $1 WHERE id = $2" (list new-email user-id))
    (chi:ok "Email updated securely")))

In this pattern, the server embeds a CSRF token in the session when the session is created and includes it in forms or requests. Each mutation endpoint calls ensure-csrf-token to compare the submitted token with the session value. This ties the database operation to both the authenticated session and the originating request, preventing cross-origin execution.

Additionally, set SameSite and Secure flags on session cookies to reduce the risk of cross-site transmission:

(defun make-session-middleware (app)
  (lambda (request)
    (let ((response (funcall app request)))
      (setf (hunchentoot:header-out "Set-Cookie")
            (format nil "session_id=~a; Path=/; HttpOnly; Secure; SameSite=Lax"
                    (hunchentoot:session-value 'session-id (hunchentoot:request session))))
      response)))

For endpoints that must accept requests from third-party origins, validate the Origin header explicitly:

(defun verify-origin (request)
  (let ((origin (hunchentoot:header-in "Origin" request))
        (allowed-origin "https://trusted.example.com"))
    (unless (string= origin allowed-origin)
      (error "origin-not-allowed"))))

By combining these measures—per-request tokens, origin checks, and secure cookie attributes—the Chi application ensures that database actions issued to CockroachDB are intentionally initiated by the authenticated user and not by a forged cross-site request.