/**
* Insert data from a $_POST of one of our generated forms insert form into the database.
*
* Here is a sample call:
*
* $tablename = "tsugi_lti_key";
* $fields = array("key_key", "key_sha256", "secret", "created_at", "updated_at");
* CrudForm::handleInsert($tablename, $fields);
*
* @param $fields An array of fields to be inserted. These items must be
* in the $_POST data as well.
* @return int Returns the constant for SUCCESS, FAIL, or NONE
*/
public static function handleInsert($tablename, $fields)
{
global $PDOX;
if (isset($_POST['doSave']) && count($_POST) > 0) {
$names = '';
$values = '';
$parms = array();
for ($i = 0; $i < count($fields); $i++) {
$field = $fields[$i];
if (strlen($names) > 0) {
$names .= ', ';
}
if (strlen($values) > 0) {
$values .= ', ';
}
$names .= $field;
if (strpos($field, "_at") > 0) {
$values .= "NOW()";
continue;
}
$key = $field;
if (strpos($field, "_sha256") !== false) {
$key = str_replace("_sha256", "_key", $field);
if (!isset($_POST[$key])) {
$_SESSION['success'] = "Missing POST field: " . $key;
return self::CRUD_FAIL;
}
$value = lti_sha256($_POST[$key]);
} else {
if (isset($_POST[$field])) {
$value = $_POST[$field];
} else {
$_SESSION['success'] = "Missing POST field: " . $field;
return self::CRUD_FAIL;
}
}
$parms[':' . $i] = $value;
$values .= ":" . $i;
}
$sql = "INSERT INTO {$tablename} \n( {$names} ) VALUES ( {$values} )";
$stmt = $PDOX->queryDie($sql, $parms);
$_SESSION['success'] = _m("Record Inserted");
return self::CRUD_SUCCESS;
}
return self::CRUD_NONE;
}
/**
* Make sure that the data in our lti_ tables matches the POST data
*
* This routine compares the POST dat to the data pulled from the
* lti_ tables and goes through carefully INSERTing or UPDATING
* all the nexessary data in the lti_ tables to make sure that
* the lti_ table correctly match all the data from the incoming post.
*
* While this looks like a lot of INSERT and UPDATE statements,
* the INSERT statements only run when we see a new user/course/link
* for the first time and after that, we only update is something
* changes. S0 in a high percentage of launches we are not seeing
* any new or updated data and so this code just falls through and
* does absolutely no SQL.
*/
public static function adjustData($p, &$row, $post)
{
global $PDOX;
$errormode = $PDOX->getAttribute(\PDO::ATTR_ERRMODE);
$PDOX->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
$actions = array();
if ($row['context_id'] === null) {
$sql = "INSERT INTO {$p}lti_context\n ( context_key, context_sha256, settings_url, title, key_id, created_at, updated_at ) VALUES\n ( :context_key, :context_sha256, :settings_url, :title, :key_id, NOW(), NOW() )";
$PDOX->queryDie($sql, array(':context_key' => $post['context_id'], ':context_sha256' => lti_sha256($post['context_id']), ':settings_url' => $post['context_settings_url'], ':title' => $post['context_title'], ':key_id' => $row['key_id']));
$row['context_id'] = $PDOX->lastInsertId();
$row['context_title'] = $post['context_title'];
$row['context_settings_url'] = $post['context_settings_url'];
$actions[] = "=== Inserted context id=" . $row['context_id'] . " " . $row['context_title'];
}
if ($row['link_id'] === null && isset($post['link_id'])) {
$sql = "INSERT INTO {$p}lti_link\n ( link_key, link_sha256, settings_url, title, context_id, created_at, updated_at ) VALUES\n ( :link_key, :link_sha256, :settings_url, :title, :context_id, NOW(), NOW() )";
$PDOX->queryDie($sql, array(':link_key' => $post['link_id'], ':link_sha256' => lti_sha256($post['link_id']), ':settings_url' => $post['link_settings_url'], ':title' => $post['link_title'], ':context_id' => $row['context_id']));
$row['link_id'] = $PDOX->lastInsertId();
$row['link_title'] = $post['link_title'];
$row['link_settings_url'] = $post['link_settings_url'];
$actions[] = "=== Inserted link id=" . $row['link_id'] . " " . $row['link_title'];
}
$user_displayname = isset($post['user_displayname']) ? $post['user_displayname'] : null;
$user_email = isset($post['user_email']) ? $post['user_email'] : null;
if ($row['user_id'] === null && isset($post['user_id'])) {
$sql = "INSERT INTO {$p}lti_user\n ( user_key, user_sha256, displayname, email, key_id, created_at, updated_at ) VALUES\n ( :user_key, :user_sha256, :displayname, :email, :key_id, NOW(), NOW() )";
$PDOX->queryDie($sql, array(':user_key' => $post['user_id'], ':user_sha256' => lti_sha256($post['user_id']), ':displayname' => $user_displayname, ':email' => $user_email, ':key_id' => $row['key_id']));
$row['user_id'] = $PDOX->lastInsertId();
$row['user_email'] = $user_email;
$row['user_displayname'] = $user_displayname;
$row['user_key'] = $post['user_id'];
$actions[] = "=== Inserted user id=" . $row['user_id'] . " " . $row['user_email'];
}
if ($row['membership_id'] === null && $row['context_id'] !== null && $row['user_id'] !== null) {
$sql = "INSERT INTO {$p}lti_membership\n ( context_id, user_id, role, created_at, updated_at ) VALUES\n ( :context_id, :user_id, :role, NOW(), NOW() )";
$PDOX->queryDie($sql, array(':context_id' => $row['context_id'], ':user_id' => $row['user_id'], ':role' => $post['role']));
$row['membership_id'] = $PDOX->lastInsertId();
$row['role'] = $post['role'];
$actions[] = "=== Inserted membership id=" . $row['membership_id'] . " role=" . $row['role'] . " user="******" context=" . $row['context_id'];
}
if (isset($post['service'])) {
// We need to handle the case where the service URL changes but we already have a sourcedid
// This is for LTI 1.x only as service is not used for LTI 2.x
$oldserviceid = $row['service_id'];
if ($row['service_id'] === null && $post['service']) {
$sql = "INSERT INTO {$p}lti_service\n ( service_key, service_sha256, key_id, created_at, updated_at ) VALUES\n ( :service_key, :service_sha256, :key_id, NOW(), NOW() )";
$PDOX->queryDie($sql, array(':service_key' => $post['service'], ':service_sha256' => lti_sha256($post['service']), ':key_id' => $row['key_id']));
$row['service_id'] = $PDOX->lastInsertId();
$row['service'] = $post['service'];
$actions[] = "=== Inserted service id=" . $row['service_id'] . " " . $post['service'];
}
// If we just created a new service entry but we already had a result entry, update it
// This is for LTI 1.x only as service is not used for LTI 2.x
if ($oldserviceid === null && $row['result_id'] !== null && $row['service_id'] !== null && $post['service']) {
$sql = "UPDATE {$p}lti_result SET service_id = :service_id WHERE result_id = :result_id";
$PDOX->queryDie($sql, array(':service_id' => $row['service_id'], ':result_id' => $row['result_id']));
$actions[] = "=== Updated result id=" . $row['result_id'] . " service=" . $row['service_id'];
}
}
// We always insert a result row if we have a link - we will store
// grades locally in this row - even if we cannot send grades
if ($row['result_id'] === null && $row['link_id'] !== null && $row['user_id'] !== null) {
$sql = "INSERT INTO {$p}lti_result\n ( link_id, user_id, created_at, updated_at ) VALUES\n ( :link_id, :user_id, NOW(), NOW() )";
$PDOX->queryDie($sql, array(':link_id' => $row['link_id'], ':user_id' => $row['user_id']));
$row['result_id'] = $PDOX->lastInsertId();
$actions[] = "=== Inserted result id=" . $row['result_id'];
}
// Set these values to null if they were not in the post
if (!isset($post['sourcedid'])) {
$post['sourcedid'] = null;
}
if (!isset($post['service'])) {
$post['service'] = null;
}
if (!isset($row['service'])) {
$row['service'] = null;
}
if (!isset($post['result_url'])) {
$post['result_url'] = null;
}
// Here we handle updates to sourcedid or result_url including if we
// just inserted the result row
if ($row['result_id'] != null && ($post['sourcedid'] != $row['sourcedid'] || $post['result_url'] != $row['result_url'] || $post['service'] != $row['service'])) {
$sql = "UPDATE {$p}lti_result\n SET sourcedid = :sourcedid, result_url = :result_url, service_id = :service_id\n WHERE result_id = :result_id";
$PDOX->queryDie($sql, array(':result_url' => $post['result_url'], ':sourcedid' => $post['sourcedid'], ':service_id' => $row['service_id'], ':result_id' => $row['result_id']));
$row['sourcedid'] = $post['sourcedid'];
$row['service'] = $post['service'];
$row['result_url'] = $post['result_url'];
$actions[] = "=== Updated result id=" . $row['result_id'] . " result_url=" . $row['result_url'] . " sourcedid=" . $row['sourcedid'] . " service_id=" . $row['service_id'];
}
// Here we handle updates to context_title, link_title, user_displayname, user_email, or role
if (isset($post['context_title']) && $post['context_title'] != $row['context_title']) {
$sql = "UPDATE {$p}lti_context SET title = :title WHERE context_id = :context_id";
$PDOX->queryDie($sql, array(':title' => $post['context_title'], ':context_id' => $row['context_id']));
$row['context_title'] = $post['context_title'];
$actions[] = "=== Updated context=" . $row['context_id'] . " title=" . $post['context_title'];
}
if (isset($post['link_title']) && $post['link_title'] != $row['link_title']) {
$sql = "UPDATE {$p}lti_link SET title = :title WHERE link_id = :link_id";
$PDOX->queryDie($sql, array(':title' => $post['link_title'], ':link_id' => $row['link_id']));
$row['link_title'] = $post['link_title'];
$actions[] = "=== Updated link=" . $row['link_id'] . " title=" . $post['link_title'];
}
if (isset($post['user_displayname']) && $post['user_displayname'] != $row['user_displayname'] && strlen($post['user_displayname']) > 0) {
$sql = "UPDATE {$p}lti_user SET displayname = :displayname WHERE user_id = :user_id";
$PDOX->queryDie($sql, array(':displayname' => $post['user_displayname'], ':user_id' => $row['user_id']));
$row['user_displayname'] = $post['user_displayname'];
$actions[] = "=== Updated user="******" displayname=" . $post['user_displayname'];
}
if (isset($post['user_email']) && $post['user_email'] != $row['user_email'] && strlen($post['user_email']) > 0) {
$sql = "UPDATE {$p}lti_user SET email = :email WHERE user_id = :user_id";
$PDOX->queryDie($sql, array(':email' => $post['user_email'], ':user_id' => $row['user_id']));
$row['user_email'] = $post['user_email'];
$actions[] = "=== Updated user="******" email=" . $post['user_email'];
}
if (isset($post['role']) && $post['role'] != $row['role']) {
$sql = "UPDATE {$p}lti_membership SET role = :role WHERE membership_id = :membership_id";
$PDOX->queryDie($sql, array(':role' => $post['role'], ':membership_id' => $row['membership_id']));
$row['role'] = $post['role'];
$actions[] = "=== Updated membership=" . $row['membership_id'] . " role=" . $post['role'];
}
// Restore ERRMODE
$PDOX->setAttribute(\PDO::ATTR_ERRMODE, $errormode);
return $actions;
}
// these with user_id and do not let a second TC slip in and take over
// an existing key. So the next few lines of code are really critical.
// And we can neither use INSERT / UPDATE because we cannot add the user_id
// to the unique constraint.
if ($re_register) {
$key_sha256 = lti_sha256($oauth_consumer_key);
$retval = $PDOX->queryDie("UPDATE {$CFG->dbprefix}lti_key SET updated_at = NOW(), ack = :ACK,\n new_secret = :SECRET, new_consumer_profile = :PROFILE\n WHERE key_sha256 = :SHA and user_id = :UID", array(":SECRET" => $shared_secret, ":PROFILE" => $tc_profile_json, ":UID" => $_SESSION['id'], ":SHA" => $key_sha256, ":ACK" => $ack));
if (!$retval->success) {
log_return_die("Unable to UPDATE Registration key {$oauth_consumer_key} " . $retval->errorImplode);
}
$return_url_lti_message = "LTI2 Key {$oauth_consumer_key} updated";
// If we do not have a key, insert one, checking carefully for a failed insert
// due to a unique constraint violation. If this insert fails, it is likely
// a race condition between competing INSERTs for the same key_id
} else {
$key_sha256 = lti_sha256($oauth_consumer_key);
$retval = $PDOX->queryDie("INSERT INTO {$CFG->dbprefix}lti_key \n (key_sha256, key_key, user_id, secret, consumer_profile)\n VALUES\n (:SHA, :KEY, :UID, :SECRET, :PROFILE)\n ON DUPLICATE KEY\n UPDATE secret = :SECRET, consumer_profile = :PROFILE\n ", array(":SHA" => $key_sha256, ":KEY" => $oauth_consumer_key, ":UID" => $_SESSION['id'], ":SECRET" => $shared_secret, ":PROFILE" => $tc_profile_json));
if (!$retval->success) {
log_return_die("Unable to INSERT Registration key {$oauth_consumer_key} " . $retval->errorImplode);
}
$return_url_lti_message = "LTI2 Key {$oauth_consumer_key} inserted";
}
echo_log("{$return_url_lti_message} \n");
if ($last_http_response == 201 || $last_http_response == 200) {
if (strpos($launch_presentation_return_url, '?') > 0) {
$launch_presentation_return_url .= '&';
} else {
$launch_presentation_return_url .= '?';
}
$launch_presentation_return_url .= "status=success";
$launch_presentation_return_url .= "<i_message=" . urlencode($return_url_lti_message);
$doLogin = true;
}
}
}
} catch (ErrorException $e) {
$errormsg = $e->getMessage();
}
}
if ($doLogin) {
if ($firstName === false || $lastName === false || $userEmail === false) {
error_log('Google-Missing:' . $identity . ',' . $firstName . ',' . $lastName . ',' . $userEmail);
$_SESSION["error"] = "You do not have a first name, last name, and email in Google or you did not share it with us.";
header('Location: index.php');
return;
} else {
$userSHA = lti_sha256($identity);
$displayName = $firstName . ' ' . $lastName;
// Load the profile checking to see if everything
$stmt = $PDOX->queryDie("SELECT P.profile_id AS profile_id, P.displayname AS displayname,\n P.email as email, U.user_id as user_id\n FROM {$CFG->dbprefix}profile AS P\n LEFT JOIN {$CFG->dbprefix}lti_user AS U\n ON P.profile_id = U.profile_id AND P.email = U.email AND\n P.displayname = U.displayname AND user_sha256 = profile_sha256 AND\n P.key_id = U.key_id\n WHERE profile_sha256 = :SHA AND P.key_id = :ID LIMIT 1", array('SHA' => $userSHA, ":ID" => $google_key_id));
$profile_row = $stmt->fetch(PDO::FETCH_ASSOC);
$profile_id = 0;
$user_id = 0;
// Make sure we have a profile for this person
if ($profile_row === false) {
$stmt = $PDOX->queryDie("INSERT INTO {$CFG->dbprefix}profile\n (profile_sha256, profile_key, key_id, email, displayname, created_at, updated_at, login_at) " . "VALUES ( :SHA, :UKEY, :KEY, :EMAIL, :DN, NOW(), NOW(), NOW() )", array('SHA' => $userSHA, ':UKEY' => $identity, ':KEY' => $google_key_id, ':EMAIL' => $userEmail, ':DN' => $displayName));
if ($stmt->success) {
$profile_id = $PDOX->lastInsertId();
}
error_log('Profile-Insert:' . $identity . ',' . $displayName . ',' . $userEmail . ',' . $profile_id);
} else {
$profile_id = $profile_row['profile_id'] + 0;
$address = isset($_GET['address']) ? $_GET['address'] : false;
header('Content-Type: application/json; charset=utf-8');
if ($address === false) {
sort($LOCATIONS);
echo jsonIndent(json_encode($LOCATIONS));
return;
}
$where = array_search($address, $LOCATIONS);
if ($where === false) {
http_response_code(400);
$retval = array('error' => 'Address not found in the list of available locations', 'locations' => $LOCATIONS);
echo jsonIndent(json_encode($retval));
return;
}
// Check to see if we already have this in the cache
$address_sha256 = lti_sha256($address);
// echo("address=$address address_sha256=$address_sha256\n");
$row = $PDOX->rowDie("SELECT json_content, updated_at, NOW() as now \n FROM {$p}pydata_geo WHERE geo_sha256 = :AD", array(':AD' => $address_sha256));
$json_content = false;
$updated_at = false;
if ($row !== false && strlen($row['json_content']) > 0) {
$now_str = $row['now'];
$now = strtotime($now_str);
$updated_at = $row['updated_at'];
$updated_time = strtotime($updated_at);
$datediff = $now - $updated_time;
$json_content = json_decode($row['json_content']);
if ($json_content == null) {
error_log("JSON error in cache for {$address}: " . json_last_error_msg());
}
if ($json_content != null && $datediff < $expire_seconds) {
