Web Programming Step by Step, 2nd Edition

Lecture Extra: Database Definition, Data Sanitization, and Data Manipulation

Reading: 13.2; Appendix B

Except where otherwise noted, the contents of this document are Copyright 2012 Marty Stepp, Jessica Miller, and Victoria Kirst. All rights reserved. Any redistribution, reproduction, transmission, or storage of part or all of the contents in any form is prohibited without the author's expressed written permission.

CRUD applications

Most web sites do four general tasks ("CRUD") with data in a database:

• Create new rows
• Read existing data (SELECT)
• Update / modify values in existing rows
• Delete rows

In lecture we explored only the "R", but now let's examine the others...

Learning about databases and tables

SHOW DATABASES;
USE database;
SHOW TABLES;
DESCRIBE table;

mysql> USE simpsons;
mysql> SHOW TABLES;
+-----------+
| students  | 
| courses   | 
| grades    | 
| teachers  |
+-----------+
4 rows in set

The SQL INSERT statement

INSERT INTO table
VALUES (value, value, ..., value);

INSERT INTO students
VALUES (789, "Nelson", "muntz@fox.com", "haha!");

• adds a new row to the given table
• columns' values should be listed in the same order as in the table
• How would we record that Nelson took CSE 190M and got a D+ in it?

More about INSERT

INSERT INTO table (columnName, columnName, ..., columnName)
VALUES (value, value, ..., value);

INSERT INTO students (name, email)
VALUES ("Lewis", "lewis@fox.com");

• some columns have default or auto-assigned values (such as IDs)
• omitting them from the INSERT statement uses the defaults

The SQL REPLACE statement

REPLACE INTO table (columnName, columnName, ..., columnName)
VALUES (value, value, ..., value);

REPLACE INTO students
VALUES (789, "Martin", "prince@fox.com");

• just like INSERT, but if an existing row exists for that key (ID), it will be replaced
• can pass optional list of column names, like with INSERT

The SQL UPDATE statement

UPDATE table
SET column = value,
    ...,
    column = value
WHERE column = value;

UPDATE students
SET email = "lisasimpson@gmail.com"
WHERE id = 888;

• modifies an existing row(s) in a table
• BE CAREFUL! If you omit the WHERE, it modifies ALL rows

The SQL DELETE statement

DELETE FROM table
WHERE condition;

DELETE FROM students
WHERE id = 888;

• removes existing row(s) in a table
• can be used with other syntax like LIMIT, LIKE, ORDER BY, etc.
• BE CAREFUL! If you omit the WHERE, it deletes ALL rows

Creating and deleting an entire database

CREATE DATABASE name;
DROP DATABASE name;

CREATE DATABASE warcraft;

• adds/deletes an entire database from the server

Creating and deleting a table

CREATE TABLE name (
	columnName type constraints,
	...
	columnName type constraints
);

DROP TABLE name;

CREATE TABLE students (
	id INTEGER,
	name VARCHAR(20),
	email VARCHAR(32),
	password VARCHAR(16)
);

• adds/deletes a table from this database
• all columns' names and types must be listed (see next slide)

SQL data types

• quick reference

Column constraints

CREATE TABLE students (
	id INTEGER UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(20) NOT NULL,
	email VARCHAR(32),
	password VARCHAR(16) NOT NULL DEFAULT "12345"
);

• NOT NULL: not allowed to insert a null/empty value in any row for that column
• PRIMARY KEY / UNIQUE: no two rows can have the same value
• DEFAULT value: if no value is provided, use the given default
• AUTO_INCREMENT: default value is the last row's value plus 1 (useful for IDs)
• UNSIGNED: don't allow negative numbers (INTEGER only)

Rename a table

ALTER TABLE name RENAME TO newName;

ALTER TABLE students RENAME TO children;

• changes the name of an existing table

Add/remove/modify a column in a table

ALTER TABLE name
	ADD COLUMN columnName type constraints;

ALTER TABLE name DROP COLUMN columnName;

ALTER TABLE name
	CHANGE COLUMN oldColumnName newColumnName type constraints;

• adds/deletes/respecifies a column in an existing table
• if a column is added, all existing rows are given a default value for that column

Granting permission to a user

GRANT permission
	ON database.table
	TO 'username'@'server';

GRANT SELECT ON simpsons.grades TO 'homer'@'localhost';
GRANT * ON simpsons.* TO 'daisy'@'localhost';

• necessary to give a user permissions (once) before they can use your database

Pure Evil

Never Trust User Input

• Okay so maybe they aren't all evil, but you should still never trust their input
• Always double check that values sent to your pages are expected ones, never blindly use them
  • Inputs with specified values: make sure the values submitted are one of the ones you specified
  • Inputs that take user data: never blindly trust it
  • This happens all the time in real life.
    • With database queries
    • With powerful commands

Never Trusting Database Input

As far as we've, this is how you do a database query. Is this good or bad?

# get email submitted
$email = $_POST["email"];

# connect to my database
$db = new PDO("mysql:dbname=turnin;host=localhost",
              "turninsystem", "th3yw1llN3ver9ue5s!");

# Get previous submissions for students
$db->query("SELECT * FROM submissions 
            WHERE email = '{$email}';");

... display submissions  ...
	

SQL Injection

• BAD! We are trusting the user to not enter malicious things!
• If a user entered '; DROP TABLE submissions;' as their username, we would loose all submissions!
• This is called SQL injection and there are people who make a living using this type of exploit
• NEVER TRUST USERS

Preventing SQL Injection

• There is a PDO method called quote that will make sure all special characters are escaped in a string so its safe to use in a query
• This requires that you call quote on all variables you want to use
• If you forget to call quote on a single variable, your query is still vulnerable!
• What if we could have PDO handle all of this for us?...

More PDO object methods

PDO objects can actually take care of most injection cases for us if we are careful

• When a statement is prepared, only the string input in the call to prepare is treated as executable.
• You specify where you want user data to go with things called labels
• Any data added later, will not be executed!

Better Query with User Data

Using this new method we can fix our previous query

# get email submitted
$email = $_POST["email"];

# connect to my database
$db = new PDO("mysql:dbname=turnin;host=localhost",
              "turninsystem", "th3yw1llN3ver9ue5s!");

# prepare query, stop bad people
$query = $db->prepare('SELECT * FROM submissions WHERE email = :email;');

# Get previous submissions for students
$query->execute();
foreach($query as $row) { ... } 
	

But this will give an error, any guesses why?

More PDOStatement methods

If you don't specify all the labels, it will crash!

• We use bindValue and bindParam to specify data for the labels
• bindValue will take whatever value it is given and add it to the query right away
• bindParam will keep track of the variable you pass in and only take the value when you call execute
• Both take a third parameter that specifies a data type, possible values:
  • PDO::PARAM_STR - Use for all non-numeric columns (VARCHAR, TEXT, etc)
  • PDO::PARAM_INT - Use for all numeric columns (INT, DOUBLE, BOOLEAN, etc)

Best Query with User Data

Now we can have a fully working query!

# get email submitted
$email = $_POST["email"];

# connect to my database
$db = new PDO("mysql:dbname=turnin;host=localhost",
              "turninsystem", "th3yw1llN3ver9ue5s!");

# prepare query, stop bad people
$query = $db->prepare('SELECT * FROM submissions WHERE email = :email;');
$query->bindValue(':email', $email, PDO::PARAM_STR);

# Get previous submissions for students
$query->execute();
foreach($query as $row) { ... }
	

Best Practices

• Use prepared PDOStatements along with other validation checks.
• Don't rely soley on one type of validation
  • If one method is coded wrong, others may save you!
• Now all of you can do the 4th Extra Credit for Kevin Bacon!

Database design principles

• database design : the act of deciding the schema for a database
• database schema: a description of what tables a database should have, what columns each table should contain, which columns' values must be unique, etc.
• some database design principles:
  • keep it simple, stupid (KISS)
  • provide an identifier by which any row can be uniquely fetched
  • eliminate redundancy, especially of lengthy data (strings)
    • integers are smaller than strings and better to repeat
  • favor integer data for comparisons and repeated values
    • integers are smaller than strings and better to repeat
    • integers can be compared/searched more quickly than strings, real numbers

First database design

• what's good and bad about this design?
  • good: simple (one table), can see all data in one place
  • bad: redundancy (name, email, course repeated frequently)
  • bad: most searches (e.g. find a student's courses) will have to rely on string comparisons
  • bad: there is no single column whose value will be unique in each row

Improved database design

• normalizing: splitting tables to improve structure / redundancy (linked by unique IDs)
• primary key: a column guaranteed to be unique for each record (e.g. Lisa Simpson's ID 888)
• foreign key: a column in table A storing a primary key value from table B
  • (e.g. records in grades with student_id of 888 are Lisa's grades)

Database design exercise

Suppose we want to write a web store like Amazon.com. The store sells products that can be purchased by customers online. The customer can add items to their shopping cart and then order them. The customer can also check the order's status, whether it has shipped, etc.

• What are some database tables and columns we could use?
• Is your design normalized? Does it have any redundancy?