PostgreSQL MCP Server

# Query Validation Examples This document demonstrates the PostgreSQL MCP Server's intelligent query validation across different scenarios. ## ✅ Good Queries - Best Practices ### Example 1: Well-Optimized Analytics Query ```json { "name": "validate_query", "arguments": { "sql": "SELECT DATE(created_at) as order_date, COUNT(*) as order_count, SUM(total_amount) as daily_revenue FROM orders WHERE created_at >= '2024-01-01' AND status = 'delivered' GROUP BY DATE(created_at) ORDER BY order_date DESC LIMIT 30", "schema": "public" } } ``` **Validation Result:** ``` Query Analysis Report ================================================== Valid: ✅ Yes Complexity: 4/10 💡 Optimization Suggestions: 1. Run EXPLAIN ANALYZE to see the actual execution plan 2. Consider adding an index on orders.created_at if queries are slow 3. For ORDER BY with LIMIT, ensure there's an index on the ORDER BY columns ``` **Why this is good:** - ✅ Uses specific columns instead of SELECT * - ✅ Includes proper date range filtering - ✅ Has a LIMIT clause to prevent large result sets - ✅ Uses indexed columns in WHERE clause --- ### Example 2: Efficient Customer Lookup ```json { "name": "validate_query", "arguments": { "sql": "SELECT id, email, first_name, last_name, created_at FROM users WHERE email = $1 AND is_active = true", "schema": "public" } } ``` **Validation Result:** ``` Query Analysis Report ================================================== Valid: ✅ Yes Complexity: 1/10 💡 Optimization Suggestions: 1. Run EXPLAIN ANALYZE to see the actual execution plan ``` **Why this is good:** - ✅ Uses parameterized queries ($1) for security - ✅ Selects only needed columns - ✅ Uses indexed columns (email) - ✅ Includes status filtering --- ## ⚠️ Queries with Performance Warnings ### Example 3: Inefficient SELECT * Query ```json { "name": "validate_query", "arguments": { "sql": "SELECT * FROM orders WHERE user_id = 123", "schema": "public" } } ``` **Validation Result:** ``` Query Analysis Report ================================================== Valid: ✅ Yes Complexity: 2/10 ⚡ Performance Warnings: WARNING: SELECT * can be inefficient 💡 Specify only needed columns instead of using SELECT * INFO: Consider adding LIMIT clause 💡 Add LIMIT clause to prevent large result sets 💡 Optimization Suggestions: 1. Run EXPLAIN ANALYZE to see the actual execution plan 2. Consider adding an index on orders.user_id if queries are slow ``` **How to improve:** ```sql -- Better version: SELECT id, order_number, total_amount, status, created_at FROM orders WHERE user_id = $1 ORDER BY created_at DESC LIMIT 50 ``` --- ### Example 4: Leading Wildcard in LIKE ```json { "name": "validate_query", "arguments": { "sql": "SELECT name, email FROM users WHERE email LIKE '%@gmail.com' ORDER BY created_at DESC", "schema": "public" } } ``` **Validation Result:** ``` Query Analysis Report ================================================== Valid: ✅ Yes Complexity: 3/10 ⚡ Performance Warnings: WARNING: LIKE with leading wildcard prevents index usage 💡 Avoid leading wildcards in LIKE patterns or consider full-text search INFO: Consider adding LIMIT clause 💡 Add LIMIT clause to prevent large result sets 💡 Optimization Suggestions: 1. Run EXPLAIN ANALYZE to see the actual execution plan 2. For ORDER BY with LIMIT, ensure there's an index on the ORDER BY columns ``` **How to improve:** ```sql -- Better approach using domain extraction: SELECT name, email FROM users WHERE email LIKE '%gmail.com' -- Still not ideal, but better OR email ~ '@gmail\.com$' -- Use regex for end-of-string match ORDER BY created_at DESC LIMIT 100 ``` --- ### Example 5: Cartesian Product Risk ```json { "name": "validate_query", "arguments": { "sql": "SELECT u.name, p.name, o.total_amount FROM users u, products p, orders o WHERE u.is_active = true", "schema": "public" } } ``` **Validation Result:** ``` Query Analysis Report ================================================== Valid: ✅ Yes Complexity: 5/10 ⚡ Performance Warnings: WARNING: Potential cartesian product detected 💡 Ensure JOIN clauses have proper ON conditions WARNING: SELECT * can be inefficient 💡 Specify only needed columns instead of using SELECT * 💡 Optimization Suggestions: 1. Run EXPLAIN ANALYZE to see the actual execution plan 2. For complex JOINs, consider the join order - start with the most selective table ``` **How to fix:** ```sql -- Correct version with proper JOINs: SELECT u.first_name, u.last_name, p.name as product_name, o.total_amount FROM users u JOIN orders o ON u.id = o.user_id JOIN order_items oi ON o.id = oi.order_id JOIN products p ON oi.product_id = p.id WHERE u.is_active = true LIMIT 100 ``` --- ## 🔒 Security Issues - Blocked Queries ### Example 6: SQL Injection Attempt ```json { "name": "validate_query", "arguments": { "sql": "SELECT * FROM users WHERE id = 1; DROP TABLE users; --", "schema": "public" } } ``` **Validation Result:** ``` Query Analysis Report ================================================== Valid: ❌ No Complexity: 10/10 🔒 Security Issues: CRITICAL: Only SELECT statements are allowed 💡 Use SELECT queries for data retrieval only WARNING: Potential SQL injection with dangerous commands 💡 Use parameterized queries instead WARNING: SQL comments can be used to bypass security 💡 Use parameterized queries instead ⚡ Performance Warnings: WARNING: SELECT * can be inefficient 💡 Specify only needed columns instead of using SELECT * ``` **Why this is blocked:** - ❌ Contains dangerous DROP statement - ❌ Uses SQL injection techniques - ❌ Has malicious SQL comments --- ### Example 7: Dangerous System Functions ```json { "name": "validate_query", "arguments": { "sql": "SELECT pg_read_file('/etc/passwd') as file_content", "schema": "public" } } ``` **Validation Result:** ``` Query Analysis Report ================================================== Valid: ❌ No Complexity: 8/10 🔒 Security Issues: CRITICAL: Dangerous function 'pg_read_file' detected 💡 Remove dangerous system functions 💡 Optimization Suggestions: 1. Run EXPLAIN ANALYZE to see the actual execution plan ``` **Why this is blocked:** - ❌ Uses dangerous PostgreSQL system function - ❌ Could expose sensitive server information - ❌ Violates security boundaries --- ### Example 8: Non-SELECT Statement ```json { "name": "validate_query", "arguments": { "sql": "UPDATE users SET email = 'hacker@evil.com' WHERE id = 1", "schema": "public" } } ``` **Validation Result:** ``` Query Analysis Report ================================================== Valid: ❌ No Complexity: 10/10 🔒 Security Issues: CRITICAL: Only SELECT statements are allowed 💡 Use SELECT queries for data retrieval only 💡 Optimization Suggestions: 1. Run EXPLAIN ANALYZE to see the actual execution plan ``` **Why this is blocked:** - ❌ UPDATE statement not allowed (read-only policy) - ❌ Could modify data unexpectedly - ❌ Violates MCP server security model --- ## 🔧 Complex Queries with Optimization Suggestions ### Example 9: High-Complexity Analytics Query ```json { "name": "validate_query", "arguments": { "sql": "WITH monthly_sales AS (SELECT DATE_TRUNC('month', o.created_at) as month, SUM(o.total_amount) as revenue, COUNT(*) as order_count FROM orders o WHERE o.status = 'delivered' GROUP BY DATE_TRUNC('month', o.created_at)), category_performance AS (SELECT c.name as category, SUM(oi.total_price) as category_revenue FROM order_items oi JOIN products p ON oi.product_id = p.id JOIN categories c ON p.category_id = c.id JOIN orders o ON oi.order_id = o.id WHERE o.status = 'delivered' GROUP BY c.id, c.name) SELECT ms.month, ms.revenue, ms.order_count, cp.category, cp.category_revenue FROM monthly_sales ms CROSS JOIN category_performance cp ORDER BY ms.month DESC, cp.category_revenue DESC", "schema": "public" } } ``` **Validation Result:** ``` Query Analysis Report ================================================== Valid: ✅ Yes Complexity: 9/10 ⚡ Performance Warnings: INFO: Consider adding LIMIT clause 💡 Add LIMIT clause to prevent large result sets 💡 Optimization Suggestions: 1. Run EXPLAIN ANALYZE to see the actual execution plan 2. Consider adding an index on orders.user_id if queries are slow 3. For complex JOINs, consider the join order - start with the most selective table 4. For ORDER BY with LIMIT, ensure there's an index on the ORDER BY columns ``` **Optimization notes:** - ⚠️ Very complex query (9/10 complexity) - 💡 Consider breaking into smaller queries - 💡 Add appropriate indexes for date-based filtering - 💡 Consider materialized views for frequently-run analytics --- ## 📊 Summary of Validation Categories | Category | Count | Description | |----------|-------|-------------| | ✅ **Good Queries** | 2 | Well-optimized, secure, efficient | | ⚠️ **Performance Warnings** | 4 | Valid but with optimization opportunities | | 🔒 **Security Blocked** | 3 | Dangerous queries that are prevented | | 🔧 **High Complexity** | 1 | Valid but computationally expensive | ## 💡 Key Takeaways 1. **Always use parameterized queries** to prevent SQL injection 2. **Specify exact columns** instead of SELECT * 3. **Include LIMIT clauses** to prevent large result sets 4. **Use proper JOIN syntax** to avoid cartesian products 5. **Leverage indexes** by filtering on indexed columns 6. **Avoid leading wildcards** in LIKE patterns 7. **Only SELECT statements** are allowed for security The MCP server helps you write better, safer, and more performant SQL queries! 🚀