Noot vooraf: sommige passages zijn deels of in zijn geheel overgenomen (vertaald) van de PHP documentatie site. Dus mochten deze je bekend voorkomen, dan kan dit kloppen.

• 1. Introductie
• 2. Is dit van invloed op mijn code of applicaties?
• 3. Samenvatting
• 4. Alternatieven en motivatie
• 5. Het belang van (bewustwording van) character sets
• 6. PDO
• 7. MySQLi
  • 7.1 Procedureel versus object georiënteerd
  • 7.2 Het maken van een database-connectie en het selecteren van de te gebruiken database
  • 7.3 Het instellen van een character set
  • 7.4 Het uitvoeren van een query zonder resultaten
  • 7.5 Het uitvoeren van een query met (het ophalen van) resultaten
  • 7.6 Veilig omgaan met queries met variabele onderdelen
    • 7.6.1 Het filteren van invoer (in MySQLi)
    • 7.6.2 Het escapen van uitvoer (in MySQLi)
    • 7.6.3 addslashes() en magic_quotes_gpc
  • 7.7 Prepared statements
    • 7.7.1 Het prepar(er)en van een prepared statement
    • 7.7.2 Het koppelen van variabelen aan de placeholders
    • 7.7.3 Het toekennen van waarden aan de placeholder-variabelen en het uitvoeren van het prepared statement
    • 7.7.4 Het afsluiten van een prepared statement
    • 7.7.5 Prepared statements met resultaten
    • 7.7.6 Resultaten ophalen met behulp van result parameters
    • 7.7.7 Resultaten ophalen met behulp van mysqli_result functies en methoden
    • 7.7.8 Ovewegingen bij het (kiezen voor het) gebruik van prepared statements
  • 7.8 Stored procedures
  • 7.9 Multiple statements
  • 7.10 API ondersteuning voor transacties
    • 7.10.1 Wat is een transactie
    • 7.10.2 Database ondersteuning voor (het gebruik van) transacties
    • 7.10.3 Het verloop van een transactie in het algemeen
    • 7.10.4 Het starten van een transactie in MySQLi
    • 7.10.5 Het committen van de transactie in MySQLi
    • 7.10.6 Het terugdraaien van de transactie in MySQLi
    • 7.10.7 Geneste transacties
    • 7.10.8 Het bundelen van transactie-operaties
    • 7.10.9 Voorbeeld van gebruik
  • 7.11 Het organiseren van MySQLi functies in een wrapper class of DAAL
• 8. Storage engines en character sets
• 9. Tot slot
• 10. Bronnen en meer informatie

De standaard MySQL extensie verdwijnt, wat nu?

1. Introductie

Zoals je wellicht weet zal de MySQL-extensie van PHP vanaf versie 5.5.0 deprecated worden. Dit houdt in dat vanaf dat moment deze functionaliteit afgeschreven is en mogelijk in een daarop volgende grote release geheel wordt verwijderd.

Met deze (voor)kennis is het dus verstandig om in ieder geval geen nieuwe code meer te schrijven met mysql_ functies, en mogelijk ook om alvast een inventarisatie te maken van bestaande code waarin MySQL-functionaliteit zit. Ook als je MySQL versie 4.1.3 of nieuwer is, word je aangeraden om over te stappen naar een alternatieve vorm om met je database te communiceren.

2. Is dit van invloed op mijn code of applicaties?

Waarschijnlijk niet direct, het zal nog wel een tijd duren voordat de MySQL-extensie echt niet meer ondersteund wordt (of geheel verdwenen is). Desalniettemin is het verstandig om vooruit te kijken en je vertrouwd te maken met de alternatieven. Vooral als jouw code een MySQL database gebruikt en je nog gebruik maakt van PHP functies die starten met mysql_.

Ook uit oogpunt van security kan het verstandig zijn op de hoogte te blijven van veranderingen in PHP-land. Zo kan door het verdwijnen van bepaalde features of het veranderen van standaard instellingen je applicatie "ineens" veiligheidslekken bevatten die uitgebuit kunnen worden. In deze tutorial zal veel nadruk op (database-gerelateerde) security gelegd worden.

3. Samenvatting

Als je het niet kunt opbrengen om hier helemaal doorheen te lezen, kun je gebruik maken van deze samenvatting van algemene vuistregels / pointers voor gebruik van MySQLi (want daar zal deze tutorial voornamelijk over gaan). Deze zijn deels gebaseerd op persoonlijke voorkeur maar zullen worden onderbouwd in de rest van dit relaas. Als je alle nitty gritty details wilt weten zul je hier toch doorheen moeten :).

4. Alternatieven en motivatie

In principe zijn er twee alternatieve wegen om te communiceren met een MySQL-database: PDO (PHP Data Objects) en MySQLi (MySQL Improved). Dit zijn de twee smaken waaruit je kunt kiezen als vervanger voor je MySQL-functionaliteit. Beide hebben voor- en nadelen. Als we de documentatie mogen geloven is de performance van alle drie de extensies ongeveer gelijk, maar je wordt nog steeds (met enige klem) aangeraden een van de twee nieuwere extensies te kiezen, te meer omdat deze ook een hoop nieuwe functionaliteit bevat in vergelijking met hun voorganger.

Een van de redenen die wordt genoemd voor het kiezen voor PDO is dat je je maar één API (een verzameling van functie-aanroepen en andere kreten) hoeft aan te leren voor gebruik in (mogelijk meerdere) databases, PDO levert namelijk een data-access abstraction layer die ondersteuning biedt aan meerdere databases. Dit houdt in dat, onafhankelijk van de database die je gebruikt, de code die je schrijft voor het uitvoeren van queries en het ophalen van resultaten hetzelfde is. PDO levert echter geen database abstraction, het herschrijft niet automagisch je MySQL-statements en emuleert ook geen ontbrekende (database-specifieke) features dus het biedt je geen volledige dekking. Het spaart je waarschijnlijk wel wat werk als je overschakelt van database, maar je kunt je afvragen hoe vaak je dat doet in de levensloop van een applicatie. PDO is beschikbaar vanaf PHP versie 5(.1).

Als je voor MySQLi kiest kun je deze op twee manieren gebruiken: procedureel of object-georiënteerd. Een van de mogelijke redenen voor het kiezen voor MySQLi is dat de procedurele variant veel lijkt op de functionaliteit van de oorspronkelijke MySQL-extensie, deze zijn nagenoeg hetzelfde. Dit kan het herschrijven van bestaande code makkelijk(er) maken. En als je voor je MySQL-extensie ook al een data-access abstraction layer had (oftewel, je gebruikte niet rechtsreeks mysql_ functies in je code, maar je had hiervoor een klasse of een aparte reeks functies) dan is dit enkel een kwestie van het aanpassen van de (gebruikte) implementatie van deze laag, de rest van je code kun je dan verder ongemoeid laten. Wel even alles testen natuurlijk ;-). MySQLi is beschikbaar vanaf PHP versie 5.

De oorspronkelijke MySQL-extensie is (was) eigenlijk alleen bedoeld voor MySQL versies ouder dan versie 4.1.3. Wanneer je MySQL versie 4.1.3 of nieuwer is dan word je eveneens (dringend) aangeraden om MySQLi (of PDO) te gebruiken. Daarnaast wordt er op dit moment niet meer actief ontwikkeld aan de MySQL-extensie, deze wordt enkel onderhouden. Tevens bieden MySQLi en PDO ondersteuning aan nieuwe (MySQL-)features (waarvan we er een aantal zullen behandelen).

Welke variant je kiest zal afhangen van persoonlijke voorkeur, toepassingsgebied et cetera. En zelfs als je deze keuze nu (nog) niet wilt maken loont het zeker de moeite je nu alvast te verdiepen in deze twee varianten zodat je dat niet hoeft te doen op het moment dat je moet kiezen.

5. Het belang van (bewustwording van) character sets

Een ander aspect waar je tot op heden mogelijk niet echt bij stil hebt gestaan is het gebruik van character sets. In alles wat je in PHP en MySQL doet (of zelfs in HTML, for that matter), wat meestal neerkomt op een vorm van het manipuleren en afdrukken van tekst, moet je je afvragen welke encoding deze tekst heeft. Alles zou in harmonie moeten zijn:

• de (optionele) Content-Type header() die je met PHP verstuurt
• de Content-Type meta-tag in je HTML-document
• de geselecteerde charset voor je database-connectie
• de character set (en collation) van je tabellen en tabel-kolommen
• last but not least: de character set waarmee je data is opgeslagen

Om vaak voorkomende problemen met de weergave van "exotische" karakters te voorkomen is er eigenlijk maar één goede manier: gebruik uitsluitend UTF-8. Indien je geen UTF-8 gebruikt, is het nog altijd een goed idee om expliciet aan te geven welke character set je dan wel gebruikt, want There Ain't No Such Thing As Plain Text.

In MySQL wordt aan UTF-8 gerefereerd via (onder andere) utf8, dit is dus geen typefout of wat dan ook.

6. PDO

Stijn heeft al een tutorial over PDO(_MYSQL) geschreven, het restant van deze tutorial zal daarom uitsluitend over MySQLi gaan.

7. MySQLi

7.1 Procedureel versus object georiënteerd

Zoals eerder aangegeven kun je MySQLi op twee verschillende manieren gebruiken. In de codefragmenten die volgen zullen voorbeelden van beide varianten worden gegeven. Misschien is dit ook een goed moment dat je jezelf vertrouwd maakt met de object georiënteerde variant. De voorbeelden zullen, waar van toepassing, ook de oorspronkelijke mysql_ functies bevatten, dit ter vergelijking voor een omzetting van oud naar nieuw.

In de voorbeelden wordt gebruik gemaakt van de volgende tabel:

CREATE TABLE config (
    id      INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    _key    VARCHAR(255) UNIQUE NOT NULL,
    _value  VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Let op: de voorbeelden in het vervolg van deze tutorial behandelen mogelijk tegelijkertijd meerdere varianten die hetzelfde doen. Zorg ervoor dat, als je deze voorbeelden zelf uitprobeert, je deze niet combineert want in sommige gevallen zullen deze dan niet werken. Houd te allen tijde één stijl aan (hetzij procedureel, hetzij object georiënteerd).

7.2 Het maken van een database-connectie en het selecteren van de te gebruiken database

In MySQL gebeurt dit in twee stappen, in MySQLi zijn deze samengevoegd.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

<?php
// mysql (deprecated vanaf PHP 5.5)
// maak verbinding
$con = mysql_connect($hostname, $username, $password);
if ($con === false) {
    die('connection failed: '.mysql_error());
}
// selecteer database
if (mysql_select_db($database, $con) === false) {
    die('database selection failed: '.mysql_error());
}

// mysqli variant #1 - procedureel
$con = mysqli_connect($hostname, $username, $password, $database);
if (mysqli_connect_errno($con)) {
    die('connection failed: '.mysqli_connect_error());
}

// mysqli variant #2 - object georiënteerd
$con = new mysqli($hostname, $username, $password, $database);
if ($con->connect_errno) {
    die('connection failed: '.$con->connect_error);
}
?>

Als jouw MySQL-server niet op de standaard poort (3306) luistert, dan moet je het poortnummer toevoegen aan je hostname, met een dubbele punt (:) tussen je hostname en het poortnummer.

Let op: mysqli_connect() is een alias. Het is waarschijnlijk een goede gewoonte om het gebruik van aliassen te vermijden. Daarnaast maakt MySQLi veelvuldig gebruik van objecten (in plaats van resources zoals in de oorspronkelijke MySQL-extensie gebeurt). Dit tezamen maakt het gebruik van de object georiënteerde variant eigenlijk de meest logische keuze, maar als je eerst alles (nagenoeg letterlijk) wilt overzetten van MySQL naar MySQLi kan de procedurele variant (initieel) uitkomst bieden.

7.3 Het instellen van een character set

Het instellen van een character set via een query (bijvoorbeeld via de query SET NAMES utf8) wordt afgeraden. Gebruik in plaats hiervan een set_charset() functie:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

<?php
// mysql (deprecated vanaf PHP 5.5)
// stel character set in (PHP 5.2.3 of hoger)
if (mysql_set_charset('utf8') === false) {
    die('failed setting charset');
}

// mysqli variant #1 - procedureel
// stel character set in (PHP 5.0.5 of hoger)
if (mysqli_set_charset($con, 'utf8') === false) {
    die('failed setting charset');
}

// mysqli variant #2 - object georiënteerd
// stel character set in (PHP 5.0.5 of hoger)
if ($con->set_charset('utf8') === false) {
    die('failed setting charset');
}
?>

Uiteraard moet de geselecteerde character set (op zijn minst) aansluiten bij de character sets van de gebruikte tabellen en tabel-kolommen.

7.4 Het uitvoeren van een query zonder resultaten

Een aantal queries (denk aan queries die beginnen met INSERT, UPDATE, DELETE, DROP etc.) leveren geen resultaten op in termen van resultaten die opgehaald kunnen worden met fetch-functies. Functies die je voor dit soort queries gebruikt zullen dan ook enkel een boolean retourneren die aangeeft of een query gelukt is (true) of om een of andere reden mislukt is (false). De standaard MySQL-extensie maakt geen onderscheid tussen deze verschillende typen queries en heeft hier één functie voor: mysql_query(). Om na te gaan hoeveel records zijn gewijzigd (of verwijderd) bij dit type query gebruik je de juiste affected_rows() functie.

1
2
3
4
5
6
7
8
9
10
11
12
13
14

<?php
// mysql (deprecated vanaf PHP 5.5)
// uitvoeren van query zonder resultaten
if (mysql_query("INSERT INTO config (_key, _value) VALUES
                ('first',   'one'),
                ('second',  'two'),
                ('third',   'three'),
                ('fourth',  'four')")) {
    echo 'affected rows: '.mysql_affected_rows();
} else {
    die('query failed');
}
// levert: affected rows: 4
?>

In MySQLi heb je een aantal mogelijkheden voor het uitvoeren van queries zonder of met resultaten. De voornaamste zijn mysqli_query() en mysqli_real_query(). Het verschil zit in de mogelijke waarden die geretourneerd kunnen worden door de verschillende functies.

mysqli_query() kan zowel een boolean retourneren (true of false) of een object van de klasse mysqli_result ingeval de query begint met SELECT, SHOW, DESCRIBE of EXPLAIN (dus bij queries die mogelijk wel resultaten kunnen opleveren). Let er op dat dit ook gebeurt als er geen resultaten zijn: de waarde die geretourneerd wordt door de functie is nog steeds een object van het type mysql_result, maar deze "bevat" geen resultaten.

mysqli_real_query() retourneert altijd enkel een boolean. Dit maakt mysqli_real_query() mogelijk geschikter voor queries die geen resultaten opleveren, maar beide zijn mogelijk. Daarnaast kun je ook nog kiezen voor de procedurele of de objectgeoriënteerde variant.

Procedureel:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

<?php
// mysqli procedureel - variant #1
if (mysqli_query($con, "INSERT INTO config (_key, _value) VALUES
                        ('first',   'one'),
                        ('second',  'two')")) {
    echo 'affected rows: '.mysqli_affected_rows($con);
} else {
    die('query failed');
}
// levert: affected rows: 2

// mysqli procedureel - variant #2
if (mysqli_real_query($con, "INSERT INTO config (_key, _value) VALUES
                        ('third',   'three'),
                        ('fourth',  'four')")) {
    echo 'affected rows: '.mysqli_affected_rows($con);
} else {
    die('query failed');
}
// levert: affected rows: 2
?>

Object georiënteerd:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

<?php
// mysqli object georiënteerd - variant #1
if ($con->query("INSERT INTO config (_key, _value) VALUES
                        ('first',   'one'),
                        ('second',  'two')")) {
    echo 'affected rows: '.$con->affected_rows;
} else {
    die('query failed');
}
// levert: affected rows: 2

// mysqli object georiënteerd - variant #2
if ($con->real_query("INSERT INTO config (_key, _value) VALUES
                        ('third',   'three'),
                        ('fourth',  'four')")) {
    echo 'affected rows: '.$con->affected_rows;
} else {
    die('query failed');
}
// levert: affected rows: 2
?>

7.5 Het uitvoeren van een query met (het ophalen van) resultaten

Wanneer de query begint met SELECT, SHOW, DESCRIBE of EXPLAIN retourneert een aanroep van mysql_query() een resource wanneer de query slaagt, en anders false. Als de resource toegekend wordt aan een variabele (of nog op een andere manier opvraagbaar is) kun je vervolgens door de resultaten heenlopen. Voordat het script eindigt kun je tevens de resultaten tussentijds vrijgeven. Dit is met name verstandig als er een heleboel queries worden uitgevoerd of wanneer de query-resultaten uitgebreid zijn, of beide. Het opvragen van het aantal resultaatrijen doe je met de juiste num_rows() functie.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

<?php
// mysql (deprecated vanaf PHP 5.5)
// query uitvoeren
$res = mysql_query('SELECT * FROM config ORDER BY id');
if ($res === false) {
    die('query failed: '.mysql_error());
} else {
    // afdrukken van aantal resultaten
    echo mysql_num_rows($res).' result(s):';

    // ophalen van resultaatrijen, gebruik je favoriete fetch functie
    while ($row = mysql_fetch_assoc($res)) {
        echo $row['id'].'. '.$row['_key'].': '.$row['_value'].'';
    }

    // resultaat vrijgeven
    mysql_free_result($res);
}
?>

Dit levert:

4 result(s):
1. first: one
2. second: two
3. third: three
4. fourth: four

Let op: op alle data die afgedrukt wordt zou eigenlijk altijd een vorm van output escaping moeten worden toegepast. Dit wordt op veel plaatsen in deze tutorial bewust niet gedaan omdat dat de voorbeelden complexer zou maken dan nodig is.
Nota bene: dus ook je mysql_error() zou geescaped moeten worden (en mogelijk is het beter om op een andere manier MySQL-fouten af te handelen), deze kan namelijk user input bevatten!

In MySQLi heb je weer een aantal mogelijkheden, al zijn de procedurele varianten eigenlijk ook (deels) object georiënteerd, immers mysqli_query() retourneert een object van het type mysqli_result.

Wanneer je mysqli_real_query() gebruikt, kun je $con (dat in feite ook een object is) gebruiken om je resource-object op te halen. Het is niet helemaal duidelijk of dit de bedoeling is, maar dit werkt. Officieel zou je gebruik moeten maken van ofwel mysqli_use_result() of mysqli_store_result().

Om het verschil tussen deze twee functies te begrijpen, moet er wat uitleg gegeven worden over hoe PHP in het algemeen omgaat met het ophalen van resultaten uit een database. Wanneer er een query is uitgevoerd kunnen de resultaten op twee manieren worden opgehaald uit de database:

1. Alle resultaten worden in één keer opgehaald en in een buffer opgeslagen, vervolgens kan de buffer doorlopen worden voor het ophalen van query-resultaten (door middel van je favoriete fetch functie). De MySQL-server wordt in dit geval minder belast, want deze hoeft na uitvoering van de query zelf niets meer te onthouden. Zo'n buffer heet een buffered result set. Een ander voordeel is dat je "vrij" door zo'n result set heen kunt wandelen (met een data_seek() functie) en de totale grootte kunt opvragen (met een num_rows() functie).
2. Bij de tweede manier wordt het resultaat rij voor rij opgehaald uit de database. Hierbij worden resultaten dus niet buiten de MySQL-server gebuffered. Dit soort resultaten worden ook wel unbuffered result sets genoemd. Aan deze manier van resultaten ophalen kleven een aantal nadelen. Zo wordt de MySQL-server bezet gehouden met de resultaten van de laatste query. Daarnaast weet je niet wat de totale omvang is van alle resultaten van de uitgevoerde query en kun je ook niet vrij navigeren door deze resultaten, je hebt deze immers nog niet opgehaald.

Nu terug naar de eerdergenoemde functies. Wanneer je een use_result() functie gebruikt, dan is dit hetzelfde als het uitvoeren van een query waarbij je het resultaat niet buffert. Als je meerdere keren achter elkaar op deze manier een query probeert uit te voeren, zonder hierbij het resultaat van de huidige query (die zich nog op de MySQL-server bevindt) vrij te geven, krijg je een Commands out of sync foutmelding van MySQL. Hierbij lijkt het niet eens uit te maken of de queries plaatsvinden op gerelateerde of dezelfde tabel(len) of andere, compleet ongerelateerde tabellen. Een manier voor het achter elkaar uitvoeren van meerdere queries met niet gebufferde result set is door gebruikmaking van een multi_query() functie (hiermee voer je meerderere MySQL-statements tegelijkertijd uit) waarover later meer.

Indien je een store_result() functie gebruikt dan is dit hetzelfde als het uitvoeren van een query waarbij het resultaat (in een keer opgehaald en) gebufferd wordt. In dat geval kun je wel meerdere queries achter elkaar uitvoeren zonder de verplichting om tussentijds de resultaten vrij te geven.

Het bufferen van result sets vindt ook (standaard) plaats als je gebruik maakt van mysqli_query() - de derde parameter (in de procedurele variant, dit is de tweede parameter in de object georiënteerde variant) geeft door middel van een constante aan hoe de result set behandeld moet worden: MYSQLI_USE_RESULT versus MYSQLI_STORE_RESULT (default).

Normaal zul je hier niet snel tegenaan lopen en (onbewust) gebruik maken van de gebufferde variant in je queries, maar nu weet je dus ook (globaal) wat dit inhoudt. Tenzij je een reden hebt om niet het hele resultaat te bufferen word je aangeraden om store_result() of equivalent te gebruiken.

Gebruik je het connectie-object $con voor het ophalen van resultaten, dan gaat het ophalen van meerdere result sets zonder het tussentijds vrijgeven ervan goed. Hiermee lijkt deze variant het gedrag van mysqli_store_result() (of MYSQLI_STORE_RESULT) te volgen.

Het onderstaande lijstje bevat een aantal varianten die je waarschijnlijk het minste problemen zal opleveren bij gebruik. In de procedurele variant moet je mysqli_ in gedachten toevoegen aan de functies:

• gebruik real_query() in combinatie met $con voor het ophalen van een result set, of
• gebruik real_query() in combinatie met store_result(), of
• gebruik simpelweg query() (heeft MYSQLI_STORE_RESULT als default "resultaat modus")

Procedureel, alle varianten leveren dezelfde uitvoer als de bovenstaande MySQL-variant.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

<?php
// mysqli (semi) procedureel - variant #1
if (mysqli_real_query($con, 'SELECT * FROM config ORDER BY id')) {
    $res = new mysqli_result($con);

    // afdrukken van aantal resultaten
    echo mysqli_num_rows($res).' result(s):';

    // ophalen van resultaatrijen, gebruik je favoriete fetch functie
    while ($row = mysqli_fetch_assoc($res)) {
        echo $row['id'].'. '.$row['_key'].': '.$row['_value'].'';
    }

    // resultaat vrijgeven
    mysqli_free_result($res);
} else {
    die('query failed: '.mysqli_error($con));
}

// mysqli procedureel - variant #2
if (mysqli_real_query($con, 'SELECT * FROM config ORDER BY id')) {
    // initialiseer modus
    // hierbij is het toegestaan tegelijkertijd meerdere result sets te gebruiken
    $res = mysqli_store_result($con);

    // afdrukken van aantal resultaten
    echo mysqli_num_rows($res).' result(s):';

    // ophalen van resultaatrijen, gebruik je favoriete fetch functie
    while ($row = mysqli_fetch_assoc($res)) {
        echo $row['id'].'. '.$row['_key'].': '.$row['_value'].'';
    }

    // resultaat vrijgeven
    mysqli_free_result($res);
} else {
    die('query failed: '.mysqli_error($con));
}

// mysqli procedureel - variant #3
$res = mysqli_query($con, 'SELECT * FROM config ORDER BY id');
if ($res === false) {
    die('query failed: '.mysqli_error($con));
} else {
    // afdrukken van aantal resultaten
    echo mysqli_num_rows($res).' result(s):';

    // ophalen van resultaatrijen, gebruik je favoriete fetch functie
    while ($row = mysqli_fetch_assoc($res)) {
        echo $row['id'].'. '.$row['_key'].': '.$row['_value'].'';
    }

    // resultaat vrijgeven
    mysqli_free_result($res);
}
?>

Object georiënteerd, alle varianten leveren dezelfde uitvoer als de bovenstaande MySQL-variant.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

<?php
// mysqli object georiënteerd - variant #1
if ($con->real_query('SELECT * FROM config ORDER BY id')) {
    $res = new mysqli_result($con); // of new MySQLi_Result($con)

    // afdrukken van aantal resultaten
    echo $res->num_rows.' result(s):';

    // ophalen van resultaatrijen, gebruik je favoriete fetch functie
    while ($row = $res->fetch_assoc()) {
        echo $row['id'].'. '.$row['_key'].': '.$row['_value'].'';
    }

    // resultaat vrijgeven
    $res->free();
} else {
    die('query failed: '.$con->error);
}

// mysqli object georiënteerd - variant #2
if ($con->real_query('SELECT * FROM  config ORDER BY id')) {
    $res = $con->store_result();

    // afdrukken van aantal resultaten
    echo $res->num_rows.' result(s):';

    // ophalen van resultaatrijen, gebruik je favoriete fetch functie
    while ($row = $res->fetch_assoc()) {
        echo $row['id'].'. '.$row['_key'].': '.$row['_value'].'';
    }

    // resultaat vrijgeven
    $res->free();
} else {
    die('query failed: '.$con->error);
}

// mysqli object georiënteerd - variant #3
$res = $con->query('SELECT * FROM config ORDER BY id');
if ($res === false) {
    die('query failed: '.$con->error);
} else {
    // afdrukken van aantal resultaten
    echo $res->num_rows.' result(s):';

    // ophalen van resultaatrijen, gebruik je favoriete fetch functie
    while ($row = $res->fetch_assoc()) {
        echo $row['id'].'. '.$row['_key'].': '.$row['_value'].'';
    }

    // resultaat vrijgeven
    $res->free();
}
?>

7.6 Veilig omgaan met queries met variabele onderdelen

Vaak worden SQL-queries dynamisch samengesteld. Deze bestaan dan uit een vast deel en uit een of meer variabele stukken en/of waarden. Denk hierbij aan queries met (door gebruikers ingevoerde) termen in zoekopdrachten, paginerings- of artikelsystemen die specifieke informatie tonen op grond van de URL et cetera. Omdat je hiermee in feite tekst invoegt in de string die SQL-code bevat is het héél belangrijk dat deze invoer ontdaan wordt van enige speciale betekenis die deze binnen SQL kan hebben omdat anders mogelijk de werking van de query gemanipuleerd kan worden. In het ergste geval wordt je invoer, en daarmee je SQL-query, zodanig gemanipuleerd dat je ongewenst gevoelige informatie in je database blootstelt aan kwaadwillenden. Dit proces van het manipuleren van SQL-code wordt ook wel SQL injection genoemd.

Net zoals in HTML en PHP waarbij invoer van gebruikers onderworpen moet worden aan een vorm van output escaping om de uitvoer te ontdoen van speciale betekenis in HTML, moet dit ook in (My)SQL gebeuren. Daarnaast zou je ook de invoer moeten inspecteren, immers, indien je in een query-parameter een getal verwacht dan zal een niet-numerieke invoer hoogstwaarschijnlijk niets opleveren - je hoeft dan niet eens te proberen de query uit te voeren. Dit proces van het controleren van invoer wordt ook wel input filtering genoemd. Dit levert tezamen het volgende paradigma op, wat eigenlijk overal toegepast zou moeten worden, indien mogelijk en van toepassing:

filter input, escape output

7.6.1 Het filteren van invoer (in MySQLi)

In sommige voorbeelden ben ik het gebruik van sprintf() tegengekomen. De eerste parameter van deze functie bevat een te formatteren string en alle volgende parameters bevatten de argumenten die ingevoegd moeten worden in de te formatteren string. Het resultaat wordt tevens als string geretourneerd. Deze functie zou je dus kunnen gebruiken als middel voor het bouwen van je MySQL-statement. Tevens zou deze invoer ge-escaped moeten worden. Dit doe je met mysqli_real_escape_string() (zie de volgende paragraaf).

Stel bijvoorbeeld dat je een script hebt voor het opvragen van een configuratie-variabele uit de voorbeeld database-tabel op grond van id (die uit je URL komt), je zou dan je MySQL-statement als volgt kunnen opbouwen:

1
2
3
4
5
6

<?php
if (isset($_GET['id'])) {
    $query = sprintf('SELECT * FROM config WHERE id = %d', mysqli_real_escape_string($con, $_GET['id']));
    // doe iets met $query
}
?>

Dit levert met een aanroep van ?id=12:

SELECT * FROM config WHERE id = 12

Echter, met een aanroep van ?id=aap wordt dit:

SELECT * FROM config WHERE id = 0

Oftewel, de invoer wordt getypecast naar een decimaal getal (integer). sprintf() kan dus mogelijk waarden manipuleren zodat het past in het formaat waarin je een parameter wilt gieten. Maar dat houdt dus in dat de oorspronkelijke invoer niet noodzakelijkerwijs het juiste formaat had! Dit maakt (wat mij betreft) sprintf() ongeschikt om queries mee te bouwen.

De beste manier lijkt vooralsnog een expliciete controle / initialisatie van parameter-waarden. Een manier, mits je PHP versie nieuw genoeg is (vanaf 5.2.0) is het gebruiken van een filter_ functie, bijvoorbeeld filter_var():

1
2
3
4
5
6
7
8
9
10
11
12
13

<?php
if (isset($_GET['id'])) {
    // controleer of de invoer een geheel getal is
    $id = filter_var($_GET['id'], FILTER_VALIDATE_INT);
    if ($id === false) {
        // foutafhandeling
        // ...
    } else {
        // $id is een (positief of negatief) geheel getal van het type int(eger)
        // ...
    }
}
?>

Afhankelijk van de waarden die je aan deze functie meegeeft kun je verschillende waarden terugkrijgen. Het bovenstaande fragment retourneert de querystring waarde als dit een getal is, en anders false. Daarnaast lijken de filter_ functies de fijne eigenschap te hebben dat deze het type van de variabele veranderen indien het filteren slaagt, oftewel $id is een echte integer (ondanks het feit dat $_GET['id'] een string is, zij het met een numerieke waarde).

Let er wel op dat de bovenstaande code ook invoer gelijk aan of kleiner dan 0 accepteert. Waarschijnlijk loont het dus ook de moeite om een geldig domein van waarden te controleren. En als je het controleren of de variabele bestaat en het filteren ervan wilt samenvoegen dan kan dat (al is dit uit het oogpunt van separation of concerns (het controleren op het al dan niet bestaan van een variabele en het controleren van het type van deze variabele) discutabel). filter_input() retourneert null als een variabele niet bestaat:

1
2
3
4
5
6
7
8
9
10
11

<?php
// controleer of de invoer een geheel getal is
$id =  filter_input(INPUT_GET, 'id', FILTER_VALIDATE_INT);
if ($id === false || $id === null) {
    // foutafhandeling
    // ...
} else {
    // $id is een (positief of negatief) geheel getal van het type int(eger)
    // ...
}
?>

Omdat je waarschijnlijk vaak een soortgelijke controle als de bovenstaande zult maken, kun je overwegen om voor dit soort controles aparte (shorthand) functies of zelfs methodes van een filter-klasse te maken.

Wanneer je PHP versie niet nieuw genoeg is voor het gebruik van filter_ functies (ouder dan 5.2.0), zou je voor de controle op een numerieke waarde is_numeric() kunnen overwegen, maar deze accepteert ook floats, octale en hexadecimale getallen. Een ander alternatief is een reguliere expressie (die je weer in een functie of class method kunt stoppen). is_int() werkt niet, want deze controleert het type, en alles wat uit $_GET en $_POST komt is van het type string.

Om onderscheid te maken tussen variabelen die gecontroleerd zijn en variabelen die nog gecontroleerd moeten worden kan het een idee zijn om, zodra ze gecontroleerd zijn, de waarde toe te kennen aan een nieuwe variabele. Dit om het onderscheid expliciet vast te leggen. Zorg er ook voor dat deze variabele altijd een waarde heeft, bijvoorbeeld door een initialisatie vooraf.

1
2
3
4
5
6
7
8
9
10
11
12
13

<?php
// retourneert een boolean die aangeeft of $in een positief geheel getal is (groter dan 0)
function is_index($in) {
    return preg_match('#^[1-9]+[0-9]*$#', $in);
}

// initialisatie
$id = false;
if (isset($_GET['id']) && is_index($_GET['id'])) {
    $id = $_GET['id'];
}
// nu is $id ofwel een positief geheel getal (groter dan 0) of false
?>

Voor andere parameters die je wilt filteren, bijvoorbeeld om te controleren of deze aan een specifiek patroon voldoen zoals een postcode of e-mailadres, kun je soortgelijke functies gebruiken of zelf bouwen. Het loont de moeite om eerst na te gaan of een filter_ functie hier een filter voor heeft, mits je PHP versie nieuw genoeg is (5.2.0 en later).

7.6.2 Het escapen van uitvoer (in MySQLi)

Naast het controleren van de invoer om te zien of deze aan bepaalde voorwaarden voldoet (input escaping), moet deze invoer vervolgens ook ontdaan worden van enige speciale betekenis binnen (My)SQL (output filtering). Dit doen we door middel van de real_escape_string() functies.

Let op: voor de correcte werking van de real_escape_string() functies is het van cruciaal belang dat de juiste character set is geselecteerd met behulp van een van de set_charset() functies.

Het makkelijkste is gewoon om altijd input die ingevoegd wordt in je MySQL-statement te escapen met deze functie. In dat geval hoef je namelijk niet elke keer na te denken of dit nu wel of niet nodig is met het risico dat je dit een keer vergeet of besluit dat dit niet nodig is en later blijkt dat dat een onverstandige keuze was :).

Wat ik geregeld zie is dat er in queries quotes om numerieke waarden worden gezet. Dit dient niet echt een doel en draagt ook nauwelijks bij aan veiligheid. Daarnaast is het gewoon fout. Controleer of de in te voegen parameters voldoen aan het formaat van een type en gebruik deze vervolgens als zodanig!

Een verkeerd gebruik van quotes om numerieke parameters in queries kan voor fouten zorgen. Strings in MySQL volgen namelijk een andere sortering (tekstueeel of lexicografisch) dan getallen (numeriek), probeer het volgende maar eens:

mysql> SELECT 312 < 41;
+----------+
| 312 < 41 |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

Versus:

mysql> SELECT '312' < '41';
+--------------+
| '312' < '41' |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

Oftewel: 312 komt numeriek niet voor 41, maar '312' komt alfabetisch (lexicografisch) wel voor '41'. Als je dus nummers als tekst behandelt in de argumenten van je query kan dit dus mogelijk resultaten opleveren waar je deze niet verwacht, of geen resultaten opleveren waar je deze wel verwacht. Ik hoop dat hiermee het onjuist gebruik van quotes voorgoed kan worden uitgebannen :).

Een veilige query met een dynamisch element zou er in de originele MySQL-variant als volgt uit zien:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

<?php
// hulpfunctie
// retourneert een boolean die aangeeft of $in een positief geheel getal is (groter dan 0)
function is_index($in) {
    return preg_match('#^[1-9]+[0-9]*$#', $in);
}

// id ophalen uit URL
$id = false;
if (isset($_GET['id']) && is_index($_GET['id'])) {
    $id = $_GET['id'];
}
// nu is $id ofwel een positief geheel getal (groter dan 0) of false

if ($id === false) {
    // foutafhandeling, of toon een overzicht van alle configuratie-variabelen
    // met links naar de details van een specifieke variabele via ?id=X
    // ...
} else {
    $res = mysql_query('SELECT * FROM config WHERE id = '.mysql_real_escape_string($id));
    // deze query heeft altijd maximaal één resultaat
    if (mysql_num_rows($res)) {
        $row = mysql_fetch_assoc($res);
        // doe vervolgens iets met $row
        // ...
    } else {
        // toon een mededeling dat er geen resultaten gevonden zijn
        // ...
    }
    mysql_free_result($res);
}
?>

De procedurele MySQLi-variant ziet er nagenoeg hetzelfde uit. Uit onderstaand en vorige voorbeelden valt af te leiden hoe je dit aanpakt indien je real_query() gebruikt, het MySQL-statement blijft precies hetzelfde.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

<?php
// hulpfunctie
// retourneert een boolean die aangeeft of $in een positief geheel getal is (groter dan 0)
function is_index($in) {
    return preg_match('#^[1-9]+[0-9]*$#', $in);
}

// id ophalen uit URL
$id = false;
if (isset($_GET['id']) && is_index($_GET['id'])) {
    $id = $_GET['id'];
}
// nu is $id ofwel een positief geheel getal (groter dan 0) of false

if ($id === false) {
    // foutafhandeling, of toon een overzicht van alle configuratie-variabelen
    // ...
} else {
    $res = mysqli_query($con, 'SELECT *
                                FROM config
                                WHERE id = '.mysqli_real_escape_string($con, $id));
    // deze query heeft altijd maximaal één resultaat
    if (mysqli_num_rows($res)) {
        $row = mysqli_fetch_assoc($res);
        // doe vervolgens iets met $row
        // ...
    } else {
        // toon een mededeling dat er geen resultaten gevonden zijn
        // ...
    }
    mysqli_free_result($res);
}
?>

Verder is het prima toegestaan om een of meer spaties en regelovergangen te introduceren in je query. Dit kan de leesbaarheid ten goede komen. Je zou zelfs kunnen overwegen om de query op te bouwen in een apart string:

1
2
3
4
5
6
7
8

<?php
// ...
$query = 'SELECT *
            FROM config
            WHERE id = '.mysqli_real_escape_string($con, $id);
$res = mysqli_query($con, $query);
// ...
?>

Zo ook de object georiënteerde variant:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

<?php
// hulpfunctie
// retourneert een boolean die aangeeft of $in een positief geheel getal is (groter dan 0)
function is_index($in) {
    return preg_match('#^[1-9]+[0-9]*$#', $in);
}

// id ophalen uit URL
$id = false;
if (isset($_GET['id']) && is_index($_GET['id'])) {
    $id = $_GET['id'];
}
// nu is $id ofwel een positief geheel getal (groter dan 0) of false

if ($id === false) {
    // foutafhandeling, of toon een overzicht van alle configuratie-variabelen
    // ...
} else {
        $query = 'SELECT *
                    FROM config
                    WHERE id = '.$con->real_escape_string($id);
        $res = $con->query($query);

    // deze query heeft altijd maximaal één resultaat
    if ($res->num_rows) {
        $row = $res->fetch_assoc();
        // doe vervolgens iets met $row
        // ...
    } else {
        // toon een mededeling dat er geen resultaten gevonden zijn
        // ...
    }
    $res->free();
}
?>

7.6.3 addslashes() en magic_quotes_gpc

Ook leek er (nog steeds?) een discussie gaande te zijn dat addslashes() een alternatief zou zijn voor real_escape_string() functies. Dit is gewoon onjuist. In de eerste plaats omdat addslashes() is bedoeld voor tekst(strings), en de real_escape_string() functies specifiek voor queries. Daarnaast: deze functies zijn geen aliassen van elkaar, ze hebben verschillende implementaties en escapen ook verschillende karakters. Deze functies dienen elk een eigen -en verschillend- doel en zijn dus niet uitwisselbaar.

Tot voor kort stond in PHP magic_quotes_gpc standaard aan. Dit houdt kortweg in dat alle data in de GET, POST en COOKIE superglobal arrays die enkele quotes ('), dubbele quotes ("), backslashes (\) of NULL karakters bevatten automagisch werd voorzien van (nog) een backslash (\). De waarden van deze arrays werden dus op dezelfde manier geescaped zoals met addslashes() gebeurt. Dit was een functionaliteit die ontstaan was uit / gebruikt werd uit gemakszucht (en was naar verluid eigenlijk niet eens bedoeld voor security).

Er zijn een aantal redenen waarom het verstandig is om hier geen gebruik (meer) van te maken, deze zijn wellicht nog het beste verwoord in een user comment op PHP.net. En zelfs als je hier (tegen beter weten in) een voorstander van bent dan heb je pech, ook deze functionaliteit is vanaf PHP 5.3.0 deprecated en vanaf PHP 5.4.0 verwijderd.

Dit heeft ook weer een aantal serieuze gevolgen voor de (output en) werking van je PHP code en opgeslagen data in je MySQL database. Het beste is gewoon om magic_quotes_gpc expliciet uit te zetten zodat je hier niet meer afhankelijk van bent. Dit kan niet at runtime maar moet via .htaccess (ingeval je Apache gebruikt) of via je php.ini file.

Dit alles en meer staat beschreven op PHP.net in het hoofdstuk over Magic Quotes. Daar staat ook beschreven dat het uit oogpunt van portability een goed idee is om karakters te strippen als je op je host niet in staat bent om magic_quotes_gpc uit te zetten.

7.7 Prepared statements

Een alternatief voor het escapen van de variabele delen in een query en tevens een alternatieve manier om een query te bouwen (in MySQLi) is het prepared statement. Een prepared statement of parameterized statement is een soort van query sjabloon dat gebruikt kan worden voor het meerdere keren efficiënt uitvoeren van dezelfde query (het sjabloon) met verschillende parameterwaarden die gemarkeerd worden door placeholders.

Het uitvoeren van prepared statement valt in twee stappen uiteen:

1. het sjabloon wordt naar de database-server toegestuuurd (prepare fase)
2. er worden variabelen gekoppeld aan de parameter placeholders, aan deze variabelen worden vervolgens waarden toegekend en worden daarna naar de database-server gestuurd; tot slot wordt tezamen met het eerder verstuurde sjabloom het uiteindelijke MySQL-statement gebouwd en uitgevoerd ((bind and) execute fase)

Hier blijkt dus al min of meer uit dat deze methode, uit oogpunt van efficiëntie, pas een zekere meerwaarde heeft wanneer dezelfde query (met mogelijk verschillende waarden voor de parameters) meerdere keren wordt uitgevoerd. Vervolgens zou je dus ook kunnen stellen dat normale SELECT-queries minder geschikt zijn om als prepared statement uitgevoerd te worden. Tenzij er wellicht een SELECT-statement in een of andere loop staat, maar dan zou je je eigenlijk eerst af moeten vragen of er niet iets mis is met de structuur van je programmacode...

Als alternatief voor een INSERT prepared statement zou je ook de multi-INSERT syntax van MySQL kunnen gebruiken (of bouwen), zoals we al gezien hebben in eerdere voorbeelden. Hiermee worden meerdere records in één query weggeschreven. Als zo'n query erg groot wordt krijg je mogelijk wel weer andere problemen door table locking, en dat is dan mogelijk weer minder efficiënt uit oogpunt van responstijden als verschillende processen dezelfde tabel raadplegen.

Prepared statements maken gebruik van een ander protocol voor het versturen van resultaat-data vanuit de database. De MySQL server verzendt de data binair, PHP zet deze data vervolgens om naar geschikte types (string, integer et cetera). Dit in tegenstelling tot (het default gedrag in) de situatie waarin je queries uitvoert zonder gebruik te maken van prepared statements. ALLE data die je dan terugkrijgt is (standaard) van het type string. Ook wanneer je niet gebruik maakt van prepared statements kun je gebruik maken van dit binary protocol door het instellen van bepaalde connection options (al heb ik deze tot op heden niet kunnen vinden :/). In dat geval moet je wel op een iets andere manier een connectie opbouwen dan simpelweg via de mysqli_connect() functie of connect() methode.

7.7.1 Het prepar(er)en van een prepared statement

Het gereed maken voor het uitvoeren van het query sjabloon (door deze te versturen naar de MySQL-server) gebeurt met behulp van een prepare() functie. Het invoegen van placeholders voor parameters mag niet overal, zo zijn er de volgende beperkingen:

• toegestaan in het VALUES() deel van een INSERT query
• toegestaan voor het vergelijken met een waarde in een specifieke kolom in het WHERE argument
• niet toegestaan als identificerend attribuut voor een tabel- of kolomnaam
• (zo ook) niet toegestaan als te-selecteren-kolom(men) in een SELECT query
• niet toegestaan als beide delen van een binaire operator (zoals het =-teken)
• niet toegestaan om (de waarde van) een placeholder te vergelijken met NULL door middel van "? IS NULL"

Oftewel: parameters zijn alleen toegestaan in zogenaamde Data Manipulation Language (DML) statements en niet in Data Definition Language (DDL) statements.

Prepared statements in MySQLi maken gebruik van zogenaamde positionele, anonieme placeholders. In het query sjabloon worden de parameters voorgesteld door een vraagteken (?).

1
2
3
4
5
6
7

<?php
// mysqli prepared statement preparen - procedureel
$stmt = mysqli_prepare($con, 'INSERT INTO config (_key, _value) VALUES (?, ?)');
if ($stmt === false) {
    die('prepare failed');
}
?>

Het eerste vraagteken komt in het bovenstaande voorbeeld overeen met de placeholder voor de in te voeren waarde voor _key, en het tweede vraagteken met de placeholder voor de in te voeren waarde voor _value. Een succesvolle aanroep van een prepare() functie retourneert een object van het type mysqli_stmt en anders false.

1
2
3
4
5
6
7

<?php
// mysqli prepared statement preparen - object georiënteerd
$stmt = $con->prepare('INSERT INTO config (_key, _value) VALUES (?, ?)');
if ($stmt === false) {
    die('prepare failed');
}
?>

7.7.2 Het koppelen van variabelen aan de placeholders

Vervolgens kun je variabelen koppelen aan de placeholders. Dit doe je met een bind_param() functie. Je kunt hiervoor losse aanroepen van bind_param() gebruiken of deze in één keer toekennen. Hierbij moet het type (de typen) aangegeven worden in een van de parameters.

Let op: de functie mysqli_bind_param() is een alias van mysqli_stmt_bind_param(). Deze functie is tevens deprecated vanaf PHP 5.3.0 en zal worden verwijderd in versie 5.4.0. Vermijd dus het gebruik van mysqli_bind_param().

Let op: als de data die je wilt koppelen aan een placeholder max_allowed_packet (dit is een MySQL instelling) overschrijdt dan moet je deze als blob in stukken versturen naar de database met een send_long_data() functie. Daarnaast moet je bij de controle om te kijken of deze waarde wordt overschreden rekening houden met je character set. Sommige functies (zoals strlen()) zijn niet UTF-8 aware. Afhankelijk van wat voor data je verstuurt (echte binaire data zoals afbeeldingen die je als BLOB opslaat of een hele grote tekst) moet je de data mogelijk ook nog encoden.

Let op: de placeholder parameters in de bind_param() functies zijn call by reference. Dit kan mogelijk voor raar gedrag zorgen bij onjuiste toepassing.

De bind_param() functies kennen de volgende type-aanduidingen:

• integer
• double
• string
• blob (wordt in stukken verstuurd via een send_long_data() functie)

Ingeval van de procedurele aanroep via mysqli_stmt_bind_param() is de eerste parameter de referentie naar het mysqli_stmt object. Daarna lopen de parameters van de procedurele en object georiënteerde variant gelijk. De eerst(volgend)e parameter bevat een of meer type-aanduidingen in een (aan elkaar geplakte) string-vorm. De parameters (een of meer) die daarna volgen bevatten call by reference variabelen waarmee waarden aan placeholders in het query sjabloon kunnen worden gekoppeld. In principe hoeven op dat moment deze variabelen nog niet geïnitialiseerd te zijn, dit is pas echt / enkel van belang als de query wordt uitgevoerd. Een bind_param() functie retourneert altijd een boolean. true indien het koppelen van (referenties naar) variabelen aan placeholders is gelukt, en anders false.

Procedurele variant:

1
2
3
4
5

<?php
if (mysqli_stmt_bind_param($stmt, 'ss', $stKey, $stValue) === false) {
    die('binding parameters failed');
}
?>

De tweede parameter - 'ss' - geeft aan dat de twee daaropvolgende variabelen ($stKey en $stValue) als string behandeld dienen te worden. Deze type-aanduidingen lijken niet per se aan te hoeven sluiten bij de overeenkomstige kolomtypen in de database-tabel.

Mogelijk is het verstandig om de placeholder-variabelen een vaste opbouw te geven, bijvoorbeeld door de naam ervan te laten beginnen met een vast voorvoegsel.

Object georiënteerde variant:

1
2
3
4
5

<?php
if ($stmt->bind_param('ss', $stKey, $stValue) === false) {
    die('binding parameters failed');
}
?>

7.7.3 Het toekennen van waarden aan de placeholder-variabelen en het uitvoeren van het prepared statement

Nadat er variabelen aan de placeholders gekoppeld zijn en er waarden aan deze variabelen zijn toegekend kun je het prepared statement uitvoeren. Dit doe je met een execute() functie. Procedureel:

1
2
3
4
5
6
7
8
9
10

<?php
foreach (array(
    array('fifth', 'five'),
    array('sixth', 'six'),
) as $row) {
    $stKey      = $row[0];
    $stValue    = $row[1];
    mysqli_stmt_execute($stmt) or die(mysqli_error($con));
}
?>

Het bovenstaande fragment voert dus de volgende twee queries uit:

INSERT INTO config (_key, _value) VALUES ('fifth', 'five')
INSERT INTO config (_key, _value) VALUES ('sixth', 'six')

Als je de variabelen van de goede type-indicatie had voorzien bij het binden (twee keer een string) worden hier automatisch quotes omheen gezet (er zaten geen quotes in het oorspronkelijke prepared statement).

Let op: ook mysqli_execute() is een alias, gebruik mysqli_stmt_execute().

Object georiënteerd:

1
2
3
4
5
6
7
8
9
10

<?php
foreach (array(
    array('fifth', 'five'),
    array('sixth', 'six'),
) as $row) {
    $stKey      = $row[0];
    $stValue    = $row[1];
    $stmt->execute() or die($con->error);
}
?>

7.7.4 Het afsluiten van een prepared statement

Het is verstandig (zo niet noodzakelijk) om, wanneer je klaar bent met een prepared statement, deze (zo snel mogelijk) af te sluiten met een close() functie. De foutmeldingstekst die mysqli_error() in mijn versie teruggaf op het moment dat ik een nieuw statement wilde preparen (waar weliswaar een syntax fout in zat) terwijl ik het huidige statement nog niet afgesloten had was leeg, dus dat is ook niet makkelijk debuggen als je daar voor het eerst tegenaan loopt, je wordt dan namelijk niet gewezen op de foute query.

Procedureel:

1
2
3

<?php
mysqli_stmt_close($stmt);
?>

Object georiënteerd:

1
2
3

<?php
$stmt->close();
?>

7.7.5 Prepared statements met resultaten

Het is in principe mogelijk om prepared statements uit te voeren die resultaten opleveren (anders dan true of false), dit zijn dus queries die starten met SELECT, SHOW, DESCRIBE of EXPLAIN. Wel kun je je afvragen of prepared statements daarvoor het juiste middel is, al helemaal wanneer de query geen dynamische elementen bevat en/of je de query niet meerdere keren uitvoert. Een prepared statement heeft namelijk ten minste twee round trips naar de MySQL-server: een om het query sjabloon naar de server te sturen en een om de query daadwerkelijk uit te voeren. Als je altijd prepared statements gebruikt voor het uitvoeren van (bijvoorbeeld) SELECT queries, dan zou dit dus een verdubbeling inhouden van het aantal vraagstukken wat je naar de MySQL-server stuurt ten opzichte van de "normale" manier voor het uitvoeren van dit soort queries via een query() of real_query() functie.

Dat gezegd hebbende, er zijn in principe twee manieren om resultaten op te halen:

1. via het binden van result parameters en het ophalen van resultaatrijen via een fetch() functie behorend bij een mysqli_stmt object
2. door gebruikmaking van mysqli_result functies of methoden

Let op: als je prepared statements gebruikt worden de resultaten standaard niet gebufferd, tenzij je expliciet gebruik maakt van een functie die dit wel doet. Dit dus in tegenstelling tot de standaard query() functies van MySQLi, waar result sets wel standaard gebufferd worden.

7.7.6 Resultaten ophalen met behulp van result parameters

Bij deze variant moet je na het uitvoeren van het prepared statement aan elke kolom die je opvraagt een (call by reference) variabele koppelen. Het maakt overigens niet uit of je wildcards gebruikt (zoals *), zolang het aantal opgehaalde kolommen en variabelen maar overeenkomt. Vervolgens kun je de resultaten rij voor rij ophalen uit de (ongebufferde result set van de) database met behulp van een fetch() functie. Je kunt de resultaten van een prepared statement ook bufferen met behulp van het aanroepen van de juiste store_result() functie. Wanneer je de ongebufferde variant gebruikt reserveert de MySQL-server resources voor het (serverside) opslaan van de resultaten zolang deze niet allemaal zijn opgehaald. Los van het ophalen in gebufferde of ongebufferde vorm heb je ook hier weer de keuze tussen een procedurele en object georiënteerde variant.

Let op: mysqli_bind_result() en mysqli_fetch() zijn aliassen (deprecated in 5.3.0, removed in 5.4.0), gebruik respectievelijk mysqli_stmt_bind_result() en mysqli_stmt_fetch().

De vier onderstaande varianten produceren elk de volgende output:

id: 1, key: first, value: one
id: 2, key: second, value: two
id: 3, key: third, value: three
id: 4, key: fourth, value: four
id: 5, key: fifth, value: five
id: 6, key: sixth, value: six

Procedureel, ongebufferd:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

<?php
// prepare
$stmt = mysqli_prepare($con, 'SELECT * FROM config ORDER BY id') or die('prepare failed');

// execute
mysqli_stmt_execute($stmt) or die(mysqli_error($con));

// bind
$stId       = null;
$stKey      = null;
$stValue    = null;
mysqli_stmt_bind_result($stmt, $stId, $stKey, $stValue) or die('binding failed');

// fetch unbuffered result set
while (mysqli_stmt_fetch($stmt)) {
    printf('id: %d, key: %s, value: %s', $stId, $stKey, $stValue);
}

// close
mysqli_stmt_close($stmt);
?>

Procedureel, gebufferd verloopt hetzelfde als het vorige voorbeeld maar met de volgende toevoeging ergens na de execute en voor de fetch. Dit stelt je tevens in staat om gebruik te maken van de mysqli_stmt_num_rows() en mysqli_stmt_data_seek() functies:

1
2
3
4

<?php
// store
mysqli_stmt_store_result($stmt) or die('storing failed');
?>

Object georiënteerd, ongebufferd:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

<?php
// prepare
$stmt = $con->prepare('SELECT * FROM config ORDER BY id') or die('prepare failed');

// execute
$stmt->execute() or die($con->error);

// bind
$stId       = null;
$stKey      = null;
$stValue    = null;
$stmt->bind_result($stId, $stKey, $stValue) or die('binding failed');

// fetch unbuffered result set
while ($stmt->fetch()) {
    printf('id: %d, key: %s, value: %s', $stId, $stKey, $stValue);
}

// close
$stmt->close();
?>

Object georiënteerd, gebufferd verloopt weer hetzelfde, met de volgende toevoeging ergens na na de execute en voor de fetch. Dit stelt je tevens in staat om gebruik te maken van de num_rows() en data_seek() methoden:

1
2
3
4

<?php
// store
$stmt->store_result() or die('storing failed');
?>

7.7.7 Resultaten ophalen met behulp van mysqli_result functies en methoden

Het is ook mogelijk om de resultaten van een prepared statement op te halen als gebufferde result set (dit lijkt overigens op een omslachtige manier om een query op de gebruikelijke manier uit te voeren). Dit doe je met een get_result() functie. In principe kun je direct na het aanroepen van deze functie het prepared statement sluiten omdat je de resultaten al hebt opgeslagen in een (lokale) buffer. Procedureel:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

<?php
// prepare
$stmt = mysqli_prepare($con, 'SELECT * FROM config ORDER BY id') or die('prepare failed');

// execute
mysqli_stmt_execute($stmt) or die(mysqli_error($con));

// haal resultaat op als mysqli_result object
// direct hierna kun je het statement in principe sluiten
$res = mysqli_stmt_get_result($stmt);

// ophalen van resultaatrijen, gebruik je favoriete fetch functie
while ($row = mysqli_fetch_assoc($res)) {
    printf('id: %d, key: %s, value: %s', $row['id'], $row['_key'], $row['_value']);
}

// resultaat vrijgeven
mysqli_free_result($res);

// close
mysqli_stmt_close($stmt);
?>

Object georiënteerd:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

<?php
// prepare
$stmt = $con->prepare('SELECT * FROM config ORDER BY id') or die('prepare failed');

// execute
$stmt->execute() or die($con->error);

// haal resultaat op als mysqli_result object
// direct hierna kun je het statement in principe sluiten
$res = $stmt->get_result();

// ophalen van resultaatrijen, gebruik je favoriete fetch functie
while ($row = $res->fetch_assoc()) {
    printf('id: %d, key: %s, value: %s', $row['id'], $row['_key'], $row['_value']);
}

// resultaat vrijgeven
$res->free();

// close
$stmt->close();
?>

7.7.8 Ovewegingen bij het (kiezen voor het) gebruik van prepared statements

Prepared statements hebben voor- maar ook nadelen. Als je kiest voor een (specifieke) manier voor het uitvoeren van queries (bijvoorbeeld prepared statements), blijf dan jezelf ook continu afvragen waarom je kiest voor deze methode. Prepared statements zijn (lang) niet altijd efficiënter dan de normale query() functies. Ook zijn er wellicht andere zaken waar je (nu) nog niet tegenaan loopt of aan hebt gedacht waar je later mee geconfronteerd wordt als je voor deze aanpak hebt gekozen. In de zeer korte periode dat ik met prepared statements heb gewerkt, eigenlijk uitsluitend in het kader van onderzoek voor deze tutorial, heb ik de volgende lijsten van potentiële voor- en nadelen opgesteld. Allereerst de voordelen indien je gebruik maakt van prepared statements:

• indien je in een script veelvuldig dezelfde query uitvoert (bijvoorbeeld een INSERT query) dan kan dit efficiënter zijn dan het uitvoeren van losse queries
• het gebruik van bound variables zorgt voor beveiliging tegen SQL injection; deze hoeven niet apart geescaped te worden in je query, op grond van de typehint die je geeft in je bind_param() functie worden de waarden van deze parameters omgezet

Dan een aantal potentiële nadelen:

• het laatste punt van de vorige lijst geeft aan dat parameters worden getypecast, wat mogelijk voor onvoorspelbaar gedrag in queries kan zorgen; verder hoeft de typehint die je bij een parameter geeft niet aan te sluiten bij het type van de kolom van de database-tabel
• er lijkt vooralsnog geen makkelijke manier te zijn om prepared (SELECT) statements weer te geven in de uiteindelijke vorm waarin ze worden uitgevoerd; dit maakt het ontwikkelen en debuggen van queries niet gemakkelijker; wat ik uiteindelijk gedaan heb om te zien welke queries er nou uiteindelijk concreet worden uitgevoerd is het aanzetten van de general_log van mijn MySQL database, waarna ik vervolgens mijn general_log_file ben wezen doorspitten om te zien welke queries er worden uitgevoerd (dat was tevens ook de enige manier waarop ik kon nagaan dat er daadwerkelijk typecasts op parameters plaatsvonden)
• het maakt het gebruik van bepaalde MySQL-specifieke constructies moeilijker (zo niet onmogelijk?), zoals bijvoorbeeld IN (...)

Uit de voorstaande lijst kun je wellicht opmaken waar mijn voorkeur ligt :). Als algemene regel kun je de volgende vuistregel hanteren:

Als je geen specifieke reden hebt om prepared statements te gebruiken in MySQLi, gebruik deze dan niet.

7.8 Stored procedures

Stored procedures zijn niet echt een voorziening van MySQLi maar meer van MySQL zelf. MySQLi biedt hier ondersteuning voor. Een stored procedure is in wezen een functie die je opslaat in de MySQL database, die je vervolgens aan kunt roepen (en uit kunt voeren) met een CALL statement.

Zoals op verschillende plaatsen wordt uitgelegd, zijn er verschillen tussen MySQL FUNCTIONs en PROCEDUREs:

• een PROCEDURE moet je (apart) aanroepen via een CALL statement
• een PROCEDURE kan een of meer result sets retourneren, of simpele waarden; in dit laatste geval moeten resultaten opgeslagen worden in, en opgehaald worden via MySQL sessie variabelen
• een FUNCTION kan los worden aangeroepen via een SELECT statement, of deel uitmaken van een MySQL-statement en kan een scalar waarde retourneren (dit is een simpel, niet samengesteld type zoals een integer, boolean, float of string)

Omdat stored procedures in wezen een MySQL ding is, en (dus) niet MySQLi specifiek is, houden we de bespreking ervan beperkt. Op PHP.net staat in de korte samenvatting aldaar al een hele hoop. Hier volgt een korte samenvatting, met een enkele eigen toevoeging:

• in het algemeen heeft het opstellen van een functie (FUNCTION of PROCEDURE) pas zin als deze werk uit handen neemt (een invoer via berekeningen omvormt tot een uitvoer) en/of meerdere keren wordt uitgevoerd, ga dus altijd na of de introductie ervan een zekere meerwaarde heeft (voordat je een wildgroei van allerhande functies hebt)
• zoals in de eerder genoemde link naar PHP.net wordt uitgelegd zijn er (waarschijnlijk technische) redenen waardoor alleen real_query() en multi_query() functies geschikt zijn om stored procedures uit te voeren die result sets retourneren, gebruik in dit geval dus niet mysqli_query(); ook prepared statements (zelfs in combinatie met get_result()) zijn hiervoor toegestaan
• afhankelijk van je MySQL versie biedt deze mogelijk geen ondersteuning voor CALL-aanroepen, of de verschillende parameter-typen die stored procedures rijk zijn

7.9 Multiple statements

MySQLi biedt ondersteuning aan het uitvoeren van meerdere statements in één SQL string. Dit kan door gebruikmaking van de multi_query() functies, waarbij de statements gescheiden worden door een punt-komma (;). Hierbij maakt het niet uit of de statements result sets teruggeven of niet. De (ongebufferde) result sets moeten na het uitvoeren van de multi_query() individueel worden opgehaald. Bij het doorlopen van de resultaten (met mogelijk bijbehorende result sets) wordt gebruik gemaakt van de more_results() en next_result() functies.

Let op: indien een van de queries om een of andere reden fout is retourneert next_result() false. Het loont dus altijd de moeite om na het ophalen van de resultaten van een multi_query() aanroep te controleren of deze helemaal doorlopen is.

Let op: wanneer je een reeks INSERT (of UPDATE, of DELETE) statements op deze manier uitvoert en ergens halverwege produceert een query een fout, dan zijn alle queries tot dat punt al uitgevoerd. Wanneer je op deze manier batches van queries wilt uitvoeren dan heb je dus de kans dat je data corrupt raakt (onvolledige toevoegingen, wijzigingen of verwijderingen). Als het belangrijk is dat een batch in zijn geheel wordt uitgevoerd, of in zijn geheel niet, gebruik dan transacties. Dit moet dan wel mogelijk zijn in jouw database en hangt af van de storage engine van je tabellen.

Let op: als je gebruik maakt van de multi_query() functies is het nog belangrijker dat je invoer beschermt is tegen SQL injection. Je wilt namelijk niet dat iemand "; DROP DATABASE ..." toevoegt (je kunt je ook afvragen als dat mogelijk is of de database-user die jij gebruikt dan niet teveel permissies heeft). Overigens werken multiple statements niet in de gewone query() functies, dus meerdere punt-komma gescheiden queries op die manier injecteren heeft geen effect, je krijgt in dat geval een (syntax)foutmelding van MySQL.

Let op: het is niet mogelijk om multi_query() functies te gebruiken in combinatie met prepared statements.

Let op: meestal wordt een store_result() functie gebruikt voor het (creëren en) ophalen van een (buffered) result set. Indien de result set niet uitgelezen kan worden (omdat de query een fout bevatte?) retourneert deze functie false, maar dit gebeurt ook wanneer de query geen result set heeft, bijvoorbeeld ingeval van een INSERT query. Dit, in combinatie met het feit dat binnen de loop voor het ophalen van resultaten na uitvoering van een multi_query() functie eigenlijk geen fouten kunnen optreden (anders bestond dit resultaat uberhaupt niet (het ophalen wordt afgebroken als er een fout in een query is opgetreden)) levert dat je hiermee onderscheid kunt maken tussen queries die wel of geen result sets opleveren (als ik het goed begrijp). Het loont altijd de moeite om na afloop van deze loop te controleren of er fouten waren en/of op een of andere manier te controleren of alle queries zijn uitgevoerd.

Let op: de volgorde waarin je de more_results() en next_result() functies aanroept maakt uit! Zorg dat next_result() als laatste wordt aangeroepen, anders skip je mogelijk het ophalen van de result set bij je laatste query (de query zelf is dan al wel uitgevoerd).

Procedureel:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

<?php
$queries = array(
    'SELECT * FROM config ORDER BY id',
    'DELETE FROM config WHERE id > 6', // zou alle records na "sixth" moeten verwijderen
    "INSERT INTO config (_key, _value) VALUES ('seventh', 'seven')",
    'SELECT * FROM config ORDER BY id DESC',
);

if (!mysqli_multi_query($con, implode(';', $queries))) {
    die('multi query failed'); // treedt alleen op bij syntax-fouten
}

// bij deze constructie gaan we er vanuit dat we tenminste één query uitvoeren
$i = 1;
do {
    $res = mysqli_store_result($con);
    if ($res === false) {
        // dit was waarschijnlijk een INSERT (of UPDATE, of DELETE) query
        // hier kun je eventueel nog kijken naar het aantal gewijzigde records met mysqli_affected_rows($con)
        // ...
    } else {
        // dit was waarschijnlijk een SELECT query
        // doe iets met $res (dit is een object van het type mysqli_result)
        // ...
        // en geef na afloop het resultaat vrij
        mysqli_free_result($res);
    }
    $i++;
} while (mysqli_more_results($con) && mysqli_next_result($con));

// traden er fouten op bij het uitvoeren van de queries?
if (mysqli_errno($con) > 0) {
    echo 'batch failed at query '.$i.': '.mysqli_error($con).' ('.mysqli_errno($con).')';
}
?>

Object georiënteerd:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

<?php
$queries = array(
    'SELECT * FROM config ORDER BY id',
    'DELETE FROM config WHERE id > 6', // zou alle records na "sixth" moeten verwijderen
    "INSERT INTO config (_key, _value) VALUES ('seventh', 'seven')",
    'SELECT * FROM config ORDER BY id DESC',
);

if (!$con->multi_query(implode(';', $queries))) {
    die('multi query failed'); // treedt alleen op bij syntax-fouten
}

// bij deze constructie gaan we er vanuit dat we tenminste één query uitvoeren
$i = 1;
do {
    $res = $con->store_result();
    if ($res === false) {
        // dit was waarschijnlijk een INSERT (of UPDATE, of DELETE) query
        // hier kun je eventueel nog kijken naar het aantal gewijzigde records met $con->affected_rows
        // ...
    } else {
        // dit was waarschijnlijk een SELECT query
        // doe iets met $res (dit is een object van het type mysqli_result)
        // ...
        // en geef na afloop het resultaat vrij
        $res->free();
    }
    $i++;
} while ($con->more_results() && $con->next_result());

// traden er fouten op bij het uitvoeren van de queries?
if ($con->errno > 0) {
    echo 'batch failed at query '.$i.': '.$con->error.' ('.$con->errno.')';
}
?>

7.10 API ondersteuning voor transacties

MySQLi biedt vanuit haar API ondersteuning voor transacties. Dit houdt in dat, wanneer je van de API calls (MySQLi-specifieke functies) gebruik maakt voor het uitvoeren van transacties in plaats van gebruikmaking van native MySQL-statements (die overigens ook gewoon werken) er mogelijk extra functionaliteit benut kan worden voor het efficiënter uitvoeren (of op een andere manier optimaliseren) van deze transacties. Oftewel: gebruik bij voorkeur de door MySQLi voorgeschreven wijze voor het uitvoeren van transacties.

Let op: de storage engine van je database-tabellen moeten ook transacties ondersteunen. De InnoDB storage engine biedt ondersteuning aan transacties.

7.10.1 Wat is een transactie

Als je normaal gesproken een INSERT, UPDATE of DELETE query uitvoert, wordt deze meteen verwerkt en is de wijziging daarmee direct "permanent", na afloop van het uitvoeren van zo'n query is de toestand van de database(gegevens) meteen bijgewerkt.

Stel nu dat je op gezette tijden een (complexe of uitgebreide) reeks van dat soort queries wilt uitvoeren. Denk hierbij bijvoorbeeld aan de (eenmalige of periodieke) import van gebruikersgegevens, of het updaten van abonnementen van een klantenbestand of iets dergelijks. Deze te importeren gegevens hebben mogelijk relaties met al reeds aanwezige gegevens in de database. In een ideale situatie zijn deze in te voeren gegevens foutvrij en conflicteren ze niet met de reeds aanwezige data, maar stel nu dat er tijdens het uitvoeren van deze importroutine om een of andere reden een query (of code) in dit proces een fout produceert. Dit kan tot gevolg hebben dat de import vastloopt en/of het script crasht. Dit heeft dan tevens tot gevolg dat je data corrupt is geraakt, immers, de queries die tot dan toe zijn uitgevoerd zijn al verwerkt dus de data in je database is maar deels up-to-date (tot het moment van de onderbreking).

Dit soort bewerkingen wil je meestal graag als één ondeelbare (atomaire) actie uitvoeren waarbij je ofwel alles verwerkt (als de import slaagt) of niets verwerkt (als de import op een of andere manier stukloopt) zodat je weer kunt terugkeren naar de meest recente (werkende) situatie. Dit is precies waarvoor transacties bedoeld zijn: het stelt je in staat om een reeks van queries af te bakenen en na het uitvoeren ervan te besluiten of je de wijzigingen permanent wilt maken of deze terug wilt draaien.

7.10.2 Database ondersteuning voor (het gebruik van) transacties

Niet alle tabeltypen (storage engines) ondersteunen het gebruik van transacties. Als je transacties wilt gebruiken bij de afbakening van een reeks queries dienen de betrokken tabellen van het type InnoDB te zijn. InnoDB tabellen bieden tevens (onder andere) ondersteuning aan foreign keys. Wanneer je database veel data bevat met veel onderlinge verbanden en het belangrijk is dat de correcte onderlinge samenhang niet wordt verstoord (dit wordt ook wel referentiële integriteit genoemd) dan loont het zeker de moeite om (in ieder geval te overwegen) de tabellen de InnoDB storage engine te geven.

Let op: het kiezen voor een storage engine dient een weloverwogen keuze te zijn en zou af moeten hangen van de data die hierin opgeslagen is en hoe je hier mee omgaat. Ook InnoDB heeft nadelen, zo biedt deze, in tegenstelling tot de MyISAM storage engine, geen ondersteuning voor FULLTEXT searches (althans, niet voor versie 5.6).

7.10.3 Het verloop van een transactie in het algemeen

Een transactie verloopt in zijn simpelste vorm doorgaans als volgt:

1. de transactie wordt gestart
2. vervolgens worden er meestal meerdere queries achter elkaar uitgevoerd (ingeval er maar één query wordt uitgevoerd is een transactie waarschijnlijk niet nodig)
3. het effect van de queries wordt permanent gemaakt; dit wordt ook wel committen genoemd, de queries worden gecommit
   of
   het effect van de queries wordt teruggedraaid, dit staat bekend als een rollback; alle bewerkingen vanaf de start van de transactie (of vanaf de laatste voorgaande rollback daarbinnen) worden ongedaan gemaakt

7.10.4 Het starten van een transactie in MySQLi

Normale queries in MySQL, dus zonder gebruikmaking van transacties, worden meteen na uitvoer permanent gemaakt; met andere worden, deze worden automatisch gecommit. Dit is het default gedrag van MySQL en is vastgelegd in de MySQL-instelling autocommit.

Door middel van het (tijdelijk) deactiveren van autocommit kun je een transactie starten. Hetzelfde effect (het starten van een transactie) kun je ook bereiken met verschillende MySQL queries, maar er wordt aangeraden om van de API functies gebruik te maken. In MySQLi doe je dit (procedureel) als volgt:

1
2
3
4

<?php
// start transactie
mysqli_autocommit($con, false);
?>

En object georiënteerd:

1
2
3
4

<?php
// start transactie
$con->autocommit(false);
?>

Let op: het is heel belangrijk om een goed gevoel te krijgen voor hoe transacties werken, zeker in systemen waarin meerdere gebruikers tegelijkertijd actief zijn. Als een persoon een website bezoekt, dan worden er mogelijk queries uitgevoerd binnen een of meer transacties tijdens het uitvoeren van PHP code voor het samenstellen van een dynamische pagina. Tijdens deze periode staat deze gebruiker via deze scripts in verbinding met de database. Deze heeft dan (tijdelijk) een "eigen" connectie met de database. Een andere gebruiker die op hetzelfde moment de site bezoekt heeft op zijn beurt (voor de duur van het laden van de pagina) ook een "eigen" connectie. Het kan dus voorkomen dat verschillende transacties van verschillende gebruikers tegelijkertijd uitgevoerd worden.

Het enkel starten van een transactie is dus niet voldoende om de "ondeelbaarheid" van gegevensmanipulatie te waarborgen!

Je kunt de actuele waarde (voor de huidige connectie) van autocommit opvragen met SELECT @@autocommit, bijvoorbeeld door gebruikmaking van een eenvoudige functie:

1
2
3
4
5
6
7
8

<?php
function getAutocommitStatus($con) {
    $res = mysqli_query($con, 'SELECT @@autocommit');
    $row = mysqli_fetch_row($res);
    mysqli_free_result($res);
    return $row[0];
}
?>

7.10.5 Het committen van de transactie in MySQLi

Na het uitvoeren van queries in je transactie heb je twee keuzen: of je voert een commit uit, of een rollback.

Door het uitvoeren van een commit() functie kun je de tot dan toe "uitstaande" queries permanent wegschrijven naar de database. Dit zou eigenlijk ook altijd het einde van je transactie moeten markeren.

Let op: het uitvoeren van een commit() zorgt er niet voor dat autocommit weer wordt geactiveerd. Wel worden records die voor de commit werden vergrendeld weer vrijgegeven. Je zou dus kunnen stellen dat je na het uitvoeren van een commit() functie binnen een transactie (zoals je deze in PHP gebruikt, als je rechtstreeks in een database bezig bent werken zaken mogelijk anders) eigenlijk weer opnieuw begint met de / een transactie. Om verwarring te voorkomen (en ook om te vermijden dat je daarna in de knoei komt met een rollback) is het waarschijnlijk het beste om in een transactie aan het einde altijd maar één commit te doen of alles terug te draaien via een rollback.

Let op: als je scripts waarin transacties zitten geen commit() uitvoeren, bijvoorbeeld omdat deze ontbreken of omdat het script voor het moment van deze functie-aanroep om een of andere reden afbreekt, worden alle wijzigingen die binnen deze transacties plaatsvonden teruggedraaid (impliciete rollback). Als je queries binnen een transactie dus van een toevoeging "... or die(...)" gebruik maken dan zorgt dit ervoor dat de verdere uitvoer van het script gestaakt wordt indien er een fout optreedt in een query, wat er vervolgens weer voor zorgt dat de transactie wordt teruggedraaid. Uit oogpunt van de "gezondheid" van (de data van) je database is dit een manier om deze tegen corrumpering van data te beschermen, maar je zou je kunnen afvragen of fouten mogelijk subtieler kunnen worden afgehandeld.

Let op: sommige MySQL-statements zorgen voor impliciete commits. Dit houdt in dat het effect van het uitvoeren van zo'n query hetzelfde is alsof je (voor het uitvoeren ervan) een commit deed en de transactie beëindigde. Dit zijn vaak queries waarbij de structuur van database(-tabellen) op een of andere manier verandert. Dit soort queries lenen zich niet voor transacties.

Let op: ook door het weer activeren van autocommit worden de "uitstaande" queries van een transactie gecommit. Maar dit beëindigt tevens de transactie zoals je hier in MySQLi mee om zou moeten gaan. Omdat het op PHP.net niet wordt aangedragen om zaken op deze manier te committen is het waarschijnlijk beter om deze methode niet te gebruiken voor dit doel. Het weer activeren van autocommit zou je wel kunnen gebruiken voor het (permanent) afsluiten van de huidige transactie, om daarna weer op de normale manier (niet binnen een transactie) queries uit te kunnen voeren.

Let op: als je records in transacties wilt vergrendelen (met FOR UPDATE) dan MOET je gebruik maken van ofwel het START TRANSACTION statement (wat niet wordt aangeraden), of door het deactiveren van autocommit (wat waarschijnlijk de betere keuze is, omdat hier een API-functie voor is).

Procedureel:

1
2
3
4
5
6
7
8
9
10
11

<?php
// start transactie
mysqli_autocommit($con, false);

// voer queries uit, eventueel met or die(...) of
// een subtielere manier om fouten te detecteren
// ...

// commit (indien alles in order is)
mysqli_commit($con);
?>

Object georiënteerd:

1
2
3
4
5
6
7
8
9
10
11

<?php
// start transactie
$con->autocommit(false);

// voer queries uit, eventueel met or die(...) of
// een subtielere manier om fouten te detecteren
// ...

// commit (indien alles in orde is)
$con->commit();
?>

7.10.6 Het terugdraaien van de transactie in MySQLi

Het terugdraaien van een transactie kan met een rollback() functie. Procedureel:

1
2
3
4
5
6
7
8
9
10

<?php
// start transactie
mysqli_autocommit($con, false);

// voer queries uit
// ...

// er ging iets mis, maak transactie ongedaan
mysqli_rollback($con);
?>

Object georiënteerd:

1
2
3
4
5
6
7
8
9
10

<?php
// start transactie
$con->autocommit(false);

// voer queries uit
// ...

// er ging iets mis, maak transactie ongedaan
$con->rollback();
?>

In principe is het mogelijk om een transactie voort te zetten na een rollback. Realiseer je hierbij wel dat de queries die eerder teruggedraaid zijn met een rollback niet opnieuw gecommit kunnen worden (tenzij je de betreffende queries echt opnieuw uitvoert). Je kunt je ook afvragen of het verstandig is om nog door te gaan met je transactie nadat je een rollback hebt uitgevoerd, want dit druist tegen het principe in om alles in zijn geheel, of in zijn geheel niet te committen.

Daarnaast is het zo dat binnen de transacties het effect van de queries wel (tijdelijk) geldt. Stel bijvoorbeeld dat je een record invoert in de config-tabel binnen een transactie, dan zou je direct daarna (nog steeds binnen de transactie) dit record kunnen opvragen met een SELECT-query.

Ook is het zo dat bij INSERT-queries de tellers van AUTO_INCREMENT kolommen worden opgehoogd. Hierbij maakt het niet uit of deze queries uiteindelijk worden gecommit of niet. Door het doen van rollbacks zouden er dus gaten kunnen vallen in reeksen van (bijvoorbeeld) record id's, maar dit zou voor de correcte werking van je applicatie niet uit mogen maken.

7.10.7 Geneste transacties

MySQL (de database) ondersteunt in principe geen geneste transacties (wel bestaat er zoiets als (13.3.7) XA transacties), maar heeft wel een functionaliteit onder de naam SAVEPOINTs. Hiermee kun je zelf specifieke breekpunten in je transactie definiëren waar je gericht rollbacks naar uit kunt voeren en hier zou je dus een soort van transactie-stack mee kunnen bouwen.

7.10.8 Het bundelen van transactie-operaties

Om het gebruik van transacties te vergemakkelijken kan het een idee zijn om een aantal zaken omtrent transacties bij te houden en/of te combineren. Je zou bijvoorbeeld in een (wrapper) class bij kunnen houden of een transactie gestart is. Afhankelijk van hoeveel flexibiliteit je wilt bij het uitvoeren van je transacties (gebruikmaking van savepoints en/of een voortzetting van de transactie na een rollback) zou je ook aanroepen van MySQLi-functies kunnen bundelen in methoden voor het starten en stoppen hiervan. Het op één plaats vastleggen hoe je met transacties omgaat heeft het voordeel dat alle transacties vervolgens op dezelfde wijze worden uitgevoerd (het zorgt dus voor meer uniformiteit), mits al je aanroepen via deze class verlopen uiteraard. Meer over dit idee wordt in een volgende paragraaf behandeld.

7.10.9 Voorbeeld van gebruik

Het verkeerd toepassen van transactie-functionaliteit is mogelijk nog vervelender dan het niet toepassen hiervan omdat het je een vals gevoel van veiligheid kan geven. Het is in eerste instantie belangrijk dat je (redelijk) precies weet hoe transacties werken. Daarnaast is het van belang dat je weet wanneer je transacties zou moeten gebruiken. Dit is in principe in elke situatie waarbij je (zoals eerder gezegd) een reeks queries in zijn geheel wilt uitvoeren, of in zijn geheel niet (waarbij je dus de mogelijkheid hebt om alles terug te draaien), maar ook waarbij één partij het exclusieve recht heeft om op elk moment een (of meer) waarden in de database uit te kunnen lezen en aan te kunnen passen (en dus op die manier ook de beschikking heeft over "ondeelbare" acties).

Nu is deze laatste omschrijving nogal vaag, maar stel je het volgende voor (dit is iets wat ik in de praktijk heb meegemaakt): in je database heb je een tabel waar de informatie van een inschrijfformulier in opgeslagen staat. Het inschrijfformulier is onderdeel van een tijdelijke actie waarbij op een bepaalde dag elk uur 50 speeltjes worden vergeven. Hierbij kunnen we verwachten dat een aanzienlijke hoeveelheid mensen elk uur (opnieuw) zal proberen het formulier in te vullen.

Als in een uur alle speeltjes voor dat uur vergeven zijn, zal er in de plaats van een formulier een boodschap getoond worden dat men het op een later tijdstip nogmaals moet proberen. De controle hiervoor kan in één query gevangen worden (tel het aantal inschrijvingen van het huidige uur) en vervolgens kan eenvoudig besloten worden of je het formulier toont.

Maar wat nu als de limiet nog niet bereikt is? In dat geval wordt het inschrijfformulier getoond en kunnen een heleboel mensen tegelijkertijd proberen een speeltje te bemachtigen. Stel nu dat er circa honderd mensen "tegelijkertijd" het formulier versturen. Waar dus alles mee valt of staat is de verwerking van het formulier. De programmeur die hiermee aan de slag ging schreef daartoe de volgende soortgelijke (pseudo)code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

<?php
// ...
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    // start transactie
    mysqli_autocommit($con, false);

        // controleer het aantal inschrijvingen van dit uur
        $res = mysqli_query('SELECT COUNT(id)
                             FROM inschrijvingen
                             WHERE HOUR(inschrijfdatum) = HOUR(NOW())');
        $row = mysqli_fetch_row($res);

        if ($row[0] < 50) {
            // verwerk de nieuwe inschrijving
            // ...
        }
    // commit transactie
    mysqli_commit($con);
}
// redirect naar een andere pagina
// ...
?>

Ziet er goed uit niet? Inderdaad, niet. Wat de programmeur vergat of waar deze niet bekend mee was is dat verschillende transacties van verschillende connecties prima tegelijkertijd plaats kunnen vinden. In dat geval moeten gegevens "vergrendeld" worden om een en ander in goede banen te leiden. Het is desalniettemin leerzaam om eerst te zien hoe het fout gaat. Als we in een tijdslijn drie gebruikers volgen (A, B, C) dan zou de volgorde van evenementen er bijvoorbeeld als volgt uit kunnen zien:

A verstuurt formulier
A start transactie
B verstuurt formulier
C verstuurt formulier
A voert controle query uit
C start transactie
A verwerkt formulier
C voert controle query uit
A commit
B start transactie
B voert controle query uit
C verwerkt formulier
B verwerkt formulier
C commit
B commit

Zoals al eerder aangegeven, het effect van een transactie (van de queries daarbinnen) wordt pas permanent op het moment dat deze gecommit wordt. Voor de connectie die met de transactie bezig is "bestaan" deze wijzigingen al tijdens de transactie maar voor de buitenwereld (lees: andere connecties) bestaan deze (nog) niet!

Misschien zie je de bui al een beetje hangen :). Worst case scenario: tot voor het moment van tonen van het formulier stond de teller op 49 inschrijvingen. De gebruikers A, B en C krijgen het formulier dus gewoon te zien. Vervolgens vinden de hierboven beschreven zaken plaats. De eerste keer dat het aantal inschrijvingen wordt gecontroleerd (A voert controle query uit) staat de teller op 49, de inschrijving van A wordt dus doorgelaten. De tweede keer dat het aantal inschrijvingen wordt gecontroleerd (C voert controle query uit) staat de teller nog steeds op 49 omdat de transactie van A nog niet gecommit is. Hiermee wordt de inschrijving van C dus ook (onterecht) doorgelaten. De derde keer dat het aantal inschrijvingen wordt gecontroleerd (B voert controle query uit) staat de teller inmiddels op 50 (A was immers reeds gecommit). Het probleem hier zit dus in het feit dat informatie verandert nadat je deze controleert en voordat je (mogelijk andere) informatie wegschrijft. Maar dan zijn dus mogelijk de condities waaronder je informatie wegschrijft niet meer van toepassing.

In het bovenstaande voorbeeld volgden we slechts drie gebruikers, maar in de praktijk kunnen dit er tientallen of honderden zijn. Het aantal gebruikers wat ten onrechte een speeltje is beloofd naar aanleiding van een geslaagde inschrijving ligt dan mogelijk ook wat hoger :).

De oplossing is simpel: de query die de controle uitvoert heeft een kleine aanpassing nodig, weliswaar met zeer grote gevolgen. Het enige wat hoeft te gebeuren is het toevoegen van de vermelding FOR UPDATE. Het effect hiervan is dat een transactie (of gewone queries, deze hoeven niet eens in een transactie te zitten) van een andere connectie die van dezelfde tabel (records) wil lezen (waar een SELECT ... FOR UPDATE query op uitgevoerd wordt) moet wachten totdat deze transactie klaar is (doordat deze een commit of rollback uitvoert zodat deze read lock weer wordt vrijgegeven). Dit heeft dus in het bovenstaande voorbeeld tot gevolg dat de verwerking van andere inschrijvingen correct worden geteld bij de bepaling of de eigen inschrijving mag plaatsvinden of niet. De code wordt aldus:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

<?php
// ...
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    // start transactie
    mysqli_autocommit($con, false);

        // controleer het aantal inschrijvingen van dit uur
        $res = mysqli_query('SELECT COUNT(id)
                             FROM inschrijvingen
                             WHERE HOUR(inschrijfdatum) = HOUR(NOW())
                             FOR UPDATE');
        $row = mysqli_fetch_row($res);

        if ($row[0] < 50) {
            // verwerk de nieuwe inschrijving
            // ...
        }
    // commit transactie
    mysqli_commit($con);
}
// redirect naar een andere pagina
// ...
?>

Dit heeft de volgende consequenties voor de tijdslijn uit het voorbeeld:

A verstuurt formulier
A start transactie
B verstuurt formulier
C verstuurt formulier
A voert controle query uit - retourneert 49
C start transactie
A verwerkt formulier
C voert controle query uit - ... en deze wacht op A
A commit - C mag nu verder, de controle query voor C retourneert 50
B start transactie
B voert controle query uit - ... en deze wacht op C
C verwerkt formulier niet - conditie voldoet niet
B verwerkt formulier - dit vindt nu niet meer plaats, moet nog wachten op C
C commit - B mag nu verder, de controle query voor B retourneert eveneens 50
B verwerkt formulier niet - conditie voldoet niet
B commit - de readonly lock op (records van) de tabel wordt opgeheven

Let hierbij op het volgende: FOR UPDATE biedt dus enige garanties voor het in stand houden van condities, maar voor hetzelfde geld verstuurden A, B en C hun formulier tegelijkertijd en kwam B (of C) als laatste inschrijving van het uur uit de bus. Ook is het onverstandig om FOR UPDATE als een soort toverspreuk te gebruiken, probeer je atijd te realiseren wat het effect hiervan is.

Met FOR UPDATE kun je in InnoDB tabellen records vergrendelen, het hangt dus heel erg van je query af welke records dit precies zijn, hier zul je nauwkeurig mee om moeten springen.

Het zou onpraktisch zijn om records voor onbepaalde tijd te locken, daartoe hebben deze timeouts. Mocht een query te lang informatie vergrendelen dan kunnen er timeouts optreden, je krijgt dan foutmeldingen als Lock wait timeout exceeded; try restarting transaction.

Als er dingen in de verkeerde volgorde gebeuren krijg je mogelijk de melding Deadlock found when trying to get lock; try restarting transaction. Je kunt met transacties bepaalde problemen uit de weg gaan, maar ook nieuwe problemen introduceren.

7.11 Het organiseren van MySQLi functies in een wrapper class of DAAL

Er zijn een aantal redenen waarom het verstandig kan zijn om voor je database-functies alternatieve functies of een of meer classes te introduceren die in wezen hetzelfde doen. Dit lijkt misschien overbodig werk, immers, de functies introduceren geen nieuwe functionaliteit, het is slechts een andere manier om hetzelfde te doen? Dit klopt, maar als je vervolgens enkel gebruik maakt van deze functies of classes ben je er ook van verzekerd dat dit altijd op dezelde manier gaat, het dwingt dus een zekere mate van uniformiteit af. Denk bijvoorbeeld aan de manier waarop je met transacties omgaat (indien je storage engine dit ondersteunt).

Een ander mogelijk bijkomend voordeel is dat je bij gebruikmaking van alternatieve functies of classes geen database-specifieke code in je programmacode hebt, met andere woorden, je vermijd hierbij hardcoding. Als je dit al deed voor je klassieke mysql_ functies zou het overstappen naar MySQLi slechts een kwestie van het aanpassen van de implementatie van deze functies of methoden kunnen zijn, in je programmacode zelf hoef je dan (in een ideale situatie) niets aan te passen. Het zorgt dus tevens voor enige abstractie - je hoeft aanpassingen (en reparaties) maar op één plaats te verrichten.

Ook kun je, zoals eerder aangehaald, bepaalde operaties bundelen in simpelere (en kortere) operaties. Dit heeft tot gevolg dat je met het schrijven van minder code hetzelfde effect bereikt, weliswaar door een eenmalige investering voor het schrijven van deze database-code, maar deze is snel terugverdiend. Een gevolg hiervan is weer dat code makkelijker leesbaar wordt, en daarmee beter onderhoudbaar is.

Voordat je op de PDO bandwagon springt, PDO redt je ook niet als je verandert van database-type, tenzij je in geen enkele query MySQL-specifieke SQL gebruikt (en als je dat niet doet, wat was dan de reden dat je voor MySQL hebt gekozen?). Zowel PDO als de laag die je zelf schrijft (of ergens van overneemt) zijn beide Data Access Abstraction Layers (DAALs), dit houdt in dat je binnen deze lagen nog steeds volledige MySQL (specifieke) queries schrijft - het zijn geen Database Abstraction Layers waarbij je queries op een database-onafhankelijke manier opstelt en je, theoretisch, vrij zou kunnen schakelen tussen database-types zonder ook maar een regel code (maar waarschijnlijk wel wat configuratie) aan te passen. Ook lijkt het mij onwaarschijnlijk dat je dit (het schakelen tussen database-types) geregeld doet.

8. Storage engines en character sets

Let er op dat dit hele MySQLi verhaal los staat van welke storage engine of welke character set je gebruikt. Dit bepaalt mede wat je wel of niet kunt met je database (denk aan fulltext searches, transacties en foreign keys) en hoe je het een en ander kunt opslaan, zoeken, ophalen en afdrukken. Al deze aspecten hangen op een of andere manier met elkaar samen dus het is zaak dat je van al deze onderwerpen iets afweet.

9. Tot slot

Bij (verhitte) discussies bij welke methode "het beste" of "beter" is (MySQLi in procedurele of object georiënteerde vorm, of toch PDO), is de grens tussen persoonlijke voorkeur en objectieve (technische) argumenten vaak vaag. Daarnaast bestaat "het beste" niet omdat dit afhankelijk is van de situatie en toepassing. Laat je keuze dus afhangen van de situatie en zorg er in ieder geval voor dat je je keuze kunt onderbouwen met argumenten.

Uiteindelijk komt het (toch) min of meer neer op persoonlijke voorkeur. In deze tutorial zijn genoeg "uitroeptekens" geplaatst bij bepaalde aanpakken in MySQLi, ten einde te voorkomen dat je kiest voor een methodiek waarmee je je mogelijk op termijn in de vingers snijdt.

In de samenvatting wordt een voorstel gedaan voor een methode die, indien je van MySQLi gebruik maakt, waarschijnlijk het flexibelste is. Wanneer je die opzet gebruikt bij het opstellen van je queries zul je meestal niet snel vastlopen, simpelweg omdat je op die manier de meeste controle hebt over de uiteindelijke vorm van je MySQL-statements.

10. Bronnen en meer informatie

• PHP.net - MySQL PHP drivers - choosing an API
• PHP.net - MySQLi Improved Extension
• PHP.net - Magic Quotes
• PHP.net - PDO Drivers - MySQL Functions (PDO_MYSQL)
• Stackoverflow - Why shouldn't I use mysql functions
• Stackoverflow - mysqli_store_result() vs. mysqli_use_result()
• Stackoverflow - differences between procedure and function and routine?
• The Absolute Minimum Every Software Developer Absolutely, Positively Must Know About Unicode and Character Sets (No Excuses!)
• MySQL.com - Storage Engines
• MySQL.com - Character Set Support