CS 4250 Project Part 7

Demonstration: you must email me a link to your website by the deadline, 5 pm on Thursday, December 19, 2024. You are welcome to send the link earlier; I will not visit until 5 pm on the due date.

Note that this due date is the day after finals end, and the CS Lab will be closed on that day. Try to complete your web site work earlier, while the CS Lab and the help possibly available there is open. Start early.

(Subject line of "cs4250, project part 7", exactly. You should send the link / URL early; I will not visit your website until the deadline.) Remember to include the name of your project/group in the email message, and the names of your project members, and to carbon copy (cc:) your project partners.

The goal of this part of the project is to interface your database with a programming language and to make it web-accessible. This is the stage where you embed your database into a complete application. The programming language that I recommend is PHP, a popular, server-side scripting language. (See the class lecture notes for more details about PHP.) PHP has been installed on the DBH 288 machines. Using PHP, the MariaDB database that you have created can be accessed via the web, using a webserver installed on dragon.csustan.edu. (Bear in mind that, due to recent security constraints, dragon.csustan.edu can only be reached if you are on-campus, either physically or virtually, with the assistance of a VPN.)

Before detailing the assignment itself, it may be helpful to read the following FAQs:

Answer: No. Due to security restrictions, you can access the MariaDB database server on dragon.csustan.edu only from machines in DBH 288, either by logging in directly or by using the web interface you will create in this part of the project.

Note that if you write your PHP scripts to connect to "localhost" and test them by connecting to an installation of MariaDB on your own machine, then porting the scripts to a DBH 288 machine for project submission should be fairly straightforward.

FAQ: Can we use a commercial service outside the university (like AWS or MS Azure) to host our database server and web site?

Answer: No. This is a "learn to do it yourself" assignment.

FAQ: Can we use our own database server and web server?

Answer: No. Students who have attempted this in the past have too often created web sites that they could access, and the students thought "everyone" could access, and the grader could not access.

FAQ: Can we use Ruby-on-Rails/AJAX/Javascript/substitute-the-latest-language-buzzword-here/ for the project?

Answer: Certainly! However, should you decide to use <insert buzzword here>, you are on your own. We do not have facilities to support these in the lab, nor to provide technical support.

FAQ: What is an SQL injection attack?

Answer: SQL injection attacks are a common way for malware to invade DBMSs via the code a web site uses to interact with its database. (Wikipedia's SQL injection page)

• SQL injection, php.net
• Prepared SQL statements, php.net
• Prepare SQL statements, php.net

Preventing SQL Injection in Python - YouTube by SANS Cloud Security. General overview on SQL injection and prevention, with material specific to Python at the end.

Test your SQL injection prevention on SQL queries that use "LIKE", if you implement the prevention technique on the ad-hoc query part of your project web page. LIKE queries, with string matching operators, should work sucessfully.

(Our system administrator required you to do to set appropriate file permissions. Work our sysadmin required does not "count" as your SQL injection-prevention, for this part of this assignment. Your group must do something additional, something the sysadmin did not require.)

• Select one person in your project in whose directory you will create the files for the web interface.
• Create a directory called cs4250 in this person's main web site directory on dragon.csustan.edu. (Frequently, this web site directory is named public_html.)
• Follow the directions on the MariaDB account information handout to make the cs4250 directory world-readable.
• Place all your scripts and files inside this directory.
• You can access your website at the following URL: http://dragon.csustan.edu/~<selected person>/cs4250/<start file name>.php.

What I want to see at the end of the day is a single web page that provides a nice interface to your database. Feel free to do more fancy and creative things, but do so only after completing this "core" requirement. Here is a style guide for what features your basic inferface must provide. Look at the template carefully. Notice that we are only querying the database. We are not doing any inserts, updates or deletes. (That's one direction to look at, if you are wondering how to earn credit for extra functionality.)

The PHP libraries that connect to database systems connect to MariaDB or MySQL.

Help with PHP

Here are some good documentation resources:

• The PHP website
• The PHP manual. The manual also includes unusually helpful user comments.
• W3Schools.com PHP tutorials
• "Developer Quickstart: PHP mysqli and MariaDB" (mysqli) or "Developer Quickstart: PHP Data Objects and MariaDB" (PDO)
• PHP Programming (in 30 minutes) - YouTube video by Derek Banas
• Our library has a subscription to the Safari Books On-line digital library. O'Reilly publishers have all their books available on Safari, and they publish some excellent PHP books. Titles like "Head First PHP & MySQL", "Learning PHP, MySQL and JavaScript", "Build Your Own Database Driven Web Site Using PHP & MySQL", "PHP and MySQL For Dummies" and many others look promisingly relevant to this project. (Your professor has not read all these books and cannot speak to which is best. Past students have spoken well of "Head First" books, and personal experience informs me that the books published by O'Reilly Media are frequently well put together.)
• Execute PHP MySQL On-line, a PHP testing facility provided by http://www.tutorialspoint.com/ (link idea courtesy of C Busby)

• PHP supports several APIs for accessing MariaDB and MySQL. Here is a guide to selecting the API you prefer.
• The MySQL Improved Extension (mysqli) allows you to access the functionality provided by MySQL 4.1 and above.

Should you find another good PHP/scripting/MySQL documentation/help WWW page, please let me know and I will add a link here for the whole class to benefit from.

One team member from your project should email the URL for your web page, where I can access your application. This can be done by sending email to Dr. Thomas.

I will not grade until December 19th, so it is fine if the URL you send leads to a non-functional page at the time you send the URL. (By the deadline, the pages should be functional.)

In addition, you need to compile a project report, plus source code appendices. More on this below.

1. (30 points) Does everything work correctly and do all the buttons and links that I click do what they are expected to do. I will see if you are really accessing the database dynamically and are not just "looking up" the data from some ready made source. I will ensure this by typing ad-hoc queries. You will also need to make sure that all query results have column names neatly presented and that data is clearly tabulated.

   Submit your source code (.zip or .tar.gz file) to Canvas along with the report (next item), or include the source code as appendices to your project report. (The actual source source code files must be submitted, not a link to some other web site. Only one member of each group needs to submit.)

   (Stick with "classic," plain .zip or .tar.gz formats. Don't use a newer compression algorithm, because some of those are specific to one operating system and I may not have a suitable decompression program.)

2. (15 points) A project report where you summarize all parts of your project in a single document. If possible, include snapshots of your web interface. This report can be easily created by cut-and-pasting (corrected versions of) all the previous reports that you submitted (in their entirety), making sure you include the E/R diagram, the details about normalization, your relational schema and the queries you created in the previous part of the project. (Do not leave out significant information, like the E/R diagram documentation or lists of functional dependencies.)

   Include the role of each team member in the project. Clearly identify the scope of everybody's contribution. If there were any problems during the course of the project (due to, for example, less than complete participation by some team member), identify them.

   Portable Document Format (PDF) format preferred for your report. MS Word or plain text also acceptable.

   Include your answer to the next question in the document, too.

   Submit your report to Canvas.

3. (2 points) Explain whether or not somebody who is truly working in the domain of your application would use your web-enabled database, or if there is something missing or intrinsically complex that will deter them from using it. (Is your web-enabled database not powerful enough for the real world, or too complicated for the real world?) For example, if you are in the books domain, would a bookseller use your database? It would be ideal if you had access to a real application domain person; you could just ask him/her to visit your web page and give you feedback. Alternately, look at "similar" systems or web pages and see if they are doing something differently and if your approach is better (or worse) and why. Include your answer to this question in your project report.

   Please note that there is no "right" answer to this question nor will you be penalized for saying that there is something missing from your project. What I want to see is if you can discuss your domain, your database, and the implications of the design and implementation decisions you made in your system for the application domain that you have chosen.

4. (3 points) Identify, in English, where I can find, in your source code, one (or more) safety checks to the code that interacts with your database, to prevent some type of SQL injection attacks. These do not need to be complicated; I just want you to illustrate your understanding of the danger of SQL injection by blocking some possible attack. (A simple if-statement, in the correct location, will be sufficient.)
5. Include in your report a sample, functional SQL query that you tried in your own ad-hoc query box on your own web site, and that worked.
6. (5 points) Neatness, presentation, grammar, style, consistency etc.
7. (15 points) Implement some extra functionality. In your project report, clearly list exactly what you have implemented and how we can test it. Note that you must implement some extra functionality, to have a chance of getting up to 15 points. Here are some suggestions for this step:
   • Implement more complicated DB functionality: inserts, updates, deletes, etc., or some more complex queries.
   • Create a more sophisticated interface for your web application, not just the vanilla style template shown above. If there is a more "natural" way for your application, maybe you will write some cute little interface for it.
   • Address other aspects of your application, such as transactions
   • Nota bene: the grader will be testing your web site on a laptop or deskop. Extra functionality that is only visible on a smartphone will not be visible.
8. (Extra Credit points) If the extra functionality is remarkable, you can get up to 15 points of extra credit, depending on how complex your application is.