Scriptorama.nl

In het vorige artikel keken we naar de standaard manier waarop je met MySQLi kon werken. De nieuwe werkwijze verschilde eigenlijk niet zoveel. In dit artikel kijken we naar een andere manier waarop MySQLi queries kan verwerken: prepared statements.

Prepared statements

Met deze functionaliteit, beschikbaar vanaf MySQL 4.1, kun je de MySQL server van te voren aangeven wat je precies gaat doen. Dit biedt een paar voordelen.

Allereerst, en wellicht het belangrijkst, is dat deze methode van queries gebruiken veiliger is. Bij de oude methode, waarbij je een query string samenstelt en vervolgens aan MySQLi::query() voert moet je zelf erg opletten op de waarden die je in de query gebruikt. Je moet er voor zorgen dat bijvoorbeeld quotes ge-escaped zijn, anders zit je binnen notime met een gevalletje sql-injectie in je maag. De parameter verwerking bij prepared statements draagt zelf zorg voor het juist escapen van deze "gevaarlijke" karakters.

Ten tweede kan de performance verbeterd worden als deze query binnen het huidige script vaak uitgevoerd gaat worden. Denk aan een paar duizend INSERT statements. Een prepared statement wordt binnen MySQL geparsed en vervolgens voor de huidige connectie gecached, zodat na de query snel meerdere keren uitgevoerd kan worden. Er is ook een keerzijde: wanneer je een prepared-query slechts eenmaal uitvoert kan het zijn dat de overhead van het prepareren wat groter is dan dat je de query direct met MySQLi::query() zou uitvoeren.

Een eerste voorbeeld

Opmerking: Om het kort en duidelijk te houden ga ik er in deze voorbeelden er steeds vanuit dat er al een connectie met MySQL beschikbaar is via het object $link.

Je ziet meteen dat het er nogal anders aan toegaat dan met de "normale" queries. Om te beginnen prepareren we de SQL query met $link->prepare(). In de SQL query zie je vraagtekens staan op de plek waar je normaal zelf de waarden plaatst. Deze vraagtekens heten placeholders en zullen straks door MySQL zelf, veilig, ingevuld worden met onze data. Als alles goed is gegaan retourneert $link->prepare() een MySQLi_stmt object: $stmt, anders FALSE.

Vervolgens roepen we de methode $stmt->bind_params() aan. Dit is de methode die voor 1 uitvoer van de query de waarden gaat invullen. De eerste parameter ( in dit geval 'ss' ) is een string waarin elk karakter een data type definitie voor elke placeholder voorstelt. Zo weet MySQLi wat er moet gebeuren, en zo ben je escapenderwijs goed bezig. In dit geval geven we aan dat beide placeholders strings zullen bevatten. Je kunt de volgende data types opgeven:

• s: string
• i: integer
• d: double
• b: blob (voor grote data)

Het laatste wat dan nog moet gebeuren, is het daadwerkelijk uitvoeren van de query. Dit gebeurd met $stmt->execute(). Zoals ik al eerder zei, je kunt prepared statements meerdere keren uitvoeren, dit kun je doen door simpleweg de combinatie $stmt->bind_params() en $stmt->execute() meerdere keren aan te roepen, maar dan met verschillende data voor $stmt->bind_params().

Gegevens ophalen

Het ophalen van gegevens van een prepared statement gaat iets anders dan je misschien verwacht. Zoals je zag moesten we sowieso al de parameters binden aan de query, maar om de gegevens terug te krijgen zullen we ook de resultaten moeten binden:

Met $stmt->bind_result() geven we aan dat we de kolom id in de variabele $id willen hebben en de kolom username in de kolom $gebruikersnaam. We zullen iedere kolom die we willen ontvangen hier een variabele naam moeten geven. Dit werkt op de volgorde van kolomnamen uit je select query. Iedere keer als $stmt->fetch() wordt uitgevoerd en deze TRUE retourneert zullen de variabelen $id en $gebruikersnaam gevuld worden met de database gegevens.

Dit lijkt misschien wat omslachtig, maar is opzich best wel okay. Een van de dingen die vaak worden gezegd om performance te verbeteren is dat je er voor moet zorgen dat je alleen de kolommen selecteert die je nodig hebt. Veel mensen lappen dit aan hun laars en doen gewoon SELECT * FROM tabel. Deze constructie zorgt er, althans bij de luie programmeurs onder ons, wel voor dat je ook alleen de benodigde kolommen zult selecteren. Anders zou je wel eens een hele berg typwerk kunnen moeten verrichten om ze allemaal in die $stmt->bind_result() aanroep te krijgen ;-)

Gegevens opslaan

Om gegevens op te slaan doe je in principe gewoon het zelfde als bij een SELECT query. Het enige verschil voor een INSERT is dat er geen gegevens worden geretourneerd en dat je een eventuele automatisch gegenereerde ID kunt benaderen via $stmt->insert_id:

Bij het updaten van gegevens is niet zoveel bijzonders. Er wordt ook geen data geretourneerd en het aantal daadwerkelijk gewijzigde rows vind je via $stmt->affected_rows:

Dat is overigens nog eens iets om een halfuur aan kwijt te raken; dit gebeurt zowel in MySQL 3, 4 als 5: MySQL zal alleen een waarde geven voor "Affected Rows" als er daadwerkelijk gegevens worden gewijzigd. Hebben de kolommen reeds de waarden waar je naar toe wilt veranderen, dan zal je de waarde 0 terug krijgen. Er is echter niets fout gegaan in de database, dus je kunt $stmt->affected_rows niet gebruiken om te controleren of alles goed is gegaan. Als er iets fout is gegaan zul je dat merken aan $stmt->execute() die FALSE retourneert.

Conclusie

Prepared statements kunnen je in enkele gevallen wat extra performance geven maar in elk geval er voor zorgen dat je minder kopzorgen hoeft te hebben over de data die je in queries laadt. Dit houdt natuurlijk niet in dat je je data niet meer hoeft te valideren.