// Import the functions to test
// Since they're not exported, we'll need to test them through a test module
import { isReadOnlyQuery, addAutoLimit } from '../src/utils/sql-validator';
describe('SQL Validation Tests', () => {
describe('isReadOnlyQuery', () => {
test('should accept simple SELECT queries', () => {
expect(isReadOnlyQuery('SELECT * FROM users')).toBe(true);
expect(isReadOnlyQuery('SELECT id, name FROM products')).toBe(true);
expect(isReadOnlyQuery('select * from table1')).toBe(true); // lowercase
});
test('should accept SELECT with WHERE clause', () => {
expect(isReadOnlyQuery('SELECT * FROM users WHERE id = 1')).toBe(true);
expect(isReadOnlyQuery('SELECT * FROM products WHERE price > 100')).toBe(true);
});
test('should accept SELECT with JOIN', () => {
expect(isReadOnlyQuery('SELECT u.*, p.* FROM users u JOIN products p ON u.id = p.user_id')).toBe(true);
});
test('should accept SELECT with ORDER BY and GROUP BY', () => {
expect(isReadOnlyQuery('SELECT * FROM users ORDER BY name')).toBe(true);
expect(isReadOnlyQuery('SELECT category, COUNT(*) FROM products GROUP BY category')).toBe(true);
});
test('should reject non-SELECT queries', () => {
expect(isReadOnlyQuery('DROP TABLE users')).toBe(false);
expect(isReadOnlyQuery('DELETE FROM users')).toBe(false);
expect(isReadOnlyQuery('UPDATE users SET name = "test"')).toBe(false);
expect(isReadOnlyQuery('INSERT INTO users (name) VALUES ("test")')).toBe(false);
expect(isReadOnlyQuery('CREATE TABLE test (id INT)')).toBe(false);
});
test('should reject SELECT with dangerous keywords', () => {
expect(isReadOnlyQuery('SELECT * FROM users; DROP TABLE users')).toBe(false);
expect(isReadOnlyQuery('SELECT * FROM users WHERE 1=1; DELETE FROM users')).toBe(false);
expect(isReadOnlyQuery('SELECT * FROM users UNION SELECT * FROM passwords')).toBe(false);
});
test('should reject queries with SQL injection patterns', () => {
expect(isReadOnlyQuery('SELECT * FROM users WHERE id = 1; --')).toBe(false);
expect(isReadOnlyQuery('SELECT * FROM users WHERE id = 1 /* comment */')).toBe(false);
});
test('should reject queries with dangerous functions', () => {
expect(isReadOnlyQuery('SELECT LOAD_FILE("/etc/passwd")')).toBe(false);
expect(isReadOnlyQuery('SELECT * INTO OUTFILE "/tmp/file" FROM users')).toBe(false);
expect(isReadOnlyQuery('SELECT SLEEP(10)')).toBe(false);
});
test('handle queries with trailing semicolon correctly', () => {
expect(isReadOnlyQuery('SELECT * FROM users;')).toBe(true);
expect(isReadOnlyQuery('SELECT * FROM users WHERE id = 1;')).toBe(true);
});
test('reject multiple statements', () => {
expect(isReadOnlyQuery('SELECT * FROM users; SELECT * FROM products;')).toBe(false);
expect(isReadOnlyQuery('SELECT * FROM users; DROP TABLE products;')).toBe(false);
});
});
describe('addAutoLimit', () => {
test('should add LIMIT to simple SELECT queries', () => {
expect(addAutoLimit('SELECT * FROM users')).toBe('SELECT * FROM users LIMIT 1000');
expect(addAutoLimit('SELECT * FROM products WHERE price > 100')).toBe('SELECT * FROM products WHERE price > 100 LIMIT 1000');
});
test('should not add LIMIT if already present', () => {
expect(addAutoLimit('SELECT * FROM users LIMIT 100')).toBe('SELECT * FROM users LIMIT 100');
expect(addAutoLimit('SELECT * FROM users LIMIT 50')).toBe('SELECT * FROM users LIMIT 50');
});
test('should not add LIMIT to COUNT queries', () => {
expect(addAutoLimit('SELECT COUNT(*) FROM users')).toBe('SELECT COUNT(*) FROM users');
expect(addAutoLimit('SELECT COUNT(id) FROM products')).toBe('SELECT COUNT(id) FROM products');
});
test('should not add LIMIT to EXISTS subqueries', () => {
expect(addAutoLimit('SELECT * FROM users WHERE EXISTS (SELECT 1 FROM orders)')).toBe('SELECT * FROM users WHERE EXISTS (SELECT 1 FROM orders)');
expect(addAutoLimit('SELECT * FROM users WHERE NOT EXISTS (SELECT 1 FROM orders)')).toBe('SELECT * FROM users WHERE NOT EXISTS (SELECT 1 FROM orders)');
});
test('should handle custom limit values', () => {
expect(addAutoLimit('SELECT * FROM users', 500)).toBe('SELECT * FROM users LIMIT 500');
expect(addAutoLimit('SELECT * FROM users', 100)).toBe('SELECT * FROM users LIMIT 100');
});
test('should handle queries with trailing semicolons', () => {
expect(addAutoLimit('SELECT * FROM users;')).toBe('SELECT * FROM users LIMIT 1000;');
});
test('should handle case-insensitive LIMIT detection', () => {
expect(addAutoLimit('SELECT * FROM users limit 100')).toBe('SELECT * FROM users limit 100');
expect(addAutoLimit('SELECT * FROM users LIMIT 100')).toBe('SELECT * FROM users LIMIT 100');
});
});
});