AgentSkillsCN

sql-security

在编写原生 SQL 查询、结合用户输入构建动态查询、使用 ORM/迁移工具或存储过程时，或是进行数据库权限与角色配置，又或是在审查与评估数据库相关安全风险时，务必遵循 SQL 安全实践，包括参数化查询、防注入措施、最小权限原则，以及 ORM 使用的安全性考量。

SKILL.md
--- frontmatter
name: sql-security
description: SQL security practices for parameterized queries, injection prevention, least-privilege access, and ORM safety. Use when writing raw SQL, building queries with user input, working with ORMs/migrations/stored procedures, configuring DB auth/roles, or reviewing database-related security risks.

SQL Security Checklist

Parameterized Queries

  • •Always use parameterized queries (prepared statements)
  • •Never concatenate user input into SQL strings
  • •Use ORM query builders correctly
sql
-- WRONG - vulnerable to SQL injection
SELECT * FROM users WHERE email = '" + user_email + "'

-- CORRECT - parameterized query
SELECT * FROM users WHERE email = ?

Least Privilege Principle

  • •Grant minimum necessary permissions to database users
  • •Use separate accounts for different application components
  • •Never use root/admin accounts for application access
  • •Revoke unnecessary privileges regularly

Data Encryption

  • •Encrypt sensitive columns (PII, financial data, passwords)
  • •Use strong encryption algorithms (AES-256)
  • •Store encryption keys securely (separate from database)
  • •Use database-level encryption features when available

Audit Logging

  • •Enable audit logging for sensitive operations
  • •Log authentication attempts (success and failure)
  • •Log data access for sensitive tables
  • •Retain logs according to compliance requirements

Query Best Practices

  • •Avoid SELECT * in production code - specify columns explicitly
  • •Use LIMIT clauses to prevent resource exhaustion
  • •Validate input data types before queries
  • •Use stored procedures for complex operations

Connection Security

  • •Use TLS/SSL for database connections
  • •Use connection pooling with authentication
  • •Rotate database credentials regularly
  • •Use connection string encryption

Injection Prevention

  • •Always use parameterized queries or ORM bind parameters
  • •Validate inputs and allowlist dynamic identifiers
  • •Rate-limit and monitor unusual query patterns

ORM Security

  • •Understand how your ORM generates SQL
  • •Avoid raw SQL queries when possible
  • •Validate data before passing to ORM
  • •Use ORM's built-in sanitization features
python
# WRONG - raw SQL vulnerable to injection
User.objects.raw("SELECT * FROM users WHERE name = '%s'" % name)

# CORRECT - use ORM query methods
User.objects.filter(name=name)

Database Configuration

  • •Disable unnecessary features and functions
  • •Remove sample databases and default accounts
  • •Use strong authentication mechanisms
  • •Keep database software updated

Protection Layers

  1. •Input Validation - Validate before query
  2. •Parameterized Queries - Use placeholders
  3. •Least Privilege - Minimal database permissions
  4. •WAF Rules - Web Application Firewall patterns
  5. •Monitoring - Detect anomalous queries

Stored Procedures Security

  • •Validate input parameters
  • •Use parameterized statements inside procedures
  • •Grant EXECUTE-only permissions
  • •Avoid dynamic SQL within procedures