Scriptorama.nl

In de vorige 3 MySQLi artikelen hebben we het gehad over het basis gebruik van MySQLi, prepared queries en unbuffered queries. In dit artikel zullen we zien hoe je met transacties kunt werken en hoe je een beveiligde verbinding kunt opzetten met MySQL.

Net als bij de vorige artikelen zullen we de OO interface voor MySQLi gebruiken in plaats van de procedurele interface. Voor elk van de methodes die we in dit voorbeeld gebruiken is een procedurele tegenhanger. Mocht je dus toch liever de procedure API gebruiken, dan kun je alle code vrij gemakkelijk omzetten.

Transacties gebruiken

Voor degene die niet precies weten wat transacties zijn, even het verhaal in de notendop: transacties maken het mogelijk om meerdere, gerelateerde, queries in een blok uit te voeren. Mocht er tijdens de uitvoer van dit blokje queries iets foutgaan, dan kun je alle wijzigingen uit dit blok in 1x terug draaien (ROLLBACK) of in 1x definitief wegschrijven (COMMIT). Wel zo veilig voor de integriteit van je gegevens, dus.

Bij MySQL is het wel zo dat niet alle tabel types transacties ondersteunen, vooralsnog is dit voornamelijk InnoDB maar straks met MySQL 6.0 komen daar de door MySQL zelf ontwikkelde Falcon en Maria storage engines bij.

In dit voorbeeld zullen we werken met een erg simpele tabel:

Om met transacties te werken heeft de MySQLi extensie de methodes mysqli::autocommit(), mysqli::rollback() en mysqli::commit():

De output van dit script is:

Als je eerder met transacties gewerkt hebt had je misschien nog een $mysqli->begin() verwacht. Deze is niet nodig door het gebruik van mysqli->autocommit(FALSE). Door deze aanroep worden alle queries in feite direct in een transactie blok uitgevoerd. Je zult altijd mysqli::commit() of mysqli::rollback() moeten aanroepen voordat er iets gebeurd met de queries die je hebt opgegeven.

Wanneer je toch liever zelf in de hand hebt over wanneer je een transactie gebruikt, dan kun je ook gewoon de transaction SQL statements gebruiken:

Het voordeel van deze methode is dat eventuele enkele los staande INSERT/UPDATE queries tussendoor wel direct worden uitgevoerd zonder dat je iedere keer een COMMIT commando hoeft te geven.

Verbinding leggen met een met SSL beveiligde MySQL server

Met MySQL 5.0 is het mogelijk om gebruikers via een met SSL beveiligde verbinding te laten werken. Dit kan handig zijn om mensen vanaf buiten toch mysql toegang te geven tot de server. Met MySQLi is het relatief eenvoudig om een connectie te leggen met een MySQL server via SSL.

Voordat je verbinding kunt leggen met een MySQL server via SSL zul je eerst een client private key & certificaat moeten genereren. Daarbij heb je het certificaat van de uitgever van het client certificaat nodig. Hoe je deze kunt genereren staat uitgelegd in de MySQL handleiding: SSL certificaten genereren.

Als je alle certificaten hebt gegenereerd, volgens de handleiding hierboven, kopieer je je client private key (client-key.pem), je client certificaat (client-cert.pem) en het certificaat van de issuer (ca-cert.pem) naar een directory van jouw website.

Let op: het is natuurlijk wel belangrijk dat je deze niet kopieert naar een directory die via het web bereikbaar is!

Vervolgens kun je de volgende code gebruiken om je verbinding op te zetten:

Allereerst gebruiken we mysqli::init() om het object zo te configureren dat het gebruikt kan worden in combinatie met mysqli::real_connect(). Vervolgens configureren we het MySQLi object voor SSL gebruik door MySQLi::ssl_set() aan te roepen.

Aan MySQLi::ssl_set() geef je het pad naar de client private key (client-key.pem), het client certificaat (client-cert.pem) en het certificaat van de issuer (ca-cert.pem). De laatste 2 argumenten zijn het pad naar het issuer certificaat (deze blijkt optioneel) en het cipher dat je wilt gebruiken, maar mysqli kan er zelf ook een kiezen. Vervolgens gebruiken we mysqli::real_connect() in plaats van mysqli::connect() om de daadwerkelijke verbinding op te zetten.

De naamgeving van deze methode vind ik zelf wat vreemd (real_connect, je weet wel, in tegenstelling tot fake_connect) maar het idee erachter lijkt te zijn dat de methodes zonder "real_" ervoor het gedrag vertonen zoals je dit al gewend bent van de MySQL extensie, terwijl de methodes die wel geprefixed zijn met "real_" extra functionaliteit bieden.

Aan de server kant is het natuurlijk dan wenselijk om voor een bepaalde of misschien wel alle gebruikers SSL af te dwingen. Dit kun je doen door de REQUIRE SSL optie mee te geven in een GRANT statement:

Veiliger is natuurlijk om de exacte certificaat gegevens - deze vul je in tijdens het genereren van de certificaten - te vereisen. Dit kun je doen met de REQUIRE SUBJECT en ISSUER opties voor GRANT:

Een volledige uitleg vind je natuurlijk in de GRANT pagina in de handleiding.