What if we want to insert data with our database class? Well, we can't until we add some more functionality.

Let's look at our query method and add a parameter:

public function query($query)

Right now we have one parameter in our method. Let's add a second.

public function query($query, $data = [])

We added $data = []. Why did we add the = []? This is the default for the parameter, meaning if we don't pass anything for that parameter, it will use an empty array as the parameter. [] is an empty array and you can create a variable with an empty array by specifying $emptyArray = [];

What's nice about doing it like this, is we don't have to pass a parameter and it makes our existing database call in index.php still work. But, if we need to use it, we just add the parameter and everything works fine.

Obviously because we are defining the default for the parameter as an empty array, that's what we would also want to pass in. In out case, we're going to specifically pass in an associative array.

An associative array is an array that has values associated with keys. For example, consider this associative array:

<?php

// $data is an associative array
$data = [
	":state" => "CA"
];

In this case, we have an array that has a key of ":state" with a value that is "CA". You access a value by using a key.

We can access the value in :state by doing something like:

<?php

// $data is an associative array
$data = [
	":state" => "CA"
];

// echo the value of state
echo $data[":state"];

You can loop through associative arrays like this:

<?php

// $data is an associative array
$data = [
	":state" => "CA",
	":phone" => "555-555-5555",
];

// loop through every member of the associative array
foreach($data as $key => $value) {
	echo $key . " = " . $value . "<br>";
}

Your browser should look like this:

:state = CA
:phone = 555-555-5555

We're going to use this exact system for our query placeholders. In fact, that's how we're going to bind the parameters of our query to the query itself.

Let's try!

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

// do a query
public function query($query, $data = []) {
	
	try {
		
		// prepare a query
		$prep = $this->db->prepare($query);
		
		// bind values to the query before executing
		foreach ($data as $k => $v) {
			
			// if the value is an integer, we have to specify that to PDO via PDO::PARAM_INT
			if (is_int($v)==true) {
				$prep->bindValue($k, $v, PDO::PARAM_INT);
			} else {
				// $filter = $this->xssFilter($v);
				$prep->bindValue($k, $v);
			}
			
		}
		
		// execute the query
		$prep->execute();
		
		return $prep;
		
	} catch(\PDOException $e) {

...

Between our prepare() and execute() methods, we've added a foreach loop that loops through the keys and values and binds the values to the query.

You'll notice a commented out line, // $filter = $this->xssFilter($v); Within the loop, we'll also take care of some security while we're in there. We want to make this database class really easy for someone to get security correct. We want to make it so they don't have to think about it.

It's commented out right now because we haven't implemented it yet. We'll get there soon.

We've also added some nice to have parts in the code, such as determining if we have an int value and whether or not we should try to return some results. Please read the comments of the code to learn more!

Okay, let's try to insert some data into the database with our shiny new method.

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

<?php

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

// do query
$db = new db;

// insert into names database
$db->query(
	"insert into names set lastName=:lastName, firstName=:firstName, salary=:salary", // query
	[ ":lastName"=>"Gob", ":firstName"=>"Mogger", ":salary"=>50000.01, ] // data
);

// get all the names back
$results = $db->query("select lastName, firstName from names")->fetchAll();

// echo the results to the browser
foreach ($results as $res) {
	echo "<div>{$res["lastName"]}, {$res["firstName"]}</div>";
}

That's all there is to it. With a system like this, code starts to look a little cleaner and more compact. It helps you focus on the real process at hand, instead of having all of the boilerplate text in every database query.

---

Adding XSS Security Into Our Database Class

XSS is a big problem with a really simple solution. XSS stands for cross site scripting. Basically, someone attacking your application would try to save straight javascript into the database, for example:

<script>alert("I'm executable javascript lurking in your database")</script>

The reason this is bad is because when you retrieve that javascript from the database, it will add it to the page. Any time a browser sees a <script></script>, it automatically executes what's inbetween the script tags. The browser doesn't understand that this came from a user and not from a developer and it runs it as if it came from a developer.

So, how do we solve this problem? We basically change every < to &lt; in all data that is passed through to the query method.

Our first step we will update our bindValue code to include $this->xssFilter().

We want to change from

// $filter = $this->xssFilter($v);
$prep->bindValue($k, $v);

to

$prep->bindValue($k, $this->xssFilter($v) );

Within our db class we then add a method that helps us filter things named xssFilter().

Add this code between class db {...} in db.php, preferably at the bottom of the file.

private function xssFilter($value) {
	return str_replace("<", "&lt;", $value);
}

What happens in the database when we filter less than signs?

This is before we started filtering:

+----+---------------------+------------------------------------+-----------+-----------+
| id | started             | lastName                           | firstName | salary    |
+----+---------------------+------------------------------------+-----------+-----------+
| 27 | 2020-02-26 16:18:05 | <script>alert('hi')</script>       | Mogger    |  50000.01 |
+----+---------------------+------------------------------------+-----------+-----------+

And this is after:

+----+---------------------+------------------------------------+-----------+-----------+
| id | started             | lastName                           | firstName | salary    |
+----+---------------------+------------------------------------+-----------+-----------+
| 28 | 2020-02-26 16:54:15 | <script>alert('hi')</script> | Mogger    |  50000.01 |
+----+---------------------+------------------------------------+-----------+-----------+

You should notice the &lt; html entity representation of the less than sign < which has replaced the original.

Basically, instead of getting this at the top of your page each time you reload the page:

You'll get this in your browser: