SQL Template Operator

Overview

The SQL Template Operator is a powerful dynamic SQL generation tool, similar to the SQL template functionality in MyBatis. It supports parameter substitution using {{}} syntax and provides rich features for dynamic SQL generation, along with comprehensive security protection mechanisms.

Core Features

• ✅ MyBatis-Style Syntax: Uses {{}} markers for parameter positions
• ✅ Nested Property Access: Supports deep property access like user.name, user.profile.email
• ✅ Default Value Support: {{param:defaultValue}} syntax for setting default values
• ✅ Modifier Functions: Supports data type conversion and formatting modifiers
• ✅ SQL Injection Protection: Built-in security checks to prevent SQL injection attacks
• ✅ Strict Mode: Optional strict parameter checking mode
• ✅ Whitespace Cleanup: Automatic SQL formatting optimization
• ✅ Detailed Execution Reports: Provides parameter substitution details and performance statistics

Basic Usage

Operator Configuration

Parameter	Type	Default	Description
enableSafeMode	boolean	true	Enable SQL injection protection
strictMode	boolean	false	Strict mode: throws error when parameter not found
nullValue	string	'NULL'	Replacement string for null values
trimWhitespace	boolean	true	Automatically clean excess whitespace

Input Parameters

Parameter	Type	Required	Description
template	string	✅	SQL template string, using {{}} markers for parameter positions
params	object	❌	Parameter object containing values to substitute

Output Results

Field	Type	Description
sql	string	Generated SQL statement
originalTemplate	string	Original template string
parameters	object	Input parameter object
replacedParams	array	Parameter substitution detail list
unusedParams	array	List of unused parameters
executedAt	string	Execution timestamp
executionTime	number	Execution duration in milliseconds
templateLength	number	Template length
sqlLength	number	Generated SQL length

Usage Examples

1. Basic Parameter Substitution

{
  "template": "SELECT * FROM users WHERE id = {{userId}} AND name = {{userName}}",
  "params": {
    "userId": 123,
    "userName": "John"
  }
}

Output:

SELECT * FROM users WHERE id = 123 AND name = John

2. Using Default Values

{
  "template": "SELECT * FROM users WHERE status = {{status:active}} LIMIT {{limit:10}}",
  "params": {
    "status": "inactive"
  }
}

Output:

SELECT * FROM users WHERE status = inactive LIMIT 10

3. Nested Property Access

{
  "template": "SELECT * FROM users WHERE email = {{user.profile.email}} AND age > {{user.age}}",
  "params": {
    "user": {
      "age": 25,
      "profile": {
        "email": "user@example.com"
      }
    }
  }
}

Output:

SELECT * FROM users WHERE email = user@example.com AND age > 25

4. Modifier Functions

String Modifiers

{
  "template": "SELECT * FROM users WHERE name = {{name|upper|quote}} AND city = {{city|lower|trim}}",
  "params": {
    "name": "john doe",
    "city": "  BEIJING  "
  }
}

Output:

SELECT * FROM users WHERE name = 'JOHN DOE' AND city = beijing

Type Conversion Modifiers

{
  "template": "UPDATE users SET age = {{age|int}}, is_active = {{active|bool}} WHERE id = {{id}}",
  "params": {
    "age": "25",
    "active": "true",
    "id": 123
  }
}

Output:

UPDATE users SET age = 25, is_active = 1 WHERE id = 123

Array Processing Modifiers

{
  "template": "SELECT * FROM users WHERE id IN ({{ids|join}}) AND tags LIKE {{tags|join:|}}",
  "params": {
    "ids": [1, 2, 3, 4],
    "tags": ["tech", "java", "sql"]
  }
}

Output:

SELECT * FROM users WHERE id IN (1,2,3,4) AND tags LIKE tech|java|sql

Modifier Reference

Basic Modifiers

Modifier	Description	Example
upper	Convert to uppercase	{{name|upper}} → JOHN
lower	Convert to lowercase	{{name|lower}} → john
trim	Trim leading/trailing whitespace	{{name|trim}} → john
quote	Add single quotes with escaping	{{name|quote}} → 'john'
doublequote	Add double quotes with escaping	{{name|doublequote}} → "john"

Type Conversion Modifiers

Modifier	Description	Example
int	Convert to integer	{{age|int}} → 25
float	Convert to float	{{price|float}} → 99.99
bool	Convert to boolean (true/false; in safe mode converts to 1/0 for SQL)	{{active|bool}} → true or false
json	Convert to JSON string	{{data|json}} → {"key":"value"}

Array/String Modifiers

Modifier	Description	Example
length	Get length	\{\{items|length\}\} → 3
reverse	Reverse array or string	\{\{items|reverse\}\} → [3,2,1]
join	Join array (default comma)	`{{items
join:separator	Join with specified separator	\{\{items|join:|\}\} → 1|2|3

Parameterized Modifiers

Modifier	Description	Example
substr:start:length	Substring extraction	{{text|substr:0:5}} → hello
pad:length:char	String padding	{{id|pad:5:0}} → 00123
replace:search:replace	String replacement	{{text|replace: :_}} → hello_world

Advanced Features

1. Complex Query Example

{
  "template": "SELECT u.*, p.email, p.phone FROM users u LEFT JOIN profiles p ON u.id = p.user_id WHERE u.status = {{status:active}} AND u.created_at >= {{startDate}} ORDER BY u.{{sortBy:created_at}} {{sortOrder:DESC}} LIMIT {{limit:20}}",
  "params": {
    "status": "verified",
    "startDate": "2024-01-01",
    "sortBy": "name",
    "sortOrder": "ASC",
    "limit": 50
  }
}

2. Dynamic Update Statement

{
  "template": "UPDATE users SET name = {{user.name|quote}}, email = {{user.email|lower|quote}}, age = {{user.age|int}}, updated_at = NOW() WHERE id = {{user.id|int}}",
  "params": {
    "user": {
      "id": "123",
      "name": "John Doe",
      "email": "JOHN@EXAMPLE.COM",
      "age": "30"
    }
  }
}

3. Conditional Insert Statement

{
  "template": "INSERT INTO user_logs (user_id, action, details, created_at) VALUES ({{userId|int}}, {{action|quote}}, {{details|json|quote}}, {{timestamp:NOW()}})",
  "params": {
    "userId": 123,
    "action": "login",
    "details": {
      "ip": "192.168.1.1",
      "userAgent": "Mozilla/5.0..."
    }
  }
}

Security Features

SQL Injection Protection

The SQL Template Operator has built-in multi-layered SQL injection protection:

1. Dangerous Keyword Detection: Automatically detects dangerous SQL keywords like DROP, DELETE, UPDATE
2. Special Character Escaping: Automatically escapes dangerous characters like single quotes and semicolons
3. System Function Interception: Detects system stored procedure calls like xp_, sp_

Example:

{
  "template": "SELECT * FROM users WHERE name = {{name}}",
  "params": {
    "name": "'; DROP TABLE users; --"
  }
}

Safe Output:

SELECT * FROM users WHERE name = ''; DROP TABLE users; --

Strict Mode

When strict mode is enabled, missing parameters will cause execution to fail:

{
  "enableSafeMode": true,
  "strictMode": true,
  "template": "SELECT * FROM users WHERE id = {{unknownParam}}",
  "params": {}
}

Error Output:

{
  "error": "Parameter 'unknownParam' not found and has no default value",
  "failed": true
}

Workflow Integration

Using in Workflows

1. Add SQL Template Node

   • Select "SQL Template" from the operator library
   • Configure the template string and parameters

2. Connect Data Sources

   • Use preceding node outputs as parameter inputs
   • Supports dynamic parameter binding

3. Downstream Processing

   • Pass the generated SQL to a database execution node
   • Or use it for logging, auditing, and other scenarios

Combining with Other Operators

{
  "workflow": [
    {
      "type": "data-source",
      "output": "userData"
    },
    {
      "type": "sql-template",
      "inputs": {
        "template": "SELECT * FROM orders WHERE user_id = {{userData.id}} AND status = {{status:pending}}",
        "params": "{{userData}}"
      },
      "output": "sqlQuery"
    },
    {
      "type": "database-executor",
      "inputs": {
        "sql": "{{sqlQuery.sql}}"
      }
    }
  ]
}

Performance Optimization

Best Practices

1. Template Caching: Identical templates are automatically optimized
2. Parameter Preprocessing: Perform data type conversions in advance
3. Moderate Modifier Usage: Avoid excessively long modifier chains
4. Batch Processing: For many similar queries, consider batch processing

Performance Metrics

• Processing Speed: Typically completes within 1-5 milliseconds
• Memory Usage: Supports templates up to 1MB in size
• Concurrency Support: Supports high-concurrency scenarios

Error Handling

Common Error Types

Error Type	Cause	Solution
Empty template	template parameter not provided	Check input parameters
Parameter not found	Missing required parameter in strict mode	Provide the parameter or set a default value
Modifier error	Unsupported modifier used	Check modifier spelling and syntax
Type conversion failure	Unable to convert data type	Ensure data format is correct

Error Response Format

{
  "error": "Error description",
  "originalTemplate": "Original template",
  "parameters": {},
  "executedAt": "2024-01-01T00:00:00.000Z",
  "failed": true
}

Troubleshooting

Debugging Tips

1. Check Parameter Substitution Details: Review the replacedParams field
2. Monitor Unused Parameters: Check unusedParams for possible typos
3. Enable Verbose Logging: Review console debug output
4. Test Simple Templates: Start with basic functionality and gradually increase complexity

FAQ

Q: Why wasn't my parameter replaced? A: Check parameter name spelling, nested path correctness, and whether strict mode is enabled.

Q: How do I handle data containing special characters? A: Use the quote modifier for automatic escaping, or enable safe mode for automatic handling.

Q: The generated SQL formatting looks messy. What can I do? A: Enable the trimWhitespace option to automatically clean up whitespace.

Version Information

• Current Version: 1.0.0
• Compatibility: Node.js 14+
• Changelog: See project update records

Related Resources

• Operator Development Guide
• Workflow Overview
• Operator Configuration Guide