All the previous examples are fine from the standpoint of security. But, what if you were making your application open to the internet? Another defensive technique is to obfuscate your table ids. For example, instead of allowing an application to update nameId=1, you could have it update nameId=GtaDtHak.

The regular nameId would still exist and would still be used internally, but anything sent to the browser will be obfuscated and makes it significantly harder to guess record ids. Keep in mind that this adds some overhead to your application.

Why would we want to do this? There are ways for people to gather information and do nefarious things if they have sequential access to your database records. This becomes especially important when you have a system that allows untrusted users to sign up and make use of the system. If they could guess your database id numbers, they could start doing things to your records that you might not want. This system makes it much much more difficult to do. In our example here, we use obfuscatedIds that are 16 characters long, but you can go higher for even more security.

Let's go through an example. First, we need to add a field to our names table. We'll add a char field and make it hold 16 characters.

NOTE: After adding the obfuscatedId field, it will be empty. You'll need to either delete all current data using the MySQL truncate command or create a strategy to fill those blank obfuscatedId fields. The code added below will not work correctly if these conditions aren't met.

alter table names add obfuscatedId char(16) not null after id;

Next up, we need to add some code to our IndexModel methods. I'll include the entire file here, with annotated changes.

This code should be put into indexmodels.php in your webroot directory.

<?php

namespace IndexModels;

require_once "db.php";
use \database\db;

class IndexModels {
	static public function index() {
		$db = new db;
		return $db->query("select obfuscatedId, lastName, firstName, number, phoneType from names n, phones p where n.id=p.nameId and isDeleted = false")->fetchAll();
	}
	
	static public function saveNewEmployee($lastName, $firstName, $salary, $street, $city, $state, $zip, $number, $type) {
		
		// create new random obfuscatedId using PHP cryptographically secure random_bytes function
		// will generate a 16 byte string
		$obfuscatedId = bin2hex( random_bytes(8) );
		
		$db = new db;
		$query = $db->query(
			"insert into names set obfuscatedId=:obfuscatedId, lastName=:lastName, firstName=:firstName, salary=:salary",
			[ ":obfuscatedId"=>$obfuscatedId, ":lastName"=>$lastName, ":firstName"=>$firstName, ":salary"=>$salary, ]
		);
		
		// get the nameId that was created in the previous insert query
		$nameId = $db->lastInsertId();
		
		$db->query(
			"insert into addresses set nameId=:nameId, street=:street, city=:city, state=:state, zip=:zip",
			[ ":nameId"=>$nameId, ":street"=>$street, ":city"=>$city, ":state"=>$state, ":zip"=>$zip, ]
		);
		
		$db->query(
			"insert into phones set nameId=:nameId, number=:number, phoneType=:phoneType",
			[ ":nameId"=>$nameId, ":number"=>$number, ":phoneType"=>$type, ]
		);
		
	}
	
	static public function getEmployee($obfuscatedId) {
		$db = new db;
		$results = $db->query(
			"select obfuscatedId, lastName, firstName, salary, street, city, state, zip, number, phoneType from names n left join addresses a on n.id=a.nameId left join phones p on n.id=p.nameId where obfuscatedId=:obfuscatedId",
			[ ":obfuscatedId"=>$obfuscatedId, ]
		)->fetchAll();
		
		// if a result was found, return it.  if not, return an empty array
		if (count($results) == 1) return $results[0];
		return [];
	}
	
	static public function saveEmployeeUpdate($obfuscatedId, $lastName, $firstName, $salary, $street, $city, $state, $zip, $number, $type) {
		$db = new db;
		
		// get the internal names.id, which we'll then use to update all the tables
		$nameId = $db->query(
			"select id as nameId from names where obfuscatedId=:obfuscatedId",
			[ ":obfuscatedId"=>$obfuscatedId, ]
		)->fetchAll();
		
		if (count($nameId) > 0) {
			
			// pull out the nameId from the returned record
			// this will put the id from the names table into $nameId
			$nameId = $nameId[0]['nameId'];
			$db->query(
				"update names set lastName=:lastName, firstName=:firstName, salary=:salary where id=:nameId",
				[ ":lastName"=>$lastName, ":firstName"=>$firstName, ":salary"=>$salary, ":nameId"=>$nameId, ]
			);
			
			$db->query(
				"update addresses set street=:street, city=:city, state=:state, zip=:zip where nameId=:nameId",
				[ ":street"=>$street, ":city"=>$city, ":state"=>$state, ":zip"=>$zip, ":nameId"=>$nameId, ]
			);
			
			$db->query(
				"update phones set number=:number, phoneType=:phoneType where nameId=:nameId",
				[ ":number"=>$number, ":phoneType"=>$type, ":nameId"=>$nameId, ]
			);
			
		}
	}
	
	// obfuscatedIds is an array
	static public function deleteEmployees($obfuscatedIds) {
		$db = new db;
		
		// loop through each nameId in the nameIds and delete the records
		foreach ($obfuscatedIds as $obfuscatedId) {
			$db->query(
				"update names set isDeleted=true where obfuscatedId=:obfuscatedId",
				[ ":obfuscatedId"=>$obfuscatedId, ]
			);
		}
	}
}

There are three things we've changed in this file. The first one that we changed is that we changed all mentions of nameId to obfuscatedId. In the case of our deleteEmployees, we changed from nameIds to obfuscatedIds.

The second thing we changed is in our saveEmployeeUpdate, we have an extra select query that we need to execute to get the internal nameId. Once we have that, we can execute our updates normally.

The third we changed was the added obfuscatedId generator, which looks like:

$obfuscatedId = bin2hex( random_bytes(8) )

random_bytes generates 8 random characters. bin2hex takes those 8 characters and turns them into a string of 16 characters that can be easily saved into the database.

Now let's take a look at what we changed in our controller.

This code should be put into index.php in your webroot directory.

<?php

require_once "filter.php";
use \filter\filter;

require_once "indexmodels.php";
use \IndexModels\IndexModels;

require_once "indexviews.php";
use \IndexViews\IndexViews;

// handle GETS
if ( isset($_GET["do"]) == true ) {
	// use $_GET here, because clicking a link and sending HTTP vars is a GET request
	if ($_GET["do"] == "update") {
		$obfuscatedId = "";
		if (isset($_GET["obfuscatedId"]) == true && $_GET["obfuscatedId"] != "") {
			$obfuscatedId = $_GET["obfuscatedId"];
		}
		
		if ($obfuscatedId != "") {
			IndexViews::updateEmployee( IndexModels::getEmployee($obfuscatedId) );
		}
		exit;
	}
}

// handle POSTS
if ( isset($_POST["do"]) == true ) {
	// utilize this validation with saveNewEmployee or saveEmployeeUpdate
	if ($_POST["do"] == "saveNewEmployee" || $_POST["do"] == "saveEmployeeUpdate") {
		
		$lastName = "";
		if (isset($_POST["lastName"]) == true && $_POST["lastName"] != "") {
			$lastName = filter::names($_POST["lastName"]);
		}
		
		$firstName = "";
		if (isset($_POST["firstName"]) == true && $_POST["firstName"] != "") {
			$firstName = filter::names($_POST["firstName"]);
		}
		
		$salary = "";
		if (isset($_POST["salary"]) == true && $_POST["salary"] != "") {
			$salary = filter::salary($_POST["salary"]);
		}
		
		$street = "";
		if (isset($_POST["street"]) == true && $_POST["street"] != "") {
			$street = filter::street($_POST["street"]);
		}
		
		$city = "";
		if (isset($_POST["city"]) == true && $_POST["city"] != "") {
			$city = filter::city($_POST["city"]);
		}
		
		$state = "";
		if (isset($_POST["state"]) == true && $_POST["state"] != "") {
			$state = filter::state($_POST["state"]);
		}
		
		$zip = "";
		if (isset($_POST["zip"]) == true && $_POST["zip"] != "") {
			$zip = filter::zip($_POST["zip"]);
		}
		
		$number = "";
		if (isset($_POST["number"]) == true && $_POST["number"] != "") {
			$number = filter::number($_POST["number"]);
		}
		
		$type = "";
		if (isset($_POST["type"]) == true && $_POST["type"] != "") {
			$type = filter::type($_POST["type"]);
		}
		
		if ($lastName != "" && $firstName != "" && $salary != "" && $street != "" && $city != "" && $state != "" && $zip != "" && $number != "" && $type != "") {
			
			// decide what IndexModels method we should use, depending on the request
			if ($_POST["do"] == "saveNewEmployee") {
				IndexModels::saveNewEmployee($lastName, $firstName, $salary, $street, $city, $state, $zip, $number, $type);
			} else if ($_POST["do"] == "saveEmployeeUpdate") {
				
				// grab the nameId
				$obfuscatedId = "";
				if (isset($_POST["obfuscatedId"]) == true && $_POST["obfuscatedId"] != "") {
					$obfuscatedId = $_POST["obfuscatedId"];
				}
				
				if ($obfuscatedId != "") {
					IndexModels::saveEmployeeUpdate($obfuscatedId, $lastName, $firstName, $salary, $street, $city, $state, $zip, $number, $type);
				}
				
			}
			
		}
		
		// after we've saved, forward the user back to our default screen
		header("Location: /");
		exit;
	}
	
	
	if ($_POST["do"] == "deleteEmployees") {
		
		// all your nameIds will come through PHP via an array
		$obfuscatedIds = [];
		if (isset($_POST["obfuscatedIds"]) == true) {
			$obfuscatedIds = $_POST["obfuscatedIds"];
		}
		
		if (count($obfuscatedIds) > 0) {
			IndexModels::deleteEmployees($obfuscatedIds);
		}
		
		// after we've deleted, forward the user back to our default screen
		header("Location: /");
		exit;
	}
	
} else {
	// show the default screen, which is a list of employees
	$results = IndexModels::index();
	IndexViews::index($results);
}

Again, we've updated all references from nameId to obfuscatedId. And again, in our deleteEmployees $_POST action, we've updated from nameIds to obfuscatedIds.

And finally, let's look at our view code.

This code should be put into indexviews.php in your webroot directory.

<?php

namespace IndexViews;

class IndexViews {
	// $employees is a list of firstName and lastName
	static public function index($employees) {
		IndexViews::header();

		// create a form so that we can send checked employees to the server
		?> 
		<form action="index.php" method="post">
		<input type="hidden" name="do" value="deleteEmployees">
		<input type="submit" value="Delete Selected Employees">
		
		<?php
		
		// echo the results to the browser
		foreach ($employees as $res) {
		?>
		<div>
			<input type="checkbox" name="obfuscatedIds[]" value="<?= $res["obfuscatedId"] ?>"> 
			<a href="index.php?do=update&obfuscatedId=<?= $res["obfuscatedId"] ?>">
				<?= $res["lastName"] ?>, <?= $res["firstName"] ?>, <?= $res["number"] ?>, <?= $res["phoneType"] ?>
			</a>
		</div>
		<?php
		}
		
		// end the form
		?>
		</form> 
		<?php

		IndexViews::employeeForm();
		IndexViews::footer();
	}
	
	
	static public function updateEmployee($employeeRecord) {
		IndexViews::header();
		?><h3>Update Employee</h3><?php
		IndexViews::employeeForm(true, $employeeRecord);
		IndexViews::footer();
	}
	
	
	static public function header() {
		
?>
<!DOCTYPE html>
<html lang="en" dir="ltr">
<head>
	<meta charset="utf-8">
	<title>Employees</title>
	<style>
		body{ background-color: #333; color: #ddd; }
		a{ color: #ddd; }
	</style>
</head>
<body>
<?php
	}
	
	static public function footer() {
?>
</body>
</html>
<?php
	}
	
	// flip $isUpdate to true if we are using this form to update a record
	// we set $employeeRecord defaults so we don't get a bunch of notices in our logs when doing saveNewEmployee
	static public function employeeForm($isUpdate = false, 
		$employeeRecord = ["obfuscatedId"=>"", "firstName"=>"", "lastName"=>"", "salary"=>"", "street"=>"", "city"=>"", "state"=>"", "zip"=>"", "number"=>"", "type"=>"", ]) {

		?>
		<form action="index.php" method="post">
			
			<?php 
			// use ternary operators to set up do and nameId to be passed back to the server
			echo $isUpdate == true ? '<input type="hidden" name="do" value="saveEmployeeUpdate">' : '<input type="hidden" name="do" value="saveNewEmployee">';
			echo $isUpdate == true ? '<input type="hidden" name="obfuscatedId" value="'.$employeeRecord["obfuscatedId"].'">' : '';
			?>
			
			<div class="">
				Name
			</div>
			<div class="">
				<input type="text" name="firstName" placeholder="first name" value="<?= $employeeRecord["firstName"] ?>">
				<input type="text" name="lastName" placeholder="last name" value="<?= $employeeRecord["lastName"] ?>">
				<input type="text" name="salary" placeholder="salary $" value="<?= $employeeRecord["salary"] ?>">
			</div>
			<div class="">
				Address
			</div>
			<div class="">
				<input type="text" name="street" placeholder="street" value="<?= $employeeRecord["street"] ?>">
				<input type="text" name="city" placeholder="city" value="<?= $employeeRecord["city"] ?>">
				<input type="text" name="state" placeholder="state" value="<?= $employeeRecord["state"] ?>">
				<input type="text" name="zip" placeholder="zip" value="<?= $employeeRecord["zip"] ?>">
			</div>
			<div class="">
				Phone
			</div>
			<div class="">
				<input type="text" name="number" placeholder="number" value="<?= $employeeRecord["number"] ?>">
				<select name="type" value="<?= $employeeRecord["phoneType"] ?>">
					<option>Cell</option>
					<option>Home</option>
					<option>Work</option>
				</select>
			</div>
			<input type="submit" value="Save">
		</form>
		
		<?php
	}
}

You've guessed it... We updated all references of nameId and made them obfuscatedId.