function validation($data, $files) { global $CFG, $db, $USER; $errors = parent::validation($data, $files); $sql = stripslashes($data['querysql']); // Simple test to avoid evil stuff in the SQL. if (report_customsql_contains_bad_word($sql)) { $errors['querysql'] = get_string('notallowedwords', 'report_customsql', implode(', ', report_customsql_bad_words_list())); // Do not allow any semicolons. } else { if (strpos($sql, ';') !== false) { $errors['querysql'] = get_string('nosemicolon', 'report_customsql'); // Make sure prefix is prefix_, not explicit. } else { if ($CFG->prefix != '' && preg_match('/\\b' . $CFG->prefix . '\\w+/i', $sql)) { $errors['querysql'] = get_string('noexplicitprefix', 'report_customsql', $CFG->prefix); // Now try running the SQL, and ensure it runs without errors. } else { $report = new stdClass(); $report->querysql = $sql; $report->runable = $data['runable']; $sql = report_customsql_prepare_sql($report, time()); $rs = report_customsql_execute_query($sql, 2); if (!$rs) { $errors['querysql'] = get_string('queryfailed', 'report_customsql', $db->ErrorMsg()); } else { if (!empty($data['singlerow'])) { if (rs_EOF($rs)) { $errors['querysql'] = get_string('norowsreturned', 'report_customsql'); } else { rs_fetch_next_record($rs); if (!rs_EOF($rs)) { $errors['querysql'] = get_string('morethanonerowreturned', 'report_customsql'); } } } } if ($rs) { rs_close($rs); } } } } return $errors; }
public function test_report_customsql_contains_bad_word() { $string = 'DELETE * FROM prefix_user u WHERE u.id > 0'; $this->assertEquals(1, report_customsql_contains_bad_word($string)); }
public function validation($data, $files) { global $CFG, $DB, $USER; $errors = parent::validation($data, $files); $sql = $data['querysql']; if (report_customsql_contains_bad_word($sql)) { // Obviously evil stuff in the SQL. $errors['querysql'] = get_string('notallowedwords', 'report_customsql', implode(', ', report_customsql_bad_words_list())); } else { if (strpos($sql, ';') !== false) { // Do not allow any semicolons. $errors['querysql'] = get_string('nosemicolon', 'report_customsql'); } else { if ($CFG->prefix != '' && preg_match('/\\b' . $CFG->prefix . '\\w+/i', $sql)) { // Make sure prefix is prefix_, not explicit. $errors['querysql'] = get_string('noexplicitprefix', 'report_customsql', $CFG->prefix); } else { if (!array_key_exists('runable', $data)) { // This happens when the user enters a query including placehoders, and // selectes Run: Scheduled, and then tries to save the form. $errors['runablegroup'] = get_string('noscheduleifplaceholders', 'report_customsql'); } else { // Now try running the SQL, and ensure it runs without errors. $report = new stdClass(); $report->querysql = $sql; $report->runable = $data['runable']; if ($report->runable === 'daily') { $report->at = $data['at']; } $sql = report_customsql_prepare_sql($report, time()); // Check for required query parameters if there are any. $queryparams = array(); foreach (report_customsql_get_query_placeholders($sql) as $queryparam) { $queryparam = substr($queryparam, 1); $formparam = 'queryparam' . $queryparam; if (!isset($data[$formparam])) { $errors['params'] = get_string('queryparamschanged', 'report_customsql'); break; } $queryparams[$queryparam] = $data[$formparam]; } if (!isset($errors['params'])) { try { $rs = report_customsql_execute_query($sql, $queryparams, 2); if (!empty($data['singlerow'])) { // Count rows for Moodle 2 as all Moodle 1.9 useful and more performant // recordset methods removed. $rows = 0; foreach ($rs as $value) { $rows++; } if (!$rows) { $errors['querysql'] = get_string('norowsreturned', 'report_customsql'); } else { if ($rows >= 2) { $errors['querysql'] = get_string('morethanonerowreturned', 'report_customsql'); } } } // Check the list of users in emailto field. if ($data['runable'] !== 'manual') { if ($invaliduser = report_customsql_validate_users($data['emailto'], $data['capability'])) { $errors['emailto'] = $invaliduser; } } $rs->close(); } catch (dml_exception $e) { $errors['querysql'] = get_string('queryfailed', 'report_customsql', $e->getMessage() . ' ' . $e->debuginfo); } catch (Exception $e) { $errors['querysql'] = get_string('queryfailed', 'report_customsql', $e->getMessage()); } } } } } } // Check querylimit in range 1 .. REPORT_CUSTOMSQL_MAX_RECORDS. if (empty($data['querylimit']) || $data['querylimit'] > REPORT_CUSTOMSQL_MAX_RECORDS) { $errors['querylimit'] = get_string('querylimitrange', 'report_customsql', REPORT_CUSTOMSQL_MAX_RECORDS); } return $errors; }