Post on 06-Apr-2018
8/2/2019 06 More Php More SQL
1/38
1
More PHP
More SQL
More PHP
More PHP Syntax of more advanced features
Mainly classes and objects Leaving concept of class primarily to CSCI124 (and proper use of class
deferred to CSCI204), won't be defining and using classes much inCSCI110
But you do need to know PHP syntax so you can use itsclasses later when you go on to more advanced exercises
Use with "cookies" Cookies are your mechanism for tracking visitors to your web
sites
$_SESSION Maintaining a stateful service
More SQL
Simply additional examples using tablesand queries that are a little moresophisticated than the babynames table
.
No "More SQL" section
Just examples worked into the illustrations ofPHP coding.
http://php.net/manual/en/language.oop5.php
Wikipedia summary of PHP class
evolution : 1 "Basic object-oriented programming functionality was
added in PHP 3 and improved in PHP 4. Object handlingwas completely rewritten for PHP 5, expanding thefeature set and enhancing performance.
PHP 5 introduced private and protected membervariables and methods, along with abstract classes andfinal classes as well as abstract methods and finalmethods.It also introduced a standard way of declaringconstructors and destructors, similar to that of otherobject-oriented languages such as C++, and a standardexception handling model."
i.e. PHP class definitions now are more similar to those of Java and C++ (I'd say more Java like as member functions aredefined in the class declaration while in C++ member functions are more commonly just declared and defined separately.Terms like "final" and "abstract" are also more in Java's style)
Wikipedia summary of PHP class
evolution : 2 "Object handling was completely rewritten for PHP 5, expanding the
feature set and enhancing performance.In previous versions of PHP, objects were handled like value types.The drawback of this method was that the whole object was copiedwhen a variable was assigned or passed as a parameter to amethod.In the new approach, objects are referenced by handle, and not byvalue."
That change helps. The cloning of objects on assignment made for someodd behaviours when doing things like iterating through a collection of objects!
8/2/2019 06 More Php More SQL
2/38
2
Wikipedia summary of PHP classevolution : 1
"PHP 5 added interfaces and allowed formultiple interfaces to be implemented.There are special interfaces that allow objects tointeract with the runtime system.
Objects implementing ArrayAccess can be used witharray syntax and objects implementing Iterator orIteratorAggregate can be used with the foreach
language construct.
Again more Java-like than C++ style with regard to interfaces andimplementation of multiple interfaces
Eeek!
Protected access, destructors, inheritance,final methods, abstract classes, abstractmethods, final classes, multipleinheritance, interfaces, value types,handles
Yes, well let us leave most of that.
You can read about such things onceyou've passed CSCI204
PHP classes
PHP's classes are generally similar to theclasses that you learn about in CSCI124 Not like the weird "prototype" based class system of Javascript!
For Java (in some ways, PHP classes are more Java-like than C++-like) take.
As always, the way you think about a class
isOwns
Does
Class and object
Class A compile time construct Defines what an "instance" (object created for this
class) has as data membersand provides the definition of the member functions
.
Object Created at run-time
Created on heap using "new" operator
Many different objects typically created from eachclass
Each owns their own data Each behaves in the same manner
Class Point Owns
X and Y coordinates
A "colour"
A "name"
Does Pretty prints itself
"Moves" given delta-X, delta-Y will update its own coordinates
Returns polar values via member functions Radius and Theta
Performs vector addition given another Point will construct anew point whose coordinates are determined by vector addition
And has "accessor" and "mutator" functions for the individualdata members
The project Put the class in a separate PHP file
Import that file into program with a "require statement"
8/2/2019 06 More Php More SQL
3/38
3
The class The class
Data members It's PHP so they don't have types.
But they do have access specifications
Make them private
The class - constructor
Constructor
Role
Initialize new instance of class when it is created inthe hea
Don't want the data members filled with random bits leftthere by the last program that ran.
PHP used to follow C++ style and have aconstructor named after the class
Now has to have a function named__construct() (that's two underscores)
__construct
$this->x
We are initializing the "x" member of the object for which theconstructor function is running;that object is identified by the handle (pointer) $this
The class: accessor and mutator Data members are private
Can only be read by program if define an "accessor" function
Can only be changed by program if define a "mutator" function
The class: "business methods" Methods that do the real work for which
this class was invented!
8/2/2019 06 More Php More SQL
4/38
4
Pretty print
Geometry
Manipulation
Using these operations : 1 Using these operations: 2
Change point3, well it is also point 2 so point2 got changed.$point2 and $point3 are what Java calls "Object Reference Variables" pointers to objects. The assignment $point3 = $point2 simply madetwo pointers point to the samePoint object. C++ would differ here.
Using these operations: 3 Class hierarchy The syntax is defined. Would you want to do it?
Role of class hierarchy is for defining related classeswith complex behaviours
Some behaviours can be defined in the "base class"; others" "are su cen y eren a eac separa e su -cass mus
define its own variant.
Appropriate applications Frameworks (Graphical like Java's awt and swing graphics,
Web-server like Java's servlet engine and servlets, Businesslike Java's "Enterprise Java"), Simulations, some Gameapplications
PHP frameworks like Drupal and advanced applications like MediaWikimake extensive use of classes.
But not really that likely to be needed in routinePHP scripts
8/2/2019 06 More Php More SQL
5/38
5
Class hierarchy
You can see a (very unconvincing)demonstration at php.net
They have
class Spinach extends Vegetable
Quite just what you need in the typical PHPapplication
PHP syntax for class hierarchies is similar to Java rather than C++
ttp://au2.p p.net/manua /en/c asso .examp es.p p
Cookies
Cookies
Introduced by Netscape when they converted the webfrom a "vanity press" to an effective medium forcommerce.
They provide one way (the most convenient way) of"labelling" a client so that a server can recognizesubse uent re uests as ori inatin from the same source.
Why might you want to "label" a client? To achieve "stateful" processing
This is the most important use, but you would probably not use cookies directly you would work instead with PHP's "sessions" (which generally use cookieson your behalf)
Personalization: To welcome back a returning visitor (labelled with a 'last visited time')
and provide "news of events since your last visit" To provide a basis for advertising Etc
See Amazon for a good example! "Recommendations for you"
A cookie A cookie consists of
A name
Data (up to 4k bytes of text)
An expiry date
File path to application that sent it Could have a company (www.acme.com) with "sales" and "advertising"
divisions both of which have web sites differentiated by path names(www.acme.com/sales/... and www.acme.com/advertising/...) andwhich both had applications that want to set cookies;
"path" would keep the cookies apart so those from "advertising" wouldbe distinguished from those from "sales" even if they used the samecookie name
Browser stores cookies A particular server (domain) is "allowed" to
set up to 20 cookies on a client.
Browser on client stores these' '
Other browsers may store them as simple files
Firefox used to store them all in one big text file (~2006) but morerecent versions use a "sqlite" database.
There are some rules of good cookie conduct 4k size limit, 20 cookies site limit,expiry date not more than one year ahead, etc. But nothing enforces the rules.
8/2/2019 06 More Php More SQL
6/38
6
Cookie exchange
Cookies are sent back and forth in HTTPheaders
Server sends a "Set-Cookie:" header
Browser stores the cookie
Browser returns the cookie (in a Cookie: header) withall subsequent requests to the same URL/pathcombination
Example cookie in HTTP headeras shown in wikipedia;
Looking at your cookies
Cookies from PHP
One problem:
Cookies are sent in the header
If your PHP script needs to set a cookie then thismust be done before any HTML/plain-text output issen .
Cookie setting code must be at very start of script.
Fails html sent before header
Fails blank line set before header!
setcookie
Part of the PHP standard library
setcookie Arguments
Name arbitrary, any name you want
Value a string
You can send a "negative time" this allows the server todelete a cookie that it sent earlier
setcookie Optional parameters
8/2/2019 06 More Php More SQL
7/38
7
Cookies from Javascript
Your Javascript can manipulate yourcookies
Example later PHP & MySQL
News viewer example
Example
Data table of "news" Date posted T it le URL of article
Cookie Name='LastVisit' a ue= mes amp
News Reader Application If no cookie, just print G'day If find cookie from previous visit, show list of all news articles posted
more recently And, of course, (re)-set the LastVisit cookie with current time
News Poster Application No prett ies!
I was too lazy to do much by way of CSS styling for this example
BrowserServer runningnews script
Table indatabase withnews records
SQL query retrieves anynews records more recent thandate stamp
Visitor view
Local file storagefor cookies
Date-stamp (last visit) cookie
transferred back and forth,updated by server script
BrowserServer running"add news"script
Table indatabase withnews records
SQL insert additional recordOwner view
Get:show add news form
Postinsert record
Develop applications in smallincremental ste s!
8/2/2019 06 More Php More SQL
8/38
8
Step-1
PHP script to manipulate a time-stampcookie ("LastVisit")
Get value from $_COOKIE[ ] (could be unset)
,week
If there was a value for "LastTime", welcomeback a returning visitor
Else welcome a new visitor
The code : 1
1. $visitor =
$_COOKIE["LastVisit"];
2. $now = time();
3. $oneweek = 60*60*24*7;
4. $expires = $now + $oneweek;
5. setcookie("LastVisit",
1. Index into $_COOKIE looking forour LastVisit cookie
2. Get current time as a Unix-styletimestamp
3. Seconds in one week!
4. Set ex i r data as 1 week fromnow, exp res ;now.
5. Set the cookie
1. Name
2. Value (timestamp to string)
3. Expiry t ime.
The code : 2
isset($visitor)
isset function bit like testing visitor != NULL in C++ If there wasn't a cookie named LastVisit, $visitor wouldn't
have been set
And there would be an entry in your Apache log file warningfrom PHP script
$datelastvisit = date('d-M-y',$visitor);
date function takes a format string and a timestamp
Test
Go to site "Refresh"
Develop applications in smallincremental ste s!
8/2/2019 06 More Php More SQL
9/38
9
Step-2
Data table in MySQL Posting date
Title 256 characters
Table definition
Develop applications in smallincremental ste s!
Step 3: Fill the table News poster
Might as well handle this through a web form
Nominal posting date
Title of posting
URL with details
FormForm
Import jQuery library (for the calendar)
=" " =" / "$(
function() {$("#datepicker").datepicker({ minDate: new Date(2010,0,1),
maxDate: new Date(2011,11,31)}
);}
);
Initialization code get jQuery to insert the HTML etcfor a popup calendar associated with the "datepicker"element in the form
8/2/2019 06 More Php More SQL
10/38
10
Form Just some text fields and the input text linked to the jQuery calendar code
Nominal posting date
Title
URL
New posting code
Check the data! Just get into the habit of checking everything
that comes in from the web
Here some of the data in ut the title will beincluded in a page sent to subsequent visitors
So if a "title" should happen to include Javascript, thatJavascript will be run
A hostile posting could contain malicious Javascript("cross-site posting attack")
Become paranoid
Paranoia is a useful trait for a webprogrammer
The bastards areout to get you!
Checking data
Your form only allows selection from predefinedoptions lists couldn't possibly go wrong But the hacker just read the source of your form and
created a hand-crafted input that didn't have valuestaken from your published selection.
our orm s on y use y peop e on-s e, eywouldn't attack their own site Well some might But any way, how sure are you that the form cannot
be accessed off siteand if URL of processing program is known it couldget data posted data from off-site even if the formwasn't accessible
Checking the data Title and URL
Text Length checks not too short or too long
Characters alphanumerics, white space, some punctuation
but if you see things like < or single quote then beware
URL does it look like http://something(:maybe a port #)/path
Date
Properly formatted date value
Represents a valid date
Checking strings for characters and
formats Example code will again use "regular expressions" (the
code is much shorter that way)
Tutorial http://www.phpro.org/tutorials/Introduction-to-PHP-Regex.html
Examples! You don't have to invent your own regexes!
Numerous people have needed to check whether the data givento a script looks like a valid URL so there are regex patternspublished for such common tasks
http://www.roscripts.com/PHP_regular_expressions_examples-136.html
http://www.webcheatsheet.com/php/regular_expressions.php
8/2/2019 06 More Php More SQL
11/38
11
Script step 1
Generate error page if data not accepted
Some minimal acknowledgement ofsuccessful posting
Test the data checking code
Script Step 2
Add validated data to database
Data checking
function checkurl() {$url = $_POST["url"];
// Must have some input!if(empty($url)) return false;
// Too short, too long - no goodif((strlen($url)256)) return false;
// Does it look like a URL should// The horrendous regex was found on the internet// (there are slightly simpler variants that are almost as good)
$urlpat ='!^((http(s)?)://)?(\.?([a-z0-9-]+))+\.[a-z]{2,6}(:[0-9]{1,5})?(/[a-zA-Z0-9.,;\?|\'+&%\$#=~_-]+)*$!i';return preg_match($urlpat,$url) ;
}
The regex says something like look for the string http://, or https://, (that string ishowever optional), then look for a domain name (alphanumeric substrings, separatedby ".", ending with a domain of 2 to 6 characters), then an optional port number (1 to 5decimal digits), then
preg_match is PHP's regular-expressionmatching function
Data checking
function checktitle() {
$title = $_POST["title"];
// Require some text
if(empty($title)) return false;
$titlelen = strlen($title);
if($titlelen
8/2/2019 06 More Php More SQL
12/38
12
Develop and test incrementally
Build the application with just datachecking and a success/fail responsepage
is doing its stuff.
Only then add the database component
Database part
Connect to database SQL Insert statement
You can assemble a SQL query but appendingstrings that include your data
Try to avoid this it's the programming construct mostfavoured by hackers
Whenever possible (which is >90% of time) useprepared statements
Run insert
Remember to close database connection
Connecting to database
function connectToDatabase() {
global $mysqli;
$mysqli = newmysqli('localhost','nabg','NOTMYPASSWORD','nabg');
// Check for connection errors
if(mysqli_connect_errno()) {
$problem = mysqli connect error();_ _
badinput($problem);
exit;
}
} Argument to mysqli constructor are
Host for database User name Password Schema
Alternative connection style
Often see example code in a different stylewhere connection request provides host, user-name, and password only
Then a separate step is used to select databaseschema required:
8/2/2019 06 More Php More SQL
13/38
13
Advantage of prepared statementand bind-param
If you use user-input and bind-param then thevalues from your user's input are never seen bythe SQL parser.
You can build the string by concatenation and then run it using a
$qstr = "insert into NewsTable values ('".
$title . "', '" . $url . "', '" . $mydate . ")" (note the single quote that are needed around strings in a SQL statement)
But then you are going to meet a hacker who chose a title with formlikex', 'y', '02-02-2000'); drop NewsTable; '
Your SQL parser will parse that and incorporate code to delete yourtable
Insert rowfunction
Form
Response Now go and view the data using theMySQL query browser just to checkthat the row really has been inserted!
Develop applications in smallincremental ste s!
Viewnews Pick up cookie with last visit time (if such a
cookie exists)
If no cookie, set last visit time as sometime long ago
Set last visit time to current time.
Convert last visit time to date.
Connect to database
Run a query of form select all rows whose dates> last visit date.
Print results.
But first build some "scaffolding" Have one small problem
Would be able to run the search once from web page,but then cookie is set and there won't be any newerarticles to read for subsequent searches!
First test probably won't work properly (there's bound'
error in the generated response text)
Need some convenient system for adjusting thelast visit date How about a really small web app that simply adjusts
the cookie to match a date you provide.
"Scaffolding"
8/2/2019 06 More Php More SQL
14/38
14
Get used to it you always have tocreate "scaffolding"
In introductory CS subjects (114, 124, 203, 204)you simply write a program that matches(maybe) the requirements set in the assignment
In real life, you write a program that matches therequirements but you also have to implement "scaffolding" Extra little programs that
Populate data bases Generate test sequences
(Tend to be one of reasons that most software is late estimates of the size of a project too rarely allow for theextras like "scaffolding")
setvisitdate.htmlsetvisitdate.php
HTML page Javascript
Is there are "LastVisit" cookie?
If yes, then make the page contain a form with a jQuerycalendar field;
"Scaffolding code"
the form submits data to the setvisitdate.php script
If no, then make the page say there is no need to do anything.
PHP script
Replace value of cookie with one corresponding todate entered in form
Acknowledge request.
HTML
As it uses jQuery (just for the calendar widget),the page has the usual host of include files stylesheets, jQuery core, jQuery user interfaceextensions.
"Scaffolding code"
Then a very simple page with an identified (initially no content)
Then a Javascript fragment that will be executedas page is loaded
Script sets content of either to contain form ormessage that there is no cookie to overwrite
document.cookie it's a string withall the cookies set by your site; if yourJavascript needs to check for aspecific named cookie then you will haveto parse the string;here simply need to check for existenceor non-existence of a cookie
"Scaffolding code"
PHP
"Scaffolding code"
PHP Checkdate function
Validates input does it look like a date string Converts it into a Unix time stamp (which is what we
are using as value for the LastVisit cookie) Timestamp is date supplement by time of day set that to 0th
"Scaffolding code"
"Main" Invoke checkdate
If invalid input report error
Use generated timestamp value as value for cookiethat gets reset
Report success
8/2/2019 06 More Php More SQL
15/38
15
Back to ViewnewsCouple of global variables declared.
Functions to connect to database and run search(and report results)
Basic updating (or initial setting) of LastVisitcookie.
Prepare a date string argument for searchagainst table
Other HTML output to end generated page
Searchcode
Code setting up and performingsearch
HTML table from data inrows retrieved from database
The search
Create a SQL select statement as a prepared statement
Bind value for date, Only retrieving posting more recent than this date
Run queryg o a mysq ate;
global $mysqli;
$stmt = $mysqli->prepare(
"select title,url,posted from NewsTable whereposted>=?");
$stmt->bind_param('s', $mysqldate);
$stmt->execute();
Getting the results
Illustrating a different approach from that inearlier example (babynames example) whereretrieved rows and then selected data from eachrow.
Here using "statement fetch" and "bind resultparameter"
It is just a different approach to looking at the
results and extracting column values. Both are OK to use. This version probably results in shorter code in
most cases.
"Bind result"$stmt->execute();
$stmt->bind_result($title,$url,$postdate);
$counter =0;
while($stmt->fetch()) {
}
Bind_result identifies PHP variables thatwill be set to retrieved column valueswhenever "fetch()" method of statementobject is executed
Generating the HTML table
Each row of table has Item number (actually a item link to the URL
from data table)
Date posted (in a human readable format)
Tit le
HTML rows are generated as each matching record isretrieved
8/2/2019 06 More Php More SQL
16/38
16
while($stmt->fetch()) {$counter++;if($counter==1) {
echo
8/2/2019 06 More Php More SQL
17/38
17
Picture Gallery
Persistent data in MySQL Picys table
Identifier (surrogate key, auto-increment integer)
Title - varchar(128)
" " ommen ex
Picture "mediumblob"
PicyTags table Tag varchar(16)
Identifier integer, really a foreign key referencing the"identifier" field in the Picys table (but since MySQL doesn'tenforce foreign key constraints, it's simply declared as integer)
Defining the MySQL tables
Names and passwords
There is a third table
User-name
Encrypted password
Application-identifier
Picture Gallery
Scripts1. " Index.php"
Really just a HTML page with links: V iew t it les Search by tag Add picture (only for gallery owner)
2. ViewTit les.php Retrieve and list titles of all pictures the listing includes links that
allow picture to be viewed
3. DisplayPicture.php
Called with picture identifier argument, generates page withHTML and content text title, comment and an link foractual picture
4. ImageFromMySQL.php Generates a Content-type: image/jpg file with image data from
"Blob" in database, i.e. an actual picture
Picture Gallery Scripts
5. SearchByTag.php User enters a tag, script returns list of picture titles that
again act as links to actual picture display
. . Gets and checks user name and password, required before
can use AddPicture script.
7. AddPicture.php Upload a picture along with title, comment, and some tags.
Support program CreateUser.php
Creates row in passwords table User name
Encrypted password
It's possible that will need similar username/passwordarrangements for "administrator"/"owner" role in otherexample applications.Hence use of a data table.
8/2/2019 06 More Php More SQL
18/38
18
View titles
View chosenpicture
Optionally tagthe picture witha new tag.
Tables
Tag-
string
PicId
Scotland 7
Venice 13
Scotland 30
Username Encrypted
password
Application
name
nabg 1af2 picy
Ident Title Comment Image
5 London Eye Aerial 1423
7 Storm One of 5327a2e113 Masked Carnival 7452feb
30 Edinburgh Castle 4325ebc
Application flow - visitor
Index.php [Get]
- Displays form
- Links to
- View titles
- Search tags
ViewTitles.php
- Get
- Runs code tolist titles (as links)
SearchByTag.php
- Get
- Displays form
- Post
- Runs code tolist titles (as links)
DisplayPicture.php
- Get
- Returns pagewith data
ImageFromMySQL.php
-Get
- Returns image
AddTag.php
- Post
- Adds tag
Application flow - Owner
Index.php [Get]
- Displays form
- Links to
- Add picture
Login.php
- Get
- Displays form
- Post
- Checks login
AddPicture.php
- Get
- Displays form
- Post
- Adds picture
Application flow
Administrator
CreateUser.php
- Get
- Dis la s form
- Post
- Adds user (owner)
8/2/2019 06 More Php More SQL
19/38
19
Development : 1 First
Define those tables
It isn't going to be easy to populate them with test datausing the MySQL tool Image data must get loaded
In the password table want an encrypted password
So Create program to populate username/password table
Create "login" program
Create "add picture" program usable by 'logged in' user
Checking a password get password from user, encrypt, compare with storedencrypted version.Why store encrypted versions?Because hackers have sometimes contrived to steal password files or dump contents of password tables.If the passwords are encrypted, hacker must use brute force password-guessing programs to try tofind "weak passwords".
First script
CreateUser "Get"
Form where user enters a name, a password, and a programidentifier
"Post"
Some basic data checking
Create row in PwdTable
Acknowledge
Application scripts
Many of the scripts in this little web application follow the same style Get
Display a form
Post Handle data entered in the form
Much of the database access code is the same "cut& aste" between the scr i ts
Where possible "prepared statement" style used for databasequeries (Didn't work well with operations on images there older style mysqli-
>query(sqlstring) used, with the sqlstring built by concatenating textfragments)
Data checking Again using simple regex texts
CreateUser
Check "REQUEST_METHOD"environment variable in $_SERVER.
Call appropriate function.
CreateUser
Standard form, action='$phpself' postback to same script.Don't show input to "shoulder surfers"
Handle create : 1; data checking
Pick up posted data
Note referenceto global variable
If any required data were missing,bounce user back to the login form.
Check that inputs are alphanumericand of approved length.
8/2/2019 06 More Php More SQL
20/38
20
Handle create : 2; do the work!
Connect to database; use a prepared statement to enter data (reducing riskof 'SQL injection attack'); execute statement; check for errors.
Encrypting the password?Various choices; here use PHP's md5() function. It creates a string of32 hexadecimal characters as a "hash" of supplied password. There isno practical way of reversing the hash and getting back the passwordfrom the 32 digit string. Hashed password string is what gets stored.
Handle create : 2b; databaseconnections
More or less the same code in several ofthese scripts so here it is as a function
Handle create : 2c; reporting badinput
More or less the same code in several ofthese scripts so here it is as a function
Handle create: 3: report
Database insert failed?Just report the error.
Database insert success?Acknowledge action.
Remember to tidy up, closingconnections etc.
Handle create Now we have some users registered as
able to add pictures.
Controls on CreateUser Realistically, would need some controls on use
of "create user" script!
Otherwise any hacker could simply create a useraccount and start uploading pictures!
Controls
E.g. don't have user name and password defined incode of connectToDatabase;ask for them to be supplied in the create user form.
Possibly use https as well
8/2/2019 06 More Php More SQL
21/38
21
Login and $_SESSION
In this example will let PHP handle cookies forus. Login mechanism
User invokes login script and gets form Data submitted are checked Encrypt (md5) password supplied Check PwdTable does this combination of (user-
name, encrypted password, and "Picys" as tablename) exist
If not bounce back to login If yes, start "session"
Store user name in session
Acknowledge
Login script
Standard!Check whether it is "Get" of "Post"
if get, show form.If post, process data.
Same "badinput()", and"connectToDatabase()" functions.
Similar global variable for databasehandle.
Display login form
Standard!Form element posting back to
$phpself (i.e. this script).
Some layout provided for formfields(maybe and but more often ).
Two input fields and a submit button.
Handle_login function
Check the data
Handle_login function Prepare and run the query
There should be exactly one row with the same combination ofuser-name, encrypted password, and application name.
$mysqli->prepare("select count(*) from PwdTable whereusername=? and cpasswrd=? and mytablename='Picys'");
Handle_login function Response If valid, reponse page is simply a form
letting user view pictures, search, add,
But if data were invalid, report failure.
Oh, and remember to tidy up by closing the $mysqli connection!
8/2/2019 06 More Php More SQL
22/38
22
MySQLI functions being used
MySQLi prepare
bind_param
_
execute
MySQLi_STMT
fetch
session_start() and $_SESSION
session_start() PHP will create a unique session identifier and place this in a
cookie on client
PHP also creates a $_SESSION hash array that it will maintainin another hash array inside PHP engine itself;the session identifier laced in cookie is ke used to retrieve oursession data.
On all subsequent calls, PHP will find our session data inits hash array.
Here only making limited use of session data Login places username in session
AddPicture script checks for a username record if not found itbounces user back to this login script.
session_start() sets a cookie in header; so must run before any html output!
AddPicture
Checks that user is "logged in".
Get Form
Enc-type multi-part form data; this is a file upload
Action post back to same script.
Input fields for title, comment, the file, and a comma-separated setof "tags"
Post
Validate No nasty embedded scripts in title or comment please; no other
troublesome characters.
Insert data via a mysqli->query() operation
Report success (or failure)
AddPicture - mainlineGlobal database handle, reportfunction for bad input, and functionto connect to database are sameas in earlier scripts
Using session so again session_start();check that there is a record showing user
logged in else divert to login page.Simply send a Location header browserwill handle the diversion.
Then the usual if "GET" show form,if "POST" process data.
A simple form for file upload
with additional textarea and textinput fields.
Enctype for multipart/form-data;Hidden field specifying 1Mbyte
maximum upload;
Textareas for title and comment.
- only want jpeg files.
Text input for comma separated listof keywords.
Submit button.
Function add_picture : 1 First steps
Did file upload work?
Do we have a title and comment?
If errors during file upload,report problem and exit.
8/2/2019 06 More Php More SQL
23/38
23
Function add_picture : 2a
Next Check title and comment for hacker attacks
Function add_picture : 2b
Next Read all bytes in the upload file and prepare for insertion intoMySQL
File I/O
PHP's file I/O is closely related to the FILE* stdiolibrary of C You will meet this in CSCI212 (and maybe in CSCI204)
fopen opens file, returns "filehandle"
fread read specified number of bytes
Function add_picture : 3
Database stuff
Connect
Create a "SQL query" actually an insert
Execute the query i.e. insert the picture.
Note the null it's MySQL thatassigns the identifier using anauto-increment integer field.
Doing it with mysqli Its a bit fussier.
Insert data with a null for the binary data.
Then use the send_long_data() function to sendthe binar data in a second ste
If the image is large, then you may need a loop thatsends successive chunks of bytes
Seehttp://oswaldatwork.thetaoofamp.com/2009/11/php_s_mysqli_extension_storing/
Function add_picture : 4 Errors on insert?
Send an error response page.
Success?
Get index number of picture
Send an acknowledgement
Insert any "tags" into the tags table
8/2/2019 06 More Php More SQL
24/38
24
Error report or success acknowledgement
Reporting the number of the newrecord.
What identifier was assigned?
Using an auto-increment integer for the key soit's the database that determines key value.
What was the key assigned?Finding that is hopelessly database dependent!
.
With MySQL you can immediately run a query of theform$mysqli->query("SELECT LAST_INSERT_ID()");
and get the value from the result of this query.
AddPicture finally, those tags AddPicture finally, those tags
"explode()" breaks string at delimetercharacters (here ",") creating array ofstrings
" , ,storm", becomes a string array "Scotland",
"mountains", "storm" Isn't it nice that you don't have to worry about allocating space likethose poor sods coding C++
Check that the tags don't contain anything nasty.
Web application so far Now have ability for "owner" to
add pictures to the database (along with title andcomment data)
add initial tags to the tag table
Next application
View all titles Simply generate a HTML table with all titles for all entries in
the data table
Also links to another script (not yet written) that will display aselected picture and all its other data.
It really is just a HTML page with a little bitof embedded code.
8/2/2019 06 More Php More SQL
25/38
25
ViewTitles
Connect to database Run select id & title from Picys no
"where" clause, we want them all.
Process 'result set'
Write each retrieved row as a row of a HTMLtable
Note how a link to display program, with aquery string argument, is being used
DisplayPicture &ImageFromMySQL
DisplayPicture script Get request, query string will have identifier of picture that is to be
displayed You can see why a simple integer surrogate key is kind of useful
sometimes!
Connect to database Retrieve title and comment for picture
Compose HTML response page with title and comment
Need an link will have to reference a script that returnsjust the jpg image data
Oh, and add in existing tags and a form that allows new tags.
ImageFromMySQL Return image data
This is pretty standardized, so deal with it first
ImageFromMySQL
Set the content type toappropriate image type.
Pick up identifier argument.
Connect to database,run simple query to get
image bytes of chosen image.
Return those bytes
DisplayPicture
HTML with a little bit of embedded PHP
3 parts
Picture display Code to pick up identifier argument, connect to database,
and retrieve title and comment
HTML markup for display area with title, link to image,comment
Tag display Retrieve all existing tags, and list them
Form Form for adding a tag
We have finished with first query (the one thatgot title and comment), so close that and start
a new request to get tags.
List all existing tags
Final part of page generated Form for adding a tag, to be handled byan AddTag.php script.
8/2/2019 06 More Php More SQL
26/38
26
Web application so far
Owner can add pictures along with title,comment, and initial tags
Anyone can view titles
What's left? Search for pictures with particular tags
The code to add more tags entered in thepicture display page
Search by tag
Get request Display form allowing user to enter a tag
Post request Handled naively!
Needs to merge data from PicyTags table and Picystable
Done by running multiple SQL requests
Learn how to do it properly in CSCI235!
What it does Gets data from PicyTags table, builds up array containing
identifiers of all pictures that have specified tag
Loops through this array, fetching data from Picys table togenerate response data as HTML table
Search by tag
Standard!If "Get" then display form.If "Post" then process data.
Search First stage
Pick up user's chosen tag
Connect to database
Get identifier from PicyTags where tag equalsuser's choice
The data are assembled into an array
8/2/2019 06 More Php More SQL
27/38
27
Search by tag
If didn't get any matches generate asimple report page
Search by tag
If get some matches Will output a page similar to that from ViewTitles
Start by outputting page header
Prepare another SQL request this one gets title andcomment given picture identifier
Loop through collection of picture ids that wereretrieved for tag,
Run query to get data
Output row of HTML table
Tidy up, close connection etc.
Page header, and new statement.
Fetching rows from PicysCreating rows in HTML table
Tidying up etc
Oh yes, remember to close database connections
Web application so far Owner can add pictures along with title,
comment, and initial tags
Anyone can view titles
Anyone can view selected picture
Search for pictures with particular tags What's left?
The code to add more tags entered in the picturedisplay page
Left as an exercise for the reader!
8/2/2019 06 More Php More SQL
28/38
28
Bigger than CSCI114Bigger than CSCI124
OK, a slightly larger application But code complexity less than most of the
C++ exercises
n s a more ncremen a approac
Build and test each part
Move from simple to advanced.
It's also a bit more interestingand impressive than the typicalC++ exercise.
Session state
Some of this mentioned before re-iterating as get into realistic
session state examples.
Original HTTP (
8/2/2019 06 More Php More SQL
29/38
29
HTTP Authentication : 2
Browser sends user entered data from itsauthentication dialog to server User-names and passwords are held on server
Location is specified by a configuration file, it can bedifferent for each controlled realm (subdirectory)
Name, encrypted password combinations are held either in atext file or a simple database
Server checks user inputs against its records forthat "realm" If no match, repeats authentication challenge and
browser redisplays the dialog If match, server returns the page from the controlled
realm that was originally requested.
HTTP authentication : 3
Browser records the triple: Data supplied in dialog User name Password
"Realm" the URL path for the directory containing
authentication challenge.
Browser sends the name, password combinationin authentication HTTP headers in allsubsequent requests for other files from thesame "realm" Server checks every subsequent request, re-
examining the name/password combination
HTTP authentication : 4
Illusion of stateful service
"Logged in" state.
pp ca on scr p s can re r eve e og nidentifier from the $_SERVER[ ]
environment data.
State via HTTP authentication
Since application can get "user name", thiscan act as an identifier key for state datastored in a database or in server memory.
HTTP authentication : 5 Useful in some limited contexts
Example
Student access to files for a subject at university User-names and passwords allocated when student
enrols,i.e. before access is needed.
Not really a practical solution for majorityof stateful services
Not very secure either.
So how to maintain state Hidden fields in forms?
Problem of state maintenance in an essentiallystateless environment is much older than the web
Similar problems occurred with the mainframe basedtransaction processing systems like CICS
CICS script displays a form on a special terminal Data entered CICS script (in COBOL) processes data End of transaction next request is completely independent
But suppose the application required more than one form? CICS style solution
Script that checks data from first form, hides the checked data ina subsequent form that gets displayed.
Script that checks data from 2nd form receives all the data rechecks earlier data and processes new data.
It's like Berners-Lee's"fetch one file" model
8/2/2019 06 More Php More SQL
30/38
30
Hidden fields
Services based on HTTP and HTML quicklyadopted the same model
Script that processes first form ("Tell me what showyou want to see and day") hides these data in secondform page ("The following seats are available, pick what youwant") that is then displayed to user
Data from second form submitted to aprocessing script that then has all the input data.
Hidden fields
Mechanism works Problems State data (information entered in forms) being
transferred back and forth between client browser andserver
Data transfer may get "eavesdropped" (on network, incached files etc)
Data entered in form-1 and hidden in form-2 may bedeliberately altered
Scheme only works if model is fill form-1, now fillform-2, now fill form-3
Too restrictive, most web applications have more diversenavigation rules
Cookies for state
Netscape cookies Added as a header field in HTTP protocol
Name=value
Could be used to transfer state data back andforth between client and server
Data entered in form-1 would be held as value of a
cookie, cookie returned with form-2 etc It's the same as hidden fields, but eliminates
restrictions on navigation; you aren't constrained towork form-1, form-2,
But still disadvantage data can be seen/altered.
Cookie as data
Illustrated with LastVisit time-stamp cookiein the "news" application
Cookies as key Better use is cookie is simply an identifier
key Identifier placed by first server script, returns
to all subsequent scripts
State data held on server side Cookie value acts as key
Data held in memory (in something like a hashmap), cookie value is key into hash map
Data held in database table, cookie value isprimary key for record
"Login state" with cookies Illustrated in "Picture Gallery" application
the AddPicture component
Use your own login form (rather than the browserdialo associated with HTTP authentication
Receive and check name and password
If combination ok, place session cookie onbrowser value is just an id,
Only "logged in" status being maintained
Check for cookie in later scripts
Code didn't explicitly handle the cookie done indirectly via calls to session_start() etc
8/2/2019 06 More Php More SQL
31/38
31
Multiple cookies
Most often have multiple cookies Long-term cookie for recognition of returning
customer and personalization data
session closed)
Just a key
Current session data held on server side
Typical session cookie usage
Session identifier Key for hashmap in memory, or identifies file,
or acts as key for tables in database Java servlet engines session hashmap is in
memory
PHP works with files (hidden from applicationprogrammer)
Store anything you want in the private hashmap (as set of key/value pairs username/Smith,address/4-10 Cliff Rd,)or as column values in database tables
In memory, files, or in database
Whichever way there are problems. Database
Heavy load place on your database Though you can always use different database engines
1. One for read only information (updated from primarydatabase by a non-web application)
2. One for these temporary session tables3. One for your real data
Need to clear out rows for completed and abandoned
session Completed ("proceed to checkout, place order, confirm")
temporary session data transferred to more permanent ordertable, session data deleted
Abandoned untouched for 30 minutes, "trigger" action indatabase engine deletes row
In memory, files, or in database
Whichever way there are problems. Files
Need to automatically delete discarded files Where are the fi les?
If local to server machine have same problem as with in memory
In memory, files, or in database Whichever way there are problems.
Memory In some ways easier, just use your session hash map of
name value pairs that is stored in the PHP engine's hashma user-id =>session record
Completed delete your session data
Abandoned handled by PHP engine, configurationparameter will give life time limit for session data thathaven't been used recently
The problem Server farms!
Server farmsServer pc? Well, more likely 100s of them!
8/2/2019 06 More Php More SQL
32/38
32
100s (sometimes 1000s) of PCs
Client requests distributed across large numberof PCs that share an internet address
Client Client Client Client Client Client Client
Firewall
Switch
Server PCServer PCServer PCServer PCServer PCServer PCServer PCServer PCServer PCServer PCServer PCServer PCServer PCServer PCServer PCServer PCServer PC
browser browser browser browser browser browser browser
internet
Session data in memory
If session data are to be stored in memory, then it isessential that subsequent requests get switched to thesame computer.
Normally handled largely by hardware Schemes like distributing requests to server machines on basis
o c ent s a ress hopefully this doesn't change during a session
Not something you need to be concerned about yet(though you do need to be aware of the issue) For advanced systems administrators and network managers
Paranoid web surfers
Another problem
People who disable cookies!
gn can percen age o we users setheir browsers to refuse cookies
They don't want their web visits to be tracked.
They also disable all stateful services.
No cookie? No session id!
How can we get browser to return asession identifier if cookies are disabled?
Put the identifier into the URLs of all linksback to our site.
"URL rewriting"
Session id in URL :1 So this user refuses cookies
Instead of sending a cookie (oursessionid=3af1672049) alongwith a page with entries like:
View contents of shopping cart
Send a page with entries like:
View
contents of shopping cart
Session id is typically a 32 character hex-string.
Session id in URL :2 Web server engine (Apache, IIS, etc) can
be configured to watch for those sessionkeys in incoming URLs
,can be found
Key is made available to script via$_SERVER[ ] environment variables.
8/2/2019 06 More Php More SQL
33/38
33
Session id in URL :3
Only problem is the need to "rewrite" the URLsof all links in dynamically generated pages
Chosen session key must be embedded into URL atappropriate point
PHP engine can partially automate this.
We will ignore the issue for rest of CSCI110
Assume that there are no paranoid users of CSCI110web sites and so cookies will always work.
PHP & MySQLExam le 3
The ubiquitous shopping cart
Apologies to all those who publish their copyrighted photos at pbase.org, butwe've decided to sell copies of your photos to our clients.
Just an extension to the PictureGallery example
Extra scripts
BuyPhoto Linked from page produce by DisplayPicture script
Gets additional data photo-size and style ('canvas', 'gloss',' '
Adds data to "shopping cart" maintained in $_SESSION
ViewCart Displays contents of customers shopping cart
Checkout
Not actually implemented! Just referenced in links in some of thepages now displayed.
Extra links in the page f rom DisplayPhoto.php
Viewing the cart
$_SESSION and session_start Scripts using the shopping cart are
BuyPhoto.php
ViewCart.php
Checkout.php
These scripts all start with session_start() First time this is invoked:
a magic session identifier is created
The identifier is set as a cookie labelling the client
An entry in the PHP engine's hash-map of session data is created,session identifier acts as key
When cookie is encountered in header of a subsequent request: PHP engine finds the appropriate session data in its hash map and
makes these data available to script in $_SESSION[ ]
Last used time of data is updated
BuyPhoto Get
Called via link in page generated by DisplayPicture.php
Query string argument with identifier of photo
Displays a simple form Labels --- "purchasing copy of (title) "
e ec or s ze
Select for style (canvas, gloss, matt print)
Post Limited data checking
Adds a record to the 'shopping cart' data kept in $_SESSION
Displays a simple acknowledgement page with links to ViewCart,Checkout, ViewTitles, SearchByTag etc
8/2/2019 06 More Php More SQL
34/38
34
ViewCart
Get Invoked via links in pages generated by
DisplayPicture and BuyPhoto
Retrieves 'shopping cart' data from$_SESSION
If no such data? Returns a page saying that cart is empty
If data found Displays a simple HTML table with entries for each
item in shopping cart
Shopping cart data
Item count number of photos to be purchased Saved in $_SESSION$_SESSION['items'] = value-of-item-count
Items Identifier Tit le Size Style ? How?
Use a PHP key=>value array$purchase = array ("ident" => $ident,"title" => $title,"size" => $picsize, "style" => $style );
Create an identifier for it - $itemid = "Item" . $itemcount; Store in $_SESSION$_SESSION[$itemid] = $purchase;
BuyPhoto.php
session_start() changes HTTP headerso must be invoked before any output.
Some old favourite functions like "badinput()",and a few new functions.
The usual if "Get" then display form,if "Post" handle data from form.
BuyPhoto session setup
session_start();
$itemcount = $_SESSION['items'];
if(!isset($itemcount)) {
$_SESSION['items'] = $itemcount;
}
If this is first use of session data, there will
be nothing in the $_SESSION hash map. Create some data just the itemcount for
now.
BuyPhoto data base Database?
Script connects to database every time
Really only needed in "GET" requests wheredatabase is accessed to retrieve title for photo withspec e en er (even this could have been avoided, couldhave had DisplayPicture pass the title as a second argument)
GET - 1Check inputs! Expecting anidentifier number.
Pick up title from database
This time I remembered to urlencode the title I'mplacing it (in a hidden field) in the form that I generate;values for such pre-set fields should be urlencoded toavoid any problems with odd characters.
8/2/2019 06 More Php More SQL
35/38
35
GET - 2
Generate the HTML form page - it posts data back to thisscrp ;- it has a form+ id and title of picture are
included in hidden fields+ Style three choice select+ Size four choice select
Handling the purchase request
Check inputs Create the record of purchase item
Add item to $_SESSION
Generate response page
functionhandlepurchase()
Checking inputs "white list" checking
Check input values against the known permittedvalues
Updating $_SESSION$purchase = array (
"ident" => $ident,
"title" => $title,
"size" => $picsize,
"style" => $style
);
$itemcount++;
$itemid = "Item" . $itemcount;
$_SESSION[$itemid] = $purchase;
$_SESSION['items'] = $itemcount;
ViewCart : 1Pick up the shopping cart data
Oooh it's empty. Someone musthave done 'View Cart' before pickingany photos for purchase.
8/2/2019 06 More Php More SQL
36/38
36
ViewCart : 2
Generate page with HTML table listingcontents of cart
Getting the data from the cart
for($i=1;$i
8/2/2019 06 More Php More SQL
37/38
37
ItemClass.php ShoppingCart.php
Programming with class
BuyPhoto.php
Picking up the ShoppingCart object from$_SESSION
Using the ShoppingCartobject and a new ItemClass
object
Programming with class
ViewCart.php
What about "checkout"?
The Checkout.php script Someone has hired you to create a real
PHP based site to sell stuff on theInternet?
Really?
Congratulations.
Now you are asking how the site shouldhandle the business of getting money
8/2/2019 06 More Php More SQL
38/38
PayPal
As of mid-2010, your best choice would be to setup a business account with PayPal
PayPal will handle the payments for you (for asmall commission of course)
PayPal site has a section on how you link yoursite into their payments system
2011 same recommendations.
PayPal's web page lets you choosefeatures that are to go into your page;
it then generates the HTML that youwill require.
Generated code for your page
" " " " " "= = _ =