Koha Solutions knowledge base: run log of a live check Article https://kohasolutions.com/knowledge-base/koha-sql-reports Check scripts/kb/live/koha-sql-reports.mjs (SHA-256 d64d03c0541cf83eced08a136d7964ffb4e6d72bf0b3989d7e150e6b406048b1) Ran 2026-11-06T08:53:59Z (UTC), 8 s Koha 26.05.02 (Koha::version 26.05.02.002, database version 26.0600002) Source https://git.koha-community.org/Koha-community/Koha/src/commit/d3fc229bc5eaea25dfe88f86149386408fe5d32d System Debian GNU/Linux 13 (trixie) Result PASS: all 15 claims held Each claim is something the article says. "seen" is what the check read back when it is not just a yes/no. Changes the check made to the test server were undone afterwards, as its header describes. == Claims == ok precondition: no Kbverify reports ok the article prints seven queries seen: 7 ok every one of the seven runs without an error on Koha 26.05 seen: #1: 53 rows | #2: 15 rows | #3: 3 rows | #4: 598 rows | #5: 0 rows | #6: 127 rows | #7: 1459 rows ok …and the ones over the sample data return rows (stock by location, checkouts per month, never borrowed, items of one type) seen: #1: 53 #2: 15 #3: 3 #4: 598 #5: 0 #6: 127 #7: 1459 ok Create from SQL: "Report name:", "Report is public:", "Cache expiry:", "Notes:", "Insert runtime parameter", Save report ok Insert runtime parameter offers Libraries, Item types, Patron categories, Date and Authorized values seen: Advanced search, Item search, Lists, Cataloging, Acquisitions, Authorities, Serials, Course reserves, Reports, Suggestions, Tools, Administration, Live Chat, About Koha, Set library, , My account, My checkouts, Log out, Disable User CSS, Disable User JS, Disable Custom Stylesheets, New guided report ok Save report: Your report "Kbverify items by location" has been saved, with Run report seen: Your report "Kbverify items by location" has been saved | Edit SQL report | Repo ok Run report asks for the library (<>), and Run the report for Centerville lists one row per location and item type seen: 15 rows vs 15 ok …with Rows per page and Download ok a DELETE report is refused at save: "This report contains an SQL keyword: DELETE." … "Only SELECT queries are allowed." seen: The following error was encountered: | This report contains an SQL keyword: DELETE. | Use of this keyword is not allowed in Koha reports due to security and data integrity risks. Only SELECT queries a ok a misspelt column (titel) saves without complaint ok …and fails only when run: "SQL error:" "Unknown column 'titel' in 'SELECT'" "Please check the log for further details." seen: SQL error: | Unknown column 'titel' in 'SELECT' | Please check the log for further details. | Return to previous page | View reports | Saved reports | Reports d ok a password column: "The column selection in this report includes a password field." seen: save: true | run: false ok a cache expiry of 31 days: "Please select a cache expiry less than 30 days." ok Reports home links "Koha database schema" to schema.koha-community.org seen: https://schema.koha-community.org/main == The check that produced this log (scripts/kb/live/koha-sql-reports.mjs) == /** * Live check for prisma/ks-kb/84-koha-sql-reports.ts (saved 2026-11-06; first run by hand 2026-11-04, not saved). The * seven queries the article prints, taken from the article file itself and run in koha-mysql with the runtime * parameters filled in (Centerville, BK, a date range), so an edit to a query is re-checked too. Then Reports → Create * from SQL: the form and its runtime parameters, a report saved with <> and run for Centerville * (rows, Rows per page, Download), and each error the article's table lists: DELETE refused at save, a misspelt column * saved and failing only at run, a password column, a cache expiry over 30 days. Koha database schema link. * Undone: every report named "Kbverify …". */ import { readFileSync } from 'node:fs' export const meta = { koha: '26.05.02', passed: '2026-11-06' } const ARTICLE = new URL('../../../prisma/ks-kb/84-koha-sql-reports.ts', import.meta.url) /** The SQL of every commands block in the article (the code strings), unescaped. */ function articleQueries() { const src = readFileSync(ARTICLE, 'utf8') return [...src.matchAll(/type: 'commands',[\s\S]*?code: "((?:[^"\\]|\\.)*)"/g)].map((m) => JSON.parse(`"${m[1]}"`)) } export default async function run(ctx) { const { q } = ctx ctx.check('precondition: no Kbverify reports', ctx.sqlValue("SELECT COUNT(*) FROM saved_sql WHERE report_name LIKE 'Kbverify%'") === '0') ctx.restore('remove the Kbverify reports', () => ctx.sql("DELETE FROM saved_sql WHERE report_name LIKE 'Kbverify%'")) // 1. The seven queries. const qs = articleQueries() ctx.check('the article prints seven queries', qs.length === 7, String(qs.length)) const fill = (s) => s.replace(/<>/g, "'CPL'").replace(/<>/g, "'BK'").replace(/<>/g, "'2000-01-01'").replace(/<>/g, 'CURDATE()') const results = qs.map((sqlText, i) => { const r = ctx.sh(`cat > /tmp/kbv-84.sql <<'KBVSQL'\n${fill(sqlText)};\nKBVSQL\nkoha-mysql ${ctx.INSTANCE} -N -B < /tmp/kbv-84.sql 2>&1; rm -f /tmp/kbv-84.sql`) const rows = r.out ? r.out.split('\n').length : 0 return { i: i + 1, ok: !/ERROR \d+/.test(r.out), rows, err: /ERROR/.test(r.out) ? r.out.slice(0, 160) : '' } }) ctx.check('every one of the seven runs without an error on Koha 26.05', results.every((r) => r.ok), results.map((r) => `#${r.i}: ${r.ok ? `${r.rows} rows` : r.err}`).join(' | ')) ctx.check('…and the ones over the sample data return rows (stock by location, checkouts per month, never borrowed, items of one type)', [2, 3, 4, 7].every((n) => results[n - 1].rows > 0), results.map((r) => `#${r.i}: ${r.rows}`).join(' ')) // 2. Create from SQL. const staff = await ctx.newPage() await ctx.staffLogin(staff) const text = (sel) => ctx.pageText(staff, sel) const setSql = (v) => staff.evaluate((val) => { const ta = document.getElementById('sql'); ta.value = val; const cm = document.querySelector('.CodeMirror'); if (cm && cm.CodeMirror) cm.CodeMirror.setValue(val) }, v) const create = async (name, sqlText, expiry) => { await staff.goto(`${ctx.STAFF}/cgi-bin/koha/reports/guided_reports.pl?op=add_form_sql`) await staff.fill('#reportname', name) await setSql(sqlText) if (expiry) { await staff.fill('#cache_expiry', String(expiry[0])); await staff.selectOption('#cache_expiry_units', expiry[1]) } await Promise.all([staff.waitForLoadState(), staff.click('input[type=submit][value="Save report"], button:has-text("Save report")')]) return text() } await staff.goto(`${ctx.STAFF}/cgi-bin/koha/reports/guided_reports.pl?op=add_form_sql`) const form = await text() const params = (await staff.locator('a.insert_param, .dropdown-menu a[data-param], #runtime_parameters a, ul.dropdown-menu a').allInnerTexts()).map((s) => s.trim()) ctx.check('Create from SQL: "Report name:", "Report is public:", "Cache expiry:", "Notes:", "Insert runtime parameter", Save report', ['Create from SQL', 'Report name:', 'Report is public:', 'Cache expiry:', 'Notes:', 'Insert runtime parameter'].every((s) => form.includes(s))) ctx.check('Insert runtime parameter offers Libraries, Item types, Patron categories, Date and Authorized values', ['Libraries', 'Item types', 'Patron categories', 'Date', 'Authorized values'].every((p) => params.includes(p)), params.join(', ').slice(0, 300)) const saved = await create('Kbverify items by location', qs[1]) ctx.check('Save report: Your report "Kbverify items by location" has been saved, with Run report', saved.includes('Your report "Kbverify items by location" has been saved') && saved.includes('Run report'), saved.slice(saved.indexOf('Your report'), saved.indexOf('Your report') + 80)) const id = ctx.sqlValue("SELECT id FROM saved_sql WHERE report_name = 'Kbverify items by location'") await staff.goto(`${ctx.STAFF}/cgi-bin/koha/reports/guided_reports.pl?op=run&id=${id}`) const ask = await text() const libSel = staff.locator('select[name="sql_params"]').first() await libSel.selectOption('CPL') await Promise.all([staff.waitForLoadState(), staff.locator('button:has-text("Run the report"), input[value="Run the report"]').first().click()]) const res = await text() const rows = await staff.locator('table#report_results tbody tr, #report_results tbody tr, table.dataTable tbody tr').count() const expect = Number(ctx.sqlValue("SELECT COUNT(*) FROM (SELECT 1 FROM items WHERE homebranch = 'CPL' GROUP BY location, itype) x")) ctx.check('Run report asks for the library (<>), and Run the report for Centerville lists one row per location and item type', ask.includes('Library') && rows === expect && rows > 0, `${rows} rows vs ${expect}`) ctx.check('…with Rows per page and Download', res.includes('Rows per page') && res.includes('Download')) // 3. The errors. const del = await create('Kbverify delete', 'DELETE FROM items WHERE 1 = 0') ctx.check('a DELETE report is refused at save: "This report contains an SQL keyword: DELETE." … "Only SELECT queries are allowed."', del.includes('The following error was encountered:') && del.includes('This report contains an SQL keyword: DELETE.') && del.includes('Only SELECT queries are allowed.') && ctx.sqlValue("SELECT COUNT(*) FROM saved_sql WHERE report_name = 'Kbverify delete'") === '0', del.slice(del.indexOf('The following'), del.indexOf('The following') + 200)) const typo = await create('Kbverify typo', 'SELECT titel FROM biblio') const typoId = ctx.sqlValue("SELECT id FROM saved_sql WHERE report_name = 'Kbverify typo'") ctx.check('a misspelt column (titel) saves without complaint', typo.includes('has been saved') && !!typoId) await staff.goto(`${ctx.STAFF}/cgi-bin/koha/reports/guided_reports.pl?op=run&id=${typoId}`) const tr = await text() ctx.check('…and fails only when run: "SQL error:" "Unknown column \'titel\' in \'SELECT\'" "Please check the log for further details."', tr.includes('SQL error:') && /Unknown column 'titel' in 'SELECT'/.test(tr) && tr.includes('Please check the log for further details.'), tr.slice(tr.indexOf('SQL error'), tr.indexOf('SQL error') + 160)) const pw = await create('Kbverify password', 'SELECT cardnumber, password FROM borrowers') const pwId = ctx.sqlValue("SELECT id FROM saved_sql WHERE report_name = 'Kbverify password'") let pwRun = '' if (pwId) { await staff.goto(`${ctx.STAFF}/cgi-bin/koha/reports/guided_reports.pl?op=run&id=${pwId}`); pwRun = await text() } ctx.check('a password column: "The column selection in this report includes a password field."', pw.includes('The column selection in this report includes a password field.') || pwRun.includes('The column selection in this report includes a password field.'), `save: ${pw.includes('password field')} | run: ${pwRun.includes('password field')}`) const ce = await create('Kbverify cache', 'SELECT 1', [31, 'days']) ctx.check('a cache expiry of 31 days: "Please select a cache expiry less than 30 days."', ce.includes('Please select a cache expiry less than 30 days.')) // 4. The schema link. await staff.goto(`${ctx.STAFF}/cgi-bin/koha/reports/reports-home.pl`) const schema = await staff.locator('a:has-text("Koha database schema")').first().getAttribute('href').catch(() => '') ctx.check('Reports home links "Koha database schema" to schema.koha-community.org', /schema\.koha-community\.org/.test(schema ?? ''), schema) }