John's Bread Crumbs: June 2018

Thursday, June 21, 2018

LAMP - Part 6 - Embed the form in a web page

My notes on how to create a LAMP form using PDO and MySQL:

Part 6: Embed the form

This is part 5 of a series:

Part 1 - Prepare mysql
Part 2 - Create the mysql login files
Part 3 - Retrieve all records
Part 4 - Insert a new record
Part 5 - Search for a record
Part 6 - Embed the form

Embed the form in your web site:

Insert this HTML into your web page:

<iframe src="http://www.whatever.com/form1.php" height="400" width="600">

LAMP - Part 5 - Search for a record

My notes on how to create a LAMP form using PDO and MySQL:

Part 5: Search for a record

This is part 5 of a series:

Part 1 - Prepare mysql
Part 2 - Create the mysql login files
Part 3 - Retrieve all records
Part 4 - Insert a new record
Part 5 - Search for a record
Part 6 - Embed the form

Create a PHP form to search for a set of records:

Contents of lookup.php:

<?php // lookup.php

echo <<<_END

<html>
<head>
<title>Lookup Test</title>
</head>
<body>
<form method="post" action="lookup.php">
Last name: <input type="text" name="lastName"> <br>
<br>
<input type="submit" value="submit">
</form>
<hr>
<br>

_END;

// Set variable $lastName if it was provided via a POST method
if (isset($_POST['lastName']) && (!empty($_POST['lastName'])))
{

// This is the code that sanitizes the user's input string
//$lastName = filter_var($_POST['lastName'], FILTER_SANITIZE_STRING);

$lastName = filter_var($_POST['lastName'], FILTER_SANITIZE_STRING, FILTER_FLAG_STR
IP_HIGH | FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_BACKTICK | FILTER_FLAG_ENCODE_AMP );

}
if (!empty($lastName))
{
echo "Entered Name: $lastName<br>";
echo '<br>';

// Retrieve records from mysql database

// Retrieve database connection info
require_once '/var/forms/login_reader.php';

// Build data source name and options
$dsn = "mysql:dbname=$db;host=$host;charset=$charset";
$opt = [
PDO::ATTR_ERRMODE=> PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE    => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES      => false,
];

// Set $targetLastName to value provided

// This appends a wildcard to whatever the user provided so it brings back a set of results

$targetLastName = $lastName . "%";

// Now try to connect to the database and retrieve records
try {

// We use PDO instead of mysqli so we can switch from mysql to MS SQL if needed

$pdo = new PDO($dsn, $user, $pass, $opt);

// Prepare the SQL statement to protect against SQL injection attacks.
// Notice the use of the question mark (?) placeholder.

$statement = $pdo-> prepare( "

                        select Firstname, Lastname
                        from Form1
                        where Lastname LIKE ?
                        order by Firstname asc
                        LIMIT 20

                " );

// Now that the SQL statement is prepared, execute it
// Provide the $targetLastName parameter

$statement->execute([$targetLastName]);

// If we found matching records, prepare to display them

if ($statement->rowCount() > 0)
{
echo "Matching records found:<br><br>";
// Display each matching record
foreach ($statement as $row)
{
echo $row["Firstname"] . " " . $row["Lastname"];
echo "<br>";
}
}

// What do we do if no matching records are found?
else

{
echo 'No matching records found.<br>';
}

}

// Deal with problems with the database
catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}

}

echo '<br>';
echo '<br>';
echo 'Click <a href="index.html">here</a> to return to Main Menu';
echo '<br>';

echo '</html>';
?>

LAMP - Part 4 - Insert a new record

My notes on how to create a LAMP form using PDO and MySQL:

Part 4: Retrieve all records

This is part 4 of a series:

Part 1 - Prepare mysql
Part 2 - Create the mysql login files
Part 3 - Retrieve all records
Part 4 - Insert a new record
Part 5 - Search for a record
Part 6 - Embed the form

Create a PHP form to insert a new record:

Contents of form1.php:

<?php // form1.php

echo <<<_END

<html>
<head>
<title>Form1 Test</title>
</head>
<body>
<form method="post" action="form1.php">
Values must be entered for BOTH fields.<br><br>
First name: <input type="text" name="firstName"> <br><br>
Last name: <input type="text" name="lastName"> <br>
<br>
<input type="submit" value="submit">
</form>
<hr>
<br>

_END;

// Set variable $lastName if it was provided via a POST method
if (isset($_POST['lastName']) && (!empty($_POST['lastName'])))
{

// This is the code that sanitizes the user's input string
//$lastName = filter_var($_POST['lastName'], FILTER_SANITIZE_STRING);

$lastName = filter_var($_POST['lastName'], FILTER_SANITIZE_STRING, FILTER_FLAG_STR
IP_HIGH | FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_BACKTICK | FILTER_FLAG_ENCODE_AMP );

}

// Set variable $firstName if it was provided via a POST method
if (isset($_POST['firstName']) && (!empty($_POST['firstName'])))
{

// Sanitize the user's input string

$firstName = filter_var($_POST['firstName'], FILTER_SANITIZE_STRING, FILTER_FLAG_S
TRIP_HIGH | FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_BACKTICK | FILTER_FLAG_ENCODE_AMP );

}

if (!empty($lastName) && !empty($firstName) )
{
echo "Entered First Name: $firstName<br>";
echo "Entered Last Name: $lastName<br>";
echo '<br>';

// Insert the values into the mysql database
// To Do:
// - Check for existing entry. Reject attempt if match exists.

// Retrieve database connection info
require_once '/var/forms/login_writer.php';

// Build data source name and options
$dsn = "mysql:dbname=$db;host=$host;charset=$charset";
$opt = [
PDO::ATTR_ERRMODE=> PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE    => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES      => false,
];

try {
// Build a PDO object

$pdo = new PDO($dsn, $user, $pass, $opt);

// Prepare the SQL statement to protect against SQL injection attacks.

$statement = $pdo-> prepare( "

                        INSERT INTO Form1
                        VALUES (DEFAULT, ?, ?)

                " );

// Now that the SQL statement is prepared, execute it
// Provide the parameters

$statement->execute([$lastName, $firstName]);

echo "Added...";

}

// Deal with problems with the database
catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}

}

echo '<br>';
echo '<br>';
echo '<br>';
echo'Click <a href="index.html">here</a> to return to Main Menu';
echo '<br>';
echo '</html>';
?>

LAMP - Part 3 - Retrieve all records

My notes on how to create a LAMP form using PDO and MySQL:

Part 3: Retrieve all records

This is part 3 of a series:

Part 1 - Prepare mysql
Part 2 - Create the mysql login files
Part 3 - Retrieve all records
Part 4 - Insert a new record
Part 5 - Search for a record
Part 6 - Embed the form

Create an index file:

Create an index.html file for convenience. Contents:

<html>
<head>
<title>Main Menu</title>
</head>
<body>
<br><br>
<a href="display_all.php">Display all</a> records</a>
<br><br>
<a href="lookup.php">Look up</a> a record</a>
<br><br>
<a href="form1.php">Insert</a> a record</a>
<br><br>
</body>
</html>

Create a PHP file to display all records:

Contents of display_all.php:

<?php // display_all.php

echo <<<_END

<html>
<head>
<title>Display_All Test</title>
</head>
<body>
<br>

_END;

echo '<br>';

// Retrieve database connection info
require_once '/var/forms/login_reader.php';

// Build data source name and options
$dsn = "mysql:dbname=$db;host=$host;charset=$charset";
$opt = [
PDO::ATTR_ERRMODE=> PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE    => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES      => false,
];

// Now try to connect to the database and retrieve records

try {

// We use PDO instead of mysqli so we can switch from mysql to MS SQL if needed

$pdo = new PDO($dsn, $user, $pass, $opt);

// Prepare the SQL statement to protect against SQL injection attacks.

$statement = $pdo-> prepare( "

                select Lastname, Firstname
                from Form1
                order by Lastname asc, Firstname asc
                LIMIT 20

        " );

// Now that the SQL statement is prepared, execute it

$statement->execute();

// If we found matching records, prepare to display them

if ($statement->rowCount() > 0)
{
// Display each record
foreach ($statement as $row)
{
echo $row["Lastname"];
echo ", ";
echo $row["Firstname"];
echo "<br>";
}
echo "<br>";
echo "<hr>";
}

// What do we do if no matching records are found?
else

{
echo 'No records found.<br>';
}

}

// Deal with problems with the database
catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}

echo '<br>';
echo '<br>';
echo 'Click <a href="index.html">here</a> to return to Main Menu';
echo '<br>';

echo '</html>';
?>

LAMP - Part 2 - Create the mysql login files

My notes on how to create a LAMP form using PDO and MySQL:

Part 2: Create the mysql login files

This is part 2 of a series:

Part 1 - Prepare mysql
Part 2 - Create the mysql login files
Part 3 - Retrieve all records
Part 4 - Insert a new record
Part 5 - Search for a record
Part 6 - Embed the form

Identify a directory outside of web root

You need to store your login credentials outside the web server directory.
This is to prevent the files from being downloaded if you accidentally misconfigure your web server.
In this example, I will store the credentials in
/var/forms

Create the files

cd /var/forms
touch login_reader.php
touch login_writer.php

Determine who apache runs as

ps aux | egrep "apache|httpd"

Since I run Ubuntu, apache runs as www-data

Change ownership of the files

By default the files will be owned by root and only root will be able to read/write the files.
You can see this by running the command:

ls -l

My files show:
owner = root
group = root

We need to change ownership so we can later assign permissions to the owner.
Recall that apache runs as www-data on Ubuntu, so we will change permissions to www-data:

chown www-data login_reader.php
chown www-data login_writer.php

ls -l

Change permissions on the files

My current file permissions:
owner: rw
group: r
world: r

Remove read permissions for world:

chmod o-r login_reader.php
chmod o-r login_writer.php

Grant write permissions for group (root):

chmod g+w login_reader.php
chmod g+w login_writer.php

Remove write permissions for www-data:
chmod u-w login_reader.php
chmod u-w login_writer.php

Finished permissions:
-r--rw---- 1 www-data root 0 Jun 21 18:53 login_reader.php
-r--rw---- 1 www-data root 0 Jun 21 18:53 login_writer.php

Determine character set:

You will need this to create your login files (see below).

Run this command against mysql:

SELECT CCSA.character_set_name FROM information_schema.`TABLES` T,
information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
WHERE CCSA.collation_name = T.table_collation
AND T.table_name = "Form1";

My system comes back with latin1

Populate login_reader.php:

<?php
$host = 'localhost';
$db = 'myforms';
$user = 'testreader';
$pass = 'myStrongPassword';
$charset = 'latin1';
?>

Populate login_writer.php:

<?php
$host = 'localhost';
$db = 'myforms';
$user = 'testwriter';
$pass = 'myOtherStrongPassword';
$charset = 'latin1';
?>

LAMP - Part 1 - Prepare MySQL

My notes on how to create a LAMP form using PDO and MySQL:

Part 1: Prepare mysql

This is part 1 of a series:

Part 1 - Prepare mysql
Part 2 - Create the mysql login files
Part 3 - Retrieve all records
Part 4 - Insert a new record
Part 5 - Search for a record
Part 6 - Embed the form

Create the database:

mysql -u root -p
create database myforms;
show databases;
use myforms;

Create the table:

This will create a table "Form1" with three fields: ID, LastName, and FirstName:

CREATE TABLE Form1 (
ID INT NOT NULL AUTO_INCREMENT,
LastName VARCHAR(255) NOT NULL,
FirstName VARCHAR(255) NOT NULL,
PRIMARY KEY (ID)
);

Populate the table with sample data:

INSERT INTO Form1 VALUES (1, 'Flintstone', 'Fred');
INSERT INTO Form1 VALUES (DEFAULT, 'Flintstone', 'Wilma');
INSERT INTO Form1 VALUES (DEFAULT, 'Rubble', 'Barney');
INSERT INTO Form1 VALUES (DEFAULT, 'Rubble', 'Betty');

SELECT * FROM Form1;

Create users:

We will be creating two users -- one for read operations and one for write operations. (This is for demo purposes. In my production application, I don't have the need for users to read from the database. For tinkering purposes, I will document it here.)

CREATE USER 'testreader'@'%' IDENTIFIED BY 'myStrongPassword';
CREATE USER 'testwriter'@'%' IDENTIFIED BY 'myOtherStrongPassword';

SELECT User FROM mysql.user;

Grant privileges to the users:

GRANT INSERT ON myforms.* TO 'testwriter'@'%';
GRANT SELECT ON myforms.* TO 'testreader'@'%';

Test user access:

mysql -u testwriter -p
use myforms;
show tables;
INSERT INTO Form1 VALUES (DEFAULT, 'Flintstone', 'Pebbles');
SELECT * FROM Form1;

(The SELECT statement will be denied for the testwriter account)

mysql -u testreader -p
use myforms;
show tables;
INSERT INTO Form1 VALUES (DEFAULT, 'Rubble', 'Bambam');
SELECT * FROM Form1;

(The INSERT statement will be denied for the testreader account)