Keskustelu: Nettisivujen teko: sql haku, bruteforce, aikaikkuna

Moro,

Yritän saada tekemääni kirjautumisjärjestelmää varmemmaksi ja aloin kehittämään pikku koodinpätkää mikä tunnistaa bruteforcettamiset ja ilmoittaa niistä minulle..

Aina kun kirjautuminen epäonnistuu niin 'bruteforce' tauluun lisätään rivi:
id, name, ip, host, timestamp

name = käytetty tunnus millä yritettiin
host = gethostbyaddr
timestamp = CURRENT_TIMESTAMP

Varsinainen ongelma tässä on aikaikkuna ja sql-haun laatiminen.
Eli jos samasta ip:stä tulee ensimmäisen yrityksen jälkeen kaksi lisää seuraavan 10 minuutin aikana niin haku "onnistuu".

Pelkistetty esimerkki taulu:
1 | tauno | samaip | 18:00
2 | tauno | samaip | 18:04
3 | tervo | samaip | 18:11
4 | tauno | samaip | 18:12

Esimerkki taulusta ensimmäinen rivi ei aiheuta hälytystä koska kolmas rivi on liian kaukana mutta toinen rivi aiheuttaa hälytyksen koska kaksi seuraavaa samasta ip:stä tullutta yritystä osuu 10 minuutin sisään.

Miten tälläinen haku muodostuu?
Vai olenko täysin väärillä jäljillä tämän toteutustavasta?

- t0ll0

edit,
ja kuinka raskas kyseinen haku on kun suoritetaan admin tasolla joka sivulatauksella?

select count(*) from logintries where ip=..gethostbyaddr.. and timestamp>DATE_SUB(CURRENT_TIMESTAMP,INTERVAL 10 MINUTES)

Jos tulos on 2 tai suurempi niin blokataan.

Jos tulevaisuudessa joku tätä lukee niin Grezin query toimii mutta loppuosaan muutos INTERVAL 10 MINUTE eli ilman S-kirjainta :)

Eli haku toimii kyllä mutta se hakee tästä hetkestä kymmenen minuuttia taaksepäin..
En aina tule olemaan kirjautuneena järjestelmään ja hainkin keinoa hakea kannasta rivit mikäli vaikkapa viikko sitten joku on yrittänyt kirjautua kolme kertaa kymmenen minuutin sisään siinä onnistumatta.

Pelkään että hausta tulee kamalan raskas kun joudutaan vertailemaan rivien aikaleimoja keskenään?

-t0ll0

Mihin tarvitset tuota historiasta hakemista? Eikö riitä, että käsittelet asian jo virheen yhteydessä Grezin kyselyllä?

Älä mielellään laita ikuista blokkausta. Suuri osa maailmasta toimii dynaamisilla IP-osoitteilla ja kirjautumisvirheitä voi sattua mm. lasten ja kissojen takia. Vaikkapa 5-merkkisen salasanan bruteforcetus vaatii niin monta yritystä, että jo 5 minuutin blokkaus kolmesta virheestä riittää varsin hyvin ja lisätoimia voi harkita sitten, jos yrityksiä tästä huolimatta satelee monta kymmentä vuorokaudessa. Voit siis laittaa Grezin kyselyn yksinkertaisesti kirjautumisen yhteyteen, ei minua ainakaan kiinnostaisi lukea jotain bottien tuottamia lokimerkintöjä. Kokonaismäärän eri päiviltä voi tarkistaa näin:

SELECT COUNT(*) AS virheita FROM loki WHERE ip = ? GROUP BY DATE(aika) HAVING virheita > 20

Minua kiinnostaa tietää mitä sivuillani tapahtuu ja siihen tarvitsen historiasta hakemista..
Tuli mieleen että voin tietenkin grezin kyselyä apuna käyttäen laittaa kirjautumisen yhteydessä toiseen tauluun ilmoituksen bruteforceista jota sitten haistelen. Eli toimisi "historia" tauluna.

Alkuperäinen idea ei sisällä minkään pituista blokkausta mutta tuo viiden minuutin blokkaus kolmesta virheestä kuulostaa optimaaliselta.

Eli saan homman toimimaan haluamallani tavalla näillä keinoin :)

Kiitän!
-t0ll0

ps, ja nyt kun luin alkuperäisen viestini niin heikosti siitä tosiaan selviää mitä varsinaisesti haen..
"tunnistaa bruteforcettamiset ja ilmoittaa niistä minulle"
Mutta näillä mennään!

Niin tosiaan vastasin miten ratkaista tuo kertomasi bruteforce-ongelma, eli rajoittaa yritysnopeus max 3 yritykseen 10 minuutissa. Jos nyt kaikesta huolimatta haluaisit selvittää tuosta taulusta nuo 3 yritystä 10 minuutin kuluessa niin sekin toki onnistuu:

SELECT
  A.ip, A.aika
FROM
  Loki A JOIN Loki B ON
    A.ip=B.ip AND
    A.aika > B.aika AND
    A.Aika < DATE_ADD(B.aika, INTERVAL 10 MINUTE)
GROUP BY
  A.ip, A.aika
HAVING
  Count(B.ip)>1

Tätäkään en sen kummemmin testannut, eli voi olla taas jotain pikku virheitä. Periaate kuitenkin selvinnee.

Edit: Olitkin ehtinyt vastata välillä. Tuo toiseen tauluun blokkauksesta kirjaaminen on varmastikin järkevämpi ratkaisu kuin kaivaa tässä laittamallani kyselyllä.