Deze website maakt gebruik van diensten van Google voor het tonen van advertenties en het bijhouden van bezoekersstatistieken. Google kan hiermee uw surfgedrag volgen. Zie voor meer informatie het privacybeleid van Google. Via Your Online Choices kunt u zich afmelden voor gepersonaliseerde advertenties. Deze melding verbergen.

21 MySQL Basistechnieken

In hoofdstuk 20 zijn de principes van MySQL aan bod gekomen: van databases, tabellen, kolommen en rijen tot SQL-query's. In dit hoofdstuk gaan we dat combineren met PHP en gaan we verder in op de verschillende voorbeeldquery's die we in het vorige hoofstuk hebben gezien. Dit doen we aan de hand van één groot praktijkvoorbeeld: het maken van een loginsysteem. In dit hoofdstuk zien we hoe we de database inrichten en maken we de scripts om in te loggen, pagina's te beveiligen en gebruikers hun wachtwoord laten wijzen.

Praktijkvoorbeeld: loginsysteem
Volledige code
Oefening: Wachtwoord vergeten

Praktijkvoorbeeld: loginsysteem

Het wordt ten strengste afgeraden om het navolgende praktijkvoorbeeld in een productieomgeving te gebruiken. Het is bedoeld om de basisprincipes van een loginscript met een database uit te leggen en gaat voorbij aan verschillende noodzakelijke beveiligingsmaatregelen. Zie hoofdstuk 30 voor meer informatie. In de oefening van hoofdstuk 30 wordt dit praktijkvoorbeeld aangepast om de beveiliging aan te scherpen. Mocht je dit loginscript in een productieomgeving willen gebruiken, neem dan de uitwerking van hoofdstuk 30 als uitgangspunt.

Database maken

De eerste stap is het maken van een database. Bij shared webhosting heeft de webhoster de database vaak al aangemaakt, of moet deze worden aangemaakt via het beheerpaneel van de webhosting. De werkwijze verschilt per webhost, maar het is belangrijk vier gegevens te achterhalen: het adres van de MySQL server (meestal localhost), de MySQL gebruikersnaam, het bijbehorende wachtwoord en de naam van de MySQL database.

In geval van XAMPP of een vergelijkbaar pakket op je computer is het adres van de MySQL server localhost, is de standaard MySQL gebruikersnaam root zonder wachtwoord en moet er eerst nog een lege database aangemaakt worden. Het aanmaken van een database kan via phpMyAdmin, maar kan ook direct vanuit PHP.

Om lokaal testen makkelijker te maken is het handig om de MySQL gegevens in een apart PHP-bestand te zetten dat later wordt geïnclude waar nodig. Zeker omdat gebruikersnaam, wachtwoord en database lokaal meestal niet hetzelfde zullen zijn als op de webhost. In ons voorbeeld maken we een bestand config.inc.php met daarin de onderstaande PHP-code. Pas de servergegevens aan als dat nodig is.

<?php
//bestand met databasegegevens
$db['server'] = 'localhost';
$db['user'] = 'root';
$db['password'] = '';
$db['database'] = 'phpboek';
?>

Het maken van een database gaat met een CREATE DATABASE SQL-statement. De syntaxis daarvan is als volgt:

CREATE DATABASE [IF NOT EXISTS] db_name [CHARACTER SET = charset_name | COLLATE = collation_name]

In de simpelste vorm zorgt CREATE DATABASE db_name dat er een database wordt gemaakt met de gegeven naam. Het toevoegen van het optionele IF NOT EXISTS zorgt er voor dat er geen foutmelding wordt gegeven als de database al bestaat. Optioneel kan met CHARACTER SET of COLLATE een karakterset of collatie worden opgegeven, anders worden de voor deze server standaard karakterset en collatie gebruikt. Beide opgeven is niet nodig, omdat een collatie impliciet een karakterset bevat.

Om nu via PHP de database te maken zijn er een paar PHP-functies nodig. Allereerst mysqli_connect() om een verbinding met de MySQL server te maken en vervolgens mysqli_query() om de SQL-query daadwerkelijk uit te voeren. Let op de i in mysqli. Er zijn ook vergelijkbare functies zonder de i. Deze functies zijn verouderd en kunnen niet gebruikt worden in PHP 7. De mysqli-familie van functies kan gebruikt worden in zowel PHP 5 als PHP 7.

mysqli mysqli_connect ([ string $host [, string $username [, string $passwd [, string $dbname ]]]] )

De functie mysqli_connect() vraagt als parameters het adres van de MySQL-server en optioneel de MySQL-gebruikersnaam, het bijbehorende wachtwoord en de naam van de database die gebruikt moet worden. Normaliter moeten server, gebruikersnaam en wachtwoord altijd worden opgegeven. Het selecteren van de database kan alleen als deze bestaat, dus kan nog niet gedaan worden als de database nog gemaakt moet worden. Het resultaat van de functie is een mysqli link. Deze link moet in een variabele opgeslagen worden, want is nodig voor mysqli_query() en enkele andere mysqli-functies.

mixed mysqli_query ( mysqli $link , string $query )

mysqli_query() voert daadwerkelijk een SQL-query uit. De parameters zijn de link die met mysqli_connect() is gemaakt en de uit te voeren SQL-query. Het resultaat van de functie is afhankelijk van de SQL-query, maar altijd FALSE wanneer de query mislukt is. Als de query gelukt is, wordt voor SELECT, SHOW, DESCRIBE en EXPLAIN query's is een resultaatset gegeven. Voor andere query's, waaronder CREATE DATABASE, is het resultaat TRUE.

Hiermee kunnen we de PHP-code schrijven die nodig is om de database te maken. Deze slaan we op in het bestand install.php.

<?php
//include database gegevens
include('config.inc.php');
//verbind met server
$link = mysqli_connect($db['server'], $db['user'], $db['password']);
//query om database te maken
$sql = "CREATE DATABASE `" . $db['database'] . "` COLLATE 'latin1_general_ci'";
//voer query uit
mysqli_query($link, $sql);
?>

Allereerst includen we de MySQL gegevens, om vervolgens met mysqli_connect() te verbinden met de MySQL-server. De naam van de database geven we hier nog niet op, omdat de database nog niet bestaat. De gemaakte $link gebruiken we in mysqli_query() om de SQL-query uit $sql uit te voeren. De query maakt een database met de naam zoals opgegeven in config.inc.php met de collatie latin1_general_ci, en dus automatisch de karakterset latin1. Let op de backticks (`) rondom de naam van de database en de enkele aanhalingstekens (') rondom de naam van de collatie. Voor de PHP-string gebruiken we nu dubbele aanhalingstekens (") om te voorkomen dat we de enkele aanhalingstekens die bij de SQL-query horen iedere keer moeten escapen. Voor het maken van de database is het nu voldoende om install.php aan te roepen via de webbrowser. Vervolgens kan met phpMyAdmin gecontroleerd worden of de database daadwerkelijk gemaakt is.

Wat als er een fout zit in de query?

Als er een fout zit in de query komen we daar alleen achter doordat er geen database wordt gemaakt. Om er achter te komen wat er fout gaat is het handig om eerst de SQL-query te bekijken via echo $sql;. Daarnaast kan de functie mysqli_error() gebruikt worden om de laatste foutmelding te tonen:

string mysqli_error ( mysqli $link )

Het resultaat van mysqli_error() is een string met de laatste foutmelding van MySQL. Is er geen foutmelding, dan is het resultaat een lege string. Als paramter moet de link van mysqli_connect() worden opgegeven. Omdat alleen de laatste foutmelding wordt gegeven, is het handig om de functie direct na iedere mysqli_query() te plaatsen. Hiermee kunnen we ons installatiescript install.php als volgt uitbreiden:

Tabel maken

Nu we een database hebben kunnen we na gaan denken over tabellen. Voor ieder MySQL-project is het goed vooraf goed na te denken over welke tabellen met welke kolommen nodig zijn. Verkeerde keuzes hierin kunnen doorwerken in het verdere script, waardoor het veel werk kan zijn om dat achteraf aan te passen.

Voor ons loginsysteem hebben we voldoende aan één tabel met gebruikersgegevens. Laten we deze gebruikers noemen. Voor de kolommen hebben we in ieder geval kolom voor gebruikersnaam en een kolom voor wachtwoord nodig. De gebruikersnamen moeten uniek zijn, dus in principe kan deze kolom dienen als primaire sleutel, maar een id is vaak handig, dus nemen we nog een kolom id erbij als primaire sleutel. Als gebruikers hun wachtwoord kwijt zijn kunnen we per e-mail een nieuw wachtwoord zenden, waarvoor nog een kolom email nodig is. En om te zien of iemand het account ook gebruikt nemen we nog een kolom laatst_ingelogd om een tijdstempel te bewaren. Dit geeft een tabel met de vorm van tabel 21.1 die we ook al gezien hebben in hoofdstuk 20.

Tabel 21.1: Voorbeeld tabel *gebruikers*
id	gebruikersnaam	wachtwoord	email	laatst_ingelogd

Nu we weten hoe de tabel er uit gaat zien moeten we voor iedere kolom nog het datatype bepalen. Dit gaat als volgt:

Voor de kolom id willen we op een volgende nummers. Dus kiezen we een integer datatype. Hoeveel gebruikers verwachten we? Wel meer dan 255. Ook meer dan 65535? Misschien niet. Maar bedenk dat id's na het verwijderen van een rij niet hergebruikt worden, dus nemen we het zekere voor het onzekeren en kiezen we voor MEDIUMINT UNSIGNED en kunnen we zestien miljoen unieke gebruikers aan.
De kolom gebruikersnaam wordt een tekstueel datatype, maar wordt het *TEXT of VARCHAR? Het voordeel van een VARCHAR kolom is dat eventuele spaties voor- en achteraan de tekst automatisch worden verwijderd. Dus als deze per ongeluk worden getypt bij het aanmaken van een account wordt dat automatisch opgelost. We moeten dan wel opgeven hoe lang de gebruikersnamen mogen zijn. 50 tekens is wel genoeg, dus wordt het datatype VARCHAR(64).
Voor het wachtwoord moeten we bepalen hoe we dit gaan opslaan. Het liefst versleuteld, bijvoorbeeld met het SHA256 algoritme. Dit geeft een hexadecimale tekenreeks van 64 tekens, dus is VARCHAR(64) geschikt.
De email kolom wordt eveneens een tekstkolom. Veel weten we niet van de te verwachten e-mailadressen, maar ze zullen in ieder geval niet langer zijn dan 254 tekens. VARCHAR(254) volstaat, maar in principe kun je ook TINYTEXT nemen.
Voor de kolom laatst_ingelogd is het TIMESTAMP datatype bij uitstek geschikt.

Nu weten we genoeg om de SQL-query te schrijven die de tabel maakt. Hiervoor gebruiken we het CREATE TABLE SQL-statement:

CREATE TABLE [IF NOT EXISTS] table_name

    (

    col_name data_type [NOT NULL] [DEFAULT default_value] [AUTO_INCREMENT] [PRIMARY KEY | UNIQUE KEY],

    ...)

[ENGINE = engine_name]

Net als bij het maken van een database zorgt IF NOT EXISTS er voor dat geen foutmelding wordt gegeven als de tabel al bestaat. Na de tabelnaam wordt tussen haakjes de kolomdefinities gegeven, door komma's gescheiden. Een kolomdefinitie bestaat in ieder geval uit de naam van de kolom gevolgd door het datatype. Daarna kunnen nog enkele opties worden toegevoegd. NOT NULL maakt het verplicht om de kolom voor iedere rij in te vullen. Wordt dat niet gedaan, dan wordt de hele rij niet opgeslagen. Met DEFAULT kan een standaardwaarde voor de kolom worden opgegeven (niet voor *TEXT datatypen). Deze wordt dan gebruikt als er bij het invoegen van een rij geen waarde voor de kolom wordt opgegeven. AUTO_INCREMENT zorgt er voor dat bij het invoegen van een nieuwe rij steeds een getal wordt ingevoegd dat 1 hoger is dan het vorige. Er kan één AUTO_INCREMENT kolom zijn per tabel, deze kolom moet numeriek zijn zonder teken, en moet een unieke of primaire sleutel zijn. Met PRIMARY KEY wordt aangegeven dat deze kolom de primaire sleutel is; ook hier één kolom per tabel. Als er ook nog andere kolommen zijn waarvan afgedwongen moet worden dat alle waarden uniek zijn, dan kan dit met UNIQUE KEY. Voor een id-kolom wordt typisch NOT NULL AUTO_INCREMENT PRIMARY KEY bij elkaar gebruikt. Na de kolomdefinities kan met ENGINE nog de gewenste opslagmethode voor deze tabel worden opgegeven, om iets anders dan de standaard te gebruiken.

Hiermee kunnen we de SQL-query voor het maken van de tabel gebruikers opstellen:

CREATE TABLE `gebruikers`
    (
    `id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `gebruikersnaam` VARCHAR(50) NOT NULL UNIQUE KEY,
    `wachtwoord` VARCHAR(64) NOT NULL,
    `email` VARCHAR(254),
    `laatst_ingelogd` TIMESTAMP
    )

Om via PHP de tabel te maken kunnen we een nieuw script maken met de functies mysqli_connect() en mysqli_query(), zoals bij het maken van de database. Maar we kunnen ook het eerdere installatiescript uitbreiden. Dan hebben we mysqli_connect() niet nog een keer nodig, omdat we al een verbinding met de database hebben. Alleen hebben we toen geen database kunnen opgeven, dus moeten we dit nog doen met de functie mysqli_select_db():

bool mysqli_select_db ( mysqli $link , string $dbname )

Deze functie stelt in welke database $dbname moet worden gebruikt voor de link $link. Hiermee kunnen we het installatiescript install.php uitbreiden door het volgende aan het eind toe te voegen:

<?php
//selecteer database
mysqli_select_db($link, $db['database']);

//query om tabel te maken
$sql = "CREATE TABLE `gebruikers`
    (
    `id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `gebruikersnaam` VARCHAR(50) NOT NULL UNIQUE KEY,
    `wachtwoord` VARCHAR(64) NOT NULL,
    `email` VARCHAR(254),
    `laatst_ingelogd` TIMESTAMP
    ) ";
//laat SQL-query zien
echo $sql . '<br>';
//voer query uit
mysqli_query($link, $sql);
//laat fouten zien
echo mysqli_error($link) . '<br>';
?>

Roep install.php nogmaals aan via de browser. De tabel wordt nu gemaakt. Je zult de twee SQL-query's voor het maken van de database en de tabel in beeld zien, én de foutmelding Can't create database 'phpboek'; database exists. De database bestaat al, logisch, want die hebben we daarstraks al gemaakt. Herlaad de pagina en je zult ook de foutmelding Table 'gebruikers' already exists te zien krijgen, een teken dat de tabel gemaakt is. Je kunt ook via phpMyAdmin zien dat de tabel gemaakt is.

Tabel vullen

Om het loginsysteem te kunnen testen en later ook gebruiken, zullen we nu een gebruikersaccount voor onszelf maken en hiervoor een rij toevoegen aan de nieuwe tabel. Hiervoor gebruiken we het SQL-statement INSERT INTO:

INSERT [IGNORE] INTO table_name

SET

    col_name = value,

    ...

In essentie wordt aangegeven aan welke tabel een rij moet worden toegevoegd met daarachter een lijst met kolomnamen en de waarden die moeten worden ingevoegd. IGNORE zorgt er voor dat er geen foutmelding wordt gegeven als geprobeerd wordt in een kolom met primaire of unieke sleutel een waarde in te voegen die al bestaat.

Om een nieuw gebruikersaccount aan de tabel toe te voegen hoeven we alleen de kolommen gebruikersnaam, wachtwoord en email in te vullen. id wordt automatisch ingevuld omdat we AUTO_INCREMENT opgegeven hebben en laatst_ingelogd wordt eveneens automatisch ingevuld omdat dit de eerste (en enige) TIMESTAMP kolom is. Laten we een account maken met gebruikersnaam admin, wachtwoord test en e-mailadres admin@example.com. Gebruikersnaam en e-mailadres kunnen we al direct in de query invullen. Van het wachtwoord willen we de hash bewaren, maar die moeten we eerst door PHP laten uitrekenen, dus vullen we voor nu even een sterretje in:

INSERT INTO `gebruikers`
SET
    `gebruikersnaam` = 'admin',
    `wachtwoord` = '*',
    `email` = 'admin@example.com'

Om de hash van het wachtwoord te berekenen gebruiken we de PHP-functie hash() die we ook al hebben gezien in hoofdstuk 11. We gebruiken het sha256 algoritme.

string hash ( string $algo , string $data )

Hiermee kunnen we het installatiescript install.php uitbreiden door het volgende aan het eind toe te voegen:

<?php
//maak gebruikersaccount
$gebruikersnaam = 'admin';
$wachtwoord = 'test';
$email = 'admin@example.com';

//bereken hash van wachtwoord
$wachtwoord = hash('sha256', $wachtwoord);

//query om tabel te maken
$sql = "INSERT INTO `gebruikers`
SET
    `gebruikersnaam` = '" . $gebruikersnaam . "',
    `wachtwoord` = '" . $wachtwoord . "',
    `email` = '" . $email . "' ";
//laat SQL-query zien
echo $sql . '<br>';
//voer query uit
mysqli_query($link, $sql);
//laat fouten zien
echo mysqli_error($link) . '<br>';
?>

Roep install.php nogmaals aan via de browser om de rij in te voegen. Je zult de drie SQL-query's voor het maken van de database, de tabel en het invoegen van de rij in beeld zien. Tussendoor komen twee foutmeldingen omdat de database en tabel al bestaan. Ververs de pagina en er komt een derde foutmelding bij: Duplicate entry 'admin' for key 'gebruikersnaam'. Hieraan kun je zien dat het invoegen van de rij gelukt is. Je kunt ook via phpMyAdmin zien dat de tabel gemaakt is, deze ziet er dan als tabel 21.2. Uiteraard met een ander tijdstempel.

Tabel 21.2: Tabel *gebruikers* met eerste rij
id	gebruikersnaam	wachtwoord	email	laatst_ingelogd
1	admin	9f86d081884c7d659a2feaa0c55ad015 a3bf4f1b2b0b822cd15d6c15b0f00a08	admin@example.com	2015-08-25 22:20:05

Het aanmaken van extra gebruikersaccounts doen we later via het nog te maken beheerdeel. Een oplettende lezer zal zich nog afvragen waarom we de hash van het wachtwoord in PHP berekenen en of MySQL dit niet zelf kan. MySQL kan dit inderdaad zelf, maar dit zou een extra beveiligingsrisico opleveren. Het wachtwoord moet dan namelijk in plain text worden overgedragen tussen PHP en MySQL. Als PHP en MySQL op dezelfde machine draaien is dat niet zo'n probleem, maar als MySQL op een externe server wordt gebruikt is dat niet wenselijk. Het is daarom beter om het hashen van het wachtwoord zo dicht mogelijk bij de bron te doen, in dit geval in PHP dus.

Login gedeelte

Voor het loginscript, dat we login.php noemen, gebruiken we een groot deel van het aanmeldscript uit hoofdstuk 11. Het relevante gedeelte is hieronder afgebeeld. Het formulier is hetzelfde, enkel het deel dat controleert of gebruikersnaam en wachtwoord correct zijn moet worden aangepast.

<?php
//als formulier verzonden
if (!empty($_POST['username']) && !empty($_POST['password'])) {
    //controleer wachtwoord

    //nog in te vullen

    //wachtwoord correct
    if ($nog_in_te_vullen) {
        $login_correct = TRUE;
    }
    //wachtwoord niet correct
    else {
        $login_error = TRUE;
    }
}
?>

<!DOCTYPE HTML>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Login</title>
</head>
<body>

<?php if ($login_correct === TRUE) { ?>

    <h1>Login gelukt!</h1>
    <p>Welkom in het beveiligde gedeelte van deze website!</p>

<?php } else { ?>

    <h1>Login</h1>
    <p>Vul gebruikersnaam en wachtwoord in om toegang te krijgen tot het beveiligde gedeelte van deze website</p>

    <?php
    if ($login_error === TRUE) {
        echo '<p class="error">De gebruikersnaam/wachtwoord combinatie bestaat niet.</p>';
    }
    ?>

    <form method="post">
    <table>
    <tr><td>Gebruikersnaam:</td><td><input type="text" name="username"></td></tr>
    <tr><td>Wachtwoord:</td><td><input type="password" name="password"></td></tr>
    <tr><td></td><td><input type="submit" value="Login"></td></tr>
    </table>
    </form>

<?php } ?>

</body>
</html>

Om nu te controleren of iemands gebruikersnaam en wachtwoord correct zijn, moeten we in de tabel gebruikers van onze database kijken of er een rij is waarin zowel de gebruikersnaam voorkomt als de hash van het ingevulde wachtwoord. Dit doen we met een SELECT SQL-statement:

SELECT

    col_name [, ...]

    FROM table_name

    [WHERE where_condition [, ...]

    [ORDER BY col_name [DESC] [, ...]]

    [LIMIT [offset,] row_count]

Met een SELECT query kunnen gegevens uit de database worden gelezen. In de simpelste vorm wordt een lijst met kolomnamen gegeven (gescheiden door komma's) en de naam van de tabel waaruit gelezen moet worden. Dit geeft een resultaatset met alle rijen van de tabel. In plaats van een lijst met kolomnamen kan ook een sterretje (*) worden gebruikt om alle kolommen te selecteren. Optioneel kan het aantal rijen in de resultaatset beperkt worden door met WHERE één of meerdere voorwaarden op te geven. Een voorwaarde is bijvoorbeeld col_name = value, waarmee wordt aangegeven dat een bepaalde kolom een bepaalde waarde moet bevatten. Naast het gelijkteken kunnen ook andere operatoren zoals <, <=, > en >= gebruikt worden. WHERE-voorwaarden worden gescheiden door AND of OR om de voorwaarden uitsluitend of aanvullend te laten zijn, vergelijkbaar met && en || in PHP. Gebruik waar nodig haakjes om de juiste voorwaarden te groeperen. Met behulp van ORDER BY kan de resultaatset gesorteerd worden op één of meerdere kolommen. Geef de kolommen in volgorde op waarop ze gesorteerd moeten worden. Met DESC achter een kolomnaam kan worden aangegeven dat deze op aflopende volgorde moet worden gesorteerd. Met behulp van LIMIT kan de resultaatset nog verder beperkt worden nadat eventueel WHERE en ORDER BY zijn toegepast door optioneel op te geven hoeveel rijen vanaf het begin moeten worden overgeslagen (offset) en hoeveel rijen de resultaatset maximaal (row_count) mag bevatten.

Om een ingevulde gebruikersnaam en wachtwoord te controleren kunnen we een SELECT query schijven met daarin een WHERE-conditie voor de kolommen gebruikersnaam en wachtwoord. Ook kunnen we LIMIT 1 toevoegen zodat MySQL niet verder hoeft te kijken wanneer er een overeenkomst gevonden is. Uit de tabel lezen we de kolom id, zodat we dit kunnen gebruiken in een cookie. Dit geeft een query die zal lijken op de volgende, de gegevens uit het formulier moeten nog worden ingevoegd:

SELECT
    `id`
    FROM `gebruikers`
    WHERE `gebruikersnaam` = 'GEBRUIKERSNAAM_UIT_POST'
    AND `wachtwoord` = 'HASH_VAN_WACHTWOORD_UIT_POST'
    LIMIT 1

Het is niet verstandig om de $_POST-velden direct in de SQL-query in te voegen. Dit maakt je script gevoelig voor zogeheten SQL-injecties. Bedenk maar eens wat er kan gebeuren als je als gebruikersnaam admin' OR 'id' > '0 zou invullen. Om dat te voorkomen is er de functie mysqli_real_escape_string(). Deze functie zorgt er voor dat speciale tekens, zoals aanhalingstekens, op de juiste manier worden geëscapet en hiermee potentiele SQL-injecties onschadelijk gemaakt.

string mysqli_real_escape_string ( mysqli $link , string $escapestr )

De functie zorgt ervoor dat tekens NUL (ASCII 0x00), \n, \r, \, ', ", en Control-Z in de string $escapestr worden voorzien van een backslash. Verder moet een geldige $link worden opgegeven die is gemaakt met mysqli_connect(). Het is belangrijk om te weten dat mysqli_real_escape_string() de standaard karakterset van de server gebruikt, en niet die van de tabel. Als de tabel een andere karakterset gebruikt dan de server, moet vooraf nog de juiste karakterset worden opgegeven met mysqli_set_charset():

bool mysqli_set_charset ( mysqli $link , string $charset )

In het geval van ons loginsysteem gebruiken we de karakterset latin1. Nu is dat in ons geval hetzelfde als de standaard karakterset. Als je de MySQL server niet zelf beheert is het desondanks toch verstandig om de gebruikte karakterset in te stellen met mysqli_set_charset(), voor het geval de beheerder van de server besluit de standaardinstelling te wijzigen. Hiermee kunnen we dan het volgende PHP-script schrijven om de logincontrole te doen:

<?php
    //include database gegevens
    include('config.inc.php');
    //verbind met server
    $link = mysqli_connect($db['server'], $db['user'], $db['password'], $db['database']);
    //stel karakterset in voor mysqli_real_escape_string
    mysqli_set_charset($link, 'latin1');
    //bereken hash van wachtwoord
    $wachtwoord = hash('sha256', $_POST['password']);
    //query om tabel te lezen
    $sql = "SELECT
    `id`
    FROM `gebruikers`
    WHERE `gebruikersnaam` = '" . mysqli_real_escape_string($link, $_POST['username']) . "'
    AND `wachtwoord` = '" . mysqli_real_escape_string($link, $wachtwoord) . "'
    LIMIT 1";
    //voer query uit
    mysqli_query($link, $sql);

Wat op valt is dat we bij mysqli_connect() als vierde parameter nu direct de database opgeven, dat hoeft dan niet meer apart zoals in het installatiescript. Wat ook op valt is dat query uitvoeren zoals we hebben gedaan in het installatiescript, maar dan? We kunnen nu nog nergens zien of de gebruikersnaam en wachtwoord goed zijn en welk id daar dan bij hoort! Met de functie mysqli_num_rows() kunnen we achterhalen of de query resultaten heeft opgeleverd (en zo ja hoeveel rijen).

int mysqli_num_rows ( mysqli_result $result )

Deze functie verwacht het resultaat van een query als parameter. Daarvoor moeten we dan het resultaat van mysqli_query() nog even in een variabele opslaan, zodat we deze hier kunnen gebruiken. Goed, nu weten we of er een geldig gebruikersaccount is gevonden in de tabel, maar nu moeten we het id nog zien te achterhalen. Dit kan met één van twee functies: mysqli_fetch_row() en mysqli_fetch_assoc():

mixed mysqli_fetch_row ( mysqli_result $result )

array mysqli_fetch_assoc ( mysqli_result $result )

Beide functies zijn vergelijkbaar. Ze verwachten het resultaat van mysqli_query() als parameter, net zoals mysqli_num_rows(). Het resultaat van de functies is een array met daarin de waarden van de eerste rij in de resultaatset. Bij mysqli_fetch_row() zijn de sleutels van die rij numeriek, beginnend bij 0. Bij mysqli_fetch_assoc() zijn de sleutels gelijk aan de namen van de kolommen. mysqli_fetch_row() is marginaal sneller; mysqli_fetch_assoc() is vooral nuttig bij het selecteren van alle kolommen (met *), zodat er geen problemen ontstaan als de tabel ooit wordt aangepast. Om de tweede en volgende rij uit de resultaatset te lezen moet de gekozen functie steeds herhaald worden. Een while-lus is hierbij bijzonder handig. Als er geen rijen meer zijn geeft de functie NULL terug.

Hiermee kunnen we het loginscript verder af maken, door te controleren of er een rij is gevonden bij de opgegeven gebruikersnaam en wachtwoord, en zo ja het id op te halen en een cookie te plaatsen.

    //voer query uit
    $result = mysqli_query($link, $sql);
    if (mysqli_num_rows($result) == 1) {
        $row = mysqli_fetch_row($result);
        $cookie['id'] = $row[0];
        $cookie['password'] = $wachtwoord;
        //zet cookie
        setcookie('login', serialize($cookie), time() + 60*60*24*7*2, '/');
        //login is gelukt
        $login_correct = TRUE;
    }
    //wachtwoord niet correct
    else {
        $login_error = TRUE;
    }
}
?>

Met mysqli_num_rows() controleren we of er precies één rij is. Vanwege LIMIT 1 kan het antwoord hier alleen maar 0 of 1 zijn. Omdat we alleen het id willen weten, gebruiken we mysqli_fetch_row() en kennen dit toe aan $row. Vervolgens kunnen we met $row[0] het id uitlezen. Vervolgens plaatsen we het id en de hash van het wachtwoord in een cookie, zoals in hoofdstuk 11. Op beveiligde pagina's gebruiken we deze gegevens uit het cookie om te controleren of een gebruiker is ingelogd.

Pagina's beveiligen

Voor pagina's die alleen toegankelijk moeten zijn voor bezoekers die zijn ingelogd, zullen we een klein scriptje maken dat bovenaan iedere te beveiligen pagina kunnen includen. Dit script haalt het gebruikers-id en gehashte wachtwoord uit het cookie en controleert of de combinatie voorkomt in de gebruikers-tabel. Het script heeft veel weg van het login-script. We noemen het logincheck.inc.php:

<?php
//haal gegevens uit cookie
$cookie = unserialize($_COOKIE['login']);
$user_id = $cookie['id'];
$wachtwoord = $cookie['password'];
//controleer of waarden in het cookie zinnig zijn
if (is_numeric($user_id) && (strlen($wachtwoord) == 64)) {
    //include database gegevens
    include('config.inc.php');
    //verbind met server
    $link = mysqli_connect($db['server'], $db['user'], $db['password'], $db['database']);
    //stel karakterset in voor mysqli_real_escape_string
    mysqli_set_charset($link, 'latin1');
    //query om tabel te maken
    $sql = "SELECT
    `id`
    FROM `gebruikers`
    WHERE `id` = '" . mysqli_real_escape_string($link, $user_id) . "'
    AND `wachtwoord` = '" . mysqli_real_escape_string($link, $wachtwoord) . "'
    LIMIT 1";
    //voer query uit
    $result = mysqli_query($link, $sql);
    if (mysqli_num_rows($result) != 1) {
        //geef foutmelding en stop verder uitvoering van pagina.
        echo '<p>Ongeldige aanmelding. <a href="login.php">login</a>';
        exit;
    }
}
//cookie bevat ongeldige informatie
else {
    //geef foutmelding en stop verder uitvoering van pagina.
    echo '<p>Je bent niet aangemeld. <a href="login.php">login</a>';
    exit;
}
?>

Allereerst worden de gegevens uit het cookie gehaald en opgeslagen in $user_id en $wachtwoord. Vervolgens wordt gecontroleerd of het id uit het cookie numeriek is en de wachtwoord-hash uit het cookie een lengte heeft van 64 tekens. Als dat niet het geval is, omdat er geen cookie is of omdat er mee geknoeid is, weten we zeker dat er geen geldige login is en hoeven we ook niet in de database te kijken. Er volgt dan een foutmelding en een exit om de rest van de pagina niet te laten zien. Als deze twee fundamentele dingen wel correct zijn, wordt verbinding gemaakt met de MySQL database om de tabel gebruikers te raadplegen. Vergelijkbaar met het loginscript kijken we nu of er een rij is waarin het id en de wachtwoord-hash voorkomen. We hoeven het wachtwoord nu niet te hashen; het staat al gehasht in het cookie opgeslagen. Als er niet precies één rij als resultaatset is, is de gebruiker niet correct ingelogd en geven we ook nu een foutmelding en exit. Is er wel precies één rij in de resultaatset, dan doen we verder niets en wordt de pagina getoond waarin logincheck.inc.php is geïnclude. Let op: de include moet boven de pagina-inhoud staan om te voorkomen dat een niet-ingelogde bezoeker de inhoud kan lezen!

Wachtwoord wijzigen

Voor gebruikers kan het prettig zijn dat ze zelf hun wachtwoord kunnen wijzigen. Hiervoor maak je een formulier (dat uiteraard alleen voor ingelogde gebruikers toegankelijk is, waarvoor uiteraard logincheck.inc.php gebruikt kan worden), met daarin een veld om het oude wachtwoord in te vullen en tweemaal het nieuwe. Het is gebruikelijk om ook voor ingelogde gebruikers om het wachtwoord te vragen, om te voorkomen dat als niet uitgelogd wordt iemand anders die de computer gebruikt het wachtwoord kan wijzigen. Door twee keer om het nieuwe wachtwoord te vragen kan gecontroleerd worden dat geen typfouten zijn gemaakt. Het PHP-script controleert het oude wachtwoord net zoals het loginscript, maar gebruikt het gebruikers-id uit het cookie in plaats van de gebruikersnaam. Het script controleert ook of de nieuwe wachtwoorden gelijk zijn; als dat zo is wordt de tabelrij met het gegeven gebruikers-id bijgewerkt. Het bijwerken van een rij gaat met een UPDATE SQL-statement:

UPDATE table_name

    SET

        col_name = value,

        ...

    [WHERE where_condition [, ...]

    [ORDER BY col_name [DESC] [, ...]]

    [LIMIT [offset,] row_count]

Een UPDATE-query lijkt op een INSERT-query waarbij de naam van de bij te werken tabel wordt opgegeven en dan een lijst met kolomnamen en waarden die aan de kolom moeten worden toegekend. Zonder verdere voorwaarden worden alle rijen in de tabel bijgewerkt. Om alleen bepaalde rijen bij te werken kunnen net zoals in een SELECT-query voorwaarden worden opgegeven via WHERE. Eventueel mogen ook nog ORDER BY en LIMIT worden opgegeven, maar in de praktijk is daar niet zo vaak een nuttige toepassing voor. Zie ook de uitleg van SELECT. De SQL-query om een wachtwoord bij te werken ziet er dan ongeveer als volgt uit:

UPDATE `gebruikers`
    SET
        `wachtwoord` = 'HASH_VAN_NIEUW_WACHTWOORD'
    WHERE `id` = 'ID_UIT_COOKIE'

Hiermee kunnen we dan het script wijzigwachtwoord.php maken:

<?php
//controleer of gebruiker is ingelogd
include('logincheck.inc.php');
//dit geeft tegelijkertijd het gebruikers-id in $user_id

//controleer of formulier verzonden
if (!empty($_POST)) {
    //controleer of nieuwe wachtwoorden gelijk
    if ($_POST['new_password1'] == $_POST['new_password2']) {
        //include database gegevens
        include('config.inc.php');
        //verbind met server
        $link = mysqli_connect($db['server'], $db['user'], $db['password'], $db['database']);
        //stel karakterset in voor mysqli_real_escape_string
        mysqli_set_charset($link, 'latin1');
        //bereken hash van oud wachtwoord
        $wachtwoord = hash('sha256', $_POST['old_password']);
        //query om rij te selecteren
        $sql = "SELECT
        `id`
        FROM `gebruikers`
        WHERE `id` = '" . mysqli_real_escape_string($link, $user_id) . "'
        AND `wachtwoord` = '" . mysqli_real_escape_string($link, $wachtwoord) . "'
        LIMIT 1";
        //voer query uit
        $result = mysqli_query($link, $sql);
        if (mysqli_num_rows($result) != 1) {
            //oud wachtwoord niet correct
            $oud_wachtwoord_fout = TRUE;
        }
        else {
            //wachtwoord correct, zet nieuw wachtwoord
            //bereken hash van nieuw wachtwoord
            $wachtwoord = hash('sha256', $_POST['new_password1']);
            //query om rij aan te passen
            $sql = "UPDATE `gebruikers`
            SET `wachtwoord` = '" . mysqli_real_escape_string($link, $wachtwoord) . "'
            WHERE `id` = '" . mysqli_real_escape_string($link, $user_id) . "'";
            //voer query uit
            $wachtwoord_gewijzigd = mysqli_query($link, $sql);
            //plaats cookie opnieuw om te voorkomen dat gebruiker uitgelogd is
            if ($wachtwoord_gewijzigd === TRUE) {
                $cookie['id'] = $user_id;
                $cookie['password'] = $wachtwoord;
                //zet cookie
                setcookie('login', serialize($cookie), time() + 60*60*24*7*2, '/');
            }
        }
    }
    else {
        //nieuwe wachtwoorden niet gelijk
        $nieuw_wachtwoord_fout = TRUE;
    }
}

?>
<!DOCTYPE HTML>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Wijzig wachtwoord</title>
</head>
<body>

<h1>Wijzig wachtwoord</h1>

<?php
if ($nieuw_wachtwoord_fout === TRUE) {
    echo '<p class="error">De ingevulde nieuwe wachtwoorden zijn niet gelijk.</p>';
}
if ($oud_wachtwoord_fout === TRUE) {
    echo '<p class="error">De oude wachtwoord is niet juist.</p>';
}
if ($wachtwoord_gewijzigd === TRUE) {
    echo '<p class="succes">Wachtwoord gewijzigd!</p>';
}
?>

<form method="post">
<table>
<tr><td>Oud wachtwoord:</td><td><input type="password" name="old_password"></td></tr>
<tr><td>Nieuw wachtwoord:</td><td><input type="password" name="new_password1"></td></tr>
<tr><td>Herhaal wachtwoord:</td><td><input type="password" name="new_password2"></td></tr>
<tr><td></td><td><input type="submit" value="Wijzig wachtwoord"></td></tr>
</table>
</form>

</body>
</html>

Volledige code

Klik hier om de volledige bestanden van het loginscript dat in dit hoofdstuk is besproken te downloaden.

Oefening: Wachtwoord vergeten

Als gebruikers hun wachtwoord zijn vergeten, zou het fijn zijn als de dat zelf kunnen oplossen zonder jou als beheerder van de website daarmee te moeten lastig vallen. Er is een kolom met e-mailadressen in de tabel gebruikers dus die kunnen we gebruiken om een nieuw wachtwoord te sturen. Omdat we alleen de hash van wachtwoorden bewaren, kunnen we het oorspronkelijke wachtwoord niet per mail opsturen. We moeten dus een nieuw wachtwoord genereren.

Opdracht

Maak een script om gebruikers een nieuw wachtwoord te laten opvragen. Gebruik hiervoor de functie mail() uit hoofdstuk 9. Gebruik de functie mt_rand() uit hoofdstuk 17 om een nieuw wachtwoord van 8 tekens te genereren. Het wachtwoord kan bestaan uit alle hoofd- en kleine letters en alle cijfers.

Uitwerking opdracht

Uitwerking

wachtwoordvergeten.php

<?php 
//functie om nieuw wachtwoord te genereren
function genereer_wachtwoord($len) {
    //alle tekens die in het wachtwoord mogen voorkomen
    $tekens = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
    //begin met een leeg wachtwoord
    $wachtwoord = '';
    //van nul tot de opgegeven lengte doe
    for ($i = 0; $i < $len; $i++) {
        //bepaal een willekeurig getal van nul tot het aantal karakters in $tekens
        $start = mt_rand(0, strlen($tekens) - 1);
        //neem het zoveelste karakter uit $tekens en voeg dit toe aan wachtwoord
        $wachtwoord = $wachtwoord . substr($tekens, $start, 1);
    }
    return $wachtwoord;
}

//controleer of formulier verzonden
if (!empty($_POST)) {
    //include database gegevens
    include('config.inc.php');
    //verbind met server
    $link = mysqli_connect($db['server'], $db['user'], $db['password'], $db['database']);
    //stel karakterset in voor mysqli_real_escape_string
    mysqli_set_charset($link, 'latin1');
    //kijk of gebruikersnaam bestaat en haal id en emailadres op
    $sql = "SELECT
    `id`, `email`
    FROM `gebruikers`
    WHERE `gebruikersnaam` = '" . mysqli_real_escape_string($link, $_POST['gebruikersnaam']) . "'
    LIMIT 1";
    //voer query uit
    $result = mysqli_query($link, $sql);
    if (mysqli_num_rows($result) != 1) {
        //er is geen gebruiker
        $gebruikersnaam_fout = TRUE;
    }
    else {
        //er is wel een gebruiker
        //haal gegevens van database op
        $data = mysqli_fetch_assoc($result);
        //genereer nieuw wachtwoord
        $wachtwoord = genereer_wachtwoord(8);
        //stel afzender en bericht op
        $afzender = 'From: ' . $_SERVER['SERVER_NAME'] . ' <noreply@' . $_SERVER['SERVER_NAME'] . '>';
        $onderwerp = 'Nieuw wachtwoord';
        $bericht = 'Je nieuwe wachtwoord is: 
        ' . $wachtwoord;
        //verzend bericht
        $verzonden = mail($data['email'], $onderwerp, $bericht, $afzender);
        //als correct verzonden, werk database bij
        if ($verzonden === TRUE) {
            //genereer hash
            $wachtwoord = hash('sha256', $wachtwoord);
            //zet hash van nieuw wachtwoord in database
            $sql = "UPDATE `gebruikers`
            SET `wachtwoord` = '" . mysqli_real_escape_string($link, $wachtwoord) . "'
            WHERE `id` = '" . mysqli_real_escape_string($link, $data['id']) . "'";
            mysqli_query($link, $sql);
        }
    }
}

?>
<!DOCTYPE HTML>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Wachtwoord vergeten</title>
</head>
<body>

<h1>Wachtwoord vergeten</h1>

<?php
if ($gebruikersnaam_fout === TRUE) {
    echo '<p class="error">De ingevulde gebruikersnaam bestaat niet.</p>';
}
if ($verzonden === TRUE) {
    echo '<p class="succes">Er is een nieuw wachtwoord naar het geregistreerde e-mailadres gestuurd.</p>';
}
?>

<form method="post">
<table>
<tr><td>Gebruikersnaam:</td><td><input type="text" name="gebruikersnaam"></td></tr>
<tr><td></td><td><input type="submit" value="Stuur nieuw wachtwoord"></td></tr>
</table>
</form>

</body>
</html>

Contact | Over PHPBoek | Doneren | Copyright en aansprakelijkheid | Privacybeleid