Last updated
Validation Checks Summary
- Syntax — correct SQL grammar and clause structure
- Clause order — SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT
- GROUP BY completeness — all non-aggregated SELECT columns in GROUP BY
- JOIN conditions — no Cartesian products from missing ON clauses
- Subquery structure — scalar subqueries return single values
- Window function placement — not in WHERE or GROUP BY
- Dialect compatibility — syntax valid for target database
- Security — no string concatenation injection patterns
- Best practices — no SELECT *, no unguarded UPDATE/DELETE
Examples
Example 1: Syntax Error Detection
-- INVALID: missing FROM keyword
SELECT id, name
WHERE active = 1;
Validation Error (line 2):
Expected FROM clause after column list.
Found: WHERE
Fix: SELECT id, name FROM table_name WHERE active = 1;
-- INVALID: unmatched parenthesis
SELECT * FROM users WHERE (id = 1 AND active = true;
Validation Error (line 1):
Unmatched opening parenthesis at position 32.
Fix: Add closing parenthesis before semicolon.
-- INVALID: wrong clause order
SELECT * FROM users ORDER BY name WHERE active = 1;
Validation Error (line 1):
WHERE clause must appear before ORDER BY.
Correct order: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT
Example 2: Dialect-Specific Validation
-- Valid in MySQL, invalid in PostgreSQL
SELECT * FROM users LIMIT 10, 20;
PostgreSQL Validation Warning:
MySQL-style LIMIT offset,count syntax is not supported in PostgreSQL.
Use: LIMIT 20 OFFSET 10
-- Valid in PostgreSQL, invalid in MySQL
SELECT * FROM users FETCH FIRST 10 ROWS ONLY;
MySQL Validation Warning:
FETCH FIRST syntax is not supported in MySQL 5.x.
Use: LIMIT 10
Example 3: Best Practice Warnings
-- SELECT * warning
SELECT * FROM users WHERE id = 1;
Warning: SELECT * retrieves all columns including unused ones.
Recommendation: Specify only the columns you need:
SELECT id, email, first_name FROM users WHERE id = 1;
-- Missing WHERE on UPDATE
UPDATE users SET is_active = false;
Warning: UPDATE without WHERE clause will modify ALL rows in the table.
This is likely unintentional. Add a WHERE clause to target specific rows.
-- Missing WHERE on DELETE
DELETE FROM orders;
Warning: DELETE without WHERE clause will delete ALL rows in the table.
Add a WHERE clause or use TRUNCATE TABLE if intentional.