PHP & SQL

20 to 30 days

Overview

Students will learn basic PHP syntax in order to coordinate storage and retrieval of data to the database. Think of PHP as a controller that will coordinate what to send to the user, and what to send to SQL for storage or retrieval of data. SQL will handle the CRUD! (Create, Read, Update, and Delete data in the database). Notice that none of the PHP or SQL is visible to the user because all of the code resides and is run on the server. This is called "server side scripting". The upside is security, but the potential downside is the speed of the site, especially when a site gains thousands, or even millions of users.

PHP and SQL run on a web server

Since PHP and SQL are "server side" languages, they only run on a web server or an environment that simulates a web server. Typically, a "backend web developer" would install special software on their computer to simulate a web server while they create their websites. (XAMPP is one example of this). This is known as a LAMP stack:

Linux - the operating system
Apache - the web server software that enables http communication
MySql - the database and SQL capability to create, read, update, and delete data in tables
PHP - the programming language that will handle incoming http requests, process instructions for what to do with the request and any data, and formulates a response that is sent back to the user.

One side note - NodeJS has gained popularity as an alternative to PHP. Essentially, it uses Javascript on the server to perform the job that is done by PHP. If you are more familiar with that workflow, it would be perfectly acceptable to use that as the basis of instruction for back-end programming. The goal is for students to gain a general understanding of the full cycle of the user/server data manipulation and storage on the internet.

The challenge for us:

Creating a webserver environment at school presents some security challenges for the FCPS network. We work closely with FCPS tech services to find acceptable virttual work environments where we can have students program a web server in a way that does not compromise their own PII, while also protecting the school network while our students learn some advanced, and potentially sensitive, computer concepts.

To that end, installation of XAMPP is not an option. We have been using the website codingrooms.com which included a virtual LAMP environment, however, they have stopped supporting k-12 educational institutions. Our latest exploration is with the site Juicemind.com which is quickly under development as a replacement for codingrooms.com. At this point, they have not included LAMP as an optional virtual environment. We will continue to explore other options, but if an acceptable solution is not found, there is still value in talking about the concepts in the abstract, and assigning tutorials for students to learn.

SQL basics

Teacher Resources

The tutorials on Codecademy or Khan Academy provide a great overview of working with SQL without needing to incorporate PHP or a web server. It is important to understand that SQL and SQL databases only work on a server. It will not work on a regular computer without special software.

Student Resources

Khan Academy Intro to SQL

PHP basics

Teacher Resources

It is important to understand that PHP is a "Server Side Language". In other words, it will not work on a regular computer without special software. It needs to be run on a web server.

PHP is similar to Javascript, Java, and other languages in that you use many of the same concepts: variables, conditional statements, loops, etc. Some of the syntax is slightly different, but students that have taken FoCS should pick it up fairly quickly.

Student Resources

Codecademy PHP tutorials

Grocery List Full Site

Teacher Resources

This project will implement the full "back end" of the grocery list "app". Students will create 2 new "front end" pages: one for a new user to sign up, and another for a user to log in to the site. All pages must be saved as .php files (note that any .html file can be saved as a .php file and work perfectly as is).

The new user page should be a form that has inputs for:

First Name
Last Name
E-mail
Username
Password

Note that the variable names that are passed from the form as a $_POST variable come from the name attribute of the input.

The login page should have, at the very least, username and password inputs as well as a link to the new user page.

At the end of the project, users should only be able to get to the grocery list page if they have entered a valid username and password combination, and they should not be able to get back to the list if they have clicked the "log out" link, which should be at the top of the list page.

Student Resources

New User

1_newUser.mp4

3. Handling New User Results

3_handling new user results.mp4

5. Log Out

5_log out.mp4

7. Adding to the Master List

7_addingToTheMaster.mp4

9. Saving List Items

9_SavingMyItems.mp4

11. Deleting Items from the List

11_delete items from DB.mp4

2. Membership

2_membership and mysql.mp4

4. Login

4_login.mp4

6. Price List Back End

8_uniqueRandomIds.mp4

8. Unique, Random ID's

8_uniqueRandomIds.mp4

10. Loading a Saved List

10_loading a saved list.mp4

PHP and SQL Security

Teacher Resources

This is a great video that explains some of the ways that hackers can break into a system. In fact, there are plenty of sites still out there that have poorly written SQL and JavaScript that allows for some pretty easy hacks. Why teach this to students? For the same reason we would explain how thieves can break into your house. If you don't know how they do it, you can't effectively prevent a break in. Teach students to use their powers for good not evil!

Once students have watched the video, have them try to hack our Grocery List site. We have shown them how to properly write SQL with something called a "prepared statement". This helps to prevent an attack called "SQL Injection". We will temporarily modify our SQL with some bad form in order to show them how SQL Injection works. We will then revert our SQL and, in addition, put a little more JavaScript to prevent "cross site scripting" or "xss".

Student Resources

Watch this video - SQL hacking

SQL Injection

Replace your validate user function in your MySql.php file with the function below. You should now be able to do some SQL injection on the login page for your site.

function validateUser($un, $pwd){

$query = "SELECT * FROM users WHERE username = '$un' AND password = '$pwd'";

$result = $this->conn->query($query);

if ($result->num_rows > 0) {

// output data of each row

while($row = $result->fetch_assoc()) {

echo "id: " . $row["id"]. " - Name: " .$row["firstname"]. " " . $row["lastname"]. "<br>";

}

} else {

return "0 results";

}

XSS (Cross Site Scripting)

Instead of a normal grocery item, type in the following line of code

What happens when you refresh the list? Is there any way for the user to fix the problem? What needs to be done?

Do some research on HTML special characters and find out how to "escape" these characters when they are entered into your form. Fix either your JavaScript or your PHP so that hackers can no longer insert a working script into the list.

Reflection

When you combine these two vulnerabilities, it is possible, and even quite easy for a hacker to get an entire list of users, passwords, or anything else in the database, especially if the information in not encrypted! They can send themselves a valid login session cookie from when you log in, which would allow them full access to everything the valid user is able to see or do on the site. Maybe this isn't a big deal for a grocery list... but what if this was a banking site? What about users that used the same username and password that they also use for their Bank of America account? See how easy it is to lose everything when you are not careful online!

Additional Resources:

Report abuse