CS2623: Server-Side Programming
Weekly Materials - Week 11
Chapter 8: Manipulating MySQL Databases with PHP
Click on a topic to open a panel | Open All | Close All
Objectives
  • Connect to MySQL from PHP
  • Work with MySQL databases using PHP
  • Create, modify, and delete MySQL tables with PHP
  • Use PHP to manipulate MySQL records
  • Use PHP to retrieve database records
  • Explain and prevent SQL injection
  • Utilize password encryption using SHAH1 and MD5
Assignments and Due Dates

Assignments and Due Dates: View
CS2623 Coding Standards - requirements for all class assignments

Required Readings

PHP Programming with MySQL //Second Edition by Don Gosselin, et al
Chapter 8 - Manipulating MySQL Databases with PHP
Text Resources
     Errata Sheet
     Best Practices

SQL Injection

Password Encryption (also review pp.138-139 in text)

HIGHLY Recommended Tutorials

Video: Lynda.com: 
PHP with MySQL Essential Training by Kevin Skoglund

       14. Using PHP to Access MySQL
             Almost all videos
             Optional Videos:
                 Database APIs in PHP
                 Introducing prepared statements                           

       19. Regulating Page Access

        Encrypting Passwords
        Salting Passwords
        Adding Password encryption to CMS
        New PHP password functions

If you choose not to use Lynda.com there are some resources below under Recommended Materials

PHP Tutorial: Password Encryption with MD5, SHA1, and Crypt by RiverCityGraphix
https://www.youtube.com/watch?v=e9nH-nsj7mk

SQL Injection - Simply Explained by Chris Linares (YouTube.com)
      http://www.youtube.com/watch?v=FwIUkAwKzG8&list=PL15DACBB2C1DE384D

Required Sample Code
The sample code is in a file which must be downloaded and extracted to location accessible by the server. Be sure to do this before testing the code.
Recommended Materials
Some of these items are from last week but included here as a reference

Chapter 8: PowerPoint from Text             View (.ppt) | Print (.pdf)

If you need additional information regarding MySQL and PHP

Other Security Resources (For Inquiring Minds!)

Software

PHPMyAdmin and MySQL which was included with your WAMP|MAMP download will be used for work with databases.

Chapter Questions and Test Reviews

Chapter 8: Review Questions (.rtf file)
Test 3: Review Sheet

Required Assignments
  • SSP09: MySQL PHP (Complete, upload to Apollo1, then submit URL in the LMS)
  • Weekly Quiz: Chapter 8 (Located in the LMS)
Professor's Class Notes and Comments

IMPORTANT:

The sample code in MySQL.zip must be downloaded and extracted to location accessible by the server (htdocs or www). To use these examples create a database named CSCourses then import cscourse.sql. This must be done locally.

When working with databases make sure both the Apache server and MySQL are online.

When connecting to databases locally use the following defaults. WAMP does not include a password but MAMP uses root as the both the user and password. The sample files use WAMP so modifications will need to be made if working on a MAC.

WAMP: $DBConnect = mysqli_connect("localhost", "root", "");
MAMP: $DBConnect = mysqli_connect("localhost", "root", "root");

When using files uploaded to Apollo1, this must be changed to the following where apollo1_login and password are the ones for your Apollo1 account:

$DBConnect = mysqli_connect("localhost", "apollo1_login", "password");

Lynda.com Videos

The Lynda.com videos make a reference to CRUD (Create Read Update Delete)
http://searchdatamanagement.techtarget.com/definition/CRUD-cycle

Security

Security is a real issue with all of websites especially with ones housing secure data. Be sure to familiarize yourself with SQL Injection and password encryption using MD5 and SHAH1. The items in required reading, videos, and sample code is required. Additional resources are in Recommended Materials are optional. If you find other good resources, please post to the Discussion List.

Secure Coding: Download and extract the zip file. Create the databases and import the .sql files.

  • SQL Injection
  • Securing Passwords
  • Authentication

Yes, it will be on the test!

Another item of interest that goes along with this is stopping "robots" from submitting forms. I am guessing at one time or anohter you were required to enter a series of letters before proceesing. With that in mind you might be interested in reCAPTCHA. http://www.google.com/recaptcha/intro/index.html It cannot be used on your apollo account because it contains a path (/user#####). Still you might keep this in mind for future web work.

Chapter 8

p. 448
Refer to the information about regarding the user and password information for each of the servers you will be using.

p. 449
Sample Code: SSP_01_MySQL_ServerInformation.php

p. 451
SSP_02_MySQL_DatabaseConnection.php
SSP_03_MySQL_ErrorSuppression_Die.php

p. 452
SSP_04_MySQL_ReportErrors.php

p.465
SSP_07_MySQL_AddRecords.php
SSP_08_MySQL_DeleteRecords.php
SSP_09_MySQL_UpdateRecords.php
SSP_10_MySQL_LoadDataLocalInfile.php

p. 475
SSP_05_MySQL_IndexedArray.php
Note: In Figure 8-8, the License column in $Row[0], the Make Column is $Row[1] and so on.

p. 477
SSP_06_MySQL_AssociativeArray.php

p. 480
mysql_num_rows() and mysql_num_fields(0 are included in the previous two sample code examples.

 

Whew..that was a whole lot of material!!!