{"record":{"id":"5bbb7d1f408100c5","repo":"can1357/oh-my-pi","slug":"sqlite-where-clause-changed-the-expected-paginatio","errorCode":null,"errorMessage":"SQLite where clause changed the expected pagination parameters; use q=SELECT ... for raw SQL","messagePattern":"SQLite where clause changed the expected pagination parameters; use q=SELECT \\.\\.\\. for raw SQL","errorType":"validation","errorClass":"ToolError","httpStatus":null,"severity":"error","filePath":"packages/coding-agent/src/tools/sqlite-reader.ts","lineNumber":735,"sourceCode":"\n\treturn { kind: \"rowid\" };\n}\n\nexport function queryRows(\n\tdb: Database,\n\ttable: string,\n\topts: { limit: number; offset: number; order?: string; where?: string },\n): { columns: string[]; rows: Record<string, unknown>[]; totalCount: number } {\n\tconst columns = getTableColumns(db, table);\n\tconst validatedWhere = validateWhereClause(opts.where);\n\tconst whereClause = validatedWhere ? ` WHERE ${validatedWhere}` : \"\";\n\tconst orderClause = resolveOrderClause(opts.order, columns);\n\tconst countSql = `SELECT COUNT(*) AS count FROM ${quoteSqliteIdentifier(table)}${whereClause}`;\n\tconst selectSql = `SELECT * FROM ${quoteSqliteIdentifier(table)}${whereClause}${orderClause} LIMIT ? OFFSET ?`;\n\tconst totalCount = db.prepare<SqliteCountRow, []>(countSql).get()?.count ?? 0;\n\tconst statement = db.prepare<SqliteRow, SQLQueryBindings[]>(selectSql);\n\tif (statement.paramsCount !== 2) {\n\t\tthrow new ToolError(\n\t\t\t\"SQLite where clause changed the expected pagination parameters; use q=SELECT ... for raw SQL\",\n\t\t);\n\t}\n\tconst rows = statement.all(opts.limit, opts.offset);\n\treturn { columns, rows, totalCount };\n}\n\nexport function getRowByKey(\n\tdb: Database,\n\ttable: string,\n\tpk: { column: string; type?: string },\n\tkey: string,\n): Record<string, unknown> | null {\n\tgetTableMasterRow(db, table);\n\tconst sql = `SELECT * FROM ${quoteSqliteIdentifier(table)} WHERE ${quoteSqliteIdentifier(pk.column)} = ? LIMIT 1`;\n\tconst binding = coerceLookupValue(key, pk.type ?? \"\");\n\treturn db.prepare<SqliteRow, SQLQueryBindings[]>(sql).get(binding);\n}","sourceCodeStart":717,"sourceCodeEnd":753,"githubUrl":"https://github.com/can1357/oh-my-pi/blob/969062200754ea02cfac922e5ebb8c608c079e15/packages/coding-agent/src/tools/sqlite-reader.ts#L717-L753","documentation":"queryRows interpolates a pre-validated WHERE clause as raw SQL into a SELECT with exactly two bound parameters (LIMIT ? and OFFSET ?). After preparing, it asserts statement.paramsCount === 2; a mismatch means the where string introduced its own `?` placeholders, which the pagination bindings would incorrectly satisfy, so the tool refuses and suggests raw SQL.","triggerScenarios":"Passing ?where= containing a question-mark placeholder, e.g. `?where=name = ?`, making the prepared statement expect 3 params while queryRows supplies 2.","commonSituations":"Developers writing parameterized-style conditions out of habit; pasting a prepared-statement fragment into the where param; encoding issues turning something into a literal `?`.","solutions":["Inline literal values in the where clause instead of `?` placeholders (e.g. `where=name='foo'`)","Switch to a raw query with q= if you need bound parameters: `db.sqlite?q=SELECT * FROM t WHERE name = ?` is not supported — inline the value","Keep the where clause to literal SQL comparisons; the where param is validated SQL text, not a binding API"],"exampleFix":"// before\nsqlite://app.db?users?where=age > ?\n// after\nsqlite://app.db?users?where=age > 18","handlingStrategy":"validation","validationCode":"// before passing where to the selector, ensure it contains no bind placeholders\nif (whereClause.includes(\"?\")) {\n  throw new Error(\"where= must be literal SQL without ? placeholders; inline values instead\");\n}","typeGuard":null,"tryCatchPattern":"try {\n  const result = queryRows(db, table, opts);\n} catch (err) {\n  if (err instanceof ToolError && err.message.includes(\"pagination parameters\")) {\n    // rewrite where clause with literal values or switch to q= raw SQL\n  } else throw err;\n}","preventionTips":["Never put ? placeholders in the where= parameter — inline literal values","Quote string literals properly in where= (e.g. name='foo')","Use q= raw SQL only with inlined values too; this tool does not bind parameters"],"tags":["sqlite","sql-injection-guard","parameter-binding"],"backgroundTag":"parameter-count-mismatch","analyzedSha":"969062200754ea02cfac922e5ebb8c608c079e15","analyzedAt":"2026-08-31T10:29:35.737Z","schemaVersion":2},"datasetVersion":"2026-08-31T14:17:45.589Z"}