Web Database Development

Objectives:

• Create a MySQL database and tables
• Query database using PHP (list, add, search for, delete records in a database)
• Implement best practices to secure user password
• Implement form-based web authentication in the user log in process
• Use prepared statements and user-defined sanitizing functions to prevent certain injection attacks
• Provide client-side validation using JavaScript and server-side validation with PHP
• Implement session management on chosen web pages
• Design a dynamic web database application.

Tasks:

This project is to acquaint you with fundamental and essential functions when building dynamic web pages with PHP and MySQL. You will make a web application that allows a user, upon successful login, to view, add, search for, and delete records in a MySQL database. The application can register a new user, logs in a returning user, or logs out a user. You will practice on web authentication, input validation, data sanitization, session management, sign-up and sign-in process, connecting to web database, querying database, processing data, and displaying results on web pages. You will make several web pages showing your knowledge and skills of building a database-driven website. The completion of this project will greatly help you in your senior project course.

Your project should consist of the following function modules:

• Registration of a new user. A new user should at least provide email, username, password, and confirm password during the sign-up process. Data validation must be provided (see below for requirements). If any data is invalid, display an error message. If all data are valid, register the user. Check if the username is available. Display an error message if the username is not available and provide a link back to the registration page so user can choose another username to complete the registration.
• Login of an existing user with correct username and password. If the user enters incorrect username or password, display an error message and provide a link back to the login page so user can reenter the username and password.
• A main menu is provided to a user upon successful login. The main menu contains links for the following tasks:
  • Listing records of database table(s). You will design your own application database and decide what information will be listed on the web page. The data must be displayed in a table format.

o Adding records into the database. Provide text fields or other HTML forms to allow user to add a record into the database.

• Searching for records in the database. Use a drop-down list for user to choose a field to search and a text field for user to enter the information of that field to search for, for example, in our book example, if a user chooses “author” in the drop-down list, then the text field allows the user to enter the author

information to look up; if a user chooses “title” in the drop-down list, then the text field allows the user to enter the title information to look up.

• Deleting records from the database. You have two ways to do this. The first approach is you can modify book examples to list all records and provide “Delete Record” button for each record. Another approach is you can allow user to search for a record and delete it if the record exists and the user confirms to delete it.
  • Log out of user.
  • Once a task is finished, a link should be provided for the user to return to the main menu. Your project should also meet the following requirements:
• Your database should have two types of tables, application table(s) and a users table. You are free to decide your database and application tables (you can’t use the same publications database as the book example). If your database has one application table, it must have at least 5 fields. Your application table must have a primary key and/or other index for the fields that will be searched by the users.
• You must have a users table to store the registered user’s information including username, email, and

secured password. The username is the primary key. The password must be salted and hashed.

• Write a php file setupDB.php to create your database tables and populate application table(s) with initial values.
• The form-based web authentication, not HTTP basic authentication, must be implemented in the user login process.
• Session management must be provided after successful authentication of a user. A user who has not successfully logged in should not be granted access to any function module in the main menu (not be able to list, add, search for, or delete records). Prompt the user to log in in the case that a user accessed the main menu page without login.
• All user input data must be sanitized to prevent injection attacks. Prepared statements with placeholders must be used for “adding records” module to sanitize the user input.
• Input data for new user registration must be validated using both JavaScript and PHP with criteria similar to the book example including the format of email must be valid; no input field is empty; usernames must be at least 6 characters long; passwords must contain at least 8 characters; and passwords must contain at least one of lowercase letters, uppercase letters, and numeric digits. The password must match confirm_password.
• The navigation and usability of your project should be reasonable and applicable. Some suggestions: add a link to the user login on the user registration page and add a link to the user registration on the user login page so user can switch between sign up and sign in; a main menu page is displayed after user logs in; add a link back to the main menu after each function module is finished.
• Complete the capstone project report that includes the Self-Assessment of Capstone Project. List your files and briefly describe the purpose of each file.

(Feel free to include screenshots to show your work, such as, your web pages before or after an action, the structures of database tables, and data in the tables.)

• Include the integrity statement I certify that this submission is my own original work with your name as comments in each of your source files.

• Make a short video to present how your project works. The video presents in the following order:
• Present your name and show your Farmingdale student ID card. Then start to demo your project.
• Sign up using invalid values (email: alice, username: alice, password: mypass, confirm_password:

mypass2).

• Sign up using valid values (email: alice@abc.com, username: alice123, password: Mypass123, and confirm_password: Mypass123).
• Show all the contents of the users table.
• Go to the login page and log in with username alice123 and password Mypass123. The user should be directed to the main menu after login.
• Demonstrate “Listing records” module.
• Demonstrate “Adding records” module. Add a new record to the database.
• Display the contents of your application table.
• Demonstrate “Search for records” module. Search for the newly added record or other records.
• Demonstrate “Delete records” module. Delete the newly added record.
• Display the contents of your application table again.
• Log out.
• After log out, go to the main menu page by using its URL. Click on “Listing records” link if it is displayed.
• Sign up using username alice123 again.
• Feel free to demo other features of your project.

Extra credit:

Demonstration of creative features or styles of your application.

Hint:

A project milestone system has been designed to guide you to complete a sequence of tasks that will lead to the successful completion of the capstone project. Please see the attached project milestones document.