// get the citation sort order chosen by the user (only occurs in 'extract.php' form and in query result lists)
$sqlQuery = $_REQUEST['sqlQuery'];
// accept any previous SQL queries
$sqlQuery = stripSlashesIfMagicQuotes($sqlQuery);
// function 'stripSlashesIfMagicQuotes()' is defined in 'include.inc.php'
} else {
$displayType = "";
// ('' will produce the default view)
$showQuery = "0";
$showLinks = "1";
$showRows = $_SESSION['userRecordsPerPage'];
// get the default number of records per page preferred by the current user
$citeStyle = "";
$citeOrder = "";
// TODO: build the complete SQL query using functions 'buildFROMclause()' and 'buildORDERclause()'
$sqlQuery = buildSELECTclause($displayType, $showLinks, "created_by, modified_date, modified_time, modified_by", false, false, $defaultFieldsListViewMajor);
// function 'buildSELECTclause()' is defined in 'include.inc.php', and '$defaultFieldsListViewMajor' is defined in 'ini.inc.php'
$sqlQuery .= " FROM {$tableRefs} WHERE modified_date = CURDATE() ORDER BY modified_date DESC, modified_time DESC";
}
}
} else {
// load the form data that were entered by the user:
$queryID = $formVars['queryID'];
$queryName = $formVars['queryName'];
$displayType = $formVars['displayType'];
$queryViewType = $formVars['queryViewType'];
if (isset($formVars['showQuery'])) {
$showQuery = $formVars['showQuery'];
} else {
$showQuery = "";
}
} else {
$oldMultiRecordQuery = "";
}
// --------------------------------------------------------------------
// (4) DISPLAY HEADER & RESULTS
// (NOTE: Since there's no need to query the database here, we won't perform any of the following: (1) OPEN CONNECTION, (2) SELECT DATABASE, (3) RUN QUERY, (5) CLOSE CONNECTION)
// Show the login status:
showLogin();
// (function 'showLogin()' is defined in 'include.inc.php')
// (4a) DISPLAY header:
// call the 'displayHTMLhead()' and 'showPageHeader()' functions (which are defined in 'header.inc.php'):
displayHTMLhead(encodeHTML($officialDatabaseName) . " -- Record Action Feedback", "noindex,nofollow", "Feedback page that confirms any adding, editing or deleting of records in the " . encodeHTML($officialDatabaseName), "", false, "", $viewType, array());
showPageHeader($HeaderString);
// (4b) DISPLAY results:
// construct the correct SQL query that will link back to the added/edited record:
$sqlQuery = buildSELECTclause("Display", "1", "", true, false);
// function 'buildSELECTclause()' is defined in 'include.inc.php'
if (isset($_SESSION['loginEmail'])) {
// if a user is logged in, show user specific fields:
$sqlQuery .= " FROM {$tableRefs} LEFT JOIN {$tableUserData} ON serial = record_id AND user_id = " . quote_smart($loginUserID) . " WHERE serial RLIKE " . quote_smart("^(" . $serialNo . ")\$") . " ORDER BY author, year DESC, publication";
} else {
// if NO user logged in, don't display any user specific fields:
$sqlQuery .= " FROM {$tableRefs} WHERE serial RLIKE " . quote_smart("^(" . $serialNo . ")\$") . " ORDER BY author, year DESC, publication";
}
// we simply use the fixed default ORDER BY clause here
$sqlQuery = rawurlencode($sqlQuery);
// Generate a 'search.php' URL that points to the formerly displayed results page:
if (!empty($oldMultiRecordQuery)) {
$oldMultiRecordQueryURL = generateURL("search.php", "html", $oldMultiRecordQuery, true);
}
// function 'generateURL()' is defined in 'include.inc.php'
function extractFormElementsBrowseMyRefs($showLinks, $loginEmail, $userID)
{
// IMPORTANT NOTE: Browse functionality is NOT fully implemented yet!!
global $tableRefs, $tableUserData;
// defined in 'db.inc.php'
$browseFieldSelector = $_REQUEST['browseFieldSelector'];
// extract field name chosen by the user
// construct the SQL query:
// TODO: build the complete SQL query using functions 'buildFROMclause()' and 'buildORDERclause()'
// if the chosen field can contain multiple items...
// TODO: we really should check here if the corresponding 'ref_...' table exists!
if (preg_match("/^(author|keywords|editor|language|summary_language|area|location|user_keys|user_groups)\$/i", $browseFieldSelector)) {
list($refTableName, $browseFieldName) = buildRefTableAndFieldNames($browseFieldSelector);
// get correct table name and field name for the 'ref_...' table that matches the chosen field
$browseFieldColumnName = " AS " . preg_replace("/^ref_(\\w+)\$/i", "\\1", $browseFieldName);
// strip the 'ref_' prefix for the column name
$queryRefTableLeftJoinPart = " LEFT JOIN {$refTableName} ON serial = ref_id";
// ...add the appropriate 'LEFT JOIN...' part to the 'FROM' clause
if (preg_match("/^(user_keys|user_groups)\$/i", $browseFieldSelector)) {
$queryRefTableLeftJoinPart .= " AND ref_user_id = " . quote_smart($userID);
}
// add the user's user_id as additional condition to this 'LEFT JOIN...' part
} else {
$browseFieldName = $browseFieldSelector;
$browseFieldColumnName = "";
$queryRefTableLeftJoinPart = "";
}
$query = buildSELECTclause("Browse", $showLinks, "", false, false, "", $browseFieldName . $browseFieldColumnName);
// function 'buildSELECTclause()' is defined in 'include.inc.php'
// if a user specific field was chosen...
if (preg_match("/^(marked|copy|selected|user_keys|user_notes|user_file|user_groups|cite_key|related)\$/i", $browseFieldSelector)) {
$query .= " FROM {$tableRefs} LEFT JOIN {$tableUserData} ON serial = record_id AND user_id = " . $userID;
} else {
$query .= " FROM {$tableRefs}";
}
// add FROM clause
$query .= $queryRefTableLeftJoinPart;
// add additional 'LEFT JOIN...' part (if required)
$query .= " WHERE location RLIKE " . quote_smart($loginEmail);
// add (initial) WHERE clause
$query .= " GROUP BY {$browseFieldName}";
// add the GROUP BY clause
$query .= " ORDER BY records DESC, {$browseFieldName}";
// add the default ORDER BY clause
return $query;
}
$additionalFields = "cite_key";
}
// add user-specific fields which are required in Citation view
} elseif (!preg_match("/^Display\$/i", $displayType)) {
if (!empty($recordIDSelector)) {
// if a record identifier (either 'serial', 'call_number' or 'cite_key') was entered via the 'show.php' web form
$additionalFields = escapeSQL($recordIDSelector);
}
// display the appropriate column
}
if (preg_match("/^Display\$/i", $displayType) and isset($_SESSION['lastDetailsViewQuery'])) {
// get SELECT clause from any previous Details view query:
$query = "SELECT " . extractSELECTclause($_SESSION['lastDetailsViewQuery']);
} else {
// generate new SELECT clause:
$query = buildSELECTclause($displayType, $showLinks, $additionalFields, false, false, "", $browseByField);
}
// function 'buildSELECTclause()' is defined in 'include.inc.php'
// Build FROM clause:
// We'll explicitly add the 'LEFT JOIN...' part to the 'FROM' clause of the SQL query if '$userID' isn't empty. This is done since the 'verifySQLQuery()' function
// (mentioned above) excludes the 'selected' field from its magic. By that we allow the 'selected' field to be queried by any user (using 'show.php')
// (e.g., by URLs of the form: 'show.php?author=...&userID=...&selected=yes').
if (!empty($userID)) {
// the 'userID' parameter was specified -> we include user specific fields
$query .= " FROM {$tableRefs} LEFT JOIN {$tableUserData} ON serial = record_id AND user_id = " . quote_smart($userID);
} else {
$query .= " FROM {$tableRefs}";
}
// add FROM clause
// Build WHERE clause:
$query .= " WHERE";
$userKeysSuggestElements = "";
$userNotesSuggestElements = "";
$userFileSuggestElements = "";
$userGroupsSuggestElements = "";
$citeKeySuggestElements = "";
$relatedSuggestElements = "";
$urlSuggestElements = "";
$doiSuggestElements = "";
$onlineCitationSuggestElements = "";
}
// --------------------------------------------------------------------
// CONSTRUCT SQL QUERY:
// if the script was called with parameters (like: 'record.php?recordAction=edit&serialNo=...')
if ($recordAction == "edit") {
// for the selected record, select *all* available fields:
$query = buildSELECTclause("Edit", "1");
// function 'buildSELECTclause()' is defined in 'include.inc.php'
if (isset($_SESSION['loginEmail'])) {
// if a user is logged in, show user specific fields:
$query .= " FROM {$tableRefs} LEFT JOIN {$tableUserData} ON serial = record_id AND user_id =" . quote_smart($loginUserID) . " WHERE serial RLIKE " . quote_smart("^(" . $serialNo . ")\$");
} else {
// if NO user logged in, don't display any user specific fields:
$query .= " FROM {$tableRefs} WHERE serial RLIKE " . quote_smart("^(" . $serialNo . ")\$");
}
// since we'll only fetch one record, the ORDER BY clause is obsolete here
}
// --------------------------------------------------------------------
// (1) OPEN CONNECTION, (2) SELECT DATABASE
connectToMySQLDatabase();
// function 'connectToMySQLDatabase()' is defined in 'include.inc.php'
// Initialize some variables (to prevent "Undefined variable..." messages):
// Redirect the browser back to the calling page:
header("Location: " . $referer);
// variable '$referer' is globally defined in function 'start_session()' in 'include.inc.php'
exit;
// >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> !EXIT! <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
} else {
$sanitizedWhereClause = extractWHEREclause(" WHERE " . $queryWhereClause);
// attempt to sanitize custom WHERE clause from SQL injection attacks (function 'extractWHEREclause()' is defined in 'include.inc.php')
}
// --------------------------------------------------------------------
// If we made it here, then the script was called with all required parameters (which, currently, is just the 'where' parameter :)
// CONSTRUCT SQL QUERY:
// Note: the 'verifySQLQuery()' function that gets called below will add the user specific fields to the 'SELECT' clause and the
// 'LEFT JOIN...' part to the 'FROM' clause of the SQL query if a user is logged in. It will also add 'orig_record', 'serial', 'file', 'url', 'doi', 'isbn' & 'type' columns
// as required. Therefore it's sufficient to provide just the plain SQL query here:
$sqlQuery = buildSELECTclause("RSS", "1", "", false, false);
// function 'buildSELECTclause()' is defined in 'include.inc.php'
$sqlQuery .= " FROM {$tableRefs} WHERE " . $sanitizedWhereClause;
// add FROM clause and the specified WHERE clause
$sqlQuery .= " ORDER BY created_date DESC, created_time DESC, modified_date DESC, modified_time DESC, serial DESC";
// sort records such that newly added/edited records get listed top of the list
// since a malicious user could change the 'where' parameter manually to gain access to user-specific data of other users, we'll run the SQL query thru the 'verifySQLQuery()' function:
// (this function does also add/remove user-specific query code as required and will fix problems with escape sequences within the SQL query)
$query = verifySQLQuery($sqlQuery, "", "RSS", "1");
// function 'verifySQLQuery()' is defined in 'include.inc.php'
// the 'verifySQLQuery()' function will save an error message to the 'HeaderString' session variable if something went wrong (e.g., if a user who's NOT logged in tries to query user specific fields)
if (isset($_SESSION['HeaderString'])) {
header("Location: index.php");
// redirect to main page ('index.php') which will display the error message stored within the 'HeaderString' session variable
exit;
// >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> !EXIT! <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
function stripFieldFromSQLQuery($sqlQuery, $field, $issueWarning = true)
{
// note that, upon multiple warnings, only the last warning message will be displayed
// if the given '$field' is part of the SELECT or ORDER BY statement...
if (preg_match("/(SELECT |ORDER BY |, *)" . $field . "/i", $sqlQuery)) {
// if the 'SELECT' clause contains '$field':
if ($issueWarning and preg_match("/SELECT(.(?!FROM))+?" . $field . "/i", $sqlQuery)) {
// return an appropriate error message:
// note: we don't write out any error message if the given '$field' does only occur within the 'ORDER' clause (but not within the 'SELECT' clause)
$HeaderString = returnMsg("Display of '" . $field . "' field was omitted!", "warning", "strong", "HeaderString");
}
$sqlQuery = preg_replace("/(SELECT|ORDER BY) " . $field . "( DESC)?/i", "\\1 ", $sqlQuery);
// ...delete '$field' from beginning of 'SELECT' or 'ORDER BY' clause
$sqlQuery = preg_replace("/, *" . $field . "( DESC)?/i", "", $sqlQuery);
// ...delete any other occurrences of '$field' from 'SELECT' or 'ORDER BY' clause
$sqlQuery = preg_replace("/(SELECT|ORDER BY) *, */i", "\\1 ", $sqlQuery);
// ...remove any field delimiters that directly follow the 'SELECT' or 'ORDER BY' terms
$sqlQuery = preg_replace("/SELECT *(?=FROM)/i", buildSELECTclause("", "", "", false, false) . " ", $sqlQuery);
// ...supply generic 'SELECT' clause if it did ONLY contain the given '$field'
$sqlQuery = preg_replace("/ORDER BY *(?=LIMIT|GROUP BY|HAVING|PROCEDURE|FOR UPDATE|LOCK IN|\$)/i", "ORDER BY author, year DESC, publication", $sqlQuery);
// ...supply generic 'ORDER BY' clause if it did ONLY contain the given '$field'
}
// if the given '$field' is part of the WHERE clause...
if (preg_match("/WHERE.+" . $field . "/i", $sqlQuery)) {
// Note: in the patterns below we'll attempt to account for parentheses but this won't catch all cases!
$sqlQuery = preg_replace("/WHERE( *\\( *?)* *" . $field . ".+?(?= (AND|OR)\\b| ORDER BY| LIMIT| GROUP BY| HAVING| PROCEDURE| FOR UPDATE| LOCK IN|\$)/i", "WHERE\\1", $sqlQuery);
// ...delete '$field' from 'WHERE' clause
$sqlQuery = preg_replace("/( *\\( *?)*( *(AND|OR)\\b)? *" . $field . ".+?(?=( *\\) *?)* +((AND|OR)\\b|ORDER BY|LIMIT|GROUP BY|HAVING|PROCEDURE|FOR UPDATE|LOCK IN|\$))/i", "\\1", $sqlQuery);
// ...delete '$field' from 'WHERE' clause
$sqlQuery = preg_replace("/WHERE( *\\( *?)* *(AND|OR)\\b/i", "WHERE\\1", $sqlQuery);
// ...delete any superfluous 'AND' that wasn't removed properly by the two regex patterns above
$sqlQuery = preg_replace("/WHERE( *\\( *?)*(?= ORDER BY| LIMIT| GROUP BY| HAVING| PROCEDURE| FOR UPDATE| LOCK IN|\$)/i", "WHERE serial RLIKE \".+\"", $sqlQuery);
// ...supply generic 'WHERE' clause if it did ONLY contain the given '$field'
if ($issueWarning) {
// return an appropriate error message:
$HeaderString = returnMsg("Querying of '" . $field . "' field was omitted!", "warning", "strong", "HeaderString");
}
}
return $sqlQuery;
}
