PHP Bible Chapter 13 SQL Tutorial PHP Bible

PHP Bible Chapter 13: SQL Tutorial ________________________________________________________ PHP Bible, 2 nd Edition 1 Wiley and the book authors, 2002

Summary n n n Relational databases and SQL standards Basic SQL statements Designing SQL databases Privileges and security ________________________________________________________ PHP Bible, 2 nd Edition 2 Wiley and the book authors, 2002

SQL Introduction n n 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, 2 nd Edition 3 Wiley and the book authors, 2002

Relational databases and SQL n n n 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 m. SQL 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, 2 nd Edition 4 Wiley and the book authors, 2002

The workhorses of SQL n The basic logical structure of an SQL database is very simple: Ø Ø n 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, 2 nd Edition 5 Wiley and the book authors, 2002

SELECT n n SELECT is the main command you need to get information out of an SQL database The basic syntax is very simple: SELECT field 1, field 2, field 3 FROM table WHERE (condition); n If you want entire records instead of picking out individual fields by using the wildcard (asterisk) symbol: SELECT * FROM table WHERE (condition); ________________________________________________________ PHP Bible, 2 nd Edition 6 Wiley and the book authors, 2002

Joins (no, not joints) n n n 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 15 th 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, 2 nd Edition 7 Wiley and the book authors, 2002

Join (example tables) Table: People Table: Fears Table: Person_Fear Person. ID Name SSN 1 Johnson 123 -45 -6789 2 Fear. ID Jane Jones Fear 987 -65 -4321 1 Black cats 2 Friday the 13 th 3 Heights 5 Flying ID Person. ID Fear. ID 1 1 1 2 3 1 5 4 2 1 5 2 5 ________________________________________________________ PHP Bible, 2 nd Edition 8 Wiley and the book authors, 2002

Join example n 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 Person. ID FROM People WHERE (NAME = 'Jane Jones'); v Ø SELECT Fear. ID FROM Person_Fear WHERE (Person. ID = 2); v Ø Returns 2 records with values of 1 and 5 SELECT Fear FROM Fears WHERE (Fear. ID = 1 OR Fear. ID = 5); v n 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 Fear. ID INNER JOIN People on Person. ID) WHERE (People. Name = 'Jane Jones'); n In effect, joins make two or more tables into one for purposes of searching for a particular piece of information ________________________________________________________ PHP Bible, 2 nd Edition 9 Wiley and the book authors, 2002

Join types n n 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 Ø n 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 Person. ID; v 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 Peson. ID; ________________________________________________________ PHP Bible, 2 nd Edition 10 Wiley and the book authors, 2002

Subselects n n n 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 table 2 WHERE ID = 1'; ________________________________________________________ PHP Bible, 2 nd Edition 11 Wiley and the book authors, 2002

INSERT n The command to put new data into a database is INSERT INTO table (col 1, col 2, col 3) VALUES (val 1, val 2, val 3); Or INSERT INTO table SET col 1=val 1, col 2=val 2, col 3=val 3; Ø Ø Ø 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, 2 nd Edition 12 Wiley and the book authors, 2002

UPDATE n n 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 field 1=val 1, field 3=val 3 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, 2 nd Edition 13 Wiley and the book authors, 2002

DELETE n 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, 2 nd Edition 14 Wiley and the book authors, 2002

Database design n 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 one; And always use a unique ID. ________________________________________________________ PHP Bible, 2 nd Edition 15 Wiley and the book authors, 2002

One-to-one n An example of one-to-one data for Americans is the social security number Ø Ø n n 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, 2 nd Edition 16 Wiley and the book authors, 2002

Many-to-one and one-to-many n n 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, 2 nd Edition 17 Wiley and the book authors, 2002

Many-to-many n 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, 2 nd Edition 18 Wiley and the book authors, 2002

Relationships n n n 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, 2 nd Edition 19 Wiley and the book authors, 2002

Database design SQL n n 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, col 1 TEXT NULL INDEX, col 2 DATE NOT NULL); v Ø 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, 2 nd Edition 20 Wiley and the book authors, 2002

Privileges and Security n n 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, 2 nd Edition 21 Wiley and the book authors, 2002

Setting database permissions n n n 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: Ø Ø n n 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, 2 nd Edition 22 Wiley and the book authors, 2002

Keep database passwords out of the Web tree n 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 ________________________________________________________ n PHP Bible, 2 nd Edition 23 Wiley and the book authors, 2002

Use two layers of password protection n 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 Ø n 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 MD 5 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, 2 nd Edition 24 Wiley and the book authors, 2002

Use two layers of password protection (cont. ) n 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] != MD 5($_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, 2 nd Edition 25 Wiley and the book authors, 2002

Learn to make backups n n 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 My. SQL), 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, 2 nd Edition 26 Wiley and the book authors, 2002