CPSC 343 Database Theory and Practice Fall 2024

DB Application — Web

Over the course of these exercises, you'll create a rudimentary photo-management system. While your application will have only very basic features, you will gain practice with HTML, PHP, and interacting with databases and will implement several things that may be useful in your final project.

Preliminaries

Setup

Handin

Exercises #1-9 are needed for full credit. Exercise #0 isn't required or graded, but is useful for making sure that you can successfully view PHP files. Exercises #10-13 also aren't required or graded — they provide extra practice and include some elements that may be useful in your project.

Warmup

This isn't part of the photo album application, but it provides a test that you can successfully view PHP files.

  1. Paste the following into a file called datetime.php in your ~/cs343/photoalbum 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>

Photo Album

These 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 their 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.





The full application will be built in stages. The first stage is to display a hardcoded set of photos. This just involves HTML and PHP, without any use of the database.

  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.

    photoalbum screenshot

    You can find a collection of images in http://bridgeman-vm.hws.edu/cs343f24/pics/ — you can choose whatever ones you want to display.

    Your solution should also do the following things:

    Implementation hint: If you don't have prior experience with HTML, consider tackling this in two steps — first just write HTML (no PHP) for the desired result with the specific image URLs and number of images hardcoded, then write the PHP version to iterate through the array of image URLs and produce that HTML.





Stage two is to add the database connection — you will create two standalone pages (working with a hardcoded values instead of user input) which now retrieve data from the database.

In addition to what is described below, your solution should also do the following things:

  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:

    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. an error 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)

    view album page

    The page has two main sections:

    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 #1 — 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).

  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:

    edit keyword page

    Your page should display arthur's keywords for the image http://bridgeman-vm.hws.edu/cs343f24/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://bridgeman-vm.hws.edu/cs343f24/pics/IMG_4881.JPG, which has no associated keywords.

    Your solution should also do the following things:





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

The flow of control is summarized in the following diagram:

application design

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:

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:

    index 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:

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

    view album page

    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://bridgeman-vm.hws.edu/cs343f24/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 Features

Finally, add some additional features to the photo management system. These exercises are optional, but provide additional practice and include some features that may be useful in your project.

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

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

    edit keyword page

  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://bridgeman-vm.hws.edu/cs343f24/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://bridgeman-vm.hws.edu/cs343f24/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).

So far we've stored the photo album usernames and passwords 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 (assuming one has valid credentials for the database).

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.

last updated: --Fri Oct 18 05:30:21 EDT 2024--
page owned by: bridgeman@hws.edu