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
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.phpfiles 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.
sudo apt install -y libapache2-mod-php php-mysql mariadb-serverThe 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.
php -m | grep pdo_mysqlpdo_mysqlNext, check that MariaDB answers by asking it who we are.
sudo mariadb -e 'SELECT CURRENT_USER();'CURRENT_USER()
root@localhostThis 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.
echo '<?php phpinfo();' | sudo tee /var/www/exemple/info.php > /dev/nullA 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.
curl -s https://exemple.fr/info.php | grep -o "<title>[^<]*</title>"<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.
sudo rm /var/www/exemple/info.phpCheck that it's really gone. The server should answer 404, "not found".
curl -s -o /dev/null -w '%{http_code}\n' https://exemple.fr/info.php404PHP 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
testdatabase: a practice database open to everyone. - Remote access for the
rootaccount: 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.
ss -ltn | grep 3306LISTEN 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.
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.');
SQLutf8mb4 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.
MDP=$(tr -dc 'A-Za-z0-9' </dev/urandom | head -c 24)Now create the user, then give it its rights:
'siteapp'@'localhost': thesiteappaccount, which may only log in from the server itself.exemple.*: every table in theexempledatabase, and nothing else.
sudo mariadb <<SQL
CREATE USER 'siteapp'@'localhost' IDENTIFIED BY '$MDP';
GRANT SELECT, INSERT, UPDATE, DELETE ON exemple.* TO 'siteapp'@'localhost';
SQLCopy 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.
sudo mariadb -e "SHOW GRANTS FOR 'siteapp'@'localhost';" | tail -1GRANT 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.
sudo mkdir /var/www/exemple-priveThe 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.
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',
];
EOFNow 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.
sudo chown root:www-data /var/www/exemple-prive/db.php && sudo chmod 640 /var/www/exemple-prive/db.phpCheck with another account. nobody, a system account with no rights, plays the visitor here. It must not be able to read the file.
sudo -u nobody cat /var/www/exemple-prive/db.phpcat: /var/www/exemple-prive/db.php: Permission deniedOne last check: the file must not be inside the site folder. Search for it. The command should print nothing.
sudo find /var/www/exemple -name db.phpThe 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.
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>';
EOFThree parts deserve a word:
require: reads the file from step 4 and brings back the list of settings thatreturnstored 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.
curl -s https://exemple.fr/messages.php<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.
curl -s 'https://exemple.fr/messages.php?auteur=bob'<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.
curl -s -G --data-urlencode "auteur=x' OR '1'='1" https://exemple.fr/messages.php<h1>Messages de x' OR '1'='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.
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.
mariadb -usiteapp -p exemple -e 'DROP TABLE messages;' 2>&1 | tail -1ERROR 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.
cd ~
sudo mariadb-dump exemple > exemple.sql && chmod 600 exemple.sqlThe 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.
grep -c "INSERT INTO" exemple.sql1A 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.
sudo mariadb -e 'CREATE DATABASE essai;' && sudo mariadb essai < exemple.sqlThe command prints nothing, which is a good sign. Count the rows of the restored table.
sudo mariadb -e 'SELECT COUNT(*) FROM essai.messages;'COUNT(*)
3The 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.
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:
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 indb.phpisn't the database's. Make a new password and give it to the account with the block after this list, then rewritedb.phpwith the command from step 4. - A 500 error with
could not find driver. The driver is missing. Installphp-mysql(step 1), then restart Apache withsudo 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-phpis installed, then runsudo systemctl restart apache2.
For the first situation, here is the block that makes a new password and gives it to the account:
MDP=$(tr -dc 'A-Za-z0-9' </dev/urandom | head -c 24)
sudo mariadb <<SQL
ALTER USER 'siteapp'@'localhost' IDENTIFIED BY '$MDP';
SQLWhat'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.