QuizExportAttemptsCsv
quiz_exportattemptscsv
quiz_exportattemptscsv is a Moodle quiz report subplugin (installed under mod/quiz/report/exportattemptscsv). It adds a "Export Attempts history as CSV" entry to the quiz Results menu, letting a teacher select attempts and download their full question/step history as a CSV file. Optional columns expose question text, student responses, correct answers and personal (GDPR) fields (username, first/last name, user id).
The plugin is well built and, most importantly, well secured. The sensitive operation — bulk CSV export of quiz attempt data — is protected by a complete, layered access-control chain:
- The core quiz report dispatcher (
mod/quiz/report.php) enforcesrequire_login()and, viaquiz_report_list(), only lists reports for which the user holds the report's registered capability. The plugin'sdb/install.php/db/upgrade.phpregister that capability asquiz/exportattemptscsv:download. - The plugin re-checks
require_capability('quiz/exportattemptscsv:download', ...)in bothprocess_actions()andexport_attempts(). - The export action validates
confirm_sesskey()and reads attempt ids withoptional_param_array(..., PARAM_INT). - Selected attempt ids are validated against the quiz and against the enrolled-students
sql_join(allowedjoin) before export, correctly preventing IDOR and respecting separate-groups visibility. This is then re-validated per attempt insideexport_attempts(). - All SQL uses
$DBplaceholders; CSV output is hardened against formula/CSV injection and usesfputcsv(..., escape: ""); temporary files are created withmake_request_directory()and native PHP file functions, which is the sanctioned pattern.
No SQL injection, XSS, IDOR, CSRF, auth-bypass or unsafe file/DB access was found. The privacy provider is complete for the user preferences the plugin owns, MOODLE_INTERNAL guards are applied exactly where required, and the install/upgrade code matches core's own quiz-report pattern (with uninstall handled by core's plugininfo\quiz::uninstall_cleanup()).
The remaining findings are minor: a fragile and now-unnecessary MySQL/MariaDB user-variable row-numbering fallback, a capability that omits the RISK_PERSONAL bitmask despite exporting personal data, a raw readfile() download that bypasses send_temp_file() (missing no-cache headers on personal data and holding the session lock), and the absence of bundled automated tests. None are security vulnerabilities and none affect other users' data.
Overview
quiz_exportattemptscsv is a small, focused quiz report subplugin that exports quiz attempt history to CSV. The code is mature and security-conscious — several comments reference prior hardening (IDOR mitigation, CSV formula neutralisation, cross-DB SQL).
Security posture — strong
The export path, which is the only state-/data-sensitive operation, is protected end to end:
- Authentication & authorization:
require_login()and capability filtering happen in the core dispatcher; the plugin additionally callsrequire_capability('quiz/exportattemptscsv:download')in bothprocess_actions()andexport_attempts(). - CSRF:
confirm_sesskey()gates the export action. - IDOR: submitted
attemptid[]values are validated against the quiz and the enrolled-students join before (and again during) export, so a teacher cannot export attempts outside their own report scope / group. - Injection: all queries are parameterised; CSV cells are prefixed to neutralise leading formula characters (
=,+,-,@, tab, CR) andfputcsvis called withescape: "". - File handling: temp CSV is written under
make_request_directory()using native PHP functions — the accepted approach for temp files.
Findings (all low / info)
- Fragile MySQL/MariaDB row-numbering fallback (low) — a
@row_numberuser variable set via a separate$DB->execute()is used only for MySQL/MariaDB, although every DB engine supported by Moodle 5.0+ supports the ANSIROW_NUMBER() OVER()already used for the other engines. - Capability omits
RISK_PERSONAL(low) — the download capability exposes personal data but does not declare the personal-data risk that its cloned-from analogmod/quiz:viewreportscarries. - Raw
readfile()download (low) — usingsend_temp_file()would add no-cache headers for the personal-data payload, close the session lock during the long export, and support X-Sendfile. - No automated tests bundled (info) — the CI runs PHPUnit/Behat but no
tests/directory exists.
No third-party libraries are bundled.
Findings
In export_attempts() the running row-number column (num) is produced two different ways depending on the database family:
- For postgres / sqlsrv / oci it uses the portable ANSI window function
ROW_NUMBER() OVER (ORDER BY quiza.userid). - For MySQL / MariaDB it falls back to a session user variable: a separate statement
SET @row_number = 0executed via$DB->execute(), then(@row_number:=@row_number + 1)inside theSELECT.
The MySQL/MariaDB fallback is both fragile and unnecessary:
- Reading and assigning a user variable within the same statement has undefined evaluation order in modern MySQL (8.x), and the practice is deprecated. It happens to work for this simple ordered query but is not guaranteed.
- It requires an extra round-trip (
SET @row_number = 0) and relies on that variable surviving on the same connection as the follow-upget_records_sql(). - Every database engine that Moodle 5.0+ supports (MySQL 8.4, MariaDB 10.11, PostgreSQL 14, SQL Server 2017, Oracle 19) supports
ROW_NUMBER() OVER(), so the same portable expression already used for the other engines can be used for all of them.
The num value is a purely cosmetic display sequence, so there is no security or data-integrity impact — this is a code-quality / portability improvement.
Low risk. No security or data-integrity impact: the affected value is a cosmetic sequence number, and the code already runs the right query per engine. The concern is maintainability and reliance on a deprecated, undefined-order MySQL construct plus an unnecessary extra statement/round-trip. Because all Moodle 5.0+ supported engines implement ROW_NUMBER() OVER(), the fallback can simply be deleted.
export_attempts() builds a large detail query joining quiz_attempts, question_usages, question_attempts, question_attempt_steps and question_attempt_step_data, and streams the result to CSV. The num column is only a 1-based counter shown in the first CSV column (seq). The author has clearly considered cross-DB portability (the postgres/sqlsrv/oci branch is correct), but retained a legacy MySQL-specific idiom for the MySQL/MariaDB branch.
$dbfamily = $DB->get_dbfamily();
$sqlsetrownumber = "";
if (in_array($dbfamily, ['postgres', 'sqlsrv', 'oci'])) {
// Standard ANSI SQL window function supported by Postgres, SQL Server, and Oracle.
$sqlquizattemptsdetails = "SELECT
ROW_NUMBER() OVER (ORDER BY quiza.userid) AS num,";
} else {
// Fallback variable-assignment for MySQL and MariaDB.
$sqlsetrownumber = "SET @row_number = 0";
$sqlquizattemptsdetails = "SELECT
(@row_number:=@row_number + 1) AS num,";
}
Use the portable window function for every engine and drop the fallback branch and its extra round-trip:
$sqlquizattemptsdetails = "SELECT
ROW_NUMBER() OVER (ORDER BY quiza.userid) AS num,";
Then remove $sqlsetrownumber and the $DB->execute($sqlsetrownumber) block entirely.
// For MySQL/MariaDB set first rownumber to zero.
if ($sqlsetrownumber != "") {
$DB->execute($sqlsetrownumber);
}
Remove this block once the ROW_NUMBER() OVER() window function is used for all database families.
The quiz/exportattemptscsv:download capability lets a holder export personal data — username, firstname, lastname and per-step userid, plus free-text student responses — whenever the Export personal information option is enabled (which defaults to on).
The capability is declared with clonepermissionsfrom => 'mod/quiz:viewreports', but clonepermissionsfrom only seeds role assignments; it does not copy risk metadata. The core capability it clones from, mod/quiz:viewreports, is declared with 'riskbitmask' => RISK_PERSONAL. This plugin's capability declares no risk bitmask, so administrators reviewing or assigning it in the permissions UI do not see the personal data risk warning that the equivalent core capability carries.
This is a capability-metadata / GDPR-awareness gap, not an access-control flaw — the capability is still correctly enforced everywhere it matters.
Low risk. The risk bitmask is informational metadata surfaced in the Define roles / Check permissions UI; its absence does not weaken enforcement and creates no exploit path. The value is in making the personal-data exposure visible to administrators, consistent with the core capability this one clones and with the plugin's own GDPR-oriented feature set.
By default quiz_exportattemptscsv_options::$showgdpr is true, so the export includes personal fields unless the teacher unticks the option. The capability is granted to teacher/editingteacher/manager by default — the same roles that already hold mod/quiz:viewreports and can view this data in the standard quiz reports, so there is no privilege escalation.
'quiz/exportattemptscsv:download' => [
'captype' => 'read',
'contextlevel' => CONTEXT_MODULE,
'legacy' => [
'manager' => CAP_ALLOW,
'teacher' => CAP_ALLOW,
'editingteacher' => CAP_ALLOW,
],
'clonepermissionsfrom' => 'mod/quiz:viewreports',
],
Declare the personal-data risk so the permissions UI flags it, matching the cloned-from core capability:
'quiz/exportattemptscsv:download' => [
'riskbitmask' => RISK_PERSONAL,
'captype' => 'read',
'contextlevel' => CONTEXT_MODULE,
'legacy' => [
'manager' => CAP_ALLOW,
'teacher' => CAP_ALLOW,
'editingteacher' => CAP_ALLOW,
],
'clonepermissionsfrom' => 'mod/quiz:viewreports',
],
The export writes the CSV to a request-scoped temp file and streams it back with manual header() calls, readfile(), unlink() and exit. Moodle provides send_temp_file() (in lib/filelib.php, auto-loaded on every page) for exactly this purpose, and it adds several protections the current code lacks:
- Session lock release:
send_temp_file()calls\core\session\manager::write_close()before streaming. The export raises the time limit to 600s; the current code holds the session lock for the whole download, which can block other concurrent requests from the same user. - Cache-control headers for sensitive data:
send_temp_file()emitsCache-Control: private ... no-transformandPragma/Expiresheaders. The plugin's response contains personal data but sets no cache headers, so an intermediary/proxy could retain a copy. - X-Sendfile acceleration and cleanup:
send_temp_file()usesreadfile_accel()(honouringxsendfile) and unlinks the file / registers shutdown cleanup, and marks the form download complete for the JS progress UI.
Writing the temp file with make_request_directory() + native functions is itself fine; only the final delivery step should move to the core helper.
Low risk. The download itself is authenticated and gated by capability + sesskey + IDOR checks, and most sites run HTTPS (limiting proxy caching), so the missing no-cache headers are a mild hardening gap rather than a live disclosure. The session-lock hold is a performance/concurrency concern for large exports. Both are resolved by delegating to send_temp_file().
This runs inside process_actions(), which is invoked before any page HTML is emitted, so streaming the file and exiting is structurally correct. The Content-Disposition filename contains only $quiz->id (an integer), so there is no header-injection risk in the current code — the finding is about robustness and privacy hygiene of the delivery, not injection.
fclose($csvfile);
header("Content-Type: text/csv; charset=UTF-8");
header("Content-Disposition: attachment; filename=\"quiz_exportattempts_" . $quiz->id . ".csv\"");
readfile($tmpcsvfile);
unlink($tmpcsvfile);
exit;
Replace the manual header/readfile/unlink/exit block with the core helper (defined in lib/filelib.php, already loaded):
fclose($csvfile);
send_temp_file($tmpcsvfile, 'quiz_exportattempts_' . $quiz->id . '.csv');
send_temp_file() sets the disposition/cache headers, closes the session, streams with X-Sendfile support, unlinks the temp file and exits.
The GitHub Actions workflow (.github/workflows/ci.yml) runs moodle-plugin-ci phpunit and moodle-plugin-ci behat, but the plugin ships no tests/ directory and no Behat feature files. The security-relevant logic — attempt-id validation against the allowed-students join (IDOR mitigation), CSV formula-injection hardening, and the GDPR column toggles — is exactly the kind of behaviour that benefits from regression tests.
Adding at least a PHPUnit test for export_attempts()/process_actions() validation and the CSV hardening closure, plus a Behat scenario for the export flow, would guard these protections against future changes.
Informational. Not a defect in shipped behaviour; a maintainability and regression-safety recommendation.
The plugin is otherwise CI-complete (phplint, phpcs, phpdoc, savepoints, grunt, mustache checks are all wired up). The absence of tests means the CI's phpunit/behat steps currently execute nothing plugin-specific.
Strong, layered access control (positive). The export is protected by require_login() + capability filtering in the core dispatcher, plus require_capability('quiz/exportattemptscsv:download'), confirm_sesskey(), and an IDOR check that validates every submitted attemptid against the quiz and the enrolled-students sql_join before and during export. Separate-groups visibility is respected via the inherited allowedjoin. This is a good example of defence in depth.
Install/upgrade and uninstall are correct. db/install.php mirrors core's own quiz report pattern (e.g. quiz_statistics), inserting into quiz_reports (with a quiz_report legacy fallback) and using $DB->get_manager() only for table_exists() — no DDL. No db/uninstall.php is needed because core's \mod_quiz\plugininfo\quiz::uninstall_cleanup() deletes the quiz_reports row on removal.
MOODLE_INTERNAL guards are placed correctly. version.php, db/access.php and report.php (which has file-scope require_once side effects) carry the guard; the class-only files (export_form.php, export_options.php, export_table.php) and the namespaced privacy provider correctly omit it, avoiding the MoodleInternalNotNeeded lint warning.
Minor string typo. The English (and Italian) language string qassequencenumber ends with a stray ' // ' ('Attempt step seq // '), which will appear verbatim as a CSV header. Worth cleaning up, though harmless.
Privacy API is complete for what the plugin owns. The plugin stores no data in its own tables; it only owns four user preferences, and classes/privacy/provider.php implements the metadata + user_preference_provider interfaces for all four, with a privacy:metadata summary string.