TITLE: Three Bears-ing a Password Generator in SQLite AUTHOR: Eugene Wallingford DATE: November 25, 2020 12:15 PM DESC: ----- BODY: A long semester -- shorter in time than usual by more than a week, but longer psychologically than any in a long time -- is coming to an end. Teaching databases for the first time was a lot of fun, though the daily grind of preparing so much new material in real time wore me down. Fortunately, I like to program enough that there were moments of fun scattered throughout the semester as I played with SQLite for the first time. In the spirit of the Three Bears pattern, I looked for opportunities all semester to use SQLite to solve a problem. When I read about the Diceware technique for generating passphrases, I found one. Diceware is a technique for generating passphrases using dice to select words from the "Diceware Word List", in which each word is paired with a five digit number. All of the digits are between one and six, so five dice rolls are all you need to select a word from the list. Choose a number of words for the passphrase, roll your dice, and select your words. The Diceware Word List is a tab-separated file of dice rolls and words. SQLite can import TSV data directly into a table, so I almost have a database. I had to preprocess the file twice to make it importable. First, the file is wrapped as a PGP signed message, so I stripped the header and footer by hand, to create diceware-wordlist.txt. Second, some of the words in this list contain quote characters. Like many applications, SQLite struggles with CSV and TSV files that contain embedded quote characters. There may be some way to configure it to handle these files gracefully, but I didn't bother looking for one. I just replaced the ' and " characters with _ and __, respectively:

    cat diceware-wordlist.txt \
      | sed "s/\'/_/g"        \
      | sed 's/\"/__/g'       \
      > wordlist.txt

Now the file is ready to import:

    sqlite> CREATE TABLE WordList(
       ...>    diceroll CHAR(5),
       ...>    word VARCHAR(30),
       ...>    PRIMARY KEY(diceroll)
       ...>    );

    sqlite> .mode tabs
    sqlite> .import 'wordlist.txt' WordList

    sqlite> SELECT * FROM WordList
       ...> WHERE diceroll = '11113';
    11113 a_s

That's one of the words that used to contain an apostrophe. So, I have a dice roll/word table keyed on the dice roll. Now I want to choose words at random from the table. To do that, I needed a couple of SQL features we had not used in class: random numbers and string concatenation. The random() function returns a big integer. A quick web search showed me this code to generate a random base-10 digit:

    SELECT abs(random())%10
    FROM (SELECT 1);

which is easy to turn into a random die roll:

    SELECT 1+abs(random())%6
    FROM (SELECT 1);

I need to evaluate this query repeatedly, so I created a view that wraps the code in what acts, effectively, a function:

    sqlite> CREATE VIEW RandomDie AS
       ...>   SELECT 1+abs(random())%6 AS n
       ...>   FROM (SELECT 1);

Aliasing the random value as 'n' is important because I need to string together a five-roll sequence. SQL's concatenation operator helps there:

    SELECT 'Eugene' || ' ' || 'Wallingford';

I can use the operator to generate a five-character dice roll by selecting from the view five times...

    sqlite> SELECT n||n||n||n||n FROM RandomDie;
    21311
    sqlite> SELECT n||n||n||n||n FROM RandomDie;
    63535

... and then use that phrase to select random words from the list:

    sqlite> SELECT word FROM WordList
       ...> WHERE diceroll =
       ...>         (SELECT n||n||n||n||n FROM RandomDie);
    fan

Hurray! Diceware defaults to three-word passphrases, so I need to do this three times and concatenate. This won't work...

    sqlite> SELECT word, word, word FROM WordList
       ...> WHERE diceroll =
       ...>         (SELECT n||n||n||n||n FROM RandomDie);
    crt|crt|crt

... because the dice roll is computed only once. A view can help us here, too:

    sqlite> CREATE VIEW OneRoll AS
       ...>   SELECT word FROM WordList
       ...>   WHERE diceroll =
       ...>           (SELECT n||n||n||n||n FROM RandomDie);

OneRoll acts like a table that returns a random word:

    sqlite> SELECT word FROM OneRoll;
    howdy
    sqlite> SELECT word FROM OneRoll;
    scope
    sqlite> SELECT word FROM OneRoll;
    snip

Almost there. Now, this query generates three-word passphrases:

    sqlite> SELECT Word1.word || ' ' || Word2.word || ' ' || Word3.word FROM
       ...>   (SELECT * FROM OneRoll) AS Word1,
       ...>   (SELECT * FROM OneRoll) AS Word2,
       ...>   (SELECT * FROM OneRoll) AS Word3;
    eagle crab pinch

Yea! I saved this query in gen-password.sql and saved the SQLite database containing the table WordList and the views RandomDie and OneRoll as diceware.db. This lets me generate passphrases from the command line:

    $ sqlite3 diceware.db < gen-password.sql
    ywca maine over

Finally, I saved that command in a shell script named gen-password, and I now have passphrase generator ready to use with a few keystrokes. Success. Yes, this is a lot of work to get a simple job done. Maybe I could do better with Python and a CSV reader package, or some other tools. But that wasn't the point. I was revisiting SQL and learning SQLite with my students. By overusing the tools, I learned them both a little better and helped refine my sense of when they will and won't be helpful to me in the future. So, success. ~~~~~ I demo'ed a variation of this to my students on the last day of class. It let me wrap up my time with them by pointing out that they are developing skills which can change how they see every problem they encounter in the future. Knowing how to write programs gives you a new power. Whenever you encounter a problem, you can ask yourself, "How might a program help me solve this?" I do this daily, both as faculty member and department head. The same is true for many more specialized CS skills. People who know how to create a language and implement an interpreter can ask themselves, "How might a language help me solve this problem?" That's one of the outcomes, I hope, of our course in programming languages. The same is true for databases. When I came across a technique for generating passphrases, I could ask myself, "How might a database help me build a passphrase generator?" Computer science students can use the tools they learn each semester to represent and interpret information. That's a power they can use to solve many problems. It's easy to lose sight of this incredible power during a hectic semester, and worth reflecting on in calmer moments after the semester ends. -----