9 Inserting Data

Building the Insert Form

Create a new PHP file ac_add_one.php. Initially the file looks like:

<?php
 
/**
 * ac_add_one.php: Add one piece of equipment to an employee
 * @package Application
 */
session_start();
require('ac_db.inc.php');
require('ac_equip.inc.php');
 
$sess = new \Equipment\Session;
$sess->getSession();
if (!isset($sess->username) || empty($sess->username)
        || !$sess->isPrivilegedUser()
        || (!isset($_GET['empid']) && !isset($_POST['empid']))) {
    header('Location: index.php');
    exit;
}
$empid = (int) (isset($_GET['empid']) ? $_GET['empid'] : $_POST['empid']);
 
$page = new \Equipment\Page;
$page->printHeader("AnyCo Corp. Add Equipment");
$page->printMenu($sess->username, $sess->isPrivilegedUser());
printcontent($sess, $empid);
$page->printFooter();
 
// Functions
 
?>

The process flow of operation will be similar to index.php. The first time ac_add_one.php is run an HTML input form will be displayed. When the user submits the form, ac_add_one.php is invoked again, which will insert the data into the database.

The privileges required by this function include checks that an employee id is set in either the $_GET['empid'] or $_POST['empid'] superglobals. When ac_add_one.php is first called (see printrecords() in ac_emp_list.php), the employee id is passed as a URL parameter and will be in the $_GET superglobal. When the form (that will shortly be shown) in ac_add_one.php is submitted, the employee identifier will be in $_POST.

Add the printcontent() function to ac_add_one.php:

/**
 * Print the main body of the page
 *
 * @param Session $sess
 * @param integer $empid Employee identifier
 */
function printcontent($sess, $empid) {
    echo "<div id='content'>\n";
    $db = new \Oracle\Db("Equipment", $sess->username);
    if (!isset($_POST['equip']) || empty($_POST['equip'])) {
        printform($sess, $db, $empid);
    } else {
        /* if (!isset($_POST['csrftoken'])
                || $_POST['csrftoken'] != $sess->csrftoken) {
            // the CSRF token they submitted does not match the one sent
            header('Location: index.php');
            exit;
        } */
        $equip = getcleanequip();
        if (empty($equip)) {
            printform($sess, $db, $empid);
        } else {
            doinsert($db, $equip, $empid);
            echo "<p>Added new equipment</p>";
            echo '<a href="ac_show_equip.php?empid='
                 . $empid . '">Show Equipment</a>' . "\n";
        }
    }
    echo "</div>";  // content
}

The printcontent() function contains the logic to decide if the HTML form should be printed or the user-entered data should be inserted. The commented-out CSRF token code will be discussed below.

Also in ac_add_one.php add the printform() function:

/**
 * Print the HTML form for entering new equipment
 *
 * @param Session $sess
 * @param Db $db
 * @param integer $empid Employee identifier
 */
function printform($sess, $db, $empid) {
    $empname = htmlspecialchars(getempname($db, $empid), ENT_NOQUOTES, 'UTF-8');
    $empid = (int) $empid;
    $sess->setCsrfToken();
    echo <<<EOF
Add equipment for $empname
<form method='post' action='${_SERVER["PHP_SELF"]}'>
<div>
    Equipment name <input type="text" name="equip"><br>
    <input type="hidden" name="empid" value="$empid">
    <input type="hidden" name="csrftoken" value="$sess->csrftoken">
    <input type="submit" value="Submit">
</div>
</form>
EOF;
}

This simple form prompts the user for a value. The CSRF token will be described later.

Add the getcleanequip() function to ac_add_one.php:

/**
 * Perform validation and data cleaning so empty strings are not inserted
 *
 * @return string The new data to enter
 */
function getcleanequip() {
    if (!isset($_POST['equip'])) {
        return null;
    } else {
        $equip = $_POST['equip'];
        return(trim($equip));
    }
}

This implementation strips any leading or trailing white space from the entered data.

The general mantra for basic web application security is to filter input and escape output. The getcleanequip() function filters input. The data could be sanitized in other ways here. You may decide that you do not want HTML tags to be accepted. You can strip such tags by using one of PHP's input filters. For example, if you wanted, you could change:

        $equip = $_POST['equip'];

to

        $equip = filter_input(INPUT_POST, 'equip', FILTER_SANITIZE_STRING);

This would remove HTML tags, leaving other text in place.

In ac_add_one.php, valid data is inserted by doinsert(). Add the code for this function to the file:

/**
 * Insert a piece of equipment for an employee
 *
 * @param Db $db
 * @param string $equip Name of equipment to insert
 * @param string $empid Employee identifier
 */
function doinsert($db, $equip, $empid) {
    $sql = "INSERT INTO equipment (employee_id, equip_name) VALUES (:ei, :nm)";
    $db->execute($sql, "Insert Equipment", array(array("ei", $empid, -1),
                                                 array("nm", $equip, -1)));
}

This uses the existing Db::execute() method in ac_db.inc.php with familiar bind variable syntax. The Db class automatically commits each time oci_execute() is called as discussed earlier in the section Running SQL with the Db Class.

Finally, to complete ac_add_one.php, add the helper function getempname():

/**
 * Get an Employee Name
 *
 * @param Db $db
 * @param integer $empid
 * @return string An employee name
 */
function getempname($db, $empid) {
    $sql = "SELECT first_name || ' ' || last_name AS emp_name
        FROM employees
        WHERE employee_id = :id";
    $res = $db->execFetchAll($sql, "Get EName", array(array("id", $empid, -1)));
    $empname = $res[0]['EMP_NAME'];
    return($empname);
}

This is identical to the function of the same name in ac_show_equip.php.

Similar functionality to the ac_show_equip.php form could be used to delete or update records, remembering the limitations of a stateless web architecture means that rows cannot be locked in one HTML page and changed in another.

Running the Single Insert Form

Run the AnyCo application and log in as Administrator. Click the Add One link next to Steven King. The equipment input form is displayed:

Enter a new piece of equipment, paper, and click Submit. The new data is inserted. The updated list can be seen by clicking Show link next to Steven King.

CSRF example with ac_add_one.php

The form is currently prone to CSRF attacks, where another site can take advantage of you being logged in and cause you to submit data or do some other privileged operation.

To show this, create a new HTML page called hack.html:

<html>
<!-- hack.html: Show issues with CSRF -->
<body>
<h1>Make Millions!</h1>
<form method='post' action='http://localhost/ac_add_one.php'>
<div>
    Do you dream of being rich?<br>
    <input type="hidden" name="equip" value="fish">
    <input type="hidden" name="empid" value="100">
    <input type="submit" value="Win">
</div>
</form>
 
</body>
</html>

Change the HTML form action URL to match your system.

Run the AnyCo application in a browser and login as Administrator. In a new browser tab or window, open the following file:

http://localhost/hack.html

Ostensibly to the person looking at the page it has nothing to do with the AnyCo application.

Click the Win button. This calls the AnyCo application and causes the bogus equipment name fish to be inserted into the equipment list of employee 100 (which is Steven King). The inserted value can be seen on the subsequent Show Equipment page:

Now edit ac_add_one.php and enable CSRF protection by removing the comments for the check in printcontent():

    ...
    } else {
        if (!isset($_POST['csrftoken'])
                || $_POST['csrftoken'] != $sess->csrftoken) {
            // the CSRF token they submitted does not match the one sent
            header('Location: index.php');
            exit;
        }
        $equip = getcleanequip();
     ...

The form in ac_add_one.php includes a generated Cross-Site Request Forgery token as a hidden field. The value is also stored in the user session. The CSRF check in printcontent() will verify that the token in the submitted form matches PHP's stored session value.

Save the file and run the AnyCo application again, logging in as Administrator. In a new browser tab or window, open the following file:

http://localhost/hack.html

Now click Win.

This time the CSRF protection in printcontent() does not find a CSRF token in the submitted form and redirects to the login page, index.php, which logs out. Log back in again to the AnyCo application and check that Steven King's equipment list is unchanged, with no second entry for fish. For hack.html to be successful it would have to know the value of the csrftoken field that gets stored in the PHP session when the ac_add_one.php generates the real entry form.

CSRF protection is just one of many kinds of security restrictions that web applications should enforce. You should do a thorough security evaluation of any code you deploy on the web.

Many of the popular PHP frameworks provide assistance to reduce the amount of effort required in producing a secure application. For example they may provide a more secure implementation of CSRF token generation than the one in the AnyCo Session class.