Transcript PHP Bible – Chapter 13: SQL Tutorial

PHP Bible
Chapter 13: SQL Tutorial
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
1
 Wiley and the book authors, 2002
Summary





Relational databases and SQL
SQL standards
Basic SQL statements
Designing SQL databases
Privileges and security
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
2
 Wiley and the book authors, 2002
SQL Introduction


This chapter covers a basic introduction to SQL databases in
which we discuss standards, database design, data manipulation
language, data definition language, and database security
procedures common to all SQL databases
This will not be a comprehensive discussion of SQL or to any
particular SQL database, too much is dependant on the
particular DBMS chosen and its version

Please read the documentation particular to your DBMS to
determine what additional support which may exist and what
differences may exist for implementation of standard SQL
commands
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
3
 Wiley and the book authors, 2002
Relational databases and SQL






SQL is the language of relational databases
A simple one-table SELECT will be more or less the same whether you're
using a tiny database like mSQL or an expensive behemoth like Oracle
Enterprise
The big advantage for developers is that, after you learn SQL, you will be
able to interact with numerous databases across all platforms without a steep
retraining curve
Within the general guidelines of the ANSI and ECMA standards, there is
considerable difference between the products of individual companies and
the open source database development organizations
To find the database that will best suit your needs, make a list of the
functions you need in order of importance and then go out looking for the
product that best meets your needs
That said, a good deal of SQL is pretty standardized. You will be using a few
SQL statements over and over, no matter which specific product you deploy.
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
4
 Wiley and the book authors, 2002
The workhorses of SQL

The basic logical structure of an SQL database is very simple:





A given SQL installation can usually contain multiple databases
(e.g. one for products and another for customers and another for
employees, etc.)
Each database contains a number of tables
Each table is made up of carefully defined fields (columns)
Every entry in a table can be thought of as an added record (row)
Four data manipulation statements are supported by every SQL
server and will constitute an extremely high percentage of all
the things you want to do with a relational database: SELECT,
INSERT, UPDATE, and DELETE
These statements only manipulate database values, not the
structure of the database itself
 To modify the database's structure, you need to use commands
like DROP, ALTER, and CREATE
_______________________________________________________________________________________________________________

PHP Bible, 2nd Edition
5
 Wiley and the book authors, 2002
SELECT


SELECT is the main command you need to get information out
of an SQL database
The basic syntax is very simple:
SELECT field1, field2, field3
FROM table
WHERE (condition);

If you want entire records instead of picking out individual
fields by using the wildcard (asterisk) symbol:
SELECT *
FROM table
WHERE (condition);
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
6
 Wiley and the book authors, 2002
Joins (no, not joints)



Only one thing about SELECT statements is even slightly
difficult but very important: joins
A SELECT statement on a single table is easily imagined as
being something like a row in a spreadsheet
But an SQL database is by definition relational


If you think back to a time when you had to fill out a whole bunch
of forms: As you're writing down your name, address, phone, and
SSN for the 15th time, you probably wondered why you couldn't
have written the data one time and let them look it up as they need
it
With a relational database you can write down each piece of
information just once and then relate it to each other piece using
foreign keys
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
7
 Wiley and the book authors, 2002
Join (example tables)
Table: People
Table: Fears
Table:
Person_Fear
PersonID
Name
SSN
1
John Johnson
123-45-6789
2
FearID
Jane Jones
Fear
987-65-4321
1
Black cats
2
Friday the 13th
3
Heights
5
Flying
ID
PersonID
FearID
1
1
1
2
1
2
3
1
5
4
2
1
5
2
5
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
8
 Wiley and the book authors, 2002
Join example

To find out what phobias are suffered by Ms. Jones, you could execute 3
separate queries using the results from the previous queries in the subsequent
queries

SELECT PersonID FROM People WHERE (NAME = 'Jane Jones');


SELECT FearID FROM Person_Fear WHERE (PersonID = 2);


Returns 2 records with values of 1 and 5
SELECT Fear FROM Fears WHERE (FearID = 1 OR FearID = 5);


Returns 1 record with a value of 2
Returns 2 records with values "Black Cats" and "Flying"
Or you could execute a single query joining the tables together
SELECT Fears.Fear
FROM (Fears INNER JOIN Person_Fear ON FearID INNER JOIN People on
PersonID)
WHERE (People.Name = 'Jane Jones');

In effect, joins make two or more tables into one for purposes of searching
for a particular piece of information
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
9
 Wiley and the book authors, 2002
Join types


Joins come in several different flavors
The Join in the preceding example is called an inner join which
is the most common and restrictive type


Records will only be returned in which the conditions in the join
exist in all involved tables
Another common type is the outer join

This could be used to return a list of all fears even if they do not
have people attached to them in a left outer join (aka natural join)
SELECT Fear FROM Fears LEFT JOIN People on PersonID;
 Fears that have people bound to them would appear in the data set
multiple times, fears without people would also appear once

To get a list of all people even if they do not have fears bound to
them we could use a right outer join
SELECT Name FROM Fears RIGHT JOIN People on PesonID;
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
10
 Wiley and the book authors, 2002
Subselects



Subselects are more of a convenience than a necessity
They can be very handy if you're working with enormous
batches of data, but you can get the same result with two
simpler SELECTS
The subselect is faster if the subselect clause returns a large data
set, but there are cases where two selects will not appreciably
affect performance
SELECT phone_number
FROM table
WHERE name = 'SELECT name FROM table2 WHERE ID = 1';
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
11
 Wiley and the book authors, 2002
INSERT

The command to put new data into a database is INSERT
INSERT INTO table (col1, col2, col3) VALUES (val1, val2, val3);
Or
INSERT INTO table SET col1=val1, col2=val2, col3=val3;



Obviously the columns and their values need to match up in the
first example
If some of the rows will not have values for some of the fields,
you will need to use an empty, null, or auto-incremented value –
and, at a deeper level, you may need to have ensured beforehand
that fields can be nullable or auto-incrementable
If this is not possible, you should simply leave out any columns
you wish to default to an empty value in an INSERT statement
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
12
 Wiley and the book authors, 2002
UPDATE


UPDATE is used to modify information already in the database
without deleting any rows
You can selectively change some information without having to
delete an entire old record and insert a new one
UPDATE table SET field1=val1, field3=val3 WHERE (condition);



The conditional statement is just like a SELECT condition (e.g.
WHERE (ID > 15 AND ID < 21)
Be very wary of any UPDATE statements without a WHERE
clause, they will update ALL records in the selected table,
possibly destroying much needed data
Consequently, when you setup the database permissions on a
table, you should allow UPDATEs to just a minimal number of
competent users
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
13
 Wiley and the book authors, 2002
DELETE

DELETE is pretty self-explanatory, you use it to delete the
contents of one or more records permanently from the database
DELETE FROM table WHERE (condition);

The most important thing to remember is the condition – if you
don't set one, you will delete every record without a confirmation
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
14
 Wiley and the book authors, 2002
Database design



As should be obvious, learning to use an SQL database isn't
exactly rocket science – you can get a lot done with just a few
simple commands
The hard part is designing the database in the first place and, of
course, operating it in the real world over time
At the fundamental level, database design can be broken down
into the following mantra:
One to one,
One to many,
Many to many,
Many to one;
And always use a unique ID.
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
15
 Wiley and the book authors, 2002
One-to-one

An example of one-to-one data for Americans is the social
security number




Each US citizen has only one unique identifier; and it is, in fact, a
crime to use the social security number of another individual or
apply for more than one number
Database designers seize upon truly unique identifiers such as this
because almost every other piece of personal information is
subject to change – which accounts for the large number of
businesses who inappropriately use the social security number for
identification purposes.
Typically, in database design, data from one-to-one
relationships are maintained in a single table
There are situations in which you may want to represent this
data relationship in multiple tables (e.g. if you have data to
collect for an entity which is not pertinent to all entities)
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
16
 Wiley and the book authors, 2002
Many-to-one and one-to-many


Many-to-one and one-to-many data are the same, differing only
in how the columns are placed in a database
An example of one-to-many data comes form the medical realm


Patients to Visits: Each patient will always be a discrete individual
but may have any number of visits to the doctor
If you designed the table to represent visits to patients, it would
instantly become many-to-one data
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
17
 Wiley and the book authors, 2002
Many-to-many

Many-to-many data is well represented by the relationship of
authors to books



Not only can a given book have multiple authors, but each author
may have written or co-authored many books
This is not a matrix of relationships that would be easy to
represent efficiently in a spreadsheet, but it is precisely this
category of data at which relational databases most excel
The relationship of People to Fears from the previous example is
another example of a many-to-many relationship
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
18
 Wiley and the book authors, 2002
Relationships



Every data relationship falls into one of these categories
As a database designer, it's your job to decide which one of
these represents what you need to know in the way you need to
know it
As soon as you have a one-to-many, many-to-one, or many-tomany relationship, you're looking at going from a single table to
multiple tables
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
19
 Wiley and the book authors, 2002
Database design SQL


After you've decided on a database design, the mechanical
details of constructing the database are minimal
The main data structure statements of SQL are CREATE,
ALTER, and DROP

CREATE: Used to make a completely new table
CREATE TABLE tablename (
id_col INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
col1 TEXT NULL INDEX,
col2 DATE NOT NULL);


Different servers have different data types and definition options
DROP: Used to completely delete a table and its data
DROP TABLE tablename;
ALTER: Used to change a table's structure. Can be used to rename
a table, change a field's data type, add/delete fields, etc.
_______________________________________________________________________________________________________________

PHP Bible, 2nd Edition
20
 Wiley and the book authors, 2002
Privileges and Security


Security online is analogous to security in the real world: you
cannot make your home absolutely crime-proof, but you can
increase the difficulty and risk to a level where a large
percentage of intruders will choose to go to an easier target
down the block
Using a database with PHP can be similar to using two locks on
your front door if used properly
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
21
 Wiley and the book authors, 2002
Setting database permissions



The most fundamental rule of database use is to give each user or group only
the minimum permissions necessary to do what needs to be done
Besides the threat of malicious/experimental outsiders, setting the correct
permissions can protect you from frivolous mistakes by your coworkers and
yourself
A typical database permissions package might be something like:






Web visitor: SELECT only
Contributor: SELECT, INSERT, and maybe UPDATE
Editor: SELECT, INSERT, UPDATE, and maybe DELETE and/or GRANT
Root/Administrator: SELECT, INSERT, UPDATE, DELETE, GRANT, and
DROP
Passwords for users should not be the same as their system passwords or
stored in other locations in the database as plain text
Username/Passwords may also be set at the table level instead of for the
whole database

E.g. Sales people would have not valid reason for accessing data in the Personnel
tables and Personnel managers may have no reason for accessing customer data
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
22
 Wiley and the book authors, 2002
Keep database passwords out of the Web tree

It's a good idea to separate passwords from the Web pages that
use them


With PHP's inclusion functions (require_once, etc.), it's very easy
to drop in text (such as database passwords) from another file at
runtime which may not even be located in a directory accessible
from the web server
When you have many scripts using the same database, they can all
use the same password file. If the username/password gets
compromised, you just need to update the database with a new
password and update the single included PHP file
Because PHP SQL error messages could reveal a database's
username and whether or not it requires a password, it's a good
idea to not use the error functions (e.g. mysql_error) in
production systems, only in development
_______________________________________________________________________________________________________________

PHP Bible, 2nd Edition
23
 Wiley and the book authors, 2002
Use two layers of password protection

In order to further prevent security breaches in web-enabled
databases, you may want to implement even another layer of
protection via another round of usernames/passwords stored in
the database itself

You can use a PHP script to check the authentication of a user by
requiring them to fill out a logon form and checking that data
against what is stored in the database (use MD5 hashing of the
passwords stored in the database if possible)
If this is your HTML logon page:

<HTML><HEAD><TITLE>Logon please</TITLE></HEAD><BODY>
<FORM METHOD="POST" ACTION="form_check.php">
<TABLE>
<TR><TH>Username:</TH><TD><INPUT TYPE="text" NAME="try_user"></TD></TR>
<TR><TH>Password:</TH><TD><INPUT TYPE="password" NAME="try_pass"></TD></TR>
<TR><TD COLSPAN="2"><INPUT TYPE="submit"></TD></TR>
</TABLE>
</FORM></BODY></HTML>
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
24
 Wiley and the book authors, 2002
Use two layers of password protection (cont.)

Your form_check.php processing script could be:
<?php
/* webvars.inc is a file with $host, $db_user, and $password
defined */
require_once('webvars.inc');
mysql_connect($host,$db_user,$password) or die ('Can\'t connect');
mysql_select_db('web_log');
$username = $_POST['try_user'];
$query = "SELECT password FROM usertable WHERE (user='$username')";
$result = mysql_query($query) or die ('Unable to execute query');
$row = mysql_fetch_row($result);
if ($row[0] != MD5($_POST['try_pass']))
print('Thank you for using our web log');
else
{
mail('security@localhost','Database alert',"$username trying'.
'to break in");
die('Who do you think you are, go away… NOW…');
}
?>
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
25
 Wiley and the book authors, 2002
Learn to make backups




Finally, one of the major parts of database security may be
backing up
Take an hour, or so, to learn the best way to back up data in
your particular database (for example, via the mysqldump
command in MySQL), and then schedule regular backups right
away
Although equally important for critical databases, replication
will not save you from hackers or stupid co-workers (in-duhviduals) since any changes made to the database on the primary
server will be replicated on the backup servers
Maintaining a good backup of your databases is the only way to
retrieve or recreate data that has been mistakenly deleted or
otherwise destroyed
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
26
 Wiley and the book authors, 2002