Database Coding for
Password Management
Dr. Michele C. Weigle
CS 312 - Internet Concepts
Old Dominion University
Many of these slides created by Dr. Ralph Grove
Agenda
1. Introduction to RDBMS & SQL
2. Basic SQL Commands – define, view, update a table
3. SQLite Commands
4. PHP – Insert, Delete, Fetch (Pet Registry)
5. PHP – User Registration & Authorization
2
Introduction to RDBMS
• RDBMS: relational database management system
• used by most Internet/Web applications for data persistence (storage)
• common systems: Oracle, MySQL, SQLite, etc.
• Relation: in mathematics, a cross-product subset
• e.g., {a,b,c} X {0,1,2} = {(a,0), (a,1), (a,2), (b,0), ..., (c,2)}
• r1 = {(a,1), (c,2)}
relations
• r2 = {(a,0), (a,1), (c,0)}
3
Introduction to RDBMS
• Relation: a mathematical model of a data table
• e.g., User Table (a relation on all possible field values)
UserId Name Salt enc(Password) Address ...
rgrove Ralph Grove X71C77jE93 e7idkd^jehj*s 123 Main St.
8
jdoe Jane Doe C98EE3692 ijf82ksajaf&^3 456 High St.
89
...
• Each database contains many tables
• e.g., E-commerce database: User table, Product table, Order table, ...
• e.g., Student database: Student table, Class table, Registration table, ...
4
Introduction to RDBMS
A table can represent
• Student Records Database - a person
- a thing
- an event (transaction)
Student Table
FinAid Table
Admissions Table Registration Table ETC.
Class Table Instructor Table
5
SQL – Structured Query Language
• A language for accessing RDBs
• ISO/ANSI standard
• Supports CRUD operations (create, retrieve, update, delete)
• e.g.,
• Registration: Create a new user record
• Login: Retrieve user record (to validate credentials)
• Profile Change: Update user record
• Delete Account: Delete user record
6
SQL Data Types
• INT integer, 32 bits, signed or unsigned
• DECIMAL(p,s) p-digit decimal number, with s fractional digits
e.g., DECIMAL(6,2) - 9999.99
• CHAR(n) fixed length string, n characters
• VARCHAR(m)variable length string, up to m characters
• DATE yyyy-mm-dd
• TIME hh:mm:ss
• etc.
• NULL is a special value (any data type) that indicates –no value assigned–
7
Think, Discuss, Explain *
• What format will data have for these data types?
• INT
• DECIMAL(9, 4)
• VARCHAR(256)
• TIME
8
Basic SQL
• Define a table: CREATE TABLE
• View table contents: SELECT
• Update table data: UPDATE
• Delete table data: DELETE
https://www.sqlitetutorial.net 9
SQL – Create a Database Table
• Create a Pet table
CREATE TABLE pet (
species CHAR(1), /* D=dog, C=cat, B=bird,
F=fish */
name VARCHAR(25),
owner VARCHAR(25),
dob DATE,
PRIMARY KEY(name) /* the unique identifier for each pet
*/
species name * owner dob
);
C Fluffy Jane 2014-03-12
D Rover Susan 2019-11-13
B Polly John 2018-05-18
10
SQL – Add a Row to a Table
• Add a Pet to the table
INSERT INTO pet VALUES ('D', 'Fido', 'Robert', '2017-09-
15');
species name * owner dob
C Fluffy Jane 2014-03-12
D Rover Susan 2019-11-13
B Polly John 2018-05-18
D Fido Robert 2017-09-15
• NULL value – used for unknown values
INSERT INTO pet VALUES ('D', 'Fido', NULL, '2017-09-
15'); 11
SQL – Change or Delete a Row Warning: Without
the WHERE clause,
ALL rows will be
affected
• Update a Pet owner's name
UPDATE pet SET owner='Steve' WHERE name='Fido';
• Delete a Pet
DELETE FROM pet WHERE name='Rover';
species name * owner dob
C Fluffy Jane 2014-03-12
D Rover Susan 2019-11-13
B Polly John 2018-05-18
D Fido Robert Steve 2017-09-15
12
SQL – Retrieve Data
• Retrieve an entire table
SELECT * FROM pet;
• Retrieve one record from a table
SELECT * FROM pet WHERE name='Fido';
• Retrieve one field from one record from a table
SELECT owner FROM pet WHERE name='Fido';
• Retrieve entire table sorted by one field
SELECT * FROM pet ORDER BY dob ASC;
• Data are returned as an iterable data structure
13
Think, Discuss, Explain *
INSERT INTO pet VALUES ('C','Fluffy','Dan',NULL),
('F','Sherm',NULL,NULL), ('D','Fido','Jack','2014-09-10'),
('B','Polly',NULL,'2018-02-09'), ('C','Kitkat','Will','2020-05-
03'), ('d','Buddy','Jane',NULL);
MODIFY pet SET owner='Fred' WHERE name='Polly';
To Fix: MODIFY should be UPDATE
DELETE FROM pet WHERE owner='Jack';
MODIFY pet SET species='D',dob='2015-11-01' WHERE species='d';
1. How many rows will be left in the table?
2. What name will be in the last row?
3. How many rows will have NULL values?
14
SQLite Commands
• SQLite: A simple, easy to use RDBMS
• integrates well with PHP
• data are stored in a flat file (unstructured text file) makedb.sql
CREATE TABLE pet(
...
• Creating an SQLite database );
% sqlite3 pet.db < makedb.sql INSERT INTO pet
VALUES(
% chgrp http pet.db ...
% chmod 770 pet.db );
• This creates the file "pet.db", containing the pet database
15
SQLite Commands Commands don't
have to be all caps
• Using the command interpreter
open the database
% sqlite3 pet.db
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> insert into pet values('C', 'Fluffy', 'Ron', '2015-
06-02');
sqlite> insert into pet values ('D', 'Spot', 'Jane', '2014-
11-22');
sqlite> select * from pet;
C|Fluffy|Ron|2015-06-02
D|Spot|Jane|2014-11-22
sqlite> .quit close the database
16
SQLite3 Online Demo
17
Think, Discuss, Explain *
• Find three errors in this SQL code
create table contact (
fname varchar(10),
lname varchar(25),
phone decimal(10,0),
email varchar(50),
type char(3),
age (int),
primary key(fname, lname)
);
insert into contact values
('Jane','Doe',7574441234,'
[email protected]','IND',35),
('Fred','Jones',5551231234,'
[email protected]',52,'FAM'),
('Jim','Fallows',6058881234,'
[email protected]','COL',48);
select email from contact where 'Jones';
18
PHP – Retrieve Records (Pet Registry)
• Formulate Query
$query = 'SELECT * FROM pet'; // get all pets
• Execute the Query
$result = $db->query($query); // execute the query
• Iterate over the Result
while ($pet = $result->fetchArray(SQLITE3_ASSOC) ) { // get next row
$name = $pet['name'];
...
}
https://www.cs.odu.edu/~mweigle/cs312/examples/#database
https://www.cs.odu.edu/~mweigle/cs312/rgrove/database/pet.php (source) 19
PHP – Retrieve Records (Pet Registry)
<table border="1"> Create an HTML table from a database table
<tr><th>Species</th><th>Name</th><th>Owner</th><th>DOB</th></tr>
<?php
$db = new SQLite3('pet.db'); // open the DBreturn sorted list
$query = 'SELECT * FROM pet'; // get all petsSELECT * FROM pet ORDER BY dob
ASC;
$result = $db->query($query); // execute the query
while ($pet = $result->fetchArray(SQLITE3_ASSOC) ) { // get next row
echo '<tr><td>' . $pet['species'] . '</td><td>' . $pet['name'] .
'</td><td>' . $pet['owner'] . '</td><td>' . $pet['dob'] . '</td></tr>';
}
$db->close(); // close the DB
?>
</table>
20
https://www.cs.odu.edu/~mweigle/cs312/rgrove/database/pet.php (source)
PHP – Insert Pet (Pet Registry)
<?php Insert a pet using form data
$species = $_POST['species'];
$name = $_POST['name'];
(addpet_handler.php)
...
$db = new SQLite3('pet.db'); // open the DB
// TO ADD: make sure not adding the same pet name
$command = "INSERT INTO pet VALUES('" . $species . "', '" . $name .
"', '" . $owner . "', '" . $dob . "')";
$result = $db->exec($command); // execute the command
if ($result) {
// ... row added ...
}
$db->close();
?>
21
https://www.cs.odu.edu/~mweigle/cs312/rgrove/database/addpet_handler.php (source)
PHP – Insert Pet (Pet Registry)
Make sure not adding the same pet name
(addpet_handler.php)
// check that primary key 'name' doesn't already exist in db
$query = "SELECT name FROM pet WHERE name='" . $name . "'";
$result = $db->query($query);
if ($result->fetchArray()[0] != null) {
// pet name already in database
$db->close();
require('pet.php');
return;
}
22
https://www.cs.odu.edu/~mweigle/cs312/rgrove/database/addpet_handler.php (source)
PHP – Insert Pet (Pet Registry)
Insert a pet using form data
(addpet_handler.php)
$command = "INSERT INTO pet VALUES('" . $species . "', '" . $name .
"', '" .
$owner . "', '" . $dob . "')";
$command:
INSERT INTO pet VALUES('Fido', 'D', 'Fred', '2019-05-04')
Form Data
23
https://www.cs.odu.edu/~mweigle/cs312/rgrove/database/addpet_handler.php (source)
Think, Discuss, Explain *
• How many rows of data will a SELECT statement return?
• What is the purpose of this PHP statement:
$row = $result->fetchArray(SQLITE3_ASSOC)
• Is this a legal insert command?
insert into pet values(NULL, NULL, NULL, NULL);
24
PHP – User Registration & Authorization
• Process Steps
1. Register new user
• generate salt, encrypt password, add user to database
2. Validate user login
• encrypt entered password, compare to stored value in database
• if successful, add authentication tokens to session
3. Validate request for secure resources
• check for authentication tokens in session
4. Logout user
• remove authentication tokens from session
25
PHP – Password Hash Function
• Generate a password "hash" one-way
• encrypted (password + salt) encryption:
$password = $_REQUEST['password']; // registration decryption is
$hash = password_hash($password, PASSWORD_DEFAULT); not possible
$2y$10$zffc9DxpzZmwMeh0D/7X4O0cyJu9UJ79ARu.1P1Hji2V/
ef7C6Ka2
alg cost salt hash (encrypted password+salt)
• Validate the login password:
$password = $_REQUEST['password']; // login
$valid = password_verify($password, $hash);
if ($valid) {
// ... login OK ...
} 26
password_hash - PHP Manual
Registration with Database Example
https://www.cs.odu.edu/~mweigle/cs312/examples/#database
https://www.cs.odu.edu/~mweigle/cs312/rgrove/userauth-db/index.php (source) 27
PHP – Register New User register_action.php
$userid = $_POST['userid'];
$password = $_POST['password'];
// ... input other fields; validate fields ...
$db = new SQLite3('user.db');
// check that primary key userid doesn't already exist in db
$query = "SELECT userid FROM user WHERE userid='" . $userid . "'";
$result = $db->query($query);
if ($result->fetchArray()[0] != null) {
// userid already in database
$db->close();
$_SESSION['message'] = "Userid already exists!";
require('register.php'); // load in registration page
return; // don't execute any more statements below here
}
28
https://www.cs.odu.edu/~mweigle/cs312/rgrove/userauth-db/register_action.php (source)
PHP – Register New User register_action.php
// generate password hash
$hash = password_hash($password, PASSWORD_DEFAULT);
// add to database
$command =
"INSERT INTO user VALUES('" . $userid . "', '" . $hash . "')";
$result = $db->exec($command);
if ($result) {
// registration success
$_SESSION['message'] = "New user registered";
require('index.php');
} else {
$_SESSION['message'] = "Registration failed";
require('register.php');
}
29
https://www.cs.odu.edu/~mweigle/cs312/rgrove/userauth-db/register_action.php (source)
PHP – Validate User Login login_action.php
$userid = $_POST['userid'];
$password = $_POST['password'];
// ... validate fields ...
$db = new SQLite3('user.db');
// look for user in database
$query = "SELECT userid, password FROM user WHERE userid='" . $userid .
"'";
$result = $db->query($query);
if ($result) {
// user exists
$row = $result->fetchArray(SQLITE3_ASSOC);
$hash = $row['password'];
// validate password
$valid = password_verify($password, $hash);
30
https://www.cs.odu.edu/~mweigle/cs312/rgrove/userauth-db/login_action.php (source)
PHP – Validate User Login login_action.php
if ($valid) { store
// valid password authorization
$_SESSION['userid'] = $userid; credentials in
$_SESSION['logged_in'] = TRUE;
the session
$_SESSION['message'] = $_SESSION['userid'] . " logged in";
$db->close();
require('index.php');
}
else {
// invalid password
unset($_SESSION['userid']);
unset($_SESSION['logged_in']);
$_SESSION['message'] = "Invalid credentials - please try
again";
$db->close();
require('login.php');
}
31
} https://www.cs.odu.edu/~mweigle/cs312/rgrove/userauth-db/login_action.php (source)
PHP – Validate Secure Resource Request
• login_check.php
if (empty($_SESSION['logged_in'] || ! $_SESSION['logged_in'] ) {
header ('Location: login.php'); // redirect to login page
}
• securepage.php
include('login_check.php');
32
PHP – Logout User
• logout_action.php
unset($_SESSION['userid']);
unset($_SESSION['logged_in']); // clear the
session
$_SESSION['message'] = "Logged Out";
header("Location: index.php"); // redirect to
home page
33
Process Steps
1. Register new user
2. Validate user login
3. Validate request for secure resources
4. Logout user
https://www.cs.odu.edu/~mweigle/cs312/examples/#database
https://www.cs.odu.edu/~mweigle/cs312/rgrove/userauth-db/index.php (source) 34
Think, Discuss, Explain *
• What is a password hash?
• Where is the user's current authorization status stored?
• What should happen when a user logs out?
• Why is it necessary to store the salt in the user's database record?
35
Recap
1. Introduction to RDBMS & SQL
2. Basic SQL Commands – define, view, update a table
3. SQLite Commands
4. PHP – Insert, Delete, Fetch (Pet Registry)
5. PHP – User Registration & Authorization
36
