CPSC 343 Database Theory and Practice Fall 2017

# DB Application - Web

Over the course of these exercises, you'll create a rudimentary photo-management system. (There are also a couple of warmup exercises.) While your application will lack many useful features, you will gain practice with HTML, PHP, and interacting with databases - and will implement several things that be useful in your final project.

## Setup

• Review the PHP Local Quick Start for information on how to set up a directory for your HTML/PHP files (do this setup now, if you haven't already!) and how to view them once you've created them.

• Then, create subdirectories warmup and photoalbum in your www/cs343 directory to hold the files you create for these exercises.

## Warmup Exercises

These aren't part of the photo album application, but provide practice with some elements of PHP and web pages.

Put the files for these exercises in your www/cs343/warmup directory.

1. Create your own copy of the date and time demo from the PHP Quick Start - just paste the following into a file called datetime.php, save it in your www/cs343/warmup directory, and try to view it.

```      <html>
<head>
<title>Date and Time Demo</title>
</head>
<body>
<h2>Current Time</h2>
<p>It is currently <?php print date("l F j, Y g:i:s a"); ?>.</p>
</body>
</html>
```
2. Create a PHP page named diceroll.php which rolls a 6-sided die 100 times, and prints out the frequency of rolling each number. The output should look like the picture shown to the right (though with different frequencies, since you'll get a different set of random numbers each time the page is loaded).

Your solution should also do the following things:

• Use constants to define the low and high end of the value range (1 and 6, respectively, for a 6-sided die), and for the number of rolls to perform.

• Use an array indexed by value to store the number of times that value has been rolled.

• Use the rand() function to generate random numbers - look it up in the PHP Manual to find out how it works. (There's a handy search box at the top of each manual page...)

• Use an HTML table to arrange the output as shown in the picture. Note that the values and frequencies are centered within the table cells.

## Photo Album

The rest of the exercises involve the development of a basic photo management system. Each user can maintain an album of photos. Photos may be tagged with keywords (a given photo may have multiple keywords). The user may view all of the pictures in her album or select just those with a certain keyword. Users are identified by a username and password (so they must log in before viewing or modifying an album). Users may create new accounts, add photos to their album, or add keywords to a photo.

The username and password associated with each user of the photo-management system is distinct from the username and password which allows you to connect to the MySQL server and carry out queries. In the latter case, MySQL handles verifying the correctness of the password and enforcing the permissions associated with the user, while in the former case, the database only stores the necessary information - it is up to the database application (i.e. your web application) to use that information to verify the correctness of the password and to enforce any necessary access permissions.

The necessary data - usernames and passwords, URLs of the images in each album, and the keywords for each image - will be stored in the database with the following schema:

```USER ( username, password )
ALBUM ( username, url, id )
KEYWORD ( id, keyword )
```

USER stores information about users and their passwords, ALBUM links images to users, and KEYWORD links keywords to images. id is numerical ID used to uniquely identify entries in the ALBUM table. ALBUM.username is a foreign key which refers to USER.username and KEYWORD.id is a foreign key which refers to ALBUM.id.

Use the database username_photoalbum (where username is replaced by your username) for the exercises. The user accounts arthur and ford have passwords tea and betelgeuse, respectively.

Put the files for these exercises in your www/cs343/photoalbum directory.

The full application will be built in stages. The first stage is to display a hardcoded set of photos:

1. Create a PHP page named photos.php to display a collection of image thumbnails, as shown below. The thumbnails should be arranged in an HTML table with no more than five images per row. Each thumbnail image should have dimensions 100x100 (regardless of the actual image size). Clicking on a thumbnail image should display the full-size image.

You can find a collection of images in http://172.21.7.83/cs343f17/pics/ - you can choose whatever ones you want to display.

Your solution should also do the following things:

• Use an array to store the URLs of the images to be displayed. (The specific URLs will be hard-coded into the page.) Include at least six URLs, and use absolute URLs (e.g. http://172.21.7.83/cs343f17/pics/108_0822.jpg, not just 108_0822.jpg). This array will need to be a variable instead of a constant because arrays are not allowed for constants.

• Correctly handle any number of images - modifying the list of images in your array initialization should be all that is necessary to display a different set of images or a different number of images.

Stage two is to add functionality for a single hardcoded user:

1. Create a file called common.php. This file will contain definitions used in multiple places; since it will be included in other pages, it does not need to include tags like <html>, <head>, or <body>.

Instead, it should contain only PHP statements to define four constants:

• DATABASE whose value is the name of the database to connect to (username_photoalbum)
• HOST whose value is the name of the machine where MySQL is running (172.21.7.83)
• DBUSER whose value is the name of the MySQL user to use when connecting to the database (use guest)
• DBPASS whose value is the password to use when connecting to the database (use guestpass)

Keep in mind that the PHP processor starts each file in HTML mode, so you will need to enclose the PHP commands in <?php ... ?> tags.

You may add additional definitions to this file as appropriate for later exercises (e.g. a showError function used for error-checking).

2. Create a file called viewalbum.php to display the contents of a user's album. It should produce results similar to the following: (exact pictures and keywords may vary; only the top of the table of images is shown)

The page has two main sections:

• A bulleted list of the keywords (in alphabetical order) assigned to at least one of the user's images.

• A table showing thumbnails of all of the images in the user's album. (The code to arrange the images in the table will be very similar to what you wrote for #3 above - as in that exercise, there should be no more than five images per row, each thumbnail should be 100x100, and clicking on the thumbnail should load the full-size image.) If there are no images in the album, the message "Album is empty" should be displayed instead of the table of thumbnails.

Your page should display the album for the user arthur - it is OK to hardcode this into the queries. The keywords and image URLs for arthur's pictures should be obtained by querying the database. A second user (ford) has also been set up - ford's album has no pictures, and is useful for testing that your code does the right thing when the album is empty.

It is easiest to consider the sections separately - first get and display the keywords, then get and display the images. That is, establish a database connection, execute the query for the keywords, format the results into the list, free the results, execute the query for the images, format the results into the table, free the results, close the database connection. Also, you can build on your photo-display page from #3 - you've already solved the arrange-in-a-table-with-clickable-links problem, so you just need to tackle how to get the picture URLs from a database query instead of a hard-coded array (and add the list of keywords at the beginning).

Your solution should also do the following things:

• Use prepared statements, where the username is provided as a parameter.

• Perform all appropriate error-checking. It is OK to output the PHP/SQL error message along with some more user-friendly text, and you can display it on the page itself or write it to the error log (as in the example in the PHP MySQL Quick Start). You can also choose to avoid continuing with the rest of the page by using exit or by using exceptions.

• Properly free results and close database connections in all circumstances (including when errors occur).

• Avoid duplicates in keywords and images.

• Include common.php to use the constants defined there instead of hardcoding the database-connection parameters.

3. Create a file called editkeyword.php to display a user's keywords (in alphabetical order) associated with a particular image. If there are no keywords for an image, "No Keywords" should be displayed instead of the list of keywords. In addition, clicking the image should load the full-size image. Your page should produce results similar to the following:

Your page should display arthur's keywords for the image http://172.21.7.83/cs343f17/pics/img_4174.jpg - it is OK to hardcode this URL into the query. The keywords should be obtained by querying the database. You may want to also test your page using http://172.21.7.83/cs343f17/pics/IMG_4881.JPG, which has no associated keywords.

Your solution should also do the following things:

• Use prepared statements, where the username and image URL are provided as parameters.

• Perform all appropriate error-checking. It is OK to output the PHP/SQL error message along with some more user-friendly text, and you can display it on the page itself or write it to the error log (as in the example in the PHP MySQL Quick Start). You can also choose to avoid continuing with the rest of the page by using exit or by using exceptions.

• Properly free results and close database connections in all circumstances (including when errors occur).

• Include common.php to use the constants defined there instead of hardcoding the database-connection parameters.

This stage completes the basic photo-management system. When complete, the application will have the following pages: (some of the features are extras)

• index.html - the main entry point, where the user can enter a username and password to log in or can create a new account
• createaccount.php - processes the request to create a new account
• dologin.php - verifies the login
• viewalbum.php - displays the photos in the current user's album, possibly filtered to show only those photos with a particular keyword
• setfilter.php - processes the request to set a particular keyword as the album filter
• addimage.php - processes the request to add a new image to the album
• editkeyword.php - displays the keywords currently associated with the image and allows for a new one to be added
• addkeyword.php - processes the request to add a new keyword
• error.php - displays an error message
• common.php - contains definitions common to many pages

The flow of control is summarized in the following diagram:

Pages which produce HTML output are shown in rectangles; those which merely carry out some processing are in ellipses (for these, the only output is an HTTP Location: header to perform redirection). Solid arrows show paths taken as a result of user action (such as submitting a form); dotted arrows show redirections. Labels on the lines indicate what variables are expected by the target of the arrow ("P" means a value sent as a result of submitting a form via POST, "SV" means a session variable has been set) - other variables may be set, but aren't used by the target page.

The exercises below provide more detail on what each page should do.

Your solution should do the following things:

• Perform all appropriate error-checking (after the database connection is established, after the query is executed, before using variables whose values are expected to have been set elsewhere, etc). If an error occurs, print an appropriate error message along with a link to allow the user to return to an appropriate page (e.g. the login page if the username is not set or the "view album" page if the current image is not set).

• Properly free results and close database connections in all circumstances (including when errors occur).

• Produce legal and properly-formed HTML. (no stray tags, all open tags which should be matched with close tags are, etc)

• Include common.php to use the constants defined there instead of hardcoding the database-connection parameters.

• Take appropriate security measures when inserting user input into SQL in order to avoid SQL injection problems.

• Use prepared statements when prepared statements should be used.

You should test each part of the application as you complete it. For pages that produce HTML output, first make sure that the HTML is correct, then begin to test the actions that result from submitting forms.

Also keep in mind that you can connect to the database via MySQL Workbench. This is useful for debugging actions that change the database - you can check the contents of tables after you've tried to insert something, or you can fix up tables if something got inserted improperly.

1. Create index.html, to display the login/create user account page:

Create separate forms for the login and create account sections; submitting the login form should go to dologin.php while submitting the create account form should go to createaccount.php. Note that this page has only HTML - there's no dynamic content, so no need for PHP.

2. Create createaccount.php, to process the request to create a new account.

This page should extract the username and password entered into the form on the index.html page, connect to the database, execute the appropriate INSERT command to add the new information to the USER table, and, if successful, set a session variable with the username and redirect to viewalbum.php.

A note on passwords: MySQL provides a way to encrypt passwords so that they are not stored in plaintext. The way to do this is via the SHA function:

```      INSERT INTO USER(username,password) VALUES ('arthur',SHA('tea'))
```

would add a new user 'arthur' to the USER table, storing the encrypted version of 'tea' as the password. (You'll need to substitute the actual values entered in the form for 'arthur' and 'tea'.) SHA implements SHA-1, a version of the Secure Hash Algorithm.

3. Create dologin.php, to verify the user's login information.

There are two ways this could be done - one is to query the database for the password which goes along with the username that was entered and have the web application check if they are the same, while the other is to query the database for the entry which matches the username and password and have the web application check whether or not such a row was found. However, the first option doesn't work if the database stores encrypted passwords - because one feature of the encryption is that it is hard to figure out the original password given just the encrypted one, and without knowing how the SHA function works, the web application can't encrypt the user's input to compare to what was retrieved from the database.

As a result, this page should extract the username and password entered into the form on the index.html page, connect to the database, execute the appropriate SELECT command to retrieve the row of the USER table with the that username and password, and, if such a row was found, set a session variable with the username and redirect to viewalbum.php.

Remember that passwords are stored encrypted, so you'll need to use SHA('tea') instead of just 'tea' (substituting the user's actual value for 'tea').

4. Edit viewalbum.php, making the following changes:

• The keywords and album images should be retrieved for the current user (found in the session variable set by dologin.php or createaccount.php) rather than a fixed user.

• The keywords, along with an additional value "show all", are displayed in a drop-down list within a form instead of in a bulleted list. Submitting this form goes to the setfilter.php page.

• Each picture is accompanied by a form containing a hidden form element storing the URL of the image, and a submit button with the label "edit keywords". Submitting one of these forms goes to the editkeyword.php page.

• Add a form which allows the user to enter a URL and add an image to the album. Submitting this form goes to the addimage.php page.

It should end up looking like the following: (the exact pictures may vary, depending on what you've added to the database)

As in the previous homework, if there are no images in the user's album, an "Album is empty" message should be displayed instead of the table of images.

5. Create addimage.php, to add an image to the album.

This page should extract the username from the appropriate session variable and the URL of the image to add from the form, connect to the database, execute the appropriate INSERT command to add the image to the ALBUM table, and, if successful, redirect to viewalbum.php.

Note: If you look at the table definition for ALBUM, you'll see that ALBUM.id is an auto-increment field. This means that you do not have to supply a value for it when inserting a new row into ALBUM - the system will automatically pick a unique value. For example, you could insert into ALBUM as follows:

```	INSERT INTO ALBUM(username,url) VALUES ('arthur','http://172.21.7.83/cs343f12/pics/IMG_0176-edit.JPG')
```

Auto-increment fields are handy when you want to have a unique integer ID as a primary key (because, say, the actual PK field - like the URL in this case - is long and it wastes space to store that over and over in KEYWORD), but there isn't any meaning to particular values.

## Additional Practice

For additional practice, add some additional features to the photo management system:

1. Edit editkeyword.php, making the following changes:

• It sets a session variable storing the URL for the current image (retrieved from the form).

• It retrieves the keywords for the current image rather than a fixed image.

• Add a form with an input element to allow the user to enter a new keyword. Submitting this form should go to the addkeyword.php page.

• "return to album view": Add a form (with only a submit button) which, when submitted, takes the user back to viewalbum.php.

It should end up looking like the following: (again, the exact picture and keywords may vary)

2. Create addkeyword.php to add a new keyword to an image in the photo album.

This page should extract the username and image URL from the appropriate session variables and the keyword to add from the form, connect to the database, execute the appropriate INSERT command to add the keyword to the KEYWORD table, and, if successful, redirect to editkeyword.php.

There is a wrinkle to the insertion. Given the schema for the KEYWORD table, you might expect to write the following to add the keyword "Ruapehu" to image 1:

```      INSERT INTO KEYWORD(keyword,id) VALUES ('Ruapehu',1);
```

The problem is that you don't know the id for the image - but you do know the URL (from a session variable). One solution is to query the ALBUM table for the row matching the current user (also from a session variable) and image URL, and then do the INSERT using the id that was retrieved in the query. Another solution is to use a version of INSERT which allows you to do both of these steps at the same time (i.e. to insert data retrieved from another table):

```      INSERT INTO KEYWORD(keyword,id) SELECT 'Ruapehu',id
FROM ALBUM
WHERE url='http://172.21.7.83/cs343f12/pics/IMG_0081.JPG' AND username='arthur'
```

This says to insert the rows retrieved by the SELECT query into KEYWORD, where the first column of the SELECT result goes into the keyword column of KEYWORD and the second column goes into the id column of KEYWORD. Also note the use of a literal in the SELECT clause - SELECT 'Ruapehu',id means that for every row of ALBUM which matches the WHERE condition, the result will contain a row whose first column is 'Ruapehu' and whose second column is the value of the id field on that row. Of course, don't forget to substitute the appropriate values retrieved from session variables and forms instead of hard-coding values like "Ruapehu", "http://172.21.7.83/cs343f12/pics/IMG_0081.JPG" and "arthur".

3. Add support for viewing just those images with a specific keyword:

1. Create setfilter.php, to set the keyword to be used as the filter.

It should extract the keyword to filter by from the form. If the keyword is anything but "show all", a session variable should be set to store the filter. If the keyword is "show all", that session variable should be unset. Once done, it should redirect to viewalbum.php.

2. Modify viewalbum.php so that if the filter session variable is set, it queries the database for just those images belonging to the current user with the specified keyword (otherwise it retrieves all of the user's images).

In the exercises above, usernames and passwords are stored in a DBMS-user-defined table rather than a DBMS system table and authentication and authorization are handled entirely by the application. (It is up to the application to check that the username and password are correct, and then only send subsequent queries to access the photo album if the login was successful.) This has the advantages that user credentials only need to be sent once (for the initial authentication) and that it is very flexible - different applications can handle authentication and authorization differently, even with the same database - but also has the significant drawback that any such scheme is easily bypassed by simply querying the DB directly.

An alternative (while still using the "one big application user" model) is to bundle authorization into the DB access by creating stored routines which check for a valid login before carrying out the desired task. DBMS user accounts would then be granted access only to the stored routines so that the authorization checks cannot be bypassed, but authentication information has to be sent (and checked) with every request.

1. For each task below, do three things:

1. Write a stored routine (procedure or function, whichever is most appropriate) to accomplish the task.

2. Identify the MySQL privileges needed to carry out the task. Be specific - for database object privileges (as opposed to administrative or database-level privileges), also identify the particular database object(s) for which the privilege is needed.

3. In light of your answer for (b), include appropriate DEFINER and SQL SECURITY clauses in your CREATE statement from (a). As part of this, identify what DBMS user account(s) would be appropriate to have. Keep in mind that with SQL SECURITY INVOKER, the user calling the routine is the one whose username and password were sent with the mysqli_connect() call from a PHP page.

• Given a username and an (unencrypted) password, create a new user account.

• Given a username and an (unencrypted) password, determine whether the login is valid (i.e. the information corresponds to an existing account).

• Given a username and an (unencrypted) password, retrieve the user's photo album (the URLs of the images in the album) if the login is valid and generate an error (SIGNAL SQLSTATE '02000' in that case) if not.

• Given a username, a URL, and a keyword, add the keyword to the specified photo in that user's album. If the photo is not already in the user's album, also add the photo to the album.

• Given a URL and a keyword, add a global keyword to the specified photo - that is, add the keyword to that photo in every album in which it appears.

 last updated: --Mon Oct 30 07:21:17 EDT 2017-- page owned by: bridgeman@hws.edu