UNIT –IV

WORKING WITH DATABASE

With PHP, you can connect to and manipulate databases.

MySQL is the most popular database system used with PHP.

What is MySQL?

 MySQL is a database system used on the web

 MySQL is a database system that runs on a server
 MySQL is ideal for both small and large applications
 MySQL is very fast, reliable, and easy to use
 MySQL uses standard SQL
 MySQL compiles on a number of platforms
 MySQL is free to download and use
 MySQL is developed, distributed, and supported by Oracle Corporation
 MySQL is named after co-founder Monty Widenius's daughter: My

The data in a MySQL database are stored in tables. A table is a collection of related data, and
it consists of columns and rows.

PHP 5 and later can work with a MySQL database using:

 MySQLi extension (the "i" stands for improved)

 PDO (PHP Data Objects)

Should I Use MySQLi or PDO?

 If you need a short answer, it would be "Whatever you like".
 Both MySQLi and PDO have their advantages:
 PDO will work on 12 different database systems, whereas MySQLi will
only work with MySQL databases.
 So, if you have to switch your project to use another database, PDO
makes the process easy. You only have to change the connection
string and a few queries. With MySQLi, you will need to rewrite the
entire code - queries included.
 Both are object-oriented, but MySQLi also offers a procedural API.
 Both support Prepared Statements. Prepared Statements protect from
SQL injection, and are very important for web application security.

Create a MySQL Database Using MySQLi and PDO

The CREATE DATABASE statement is used to create a database in MySQL.
The following examples create a database named "myDB":

 <?php
$servername = "localhost";
$username = "username";
$password = "password";

// Create connection
$conn = new mysqli($servername, $username, $password);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

// Create database
$sql = "CREATE DATABASE myDB";
if ($conn->query($sql) === TRUE) {
echo "Database created successfully";
} else {
echo "Error creating database: " . $conn->error;
}

$conn->close();
?>

Example (PDO)
<?php
$servername = "localhost";
$username = "username";
$password = "password";

try {
$conn = new PDO("mysql:host=$servername;dbname=myDB", $username,
$password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "CREATE DATABASE myDBPDO";
// use exec() because no results are returned
$conn->exec($sql);
echo "Database created successfully<br>";
}
catch(PDOException $e)
{
echo $sql . "<br>" . $e->getMessage();
}
$conn = null;
?>

Create a MySQL Table Using MySQLi and PDO

The CREATE TABLE statement is used to create a table in MySQL.

We will create a table named "MyGuests", with five columns: "id", "firstname", "lastname",
"email" and "reg_date":

CREATE TABLE MyGuests (

id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
)

Notes on the table above:

The data type specifies what type of data the column can hold. For a
complete reference of all the available data types, go to our Data Types
reference.

After the data type, you can specify other optional attributes for each
column:

 NOT NULL - Each row must contain a value for that column, null values
are not allowed
 DEFAULT value - Set a default value that is added when no other value
is passed
 UNSIGNED - Used for number types, limits the stored data to positive
numbers and zero
 AUTO INCREMENT - MySQL automatically increases the value of the
field by 1 each time a new record is added
 PRIMARY KEY - Used to uniquely identify the rows in a table. The
column with PRIMARY KEY setting is often an ID number, and is often
used with AUTO_INCREMENT

Each table should have a primary key column (in this case: the "id" column).
Its value must be unique for each record in the table.

MYSQL-i

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

// sql to create table

$sql = "CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
)";

if ($conn->query($sql) === TRUE) {

echo "Table MyGuests created successfully";
} else {
echo "Error creating table: " . $conn->error;
}

$conn->close();
?>

Example (PDO)
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";

try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname",
$username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// sql to create table

$sql = "CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
)";
 // use exec() because no results are returned
$conn->exec($sql);
echo "Table MyGuests created successfully";
}
catch(PDOException $e)
{
echo $sql . "<br>" . $e->getMessage();
}

$conn = null;
?>

SQL JOIN
A JOIN clause is used to combine rows from two or more tables, based on a
related column between them.

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate

FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerI

Different Types of SQL JOINs

Here are the different types of the JOINs in SQL:

 (INNER) JOIN: Returns records that have matching values in both

tables
 LEFT (OUTER) JOIN: Return all records from the left table, and the
matched records from the right table
 RIGHT (OUTER) JOIN: Return all records from the right table, and
the matched records from the left table
 FULL (OUTER) JOIN: Return all records when there is a match in
either left or right table
SQL INNER JOIN Keyword
The INNER JOIN keyword selects records that have matching values in both
tables.

SELECT column_name(s)
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

SQL LEFT JOIN Keyword

The LEFT JOIN keyword returns all records from the left table (table1), and
the matched records from the right table (table2). The result is NULL from
the right side, if there is no match.

SELECT column_name(s)
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;

SQL RIGHT JOIN Keyword

The RIGHT JOIN keyword returns all records from the right table (table2),
and the matched records from the left table (table1). The result is NULL from
the left side, when there is no match.

SELECT column_name(s)
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;

In some databases RIGHT JOIN is called RIGHT OUTER JOIN.

SQL FULL OUTER JOIN Keyword

The FULL OUTER JOIN keyword return all records when there is a match in
either left (table1) or right (table2) table records.
Note: FULL OUTER JOIN can potentially return very large result-sets!

FULL OUTER JOIN Syntax

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;

MySQL Aggregate Functions Tutorial : SUM, AVG, MAX, MIN , COUNT,

DISTINCT

Aggregate Functions are all about

 Performing calculations on multiple rows

 Of a single column of a table
 And returning a single value.

The ISO standard defines five (5) aggregate functions namely;

1) COUNT
2) SUM
3) AVG
4) MIN
5) MAX

Why use aggregate functions.

From a business perspective, different organization levels have different
information requirements. Top levels managers are usually interested in knowing
whole figures and not necessary the individual details.

>Aggregate functions allow us to easily produce summarized data from our

database.

For instance, from our myflix database , management may require following
reports

 Least rented movies.

 Most rented movies.
 Average number that each movie is rented out in a month.

We easily produce above reports using aggregate functions.

Let's look into aggregate functions in detail.

 COUNT Function
The COUNT function returns the total number of values in the specified field. It
works on both numeric and non-numeric data types. All aggregate functions by
default exclude nulls values before working on the data.

COUNT (*) is a special implementation of the COUNT function that returns the
count of all the rows in a specified table. COUNT (*) also considers Nulls and
duplicates.

The table shown below shows data in movierentals table

reference_ transaction_ date return_date membership_ number movie_id movie_

number returned

11 20-06-2012 NULL 1 1 0

12 22-06-2012 25-06-2012 1 2 0

13 22-06-2012 25-06-2012 3 2 0

14 21-06-2012 24-06-2012 2 2 0

15 23-06-2012 NULL 3 3 0

Let's suppose that we want to get the number of times that the movie with id 2
has been rented out

SELECT COUNT(`movie_id`) FROM `movierentals` WHERE `movie_id` = 2;

Executing the above query in MySQL workbench against myflixdb gives us the
following results.

COUNT('movie_id')

3
 DISTINCT Keyword

The DISTINCT keyword that allows us to omit duplicates from our results. This is
achieved by grouping similar values together .

To appreciate the concept of Distinct, lets execute a simple query

SELECT `movie_id` FROM `movierentals`;

movie_id

Now let's execute the same query with the distinct keyword -

SELECT DISTINCT `movie_id` FROM `movierentals`;

As shown below , distinct omits duplicate records from the results.

movie_id

MIN function
The MIN function returns the smallest value in the specified table field.
 As an example, let's suppose we want to know the year in which the oldest movie
in our library was released, we can use MySQL's MIN function to get the desired
information.

The following query helps us achieve that

SELECT MIN(`year_released`) FROM `movies`;

Executing the above query in MySQL workbench against myflixdb gives us the
following results.

MIN('year_released')

2005

MAX function
Just as the name suggests, the MAX function is the opposite of the MIN function.
It returns the largest value from the specified table field.

Let's assume we want to get the year that the latest movie in our database was
released. We can easily use the MAX function to achieve that.

The following example returns the latest movie year released.

SELECT MAX(`year_released`) FROM `movies`;

Executing the above query in MySQL workbench using myflixdb gives us the
following results.

MAX('year_released')

2012

SUM function
Suppose we want a report that gives total amount of payments made so far. We
can use the MySQL SUM function which returns the sum of all the values in
the specified column. SUM works on numeric fields only. Null values are
excluded from the result returned.
 payment_ membership_ payment_ description amount_ external_ reference
id number date paid _number

1 1 23-07-2012 Movie rental 2500 11

payment

2 1 25-07-2012 Movie rental 2000 12

payment

3 3 30-07-2012 Movie rental 6000 NULL

payment

The following table shows the data in payments table-

The query shown below gets the all payments made and sums them up to return
a single result.

SELECT SUM(`amount_paid`) FROM `payments`;

Executing the above query in MySQL workbench against the myflixdb gives the
following results.

SUM('amount_paid')

10500

AVG function
MySQL AVG function returns the average of the values in a specified
column. Just like the SUM function, it works only on numeric data types.

Suppose we want to find the average amount paid. We can use the following
query -

SELECT AVG(`amount_paid`) FROM `payments`;

Executing the above query in MySQL workbench, gives us the following results.

AVG('amount_paid')

3500
Summary
 MySQL supports all the five (5) ISO standard aggregate functions COUNT,
SUM, AVG, MIN and MAX.
 SUM and AVG functions only work on numeric data.
 If you want to exclude duplicate values from the aggregate function results,
use the DISTINCT keyword. The ALL keyword includes even duplicates. If
nothing is specified the ALL is assumed as the default.
 Aggregate functions can be used in conjunction with other SQL clauses
such as GROUP BY

What is a SQL Injection?

SQL Injection is an attack that poisons dynamic SQL statements to comment out
certain parts of the statement or appending a condition that will always be true. It
takes advantage of the design flaws in poorly designed web applications to
exploit SQL statements to execute malicious SQL code.

How SQL Injection Works

The types of attacks that can be performed using SQL injection vary depending
on the type of database engine. The attack works on dynamic SQL
statements. A dynamic statement is a statement that is generated at run time
using parameters password from a web form or URI query string.

Let’s consider a simple web application with a login form. The code for the HTML
form is shown below.

<form action=‘index.php’ method="post">

<input type="email" name="email" required="required"/>

<input type="password" name="password"/>

<input type="checkbox" name="remember_me" value="Remember me"/>

<input type="submit" value="Submit"/>

</form>

HERE,

 The above form accepts the email address, and password then submits
them to aPHP file named index.php.
  It has an option of storing the login session in a cookie. We have deduced
this from the remember_me checkbox. It uses the post method to submit
data. This means the values are not displayed in the URL.

Let’s suppose the statement at the backend for checking user ID is as follows

SELECT * FROM users WHERE email = $_POST['email'] AND password =

md5($_POST['password']);

HERE,

 The above statement uses the values of the $_POST[] array directly
without sanitizing them.
 The password is encrypted using MD5 algorithm.

Step 1) Enter this code in left pane

CREATE TABLE `users` (

`id` INT NOT NULL AUTO_INCREMENT,
`email` VARCHAR(45) NULL,
`password` VARCHAR(45) NULL,
PRIMARY KEY (`id`));

insert into users (email,password) values ('[email protected]',md5('abc'));

Step 2) Click Build Schema

Step 3) Enter this code in right pane

select * from users;

Step 4) Click Run SQL. You will see the following result

Suppose user supplies [email protected] and 1234 as the password. The

statement to be executed against the database would be

SELECT * FROM users WHERE email = '[email protected]' AND password =

md5('1234');

The above code can be exploited by commenting out the password part and
appending a condition that will always be true. Let’s suppose an attacker
provides the following input in the email address field.
[email protected]' OR 1 = 1 LIMIT 1 -- ' ]

xxx for the password.

The generated dynamic statement will be as follows.

SELECT * FROM users WHERE email = '[email protected]' OR 1 = 1 LIMIT 1 -- ' ]

AND password = md5('1234');

HERE,

 [email protected] ends with a single quote which completes the string quote
 OR 1 = 1 LIMIT 1 is a condition that will always be true and limits the
returned results to only one record.
 -- ' AND … is a SQL comment that eliminates the password part.

Copy the above SQL statement and paste it in SQL FiddleRun SQL Text box as
shown below

Hacking Activity: SQL Inject a Web Application

We have a simple web application at http://www.techpanda.org/ that is
vulnerable to SQL Injection attacks for demonstration purposes only. The
HTML form code above is taken from the login page. The application provides
basic security such as sanitizing the email field. This means our above code
cannot be used to bypass the login.

To get round that, we can instead exploit the password field. The diagram below
shows the steps that you must follow

Let’s suppose an attacker provides the following input

 Step 1: Enter [email protected] as the email address

 Step 2: Enter xxx') OR 1 = 1 -- ]
  Click on Submit button
 You will be directed to the dashboard

The generated SQL statement will be as follows

SELECT * FROM users WHERE email = '[email protected]' AND password =

md5('xxx') OR 1 = 1 -- ]');

The diagram below illustrates the statement has been generated.

HERE,

 The statement intelligently assumes md5 encryption is used

 Completes the single quote and closing bracket
 Appends a condition to the statement that will always be true

In general, a successful SQL Injection attack attempts a number of different

techniques such as the ones demonstrated above to carry out a successful
attack.

Prepared Statements and Bound Parameters

A prepared statement is a feature used to execute the same (or similar) SQL
statements repeatedly with high efficiency.

Prepared statements basically work like this:

1. Prepare: An SQL statement template is created and sent to the

database. Certain values are left unspecified, called parameters
(labeled "?"). Example: INSERT INTO MyGuests VALUES(?, ?, ?)
2. The database parses, compiles, and performs query optimization on
the SQL statement template, and stores the result without executing it
 3. Execute: At a later time, the application binds the values to the
parameters, and the database executes the statement. The application
may execute the statement as many times as it wants with different
values

Compared to executing SQL statements directly, prepared statements have

three main advantages:

 Prepared statements reduces parsing time as the preparation on the

query is done only once (although the statement is executed multiple
times)
 Bound parameters minimize bandwidth to the server as you need send
only the parameters each time, and not the whole query
 Prepared statements are very useful against SQL injections, because
parameter values, which are transmitted later using a different
protocol, need not be correctly escaped. If the original statement
template is not derived from external input, SQL injection cannot
occur.

Prepared Statements in MySQLi

The following example uses prepared statements and bound parameters in
MySQLi:

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

// prepare and bind

$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email)
VALUES (?, ?, ?)");
$stmt->bind_param("sss", $firstname, $lastname, $email);

// set parameters and execute

$firstname = "John";
$lastname = "Doe";
$email = "[email protected]";
$stmt->execute();

$firstname = "Mary";
$lastname = "Moe";
$email = "[email protected]";
$stmt->execute();

$firstname = "Julie";
$lastname = "Dooley";
$email = "[email protected]";
$stmt->execute();

echo "New records created successfully";

$stmt->close();
$conn->close();
?>

Code lines to explain from the example above:

"INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)"

In our SQL, we insert a question mark (?) where we want to substitute in an

integer, string, double or blob value.

Then, have a look at the bind_param() function:

$stmt->bind_param("sss", $firstname, $lastname, $email);

This function binds the parameters to the SQL query and tells the database
what the parameters are. The "sss" argument lists the types of data that the
parameters are. The s character tells mysql that the parameter is a string.

The argument may be one of four types:

 i - integer
 d - double
 s - string
 b - BLOB

Prepared Statements in PDO

The following example uses prepared statements and bound parameters in
PDO:

Example (PDO with Prepared Statements)

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username,
$password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// prepare sql and bind parameters

$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname,
email)
VALUES (:firstname, :lastname, :email)");
$stmt->bindParam(':firstname', $firstname);
$stmt->bindParam(':lastname', $lastname);
$stmt->bindParam(':email', $email);

// insert a row
$firstname = "John";
$lastname = "Doe";
$email = "[email protected]";
$stmt->execute();

// insert another row

$firstname = "Mary";
$lastname = "Moe";
$email = "[email protected]";
$stmt->execute();

// insert another row

$firstname = "Julie";
$lastname = "Dooley";
$email = "[email protected]";
$stmt->execute();

echo "New records created successfully";

}
catch(PDOException $e)
{
echo "Error: " . $e->getMessage();
}
$conn = null;
?>