// 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; }