Hide connection data

The problem

Here's what we've been doing.

  • // To connect to a DB, need the DB name, user name, and password.
  • $dbName = 'something';
  • $dbUserName = 'something';
  • $dbPassword = 'something';
  • // Create DSN.
  • $dsn = "mysql:host=localhost;dbname=$dbName";
  • // Connect.
  • global $dbConnection;
  • $dbConnection = new PDO($dsn, $dbUserName, $dbPassword);

We have the DB name, user name, and password on every page that connects to the DB. That could be many pages, in a business web app.

Show all

Moving the DB connection data

We'd like to make it easy to change the data, in case we want to, for example, change the name of the database. Which we do, if we want to move the code from our PC to our Reclaim server, which use different DB and user name conventions.

The DSN could change as well. Maybe on one weird server as have to use SQL Server rather than MySQL. PDO can handle that, but we can't use the default MySQL port of 3306 anymore. We'd have to change the DSN string.

Let's also move the connection data outside the web site entirely, to enhance security. This is something Adela asked about earlier.

Show all

No problem. Let's take the code above, and put it in a separate .php file, called, say, db-connect.php:

  • <?php
  • // To connect to a DB, need DB name, user name, and password.
  • $dbName = 'something';
  • $dbUserName = 'something';
  • $dbPassword = 'something';
  • // Create DSN.
  • $dsn = "mysql:host=localhost;dbname=$dbName";
  • // Connect.
  • global $dbConnection;
  • $dbConnection = new PDO($dsn, $dbUserName, $dbPassword);

(Note the <?php at the top.)

Let's put the file outside the web root, maybe in D:\secret-stuff\ on your local PC. Remember that XAMPP's web root is something like D:\xampp\htdocs\, so only files under that are accessible through localhost. Any file outside that folder won't have a URL, since it can't be accessed through Apache.

On your Reclaim server, your exercise subdomain is in a folder like /home/rosie/exercises.mysite.com/. The files in the folder are accessible at https://exercises.mysite.com. So, let's put the file with the DB connection info in a folder outside that, like /home/rosie/secret-stuff/.

Show all

Then change the programs in our app to do something like this:

  • <?php
  • require_once 'D:\secret-stuff\db-connect.php';
  • // Get the id of the wombat the user wants to see.
  • $id = $_GET['id'];
  • // Prepare SQL.
  • $stmt = $dbConnection->prepare('SELECT * FROM wombats WHERE wombat_id = :id');

W00t!

Show all
Adela
Adela

Does that fix the problem, though? We have D:\secret-stuff\db-connect.php in every page. Move the programs to our Reclaim servers, and we'd have to touch every page.

Rats! You're right. Any ideas, anyone?

Ethan
Ethan

That line:

  • require_once 'D:\secret-stuff\db-connect.php';

We could stick it in a code library, that gets included on every page.

Hmm...

Adela: Aha!
Adela

Oh, my dude, that's brilliant!

OK, that would work.

Earlier, we talked about making a code library, with useful functions we could share across our app. Let's say we have a code library called useful-stuff.php. It's in the library folder. Programs load it like this:

  • // Load useful functions.
  • require_once 'library/useful-stuff.php';

OK, we did that before.

In useful-stuff.php, we add the line...

  • require_once 'D:\secret-stuff\db-connect.php';

That line would just be in that one file, useful-stuff.php, that's included on every page.

When we upload the app to Reclaim, change that one line in useful-stuff.php to the new location.

Show all

Let's go over that again. When we deploy an app from our PC to Reclaim, we need to:

  • Change where the secrets file is kept, from a Windows folder, to a Linux folder.
  • Change the secrets file, to the Linux DB connection info.

So, we edit library/useful-stuff.php, to point to the secrets file on the production server. From:

  • // Load DB secrets from outside the web root.
  • require_once 'D:\secret-stuff\db-connect.php';

To:

  • // Load DB secrets from outside the web root.
  • require_once '/home/rosie/secret-stuff/db-connect.php';

Then change db-connect.php, from the local Windows version:

  • <?php
  • // To connect to a DB, need DB name, user name, and password.
  • $dbName = 'something';
  • $dbUserName = 'something';
  • $dbPassword = 'something';
  • // Create DSN.
  • $dsn = "mysql:host=localhost;dbname=$dbName";
  • // Connect.
  • global $dbConnection;
  • $dbConnection = new PDO($dsn, $dbUserName, $dbPassword);

To the production Linux version:

  • <?php
  • // To connect to a DB, need DB name, user name, and password.
  • $dbName = 'rosie_wombats';
  • $dbUserName = 'rosie_something';
  • $dbPassword = 'something';
  • // Create DSN.
  • $dsn = "mysql:host=localhost;dbname=$dbName";
  • // Connect.
  • global $dbConnection;
  • $dbConnection = new PDO($dsn, $dbUserName, $dbPassword);
Show all

We've got it all!

Now we have our secret data protected, while allowing for deployment across operating systems, at the same time!

Job interview

Another thing that sounds good in a job interview: "I learned how to make deployment easier from development servers to production servers."

Show all

What now?

Let's talk about cross-table queries.