CS 346 (Fall 20): Cloud Computing
Project #4
SQL
due at 5pm, Fri 23 Oct 2020
REMEMBER: Javascript is BANNED in this project – and all following projects – until/unless I say otherwise.
1 Overview
In this project, you’ll be setting up your first SQL database (through Amazon RDS), defining a couple of tables, and writing CGI scripts which read and write those tables. In the end, you will have something which approximates the “orders” database example from the slides.
You will turn in the following (to GitHub Classroom):
• Log onto your database (after the website is done, of course!) and use the SHOW CREATE TABLE command (detailed below) to show the configuration of each table you’ve created. Save this into a file called TABLES.txt.
• All of your CGI code. DO NOT include any passwords in any of the files!!!
• A file, named README.txt, which documents the address where your web- site is running (leave it online, so that the TA can check it).
NOTE: In this spec, I’ve provided some minimal requirements. If you fulfill these requirements, you will earn full points. However, everything is graded by hand, and so you are allowed – encouraged, even – to add additional features. Feel free to be creative! Just remember to actually complete the tasks I’ve assigned (so that I will know you have learned what you need to learn.)
1.1 Detailed Requirements
You must:
• Create a database with at least two tables: orders and products. Make
sure that orders has a foreign key, which references the product table. In addition to the primary and foreign keys, each table must have at least
3 other fields. (Feel free to be creative about what they are.)
You are allowed (but not required) to add more tables and features to your database.
1
2
•
Create a website to read and write the database. The start page of this website must be a static page, with links (and/or forms) that go to the CGI pages.
Create CGI scripts which allow you to read the contents of each table. Report the records as an HTML table. Exactly how you name the pages is up to you, but often, we have a separate HTML page for each SQL table. (You may, but are not required to, use a join when reading the orders table.)
Create a form and CGI script which allows you to add to the products table, and another to add to the orders table.
Create a least one form and CGI script which allows you to search the or- ders table; you may search by any parameter except the order ID (product ID is OK). Your code must be able to report many different rows, and use an HTML table like we required above. Moreover, this page must use a join, to connect the orders with the associated product fields.
Setting up SQL for the First Time
Before we do anything more, we need to set up your first database. Look at the lecture slides – I have a walkthrough, which will help you create a “database instance” (basically, an EC2 instance running a MySQL server) using Amazon RDS.
Once you’ve set up the RDS instance (it will take a few minutes for it to start running), log onto your EC2 instance. Install mariadb (see the lecture slides), and then run the mysql command to connect to your DB instance.
3 Creating a Database
If you successfully connect to your RDS instance, you will see a prompt like this:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 86
Server version: 5.7.22-log Source distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type ’help;’ or ’\h’ for help. Type ’\c’ to clear the current input statement.
MySQL [mysql]>
2
4 Choosing Your Database
This database instance can host many different databases at the same time. Thus, when you start up the client, you must indicate which one you want to use.
Start by exploring the list of available databases, with the command
SHOW databases;
(the semicolon is important). A brand-new instance has a few databases already created; these exist to manage the basic configuration of the database system. (We won’t be using those – although you can feel free to explore.)
You will only create a new database very rarely – most of the time, you will use one that you’ve already created. But we need to create one now. Use the command
CREATE DATABASE choose_a_database_name;
to create it. (Give it a name you prefer.) It is conventional (though not required) that databases – and the fields inside them – use lowercase names; if you follow that habit, it will be easier, when you write your commands, to keep track of which words are SQL keywords (like CREATE and DATABASE) and which are names that you’ve defined.
Once you’ve create a database, use SHOW to confirm that it exists. Then, type the command
USE choose_a_database_name;
to indicate what database you’ll be using for all of your transactions.
4.1 Next Time
From now on, when you connect to your database instance with the mysql command, you only need to run the USE command. (If you forget the name of your database, you can find it with SHOW.)
5 Creating a Table
Now, it’s time to see what tables we want in our database. Use the command
SHOW TABLES;
to see what tables exist in the database; when we first create the database, it’s empty (except for some system tables, which we don’t need to look at).
Now it’s time to create a table. Look at the lecture slides – they show you how to create a new table; use the CREATE TABLE command on the mysql command line.
Not sure what to create the first time? That’s fine, create a simple table, just to experiement. Later, when you decide exact what you’re need for this project, you can always use
3
DROP TABLE table_name;
to get rid of old tables.
Once you’ve created a table, confirm that it’s there. First, use
SHOW TABLES;
to see what tables exist. Then, use
SHOW COLUMNS FROM table_name;
to get details on a single table. Finally, you can use
SHOW CREATE TABLE table_name;
to get the exact syntax you would use to re-create this table from scratch.
6 Exploring SQL
Take some time to try out some commands. Using the slides to help you, insert a few rows into your table.
Remember: The primary key is automatically set by the database when you create a new record. Never provide an explicit value for this when creating a new record (at least, until we get more advanced with our primary keys).
Explore the SELECT command; can you read all of the records from the table? Can you read just a few? Can you read all of the fields in the records you select? Can you tell SQL to only return a few of the fields, or maybe just one of them?
Explore the UPDATE and DELETE commands. Can you modify an existing record? Can you delete an old one?
7 Basic CGI Queries
Next, it’s time to start querying the database with a CGI script. But first, we need to get more software installed on the instance. Do the following:
sudo yum install mariadb-devel gcc python3-devel
sudo pip3 install mysqlclient
Then, open up a CGI script on your instance. Set it up as you’ve done in the previous project, but in addition, add the following two lines to the Python script:
import MySQLdb
import passwords
4
7.1 Handling your SQL Password
You should *NOT* ever put passwords into your raw source file!!!
Source files are often shared between people (such as when you check them into Git), and so putting usernames, passwords, or other sensitive information into a source file is a major security risk.
Instead, you want to keep your code separate from your login information; to do this, you should put the login information into another Python source file, which will never be checked into Git or any other publicly viewable system. You can name this file anything you want, of course – but in the example above, I’ve suggested that you create a module named passwords.
To make this work, create a file passwords.py (note the .py) in the cgi-bin directory. Make sure that you mark this file as NOT exectuable; this will ensure that users of your website can’t accidentally read it or otherwise expose information about it. (A better long-term solution would be to move it into a different directory, outside cgi-bin, but that requires updating the Python import path. Feel free to investigate that on your own time.)
Inside passwords.py, define variables that represent your secret information. For instance, I like the following variable names:
SQL_HOST = “this.is.the.DNS.name.to.access”
SQL_USER = “captainamazing”
SQL_PASSWD = “booyah24”
We will use those variables below, when we connect to the database.
7.2 SQL Library Overview
The SQL library that we’ve imported, MySQLdb, requires that we perform several steps to connect to a database and perform operations:
• We must “connect” to the database. This makes the network connection, and requires that we provide the username and password.
This connection object is also used to represent the ongoing transaction; when all of our operations have completed, we will call commit() to com- mit all of the work to our database.
• After we have connected, we must create a “cursor.” This represents a sin- gle ongoing operation. (In SQL, a “cursor” keeps track of a long-running operation, which might require many network exchanges to download all of the data from the server.)
• Using the cursor, we will call execute() to perform an SQL statement. We will pass execute() a string which is basically the same thing as we would have typed on the SQL terminal.
• We will call methods on the cursor to read back the status or results from our operation.
5
• We will close the cursor when we’re done (and open a new cursor if we want to perform another SQL command).
• We will commit (and then close) the connection when we want to commit the current transaction.
The code for each of the steps is as follows:
# create the connection. Of course, you can replace ’conn’ with any
# variable name you like. The ’host’ is the DNS name of the DB instance;
# the ’db’ is the database that you want to “USE” for this transaction.
conn = MySQLdb.connect(host = passwords.SQL_HOST,
user = passwords.SQL_USER,
passwd = passwords.SQL_PASSWD,
db = “livedemo”)
# builds a single cursor. Note that, after we’ve finished this operation,
# we can close this cursor and then build another cursor for another
# operation, if we want. As always, the variable name on the left-hand
# side here can be anything that you want.
cursor = conn.cursor()
# send an SQL command to the DB
cursor.execute(“SELECT * FROM livedemo_users;”)
# this is the way to get the results from a SELECT operation. It returns
# a list (possibly empty) of tuples. Each tuple is a record, and each
# element in the tuple a field. If you use *, then this is all of the
# fields in the table, in an order determined by the DB. If you want to
# limit the number of fields or set their order, then you can specify the
# fields in the SELECT statement – and each tuple will model the order
# that you’ve chosen.
#
# Note that other SQL statement types use other mechanisms to check status;
# see below.
results = cursor.fetchall()
# once you’ve read the results into a local variable, we don’t need the
# cursor anymore. Note that the connection object – and the transaction –
# stay open.
cursor.close()
6
8
print(“Num records found: “, len(results))
for rec in results:
print(rec)
# create another cursor, and start another operation
cursor = conn.cursor()
cursor.execute(“INSERT INTO ” … )
# INSERT, UPDATE, and DELETE statements don’t return a list of the affected
# rows; instead, you simply ask *how many* rows were affected.
count = cursor.rowcount
cursor.close()
# if we don’t try to commit this operation, then the changes that we made
# will never be visible to any other person reading from the database.
#
# NOTE: In the real world, you need to have try/catch blocks around all of
# this SQL stuff (especially the commit, since committing the
# transaction might fail). In our little programs for this class,
# we can live without that.
conn.commit()
conn.close()
Passing Parameters to execute()
Since the SQL command is a string, it’s tempting to just drop variables into the right places, using the Python string-formatting tools. But what if your user is a hacker, who actively wants to hurt you? Do you really want them to be able to drop arbitrary code into your command???
7
https://xkcd.com/327/
Instead, you need to use sanitized inputs – that is, inputs which are guar- anteed to never be misinterpreted as part of the command itself. Happily, the library can do this for you automatically.
Instead of building the search string yourself, you should build a pattern for the search string. If you have used printf() before, you’ll notice that this is similar: the marker %s denotes a place where execute should drop a parameter:
cursor.execute(“SELECT * FROM users WHERE name LIKE %s;”, (username,))
https://www.w3schools.com/sql/sql_like.asp1
If you want to pass multiple parameters, then you must wrap them in a
tuple:
cursor.execute(“SELECT * FROM users WHERE name LIKE %s AND age>%s;”, (username, minage))
(Note I used an %s pattern, even when the parameter is an integer.)
All of your execute() calls should use this feature to protect your
database from hackers.
9 Your Task
Now, you know enough to write the code for your website! Look at the detailed requirements, at the top of this document, to review what is required. I would urge you to implement your website in the following steps:
• Design your tables. How many tables will you have? What will their fields be? Be specific about what types each field will use, and figure out how the foreign key releationships will work.
• Use the mysql command line to create the tables you’ve designed. Test your tables out with a couple of simple INSERT and SELECT statements.
• Design your website, from the user side. What pages will we offer? Which pages will be static, and which dynamic? What forms will we include?
1Why did I use the funny syntax around username ? That makes it a tuple with one element.
8
• Sketch out the SQL commands that each CGI script will need to execute.
• Start writing CGI code, starting with the simplest – which is probably the “query the entire table” pages. (These only require a single SQL statement, and don’t require you to commit when you’re done.) Confirm that you can read records from the table.
(Use the mysql command line to insert some dummy records.)
• After the simplest code is working (and saved into git!) copy-paste that file, as the baseline for more complex CGI scripts. Try the “search for matching record” next, since it also is just a SELECT statement.
• Do the most complex (inserting into the order table, confirming that the product ID is valid) last.
9