Mysqli/PDO/Doctrine wel/geen prepared statements en character encoding

utf8mb4 is aan PHP zijde volgens mij gewoon utf8, alleen kan ie nu echt alle karakters uit de Unicode set opslaan (waar dat voorheen beperkt was tot de karakters met max 3 bytes).

Zelf zit ik ook in het "altijd alles escapen" kamp. Gewoon omdat je dan niet na hoeft te denken. Zelf werk ik met een soort DB wrapper die dit allemaal redelijk makkelijk voor me maakt.

Daar werk ik dan met named placeholders omdat ik er dan gewoon een array met variabelen achteraan kan gooien (zonder dat ze allemaal strict noodzakelijk zijn, of in de goede volgorde in de array zitten). Stel dat ik een array $user heb, met alle data voor de ingelogde gebruiker. Als ik dan een query wil draaien waarbij ik "een" waarde (of meer) uit deze array nodig heb geef ik 'm gewoon integraal mee bij de argumenten.

Andersom wordt een query vaak uit losse stukken samengesteld (afhankelijk van gemaakt keuzes). Placeholders komen dus niet altijd in de uiteindelijke query terecht. Met named placeholders hoef je je hier niet zo druk om te maken (gewoon meegeven, en de query wrapper zoekt wel uit of ie echt nodig is/was).

Merk op: met standaard PDO kom je hier niet zo makkelijk mee weg, omdat je geen argumenten mag binden die je vervolgens niet in de query gebruikt.

Rob Doemaarwat op 30/09/2019 19:49:41

utf8mb4 is aan PHP zijde volgens mij gewoon utf8, alleen kan ie nu echt alle karakters uit de Unicode set opslaan (waar dat voorheen beperkt was tot de karakters met max 3 bytes).

Bedankt voor je reactie Rob.

Gebruik jij de utf8mb4 set in mysql? Het nadeel is volgens mij dat utf8mb4 dan maar 180 karakters kan plaatsen in een VARCHAR. En moet je dan met mysqli_set_charset() utf8 opgeven of utf8mb4? Hoe belangrijk is het om utf8mb4 te gebruiken voor laten we zeggen Nederlands of Engels?

Rob Doemaarwat op 30/09/2019 19:49:41

Zelf zit ik ook in het "altijd alles escapen" kamp. Gewoon omdat je dan niet na hoeft te denken. Zelf werk ik met een soort DB wrapper die dit allemaal redelijk makkelijk voor me maakt.

Oke alles escapen om zeker te weten dat je je niet ergens per ongeluk vergist. Verder geen andere reden?
(Ik zeg niet dat het fout is maar ik wil dit draadje graag gebruiken om deze zaken zo veel mogelijk uit te diepen).

Rob Doemaarwat op 30/09/2019 19:49:41

Daar werk ik dan met named placeholders omdat ik er dan gewoon een array met variabelen achteraan kan gooien (zonder dat ze allemaal strict noodzakelijk zijn, of in de goede volgorde in de array zitten). Stel dat ik een array $user heb, met alle data voor de ingelogde gebruiker. Als ik dan een query wil draaien waarbij ik "een" waarde (of meer) uit deze array nodig heb geef ik 'm gewoon integraal mee bij de argumenten.

Dat vind ik interessant. je geeft gewoon een associatieve array mee en dan vergelijkt de wrapper de array keys met de named placeholders? Jouw wrapper gebruikt uiteindelijk PDO?

Rob Doemaarwat op 30/09/2019 19:49:41

Andersom wordt een query vaak uit losse stukken samengesteld (afhankelijk van gemaakt keuzes). Placeholders komen dus niet altijd in de uiteindelijke query terecht. Met named placeholders hoef je je hier niet zo druk om te maken (gewoon meegeven, en de query wrapper zoekt wel uit of ie echt nodig is/was).

Ik herken dat denk ik van filters op een listview. De basis SELECT query ga je dan uitbreiden met een WHERE claus met geen of één of meerdere ANDs afhankelijk van hoeveel filters er gebruikt worden. Dat deed ik dus in twee stappen. Als eerste bouwde ik de query string (met named placeholders) op en een array met de placeholders en de variabelen die dan met setParameters() doorgegeven kon worden. Maar nog steeds is de string die ik uiteindelijk door geef aan de prepare() functie één geheel.

* utf8mb4: Bij nieuwe databases (op zich ben ik nog niet tegen problemen aangelopen met plain utf8, maar ik zie dat steeds meer mensen emoji's in "normale" communicatie gaan gebruiken, dus kan het geen kwaad om er "klaar" voor te zijn).

* alles escapen: Ja, puur gemak. Ook bij teksten die je volledig zelf in de hand hebt, en waar nooooit een quootje in zal staan zul je zien dat er op den duur een quootje in komt ... (de "Kia Cee'd" was in mijn branche bijvoorbeeld een "leuke" eye opener).

* wrapper: Die gebruikt inderdaad PDO. Meestal heb je te maken met MySQL, maar soms komt er een ander "merk" voorbij (MS-SQL, Oracle), dus ik wil me niet vastpinnen op de mysqli extensie (en PDO en MySQL gaat prima).

<?php

//single row = assoc.array retourneren
$user = $db->single('select * from user where id = :id',['id' => 5]);
/* array(
  'id' => 5,
  'name' => 'Rob',
  'profile_id' => 21,
  ...
) */

//array met key = right.id en value = right.code
$rights = $db->record('
  select r.id,r.code
  from `profile_right` pr
    join `right` r on r.id = pr.right_id
  where pr.profile_id = :profile_id',
  $user //argumenten; hier zit dus veel meer in, maar in ieder geval 'profile_id'
);

?>

* samenstellen: Inderdaad, uiteindelijk houd je 1 stuk SQL over, maar omdat de inhoud en volgorde van de argumenten niet van belang is hoef je er niet zo panisch mee te doen.

<?php

$sql = 'select * from items i';
$where = $args = [];

if($args['foo'] = $_GET['foo'] ?? null) 
  $where[] = 'i.foo = :foo';
if($args['bar'] = $_GET['bar'] ?? null) 
  $sql .= "\n  join `other` o on o.id = i.other_id and o.bar = :bar";
if($args['enz'] = $_GET['enz'] ?? null) 
  $where[] = 'i.enz = :enz';

if($where) 
  $sql .= "\nwhere " . implode("\n  and ",$where);

$items = $db->all($sql,$args);

?>

Ff wat Googlen :).

utf8mb4 is aan PHP zijde volgens mij gewoon utf8

Waarschijnlijk bedoel je:

utf8mb4 in MySQL is aan de PHP zijde "equivalent" aan UTF-8

UTF-8 karakters bestaan maximaal uit 4 octets en utf8mb4 biedt hier ruimte aan:

> show character set where Charset like 'utf%';
+---------+------------------+--------------------+--------+
| Charset | Description      | Default collation  | Maxlen |
+---------+------------------+--------------------+--------+
| utf8    | UTF-8 Unicode    | utf8_general_ci    |      3 |
| utf8mb4 | UTF-8 Unicode    | utf8mb4_general_ci |      4 |
| utf16   | UTF-16 Unicode   | utf16_general_ci   |      4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci |      4 |
| utf32   | UTF-32 Unicode   | utf32_general_ci   |      4 |
+---------+------------------+--------------------+--------+

(maar let dus op het verschil tussen UTF-8 en utf8, deze zijn niet equivalent, het tweede artikel waar je naar linkt gebruikt deze twee termen nogal klakkeloos door elkaar...)

Dan bestaat er nog het idee dat VARCHAR nog rekent met bytes, maar vanaf MySQL versie 5 worden karakters gebruikt. Let wel op dat de totale mogelijke geheugenruimte voor VARCHAR vastligt (65.535 bytes). Dat bepaalt op zijn beurt hoeveel karakters deze (worst case) kan bevatten (ca. 16382).

Vervolgens heerst er nog steeds een misverstand over set_charset(). Je moet het als volgt zien. set_charset() is in wezen een contract tussen jouw PHP-applicatie en de database, die uit twee delen bestaat:
- enerzijds dien jij er zorg voor te dragen dat alle data die je vanuit je applicatie de database in schiet van deze voorgeschreven character encoding is, en
- anderzijds doet MySQL haar best om de data in de database in de voorgeschreven character encoding (terug) te serveren

Maar dit is niet noodzakelijkerwijs de character encoding van de gebruikte database-tabellen of -kolommen! Natuurlijk is het handiger dat alles qua character encoding in de pas loopt (en daar zou je dus ook naar moeten streven) maar dit is geen noodzakelijke voorwaarde. MySQL voert, zo goed en zo kwaad als dat kan uiteraard, zelf vertalingen uit tussen character encodingen als die ziet dat er een discrepantie bestaat tussen de definities en de waarde in set_charset(). En ja, als dat niet past kan dat resulteren in vraagtekens - karakters die niet ondersteund worden in de gewenste character encoding. Maar je zou dus prima een UTF-8 applicatie kunnen hebben die middels set_charset('utf8') communiceert met een sec latin1 database. latin1 is in zekere zin een "subset" van utf8 dus dit levert bij het uitlezen waarschijnlijk geen problemen op. Maar als je dus utf8 (of dus zelfs UTF-8) dingen probeert weg te schrijven in een latin1 database, dat is natuurlijk niet echt echt niet optimaal.

Toch ben ik wel een beetje nieuwsgierig waarom Thomas schrijft dat je gewoon alle DATA in je query moet escapen. Naar deze onderbouwing ben ik wel nieuwsgierig.

Heel simpel. Je wilt niet dat DATA als SQL geïnterpreteerd kan worden. Dat is namelijk de definitie van SQL-injectie. Alles wat je niet kunt escapen zou je via whitelists moeten laten verlopen (lijst van toegestane waarden). En dat valt eigenlijk gewoon onder validatie, die altijd, waar dat relevant is, plaats zou moeten vinden nog voordat je een query uitvoert. Als de validatie mislukt hoef je niet eens te proberen om een query te draaien en dat zou je dan dus ook nooit moeten doen.

Het eerste artikel waar je naar linkte gaat van simpel naar geavanceerd en legt uit wat de voordelen van prepared statements zijn. Maar hier kleven weer andere "nadelen" aan. De prepared statements variant van mysqli is wat mij betreft veel te "clunky".

Dan de PDO versie (waarbij de bovenstaande afbeelding weer min of meer van toepassing is). Dat lijkt allemaal simpel, een handjevol classes etc, maar de echte leercurve zit in de PDO_MYSQL driver, waar legio instellingen in zitten waar je vertrouwd mee dient te zijn. Daarnaast simuleert PDO standaard de "native" prepared statements voorziening die MySQL zelf heeft, dus dat zijn in wezen geen "echte" prepared statements. Wat op zich niet erg is, want de meeste queries herhaal je toch niet waarbij je 1x een template naar de database stuurt en vervolgens meerdere queries uitvoert met gebruikmaking van dat template.

De "gripes" die ik heb met PDO zijn als volgt:
- PDO is niet specifiek geschreven voor MySQLi, en als zodanig ook niet (out-of-the-box) geoptimaliseerd voor MySQL
- als je toch alleen maar van MySQL gebruik maakt in je applicatie, waarom zou je dan geen gebruik maken van een extensie die specifiek geschreven is voor MySQL (mysqli)
- debugging van queries; geen idee hoe dat gaat in mysqli+prepared statements / PDO? moet je dan je query log aanzetten en dan in je logs duiken om een (concrete) query op te snorren? f*ck that :)

Iedereen moet zelf maar weten wat ie gebruikt (choose your poison), maar met een eenvoudige wrapper om mysqli die werk uit handen neemt kom je echt al een heel eind. Wat je ook gebruikt, het is natuurlijk wel zaak dat je heel goed vertrouwd bent met de spelregels van de constructie die je gebruikt. En je kunt dan in principe altijd nog besluiten om deze wrapper te implementeren via PDO, of je gaat nog een stap verder en je gaat met Database Abstraction Layers aan de slag. Je kunt dan in principe steeds meer dingen doen op een abstract niveau, maar deze abstractie heeft ook een prijs (en mogelijk een snel afnemende meerwaarde). Voor een heleboel applicaties is die abstractie gewoon niet interessant (genoeg).

Ik zie Doctrine in de titel maar niemand die het gebruik ervan aanmoedigd? Als er één goede ORM is out there, dan is het wel Doctrine. Hoef je zelf niet overbodige wrappers te schrijven of in te zitten om data te escapen al dan niet.

Mijn tip: gebruik gewoon een reeds bestaande ORM, bij voorkeur Doctrine (of Eloquent). Zitten een hoop zaken in die zeker interessant zijn voor je webapplicatie.

Rob, bedankt voor de toelichting. Dus voor de emoji's zou ik kunnen kiezen voor utf8mb4... Het verhaal van die max 180 karakters in een VARCHAR blijkt niet op te gaan nu ik naar aanleiding van Thomas zijn reactie er nog eens ingedoken ben. Blijkt weer eens dat internet vooral vooral vol staat met onwaarheden en te oude informatie die al achterhaald is...

Thomas bedankt voor je uitgebreide reactie.

Thomas van den Heuvel op 01/10/2019 00:31:08

(maar let dus op het verschil tussen UTF-8 en utf8, deze zijn niet equivalent, het tweede artikel waar je naar linkt gebruikt deze twee termen nogal klakkeloos door elkaar...)

Deze begrijp ik even niet. UTF8 utf8 UTF-8 en utf-8... Waar zit het verschil in behalve dan in de hoofdletters en al dan geen minteken?

Thomas van den Heuvel op 01/10/2019 00:31:08

Dan bestaat er nog het idee dat VARCHAR nog rekent met bytes, maar vanaf MySQL versie 5 worden karakters gebruikt. Let wel op dat de totale mogelijke geheugenruimte voor VARCHAR vastligt (65.535 bytes). Dat bepaalt op zijn beurt hoeveel karakters deze (worst case) kan bevatten (ca. 16382).

Hier lees ik weer iets nieuws waarvoor dank. Echter begrijp ik van stackoverflow dat de 65.535 bytes de maximale lengte is van alle kolommen bij elkaar. Een soort maximale regellengte dus.

Over de set_charset() functie binnen PHP zou je dus kunnen zeggen dat als je database is ingesteld utf8 of utf8mb4 je utf8 meegeeft als parameter: <?php mysqli_set_charset($con,"utf8"); ?>

Dat de mysqli prepared statements een beetje klungelig in elkaar steken dat is nu wel duidelijk.

Thomas van den Heuvel op 01/10/2019 00:31:08

- debugging van queries; geen idee hoe dat gaat in mysqli+prepared statements / PDO? moet je dan je query log aanzetten en dan in je logs duiken om een (concrete) query op te snorren? f*ck that :)

In PDO kun je de exceptions natuurlijk zien als je ze niet opvangt. Bij mysqli blijf je telkens met onhandige if statements klooien zover ik het kan beoordelen. Of doel je hier op iets anders?

Jij gebruikt dus een eigen mysqli wrapper?

Jelle,
Fijn om te lezen dat er nog een Doctrine fan aanwezig is :-) Eloquent ken ik van naam maar ik heb er nog nooit naar gekeken. Doctrine daartegen ken ik ondertussen zeer goed. Dit komt denk ik vooral omdat ik graag met Symfony werk. Wel herken ik iets in het laatste stukje van Thomas zijn opmerking als we het over Doctrine hebben: Het feit dat je een prijs betaald voor een abstraction layer. Maar met alle voordelen daar tegenover vind ik Doctrine nog steeds erg fijn om mee te werken. Vooral omdat de data in een Entity (een class) wordt aangeleverd. En met doctrine ORM werk je erg makkelijk met prepared statements.

UTF8 utf8 UTF-8 en utf-8... Waar zit het verschil in behalve dan in de hoofdletters en al dan geen minteken?

UTF-8 is een ISO-standaard, utf8 is een interpretatie (subset) hiervan specifiek voor MySQL. Ging niet zozeer over case maar meer over het minteken.

Neemt niet weg dat je de case ook overal consequent zou moeten gebruiken, zoals:

<meta charset="UTF-8">

In een HTML-document maakt het niet zoveel uit, maar in andere gevallen (XML?) kan het mogelijk weer wel uitmaken. Als je hier eens op Googled zijn de resultaten min of meer "maakt niet uit / zou niet uit moeten maken, maar UTF-8 verdient de voorkeur boven utf-8". Als je verwarring kunt voorkomen door gewoon gebruik te maken van UTF-8 lijkt mij dat gratis winst.

Een soort maximale regellengte dus.

Precies, en hoeveel karakters je hier (theoretisch) in kunt proppen hangt van het maximaal aantal bytes af waar een karakter uit opgebouwd is. Als je niet tegen deze restricties aan wilt of dreigt te hikken kun je beter een ander kolomtype gebruiken die deze harde bovengrens niet heeft (of iig veel ruimer is).

In PDO kun je de exceptions natuurlijk zien als je ze niet opvangt. Bij mysqli blijf je telkens met onhandige if statements klooien zover ik het kan beoordelen. Of doel je hier op iets anders?

Ik bedoel meer: je kunt met prepared statements niet (of iig verre van makkelijk) direct zien welke query uiteindelijk wordt uitgevoerd. Misschien is dat inmiddels veranderd, maar prepared statements in mysqli ga ik niet gebruiken en in PDO moet (zou) je eigenlijk een heleboel (moeten) inregelen om dit intuïtief in MySQL te laten werken want zoals gezegd is PDO hier niet specifiek voor geschreven.

Jij gebruikt dus een eigen mysqli wrapper?

Ja, en als ik de keuze heb zou ik deze ook gewoon altijd gebruiken. Omdat deze gewoon het meeste rechttoe rechtaan is.

@Thomas: Bereid om deze te delen?
Ik zie vaak wat nuttige code van je in het forum komen die naar mijn idee prima in de scripts-database past.

Deze staat in principe al een tijdje op PHPhulp. Heb deze ondertussen wel wat uitgebouwd en uitgebreid met het tracken van (specifieke) queries en een wat andere aanpak qua transacties maar deze is in grote lijnen hetzelfde. Kan de laatste versie desgewenst hier plaatsen als men interesse heeft.

Mja, ik kan het je niet verplichten, maar je snapt hopelijk wel dat het forum niet echt de plek is waar iemand een kant-en-klaar script vindt. Juist daarvoor hebben we de scripts-pagina. ;-)
Dus als je daar het script wilt plaatsen, graag :-)