Accessing Results And Outputting HTML

Instead of just dumping out everything we get from MySQL, let's access just the fields we need and output those to HTML.

First, let's clean up our SQL call. WARNING: It's bad practice and can be insecure to use 'select *' queries. Instead, we'll access just the fields we need. This also helps save memory and speeds up our queries. All around, a big win!

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

<?php
// make a connection to the MySQL server
$db = new PDO("mysql:host=localhost;dbname=employees;", "[username]", "[password]");

// prepare a query
$prep = $db->prepare("select lastName, firstName from names");

// execute the query
$prep->execute();

// fetch the results and put them into a variable
$results = $prep->fetchAll();

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

And the output should look like:

Smith, Jim
Rocker, Bob
Kella, Shella
Toga, Sara
Flauntana, Hannah
Found, Lost
Donkey, Man

We only changed a few lines in this example. Let's take a look. In our SQL, we changed from

select * from names

to

select lastName, firstName from names

For our example, we only want the lastName and firstName fields. So, we replace * with lastName, firstName

The other change we made is to loop through the results, access the field names, and create HTML output that is sent back to the browser.

Let's talk about why we don't use * in our SQL query in the context of security. If we only select the fields that we actually use, we limit possible data leaks. If we use *, we open our code up to possible data leaks.

Data leaks are bad, because they expose more information than we'd like to expose. Data leaks are also bad, because they might expose personally identifying information, or PII.

In our example above, let's pretend we're storing social security numbers along with our employee names. In our internal company application that we are developing, we want to provide a list of names, but we DO NOT want to list our social security data. In this case, we would only select the specific fields that we want to expose. If we used select *, we would open up the possibility that someone not authorized to access our social security number, could potentially harvest that data via our application.

In the security world, part of securing an application is limiting what can be done with the application and limiting data that we expose. Security is just as much about limitations as it is closing down insecure holes in our code.

Also, something to consider when storing extremely sensitive information in a database, such as social security numbers, you should HIGHLY CONSIDER encrypting data before inserting into the database. We'll cover strategies for that in later sections. For now, we just won't store social security numbers.