Canvas enrollments.csv and add_sis_stickiness

I have an enrollments.csv file for Instructure’s Canvas LMS, and I want all of the enrollments in it to “stick”–that is, to survive a batch mode SIS import. These are primarily course designers, and so they have no official standing in the class–and therefore are not in our database, and therefore are not included with regular updates to enrollments.

According to the Canvas documentation for SIS imports:

add_sis_stickiness – Boolean

This option, if present, will process all changes as if they were UI changes. This means that “stickiness” will be added to changed fields. This option is only processed if ‘override_sis_stickiness’ is also provided.

Source: https://canvas.instructure.com/doc/api/sis_imports.html#method.sis_imports_api.create

However, experience tells me otherwise. An inquiry to Instructure’s support confirms that add_sis_stickiness does not apply to enrollments. Enrollments added this way will be deleted following the next enrollments batch import.

The choices to preserve these course designer enrollments are basically to add each one manually using the web UI, or add them via the API. Either option will make the enrollments “stick.”

I opted to use the API. Since I already had a formatted input file, I wrote a short BASH script (with the help of several man pages and a couple StackOverflow pages) that reads the CSV and processes each row, adding the enrollment via the API:

headerrow=1
while read row; do
    if [ $headerrow -eq 0 ]
    then
        # get the SIS course ID
        cid="$(echo $row | cut -d',' -f1)"
        # get the SIS user ID
        uid="$(echo $row | cut -d',' -f2)"
        # get the role / enrollment type
        type="$(echo $row | cut -d',' -f3)"
        # reformat the enrollment type
        tid="$(echo $type | cut -c1 | tr [[:lower:]] [[:upper:]])""$(echo $type | cut -c2-)"Enrollment
        echo course is $cid
        echo user is $uid
        echo type is $tid
        result="$(curl https://[yourcanvassite].instructure.com/api/v1/courses/sis_course_id:$cid/enrollments -H 'Authorization: Bearer [REDACTED]' -X POST -F enrollment[type]=$tid -F enrollment[user_id]=sis_user_id:$uid -F 'enrollment[enrollment_state]=active' -F 'enrollment[notify]=false')"
        echo $result
    fi
    headerrow=0
done <enrollments.csv

MySQL date_add and date_sub functions running against millions of rows

One of my servers runs a query once a week to remove all rows from a Syslog table (>20,000,000 rows) in a MySQL database that are older than 60 days. This was running terribly slowly and interfering with other tasks on the server.

Although the original query used a DELETE statement I’ve used SELECT statements in the examples below.

SELECT COUNT(*)
FROM SystemEvents
WHERE ReceivedAt < DATE_SUB(NOW(), INTERVAL 60 DAY);

That selects about 900,000 rows and takes about 45 seconds.

SELECT COUNT(*)
FROM SystemEvents
WHERE ReceivedAt < DATE_ADD(CURRENT_DATE, INTERVAL -60 DAY);

Likewise takes about 48 seconds.

Is MySQL running a function every time it makes a comparison? I decided to try using a hard-coded date to find out:

SELECT COUNT(*)
FROM SystemEvents
WHERE ReceivedAt < '2015-11-12 12:00:00';

6 seconds! Much faster.

I created a user-defined variable:
SET @sixty_days_ago = DATE_SUB(NOW(), INTERVAL 60 DAY);

Then ran the query:
SELECT COUNT(*)
FROM SystemEvents
WHERE ReceivedAt < @sixty_days_ago;

12 seconds. No 6 seconds, but still a fraction of the original time!

Yammer Enterprise Block Users appears to be limited to 70 users per submission

This is just based on trial-and-error testing, but when I submit over 70 e-mail addresses at:

https://www.yammer.com/[my domain]/admin/blocked_email_addresses

I get one of the following errors:

  • Nginx 502 Bad Gateway
  • We’re sorry, but something went wrong.
    We’ve been notified about this issue and we’ll take a look at it shortly.

When I submit 70 addresses or fewer, it works as expected.

I have not yet discovered a way to block addresses via PowerShell.

Error retrieving Glacier vault inventory via AWS CLI: Unknown options: inventory-retrieval}’

Amazon’s Glacier service is great, but low-cost storage has other costs. For example, you might have a vault but not know what archives it contains. You can retrieve the vault inventory–essentially listing the contents of a directory–but like any Glacier retrieval, it may take several hours.

Using Downloading Vault Inventory using the REST API, I put this together:
C:\>aws glacier initiate-job --account-id - --vault-name my_poorly_named_vault --job-parameters '{"Type": "inventory-retrieval"}'

Which returned:

Unknown options: inventory-retrieval}'

I had taken the command directly from AWS’s example:
http://docs.aws.amazon.com/cli/latest/reference/glacier/initiate-job.html.

(Keep in mind that I had already followed the steps at Installing the Amazon Web Services Command Line Interface and Configuring the Amazon Web Services Command Line Interface.)

According to the documentation for the job-parameters option (http://docs.aws.amazon.com/sdkforruby/api/Aws/Glacier/Types/JobParameters.html#type-instance_method),
valid values are “archive-retrieval” and “inventory-retrieval”.

But the error message says inventory-retrieval}’. Why is it picking up the trailing curly brace and the apostrophe?

I formatted the job-parameters JSON in a file named aws-json.txt, with the curly braces on separate lines:

{
    "Type": "inventory-retrieval"
}

I tried this variation on the initiate-job command:
C:\>aws glacier initiate-job --account-id - --vault-name my_poorly_named_vault --job-parameters file://aws-json.txt

That worked!

The results returned:

{
    "jobId": "y8ugyoNzzusaf6Lv72G3hsjAA6O7nw5bJQ2u6J9TDnJ82_qx-lxnqrhSxIcGvOU1iiXoUhZboiojxsDu8gLQOfiJ7hR2",
    "location": "/123456789011/vaults/my_poorly_named_vault/jobs/y8ugyoNzzusaf6Lv72G3hsjAA6O7nw5bJQ2u6J9TDnJ82_qx-lxnqrhSxIcGvOU1iiXoUhZboiojxsDu8gLQOfiJ7hR2"
}

ORA-00904: “WM_CONCAT”: invalid identifier

I’ve never run into wm_concat before when using Oracle, but I saw it in a statement similar to this earlier today:

SELECT WM_CONCAT(id)
FROM chris.item
WHERE item_type LIKE '%floppy%';

Of course, it didn’t work:

ORA-00904: "WM_CONCAT": invalid identifier

It looks like WM_CONCAT isn’t a supported command (Error when using wm_concat function), and possibly the code I was debugging stopped working when the database was upgraded to 12c.

WM_CONCAT is intended to return the results as a comma-separated list. A strange idea, but it this particular case it almost made sense. A supported replacement that aggregates results into a single string is LISTAGG.

SELECT LISTAGG(id, ',')
WITHIN GROUP (ORDER BY id)
FROM chris.item
WHERE item_type LIKE '%floppy%'
GROUP BY 1;

In this case, I did not want the results separated into groups, so I used GROUP BY 1 to put all results in the same group.

There are a number of other string aggregation techniques for Oracle, if you ever find yourself with such a need.

Connecting to Oracle instance in AWS RDS

I inherited a development Oracle instance in Amazon Web Services’s Relational Database Service (AWS RDS) from a former colleague. I wanted to know what was in the database, and for that, I had to connect to it. I decided the most straightforward way would be with the simplest tool: SQL*Plus.
Continue reading Connecting to Oracle instance in AWS RDS

Holding messages in the Postfix mail queue

Earlier today, someone sent a large number of email messages each containing a 30 megabyte attachment to users on our servers. This put our Postfix servers under a heavy load and caused some messages to be delivered after a substantial delay. (This was in part due to additional processing done by our servers, I’m sure a plain-jane Postfix instance could have handled it without an issue.)

This was no good. The sender–let’s call it bigbulk.test.com–should be able to send such messages, but not at the expense of normal mail delivery. I needed to change the priority of those messages to let other messages take priority.

The first thing I did was to hold all the mail from bigbulk.test.com:

  • Retrieve the mail queue
  • Select only the lines containing bigbulk.test.com
  • Select only the queue ID, the first item listed in each result
  • Pass the queue IDs to the postsuper -h command

mailq | grep bigbulk.test.com | cut -d ' ' -f 1 | xargs -n1 postsuper -h

But what about delivering them? I sent them in small batches so as not to overload the server again.

  • Retrieve the mail queue
  • Select only the lines containing bigbulk.test.com
  • Select only the queue ID (stripping out the hold-indicator)
  • Select only the first 5 results
  • Pass the queue IDs to the postsuper -H command

mailq | grep bigbulk.test.com | cut -d '!' -f 1 | head -n5 | xargs -n1 postsuper -H

Password Form Usability: Duke Energy

When you’re singing up with any online service, picking a password is always trouble. What weird password requirements does this service have? Tonight I had that question with Duke Energy’s sign-up form. Fortunately, they had a Help icon that described the password rules:

Duke Energy Sign-up Form: password rules
Text reads: Password are case-sensitive and must contain at least eight letters or numbers.

OK, minimum of 8 alphanumeric characters. Easy enough.

I use Password Safe as my password manager. I have no idea what any of my passwords are: they are auto-generated random strings. I generated a new random password and entered it into the form:

Duke Energy Form: password feedback mixed messages
Text reads: Must be at least eight characters, contain one letter and one number and no special characters.

OK, so the password rules are a little different than what was initially described. But which symbols are special characters? And this screen is sending mixed-messages: if the password strength is rated Strong, why is it not valid?

I updated the password generation rules in Password Safe to generate a new password, assuming that only alphanumeric characters are allowed:

  • Use lowercase letters, minimum 1
  • Use upper case letters, no minimum
  • Use numbers, minimum 1
  • No symbols

And, since no symbols are included, I increased the password length to 16 characters. This new password was accepted, but the feedback indicates that the password is only moderately strong!

Duke Energy form: a valid password, rated moderately strong
The password is valid, but rated only moderately strong

I’m guessing that the code that generates the password strength indicator is from a 3rd-party and has no knowledge of Duke Energy’s password rules.

My problems with this, from a usability perspective:

  1. The password rules should apparent and described accurately.
  2. The password strength indicator should be aware of any password rules, and should describe a rejected password as such.

From a security perspective, I don’t see why any keyboard characters should be restricted. More characters to choose from means more complexity. Plus, if there were no character restrictions, it would be easier to describe the rules–and use an accurate 3rd-party password-strength tool.

Using getResponseHeader with jQuery’s ajax method

The Canvas API uses pagination for requests that return multiple items. The URL of the next result set is specified in an HTTP response header. I know how to get the response body, but how do I get the response headers from a jQuery AJAX call?

The jQuery ajax documentation indicates that the “[jqXHR] object returned by $.ajax() as of jQuery 1.5 is a superset of the browser’s native XMLHttpRequest object.” And the XMLHttpRequest object includes a getResponseHeader method.

At first I tried calling getResponseHeader as a method of the data object:

$.ajax({
    url:'/api/v1/courses'
}).done(function (data) {
    console.log(data.getResponseHeader('Link'));
});

But data contained just the response body, and data.getResponseHeader was null.

A closer look at the jQuery documentation indicated that additional objects are passed to the done function, including the jqXHR object. Accessing that object’s getResponseHeader method worked:

$.ajax({
    url:'/api/v1/courses'
}).done(function (data, textStatus, xhr) { 
    console.log(xhr.getResponseHeader('Link')); 
});

ColdFusion session fixation and jsessionid

A web application is vulnerable to a session fixation attack if an unauthenticated user’s session ID does not change after authentication. A malicious user could start an unauthenticated session and give the associated session ID to the victim. Once the victim authenticates, the malicious user now shares that authenticated session.

ColdFusion introduced SessionRotate in CF10 to mitigate such attacks. However, it only applies to CFID and CFTOKEN (ColdFusion sessions). If you are using J2EE sessions, there is no SessionRotate. This is unfortunate, as the ColdFusion documentation has stated for years that J2EE sessions have advantages over ColdFusion sessions (see Configuring and using session variables), the foremost being that J2EE sessions are not persistent. It’s also unfortunate, because this is an important security-related tag that only works under certain server configurations–and fails silently under others!

As a side note, J2EE has been called JEE (Java Enterprise Edition) since Java 1.4. The ColdFusion administrator continues to refer to it as J2EE, but you are likely to see references to JEE in reference material online.

Session rotation basically consists of:

  • Deleting the user’s current session
  • Creating a new session containing the same data, but with a different ID

Jason Dean at 12robots.com wrote about mitigating session fixation on ColdFusion in Session token rotation REVISITED and other articles. His posts were helpful in explaining the issue and offering a solution for rotating ColdFusion sessions on pre-CF10 systems. However, he posted no solution for JEE sessions.

But according to the CF documentation on Ending a session: You cannot destroy the session and create a session on the same request, as creating a new session involves sending session cookies back.

That’s problematic. Once a user authenticates, we can destroy the user’s session. But how do we create the new, authenticated session?

An answer posted in response to ColdFusion Session Fixation on StackOverflow suggests the solution is to store necessary data in a secure way and pass the data, or a token associated with that data, on to a new HTTP request. I considered, for example, generating a UUID as a nonce and storing it in a database along with the username and a near-future timestamp. The user would then be directed to a URL with the nonce appended as a token, which could be validated against the database to authenticate the user.

Fortunately, a simpler solution exists. Pete Freitag posted SessionRotate solution for JEE Sessions in March, 2014. By accessing properties of the Java servlet session running under ColdFusion, his methods perform the following steps:

  • Copy the session scope into a temporary variable
  • Remove the ID and URL tokens from the temporary variable
  • Invalidate the existing session
  • Create a new session
  • Copy the attributes of the temporary variable into the new session

The final step is somewhat more complicated, as the new session is apparently not immediately available, and copying the attributes happens in the Application.cfc’s OnSessionStart. Still, it can happen in a single request! The JEE documentation on Creating and Managing User Sessions has more information about the underlying JEE sessions.

When I implemented Pete Freitag’s solution, I made the following modifications:

  • I created a jeeSession.cfc containing:
    1. rotate (originally named jeeSessionRotate)
    2. copy (containing most of onSessionStart)
  • I created jeeSecureApp.cfc containing:
    1. onSessionStart
    2. an instance of jeeSession.cfc

I can modify the cfcomponent tag in the Application.cfc of any app to include:
extends="jeeSecureApp"

Then jeeSession.rotate() is available.

Caveats:

  • If your application already extends a CFC, you’d have to adjust this approach.
  • If your application already defines an onSessionStart, you’d want to add a call to super.onSessionStart