SPB Git

Repository Public

EngineTests.swift 25.9 KB 491 lines swift

1// =============================================================================2//                  SP BOUCHER | SOFTWARE & RESEARCH3//             ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING4// =============================================================================5//6// PROJECT METADATA7// -----------------------------------------------------------------------------8// Project          : ORIX Sheet9// File             : Packages/SheetData/Tests/SheetDataTests/EngineTests.swift10// Module           : SheetDataTests11// Repository       : https://git.spboucher.ai/orix-sheet12// Created          : 2026-11-2113// Last Modified    : 2026-11-2114//15// =============================================================================16// AUTHOR & PROFESSIONAL AFFILIATION17// =============================================================================18//19// Author           : Simon-Pierre Boucher20// Position         : Professor of Finance21// Institution      : Université du Québec en Outaouais (UQO)22// Department       : Département des sciences administratives23// Organization     : SP Boucher24//25// Website          : https://spboucher.ai26// Git              : https://git.spboucher.ai27//28// =============================================================================29// OFFICIAL CONTACT DIRECTORY30// =============================================================================31//32// General          : info@spboucher.ai33// Professional     : contact@spboucher.ai34// Direct           : simon@spboucher.ai35// Research         : research@spboucher.ai36// Development      : dev@spboucher.ai37// Support          : support@spboucher.ai38// Security/Admin   : admin@spboucher.ai39// Notifications    : notifications@spboucher.ai40//41// Preferred Contact: support@spboucher.ai42//43// =============================================================================44// COPYRIGHT & INTELLECTUAL PROPERTY45// =============================================================================46//47// Copyright (c) 202648// Simon-Pierre Boucher49//50// All rights reserved where applicable.51//52// This file is governed by the applicable project license,53// copyright notices, institutional agreements and relevant law.54//55// Unless expressly authorized by the applicable license or56// rights holder, copying, redistribution, sublicensing,57// publication and commercial exploitation are not permitted.58//59// Existing third-party rights and notices remain unaffected.60//61// =============================================================================62// CONFIDENTIALITY & SECURITY63// =============================================================================64//65// Classification   : Public66// Distribution     : Public repository; use is governed by the project license67// Security Contact : admin@spboucher.ai68//69// Access to this source does not independently grant permission70// to redistribute or disclose confidential materials.71//72// Do not embed credentials, private keys, access tokens,73// certificates or confidential personal information.74//75// =============================================================================76// LICENSE & MAINTENANCE77// =============================================================================78//79// License          : None specified (no license file in this repository)80// Maintainer       : Simon-Pierre Boucher81// Technical Contact: dev@spboucher.ai82//83// =============================================================================84// END OF SP BOUCHER SOURCE HEADER85// =============================================================================8687import Foundation88import Testing89import SheetCore90@testable import SheetData9192@Suite struct EngineTests {93    @Test func linksVendoredDuckDB() throws {94        #expect(DataEngine.duckDBVersion == "v2.0.1")95    }9697    @Test func typedColumns() throws {98        let e = try DataEngine()99        let t = try e.query("""100            SELECT true AS b, 42::TINYINT AS i8, 9007199254740993::BIGINT AS i64, 1.5::FLOAT AS f, 2.25::DOUBLE AS d,101                   123.4500::DECIMAL(18,4) AS dec, -0.05::DECIMAL(4,2) AS negdec, 'héllo 日本' AS s, DATE '2024-02-29' AS dt,102                   TIMESTAMP '2024-01-31 13:45:10.5' AS ts, TIME '08:30:00' AS tm, 'ab'::BLOB AS bl, NULL AS n,103                   18446744073709551615::UBIGINT AS u64, sum(7) AS huge, [1, 2] AS list, {'a': 1} AS st,104                   12345678901234567890123.456::DECIMAL(30,3) AS bigdec, 'x'::ENUM('x', 'y') AS en,105                   TIMESTAMP_MS '2024-01-01 00:00:01' AS tms, TIMESTAMP_S '2024-01-01 00:00:01' AS tss106            """)107        func v(_ n: String) -> DataValue { t.value(row: 0, column: n)! }108        #expect(v("b") == .bool(true))109        #expect(v("i8") == .int(42))110        #expect(v("i64") == .int(9007199254740993))111        #expect(v("f") == .double(1.5))112        #expect(v("d") == .double(2.25))113        #expect(v("dec") == .decimal("123.4500"))114        #expect(t.column("dec")!.double(0) == 123.45)115        #expect(v("negdec") == .decimal("-0.05"))116        #expect(v("s") == .string("héllo 日本"))117        #expect(v("dt") == .date(year: 2024, month: 2, day: 29))118        #expect(v("dt").text == "2024-02-29")119        #expect(v("ts").text == "2024-01-31 13:45:10.500000")120        #expect(v("tm").text == "08:30:00")121        #expect(v("bl") == .blob(Data("ab".utf8)))122        #expect(v("n") == .null)123        #expect(v("u64") == .decimal("18446744073709551615"))124        #expect(v("huge") == .int(7))125        #expect(v("list") == .string("[1, 2]"))126        #expect(v("st") == .string("{'a': 1}"))127        #expect(v("bigdec") == .decimal("12345678901234567890123.456"))128        #expect(v("en") == .string("x"))129        #expect(v("tms").text == "2024-01-01 00:00:01")130        #expect(v("tss").text == "2024-01-01 00:00:01")131        #expect(t.column("dec")!.type == .decimal)132        #expect(t.column("dec")!.declaredType == "DECIMAL(18,4)")133        #expect(t.column("i8")!.declaredType == "TINYINT")134    }135136    @Test func nullMasksAndColumnAccess() throws {137        let e = try DataEngine()138        let t = try e.query("SELECT i, CASE WHEN i % 3 = 0 THEN NULL ELSE i * 1.5 END AS x, CASE WHEN i % 2 = 0 THEN 's' || i END AS s FROM range(10) t(i) ORDER BY i")139        #expect(t.rowCount == 10)140        #expect(t.column("i")!.valid == nil)141        #expect(t.column("x")!.nullCount == 4)142        #expect(t.column("s")!.nullCount == 5)143        #expect(t.column("x")!.double(3) == nil)144        #expect(t.column("x")!.double(4) == 6.0)145        #expect(t.value(row: 2, column: "s") == .string("s2"))146        #expect(t.value(row: 1, column: "s") == .null)147    }148149    @Test func parametersBindEveryType() throws {150        let e = try DataEngine()151        let t = try e.query("SELECT ?::BOOLEAN, ?::BIGINT + 1, ?::DOUBLE * 2, ? || '!', ?::DATE + 1, ?::TIMESTAMP, ?::TIME, octet_length(?::BLOB), ? IS NULL, ?::DECIMAL(10,2)",152                            parameters: [.bool(true), .int(41), .double(1.25), .string("O'Brien; DROP TABLE x;--"), .date(year: 2024, month: 12, day: 31),153                                         .timestamp(1_704_067_200_000_000), .time(3_600_000_000), .blob(Data([0, 1, 2])), .null, .decimal("12.34")])154        #expect(t.row(0) == [.bool(true), .int(42), .double(2.5), .string("O'Brien; DROP TABLE x;--!"), .date(year: 2025, month: 1, day: 1),155                             .timestamp(1_704_067_200_000_000), .time(3_600_000_000), .int(3), .bool(true), .decimal("12.34")])156        #expect(throws: DataError.self) { _ = try e.query("SELECT ?", parameters: []) }157    }158159    @Test func limitStopsFetchingAndFlagsTruncation() throws {160        let e = try DataEngine()161        let t = try e.query("SELECT * FROM range(1000000)", limit: 2500)162        #expect(t.rowCount == 2500)163        #expect(t.truncated)164        let full = try e.query("SELECT * FROM range(10)", limit: 10)165        #expect(full.rowCount == 10 && !full.truncated)166    }167168    @Test func streamDeliversChunksAndCanStop() throws {169        let e = try DataEngine()170        var total = 0, sum: Int64 = 0, chunks = 0171        try e.stream("SELECT i FROM range(300000) t(i)", chunkRows: 50_000) { chunk in172            chunks += 1173            total += chunk.rowCount174            if case .int64(let a) = chunk.columns[0].storage { sum += a.reduce(0, +) }175            return true176        }177        #expect(total == 300_000)178        #expect(sum == Int64(299_999) * 300_000 / 2)179        #expect(chunks >= 5)180        var seen = 0181        try e.stream("SELECT i FROM range(300000) t(i)", chunkRows: 10_000) { seen += $0.rowCount; return false }182        #expect(seen < 300_000 && seen > 0)183        #expect(try e.query("SELECT 1").rowCount == 1)   // the connection is usable after an early stop184    }185186    @Test func multiStatementScripts() throws {187        let e = try DataEngine()188        try e.execute("CREATE TABLE t (a INTEGER); INSERT INTO t VALUES (1), (2), (3);")189        let t = try e.query("INSERT INTO t VALUES (4); SELECT sum(a) FROM t")190        #expect(t.value(row: 0, column: 0) == .int(10))191        #expect(throws: DataError.self) { try e.execute("SELECT 1; SELEC 2") }192    }193194    @Test func errorsCarryDuckDBMessage() throws {195        let e = try DataEngine()196        do { _ = try e.query("SELECT * FROM missing_table"); Issue.record("expected an error") } catch let DataError.sql(message, _) {197            #expect(message.contains("missing_table"))198        }199    }200201    @Test func interruptCancelsALongQuery() async throws {202        let e = try DataEngine()203        let started = Date()204        let canceller = Task.detached {205            try await Task.sleep(nanoseconds: 300_000_000)206            e.interrupt()207        }208        var interrupted = false209        do { _ = try e.query("SELECT count(*) FROM range(200000000000) t(i) WHERE i % 7 = 3") } catch DataError.interrupted { interrupted = true }210        _ = await canceller.result211        #expect(interrupted)212        #expect(Date().timeIntervalSince(started) < 20)213        // the engine keeps working, and an interrupt while idle does not poison the next query214        e.interrupt()215        #expect(try e.query("SELECT 41 + 1").value(row: 0, column: 0) == .int(42))216    }217218    @Test func progressIsReportedDuringALongStatement() throws {219        let e = try DataEngine()220        let box = ProgressBox()221        try e.withProgress({ box.add($0) }) {222            try e.execute("CREATE TABLE big AS SELECT i, hash(i) AS h FROM range(60000000) t(i)")223        }224        #expect(box.values.last == 1)225        #expect(box.values.allSatisfy { $0 >= 0 && $0 <= 1 })226    }227228    @Test func concurrentCallersAreSerialized() async throws {229        let e = try DataEngine()230        try e.execute("CREATE TABLE c (v BIGINT)")231        await withTaskGroup(of: Void.self) { group in232            for i in 0..<16 {233                group.addTask {234                    try? e.execute("INSERT INTO c VALUES (?)", parameters: [.int(Int64(i))])235                    _ = try? e.query("SELECT count(*) FROM c")236                    _ = try? e.datasets()237                }238            }239        }240        #expect(try e.query("SELECT count(*), sum(v) FROM c").row(0) == [.int(16), .int(120)])241    }242243    @Test func describeReturnsSchemaWithoutRunning() throws {244        let e = try DataEngine()245        let s = try e.describe("SELECT 1::BIGINT AS a, 'x' AS b, DATE '2024-01-01' AS c, 1.5::DECIMAL(9,2) AS d FROM range(100000000000)")246        #expect(s.map(\.name) == ["a", "b", "c", "d"])247        #expect(s.map(\.type) == [.integer, .string, .date, .decimal])248    }249250    @Test func diskBackedOperationsUnderASmallMemoryLimit() throws {251        // 40M rows sorted with a 256 MiB limit: the sort must spill to the temp directory instead of failing.252        let e = try DataEngine(options: DataEngineOptions(memoryLimitBytes: 256 << 20, threads: 4))253        try e.execute("CREATE TABLE s AS SELECT hash(i) AS h, i FROM range(40000000) t(i)")254        let t = try e.query("SELECT h FROM s ORDER BY h LIMIT 1 OFFSET 39999999")255        #expect(t.rowCount == 1)256    }257258    @Test func persistentDatabaseReopens() throws {259        let dir = Fixtures.temporaryDirectory()260        defer { try? FileManager.default.removeItem(at: dir) }261        let path = dir.appendingPathComponent("book.duckdb")262        do {263            let e = try DataEngine(path: path)264            try e.importFile(Fixtures.url("customers.csv"), as: "customers")265            try e.registerView(Fixtures.url("sales.parquet"), as: "sales_view")266        }267        let e = try DataEngine(path: path)268        let names = try e.datasets().map(\.name)269        #expect(names == ["customers", "sales_view"])270        #expect(try e.rowCount(of: "sales_view") == 2000)       // the staged file lives next to the database271        #expect(try e.dataset("customers").sourceURL?.lastPathComponent == "customers.csv")272    }273}274275/// Polls a directory from a background thread and remembers the largest total file size seen.276final class SpillObserver: @unchecked Sendable {277    private let directory: URL278    private let lock = NSLock()279    private var peak: Int64 = 0280    private var running = false281    private let done = DispatchSemaphore(value: 0)282    init(directory: URL) { self.directory = directory }283284    private func sample() {285        let fm = FileManager.default286        var total: Int64 = 0287        if let walker = fm.enumerator(at: directory, includingPropertiesForKeys: [.fileSizeKey]) {288            for case let url as URL in walker { total += Int64((try? url.resourceValues(forKeys: [.fileSizeKey]).fileSize) ?? 0) }289        }290        lock.lock(); peak = max(peak, total); lock.unlock()291    }292293    func start() {294        lock.lock(); running = true; lock.unlock()295        Thread.detachNewThread { [self] in296            while true {297                lock.lock(); let go = running; lock.unlock()298                if !go { break }299                sample()300                usleep(2_000)301            }302            done.signal()303        }304    }305306    func stop() -> Int64 {307        lock.lock(); running = false; lock.unlock()308        done.wait()309        lock.lock(); defer { lock.unlock() }310        return peak311    }312}313314final class ProgressBox: @unchecked Sendable {315    private let lock = NSLock()316    private var storage: [Double] = []317    func add(_ v: Double) { lock.lock(); storage.append(v); lock.unlock() }318    var values: [Double] { lock.lock(); defer { lock.unlock() }; return storage }319}320321@Suite struct SecurityTests {322    @Test func noFileAccessOutsideTheStagingDirectory() throws {323        let e = try DataEngine()324        let secret = Fixtures.temporaryDirectory().appendingPathComponent("secret.csv")325        try "a,b\n1,2\n".write(to: secret, atomically: true, encoding: .utf8)326        let attempts = [327            "SELECT * FROM read_csv(\(SQL.string(secret.path)))",328            "SELECT * FROM \(SQL.string(secret.path))",329            "SELECT * FROM read_text('/etc/hosts')",330            "SELECT * FROM read_blob('/etc/hosts')",331            "SELECT * FROM glob('/etc/*')",332            "COPY (SELECT 1) TO \(SQL.string(secret.deletingLastPathComponent().appendingPathComponent("out.csv").path))",333            "COPY (SELECT 1) TO '/tmp/orix-evil.parquet' (FORMAT parquet)",334            "EXPORT DATABASE '/tmp/orix-evil-export'",335            "ATTACH '/tmp/orix-evil.duckdb' AS evil",336            "CREATE TABLE x AS SELECT * FROM read_json('/etc/hosts')",337            "SELECT * FROM read_csv(\(SQL.string(e.stagingDirectory.path + "/../../secret.csv")))",338        ]339        for sql in attempts {340            #expect(throws: DataError.self, "\(sql)") { _ = try e.query(sql) }341        }342        #expect(!FileManager.default.fileExists(atPath: "/tmp/orix-evil.parquet"))343        #expect(!FileManager.default.fileExists(atPath: "/tmp/orix-evil.duckdb"))344    }345346    @Test func noNetworkNoExtensions() throws {347        let e = try DataEngine()348        for sql in ["INSTALL httpfs", "LOAD httpfs", "INSTALL sqlite_scanner", "LOAD sqlite_scanner", "FORCE INSTALL spatial",349                    "SELECT * FROM read_csv('https://example.com/data.csv')", "SELECT * FROM 's3://bucket/file.parquet'",350                    "SELECT * FROM sqlite_scan('x.db', 't')", "INSTALL h3 FROM community", "UPDATE EXTENSIONS"] {351            #expect(throws: DataError.self, "\(sql)") { _ = try e.query(sql) }352        }353        // nothing was written to the (empty, private) extension directory354        let extDir = e.spillDirectory.deletingLastPathComponent().appendingPathComponent("extensions-disabled")355        #expect((try FileManager.default.contentsOfDirectory(atPath: extDir.path)).isEmpty)356    }357358    /// The engine sets `allowed_directories = [stagingDirectory]`; DuckDB adds its own `temp_directory` (the spill359    /// directory) when external access is switched off. The effective allow-list is therefore exactly360    /// {staging, spill}: larger-than-memory operators work under `enable_external_access = false`, and the spill361    /// directory is, like the staging directory, reachable from SQL. Both are private to the engine.362    @Test func spillingWorksAndTheAllowListIsStagingPlusSpill() throws {363        let e = try DataEngine(options: DataEngineOptions(memoryLimitBytes: 64 << 20, threads: 2))364        let allowed = try e.query("SELECT value FROM duckdb_settings() WHERE name = 'allowed_directories'").rows[0][0].text365        let entries = allowed.trimmingCharacters(in: CharacterSet(charactersIn: "[]")).split(separator: ",")366            .map { $0.trimmingCharacters(in: .whitespaces) }.map { $0.hasSuffix("/") ? String($0.dropLast()) : $0 }367        #expect(Set(entries) == [e.stagingDirectory.path, e.spillDirectory.path])368        #expect(try e.query("SELECT value FROM duckdb_settings() WHERE name = 'enable_external_access'").rows[0][0].text == "false")369370        // ~190 MiB of sort keys against a 64 MiB limit: the sort has to go to disk.371        try e.execute("CREATE TABLE s AS SELECT hash(i) AS h, hash(i + 1) AS g, i FROM range(8000000) t(i)")372        let observer = SpillObserver(directory: e.spillDirectory)373        observer.start()374        let t = try e.query("SELECT h FROM s ORDER BY h, g LIMIT 1 OFFSET 7999999")375        let peak = observer.stop()376        #expect(t.rowCount == 1)377        #expect(peak > 0, "the sort did not write anything to the spill directory")378379        // The spill directory is inside the allow-list; its sibling (the disabled extension directory) is not.380        let planted = e.spillDirectory.appendingPathComponent("planted.csv")381        try "a,b\n1,2\n".write(to: planted, atomically: true, encoding: .utf8)382        #expect(try e.query("SELECT a + b FROM read_csv(\(SQL.string(planted.path)))").rows[0][0].text == "3")383        let outside = e.spillDirectory.deletingLastPathComponent().appendingPathComponent("extensions-disabled/planted.csv")384        try "a,b\n1,2\n".write(to: outside, atomically: true, encoding: .utf8)385        #expect(throws: DataError.self) { _ = try e.query("SELECT * FROM read_csv(\(SQL.string(outside.path)))") }386        try FileManager.default.removeItem(at: outside)387    }388389    @Test func configurationIsLocked() throws {390        let e = try DataEngine()391        for sql in ["SET enable_external_access = true", "SET allowed_directories = ['/']", "SET allowed_paths = ['/etc/hosts']",392                    "SET autoinstall_known_extensions = true", "SET autoload_known_extensions = true", "SET lock_configuration = false",393                    "RESET enable_external_access", "SET GLOBAL enable_external_access = true", "PRAGMA enable_external_access = true",394                    "SET extension_directory = '/tmp'", "SET temp_directory = '/tmp'"] {395            #expect(throws: DataError.self, "\(sql)") { try e.execute(sql) }396        }397        let t = try e.query("SELECT name, value FROM duckdb_settings() WHERE name IN ('enable_external_access', 'lock_configuration', 'autoinstall_known_extensions', 'autoload_known_extensions', 'allow_community_extensions', 'allow_unsigned_extensions') ORDER BY name")398        let settings = Dictionary(uniqueKeysWithValues: t.rows.map { ($0[0].text, $0[1].text) })399        #expect(settings["enable_external_access"] == "false")400        #expect(settings["lock_configuration"] == "true")401        #expect(settings["autoinstall_known_extensions"] == "false")402        #expect(settings["autoload_known_extensions"] == "false")403        #expect(settings["allow_community_extensions"] == "false")404        #expect(settings["allow_unsigned_extensions"] == "false")405        // the reader connection obeys the same (database-wide) policy406        #expect(throws: DataError.self) { _ = try e.reading { try $0.queryTable("SELECT * FROM read_text('/etc/hosts')") } }407    }408409    @Test func identifiersWithSpacesQuotesUnicodeAndSQL() throws {410        let e = try DataEngine()411        let info = try e.importFile(Fixtures.url("odd names.csv"), as: "odd \"names\"; DROP TABLE x;--")412        let names = ["Unit Price ($)", "naïve \"quoted\" col", "日本語", "select", "x; DROP TABLE sales;--"]413        #expect(info.columns.map(\.name) == names)414        #expect(info.rowCount == 3)415        let dataset = info.name416        let p = try e.preview(dataset, offset: 0, limit: 10, orderBy: [SortSpec("日本語", ascending: false)],417                              filter: FilterGroup(.all, [FilterCondition("naïve \"quoted\" col", .notEqual, "b"), FilterCondition("x; DROP TABLE sales;--", .greaterOrEqual, 1)]),418                              columns: ["select", "Unit Price ($)"])419        #expect(p.rows == [[.string("z"), .double(4.0)], [.string("x"), .double(1.5)]])420        let prof = try e.profile(dataset)421        #expect(prof.map(\.name) == names)422        #expect(prof[0].max == .double(4.0))423        // every transform step builds SQL from these names424        let pipeline = TransformPipeline(name: "odd", source: .dataset(dataset), steps: [425            .renameColumn(from: "select", to: "from \"where\""),426            .calculatedColumn(name: "total; --", expression: "\"Unit Price ($)\" * \"日本語\""),427            .groupBy(keys: ["from \"where\""], aggregations: [Aggregation(.sum, "total; --", as: "Σ")]),428            .sort([SortSpec("Σ")]),429        ])430        let out = try TransformRunner.run(pipeline, engine: e)431        #expect(out.columnNames == ["from \"where\"", "Σ"])432        #expect(out.column("Σ")!.double(2) == 120)433        // pivot over the same names434        let spec = PivotSpec(source: .dataset(dataset), rows: [PivotField("select")], columns: [PivotField("naïve \"quoted\" col")],435                             values: [PivotValue("x; DROP TABLE sales;--", .sum)])436        let r = try PivotEngine.compute(spec, workbook: nil, data: e)437        #expect(r.value(row: r.rowIndex(keys: [])!, column: r.dataColumnCount - 1) == 6)438        try e.dropDataset(dataset)439        #expect(try e.datasets().isEmpty)440    }441442    @Test func injectionThroughValuesAndNames() throws {443        let e = try Fixtures.engine()444        let evil = "x'; DROP TABLE sales; --"445        // filter constants446        let none = try e.preview("sales", offset: 0, limit: 5, filter: FilterGroup(FilterCondition("region", .equal, .string(evil))))447        #expect(none.rowCount == 0)448        _ = try e.preview("sales", offset: 0, limit: 5, filter: FilterGroup(.any, [449            FilterCondition("product", .contains, .string(evil)), FilterCondition("product", .isIn, [.string(evil), .string("a\"b")]),450            FilterCondition("product", .startsWith, .string("') OR 1=1 --")), FilterCondition("product", .matchesRegex, .string("'(x)'; DROP TABLE sales;--")),451        ]))452        // unknown / hostile column names are rejected before any SQL runs453        #expect(throws: DataError.self) { _ = try e.preview("sales", offset: 0, limit: 5, orderBy: [SortSpec("1; DROP TABLE sales")]) }454        #expect(throws: DataError.self) { _ = try e.preview("sales\"; DROP TABLE sales; --", offset: 0, limit: 5) }455        #expect(throws: DataError.self) { _ = try e.rowCount(of: "sales WHERE 1=1; DROP TABLE sales") }456        // hostile dataset names are just names457        try e.createTable(evil, from: "SELECT 1 AS a")458        #expect(try e.rowCount(of: evil) == 1)459        // replace / fill / pivot values460        let p = TransformPipeline(name: "inj", source: .dataset("sales"), steps: [461            .replaceValues(column: "region", replacements: [Replacement("North", .string(evil))], matchSubstring: false),462            .fillMissing(columns: ["note"], strategy: .constant(.string("'); DROP TABLE sales; --"))),463            .mergeColumns(columns: ["region", "category"], separator: "', (SELECT 1)); DROP TABLE sales; --", into: "m", keepOriginals: true),464            .pivot(rowKeys: ["category"], column: "region", value: "quantity", aggregation: .sum),465        ])466        let out = try TransformRunner.run(p, engine: e)467        #expect(out.columnNames.contains(evil))468        // calculated expressions cannot escape their parentheses469        for expression in ["1); DROP TABLE sales; --", "1) AS x, (SELECT 2", "quantity /* c */", "1 -- c", "1, 2", "'unterminated",470                           "$$a$$", "E'\\'' ) FROM sales; DROP TABLE sales; SELECT (E'\\''", "quantity; DELETE FROM sales", ""] {471            let bad = TransformPipeline(name: "bad", source: .dataset("sales"), steps: [.calculatedColumn(name: "c", expression: expression)])472            #expect(throws: DataError.self, "\(expression)") { _ = try TransformCompiler.sql(for: bad, engine: e) }473            let diag = TransformCompiler.validate(bad, engine: e)474            #expect(diag.last?.status == .error)475        }476        // export and createTable accept exactly one SELECT477        let out2 = Fixtures.temporaryDirectory().appendingPathComponent("o.csv")478        #expect(throws: DataError.self) { _ = try e.exportQuery("SELECT 1; DROP TABLE sales", to: out2, format: .csv) }479        #expect(throws: DataError.self) { _ = try e.exportQuery("DROP TABLE sales", to: out2, format: .csv) }480        #expect(throws: DataError.self) { _ = try e.createTable("t2", from: "SELECT 1); DROP TABLE sales; --") }481        #expect(try e.rowCount(of: "sales") == 2000)482    }483484    @Test func expressionScannerAcceptsOrdinarySQL() {485        for ok in ["quantity * unit_price", "CASE WHEN a > 1 THEN 'x;y' ELSE \"weird, name\" END", "round(a / nullif(b, 0), 2)",486                   "coalesce(note, '--')", "list_extract([1, 2, 3], 2)", "regexp_extract(code, '(\\d+)-(\\w)', 1)", "date_trunc('month', d)"] {487            #expect(SQL.expressionProblem(ok) == nil, "\(ok)")488        }489    }490}491
Files main
Menu

Theme