zntr.io/cel2squirrel

cel2squirrel

A Go package that converts Common Expression Language (CEL) expressions to SQL WHERE clauses using the Squirrel SQL builder.

Overview

cel2squirrel enables filtering using CEL expressions (as specified in AIP-160) and converts them to SQL queries that can be executed against a database. It validates that expressions are boolean and provides type-safe conversion to Squirrel SQL builder objects.

Features

• ✅ CEL Expression Parsing: Parse and validate CEL expressions
• ✅ Boolean Validation: Ensure expressions return boolean values
• ✅ Type-Safe Conversion: Convert CEL AST to Squirrel SQL builder
• ✅ Field Mapping: Map CEL field names to SQL column names
• ✅ Operator Support:
  • Comparison: ==, !=, <, <=, >, >=
  • Logical: && (AND), || (OR), ! (NOT)
  • String: contains(), startsWith(), endsWith()
  • Membership: in operator
  • Null: == null, != null
• ✅ PostgreSQL Compatible: Works with PostgreSQL placeholder format ($1, $2, etc.)
• 🔒 Security Hardened: SQL injection protection, DoS prevention, field-level authorization

Installation

go get zntr.io/cel2squirrel

Usage

Basic Example

package main

import (
    "fmt"
    "log"
    
    "github.com/Masterminds/squirrel"
    "github.com/google/cel-go/cel"
    "zntr.io/cel2squirrel"
)

func main() {
    // Define field declarations (CEL variables and their types)
    config := cel2squirrel.Config{
        FieldDeclarations: map[string]*cel.Type{
            "status": cel.StringType,
            "age":    cel.IntType,
            "rating": cel.DoubleType,
        },
    }
    
    // Create converter
    converter, err := cel2squirrel.NewConverter(config)
    if err != nil {
        log.Fatal(err)
    }
    
    // Convert CEL expression to SQL
    celExpr := `status == "published" && age >= 18`
    result, err := converter.Convert(celExpr, nil)
    if err != nil {
        log.Fatal(err)
    }
    
    // Build complete query with Squirrel
    query := squirrel.Select("*").
        From("users").
        Where(result.Where)
    
    sql, args, _ := query.ToSql()
    fmt.Println(sql)
    // Output: SELECT * FROM users WHERE (status = ? AND age >= ?)
    fmt.Println(args)
    // Output: [published 18]
}

With Field Mappings

Map CEL field names to different SQL column names:

config := cel2squirrel.Config{
    FieldDeclarations: map[string]*cel.Type{
        "isDraft": cel.BoolType,
        "ownerId": cel.StringType,
    },
}

converter, _ := cel2squirrel.NewConverter(config)

// Map camelCase CEL fields to snake_case SQL columns
fieldMappings := map[string]string{
    "isDraft": "is_draft",
    "ownerId": "owner_id",
}

celExpr := `isDraft == false && ownerId == "user123"`
result, _ := converter.Convert(celExpr, fieldMappings)

sql, args, _ := result.Where.ToSql()
// SQL: (is_draft = ? AND owner_id = ?)
// Args: [false user123]

PostgreSQL Placeholders

Use PostgreSQL-style numbered placeholders:

query := squirrel.Select("*").
    From("users").
    Where(result.Where).
    PlaceholderFormat(squirrel.Dollar)

sql, args, _ := query.ToSql()
// SQL: SELECT * FROM users WHERE (status = $1 AND age >= $2)

Complex Expressions

Handle complex nested boolean expressions:

celExpr := `(status == "published" || status == "featured") && age >= 18 && rating > 4.0`
result, _ := converter.Convert(celExpr, nil)

query := squirrel.Select("id", "name", "rating").
    From("users").
    Where(result.Where).
    OrderBy("rating DESC").
    Limit(10)

sql, args, _ := query.ToSql()
// SQL: SELECT id, name, rating FROM users 
//      WHERE ((status = ? OR status = ?) AND age >= ? AND rating > ?) 
//      ORDER BY rating DESC LIMIT 10
// Args: [published featured 18 4.0]

String Operations

Use CEL string methods:

// Contains
celExpr := `name.contains("john")`
// SQL: name LIKE ?
// Args: [%john%]

// Starts with
celExpr := `name.startsWith("Dr. ")`
// SQL: name LIKE ?
// Args: [Dr. %]

// Ends with
celExpr := `email.endsWith("@example.com")`
// SQL: email LIKE ?
// Args: [[e-mail]]

Null Comparisons

Handle NULL values:

// IS NULL
celExpr := `deletedAt == null`
// SQL: deletedAt IS NULL

// IS NOT NULL
celExpr := `deletedAt != null`
// SQL: deletedAt IS NOT NULL

IN Operator

Filter with multiple values:

celExpr := `status in ["published", "featured", "archived"]`
result, _ := converter.Convert(celExpr, nil)

sql, args, _ := result.Where.ToSql()
// SQL: status IN (?,?,?)
// Args: [published featured archived]

Real-World Example

Example implementation of a database repository with CEL filtering (AIP-160 compliant):

package repository

import (
    "context"
    "fmt"
    
    "github.com/Masterminds/squirrel"
    "github.com/google/cel-go/cel"
    "zntr.io/cel2squirrel"
)

type UserRepository struct {
    db        *sql.DB
    converter *cel2squirrel.Converter
}

func NewUserRepository(db *sql.DB) (*UserRepository, error) {
    // Setup CEL converter
    config := cel2squirrel.Config{
        FieldDeclarations: map[string]*cel.Type{
            "status":     cel.StringType,
            "isActive":   cel.BoolType,
            "rating":     cel.DoubleType,
            "createTime": cel.TimestampType,
        },
    }
    
    converter, err := cel2squirrel.NewConverter(config)
    if err != nil {
        return nil, fmt.Errorf("failed to create CEL converter: %w", err)
    }
    
    return &UserRepository{
        db:        db,
        converter: converter,
    }, nil
}

func (r *UserRepository) Search(ctx context.Context, filter string, pageSize int) ([]User, error) {
    // Map CEL fields to SQL columns
    fieldMappings := map[string]string{
        "isActive":   "is_active",
        "createTime": "create_time",
    }
    
    // Convert CEL filter to SQL
    result, err := r.converter.Convert(filter, fieldMappings)
    if err != nil {
        return nil, fmt.Errorf("invalid filter expression: %w", err)
    }
    
    // Build query with Squirrel
    query := squirrel.Select("*").
        From("users").
        Where(result.Where).
        PlaceholderFormat(squirrel.Dollar).
        Limit(uint64(pageSize))
    
    sql, args, err := query.ToSql()
    if err != nil {
        return nil, err
    }
    
    // Execute query
    rows, err := r.db.QueryContext(ctx, sql, args...)
    if err != nil {
        return nil, err
    }
    defer rows.Close()
    
    // Scan results...
    var users []User
    for rows.Next() {
        var u User
        // Scan into user struct...
        users = append(users, u)
    }
    
    return users, rows.Err()
}

API Usage Example

GET /v1/users?filter=status == "active" && rating > 4.0&page_size=20

Supported CEL Operations

Comparison Operators

CEL Operator	SQL Equivalent	Example
==	=	status == "published"
!=	<> or IS NOT	status != "draft"
<	<	age < 18
<=	<=	age <= 21
>	>	age > 65
>=	>=	rating >= 4.5

Logical Operators

CEL Operator	SQL Equivalent	Example
&&	AND	status == "published" && age >= 18
||	OR	status == "draft" || status == "published"
!	NOT	!(isDraft)

String Operations

CEL Function	SQL Equivalent	Example
contains(x)	LIKE '%x%'	label.contains("test")
startsWith(x)	LIKE 'x%'	label.startsWith("prod")
endsWith(x)	LIKE '%x'	label.endsWith("v2")

Membership Operators

CEL Operator	SQL Equivalent	Example
in	IN (...)	status in ["published", "featured"]

Null Comparisons

CEL Expression	SQL Equivalent	Example
field == null	IS NULL	deletedAt == null
field != null	IS NOT NULL	deletedAt != null

Error Handling

The converter validates expressions and returns descriptive errors:

// Non-boolean expression
celExpr := `age + 5`  // Returns: "CEL expression must return a boolean, got int"

// Undefined field
celExpr := `unknownField == "value"`  // Returns: "failed to compile CEL expression: undeclared reference..."

// Syntax error
celExpr := `status == `  // Returns: "failed to compile CEL expression: Syntax error..."

Type Declarations

Use CEL types directly to define field types:

import "github.com/google/cel-go/cel"

FieldDeclarations: map[string]*cel.Type{
    "stringField":    cel.StringType,
    "intField":       cel.IntType,
    "doubleField":    cel.DoubleType,
    "boolField":      cel.BoolType,
    "uintField":      cel.UintType,
    "timestampField": cel.TimestampType,
    "durationField":  cel.DurationType,
    "bytesField":     cel.BytesType,
    
    // Complex types
    "stringList":     cel.ListType(cel.StringType),
    "stringMap":      cel.MapType(cel.StringType, cel.StringType),
}

Limitations

• No Function Calls: Custom CEL functions are not supported (only built-in string methods)
• Simple Expressions: Complex nested member access is limited
• List Literals Only: The in operator requires constant list literals
• No Arithmetic in Filters: Expressions like age + 5 > 30 are not supported

Performance Considerations

• Prepared Statements: The generated SQL is parameterized and suitable for prepared statements
• Validation: CEL expressions are validated before conversion, preventing SQL injection
• Caching: Consider caching converter instances for frequently used field declarations

Security

Overview

The cel2squirrel package implements multiple layers of security to protect against common vulnerabilities when converting user-supplied filter expressions to SQL queries. All security features are enabled by default with secure defaults.

SQL Injection Protection

LIKE Pattern Escaping: The package automatically escapes SQL special characters (%, _, \, [, ]) in string operations to prevent LIKE pattern injection:

// User input: name.contains("%")
// Unsafe: LIKE '%%%'  (matches all records!)
// Safe:   LIKE '%\%%' (matches literal % character)

celExpr := `name.contains("%admin")`
result, _ := converter.Convert(celExpr)
// Generates: name LIKE ?
// Args: [%\%admin%]  // Special chars properly escaped

Parameterized Queries: All values are passed as query parameters, never concatenated into SQL strings:

celExpr := `status == "published" && age > 18`
// Generates parameterized SQL: status = ? AND age > ?
// Args: ["published", 18]

Denial of Service Prevention

Configure expression complexity limits to prevent resource exhaustion:

config := cel2squirrel.Config{
    FieldDeclarations: map[string]cel2squirrel.ColumnMapping{
        "status": {Type: cel.StringType, Column: "status"},
    },

    // Security limits (default values shown)
    MaxExpressionLength: 10000,  // Max 10KB expression
    MaxExpressionDepth:  50,     // Max 50 levels of nesting
    MaxInClauseSize:     1000,   // Max 1000 values in IN clause
}

converter, _ := cel2squirrel.NewConverter(config)

// These will be rejected:
_, err := converter.Convert(strings.Repeat("a", 20000))  // Too long
_, err := converter.Convert(deeplyNestedExpression)       // Too deep
_, err := converter.Convert(`status in [...]`)            // Too many values

Field-Level Authorization

Restrict which fields users can filter by based on their roles:

config := cel2squirrel.Config{
    FieldDeclarations: map[string]cel2squirrel.ColumnMapping{
        "status":   {Type: cel.StringType, Column: "status"},
        "owner_id": {Type: cel.StringType, Column: "owner_id"},
        "salary":   {Type: cel.IntType, Column: "salary"},
    },

    // Public fields accessible to all users
    PublicFields: []string{"status"},

    // Role-based access control
    FieldACL: map[string][]string{
        "owner_id": {"admin", "manager"},
        "salary":   {"admin", "hr"},
    },
}

converter, _ := cel2squirrel.NewConverter(config)

// Check authorization before conversion
userRoles := []string{"user"}
_, err := converter.ConvertWithAuth(`owner_id == "user123"`, userRoles)
// Returns: "access denied: insufficient permissions"

adminRoles := []string{"admin"}
result, _ := converter.ConvertWithAuth(`owner_id == "user123"`, adminRoles)
// Success: admin can filter by owner_id

Error Message Sanitization

The package sanitizes error messages to prevent information disclosure:

// Internal error with details (logged server-side)
convErr := err.(*cel2squirrel.ConversionError)
log.Printf("Conversion failed: %v", convErr.InternalError)
// Logs: "undeclared reference to 'secretField'"

// Public error message (returned to user)
fmt.Println(convErr.Error())
// Returns: "invalid filter expression syntax"
// Does NOT reveal field names or internal structure

Runtime Type Validation

Defense-in-depth type checking validates that values match declared field types:

config := cel2squirrel.Config{
    FieldDeclarations: map[string]cel2squirrel.ColumnMapping{
        "age": {Type: cel.IntType, Column: "age"},
    },
}

converter, _ := cel2squirrel.NewConverter(config)

// CEL's type system catches this at compile time
_, err := converter.Convert(`age == "not a number"`)
// Returns type mismatch error

// Runtime validation provides additional protection

Secure Configuration Example

A production-ready secure configuration:

func NewSecureConverter() (*cel2squirrel.Converter, error) {
    config := cel2squirrel.DefaultConfig() // Start with secure defaults

    config.FieldDeclarations = map[string]cel2squirrel.ColumnMapping{
        "status":     {Type: cel.StringType, Column: "status"},
        "created_at": {Type: cel.TimestampType, Column: "created_at"},
        "owner_id":   {Type: cel.StringType, Column: "owner_id"},
        "is_private": {Type: cel.BoolType, Column: "is_private"},
    }

    // Define public fields
    config.PublicFields = []string{"status", "created_at"}

    // Restrict sensitive fields
    config.FieldACL = map[string][]string{
        "owner_id":   {"admin", "manager"},
        "is_private": {"admin"},
    }

    // Adjust limits for your use case
    config.MaxExpressionLength = 1000  // Shorter limit for API
    config.MaxExpressionDepth = 10     // Prevent deeply nested attacks
    config.MaxInClauseSize = 100       // Reasonable batch size

    return cel2squirrel.NewConverter(config)
}

// Usage in HTTP handler
func (h *Handler) SearchRecords(w http.ResponseWriter, r *http.Request) {
    filter := r.URL.Query().Get("filter")

    // Get user roles from authentication context
    userRoles := h.getUserRoles(r.Context())

    // Convert with authorization check
    result, err := h.converter.ConvertWithAuth(filter, userRoles)
    if err != nil {
        if convErr, ok := err.(*cel2squirrel.ConversionError); ok {
            // Return sanitized error to user
            http.Error(w, convErr.PublicMessage, http.StatusBadRequest)

            // Log detailed error server-side
            log.Printf("Filter conversion failed: %v (code=%s)",
                convErr.InternalError, convErr.ErrorCode)
            return
        }
        http.Error(w, "Invalid filter", http.StatusBadRequest)
        return
    }

    // Build and execute query...
}

Security Best Practices

• Always use field-level authorization for multi-tenant or multi-user systems
• Log security events to detect attack patterns
• Set conservative limits for expression complexity based on your use case
• Never expose internal errors to end users
• Use ConvertWithAuth() instead of Convert() when authorization is configured
• Validate user roles from trusted authentication context, never from user input
• Monitor for unusual patterns like deeply nested expressions or large IN clauses
• Test security controls regularly with security-focused test cases
• Keep dependencies updated (CEL, Squirrel) for security patches

Testing

Run tests:

go test -v ./...

Run with coverage:

go test -cover ./...

Run fuzz tests:

go test -fuzz=Fuzz -fuzztime=30s

Contributing

Contributions are welcome! Please feel free to submit a Pull Request. For major changes, please open an issue first to discuss what you would like to change.

Development Setup

• Clone the repository
• Install dependencies: go mod download
• Run tests: go test ./...
• Run linter: go vet ./...

Guidelines

• Write tests for new features
• Follow Go best practices and idioms
• Update documentation for API changes
• Ensure all tests pass before submitting PR

Use Cases

This library is ideal for:

• REST APIs: Implement AIP-160 compliant filtering in your API endpoints
• Multi-tenant Applications: Safe, field-level authorization for database queries
• Admin Dashboards: Flexible search and filtering capabilities
• Data Export Tools: User-defined filtering without SQL injection risks
• GraphQL Backends: Convert filter arguments to SQL queries

References

• Google AIP-160: Filtering
• CEL Specification
• CEL Go Library
• Squirrel SQL Builder

License

MIT License - see LICENSE file for details.

Support

• Report issues on GitHub Issues
• For questions and discussions, use GitHub Discussions

Acknowledgments

Built with:

• google/cel-go - Common Expression Language implementation
• Masterminds/squirrel - SQL query builder