function doCartoSqlApiPush($get)
{
global $cartodb_username, $cartodb_api_key, $db, $udb, $login_status;
// error_reporting(E_ALL);
// ini_set('display_errors', 1);
// error_log('Login is running in debug mode!');
$sqlQuery = decode64($get['sql_query'], true);
if (empty($sqlQuery)) {
$sqlQuery = base64_decode(urldecode($get["sql_query"]));
}
$originalQuery = $sqlQuery;
# If it's a "SELECT" style statement, make sure the accessing user
# has permissions to read this dataset
$searchSql = strtolower($sqlQuery);
$queryPattern = '/(?i)([a-zA-Z]+(?: +INTO)?) +.*(?:FROM)?[ `]*(t[0-9a-f]{10,}[_]?[0-9a-f]*)[ `]*.*[;]?/m';
$statements = explode(');', $sqlQuery);
$checkedTablePermissions = array();
$pidList = array();
foreach ($statements as $k => $statement) {
if (empty($statement)) {
unset($statements[$k]);
continue;
}
$sqlAction = preg_replace($queryPattern, '$1', $statement);
$sqlAction = strtolower(str_replace(" ", "", $sqlAction));
$restrictedActions = array("select" => "READ", "delete" => "EDIT", "insert" => "EDIT", "insertinto" => "EDIT", "update" => "EDIT");
$unrestrictedActions = array("create" => true);
# Looking up the columns is a safe action
if (preg_match('/\\A(?i)SELECT +\\* +(?:FROM)?[ `]*(t[0-9a-f]{10,}[_]?[0-9a-f]*)[ `]* +(WHERE FALSE)[;]?\\Z/m', $statement)) {
# Successful match
unset($restrictedActions["select"]);
$unrestrictedActions["select"] = true;
}
if (isset($restrictedActions[$sqlAction])) {
# Check the user
# If bad, kick the access out
$cartoTable = preg_replace($queryPattern, '$2', $statement);
$checkedTablePermissions[] = $cartoTable;
$cartoTableJson = str_replace('_', '_', $cartoTable);
$accessListLookupQuery = 'SELECT `project_id`, `author`, `access_data`, `public` FROM `' . $db->getTable() . "` WHERE `carto_id` LIKE '%" . $cartoTableJson . "%' OR `carto_id` LIKE '%" . $cartoTable . "%'";
$l = $db->openDB();
$r = mysqli_query($l, $accessListLookupQuery);
$row = mysqli_fetch_assoc($r);
$pid = $row["project_id"];
$pidList[$cartoTable] = $pid;
# Non-existant projects are fair game
if (!empty($pid)) {
$requestedPermission = $restrictedActions[$sqlAction];
$pArr = explode(",", $row["access_data"]);
$permissions = array();
foreach ($pArr as $access) {
$up = explode(":", $access);
$permissions[$up[0]] = $up[1];
}
# End loop
$csvString = preg_replace('/(:(EDIT|READ))/m', '', $row['access_data']);
$users = explode(',', $csvString);
$users[] = $row['author'];
$isPublic = boolstr($row['public']);
$suFlag = $login_status['detail']['userdata']['su_flag'];
$isSu = boolstr($suFlag);
# Get current user ID
if ($login_status['status'] !== true && !$isPublic) {
$response = array('status' => false, 'error' => 'NOT_LOGGED_IN', 'human_error' => 'Attempted to access private project without being logged in', 'args_provided' => $get, "project_id" => $pid, "query_type" => $sqlAction, 'is_public_dataset' => $isPublic, "statement_parsed" => $statement);
returnAjax($response);
}
# End login check
$uid = $login_status['detail']['uid'];
if (!in_array($uid, $users) && !$isPublic && $isSu !== true) {
$response = array('status' => false, 'error' => 'UNAUTHORIZED_USER', 'human_error' => "User {$uid} isn't authorized to access this dataset", 'args_provided' => $get, "project_id" => $pid, 'is_public_dataset' => $isPublic, "statement_parsed" => $statement);
returnAjax($response);
}
# End authorized user check
if ($requestedPermission == "EDIT") {
# Editing has an extra filter
$hasPermission = $permissions[$uid];
if ($hasPermission !== $requestedPermission && $isSu !== true) {
$response = array('status' => false, 'error' => 'UNAUTHORIZED_USER', 'human_error' => "User '{$uid}' isn't authorized to edit this dataset", 'args_provided' => $get, "project_id" => $pid, "query_type" => $sqlAction, "user_permissions" => $hasPermission, "statement_parsed" => $statement);
returnAjax($response);
}
# End edit permission check
}
# End edit permission case
}
# End project existence check
} else {
if (!isset($unrestrictedActions[$sqlAction])) {
# Unrecognized query type
returnAjax(array("status" => false, "error" => "UNAUTHORIZED_QUERY_TYPE", "query_type" => $sqlAction, "args_provided" => $get, "statement_parsed" => $statement));
}
}
if (empty($statement)) {
returnAjax(array('status' => false, 'error' => 'Invalid Query', 'args_provided' => $get));
}
}
$cartoPostUrl = 'https://' . $cartodb_username . '.cartodb.com/api/v2/sql';
$cartoArgSuffix = '&api_key=' . $cartodb_api_key;
$l = sizeof($statements);
$lastIndex = $l - 1;
foreach ($statements as $k => $statement) {
# Re-append the closing parens
if (substr_count($statement, "(") === substr_count($statement, ")") + 1) {
$statements[$k] = $statement . ")";
}
}
$responses = array();
$parsed_responses = array();
$urls = array();
ini_set('allow_url_fopen', true);
try {
set_time_limit(0);
} catch (Exception $e) {
$length = 30 * sizeof($statements);
set_time_limit($length);
}
if (!boolstr($get['blobby'])) {
foreach ($statements as $statement) {
$statement = trim($statement);
if (empty($statement)) {
continue;
}
$cartoArgs = 'q=' . urlencode($statement) . $cartoArgSuffix;
# Fetch a table
$cartoTable = preg_replace($queryPattern, '$2', $statement);
$sqlAction = preg_replace($queryPattern, '$1', $statement);
$cartoFullUrl = $cartoPostUrl . '?' . $cartoArgs;
$urls[] = $cartoFullUrl;
if (boolstr($get['alt'])) {
$responses[] = json_decode(do_post_request($cartoPostUrl, $cartoArgs, 'GET'), true);
} else {
# Default
$opts = array('http' => array('method' => 'GET', 'ignore_errors' => true, 'timeout' => 3.5));
$context = stream_context_create($opts);
$response = file_get_contents($cartoFullUrl, false, $context);
$responses[] = $response;
$decoded = json_decode($response, true);
$decoded["query"] = $statement;
$decoded["encoded_query"] = urlencode($statement);
$decoded["table"] = $cartoTable;
$decoded["project_id"] = $pidList[$cartoTable];
$decoded["blobby"] = false;
$decoded["sql_action"] = $sqlAction;
$parsed_responses[] = $decoded;
}
}
} else {
$cartoArgs = 'q=' . $sqlQuery . $cartoArgSuffix;
$cartoFullUrl = $cartoPostUrl . '?' . $cartoArgs;
$opts = array('http' => array('method' => 'GET', 'request_fulluri' => true, 'timeout' => 3.5));
$context = stream_context_create($opts);
$response = file_get_contents($cartoFullUrl, false, $context);
$responses[] = $response;
$decoded = json_decode($response, true);
$decoded["query"] = $sqlQuery;
$decoded["blobby"] = true;
$parsed_responses[] = $decoded;
}
try {
$response = array('status' => true, 'sql_statements' => $statements, 'post_response' => $responses, 'parsed_responses' => $parsed_responses, 'blobby' => boolstr($get['blobby']), "query_type" => $sqlAction, "parsed_query" => $originalQuery, "checked_tables" => $checkedTablePermissions);
if (boolstr($get['blobby'])) {
$response["project_id"] = $pid;
$response["query_type"] = $sqlAction;
}
returnAjax($response);
} catch (Exception $e) {
returnAjax(array('status' => false, 'error' => $e->getMessage(), 'human_error' => 'There was a problem uploading to the CartoDB server.', 'blobby' => boolstr($get['blobby']), "query_type" => $sqlAction, "project_id" => $pid));
}
}
function getFromUser($get)
{
$conf = $get['hash'];
$s = $get['secret'];
$id = $get['dblink'];
if (!empty($conf) && !empty($s) && !empty($id) && !empty($get['col'])) {
$u = new UserFunctions();
if ($u->validateUser($id, $conf, $s)) {
require_once dirname(__FILE__) . '/CONFIG.php';
global $default_user_database, $default_user_table;
$col = decode64($get['col']);
$l = $u->openDB($default_user_database);
$query = "SELECT {$col} FROM `{$default_user_table}` WHERE dblink='{$id}'";
$r = mysqli_query($l, $query);
$row = mysqli_fetch_row($r);
return array('status' => true, 'data' => deescape($row[0]), 'col' => $col, 'id' => $id);
} else {
return array('status' => false, 'error' => 'Invalid user');
}
}
return array('status' => false, 'error' => 'One or more required fields were left blank');
}
function validateDataset($dataPath, $projectLink, $fimsAuthCookiesAsString = null, $continue = false)
{
try {
$fimsValidateUrl = 'http://www.biscicol.org/biocode-fims/rest/validate';
# See
# http://biscicol.org/biocode-fims/rest/fims.wadl#idp1379817744
# https://fims.readthedocs.org/en/latest/amphibian_disease_example.html#validate-dataset
if ($continue == true) {
$fimsStatusUrl = $fimsValidateUrl . '/status';
$fimsContinueUrl = $fimsValidateUrl . '/continue';
$params = array('http' => array('method' => 'GET', 'header' => implode("\r\n", array('Content-type: application/x-www-form-urlencoded', 'Accept: application/json', 'User-Agent: amphibian disease portal')) . "\r\n"));
$params['http']['header'] .= "Cookie: " . $cookiesString . "\r\n";
$ctx = stream_context_create($params);
$rawResponse = file_get_contents($fimsStatusUrl, false, $ctx);
if ($rawResponse === false) {
throw new Exception("Fatal FIMS communication error 007 (No Response)");
}
$rawResponse2 = file_get_contents($fimsContinueUrl, false, $ctx);
$resp = json_decode($rawResponse, true);
$resp2 = json_decode($rawResponse2, true);
return array('status' => true, 'responses' => array('status' => $resp, 'continue' => $resp2), 'cookies' => $cookiesString);
}
# $data = smart_decode64($dataset, false);
$datasrc = decode64($dataPath);
$file = realpath($datasrc);
if (!file_exists($file)) {
return array('status' => false, 'error' => 'INVALID_FILE_PATH', 'human_error' => "Sorry, we couldn't validate your uploaded file", 'provided' => array('path' => $datasrc, 'computed_path' => $file));
}
$finfo = finfo_open(FILEINFO_MIME_TYPE);
$mime = finfo_file($finfo, $file);
finfo_close($finfo);
if (empty($mime) || $mime == 'application/zip') {
# Just the fallback that is based purely on extension
# Only used when finfo can't find a mime type
try {
include_once dirname(__FILE__) . '/helpers/js-dragdrop/manual_mime.php';
$mime = mime_type($file);
} catch (Exception $e) {
$mime_error = $e->getMessage();
$mime = null;
}
}
# https://secure.php.net/manual/en/function.curl-file-create.php
$dataUploadObj = curl_file_create($file, $mime);
# Remove the invalid "fims_extra" data
// foreach($data as $k=>$row) {
// unset($row["fimsExtra"]);
// $data[$k] = $row;
// }
# The POST object
$fimsValidateData = array('dataset' => $dataUploadObj, 'projectId' => 26, 'expeditionCode' => $projectLink);
# Login
if (empty($fimsAuthCookiesAsString)) {
global $fimsPassword;
$fimsPassCredential = $fimsPassword;
$fimsUserCredential = 'amphibiaweb';
# AmphibianDisease
$fimsAuthUrl = 'http://www.biscicol.org/biocode-fims/rest/authenticationService/login';
$fimsAuthData = array('username' => $fimsUserCredential, 'password' => $fimsPassCredential);
# Post the login
$params = array('http' => array('method' => 'POST', 'content' => http_build_query($fimsAuthData), 'header' => implode("\r\n", array('Content-type: application/x-www-form-urlencoded', 'Accept: application/json', 'User-Agent: amphibian disease portal')) . "\r\n"));
$ctx = stream_context_create($params);
$rawResponse = file_get_contents($fimsAuthUrl, false, $ctx);
if ($rawResponse === false) {
throw new Exception("Fatal FIMS communication error 008 (No Response)");
}
$loginHeaders = $http_response_header;
$cookies = array();
$cookiesString = '';
foreach ($http_response_header as $hdr) {
if (preg_match('/^Set-Cookie:\\s*([^;]+)/', $hdr, $matches)) {
$cookiesString .= $matches[1] . ';';
parse_str($matches[1], $tmp);
$cookies += $tmp;
}
}
$loginResponse = json_decode($rawResponse, true);
if (empty($loginResponse['url'])) {
throw new Exception('Invalid Login Response E004');
}
} else {
$loginResponse = 'NO_LOGIN_CREDENTIALS_PROVIDED';
$cookiesString = $fimsAuthCookiesAsString;
$params = array('http' => array('method' => 'POST'));
}
# Post the args
$headers = array();
$headers[] = 'Content-type: multipart/form-data';
$headers[] = 'Accept: application/json';
$headers[] = 'User-Agent: amphibian disease portal';
$params = array('http' => array('method' => 'POST', 'header' => $headers, 'content' => http_build_query($fimsValidateData)));
# https://fims.readthedocs.org/en/latest/amphibian_disease_example.html#validate-dataset
$ch = curl_init($fimsValidateUrl);
curl_setopt($ch, CURLOPT_POST, 1);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_SAFE_UPLOAD, false);
// required as of PHP 5.6.0
# Also auto sets header to "multipart/form-data"
# Must be an array for file uploads
curl_setopt($ch, CURLOPT_POSTFIELDS, $fimsValidateData);
curl_setopt($ch, CURLOPT_FOLLOWLOCATION, 1);
curl_setopt($ch, CURLOPT_COOKIE, $cookiesString);
curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);
#curl_setopt($ch, CURLOPT_USERAGENT, "amphibian disease portal");
#curl_setopt( $ch, CURLOPT_HEADER, 1);
$rawResponse = curl_exec($ch);
curl_close($ch);
$resp = json_decode($rawResponse, true);
$status = true;
$validateStatus = true;
# Check the response for errors
try {
if (isset($resp['done']['worksheets'][0]['Samples'])) {
$hasError = !empty($resp['done']['worksheets'][0]['Samples']['errors']);
$hasWarning = !empty($resp['done']['worksheets'][0]['Samples']['warnings']);
} else {
$hasError = false;
$hasWarning = false;
}
} catch (Exception $e) {
$hasError = false;
$hasWarning = false;
}
if (empty($resp) || !isset($resp['done']['worksheets'][0])) {
$validateStatus = 'FIMS_SERVER_DOWN';
} elseif ($hasError) {
$mainError = $resp['done']['worksheets'][0]['Samples']['errors'][0];
$meK = key($mainError);
$errorMessage = $meK . ': ' . $mainError[$meK][0];
if (!empty($mainError[0][0])) {
$errorMessage .= $mainError[0][0];
}
$validateStatus = array('status' => false, 'error' => $errorMessage, 'main_error' => $mainError, 'errors' => $resp['done']['worksheets'][0]['Samples']['errors'], 'warnings' => $resp['done']['worksheets'][0]['Samples']['warnings']);
}
# Make the response
$response = array('status' => $status, 'validate_status' => $validateStatus, 'responses' => array('login_response' => array('response' => $loginResponse, 'cookies' => $cookiesString), 'validate_response' => $resp, 'raw_response' => $rawResponse, 'validate_has_error' => $hasError), 'post_params' => array('file_sent' => $dataUploadObj, 'header_params' => $params), 'data' => array('data_sent' => $fimsValidateData, 'data_mime' => array('mime' => $mime, 'mime_error' => $mime_error)));
return $response;
} catch (Exception $e) {
return array('status' => true, 'validate_status' => 'FIMS_SERVER_DOWN', 'error' => $e->getMessage(), 'human_error' => 'There was a problem communicating with the FIMS project. Please try again later.');
}
}