escapen van sommige strings lukt niet met PDO
Nu wil ik die class ombuigen van MySQLi naar PostgreSQL. PHP heeft daarvoor een keuze voor twee opties, pgsql en PDO. Beiden heb ik nog niet eerder gebruikt. Ik beschik dus ook over de meest recente PostgreSQL 9.4.5.
Op het eerste gezicht ben ik wel gecharmeerd van PDO, maar nu loop ik tegen het volgende aan:
Om queries veilig te kunnen genereren dingen moeten schema-, tabel- en kolomnaam kunnen worden ge-escape-d, en dat gaat met PostgreSQL net anders dan met literals. Dientengevolge zijn er in pgsql verschillende functies voor:
- pg_escape_string(), pg_escape_literal() en pg_escape_bytea() voor literals en binaire data
- pg_escape_identifier() voor escapen van identifiers (schema-, tabel- en kolomnaam)
Echter, als ik die functionaliteit in PDO zoek dan lijkt die niet te bestaan?
Uiteraard ben ik niet de eerste met die vraag, en hij is al eerder beantwoord op deze plek:
http://stackoverflow.com/questions/13448274/escaping-column-name-with-pdo
Mijn vraag voor PHP-hulp: wie gebruikt PDO met PostgreSQL en heeft hier dus ervaring mee? Ben ik inderdaad aangewezen op pgsql? (geen ramp verder) of is het toch gewoon met PDO op te lossen?
Toevoeging op 02/11/2015 12:09:03:
Kleine toevoeging: de reden waarom ik niet zit te springen op de whitelisting-oplossing op StackOverflow is dat ik hem vrij omslachtig vind, daarnaast check ik ook van te voren of een tabel bestaat* en het zou omslachtig worden om voorafgaand aan elke query een whitelist te bouwen om daarop te checken.
* http://stackoverflow.com/questions/20582500/how-to-check-if-a-table-exists-in-a-given-schema (gelukkig kan ik al van 9.4 gebruik maken)
Of schiet je een beetje door in het generiek maken van je databaseclass?
Als een class zoals bijvoorbeeld Users query's maakt, dan spreekt hij daarbij normaal de tabel "user" aan. (of "users" zo je wilt).
Maar als die class ineens besluit om een tabel "Gebruikers" aan te spreken, en deze bestaat niet, dan is een harde error op z'n plaats. Die situatie mag niet voor kunnen komen.
Enige situaties waarbij ik me dit voor kan stellen:
a) een query die uitzoekt of de database bij een update van de software is bijgewerkt.
b) situatie waarbij elke gebruiker een eigen tabel krijgt ipv een row in de tabel User, zoals hier laastst iemand aan het bouwen was op PHPhulp, maar dan ben je op de verkeerde weg.
Eindgebruikers zullen zich meestal niet direct bezighouden met de database-structuur, meestal wil men dingen doen met de DATA. Daarbij regelt PDO haar DATA-escaping normaliter via de prepared statement laag?
Als je een soort van zoek/filter-functionaliteit hebt lijkt mij een soort whitelist-constructie op zijn plaats.
Maar uit je verhaal klinkt het alsof je meer bezig bent met structuur, en de manipulatie daarvan.
Misschien als je de kijkers thuis uitlegt wat je probeert te bereiken?
Maar het moet ook mogelijk zijn om te kunnen bepalen of een tabel bestaat in PostgreSQL. Volgens eerdergenoemde link is de meest eenvoudige weg vanaf 9.4 deze:
Code (php)
1
SELECT to_regclass('schema_name.table_name');
Het handige is dus dat het er dan zo uit moet komen te zien:
Code (php)
1
SELECT to_regclass('"mijnschema"."mijntabel"');
En ik ga er van uit dat datzelfde nog wel bij andere functies nodig zal zijn.
In de applicatie zijn in ieder geval de tabelnamen variabel, en dan loop ik met PDO tegen een eigenaardig issue aan, namelijk dat tussen quotes van een SQL-query überhaupt geen variabelen vervangen worden met een prepared statement.
Voorbeeld:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
// $this->oPDO is geinitialiseerd met een verbinding naar PostgreSQL 9.4.5
// schema- en tabelnaam bestaan in de database
// PostgreSQL versienummer, tijdzone en encoding is gecontroleerd
$sSchema = 'mijnschema';
$sTabel = 'mijntabel';
$sSqlBestaatTabel = "SELECT to_regclass(':sSchema.:sTabel') IS NOT NULL;";
$oStmtBestaatTabel = $this->oPDO->prepare($sSqlBestaatTabel);
$oStmtBestaatTabel->execute(array(
'sSchema' => $sSchema,
'sTabel' => $sTabel,
));
var_dump($oStmtBestaatTabel->fetchAll());
// schema- en tabelnaam bestaan in de database
// PostgreSQL versienummer, tijdzone en encoding is gecontroleerd
$sSchema = 'mijnschema';
$sTabel = 'mijntabel';
$sSqlBestaatTabel = "SELECT to_regclass(':sSchema.:sTabel') IS NOT NULL;";
$oStmtBestaatTabel = $this->oPDO->prepare($sSqlBestaatTabel);
$oStmtBestaatTabel->execute(array(
'sSchema' => $sSchema,
'sTabel' => $sTabel,
));
var_dump($oStmtBestaatTabel->fetchAll());
Geeft foutmelding:
PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number
Dit gedrag komt overeen met het commentaar van gebruikers op http://php.net/manual/en/pdo.prepare.php .
Van PDO ben ik gecharmeerd vanwege de cross-database aanpak, maar het leidt kennlijk ook tot oversimplificatie. Dus is mijn voorlopige conclusie dat PDO voor deze toepassing minder handig is dan de pgsql-extentie.
Misschien zul je nu wel denken ,,Maar jij weet dus van te voren niet precies wat je met de data moet gaan doen?'' en dat klopt inderdaad. Het zijn van die dingen die ik wel eens tegenkom bij organisaties met een eilandjescultuur. Dan hebben ze zelf iets opgetuigd en 10 jaar later zijn de sleutelfiguren weg en blijf je met gebrekkige overdracht achter.
Als je een identifier voor een tabelnaam escapet dan word de tabelnaam gewoon gelijk aan de ge-escapte string, en dat wil je *absoluut* niet, want dan krijg je een tabelnaam als "WHERE 1 = 1" (even los van dat dat niet mag), of met ge-escapete quotes erin. Die ga je nooit meer terugvinden en boem daar gaat je database.
Een schema/tabel/kolomnaam moet aan een bepaald formaat voldoen en dat kun je prima controleren met een regexp, daar heb je geen escape voor nodig. Escapen is uitsluitend bedoeld om een string die SQL bevat in een query te kunnen zetten zonder dat de queryparser de SQL in die string interpreteert als SQL.
"Laat staan dat er een volledige data dictionary is van de vele honderden tabellen."
Je weet van het bestaan van het information-schema, waar een volledige dictionary in staat van alle tabellen?
En dit kan/mag overigens wel gewoon:
Code (php)
1
create table "test" ("WHERE 1=1" text);
Overigens heeft het information schema weinig zin als een van de tussenstations van bron naar DHW een Access database is die data aangeleverd krijgt als TSV. Access gokt dan wat de kolomtypen zijn (op basis van de eerste regels data?). Het enige dat je dan hebt zijn de afkortingen van tabelnamen en kolomnamen waarvan je ook niet altijd weet wat nou precies wat is.
An tje op 02/11/2015 16:45:35:
Overigens heeft het information schema weinig zin als een van de tussenstations van bron naar DHW een Access database is die data aangeleverd krijgt als TSV. Access gokt dan wat de kolomtypen zijn (op basis van de eerste regels data?). Het enige dat je dan hebt zijn de afkortingen van tabelnamen en kolomnamen waarvan je ook niet altijd weet wat nou precies wat is.
Tsja dat is de algehele malaise bij het werken met datafeeds (want dat is feitelijk wat je doet als je data uit excel importeert).
In het verleden heb ik deze ellende altijd opgelost door een mapping te maken tussen de kolomnaam uit de feed en de kolomnaam uit de database. Degene die de feed configureert krijgt vanuit het information schema een setje kolommen (met de beschrijving die je in PgSQL kunt opslaan per kolom) en kiest welke kolom uit de feed het best past bij welke database kolom.
Vervolgens de data inlezen met COPY naar een tijdelijke tabel, en via UPDATE/INSERT de data op de juiste plek zetten.
Als degene die de feed aanbiedt ook niet weet waar de data over gaat dan houdt het op, maar wie wil er nu een bestand inlezen waarvan hij niet weet wat er in staat? :-)
Quote:
In de applicatie zijn in ieder geval de tabelnamen variabel, en dan loop ik met PDO tegen een eigenaardig issue aan, namelijk dat tussen quotes van een SQL-query überhaupt geen variabelen vervangen worden met een prepared statement.
Dat is niet eigenaardig, dat is hoe prepared statements werken :). In het algemeen zijn parameters alleen toegestaan in Data Manipulation Language (DML) statements en niet in Data Definition Language (DDL) statements. Ze mogen ook niet gebruikt worden als identifiers, als lijst van kolommen volgend op SELECT of om beide operanden van een binaire operator te specificeren (zoals bij het = teken). (Als ik de werking van prepared statements in PDO mag vergelijken met die van MySQLi)
Quote:
Van PDO ben ik gecharmeerd vanwege de cross-database aanpak, maar het leidt kennlijk ook tot oversimplificatie.
De toegevoegde waarde van een cross-database aanpak is 0,0 als je geen database abstraction layer gebruikt. En zelfs dan zijn databases niet zomaar vrij uitwisselbaar. Het enige goede argument wat ik eigenlijk ooit gehoord heb voor het gebruik van PDO is wanneer je een applicatie aan het bouwen bent, en je er nog niet helemaal over uit bent welk database-type je wilt gaan gebruiken. Volgens mij noemde deze persoon dat "green field testing" of iets dergelijks. En dan zul je nog steeds database-specifieke code / queries / functionaliteit moeten schrijven ergens :).
Quote:
Dus is mijn voorlopige conclusie dat PDO voor deze toepassing minder handig is dan de pgsql-extentie.
PDO an sich is niet geschreven voor een specifieke database. De PDO drivers zorgen voor database-specifieke implementaties die de werking van de database zo goed/dicht mogelijk proberen te benaderen. Deze sluiten haast per definitie minder goed aan met de database-variant in kwestie dan functies/methodes die specifiek geschreven zijn voor die variant...
Quote:
regexp
A.k.a. whitelist :D.
Quote:
Dat is niet eigenaardig, dat is hoe prepared statements werken
Hm, en ik maar hopen dat het ook voor DDL geschikt zou zijn. Het ziet er naar uit dat je daarin gelijk hebt, als ik bv. kijk naar het PREPARE statement in PostgreSQL (http://www.postgresql.org/docs/9.4/static/sql-prepare.html) dan mag een statement alleen zijn: "Any SELECT, INSERT, UPDATE, DELETE, or VALUES statement."
Ik blijf het vreemd vinden dat een functie als pg_prepare() dat niet aangeeft:
Code (php)
1
$rResultaat = pg_prepare($rPgsql, "myquery", 'SET SCHEMA $1');
Werkt prima, totdat de query voorbereid moet worden door PostgreSQL:
pg_prepare(): Query failed: ERROR: syntax error at or near "$1"
Kan ik hem gelukkig toch nog toeschrijven aan de pgsql-extentie voor het niet tijdig afvangen van statements anders dan "SELECT, INSERT, UPDATE, DELETE, or VALUES". Want je raad het al, zonder parameters doet het prepared statement het wel... (?)
Code (php)
1
$rResultaat = pg_prepare($rPgsql, "myquery", "SET SCHEMA 'mijnschema'");
Is dat dan een bug? Gooit pg_prepare() het zomaar over de schutting bij PostgreSQL?
Ik denk dat ik maar aan moet wennen dat ook prepared statements hun limitaties kennen. Maar zo is het leven van een eenvoudige ontwikkelaar.
An tje op 02/11/2015 16:59:40:
Soms is de data belangrijk genoeg, ook al weet je van te voren nog niet precies hoe het is georganiseerd. Dat is een lang proces dat ikzelf niet hoef te doen gelukkig, ik wil alleen maar de tooling maken.
Het klinkt alsof je meer hebt aan een NoSQL oplossing, daarin maakt de structuur van de data niet uit en heb je al het gedoe met kolomnamen niet meer.
PostgreSQL kan dat ook, via HSTORE en BSON; dan sla je elke rij gewoon op 'as is' en als iemand laeter denkt te hebben achterhaald waar de data voor is dan kan hij de data herstructureren.
Dit klinkt ook als een geval waar je met tekst-klassificatie aan de gang moet; onbekende data vergelijken met bekende data om te zien hoeveel daarvan overeenkomt qua inhoud en formaat. Huisummers zitten altijd tussen de 1 en 2000, postcodes hebben altijd 4 cijfers en eventueel twee letters als het een Nederlandse postcode is, en straatnamen zijn er maar een stuk of 20.000 dus daarvan moet een hele bups overeenkomen, met als bij steden.
Iets vergelijbaars moet ook wel te doen zijn voor andere datasoorten waarvan je een bekende set hebt.