Door
Onbekende gebruiker
op 02-11-2015 12:01
gewijzigd op 02-11-2015 12:02
2.011 views
Ik heb een zelfgemaakte database-class die de queries die mijn appje nodig heeft genereert met MySQLi.
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)
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?
[size=xsmall]Toevoeging op 02/11/2015 12:09:03:[/size]
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.
hoezo kan de client bepalen in welke tabel er geschreven gaat worden?
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.
Ik ben het denk ik eens met Ivo. Bij het uitvoeren van (SELECT) queries moet je er in zekere zin van uit kunnen gaan dat tabellen/kolommen bestaan.
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?
?
Onbekende gebruiker
02-11-2015 14:50
Het gaat om een wat uitgebreidere database class. Het escapen van identifiers is overigens ook ter preventie van injection.
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:
SELECT to_regclass('schema_name.table_name');
Het handige is dus dat het er dan zo uit moet komen te zien:
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:
// $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());
Geeft foutmelding:
PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number
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 kun je een tipje van de sluier oplichten mbt de vraag waarom jouw tabellen variabel zijn en waarom er geen zekerheid kan zijn of de tabelnaam veilig is en of hij überhaupt bestaat.
?
Onbekende gebruiker
02-11-2015 15:29
De beheerder moet via dezelfde webinterface gewoon bepaalde DDL-taken kunnen uitvoeren. Het is gekoppeld aan bepaalde data sources waarvan niet vaststaat wat de kolomtypes zullen zijn, laat staan dat er een volledige data dictionary is van de vele honderden tabellen. Mijn appje voorziet hierin met datakwaliteitsfuncties.
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.
" Het escapen van identifiers is overigens ook ter preventie van injection."
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?
?
Onbekende gebruiker
02-11-2015 16:45
gewijzigd op 02-11-2015 16:54
Je hebt gelijk Vincent, soms moet je aan zoveel zaken tegelijk denken dat je door de bomen het bos niet ziet. Juist vanwege die database class hoefde ik zelden over injection na te denken. Dat wordt dan pg_escape_identifier(), of beschermt die daar niet tegen?
En dit kan/mag overigens wel gewoon:
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.
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? :-)
?
Onbekende gebruiker
02-11-2015 16:59
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.
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)
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 :).
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...
