Course Notes for
CS 1520
Programming Languages for
Web Applications
By
John C. Ramirez
Department of Computer Science
University of Pittsburgh
• These notes are intended for use by students in CS1520 at the
University of Pittsburgh and no one else
• These notes are provided free of charge and may not be sold in any
shape or form
• These notes are NOT a substitute for material covered during course
lectures. If you miss a lecture, you should definitely obtain both
these notes and notes written by a student who attended the lecture.
• Material from these notes is obtained from various sources, including,
but not limited to, the following:
 Programming the World Wide Web, various editions, by Robert W. Sebesta
 Online documentation at: http://www.php.net and in particular the PHP Manual at
http://www.php.net/manual/en/
 PHP and MySQL Web Development, by Luke Welling and Laura Thomson
(Developer's Library)
 The Web Wizard's Guide to PHP by David A. Lash (AW)
2
Goals of the Course
1) To understand and be able to program
web servers and web clients to
accomplish useful tasks
Web servers are ubiquitous and many / most
organizations now have elaborate /
interactive web sites
These web sites require programming on the
server side
• Scripts that execute on the server and return
•
resulting documents to the client
For server-side scripts we will primarily utilize the
PHP language
3
Goals of the Course
These web sites require programming on the
client side
• Scripts that execute locally on a user’s machine
•
•
•
via the web browser
For client-side scripts we will primarily utilize the
Javascript language
We will also utilize JQuery, a library of Javascript
functions that simplifies much of the client-side
programming
We may also look at an API, such as Google Maps
that can be integrated into our sites
4
Goals of the Course
These web sites often require interaction
between web servers and web clients
• More and more sites contain resident client code
•
which accesses the server asynchronously and
dynamically updates the client page
We will utilize DOM and AJAX for this interaction
– Document Object Model
– Asynchronous Javascript And XML
5
Goals of the Course
2) To examine and understand the
differences in various programming
languages, focusing especially on those
geared toward Web programming
Many programming languages are used for
general and Web programming
• See just the previous few slides for examples
How do these languages differ?
• Why is one better suited to a specific purpose
than another?
• Now some more fine-grained goals:
6
Goals of Course
• To learn and utilize the PHP language,
and its applications
PHP syntax, constructs and basic language
features
Using PHP for server-side embedded scripts
• Writing and processing HTML forms with PHP
• Access and utilizing system state variables
• Maintaining individual states via cookies and
sessions
Regular expression handling in PHP
Object-oriented features of PHP
7
Goals of Course
Using PHP for Web database access
• With a MySQL database
– We will cover the very basics of using MySQL and
DB development as well
– Simple database setup and querying
– We will leave more interesting DB issues to the CS
1555 course
Simple authentication and authorization
8
Goals of Course
• To learn and utilize the Javascript
language in conjunction with html files
Learning the basics of the language
• Quickly
Using Javascript in conjunction with DOM to
add dynamic content to web pages
• Ex: Doing client-side processing of forms
– Checking form input, dynamic formatting, etc.
Using AJAX techniques to make web apps
more like stand-alone applications
• Updating a web page without requiring a full
refresh
9
Goals of Course
• To learn and utilize the JQuery library
Many client-side actions can be simplified
greatly utilizing JQuery rather than straight
Javascript
JQuery has many powerful plugins
• To learn and utilize XML and how it can
be used in Web applications
Basic idea / syntax of XML
Parsing / formatting XML documents
10
Goals of Course
• If time permits…
To learn the basics of the Python language
• Python is growing in popularity as both a general
purpose and web programming language
To look at an API such as Google Maps and
how it can be integrated into our sites
To examine mobile web apps and how they
differ from traditional web apps
• Content and formatting differences
11
Lecture 1: Getting Started
• What is assumed of you:
You should be familiar with Java and have used
it in CS0401/445
You are expected to know object-oriented
programming and basic event-driven
programming
You are expected to know the basics of html
• What is not assumed of you:
Detailed knowledge / experience with any of the
languages we will be using
12
Lecture 1: Intro. to Web Servers
• How do Web Servers work?
Client specifies document at a specific web
address that is desired (specified by a URL)
• Ex: http://www.cs.pitt.edu/
If the document is HTML or text, the server
simply forwards it back to the client
• If it is text, it is shown unaltered in the browser
• If it is HTML it is rendered in the client's browser
– HTML tags are interpreted and result is shown to
the user
– For info on HTML/XHTML, read Ch. 2 in the
Sebesta text
13
Lecture 1: Intro. to Web Servers
However, the requested document may be
an executable script, or it may be HTML with
an embedded script
• The script could be written in any of many
different web scripting languages
In these cases, the server executes the script
• If the entire document was a script, the server
simply sends the output back to the client
• If the document had an embedded script, the
script sections are replaced with the output and
the modified document is then sent to the client
14
Lecture 1: Intro. to Web Servers
Note that the client never sees the
server-side script code
• This is important – typically client should not see
•
logic / code that the server executes to process
requests
The server may be accessing files whose names
should not be seen, or preprocessing data that it
does not want the client to see
See ex1.txt
See ex1.html
See ex1.php
15
Lecture 1: CS 1520 Web Servers
• In order to be useful, web servers need
access to the directories from which they
serve and process files
Minimally the server must have read access,
but in many cases, write access is also
needed and/or useful
• In case server creates / modifies files
– Ex: User submits a file
– Ex: Various user data is stored / updated in a file
• This leads to an issue for a course like CS
1520:
16
Lecture 1: CS 1520 Web Servers
How do we give the server access to
students’ individual directories in isolation?
• For student X the server should have read/write
•
access to X’s directory
However, when executing a script in X’s directory,
the server should not be able to access files in the
directory of some other student, Y
Discuss
•
There are ways of doing this but they are
not simple
• We may utilize this later in the term
For now we are eliminating this issue entirely
17
Lecture 1: CS 1520 Web Servers
• Rather than using a shared Web server,
•
you will each use your own server
The server you will use is XAMPP
This is a full-featured Apache / MySQL / PHP
/ Perl development environment
It can be installed on a laptop
• If you have one
It can be installed on a flash drive
• Makes it very portable
See: https://www.apachefriends.org/
18
Lecture 1: CS 1520 Web Servers
You will develop your projects on your own
servers, and then run them for your TA via a
demonstration
• You will need to bring either your laptop or your
flash drive (with the server on it) to the
demonstration
Assignment:
• Your first in-class exercise (next Wednesday) will
be to run a simple script on your own XAMPP
server
• You have from now until then to get it installed
on either your laptop or on a flash drive
19
Lecture 1: CS 1520 Web Servers
If you are putting it on your laptop the
installation is fairly straightforward
If you are putting it on a flash drive, you
don’t actually “install” it – just download the
.zip file, unzip it and run a .bat script
See documentation for where to put your
scripts so that they will run
If you have any trouble with this, see your
TA or me before next Wednesday!
20
Lecture 1: Intro. to HTML
• HTML is a mark-up language
Idea is that extra characters / symbols in the
text provide information to a parser, which
uses that information to render the
document in a certain way
• Ex:
<strong>Hello There</strong>
– The tags do not appear in the rendered document
– The parser utilizes them to alter the appearance of
the text
We will discuss mark-up languages in more
detail when we discuss XML later in the term
21
Lecture 1: Intro. to HTML
• HTML has evolved greatly over the years
New tags have been added
Some obsolete tags have been removed
Syntax has been standardized
• The current version is HTML 5
Still not universally used
However, recently updated browsers should
all support it for the most part
22
Lecture 1: Intro. to HTML
• Fundamentally, each HTML 5 document
has the following shell:
<!DOCTYPE html>
<html>
<head>
</head>
<body>
</body>
</html>
• There are a LOT of other tags / attributes and
•
variations
See: http://dev.w3.org/html5/html-author/
– There are many other reference links as well
• We will discuss some of these in more detail later,
when we focus on client-side programming
23
Lecture 1: Intro. to HTML
• HTML can be generated in several ways:
Can by typed in directly, using a text editor
– Ex: TextWrangler, TextPad, vi
Can be generated through software
– Ex: MS Word, DreamWeaver
Can be generated dynamically via scripts
– Ex: PHP, Perl
In this course we will be either typing in
HTML directly or generating dynamically via
scripts
• Ex: Using PHP or Python
24
Lecture 2: Intro to PHP
• What is PHP?
Language developed by Rasmus Lerdorf
from the Apache Group
Its primary use is for server-side scripting
• Ex: To process HTML forms
• Ex: To perform a DB query and pass on results
• Ex: To dynamically generate HTML
PHP scripts are often embedded within HTML
documents
• The server processes the HTML document,
executing the PHP segments and substituting the
output within the HTML document
25
Lecture 2: Intro to PHP
The modified document is then sent to the client
As mentioned previously, the client never sees
the PHP code
• The only reason the client even knows PHP is involved
is due to the file extension  .php
– But even this is not required if the server is configured
correctly
> The server can be configured to run PHP scripts even if the
file does not have a .php extension
> By default XAMPP will only execute PHP if the .php
extension is provided
> Can change this with an .htaccess file
> See what happens if you have PHP code without the .php
extension
26
Lecture 2: Intro to PHP
• PHP is a HUGE language
It is a fully functional language
It has an incredible amount of built-in
features
• Form processing
• Output / generate various types of data (not just
•
text)
Database access
– Allows for various DBs and DB formats
• Object-oriented features
– Somewhat of a loose hybrid of C++ and Java
• Huge function / class library
27
Lecture 2: Intro to PHP
• We will look at only a small part of PHP
There are also many tools that are already
pre-written in / for PHP
• If you are building a substantial project you may
want to use some of these
– Ex: http://github.com/pear
– Also see http://www.php.net/sites.php
• There are also content management systems
written in PHP
– Ex: http://drupal.org/
– Ex: http://wordpress.org/
• However, we may not be covering them here
– We will focus on the straight PHP language
28
Lecture 2: Intro. to PHP
• PHP Program Structure
Or really lack thereof
PHP, as with many scripting languages, does
not have nearly the same structural
requirements as a language like Java
A script can be just a few lines of code or a
very large, structured program with classes
and objects
• The complexity depends on the task at hand
However, there are some guidelines for
incorporating PHP scripts into HTML files
29
Lecture 2: Intro. to PHP
When a PHP file is requested, the PHP
interpreter parses the entire file
• Any content within PHP delimiter tags is
•
•
•
interpreted, and the output substituted
Any other content (i.e. not within PHP delimiter
tags) is simply passed on unchanged
This allows us to easily mix PHP and other
content (ex: HTML)
See:
– http://us3.php.net/manual/en/language.basic-syntax.phptags.php
– http://us3.php.net/manual/en/language.basic-syntax.phpmode.php
30
Lecture 2: Intro to PHP
• Consider the following PHP file
<!DOCTYPE html>
<html>
D
O
C
B
O
D
Y
HTML 5 Document
Root HTML Tag
<head>
<title>Simple PHP Example</title>
Document Head
</head>
<body>
<?php echo "<p><h1>Output</h1>";
echo "<h2>Output</h2>";
echo "<h3>Output</h3></p>";
PHP Code
?>
<script language="PHP">
echo "\n<b>More PHP Output</b>\n";
echo "New line in source but not rendered";
echo "<br/>";
echo "New line rendered but not in source";
</script>
</body>
</html>
31
Lecture 2: Intro. to PHP
• Now consider the resulting HTML
<!DOCTYPE html>
<html>
<head>
<title>Simple PHP Example</title>
</head>
<body>
<p><h1>Output</h1><h2>Output</h2><h3>Output</h3></p>
<b>More PHP Output</b>
New line in source but not rendered<br/>New line rendered but not i
</html>
• How will it look in the browser?
– Look at it in the browser!
– See ex2.php
32
Lecture 2: Intro to PHP
If we prefer to separate a PHP code segment
from the rest of our script, we can write it in
another file and include it
• Sometimes it is easier if we "separate" the PHP code
from the straight html
– We also may be using several different files, esp. if we
are using classes
• But we must tag it even if it is already within a PHP
tagged segment
– Included files are not interpreted by default
> Don’t necessarily have to be PHP
> If we want PHP, include PHP tags within the included file
– See ex3.php
– See http://us3.php.net/manual/en/function.include.php
33
Lecture 2: Intro to PHP
• Simple types
• See: http://us3.php.net/manual/en/language.types.php
boolean
• TRUE or FALSE
integer
• Platform dependent – size of one machine word
– typically 32 or 64 bits
float
• Double precision
• We could call it a double, but since we don't
declare variables (we will discuss shortly) float
works
34
Lecture 2: Intro to PHP
string
• We have single-quoted and double-quoted string
literals
– Double quoted allows for more escape sequences
and allows variables to be interpolated into the
string
– What does that mean?
> Rather than outputting the name of the variable, we
output its contents, even within a quote
> We'll see an example once we define variables
> Note that this is NOT done in Java
> See example
• Length can be arbitrary
– Grows as necessary
35
Lecture 2: Intro to PHP
• Easy conversion back and forth between strings
and numbers
– In Web applications these are mixed a lot, so PHP
will implicitly cast between the types when
appropriate
– This is another clear difference between PHP and
Java
> Java requires explicit casting
> PHP allows explicit casting if desired
– See:
http://us3.php.net/manual/en/language.types.type-juggling.php
• Can be indexed – the preferred way is using
square brackets
$mystring = "hello";
echo $mystring[1];
– Output here is 'e'
36
Lecture 2: Intro to PHP
• PHP variables
All PHP variables begin with the $
• Variable names can begin with an underscore
• Otherwise rules are similar to most other
languages
Variables are dynamically typed
• No type declarations
– Variables are BOUND or UNBOUND
> Unbound variables have the value NULL
– Type information for a variable is obtained from
the current bound value
– Compare this to Java
37
Lecture 2: Intro. to PHP
Implications of dynamic typing:
• No “type clash” errors like in Java
int x = 3.5;
// oh no!
String s = 100;
// argh!
–Instead we have in PHP
$x = 3.5;
// no problem!
$s = 100;
// a-ok!
• A variable’s type may change throughout program
execution
$x = 5;
// integer
$x = $x + 1.5;
// float
$x = $x . “ dollars”; // string
38
Lecture 2: Intro. to PHP
• Perhaps intentionally but perhaps by mistake
• We have to be careful and to test types during
execution
– gettype() function returns a string representation
of variable’s type
$x = 5; echo(gettype($x)); // integer
$x = $x + 1.5; echo (gettype($x)); // float
$x = $x . “ dollars”; echo(gettype($x)); // string
– is_<type>() function returns a boolean to test for
a given <type>
$x = 5;
$check = is_int($x); // true
$check = is_float($x); // false
– Can use these tests to make decisions within a
script
– See ex4.php
39
Lecture 2: Intro. to PHP
• Why is PHP dynamically typed?
Allows for faster interpreting of the code
• Compiled code will run faster than interpreted
code, but compiling itself takes time
Allows for easier / simpler templates /
generic code
• Think about generics in Java and how much
syntax they require
– Much of this is due to type checking
For more information see:
• http://en.wikipedia.org/wiki/Type_system
40
Lecture 2: Intro to PHP
PHP programs have access to a large
number of predefined variables
• These variables allow the script access to server
•
•
information, form parameters, environment
information, etc.
Very helpful (as we will see) for determining and
maintaining state information
Ex:
– $_SERVER is an array containing much information
about the server
– $_POST is an array containing variables passed to
a script via HTTP POST
– $_COOKIE is an array containing cookies
– See ex5.php
41
Lecture 3: Intro to PHP
• PHP Expressions and Operators
Similar to those in C++ / Java / Perl
Be careful with a few operators
• / in PHP is always floating point division
– To get integer division, we must cast to int
$x = 15;
$y = 6;
echo ($x/$y), (int) ($x/$y), "<BR />";
> Output is 2.5 2
• Mixed operands can produce odd results
– Values may be cast before comparing
42
Lecture 3: Intro to PHP
– To compare strings, it is better to use the C-like string
comparison function, strcmp()
> Other string functions are listed in Sebesta Table 9.3
• Even the == operator has odd behavior in PHP when
operands are mixed
– Ex: Consider comparing a string and an int
> Any non-numeric PHP string value will “equal” 0
> Any numeric PHP string will equal the number it
represents
– Ex: Consider comparing a string and a boolean
> Regular PHP string value will “equal” true
> “0” string will equal false
– This behavior is consistent but confusing to the
programmer and is probably best avoided
43
Lecture 3: Intro to PHP
• An additional equality operator and inequality
operator are defined
=== returns true only if the variables have the same
value and are of the same type
> If casting occurred to compare, the result is false
!== returns true if the operands differ in value or in
type
Precedence and associativity are similar to
C++/Java
• See
http://us2.php.net/manual/en/language.operators.precedence.php
44
Lecture 3: Intro to PHP
• PHP Control Structures
Again, these are similar to those in C++ /
Java
• if, while, do, for, switch are virtually identical
•
to those in C++ and Java
PHP allows for an alternative syntax to designate
a block in the if, while, for and switch statements
– Open the block with : rather than {
– Close the block with endif, endwhile, endfor,
endswitch
> Advantage to this syntax is readability
> Now instead of seeing a number of close braces, we
see different keywords to close different types of
control structures
45
Lecture 3: Intro to PHP
• A nice feature of PHP is that the "control"
resulting from a control structure is maintained
even when you exit back to html mode
– Thus, in <?php you can branch / loop etc.
– You can then exit php ?> and format in straight
html
• PHP also has the foreach loop
– Similar to the Java for loop for Iterable objects
– We will look at this when we discuss arrays
• See ex6.php
46
Lecture 3: PHP Arrays
• Arrays in PHP are quite versatile
• See http://php.net/manual/en/language.types.array.php
We can use them as we use traditional arrays,
indexing on integer values
We can use them as hashes
• You may know hashing from CS 0445 or CS 1501,
associating a key with a value in an arbitrary index of
the array
In either case we access the data via subscripts
• In the first case the subscript is the integer index
• In the second case the subscript is the key value
We can even mix the two if we'd like
47
Lecture 3: PHP Arrays
• Creating Arrays
PHP Arrays can be created in a number of
ways
• Explicitly using the array() construct
• Implicitly by indexing a variable
– Since PHP has dynamic typing, you cannot identify
a variable as an array except by assigning an
actual array to it
– If the variable is already set to a string, indexing
will have undesirable results – indexes the string!
– However, we can unset() it and then index it
> We can test a variable to see if it is set (isset() and
if it is an array (is_array()) among other things
– Lesson: Be careful with implicit behaviors
• Size will increase dynamically as needed
48
Lecture 3: More on PHP Arrays
• Accessing Arrays – can be done in many ways
We can use direct access to obtain a desired item
• Good if we are using the array as a hash table or if we
need direct access for some other reason
• We provide the key and retrieve the value
For sequential access, the foreach loop was
designed to work with arrays
• Iterates through the items in two different ways
foreach ($arrayvar as $key => $value)
> Gives both the key and value at each iteration
foreach ($arrayvar as $value)
> Gives just the next value at each iteration
49
Lecture 3: PHP Arrays
How can these both be done efficiently?
• PHP arrays are not implemented in the traditional
way
– Ex: In Java or C++ the array is a contiguous
collection of memory locations
• PHP arrays more resemble a linked list (see
Figure 9.3 in Sebesta text)
– But wouldn't this not allow direct access?
• The locations are also hashed
– The "key" in PHP arrays is actually a hash value
• So sequential access follows the linked list
• Direct access accesses via the hash value
50
Lecture 3: More on PHP Arrays
• Be careful – iteration via foreach is in the order the
data has been generated, not by index order
– i.e. it follows the linked list
– Thus, even arrays with identical keys and values can
have different orderings
• Items accessed in the arrays using foreach are
copies of the data, not references to the data
– So changing the loop control variable in the foreach
loop in PHP does NOT change the data in the original
array
– To do this we must change the value using indexing
A regular for loop can also be used, but due to
the non-sequential requirement for keys, this
does not often give the best results
44
Lecture 3: More on PHP Arrays
• The data in the array is not contiguous, so incrementing
a counter for the next access will not work correctly
unless the array index values are used in the
"traditional" way
for (int $i = 0; $i < count($A); $i++):
echo “$A[$i] <br/>”;
endfor;
–We know that there are count($A) items in $A
–What we do NOT know, is under which indices they are
being stored
> There is no requirement that they have to start at 0 or even
be integers at all
• See ex7.php
52
Lecture 3: More on PHP Arrays
• In addition to foreach, there are other
array iterators that we can use
Ex: Using next to access the array elements
• The next() function gives us the next value in the
array with each call
– It moves to the next item, then returns it, so we
must get the first item with a separate call (ex: use
current())
$curr = current($a1);
while ($curr):
echo "\$curr is $curr <br/>\n";
$curr = next($a1);
endwhile;
53
Lecture 3: More on PHP Arrays
Ex: Using each to iterate:
• The each() function returns a pair with each call
– A key field for the current key
– A value field for the current value
– It returns the next (key,value) pair, then moves, so the
first item is no longer a special case
while ($curr = each($a1)):
$k = $curr["key"];
$v = $curr["value"];
echo "key is $k and value is $v <BR />\n";
endwhile;
– This function may be preferable to next() if it is possible
that FALSE or an empty string or 0 could be in the array
> The loop on the previous slide will stop for any of those
values
54
Lecture 3: More on PHP Arrays
• Both of these iteration functions operate similar to
the Iterator interface in Java
– Iterate through the data in the collection without
requiring us to know how that data is actually
organized
– However, unlike in Java, if the array is changed
during the iteration process, the current iteration is
NOT invalidated
> Since new items are always added at the "end" of
the array (from an iterator’s point of view) adding a
new item during an iteration does not cause any
data validity problems
> However, we need to be careful if doing this – can
lead to an infinite iteration
55
Lecture 3: Sorting PHP Arrays
• There are various predefined sort functions
in PHP
sort (rsort for reverse)
• Sorts arrays of numbers numerically
• Sorts arrays of strings alphabetically
• If mixed, the strings count as 0 compared to numbers
• Reindexes array so that keys start at 0 and increment
from there
asort
• Same as sort but retains the original key values
(arsort for reverse)
56
Lecture 3: Sorting PHP Arrays
• PHP uses Quicksort to sort arrays
This means that PHP sorting is NOT STABLE
What does it mean for a sort to be STABLE?
• Given equal keys K1 and K2, their relative order
before and after the sort will be the same
Due to data movement during partition,
Quicksort is not stable
• Implications?
• If we want stability, we will have to do it
ourselves
– See Web for some solutions
See ex8.php
57
Lecture 3: Two-dimensional Arrays
• Array values can be any legal PHP type
This includes the array type, and allows for
arbitrary dimensional arrays
We may think of them as "arrays of arrays"
It seems odd but once you know the array
syntax it follows quite naturally
$a[0] = array(1,2,3,4);
$a[1] = array(5,6,7,8);
$a[2] = array(9,10,11,12);
58
Lecture 3: Two-dimensional Arrays
• In fact Java 2-D arrays are implemented in a
similar way
– Although in Java they must be homogeneous
We can also use "normal" indexing for 2-D
PHP arrays
• Keep in mind that the key values are still
•
arbitrary, so we need to be careful
The standard nested i, j loops that work in Java
and C++ may not be appropriate here
– They will only work if we use the arrays in the
"normal" way consistently
• More general access can be done via iterators or
•
recursive functions – we will see this soon
See ex9.php
59
Lecture 4: Functions and Parameters
• General syntax:
function name ( params )
{
// statements
// optional return statement
}
Parameters are optional
Note that there is no return type in the
header
• If no return statement is used, the function will
return NULL
If return is done, type could be anything
•
Cannot overload function names
• Dynamic typing does not allow for disambiguation
60
Lecture 4: Functions and Parameters
By default, parameters are passed by value
• Formal parameter is a copy of the actual parameter
• Changes to formal parameter do not affect the actual
parameter
If we add an ampersand before the formal
parameter, we change it to pass by reference
• Like in C++, but not an option in Java
• Formal parameter is another name for the actual
•
parameter
Changes to the formal parameter also change the
actual parameter
61
Lecture 4: Value vs. Reference Parameters
• Let's discuss this a bit
Java has only value parameters
• This means we cannot alter the data stored in the
actual parameter within the function
• However, in many cases the data passed into the
function is itself a reference
– This allows us to change the object that the
reference refers to, but we cannot reassign the
reference to a new object
reference
cannot change
this
X
data in object
62
can change
this
(mutate)
Lecture 4: Value vs. Reference Parameters
Reference parameters allow the data in the
object to be changed
• Assuming the parameter is a reference to an
object
They also allow the reference to be
reassigned so that the actual parameter can
reference a different object
reference
can also be
reassigned
X
new
object
old
object
63
can change
this
(mutate)
Lecture 4: Value vs. Reference Parameters
Implications:
• In Java we can alter objects, including the
contents of arrays, via parameters
– However, the argument itself (either a primitive
value or a reference) cannot change
– If we want to reassign a variable within a method
what can we do?
> Use instance variables
> Pass them in via an array (i.e. hack)
• In PHP we CAN reassign a reference with a
function if we want
– Be careful if you choose to do this
> Avoid inadvertent changes to arguments
64
Lecture 4: Functions and Parameters
Variables within functions by default are local
to the function
• Like method variables in Java or automatic
•
variables in C++
We can use the global declaration to state that a
variable used in a function is a global rather than
a local variable
See ex10.php
65
Lecture 4: More on Functions
• PHP Function names can be stored in
variables and called through the variables
This allows the function that is used to be
determined dynamically (at run time)
Can be handy if different functions need to
be called in different situations
• PHP functions can have default
arguments
If no actual parameter is supplied the default
value is used
• See ex11.php
66
Lecture 4: PHP Files
• Using files in PHP is fairly straightforward
Can open a file for reading, writing, append,
and a couple variations of reading+writing
• Note 1: Files are not covered in the Sebesta text
• Note 2: You may have to set some permissions on
your file system to allow your server write access to
files
• There are a few different ways to access
files in PHP
Many C file functions are almost identical in PHP
• Ex: fopen, fseek, fscanf, fgetc, fgets
• See the manual for complete list
67
Lecture 4: PHP Files
• Opening files
Typically we use fopen() to open a file for
either reading or writing
$fp = fopen(<filename>, <mode>);
• Where <filename> is the path/name of a file that
is accessible to the server
• Where <mode> specifies how the file will be
accessed
– Ex: "r"  read only
"r+"  read/write with pointer at beginning
» The above modes require the file to already exist
68
Lecture 4: PHP Files
"w"  write only
"w+"  write / read, truncating previous file length to 0
– For the above modes, the server will attempt to create the
file if it does not exist.
– Also "a" and "a+" for append modes
• Reading from files
For text files, we can read different amounts per
read depending on our requirements
• Read a single character at a time
• Read the entire file into a single string
• Read the lines of the file into an array of strings
Can also read binary data if necessary
• Ex: images, audio, etc.
69
Lecture 4: PHP Files
PHP allows all of these with various functions
• Look at the options in the manual
• See: http://php.net/manual/en/ref.filesystem.php
• Writing to files
Most commonly done with fwrite
Again see manual for details
• Very Simple Example:
See readwrite.php
70
Lecture 4: Debugging Note
• Many situations that produce compilation
or run-time errors in Java will not do so
in PHP
• Ex: Accessing a variable that has no value:
$count = $count + 1;
• Ex: Reading a file that does not exist:
$data = file(“nonexistentfile.txt”);
However, these situations will produce
warnings, which we can elect to see or not
see in the returned web page
• We can determine whether these warnings (and
actual errors) are seen or not via .htaccess files
71
Lecture 4: Debugging Note
• These are configuration files that allow per directory
•
configuration options for the server
For example the settings:
php_value display_errors 1
php_value display_startup_errors 1
– will send PHP warnings back to the client browser
• And the settings:
php_value display_errors 0
php_value display_startup_errors 0
– will hide the warnings from the user
• Note: In some installations these cause problems for
the server – if these cause an error in your server
don’t use them
72
Lecture 4: PHP Files
• Flocking files
• See http://php.net/manual/en/function.flock.php
The flock() function is called to restrict
access to files (when necessary) to one
“user” at a time
• If each “user” calls flock() prior to accessing a file
pointer to the same file, only one will be allowed
to access it at a time
Why do we need this?
• Multiple users frequently access the same server
• Server typically spawns a separate process for
each user
73
Lecture 4: PHP Files
These processes can execute in pseudo-
parallel or in actual parallel depending on how
the server is configured
Consider the following scenario for process P1:
– Read a file into an array
– Update a value in the array
– Write the array back to the file
What if process P2 writes to the file between
P1's reading and writing?
If used correctly, flock() can prevent this
problem
• See flock.php
74
Lecture 4: PHP Files
Note: This issue is difficult to test with
localhost server access
• Practically speaking we can only submit one
•
•
request at a time
However an online server can be accessed via
many clients, with many coinciding processes
See demo of flock.php on cs1520.cs.pitt.edu
75
Lecture 5: CGI and Scripts
• CGI - Common Gateway Interface
• http://en.wikipedia.org/wiki/Common_Gateway_Interfa
ce
• http://tools.ietf.org/html/rfc3875
Interface for Web servers that interact with
browsers, utilizing scripting languages and
the HTTP (HyperText Transfer Protocol)
Used to allow data interaction between
clients and server scripts
• Ex. Extracting data sent via HTTP requests and
•
passing to scripts
Scripts can then use this data as input and act on
it
76
Lecture 5: CGI and Scripts
Two best known HTTP methods: GET and POST
• GET
– appends user input to URL and requests corresponding
document
– server parses URL - first part is a program that it
invokes, second part is parameters passed along
Ex. http://cs1520.cs.pitt.edu/~nomad/php/bogus.php?arg1=wacky
– Recommended usage for safe and idempotent requests
> I dem WHAT?
> Isn’t superman idempotent?
– Safe:
> For retrieval only – has no side effects on the server
– Idempotent:
> Making N > 1 identical requests has the same effect as
making only 1 request
– See:
http://www.w3.org/Protocols/rfc2616/rfc2616-sec9.html
77
Lecture 4: GET and POST
• POST
– sends data as a stream to script program
– more suitable for large amounts of data
– arguments are not shown in address but are still
extracted and processed by server
– Used for requests that may alter / update the
server
> i.e. NOT safe and NOT idempotent
> Ex: update a database
> Ex: submit a payment
78
Lecture 4: CGI and Scripts
• GET and POST are often used within
HTML forms
User enters data into form and then
SUBMITS it
Browser processes form and passes choices
and information to the url specified
Server invokes appropriate script utilizing
requested method, extracting submitted data
• Most scripting languages (including PHP) have
•
predefined ways to easily extract this data
This data is used as input to the script
79
Lecture 4: CGI and Scripts
Results are sent back to browser and
displayed in the Web browser
See getpost.html and getpost.php
• Since scripts are executed by the server
and can access files on the server
It is prudent for a webmaster to be cautious
about the scripts placed onto the server
For example many servers will only execute
“approved” scripts placed into an approved
directory
80
Lecture 5: CGI and Scripts
Also, using a production server for a class
slows it and increases the probability of
down-time
Yet we need to be able to work on scripts on
a full-fledged web server
For these reasons we are using individual
XAMPP servers for each student
We may also use cs1520.cs.pitt.edu later in
the term
• If so details on how to set up your files will be
discussed at that time
81
Lecture 5: Processing Forms with PHP
• Using PHP with forms is fairly simple
When forms are submitted the server executes
the php script, returning the resulting html
• Remember that some of the file is unchanged, since
•
•
it may not have an embedded php script within it
Server can be set to that the form variables can be
accessed directly by simply using the $ sign
However, it is better to access the variables from the
$_POST array (or the $_GET array)
– The form element name is the key into the array
– Discuss and see getpost.php
82
Lecture 5: Processing Forms with PHP
We can also use PHP to create forms
• However, it is really just HTML that we are using
• We can "interleave" the PHP and html to get the
desired overall result, or we can have PHP output the
appropriate HTML tags
• So if you don't know it yet – learn some HTML
– See Chapter 2 in Sebesta
See ex12.php, ex12b.php – note many
comments!
• Note how the script interacts with the data file
– It will show as many rows in the table as there are lines
in the file
• Note how the PHP and html are interleaved
83
Lecture 5: Maintaining State
• HTTP is a stateless protocol
It is simply defines how clients and servers
communicate with each other over the Web
Yet with many Web applications, maintaining
state is important
• Ex: When a customer logs into a site such as
Amazon, he/she may go through multiple pages
– We may want to keep track of the user him / her
self (authentication information)
– We may want to keep track of what he / she has
been doing
84
Lecture 5: Maintaining State
State can be maintained in various ways and
in various places
• Ex: We can store information on the server or on
•
the client
We will examine several of these throughout the
rest of the term
• One way of maintaining state is via
Cookies
• http://en.wikipedia.org/wiki/HTTP_cookie
85
Lecture 5: Cookies
• Cookies – what are they?
Small pieces of information (up to 4K)
initially sent by the server to the client and
stored on the client machine
When client next connects to a server, it
sends cookies from that server back to it
Information about the client can then be
extracted by the server
• If no cookie, server can create a new cookie for
•
the client and send it with the response
However, browsers can disable cookies
– Can cause problems if server is dependent upon
them
86
Lecture 5: Cookies
Cookie format:
• Name: name of the cookie – typically used to
extract / examine the cookie
• Value: contents of the cookie – seems like a
simple value but can be an array if generated
correctly
• Domain: domain of the server that is to receive
the cookie – actual domain of server must match
domain stored in the cookie
– Idea is that other servers cannot look at all of your
cookies to see what you have
– If not explicitly set in the cookie, it is the full
domain of the server that created the cookie
87
Lecture 5: Cookies
• Expires: When cookie will expire
– Timestamp: Very specific format is required, but
we can use function calls to make it easier
• Path: Path in server from which cookie can be
sent
– If not specified it is the full path from where cookie
was set
• Secure: Does cookie require secure server using
https
– Default is no
88
Lecture 5: Sending Cookies to Client
Cookies are sent with the HTTP header of an
html file:
• Set-Cookie: oreo=Count Chocula;
domain=.chocolate.com;
path=/cgi/bin;
expires=Thu, 08-Jun-2015, 16:15:00 GMT;
– Must be set PRIOR to any html tags (since it is
sent with the header)
• If not sent with HTTP header will not be
•
interpreted as a cookie
If client does not accept cookies it will just discard
them
– We can send a cookie and test to see if client
accepts cookies
89
Lecture 5: Cookies in PHP
• Cookies in PHP are fairly easy to use:
setcookie() function is called to create a cookie
that will be sent to the client
• See http://php.net/manual/en/function.setcookie.php
• As always with cookies, they must be sent with the http
header
– Thus, you should determine and set any cookies in PHP
mode prior to using any html (or even simple text)
$_COOKIE array contains the cookies received
back from the client machine
• Cookies sent to client by server previously
• Associative array allows access of cookies by name
90
Lecture 5: Cookies in PHP
Thus, to maintain state a server can:
• Send the client a cookie the first time the client
connects to the server
• Receive and update / modify the cookie as client
navigates the site
– Or send additional cookies
• Use the presence and / or value of cookies to
discern information about the client
– Ex: A repeat customer – time of last visit
– Ex: A current customer – last request or last page
visited
We will look at an example soon
91
Lecture 6: Session Tracking
• Cookies allow us to maintain state, but are
somewhat clumsy to program
To keep detailed state information we probably
need many cookies and we must store a lot of
information within them
• Each cookie is only 4K and Value field is simple
Cookies are good for keeping track of return
visitors
For keeping state within a "current" visit, there
are better ways
• PHP allows session tracking which can simplify and
streamline the process of maintaining state
92
Lecture 6: Session Tracking
• Idea:
When user first logs into (or simply visits) a
site, a session is started and a unique,
random ID is assigned to that user
ID is stored in a cookie (on client) or on the
URL, but state information (session
variables) is stored on the server
Any accesses by the same client with the
same session ID are recognized and the
session variables can be retrieved and used
• From any .php script – multiple scripts can be
used in the same session
93
Lecture 6: Session Tracking
In other words, the session variables are a pool
of semi-permanent data stored on the server
• A separate pool is associated with each client
• Through the session ids the pools can be
distinguished and accessed appropriately
Arbitrary information can be stored for each client
•
When session is finished (client logs out or
browser is closed) the session variables are
cleared and the session ID is disposed of
94
Lecture 6: Session Tracking
• Syntax
Session tracking can be automatically turned
on (with a server setting)
If not the programmer must explicitly start a
session in each script using
session_start()
• This should be done at the beginning of the
•
•
script, prior to any regular html tags
It must be done in any script in which the session
variables are to be accessed
See ex5.php to note that session tracking is NOT
automatically on in our server
95
Lecture 6: Session Tracking
• During a session, session variables are accessed
by scripts through the $_SESSION array
– Arbitrary values can be stored there
• Implementation
Be default PHP uses cookies to implement
sessions
• However, they are used behind the scenes, so
programmer does not have to deal with the
particulars
Session ID is embedded within a cookie
•
Can also insert the session ID into the URL if
you prefer (ex: client doesn’t accept
cookies)
96
Lecture 6: Session Tracking
• Issues:
Session tracking in itself is not a secure process
• Session id is the key to obtaining the information, so
it must be protected
• If we use a secure server (using SSL) we ensure that
the ids are not sent as plain text
For more information:
• See: http://www.php.net/manual/en/intro.session.php
For example of using session tracking and
cookies, see
• ex13.php for simple example
• usesession.php for a bit more complex handout
97
Lecture 6: Mail
• Many web apps require mail to be sent
PHP has a built-in mail function:
mail ($receiver, $subject, $message, $extras)
All arguments are strings
• $extras allows additional information to be passed
– Ex: From, Cc, Bcc
• See mail() in the PHP manual
This should work in a production server
However, it relies on the web server having
the ability to actually send the mail
• This may not be the case for your XAMPP servers
98
Lecture 6: Mail
There is an alternative that we can use,
which will utilize a mail server that you
already have access to
• Ex: Pitt Mail or GMail
However, it is not standard PHP but rather
an add on
• It is called PHPMailer and is fairly widely used
• To download it see:
– https://github.com/Synchro/PHPMailer
• You will need to install this onto your system
– Demonstrating this will be Weekly In-class Exercise
3 (Wed. June 3)
• See mail.php and sendmail.php
99
Lecture 6: OOP PHP
• PHP is an object-oriented language
• See: http://us.php.net/manual/en/language.oop5.php
Has classes + objects
Has inheritance and method overriding
• However, the dynamic typing of PHP variables does
not give it quite the same type of polymorphism as
Java
– The reference type always matches the object type
Object syntax is more like C++ than Java
• Uses the scope resolution operator for parent class
access
• Uses the “arrow” operator for field / method access
100
Lecture 6: OOP PHP
PHP objects can have instance variables and
instance methods
• Like Java (more or less) we can restrict visibility
by using
– private
> Only visible within class of variable’s declaration
– protected
> Visible within class of variable’s declaration, plus
any subclasses
– public
> Visible anywhere
• Unlike Java we do not have implicit access to
instance variables from within objects
– To access we must use “this” for explicit access
101
Lecture 6: OOP PHP
class Foo
{
private $x;
public function setX($data)
{
$this->x = $data;
}
public function getX()
{
return $this->x;
}
...
}
• See what happens if you just use $x
102
Lecture 6: OOP PHP
PHP also has a lot of functions to help with OOP
• Some are particularly useful for the Web environment
in which PHP is used
• Ex: __autoload()
– Can automatically include class files for any classes used
in a PHP script
> We don’t have to explicitly include each file
> We don’t have to worry about including a file multiple
times
– Note the name: prefixed with two underscores
> There are several useful functions with this notation
> Ex: __construct(), __destruct(), __toString()
103
Lecture 6: OOP PHP
• These are called “magic methods”
– Mostly because they are called implicitly in some
way or another
– PHP programmer may define the method bodies
but does not explicitly call them
– For more information see:
> http://php.net/manual/en/language.oop5.magic.php
– See ex14.php, Foo.php and SubFoo.php
• Ex: serialize(), unserialize()
– Allow serialization and deserialization of PHP
objects
> This is good if we want to save an object into a file
or a cookie and then later restore it
> See usesession-oop.php and User.php
104
Lecture 7: OOP PHP
PHP OOP definitely has differences from Java
OOP
• However, there is extensive documentation on it
so avail yourselves of it
Ex: Interfaces and Polymorphism
• Since PHP variables are dynamically typed, we
never have to cast objects to store them
• See ex15.php and class files
Why use it (or when to use it)?
• When scripts get larger / more complex
• To interact with some predefined resources
– Ex: a MySQL database
105
Lecture 7: Sorting Instability
• As we mentioned previously, the default
sort() method in PHP is unstable
This does not really matter when sorting
simple types
However, when sorting complex types such
as objects, we can have issues:
• Original data is in order on Field A
• We sort the data on Field B
• Objects which are equal on Field B, may not have
the original order based on Field A
To obtain stability we will have to write our
own sort method
106
Lecture 7: Sorting Stability
• Or, more likely, use code that someone else has
written!
See unstable.php
107
Lecture 8: Flat Files vs. DB Files
So far, our PHP examples have used regular
text files
• Often called FLAT FILES
These have a certain advantage, since we
can edit the files easily and can read them
without any special software
However, they have many disadvantages as
well
• It is difficult to "update" the data in a file without
rewriting the entire file
– How to change data in the middle of the file?
108
Lecture 8: Flat Files vs. DB Files
• Concurrent access of the file is tricky
– We use FLOCK to lock out the file, but even that
only works when used consistently
– We also often FLOCK a file for a long period of
time to prevent corruption – limiting access to the
file for that time
> Even if we really need to lock only part of the file
• Access can be slow, especially if the data is large
• Access privileges must be implemented by the
programmer
109
Lecture 8: Flat Files vs. DB Files
An alternative is to use a DATABASE to store
our data
• Most common databases now are relational
databases
– We have data stored in tables and relate the data
from one table to that of another
• Access is faster than flat files
• Queries to obtain specific sets of data can be
•
•
•
done using a well-defined query language
User has random access to data
Concurrent access handling is built in
Access privileges are built-in
110
Lecture 8: Database Definitions
• Some definitions / notions we will be using
Database
• The overall collection of data – may consist of many
tables
Table
• An individual "relation" in the relational database
– Relates keys to values
• Table Column
– An attribute in the table
• Table Row
– An entity in the table
– Typically has a value for each column
111
Lecture 8: Database Definitions
• Key
– An attribute that uniquely identifies an entity
> Ex: SSN for a student at Pitt
• Foreign Key
– Key used to relate data in one table with data in
another table
> Ex: PSID may be key to a student table
> May also be a foreign key in a table for a given
course
• Schema
– A set of table designs that determine a database
– Does not yet include the data – simply shows how
it will be structured in the database
112
Lecture 8: Database Definitions
Relationships -- how do data in different tables
relate?
• One to one
– An entity in a table corresponds to a single entity in
another table
– The relationship is typically established using a foreign key
for one or both entities
> Ex: If we have a table for Student_Info and a table for
Academic_History, there is a one-to-one relationship
between them
• One to many
– An entity in a table corresponds to 1 or more entities in
another table
113
Lecture 8: Database Definitions
> Ex: If the table for Academic_History has an entry for
each term, the relationship now becomes one student to
many terms
• Many to many
– Multiple entities in one table correspond to multiple
entities in another table
– This relationship is often defined by a separate table,
which in fact changes it into 2 one to many relationships
> Ex: Tables Student_Info and Courses_Taken have a many
to many relationship, since a student can take many
courses and each course can be taken by many students
> However, if we create a new table Student_Courses, we
can have each entity be a pair: Student_Id, Course_id
> Now Student_Info has a one to many relationship with
Student_Courses, and so does Courses_Taken
114
Lecture 8: Database Theory?
There is a lot of database theory about how
to best create a schema
• Best modeling the data you are storing
• Storing it most efficiently
• Designing most efficient queries
We will leave most of this to the Database
course
• Take CS 1555
• Here we are more concerned with the
fundamentals, and for using MySQL through PHP
and a Web interface
115
Lecture 8: Getting Started with MySQL
• First we need access
Typically this is done by the site administrator
Since you are using your own servers, you are
the administrators!
This can be done in several ways:
• XAMPP comes with phpMyAdmin
– This is accessed via the Web Server and allows you to
access / modify the DB through a Web interface
• XAMPP also comes with a mysql program
– This allows command line access to the MySQL server
• You can use either of these to initialize your DB
• Let’s try a simple example with both
– See handouts and demo
116
Lecture 8: Getting Started with MySQL
• The mysql program also allows you to run batch
commands using the “source” command
– Be careful if you do this!
Note: The root MySQL account in XAMPP
initially has no password
• If others will have access to your site, you need
•
•
to add one!
There are other security holes in XAMPP that you
should address if it will be shared
To address / fix these, go to the XAMPP home
page on your system:
http://localhost/
– Click on "English" and then click on the Security
link for more information
117
Lecture 8: Getting Started with MySQL
• There is a lot of info available for MySQL
For MySQL home page, see: http://www.mysql.com
For docs, see: http://dev.mysql.com/doc/refman/5.0/en/
• There are many versions in the doc site – depends on
your version of MySQL
Googling various issues you may have is also
helpful
118
Lecture 8: Setting up a DB
• As mentioned before, first we should
•
develop a schema for our database
Once that is done satisfactorily, we need
to set up our tables and initialize the DB
Can be done fairly simply using a script in
conjunction with one or more flat files
• The nice thing here is that the flat files are
•
needed only for initialization
Once the database is set up, we use it exclusively
119
Lecture 8: PHP MySQL Basics
To set up our DB, we need to issue some
MySQL commands
• Allow us to create tables and add data to them
• Some valid MySQL commands are shown in Chapter
13 of the Sebesta text
– Ex: create table, insert into, select
We will use these MySQL commands through
PHP scripts
• In PHP, there are several ways of accessing a MySQL
•
DB
Older installations used procedural access with the
mysql_xxx() suite of functions
120
Lecture 8: PHP MySQL Basics
• Newer versions of PHP allow object-oriented
access using mysqli
– This is how we will be accessing our DB
– Note:
> Text uses mysqli commands but via the older
procedural access
> This works but oo access is nicer so we will be
accessing it that way
– We will initially create a mysqli object which
connects us to the server
– Then we access the db via methods in the object
121
Lecture 8: Setting up a DB
Thus it is important to know both
• The correct MySQL syntax to manipulate / access the
database
– More info in Chapter 13 of Sebesta and
http://dev.mysql.com/doc/refman/5.0/en/index.html
• The correct PHP syntax to connect to and query using
its MySQL objects and functions
– More info in Section 13.5 and
– http://www.php.net/manual/en/mysql.php
> Overview
– http://www.php.net/manual/en/book.mysqli.php
> Object-oriented access using mysqli
122
Lecture 8: MySQL Commands to Set Up a DB
• Some example commands
DROP TABLE
CREATE TABLE
• Allows us to delete a table or create a new table
•
in our DB schema
We can specify the name and properties of each
of the fields (columns) within the new table
INSERT INTO
• Allows us to insert entries (rows) into a table
• Column values are provided positionally based on
the order established when the table was created
123
Lecture 8: PHP MySQL Commands
• First we must connect to the DB (via PHP)
We do this by creating a new mysqli object:
$db = new mysqli(<server>,<user>,<pass>,<db>);
– Where <server> is the mysql server we are connecting
to
– <user> is the account on the server
– <pass> is the password for the account
– <db> is the database that we want to access
•Since our Webserver and PHP server are on the same
host, we can use ‘localhost’ (or 127.0.0.1) for the host
•For simplicity, in our accounts the <user> and <db>
values will always be the same
– Not required though
124
Lecture 8: PHP MySQL Commands
• Once we have connected to our database
and created our mysqli object we can
operate on it
There are many methods available in the mysqli
class
The method we will use the most is query()
• This will pass an arbitrary query string to the
•
•
database
The return value depends on the nature of the query
If the query fails
– the boolean false is returned
125
Lecture 8: PHP MySQL Commands
• If the query succeeds
– Commands such as INSERT, UPDATE, CREATE, etc
will return true to indicate that they succeeded
> Ex: The new row was added to the table
> Ex: The current row was modified as specified
– Commands such as SELECT and SHOW will return
a mysqli_result object
> Idea in this case is that there is data that is
returned from the query
> The mysqli_result object gives us access to this data
through its methods
> We will look more at this class and its methods soon
when we look at some queries
126
Lecture 8: PHP Commands to Set Up a DB
• For example, let’s create a new table called
“CD” with a field for an id number, a title
and an artist
The id number will be the primary key
• Must be unique for each row / entry
• We will then insert a few CDs into our table
See setCDs.php
Then see carsdata.html
• Don’t let the name fool you – you can do an arbitrary
query from this web form
127
Lecture 9: Another Simple Example
• There is also a nice example in the text – let’s
talk about this one a bit
Four tables:
• Corvettes – stores basic information about the cars
• States – associates an id with each state name
• Equipment – associates an id with each equipment value
• Corvettes_Equipment – “helper” table to connect
Corvettes to Equipment
– Corvettes.Vette_id is one to many with
Corvettes_Equipment.Equip
See text files
128
Lecture 9: Another Simple Example
– Corvettes_Equipment.Vette_id is many to one with
Equipment.Equip_id
> Together they effectively make Corvettes.Vette_id and
Equipment.Equip_id many to many
– States is not really necessary
> Just “clean” up Corvettes table
• The setup.php script creates these tables
– Look at it (see comments especially)
129
Lecture 9: Joins
Once these tables are set up, we need to extract
information via queries
• These often involve a “join” of two or more tables
– Relating data in one table to data in another to get a new
set of entities
Relational databases allow for many variations of
joins:
• Ex: INNER JOIN, CROSS JOIN, NATURAL JOIN, LEFT
•
•
JOIN, RIGHT JOIN
To learn more about these, other variations and their
differences, take the CS 1555 course!
We will focus on just a few of the possibilities
130
Lecture 9: Joins
INNER JOIN (or cartesian product, or CROSS
JOIN)
• Associates each row in one table with each row in the
other
> Note: Technically, an INNER JOIN is not equivalent to a
CROSS JOIN – the INNER JOIN requires the tables to
have some matching data -- however, in MySQL they
are equivalent
• If table T1 had R1 rows and table T2 had R2 rows, the
cross join will have R1x R2 rows
– For example, a join of the Corvettes and States tables
• This is the default join in MySQL, and syntactically
can be done in several different ways
131
Lecture 9: Joins
Implicit vs explicit
Explicit:
• SELECT * FROM Corvettes INNER JOIN States
• SELECT * FROM Corvettes JOIN States
Implicit
• SELECT * FROM Corvettes, States
Clearly, in most cases this is not what we
really want
• Rather, we want certain rows in one table that
relate to certain rows in the other table in a
certain way
132
Lecture 9: Joins
We can achieve this with an equi-join
• In an equi-join, we add equality testing conditions to
the join that only some of the rows in the full join
table meet
– For example in the Corvettes and States tables
– We can either match fields in one table to those in
another or we can match fields in either table to a
specific value. For example:
SELECT * FROM Corvettes, States WHERE
Corvettes.State = States.State_id
SELECT * FROM Corvettes, States WHERE
Corvettes.State = States.State_id AND
States.State = 'California‘
− We can also use the other syntax variations
133
Lecture 9: Joins
• Technically, an equi-join requires all conditions to
be equality tests
– Allows for some efficiency improvements
• However, the restrictions given in the WHERE
clause can be arbitrary
– Any relational expression that we’d like to use
when comparing data in the tables
• The more restrictions we have, the fewer records
will be returned
– Note, however, that having fewer returned records
does not necessarily imply that the query took less
time to be done!
– Take CS 1555!
134
Lecture 9: Joins
Generally a MySQL join is specified in the select
command
(this version is somewhat simplified)
:
SELECT items FROM tables
[WHERE condition]
[GROUP BY group_type]
[HAVING where_definition]
[ORDER BY order_type]
[LIMIT limit criteria]
• [] values are optional
• More than one table gives us a join
• The WHERE condition restricts the join
135
Lecture 9: Accessing the DB
We do these queries using MySQL commands
• Our access can be very general if we'd like
– See carsdata.html and access_cars.php
– BE CAREFUL with these files on your accounts
> Allow user to make arbitrary changes to DB
> This type of script would not be available on a production
server
• Let’s try a few queries:
– Find all cars since 1995 (inclusive) that have automatic
transmission, also showing their state
– Find out how many cars are from each state in the list
– Find all cars from California, sorted by mileage
136
Lecture 10: Accessing the DB
• More often the access is restricted, based on the
needs of the site that we are developing
– User of the site doesn't even know that MySQL is
being used
– Simply enters information into a form as we'd
expect and the processing of the form is done via
a MySQL database
– Queries are designed based on what information is
needed
– Access is similar in nature to that used to set up
DB
137
Lecture 10: Accessing the DB
• For example:
User logs into a website by typing id and
password
• Ids and passwords are stored in a DB and a query is
done to determine if the user is valid
New users are added to the DB
•
Let's talk a bit about this from a security
standpoint
• Assume DB X is storing ids and passwords for System
•
Y
Assume DB X becomes compromised
138
Lecture 10: Accessing the DB
• We'd like this to NOT also compromise System Y
• How can we handle this?
– Rather than storing passwords in plaintext, we
instead store a hashed version of the password
> The function hash() allows several different hash
algorithms to be used
– These are one way mappings of the password
using sophisticated hashing techniques
> Since they are one way, we cannot (easily)
reproduce the password from the hashed versions
– But don’t we need the actual password?
> Not usually
139
Lecture 10: Accessing the DB
• Ex:
– When logging into System Y user types password, P'
– Hashed version of actual password P, hash(P) is stored
in the DB
– System Y retrieves hash(P) from the DB and compares
it with hash(P')
– If hash(P) == hash(P’), the password is valid;
otherwise it is not
– Note that obtaining hash(P) by breaking into the DB
will not give away actual passwords
> Since login system requires user to enter P, not hash(P)
• Q: How can we tell if hashed passwords are being
used on a given system?
140
Lecture 10: Accessing the DB
Let's look at a detailed example:
– setquotes.php, ex17.php, ex17b.php, ex17c.php
Script ex17.php is the "initial script"
• Session variables are used to prevent users from
going directly to ex17b.php or ex17c.php
• Idea is that access is granted each step of the
way and if you start in the middle it will be
detected
Take a look at the files and read the
comments very carefully
• There is a lot of information in there!
141
Lecture 10: SQL Injection
We have mentioned security a few times now
The most important thing to realize as a Web
developer / administrator is that hackers are
always trying / finding new / unusual ways to
attack you
Ex: SQL Injection (http://en.wikipedia.org/wiki/SQL_injection)
• One common use of a DB is to search based on input
from the user
– Ex: Enter name of person you wish to find
• A dangerous assumption is that the user will always
enter a legitimate search string
142
Lecture 10: SQL Injection
SQL syntax is such the user can actually enter SQL
code into a search string thereby circumventing the
original intent of the query
• In some cases, the user can actually be destructive,
changing the DB itself
Ex: Consider the following MySQL query in a PHP
script:
$query = "select * from Movies where Movie_name = '$movie'";
– Assume $movie was extracted from a POST submission
• The intent is that the user will enter a movie name
• What if the user enters SQL syntax?
– Let's try it!
143
Lecture 10: SQL Injection
In some systems the problem can be much
worse, due to allowance of cascading SQL
statements in a query
• Ex user input:
' or '1'; drop table Movies; select * from
Movies where '1' = '1
• Oh no!
– Note that this depends on the user knowing the
table name – but it can often be guessed
– Also it depends on system allowing multiple
statements in a query – MySQL does not but some
do
• See ex18.php
http://xkcd.com/327/
144
Lecture 11
• Exam One
145
Descargar

Document