Пример #1
0
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.');
    }
}