Your assignment is to create a version 2 of your cryptoquote webserver on 45.56.74.139.

This time around, you have use two scripts. The first, crypto2, will display only the encrypted quote, and will also save the quote, the key, and the encrypted version in a SQLITE database.

The second script, crypto2-reveal, will display the answer by consulting the SQLITE database.

You can try this at here.

Again, please use port numbers consisting 43000 plus your roster number (do check it again since it has been updated since the last time around.) Otherwise, you should not have to make changes to your start-server script.

Your SQLITE database should be in a subdirectory "~/httpd/data"; please name it "~/httpd/data/crypto.sqlite":

COP4342_test@(none):~$ ls -lR
.:
total 8
drwxr-xr-x 2 COP4342_test COP4342_test 4096 Feb 14 18:04 bin
drwxr-xr-x 4 COP4342_test COP4342_test 4096 Mar  7 17:32 httpd

./bin:
total 12
-rwxr-xr-x 1 COP4342_test COP4342_test 264 Feb 14 18:04 fib.sh
lrwxrwxrwx 1 COP4342_test COP4342_test  12 Jan 31 16:40 httpd -> /bin/busybox
-rwxr-xr-x 1 COP4342_test COP4342_test  64 Jan 31 17:19 start-server

./httpd:
total 8
drwxr-xr-x 2 COP4342_test COP4342_test 4096 Mar  7 17:54 cgi-bin
drwxr-xr-x 2 COP4342_test COP4342_test 4096 Mar  7 18:02 data

./httpd/cgi-bin:
total 24
-rwxr-xr-x 1 COP4342_test COP4342_test  983 Mar  7 16:33 crypto2
-rwxr-xr-x 1 COP4342_test COP4342_test 1233 Mar  7 17:54 crypto2-reveal
-rwxr-xr-x 1 COP4342_test COP4342_test  982 Jan 31 17:51 cryptoquote

./httpd/data:
total 40
-rw-r--r-- 1 COP4342_test COP4342_test 35840 Mar  7 18:02 crypto.sqlite

Here is a suggestion for creating a SQLITE table to hold your data:

COP4342_test@(none):~/httpd/data$ sqlite3 crypto.sqlite 
SQLite version 3.7.13 2012-06-11 02:05:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE cryptoquote (id integer, cleartext text, encryptedtext text, key text, primary key(id));
sqlite> .q

Very important: please sanitize and sanity check your input!

Your script should look at the QUERY_STRING environment variable to find which cryptoquote to display; legitimate QUERY_STRINGs should look like "key=NUMBER", and any other QUERY_STRINGs should report an error:

The key value should refer to the id primary key in your table.

The difficult parts of this assignment are

1. The quoting necessary to insert items into the database; dealing with SQLITE's quoting conventions is quite a challenge from a Bash script
2. Sanitizing and sanity checking the QUERY_STRING input to make sure that it is safe from SQL injection attacks:

I will be pretty stringent on the latter point, and if your program doesn't reject bad inputs, you will get a zero on the assignment.

This assignment is due by 11:59pm on Friday, March 24. Please turn in a tar file with your new files: the two Bash scripts, crypto2 and crypto2-reveal, and your SQLite database. If you change your start-server script, you can include that modified script if you think the change is significant.