Apple Tools MCP

/** * Security tests for SQL injection prevention * Tests escapeSQL, validateLanceDBId, and query parameterization */ import { describe, it, expect } from 'vitest' describe('SQL Injection Prevention', () => { describe('escapeSQL function', () => { // Simulated escapeSQL implementation const escapeSQL = (str) => { if (typeof str !== 'string') return '' return str.replace(/'/g, "''") } it('should escape single quotes', () => { const input = "O'Reilly" const escaped = escapeSQL(input) expect(escaped).toBe("O''Reilly") }) it('should handle multiple single quotes', () => { const input = "It's John's meeting" const escaped = escapeSQL(input) expect(escaped).toBe("It''s John''s meeting") }) it('should handle no quotes', () => { const input = 'Regular text' const escaped = escapeSQL(input) expect(escaped).toBe('Regular text') }) it('should handle empty string', () => { const escaped = escapeSQL('') expect(escaped).toBe('') }) it('should handle non-string input', () => { expect(escapeSQL(null)).toBe('') expect(escapeSQL(undefined)).toBe('') expect(escapeSQL(123)).toBe('') }) it('should prevent basic SQL injection', () => { const malicious = "'; DROP TABLE users; --" const escaped = escapeSQL(malicious) // The single quote is doubled, preventing injection expect(escaped).toBe("''; DROP TABLE users; --") // The key is that the first quote is escaped (doubled) // so when used in a query like WHERE x = '...', it becomes: // WHERE x = '''; DROP TABLE users; --' // which is a valid string literal, not injection expect(escaped.startsWith("''")).toBe(true) }) it('should handle Unicode single quote variants', () => { // Note: These are different characters, may or may not need escaping const variants = ["'", "\u2018", "\u2019"] // ASCII, left single, right single const escaped = escapeSQL(variants[0]) expect(escaped).toBe("''") }) }) describe('validateLanceDBId function', () => { // Simulated validateLanceDBId - only allows safe characters const validateLanceDBId = (id) => { if (typeof id !== 'string') return false // Only alphanumeric, hyphen, underscore, and dot return /^[a-zA-Z0-9_.-]+$/.test(id) } it('should accept alphanumeric IDs', () => { expect(validateLanceDBId('event123')).toBe(true) expect(validateLanceDBId('Meeting456')).toBe(true) }) it('should accept IDs with hyphens', () => { expect(validateLanceDBId('team-meeting-123')).toBe(true) }) it('should accept IDs with underscores', () => { expect(validateLanceDBId('team_meeting_123')).toBe(true) }) it('should accept IDs with dots', () => { expect(validateLanceDBId('v1.2.3')).toBe(true) }) it('should reject special characters', () => { expect(validateLanceDBId("id'injection")).toBe(false) expect(validateLanceDBId('id;drop')).toBe(false) expect(validateLanceDBId('id--comment')).toBe(true) // Double hyphen is OK expect(validateLanceDBId('id/*comment*/')).toBe(false) }) it('should reject SQL keywords with quotes', () => { expect(validateLanceDBId("'; DROP TABLE --")).toBe(false) expect(validateLanceDBId('1 OR 1=1')).toBe(false) }) it('should reject empty string', () => { expect(validateLanceDBId('')).toBe(false) }) it('should reject null/undefined', () => { expect(validateLanceDBId(null)).toBe(false) expect(validateLanceDBId(undefined)).toBe(false) }) it('should reject paths', () => { expect(validateLanceDBId('../etc/passwd')).toBe(false) expect(validateLanceDBId('/bin/sh')).toBe(false) }) }) describe('calendar delete ID validation', () => { // Calendar IDs should be in format: "title-timestamp" const validateCalendarId = (id) => { if (typeof id !== 'string') return false // More permissive for titles, but no SQL-dangerous chars return !/[';\\"]/.test(id) && id.length > 0 } it('should accept valid calendar IDs', () => { expect(validateCalendarId('Team Meeting-725760000')).toBe(true) expect(validateCalendarId('Standup-123456789')).toBe(true) }) it('should accept IDs with spaces', () => { expect(validateCalendarId('Team Meeting Review-123')).toBe(true) }) it('should reject single quotes', () => { expect(validateCalendarId("O'Reilly Meeting-123")).toBe(false) }) it('should reject semicolons', () => { expect(validateCalendarId('Meeting; DROP TABLE-123')).toBe(false) }) it('should reject backslashes', () => { expect(validateCalendarId('Meeting\\injection-123')).toBe(false) }) it('should reject double quotes', () => { expect(validateCalendarId('Meeting"injection-123')).toBe(false) }) }) describe('query parameter safety', () => { it('should not use string interpolation for queries', () => { // Bad pattern (DON'T DO THIS): const badQuery = (id) => `DELETE FROM events WHERE id = '${id}'` // Good pattern (parameterized): const goodQuery = () => 'DELETE FROM events WHERE id = ?' const maliciousId = "'; DROP TABLE events; --" // Bad pattern is vulnerable const unsafeQuery = badQuery(maliciousId) expect(unsafeQuery).toContain('DROP TABLE') // Good pattern is safe (placeholder not replaced) const safeQuery = goodQuery() expect(safeQuery).not.toContain(maliciousId) expect(safeQuery).toContain('?') }) it('should use parameter binding syntax', () => { // SQLite parameter styles const positionalParams = 'SELECT * FROM events WHERE id = ?' const namedParams = 'SELECT * FROM events WHERE id = :id' const numberedParams = 'SELECT * FROM events WHERE id = ?1' expect(positionalParams).toContain('?') expect(namedParams).toContain(':id') expect(numberedParams).toMatch(/\?1/) }) }) describe('LIKE wildcard escaping', () => { const escapeLikePattern = (str) => { if (typeof str !== 'string') return '' // Escape % and _ which are LIKE wildcards return str .replace(/%/g, '\\%') .replace(/_/g, '\\_') .replace(/'/g, "''") } it('should escape percent signs', () => { const input = '50% off' const escaped = escapeLikePattern(input) expect(escaped).toBe('50\\% off') }) it('should escape underscores', () => { const input = 'file_name.txt' const escaped = escapeLikePattern(input) expect(escaped).toBe('file\\_name.txt') }) it('should escape both wildcards', () => { const input = '100% of users_active' const escaped = escapeLikePattern(input) expect(escaped).toBe('100\\% of users\\_active') }) it('should also escape single quotes', () => { const input = "O'Reilly 50%" const escaped = escapeLikePattern(input) expect(escaped).toBe("O''Reilly 50\\%") }) it('should handle empty string', () => { expect(escapeLikePattern('')).toBe('') }) it('should prevent wildcard injection', () => { // Attacker tries to match all records const malicious = '%' const escaped = escapeLikePattern(malicious) expect(escaped).toBe('\\%') expect(escaped).not.toBe('%') }) }) describe('integer validation', () => { const validateInteger = (value) => { if (typeof value === 'number' && Number.isInteger(value)) return true if (typeof value === 'string' && /^-?\d+$/.test(value)) return true return false } it('should accept valid integers', () => { expect(validateInteger(123)).toBe(true) expect(validateInteger(-456)).toBe(true) expect(validateInteger(0)).toBe(true) }) it('should accept string integers', () => { expect(validateInteger('123')).toBe(true) expect(validateInteger('-456')).toBe(true) }) it('should reject non-integers', () => { expect(validateInteger(12.34)).toBe(false) expect(validateInteger('12.34')).toBe(false) }) it('should reject SQL injection attempts', () => { expect(validateInteger('1; DROP TABLE')).toBe(false) expect(validateInteger('1 OR 1=1')).toBe(false) expect(validateInteger("1' OR '1'='1")).toBe(false) }) it('should reject empty/null', () => { expect(validateInteger('')).toBe(false) expect(validateInteger(null)).toBe(false) expect(validateInteger(undefined)).toBe(false) }) }) describe('SQLite JSON handling', () => { it('should safely parse JSON results', () => { const safeParseJSON = (str) => { try { return JSON.parse(str) } catch { return null } } const validJSON = '[{"id": 1, "name": "test"}]' const invalidJSON = "'; DROP TABLE; --" expect(safeParseJSON(validJSON)).toEqual([{ id: 1, name: 'test' }]) expect(safeParseJSON(invalidJSON)).toBeNull() }) it('should handle malformed JSON gracefully', () => { const safeParseJSON = (str) => { try { return JSON.parse(str) } catch { return [] } } expect(safeParseJSON('{malformed')).toEqual([]) expect(safeParseJSON('')).toEqual([]) }) }) describe('table/column name validation', () => { const validateIdentifier = (name) => { if (typeof name !== 'string') return false // SQLite identifier rules: alphanumeric + underscore, not starting with digit return /^[a-zA-Z_][a-zA-Z0-9_]*$/.test(name) } it('should accept valid table names', () => { expect(validateIdentifier('emails')).toBe(true) expect(validateIdentifier('calendar_events')).toBe(true) expect(validateIdentifier('Messages2024')).toBe(true) }) it('should reject names starting with digit', () => { expect(validateIdentifier('123table')).toBe(false) }) it('should reject special characters', () => { expect(validateIdentifier('table-name')).toBe(false) expect(validateIdentifier('table.name')).toBe(false) expect(validateIdentifier('table name')).toBe(false) }) it('should reject SQL injection in identifiers', () => { expect(validateIdentifier('table; DROP')).toBe(false) expect(validateIdentifier("table' OR")).toBe(false) }) }) })