Anime-Sanctuary

A dynamic site: PHP and MariaDB

Your site shows the same page to everyone. You will install PHP and MariaDB so a page can read its data from a database, without leaving a password lying around.

Course
Tutorial 19 of 35
Time
35 min to read
Tested with
Commands: tested on Debian 13.7 (Apache 2.4.69, PHP 8.4.27, MariaDB 11.8.6)
What you will be able to do
  • Install PHP and MariaDB and check that they answer.
  • Create a database and a user that only has the rights it needs.
  • Show data from the database with a safe PHP page.
  • Back up the database with a single command.

Before you start: Your first website with Apache.

Contents
  1. What a dynamic site is
  2. Install PHP and MariaDB
  3. Test PHP, then delete the test
  4. Secure the database, create a dedicated user
  5. Keep the password outside the site
  6. Read the database from a PHP page
  7. Back up the database with mysqldump
  8. If it fails
  9. What's next
  10. Sources

How do you build a dynamic site with PHP and MariaDB on Debian? Your index.html page is written once and for all. You can't show a visitor's messages or a list of your articles without rewriting it by hand. We will install what builds the page at every visit, connect it to a database and lock it down properly. At the end, one address on your site will show messages read from the database. Several new words will go by. Each one is explained when you need it, so just keep the idea.

What a dynamic site is

A dynamic site is a site whose pages are built on demand instead of being written once. Your current page is like a printed poster: everyone reads the same one. A dynamic page is more like a dish ordered at a restaurant. A cook makes it for each order, with whatever is in the pantry.

In this kitchen, two programs share the work:

  • PHP is the cook. It's a programming language: Apache hands it a page, it runs the instructions and sends back ready-made HTML.
  • MariaDB is the pantry. It stores your data in a database and finds it in the blink of an eye.

Neither program is on your server yet. Let's install them.

1Install PHP and MariaDB

Three packages are enough (a package is software ready to install, and apt is Debian's tool for installing them). The commands start with sudo, which lends you administrator rights for one command (see Users, sudo and file permissions). Apache then picks them up on its own:

  • libapache2-mod-php: a module, meaning an add-on for Apache, that teaches it to hand .php files to PHP.
  • php-mysql: the driver that lets PHP talk to MariaDB. MariaDB speaks the same language as MySQL, its elder sibling, hence the name.
  • mariadb-server: the database itself.
On the server
sudo apt install -y libapache2-mod-php php-mysql mariadb-server

The installation scrolls by for a while, then both programs start by themselves. First, check that PHP can talk to MariaDB. PDO is PHP's tool for talking to a database, and pdo_mysql is its driver.

On the server
php -m | grep pdo_mysql
Expected output
pdo_mysql

Next, check that MariaDB answers by asking it who we are.

On the server
sudo mariadb -e 'SELECT CURRENT_USER();'
Expected output
CURRENT_USER()
root@localhost

This root@localhost is the all-powerful account of the database. Don't mix it up with Debian's root: same name, two separate accounts. With sudo you get in without a password, because you already administer the server. Remember this: the site will never use this account. Now let's see whether PHP really runs a page.

2Test PHP, then delete the test

To see whether Apache really hands pages to PHP, give it a page that prints the installation's ID card. The phpinfo() function does exactly that. The file goes in the site folder from Your first website with Apache, and it stays only for the test.

On the server
echo '<?php phpinfo();' | sudo tee /var/www/exemple/info.php > /dev/null

A PHP file starts with <?php. That tells Apache to let PHP work instead of sending the text as it is. Now ask for the page, keeping only its title. The "On your computer" commands in this article aim at https://exemple.fr. Replace it with your own address. If your site has no HTTPS yet, replace https with http or see HTTPS with Let's Encrypt.

On your computer
curl -s https://exemple.fr/info.php | grep -o "<title>[^<]*</title>"
Expected output
<title>PHP 8.4.27 - phpinfo()</title>

PHP answers, with the version number Debian installed for you. In a browser, you would see a long table of settings.

On the server
sudo rm /var/www/exemple/info.php

Check that it's really gone. The server should answer 404, "not found".

On your computer
curl -s -o /dev/null -w '%{http_code}\n' https://exemple.fr/info.php
Expected output
404

PHP works and the test is gone. Before trusting MariaDB with data, let's talk about an old script that every guide tells you to run: mysql_secure_installation.

3Secure the database, create a dedicated user

mysql_secure_installation is a script that closes the settings left too open in a brand-new database. Its current name is mariadb-secure-installation. Good news: on Debian, everything is already set and you have nothing to run. If you run it anyway, it tells you "MariaDB is secure by default in Debian". The database's root account is already protected by sudo, as you saw.

The three things this script fixes
  • Anonymous accounts: accounts with no name, meant for trials, that let anyone in.
  • The test database: a practice database open to everyone.
  • Remote access for the root account: it should only log in from the server itself.

One thing left to check: the database should listen to the server itself and nobody else. The ss -ltn command lists the ports waiting for visitors, and grep 3306 keeps only MariaDB's.

On the server
ss -ltn | grep 3306
Expected output
LISTEN 0      80         127.0.0.1:3306      0.0.0.0:*

The address 127.0.0.1 means the server itself, and 3306 is MariaDB's port. No other machine can reach it, and there's no port to open in the firewall (ufw, the program that decides which connections come in, covered in A firewall with ufw). The database is ready for a new tenant.

A database is a filing cabinet, and each table is a spreadsheet in that cabinet. Let's create a database called exemple with a messages table of three columns (number, author, text) and three starter rows. The command sudo mariadb <<'SQL' sends MariaDB all the text that follows, up to a line containing only SQL. That end word is just a marker, and you pick it yourself.

On the server
sudo mariadb <<'SQL'
CREATE DATABASE exemple CHARACTER SET utf8mb4;
USE exemple;
CREATE TABLE messages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  auteur VARCHAR(50) NOT NULL,
  texte VARCHAR(255) NOT NULL
);
INSERT INTO messages (auteur, texte) VALUES
  ('alice', 'Mon premier message dans la base.'),
  ('alice', 'Le deuxieme message arrive.'),
  ('bob', 'Bonjour de la part de Bob.');
SQL

utf8mb4 lets the database keep every accent and symbol. Each SQL statement (SQL is the language of databases) ends with a semicolon. Now the key point: the site must never connect with the root account. If someone found a flaw in your page, they would hold the keys to all your databases. A dedicated account limits the damage to a single database.

The site only needs to read, add, change and delete rows. It will get those four rights, on this database only. Start with its password: 24 random characters, letters and digits, kept in a variable called MDP, a named memory slot.

On the server
MDP=$(tr -dc 'A-Za-z0-9' </dev/urandom | head -c 24)

Now create the user, then give it its rights:

  • 'siteapp'@'localhost': the siteapp account, which may only log in from the server itself.
  • exemple.*: every table in the exemple database, and nothing else.
On the server
sudo mariadb <<SQL
CREATE USER 'siteapp'@'localhost' IDENTIFIED BY '$MDP';
GRANT SELECT, INSERT, UPDATE, DELETE ON exemple.* TO 'siteapp'@'localhost';
SQL

Copy the block as it is. Before sending it, the terminal puts the real password in place of $MDP. If MDP were abc, MariaDB would receive IDENTIFIED BY 'abc'. Now read back the new account's rights. tail -1 keeps only the last line of the answer.

On the server
sudo mariadb -e "SHOW GRANTS FOR 'siteapp'@'localhost';" | tail -1
Expected output
GRANT SELECT, INSERT, UPDATE, DELETE ON `exemple`.* TO `siteapp`@`localhost`

The account exists, but the site doesn't know its password yet. Where do you put it?

4Keep the password outside the site

The password must live in a file that a visitor can never request. Apache sends out everything inside the site folder, /var/www/exemple. If the password were there, one PHP breakdown would be enough for Apache to send it out in clear text. So we put it next to the site, in a folder the site doesn't publish.

On the server
sudo mkdir /var/www/exemple-prive

The db.php file holds a list of settings: where the database is, the account name and its password. The command writes everything that follows into the file, up to the line EOF (the same kind of end marker as SQL a moment ago). Copy it as it is. $MDP is replaced by the real password as the file is written.

On the server
sudo tee /var/www/exemple-prive/db.php > /dev/null <<EOF
<?php
return [
    'dsn'  => 'mysql:host=localhost;dbname=exemple;charset=utf8mb4',
    'user' => 'siteapp',
    'pass' => '$MDP',
];
EOF

Now set who can read it. Apache works under the www-data account. The file belongs to root, the www-data group can read it, and nobody else.

On the server
sudo chown root:www-data /var/www/exemple-prive/db.php && sudo chmod 640 /var/www/exemple-prive/db.php

Check with another account. nobody, a system account with no rights, plays the visitor here. It must not be able to read the file.

On the server
sudo -u nobody cat /var/www/exemple-prive/db.php
Expected output
cat: /var/www/exemple-prive/db.php: Permission denied

One last check: the file must not be inside the site folder. Search for it. The command should print nothing.

On the server
sudo find /var/www/exemple -name db.php

The password is safe. Time to write the page that needs it.

5Read the database from a PHP page

This page shows an author's messages. It reads the file from step 4, opens the connection with PDO, asks the database its question and writes the result as HTML.

On the server
sudo tee /var/www/exemple/messages.php > /dev/null <<'EOF'
<?php
$cfg = require '/var/www/exemple-prive/db.php';
$pdo = new PDO($cfg['dsn'], $cfg['user'], $cfg['pass'], [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$auteur = $_GET['auteur'] ?? 'alice';
$req = $pdo->prepare('SELECT texte FROM messages WHERE auteur = ?');
$req->execute([$auteur]);
echo '<h1>Messages de ' . htmlspecialchars($auteur) . '</h1><ul>';
foreach ($req as $ligne) {
    echo '<li>' . htmlspecialchars($ligne['texte']) . '</li>';
}
echo '</ul>';
EOF

Three parts deserve a word:

  • require: reads the file from step 4 and brings back the list of settings that return stored there.
  • ATTR_ERRMODE: tells PDO to report any error clearly instead of staying silent.
  • prepare: creates a prepared query. You first send the template of the question, with a ? where the value goes, then the value separately.

That way, whatever the visitor types stays data and never becomes a command. Without it, they could slip their own instructions into your question. That's an SQL injection, one of the most common attacks.

Last piece: htmlspecialchars neutralizes characters like < before the text is written into the page. Without it, a message could contain code that runs in your visitors' browsers. Test the page, first without giving anything.

On your computer
curl -s https://exemple.fr/messages.php
Expected output
<h1>Messages de alice</h1><ul><li>Mon premier message dans la base.</li><li>Le deuxieme message arrive.</li></ul>

The page read the database. Now add ?auteur=bob to the address. The question mark introduces a piece of information the page can read.

On your computer
curl -s 'https://exemple.fr/messages.php?auteur=bob'
Expected output
<h1>Messages de bob</h1><ul><li>Bonjour de la part de Bob.</li></ul>

Now let's attack our own page, with the classic SQL injection text.

On your computer
curl -s -G --data-urlencode "auteur=x' OR '1'='1" https://exemple.fr/messages.php
Expected output
<h1>Messages de x&#039; OR &#039;1&#039;=&#039;1</h1><ul></ul>

The page looks for an author literally named "x' OR '1'='1", finds none and shows nothing. The attack stayed plain text.

Finally, check that the siteapp account can't destroy the table, even if it wanted to. This test is the only time you display the password in your terminal, on purpose. Display it, then copy it with the mouse. If nothing appears, the variable is empty, which means you switched windows.

On the server
echo "$MDP"

The next command asks for this password. Paste it into the terminal (right click, or Ctrl+Shift+V). Nothing shows while you type, not even dots. That's normal, so press Enter. Because you don't type it in the command itself, it doesn't stay in your history, the list of commands you already typed.

On the server
mariadb -usiteapp -p exemple -e 'DROP TABLE messages;' 2>&1 | tail -1
Expected output
ERROR 1142 (42000) at line 1: DROP command denied to user 'siteapp'@'localhost' for table `exemple`.`messages`

MariaDB refuses: the account doesn't have that right. One thing left: keep a copy of all this, just in case.

6Back up the database with mysqldump

A database backup fits in one text file: a list of SQL statements that rebuilds everything. The mariadb-dump command makes it. Its old name, mysqldump, still works. Write it in your home folder: cd ~ takes you there (the ~ sign is the shortcut for that folder), and the backup never ends up in a folder served by Apache. The > sign sends the command's output into the file exemple.sql instead of printing it on screen.

On the server
cd ~
sudo mariadb-dump exemple > exemple.sql && chmod 600 exemple.sql

The chmod 600 keeps the file for you alone, because it holds all your data. Check that it contains a single INSERT INTO statement, which groups our three messages.

On the server
grep -c "INSERT INTO" exemple.sql
Expected output
1

A backup is only worth something if you know how to read it back. To restore, you replay the file into a database. Try it in a practice database, so nothing is at risk.

On the server
sudo mariadb -e 'CREATE DATABASE essai;' && sudo mariadb essai < exemple.sql

The command prints nothing, which is a good sign. Count the rows of the restored table.

On the server
sudo mariadb -e 'SELECT COUNT(*) FROM essai.messages;'
Expected output
COUNT(*)
3

The three messages are back. To restore for real, you would run the same command with exemple in place of essai. It would replace the table's content. Now delete the practice database.

On the server
sudo mariadb -e 'DROP DATABASE essai;'

A backup that stays on the server won't save you from a dead disk. Copy exemple.sql somewhere else, onto another computer.

If it fails

Three situations come up most often. In the first two, Apache's error log gives the cause, because PHP hides its errors from visitors:

On the server
sudo tail -n 1 /var/log/apache2/exemple-error.log
  • A blank page or a 500 error, with Access denied for user 'siteapp'@'localhost' in the log. The password in db.php isn't the database's. Make a new password and give it to the account with the block after this list, then rewrite db.php with the command from step 4.
  • A 500 error with could not find driver. The driver is missing. Install php-mysql (step 1), then restart Apache with sudo systemctl restart apache2, because a reload doesn't load a new module.
  • The browser shows the PHP code instead of the page. Apache's PHP module isn't active. Check that libapache2-mod-php is installed, then run sudo systemctl restart apache2.

For the first situation, here is the block that makes a new password and gives it to the account:

On the server
MDP=$(tr -dc 'A-Za-z0-9' </dev/urandom | head -c 24)
sudo mariadb <<SQL
ALTER USER 'siteapp'@'localhost' IDENTIFIED BY '$MDP';
SQL

What's next

Your site now reads its data from a database, and you know how to back it up. But many ready-made apps don't run inside Apache. They listen on their own port, like a small separate server. To show them on exemple.fr over HTTPS without opening a new port, you need a go-between. That's covered in Reverse proxy.