function report_customsql_generate_csv($report, $timenow) { global $DB; $starttime = microtime(true); $sql = report_customsql_prepare_sql($report, $timenow); $queryparams = !empty($report->queryparams) ? unserialize($report->queryparams) : array(); $querylimit = !empty($report->querylimit) ? $report->querylimit : REPORT_CUSTOMSQL_MAX_RECORDS; $rs = report_customsql_execute_query($sql, $queryparams, $querylimit); $csvfilenames = array(); $csvtimestamp = null; foreach ($rs as $row) { if (!$csvtimestamp) { list($csvfilename, $csvtimestamp) = report_customsql_csv_filename($report, $timenow); $csvfilenames[] = $csvfilename; if (!file_exists($csvfilename)) { $handle = fopen($csvfilename, 'w'); report_customsql_start_csv($handle, $row, $report->singlerow); } else { $handle = fopen($csvfilename, 'a'); } } $data = get_object_vars($row); foreach ($data as $name => $value) { if (report_customsql_get_element_type($name) == 'date_time_selector' && report_customsql_is_integer($value)) { $data[$name] = userdate($value, '%F %T'); } } if ($report->singlerow) { array_unshift($data, strftime('%Y-%m-%d', $timenow)); } report_customsql_write_csv_row($handle, $data); } $rs->close(); if (!empty($handle)) { fclose($handle); } // Update the execution time in the DB. $updaterecord = new stdClass(); $updaterecord->id = $report->id; $updaterecord->lastrun = time(); $updaterecord->lastexecutiontime = round((microtime(true) - $starttime) * 1000); $DB->update_record('report_customsql_queries', $updaterecord); // Report is runable daily, weekly or monthly. if ($report->runable != 'manual' && !empty($report->emailto)) { if ($csvfilenames) { foreach ($csvfilenames as $csvfilename) { report_customsql_email_report($report, $csvfilename); } } else { // If there is no data. report_customsql_email_report($report); } } return $csvtimestamp; }
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; }
function report_customsql_generate_csv($report, $timenow, $limitnum = REPORT_CUSTOMSQL_MAX_RECORDS) { global $db; $starttime = microtime(true); $sql = report_customsql_prepare_sql($report, $timenow); $rs = report_customsql_execute_query($sql, $limitnum); if (!$rs) { throw new Exception($db->ErrorMsg()); } $cvstimestamp = null; while ($row = rs_fetch_next_record($rs)) { if (!$cvstimestamp) { list($csvfilename, $cvstimestamp) = report_customsql_csv_filename($report, $timenow); if (!file_exists($csvfilename)) { $handle = fopen($csvfilename, 'w'); report_customsql_start_csv($handle, $row, $report->singlerow); } else { $handle = fopen($csvfilename, 'a'); } } $data = get_object_vars($row); if ($report->singlerow) { array_unshift($data, strftime('%Y-%m-%d', $timenow)); } report_customsql_write_csv_row($handle, $data); } rs_close($rs); if (!empty($handle)) { fclose($handle); } // Update the execution time in the DB. $updaterecord = new stdClass(); $updaterecord->id = $report->id; $updaterecord->lastrun = time(); $updaterecord->lastexecutiontime = round((microtime(true) - $starttime) * 1000); update_record('report_customsql_queries', $updaterecord); return $cvstimestamp; }
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; }