postgres/docs/SECURITY_IMPROVEMENTS.md

Security Improvements Summary

Overview

This document summarizes the security improvements made to address issues identified in SECURITY_REVIEW.md.

Issues Resolved

1. ✅ Code Execution Vulnerability in ComponentTreeRenderer (CRITICAL)

Location: src/utils/ComponentTreeRenderer.tsx

Previous Implementation (INSECURE):

// Used new Function() - allows arbitrary code execution
function evaluateCondition(condition: string, data: any): boolean {
  const func = new Function(...Object.keys(data), `return ${condition}`);
  return func(...Object.values(data));
}

Attack Example:

{
  "props": {
    "text": "{{require('fs').readFileSync('/etc/passwd')}}"
  }
}

New Implementation (SECURE):

// Safe property accessor with regex validation
function safeGetProperty(obj: any, path: string): any {
  // Only allows: letters, numbers, dots, underscores
  if (!/^[a-zA-Z_$][a-zA-Z0-9_$.]*$/.test(path)) {
    return undefined;
  }
  
  const parts = path.split('.');
  let current = obj;
  for (const part of parts) {
    if (current == null) return undefined;
    current = current[part];
  }
  return current;
}

Security Improvements:

• ✅ No new Function() or eval() - prevents arbitrary code execution
• ✅ Regex validation: ^[a-zA-Z_$][a-zA-Z0-9_$.]*$
• ✅ Whitelisted operators: ===, !==, >, <, >=, <=, &&, ||
• ✅ Whitelisted Math operations: abs, ceil, floor, round, max, min
• ✅ Blocks: require(), eval(), process, function calls

Supported Patterns:

// ✅ SAFE
"{{user.name}}"
"{{count > 10}}"
"{{isActive ? 'Yes' : 'No'}}"
"{{Math.round(price)}}"

// ❌ BLOCKED
"{{require('fs')}}"
"{{eval('code')}}"
"{{process.exit()}}"

---

2. ✅ SQL Injection Risk in Query Templates (HIGH)

Location: src/config/features.json, src/utils/featureConfig.ts

Previous Implementation (INSECURE):

// String interpolation - vulnerable to SQL injection
function interpolateSqlTemplate(template: SqlTemplate, params: any): string {
  let query = template.query;
  Object.entries(params).forEach(([key, value]) => {
    query = query.replace(`{{${key}}}`, String(value));
  });
  return query;
}

Attack Example:

const tableName = "users; DROP TABLE users--";
interpolateSqlTemplate(template, { tableName });
// Result: CREATE TABLE "users; DROP TABLE users--" (...)

New Implementation (SECURE):

Parameter Type Definitions:

{
  "sqlTemplates": {
    "parameterTypes": {
      "tableName": {
        "type": "identifier",
        "validation": "^[a-zA-Z_][a-zA-Z0-9_]{0,62}$",
        "sanitize": "identifier"
      },
      "dataType": {
        "type": "enum",
        "allowedValues": ["INTEGER", "VARCHAR", "TEXT"],
        "sanitize": "enum"
      }
    }
  }
}

Query Templates with Drizzle Patterns:

{
  "queries": {
    "tables": {
      "dropTable": {
        "parameters": {
          "tableName": "tableName"
        },
        "drizzlePattern": {
          "type": "identifier",
          "example": "sql`DROP TABLE ${sql.identifier([tableName])}`"
        },
        "securityNotes": "Uses sql.identifier() for safe escaping"
      }
    }
  }
}

Validation Functions:

export function validateSqlParameter(paramName: string, value: any) {
  const paramType = getSqlParameterType(paramName);
  
  switch (paramType.type) {
    case 'identifier':
      // PostgreSQL identifier: ^[a-zA-Z_][a-zA-Z0-9_]{0,62}$
      if (!new RegExp(paramType.validation).test(value)) {
        return { valid: false, error: 'Invalid identifier' };
      }
      return { valid: true, sanitized: value };
      
    case 'enum':
      if (!paramType.allowedValues.includes(value)) {
        return { valid: false, error: 'Invalid enum value' };
      }
      return { valid: true, sanitized: value };
  }
}

Security Improvements:

• ✅ Regex validation for identifiers: ^[a-zA-Z_][a-zA-Z0-9_]{0,62}$
• ✅ Enum validation against whitelist
• ✅ Drizzle ORM patterns using sql.identifier()
• ✅ Parameterized queries with $1, $2 placeholders
• ✅ No string interpolation or concatenation
• ✅ Type-safe validation before query execution

Usage Example:

// Validate parameters
const validation = validateSqlTemplateParams('tables', 'dropTable', {
  tableName: 'users'
});

if (!validation.valid) {
  throw new Error(`Invalid parameters: ${validation.errors.join(', ')}`);
}

// Use sanitized values with Drizzle
const { tableName } = validation.sanitized;
await db.execute(sql`DROP TABLE ${sql.identifier([tableName])}`);

Blocks:

// ❌ These will be rejected by validation
validateSqlParameter('tableName', 'users; DROP TABLE users--');
// Returns: { valid: false, error: 'Invalid identifier format' }

validateSqlParameter('dataType', 'MALICIOUS');
// Returns: { valid: false, error: 'Invalid enum value' }

---

3. ✅ Type Safety Issues (MEDIUM)

Location: src/utils/featureConfig.ts

Previous Implementation:

export function getStorybookStory(componentName: string, storyName: string): any {
  return config.storybookStories?.[componentName]?.[storyName];
}

export function getAllStorybookStories(): Record<string, any> {
  return config.storybookStories || {};
}

New Implementation:

export function getStorybookStory(
  componentName: string, 
  storyName: string
): StorybookStory | undefined {
  return config.storybookStories?.[componentName]?.[storyName];
}

export function getAllStorybookStories(): Record<string, Record<string, StorybookStory>> {
  return config.storybookStories || {};
}

Security Improvements:

• ✅ Proper TypeScript types throughout
• ✅ No any types in public APIs
• ✅ Better IDE autocomplete and type checking
• ✅ Compile-time error detection

---

Test Results

All unit tests pass:

✓ unit src/validations/DatabaseIdentifierValidation.test.ts (12 tests)
✓ unit src/utils/featureConfig.test.ts (134 tests)
✓ unit src/utils/Helpers.test.ts (2 tests)

Test Files  3 passed
Tests       148 passed

---

Security Architecture

Component Tree Templates

Threat Model:

• Malicious template expressions in features.json
• Arbitrary JavaScript execution via new Function()
• File system access, network requests, process termination

Mitigation:

1. Safe Property Access: Only dot-notation paths allowed
2. Regex Validation: Path must match ^[a-zA-Z_$][a-zA-Z0-9_$.]*$
3. Whitelisted Operators: Limited to comparison and logical operators
4. Whitelisted Math: Only safe Math operations allowed
5. No Function Calls: Blocks require(), eval(), etc.

SQL Templates

Threat Model:

• SQL injection via table/column names
• Unauthorized data access or modification
• Database schema manipulation

Mitigation:

1. Parameter Validation: All identifiers validated with regex
2. Enum Whitelisting: Data types, index types validated against allowed list
3. Drizzle ORM: Uses sql.identifier() for automatic escaping
4. Parameterized Queries: Uses $1, $2 placeholders
5. No String Interpolation: No template string replacement
6. Type Checking: TypeScript types enforce correct usage

---

Recommendations

For Developers

1. Always validate parameters before using SQL templates:

   const validation = validateSqlTemplateParams(category, template, params);
   if (!validation.valid) throw new Error(validation.errors.join(', '));
   

2. Use Drizzle ORM methods over raw SQL when possible:

   // Preferred
   await db.insert(table).values(data);
   
   // If raw SQL needed
   await db.execute(sql`SELECT * FROM ${sql.identifier([tableName])}`);
   

3. Never bypass validation - always use the provided helper functions

For Reviewers

1. Look for any usage of new Function(), eval(), or string interpolation
2. Verify all SQL queries use parameterized queries or sql.identifier()
3. Check that parameter validation is performed before query execution
4. Ensure no user input is directly concatenated into SQL

---

References

• SECURITY_REVIEW.md: Original security audit findings
• FEATURES_JSON_GUIDE.md: Updated documentation with secure examples
• Drizzle ORM Security
• OWASP SQL Injection Prevention
• PostgreSQL Identifier Rules

---

Conclusion

All critical and high-priority security issues identified in SECURITY_REVIEW.md have been resolved:

✅ Code Execution Vulnerability: Fixed with safe property accessor
✅ SQL Injection Risk: Fixed with parameter validation and Drizzle ORM
✅ Type Safety Issues: Fixed with proper TypeScript types

The redesigned architecture provides multiple layers of defense:

• Input validation with regex patterns
• Whitelist-based operation filtering
• Type-safe query builders
• Automatic identifier escaping
• Parameterized query execution

All changes maintain backward compatibility with existing features while significantly improving security posture.