Lab: LAMP Apps

Overview

This lab walks you through using Linux, Apache, MySQL and PHP (LAMP) to create simple, yet very powerful PHP applications connected to a MySQL database. For developers using Windows, the acronym becomes WAMP (Linux is replaced by Windows). The basics of inserting, updating, deleting and selecting from MySQL using PHP forms will be provided. Some "bad" security practices that lead to SQL injection vulnerabilities will be exposed as well as some techniques to mitigate these issues.

Learning Outcomes:

At the completion of the lab you should be able to:

1. Insert data into a MySQL database using PHP forms
2. Query existing data in a MySQL database using PHP forms
3. Delete data from a MySQL database using PHP forms
4. Update data in a MySQL database using PHP forms

Lab Submission Requirements:

After completing this lab, you will submit a word (or PDF) document that meets all of the requirements in the description at the end of this document. In addition, your LAMP application and all associated files should be submitted.

Virtual Machine Account Information

Your Virtual Machine has been preconfigured with all of the software you will need for this class. The default username and password are:

Username : umucsdev Password: umuc$d8v

MySQL Username: sdev_owner MySQL password: sdev300 MySQL database: sdev

Part 1 - Insert data into a MySQL database using PHP forms

In this exercise we will create a small table in MySQL and then use a PHP form to insert collected from the user into the form. We will first use a technique very susceptible to SQL injection and then a better approach using prepared statements.

1. Assuming you have already launched and logged into your SDEV32Bit Virtual Machine (VM) from the Oracle VirtualBox, pen up the terminal by clicking on the terminal icon.

2. To start the MySQL database type the following the terminal prompt: mysql -u sdev_owner -p

When prompted for the password enter sdev300

3. To display the available databases type the following at the mysql prompt: show databases;

4. The database we will be using for this course is sdev. To use this database, type the following at the mysql prompt:
use sdev;

5. To display the current tables in the sdev database, type the following command at the mysql prompt:
show tables;

You may already have some tables in your database. If so, the names of those tables would be displayed. If not, you would see Empty set as illustrated above.

6. Create a Students table in the SDEV database, if one does not already exist:
use sdev;

// Create a student table CREATE TABLE Students (
tychoName varchar(30) primary key, firstName varchar(30),
lastName varchar(30), eMail varchar(60)
);

7. Next, we will create the PHP code that will provide an HTML form and response for entering data into the database table from the form. Type, or copy and paste from the code examples, the following code into your text editor and save as InsertApp.php. This code has many components including the use of PHP classes, reading parameters from files and other functionality. The code is relative long and may take some experimentation and analysis for full understanding. You should review and tinker with all aspects of the code to become comfortable with the functionality.

8. To run the code place the file in a week7 folder in the appropriate location on your VM and launch it. Note: Be sure to create a parms folder and place the dbparms.txt file in the folder or your application will not connect to the database.

9. Add an entry to verify a student was successfully entered.

10. Note the following code is assuming you have honest users.

11. Replace this with a prepared statements to help mitigate the SQL injection in the insertStudent function:

12. Note the bind statement is using "ssss" representing 4 strings. Other options include i for integer and d for double. We will use the prepared statement in the remaining examples.

Attachment:- Lab_LampApps.pdf