Google+ Followers

zondag 22 september 2013

Quoting integers in SQL, yes or no?

If your database let's you quote numeric values then handle them just like you would handle a string.

The single biggest reason why you should: Risk management.

Not quoting numeric values means each and every numeric value that you give to the database must be validated. Every one, every where, every time. That may sound easy but it's almost impossible to do. validations are a nuissance to programmers, they are added last, and if there is a deadline looming then validations are the first thing to be skipped because "well, what are the chances..."

Escaping and quoting on the other hand can be automated. Most software, especially frameworks, already have classes and methods to run all the queries in the appication. for example:

$objDatabase->query("INSERT INTO foo (bar) VALUES ($1)", array("hello"));

With this setup you can just loop over the array of values and escape and quote them all.
That is a 100% thing, none of the values are left open to injection.



"But developers can bypass this routine!"
True, they can decide to write their own database access code, just like they can decide not to do any validation.
But what is more likely; completely ignoring the existing framework, or forgetting to validate a variable?


"But quotes define strings, not numbers!"
In a query the quotes only define data boundries. The parser uses quotes to work out where the values begin and end, in case the values contain text that could be misinterpreted as part of the query grammer, it has nothing to do with datatypes.

"If you quote a number the database will read it as a string and has to re-cast it to a number"
The database doesn't cast anything, anywhere. It parses the query, picks up the values and verifies that whatever the value represent is valid for the target column or operation.

The only thing that happens that resembles casting is that the characters from the query are transformed into a binary value that the database stores in the actual table, and that happens for all the values in the query, quoted or not.


The only legit argument I've found against quoting numbers is that it requires escaping and escaping may require a roundtrip to the database. It's a small price to pay for your safety, but if you want to bitch about something; this is it.

maandag 9 september 2013

PostgreSQL 9.3 uitgekomen.

Feestgedruis, toeters en bellen!

PostgreSQL 9.3 is uit en we zijn blij.

Waarom?
De JSON functies zijn aanzienlijk uitgebreid zodat je nu ook JSON obecten kunt parsen, doorzoeken, genereren en indexeren.
Schrijfbare Foreign-Data-Wrappers, dus nu kun je ook in je MySQL database of CSV file gaan schrijven.
Event triggers, triggers die afgaan op DDL veranderingen zoals CREATE en ALTER. Eindelijk gewoon monitorren wat je "lieve collega's allemaal aanpassen in je database.
En een aantal verbeteringen in de replicatie, waaronder snellere failover.

Wat is er nieuw in 9.3: https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.3


zaterdag 7 september 2013

Hoeveel beveiliging is genoeg?

"Deze site is niet zo belangrijk dus de beveiliging hoeft niet zo goed te zijn."

Herken je die? En herken je jezelf erin? Hoe weet je wanneer data belangrijk genoeg is om "goed" te beveiligen? En hoe goed is "goed" dan?

Inderdaad, het is een zin die je niet kunt interpreteren omdat je van geen enkel ingredient kunt definieren wat het precies inhoud.

De plaatselijke klaverjasclub zal het ongetwijfeld worst zijn of de wedstrijduitslagen worden gehackt, dat is jammer maar zo hersteld en niemand is er slechter van geworden. Het wordt een heel ander verhaal als een paar dagen later de bankrekening van een van de leden is geplunderd omdat de beste man het wachtwoord van de klaverjas website ook had gebruikt voor zijn internetbankier account. Natuurlijk was het niet slim van hem om dat te doen, maar mensen doen dat nu eenmaal, ja, ook u, lieve lezer, hebt een aantal wachtwoorden die voor meerdere dingen gebruikt worden en geef toe, sommige zouden eigenlijk al jaren veranderd moeten worden.

Een maand later blijkt dat een andere website dertienduizend creditcardnummers kwijt is geraakt en dat de hackers zijn binnengekomen via een IP adres binnen het privenetwerk van de hoster, door een beveiligingslek in een website die in hetzelfde segment stond. Raad eens welke site dat was...

Hackers zijn niet dom, ze zijn aanzienlijk slimmer dan de gemiddelde webmaster en zij weten dat veel webmasters denken dat hun site niet zo belangrijk is en daarom de beveiliging niet goed voor elkaar maken.

De ironie is dat "goed" beveiligen helemaal niet ingewikkeld is dus waarom er naar smoezen wordt gezocht om het niet te doen is voor mij een raadsel.



woensdag 4 september 2013

Series tellen.

Een van de mooiste aspecten van SQL vind ik dat je regelmatig oplossingen tegenkomt die zo simpel lijken dat je jezelf afvraagt waarom je er zelf niet op gekomen bent.

Het volgende probleem deed zich voor; een statisticus heeft een serie lotto uitslagen. Elke uitslag bestaat uit zes cijfers van 1-9 en zijn in de database opgeslagen als een tabel met zes kolommen, waarin de waarden voor het gemak zijn opgeslagen in oplopende volgorde, dus de eerste kolom bevat altijd het laagste getal van de trekking en de laatste kolom het hoogste getal.

Vraag: hoe kun je achterhalen hoevaak elke serie van drie getallen voorkomt in de totale set?

Dus niet "hoe vaak komen alle mogelijke combinaties van drie getallen voor", maar "1,3,5,6,8,9" bevat "3,5,6", en "3,5,6,7,8,9" bevat ook "3,5,6", en "1,2,3,5,6,7" bevat ook "3,5,6" dus "3,5,6" komt in deze set trekkingen drie keer voor.

Het feit dat de getallen direct op elkaar moeten volgen lijkt in eerste instantie wellicht een extra moeilijkheid maar het maakt het juist eenvoudiger omdat er in een combinatie van zes getallen maar vier mogelijkheden zijn waarop zo'n combinatie kan voorkomen; immers alleen de eerste vier getallen kunnen het begin van een serie van drie zijn, daarna zijn er gewoon niet meer genoeg getallen.

Het is dus zeer goed mogelijk om alle combinaties van kolommen uit te schrijven, de getallen te combineren tot een string die uniek is per combinatie, en vervolgens te tellen hoeveel van die strings er zijn.

Voor het combineren van de kolommen gebruik ik hier domweg stringconcatenatie met een spatie. Om alle gevonden combinaties tegelijk te krijgen worden alle combinatiequeries aan elkaar geplakt met een UNION ALL, immer; UNION filtert dubbelen weg en dan tel je alleen het aantal unieke combinaties in plaats van het aantal keer dat elke combinatie voorkomt. Aan het eind wordt er op de combinaties gegroepeerd en een simpele COUNT(*) vertelt hoevaak elke combinatie in de set zat.


CREATE TABLE trekkingen (trekkingid SERIAL, bal1 int, bal2 int, bal3 int, bal4 int, bal5 int ,bal6 int);
INSERT INTO trekkingen VALUES (1, 2,3,5,6,7,8), (2, 1,4,6,7,8,9), (3, 1,5,6,7,8,9),(4,1,4,6,7,8,9);

SELECT combinatie, COUNT(*) AS aantal
FROM
(
SELECT (bal1 || ' ' ||bal2 || ' ' || bal3) AS combinatie FROM trekkingen
UNION ALL
SELECT (bal2 || ' ' ||bal3 || ' ' || bal4) AS combinatie FROM trekkingen
UNION ALL
SELECT (bal3 || ' ' ||bal4 || ' ' || bal5) AS combinatie FROM trekkingen
UNION ALL
SELECT (bal4 || ' ' ||bal5 || ' ' || bal6) AS combinatie FROM trekkingen
) AS combinaties
GROUP BY combinatie;