server/src/api/analytics/utils/getFilterStatement.ts (2)

101-121: Session-level subquery uses raw param instead of getSqlParam(param).

When buildSessionLevelSubquery is called with a parameter from sessionLevelParams, it uses the raw param directly in the SQL condition (line 111). However, parameters like pathname don't need transformation, but if someone adds a parameter like referrer (which maps to domainWithoutWWW(referrer)) to sessionLevelParams, the SQL would be incorrect.

Consider using getSqlParam(param) for consistency, or document that sessionLevelParams must only contain parameters that don't require SQL transformation.

♻️ Suggested fix

   const buildSessionLevelSubquery = (
     param: FilterParameter,
     filterType: FilterType,
     values: (string | number)[],
     wildcardPrefix: string
   ): string => {
     const whereClause = [siteIdFilter, timeFilter].filter(Boolean).join(" AND ");
+    const sqlParam = getSqlParam(param);
     const condition =
       values.length === 1
-        ? `${param} ${filterTypeToOperator(filterType)} ${SqlString.escape(wildcardPrefix + values[0] + wildcardPrefix)}`
-        : `(${values.map(value => `${param} ${filterTypeToOperator(filterType)} ${SqlString.escape(wildcardPrefix + value + wildcardPrefix)}`).join(" OR ")})`;
+        ? `${sqlParam} ${filterTypeToOperator(filterType)} ${SqlString.escape(wildcardPrefix + values[0] + wildcardPrefix)}`
+        : `(${values.map(value => `${sqlParam} ${filterTypeToOperator(filterType)} ${SqlString.escape(wildcardPrefix + value + wildcardPrefix)}`).join(" OR ")})`;

---

304-311: Consider using replaceAll() instead of regex to avoid static analysis warnings.

The static analysis tool flagged a potential ReDoS vulnerability on line 308. While this is likely a false positive (since fieldMappings are developer-controlled constants, not user input), you can silence the warning and simplify the code by using String.prototype.replaceAll():

♻️ Suggested fix using replaceAll

   // Apply field mappings if provided (for CTEs that extract fields to different column names)
   if (options?.fieldMappings) {
     for (const [from, to] of Object.entries(options.fieldMappings)) {
-      // Escape special regex characters in the 'from' string
-      const escapedFrom = from.replace(/[.*+?^${}()|[\]\\]/g, "\\$&");
-      result = result.replace(new RegExp(escapedFrom, "g"), to);
+      result = result.replaceAll(from, to);
     }
   }

server/src/api/analytics/getSessions.ts (1)

60-67: Consider extracting shared SESSION_FIELD_MAPPINGS to avoid duplication.

This constant is identical to the one defined inline in getFunnelStepSessions.ts (lines 69-75). Consider extracting it to a shared location (e.g., utils/getFilterStatement.ts or a constants file) to maintain consistency and reduce duplication.

♻️ Example shared constant in getFilterStatement.ts

// In server/src/api/analytics/utils/getFilterStatement.ts
export const UTM_FIELD_MAPPINGS: Record<string, string> = {
  "url_parameters['utm_source']": "utm_source",
  "url_parameters['utm_medium']": "utm_medium",
  "url_parameters['utm_campaign']": "utm_campaign",
  "url_parameters['utm_term']": "utm_term",
  "url_parameters['utm_content']": "utm_content",
};

Then import and use in both getSessions.ts and getFunnelStepSessions.ts.