function SLAM_makeUpdateStatement($db, $table, $array, $where, $limit = False)
{
$a = array();
foreach ($array as $k => $v) {
$a[] = "`" . sql_real_escape($k, $db->link) . "`='" . sql_real_escape($v, $db->link) . "'";
}
$a = implode(',', $a);
$b = $limit === false ? '' : "LIMIT {$limit}";
return "UPDATE `{$table}` SET {$a} WHERE ({$where}) {$b}";
}
function sql_real_escape($a, $link)
{
/*
recursively mysql_real_escape_string's an array or a string
*/
if (!is_array($a)) {
return substr($link->quote($a), 1, -1);
}
foreach ($a as $k => $v) {
$a[$k] = is_array($v) ? sql_real_escape($v, $link) : substr($link->quote($v), 1, -1);
}
return $a;
}
function SLAM_updateArchiveFileList(&$config, $db, $category, $identifier)
{
$path = SLAM_getArchivePath($config, $category, $identifier);
$files = SLAM_getArchiveFiles($config, $path);
if (empty($files)) {
return;
}
/* slam together all the files for the records, separated by newlines */
$s = sql_real_escape(implode("\n", array_keys($files)), $db->link);
$q = "UPDATE `{$category}` SET `Files`='{$s}' WHERE (`Identifier`='{$identifier}') LIMIT 1";
if (($result = $db->Query($q)) === false) {
$config->errors[] = 'Database error: Could not update asset file list:' . $db->ErrorState();
}
return;
}
public function getRecords(&$config, $db, $user, $request)
{
if (!is_array($request->categories)) {
return true;
}
if ($request->action == 'new') {
return true;
}
foreach ($request->categories as $category => $identifiers) {
$this->assets[$category] = array();
/* if the order-by field isn't in this category, default to Identifier */
if (!in_array($request->order['field'], array_keys($this->fields[$category]))) {
$request->order['field'] = 'Identifier';
}
/* convert identifiers to numeric sort */
if ($request->order['field'] == 'Identifier') {
$order = 'CAST(SUBSTR(`Identifier`,6) AS SIGNED) ' . sql_real_escape($request->order['direction'], $db->link);
} else {
$order = "`" . sql_real_escape($request->order['field'], $db->link) . "` " . sql_real_escape($request->order['direction'], $db->link);
}
/* retrieve assets from the table */
if (empty($identifiers) || $request->action == 'save') {
$select = "1=1";
$limit = $request->limit > 0 ? "{$request->limit}," . ($request->limit + $config->values['list_max']) : "0,{$config->values['list_max']}";
} else {
$select = "`Identifier`='" . implode("' OR `Identifier`='", $identifiers) . "'";
$limit = count($identifiers);
}
$query = SLAM_makePermsQuery($config, $db, $user, '*', $category, $select, $order, $limit);
if (($this->assets[$category] = $db->getRecords($query)) === false) {
$config->errors[] = 'Database error: Error retrieving assets:' . $db->ErrorState() . $query;
}
/* count the number of visible assets in the category */
$query = SLAM_makePermsQuery($config, $db, $user, 'COUNT(*)', $category, $select);
if (($count = $db->getRecords($query)) === false) {
$config->errors[] = 'Database error: Error counting assets:' . $db->ErrorState() . $query;
}
$this->counts[$category] = $count[0]['COUNT(*)'];
}
return true;
}
function replaceExistingAsset($config, $db, $user, $category, $asset)
{
/* save the asset perms for now */
$permissions = (array) $asset['permissions'];
unset($asset['permissions']);
/* don't trust the user-provided asset, check permissions separately */
$old_perms = $db->GetRecords("SELECT * FROM `{$config->values['perms_table']}` WHERE `Identifier`='{$asset['Identifier']}' LIMIT 1");
if (count($old_perms) == 1) {
$asset['permissions'] = $old_perms[0];
} else {
SLAM_setDefaultPerms($asset, $config);
}
/* verify that the current user is qualified */
if (SLAM_getAssetAccess($user, $asset) < 2) {
return SLAM_makeErrorHTML('Authentication error: You are not authorized to save edits to this asset.', true);
}
/* don't try and save the permissions field into the asset table */
unset($asset['permissions']);
$q = SLAM_makeUpdateStatement($db, $category, $asset, "`Identifier`='" . sql_real_escape($asset['Identifier'], $db->link) . "'", 1);
if ($db->Query($q) === false) {
return SLAM_makeErrorHTML('Database error: could not save record: ' . $db->ErrorState(), true);
}
$asset['permissions'] = $permissions;
if (($ret = SLAM_saveAssetPerms($config, $db, $asset)) !== true) {
return $ret;
}
return True;
}
function SLAM_loadSearchResults($config, $db, $user, $request)
{
/*
runs a search query on the requested tables and returns as SLAMresult containing the matching records
*/
/* return empty result on invalid attempt */
if (empty($request->search)) {
return new SLAMresult();
}
$categories = array_keys($request->categories);
if (empty($categories)) {
return new SLAMresult();
}
/* collect all of the possible fields to search in from the current category(s)*/
$result = new SLAMresult();
/* retrieve the structure of the categories in the request */
$result->getStructures($config, $db, $user, $request);
/* retrieve all the searchable fields in the provided category(s) */
$fields = array();
foreach ($categories as $category) {
if (empty($fields)) {
$fields = array_keys($result->fields[$category]);
} else {
$fields = array_intersect($fields, array_keys($result->fields[$category]));
}
}
/* if the user isn't a superuser, make sure that hidden fields aren't searched */
$diff = array_intersect($fields, $config->values['hide_fields']);
if (!$user->superuser) {
$fields = array_diff($fields, $diff);
}
/* don't forget to remove the pseudofields! */
$diff = array_intersect($fields, array('permissions', 'Files'));
$fields = array_diff($fields, $diff);
/* use an assoc array to sanitize search modes */
$allowed_modes = array('LIKE' => 'LIKE', 'NOT LIKE' => 'NOT LIKE', '>' => '>', '<' => '<', '=' => '=');
$allowed_likes = array('LIKE', 'NOT LIKE');
$allowed_joins = array('AND', 'OR');
/* extract search terms */
$terms = array();
$joins = array();
foreach ($request->search['field'] as $i => $field) {
/* automatically bracket LIKE and NOTLIKE terms with % */
$value = in_array($request->search['mode'][$i], $allowed_likes) ? "%{$request->search['value'][$i]}%" : $request->search['value'][$i];
/* joins have to be in the approved list, or they default to AND */
$joins[] = in_array($request->search['join'][$i], $allowed_joins) ? $request->search['join'][$i] : 'AND';
if (in_array($field, $fields)) {
$terms[] = '`' . sql_real_escape($field, $db->link) . '` ' . $allowed_modes[$request->search['mode'][$i]] . ' \'' . sql_real_escape($value, $db->link) . '\'';
} elseif ($field == '(Search all)') {
/* build a special term that contains all of the available fields with OR joins */
$sub_terms = array();
foreach ($fields as $field) {
$sub_terms[] = '`' . sql_real_escape($field, $db->link) . '` ' . $allowed_modes[$request->search['mode'][$i]] . ' \'' . sql_real_escape($value, $db->link) . '\'';
}
if ($request->search['mode'][$i] == 'LIKE') {
$terms[] = '( ' . implode(' OR ', $sub_terms) . ' )';
} else {
$terms[] = '( ' . implode(' AND ', $sub_terms) . ' )';
}
} else {
/* if the field name isn't present in the fields of the current category(s), bail */
$config->errors[] = "Error: User attempted to search field named '{$field}' which isn't in the current categories.";
continue;
}
}
/* generate the limit based upon the previously provided limit */
$limit = $request->limit > 0 ? "{$request->limit}," . ($request->limit + $config->values['list_max']) : "0,{$config->values['list_max']}";
/* run the query on each category */
foreach ($categories as $category) {
/* check that the order-by field is appropriate for this category */
if (!in_array($request->order['field'], array_keys($result->fields[$category]))) {
$request->order['field'] = 'Identifier';
}
/* convert identifiers to numeric sort */
if ($request->order['field'] == 'Identifier') {
$order = 'CAST(SUBSTR(`Identifier`,6) AS SIGNED) ' . sql_real_escape($request->order['direction'], $db->link);
} else {
$order = "`" . sql_real_escape($request->order['field'], $db->link) . "` " . sql_real_escape($request->order['direction'], $db->link);
}
/* construct the select statement by putting together the field names and joining conjunctions */
$select = '';
foreach ($terms as $i => $term) {
$select .= count($terms) > 1 && $i < count($terms) - 1 ? "{$term} {$joins[$i]} " : $term;
}
/* generate the query */
$query = SLAM_makePermsQuery($config, $db, $user, '*', $category, $select, $order, $limit);
/* execute the query */
if (($result->assets[$category] = $db->getRecords($query)) === false) {
$config->errors[] = 'Database error: Error retrieving search:' . $db->ErrorState() . $query;
return new SLAMresult();
}
/* count the number of assets in the category */
$query = SLAM_makePermsQuery($config, $db, $user, 'COUNT(*)', $category, $select);
if (($count = $db->getRecords($query)) === false) {
$config->errors[] = 'Database error: Error counting assets:' . $db->ErrorState() . $query;
}
$result->counts[$category] = $count[0]['COUNT(*)'];
}
/* associate the retrieved records with their permissions*/
$result->getPermissions($config, $db, $user, $request);
return $result;
}
function SLAM_createNewUser(&$config, $db, $user)
{
if (!$user->superuser) {
return "Only superusers can add a new user.";
}
$username = sql_real_escape($_REQUEST['new_user_name'], $db->link);
$email = sql_real_escape($_REQUEST['new_user_email'], $db->link);
$password = sql_real_escape($_REQUEST['new_user_password'], $db->link);
$projects = sql_real_escape($_REQUEST['new_user_projects'], $db->link);
$auth = $db->GetRecords("SELECT * FROM `{$config->values['user_table']}` WHERE `username`='{$username}' LIMIT 1");
if ($auth === false) {
//GetRecords returns false on error
$config->errors[] = 'Database error: Could not save new password, could not access user table:' . $db->ErrorState();
return;
} elseif (count($auth) > 0) {
return "A user with that username already exists.";
}
$result = $db->Query("INSERT INTO `{$config->values['user_table']}` (`username`,`email`,`projects`) VALUES ('{$username}','{$email}','{$projects}')");
if ($result === false) {
$config->errors[] = 'Database error: Could not create the new user:' . $db->ErrorState();
return "Could not create the user.";
}
if (!SLAM_changeUserPassword($config, $db, $username, $password)) {
return "Created user, but could not set password!";
}
return true;
}
function savePrefs(&$config, $db)
{
$prefs = sql_real_escape(serialize($this->prefs), $db->link);
$q = "UPDATE `{$config->values['user_table']}` SET `prefs`='{$prefs}' WHERE `username`='{$this->username}' LIMIT 1";
if (!$db->Query($q)) {
$config->errors[] = 'Error updating user preferences: ' . $db->ErrorState();
return false;
}
return true;
}
