Security Features

SQL Injection Prevention

DEV1.0 Picnic uses parameterized queries exclusively. All user input is passed as JDBC parameters, never concatenated into SQL strings.

Example:


// User input
filter: ["firstName", "=", "Robert'); DROP TABLE employees;--"]
 
// Generated SQL (SAFE - uses parameters)
WHERE first_name = :p1
 
// Parameter binding
params.put("p1", "Robert'); DROP TABLE employees;--")

SQL Expression Validation

For developer-defined SQL expressions in annotations:


import com.dev10.picnic.security.SQLExpressionValidator;
 
SQLExpressionValidator validator = new SQLExpressionValidator();
 
// Validates @CalculatedColumn expressions
ValidationResult result = validator.validateExpression("salary * 12");
if (!result.isValid()) {
    throw new SecurityException(result.getReason());
}
 
// Validates @With CTE queries
result = validator.validateWithQuery("SELECT id, name FROM employees WHERE active = true");
result.throwIfInvalid();

The validator blocks:

Dangerous keywords: DROP, DELETE, INSERT, UPDATE, ALTER, CREATE, etc.
SQL comments: --, #, /* */
Stacked queries: ; followed by SQL commands
UNION-based injection attempts
Suspicious encoding (hex, binary)

DoS Attack Mitigation

Default Protection Limits

Max skip: 100,000 rows
Max take: 10,000 rows
Max filter depth: 20 levels
Max filter conditions: 100
Max group fields: 10
Max sort fields: 10

Configure Custom Limits


import com.dev10.picnic.validation.LoadOptionsValidator;
 
// Conservative validation for public APIs
LoadOptionsValidator validator = LoadOptionsValidator.builder()
    .maxSkip(10_000)           // Only first 10K rows accessible
    .maxTake(1_000)            // Max 1K rows per request
    .maxFilterDepth(10)        // Max 10 levels of nesting
    .maxFilterConditions(50)   // Max 50 filter conditions
    .maxGroupFields(5)         // Max 5 group columns
    .maxSortFields(5)          // Max 5 sort columns
    .build();
 
GridDataProvider gridDataProvider = new GridDataProvider(dataSource, validator);

Absolute Maximums (Cannot Be Exceeded)

Max skip: 1,000,000
Max take: 50,000
Max filter depth: 50
Max filter conditions: 500
Max group fields: 20
Max sort fields: 20

Query Timeout Protection

Prevent slow query DoS attacks:


GridDataProvider gridDataProvider = new GridDataProvider(dataSource);
 
// Conservative timeout for public APIs
gridDataProvider.setQueryTimeout(10); // 10 seconds
 
// Standard timeout (default)
gridDataProvider.setQueryTimeout(30); // 30 seconds
 
// Relaxed timeout for reporting
gridDataProvider.setQueryTimeout(60); // 60 seconds
 
// Disable timeout (not recommended for production)
gridDataProvider.setQueryTimeout(0);  // No timeout

Security Best Practices

Always use validation in production:


LoadOptionsValidator validator = LoadOptionsValidator.builder()
    .maxTake(1_000)
    .maxSkip(10_000)
    .build();
GridDataProvider gridDataProvider = new GridDataProvider(dataSource, validator);

Set appropriate query timeouts:


gridDataProvider.setQueryTimeout(30); // 30 seconds for OLTP

Never disable validation for public APIs
Use DataSource mode for connection pooling
Validate developer-defined SQL expressions in CI/CD