help modifying fairly simple query I think

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

help modifying fairly simple query I think

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-09-25 : 13:37:02

Hi,

I'm getting dictionary style attacks on my login system, so I have to integrate something to prevent this. I have a table called "tblInvalidLoginAttempts" that logs all the bad attempts.

If the ligin attempt from a certain IP has a certain amount of bad attempts across different nameOnline's I want to deny access. Basically I don't want to lock somebody out for attempting to login to the same account 5x with the password = 'password'. However if they start trying other accounts with password ='password' I want to ban them.

Here's just some queries I was messing with below, not sure if its helpful. Can anybody suggest the best way to do this?

The table is structured as below

AttemptID,nameOnline,passwordAttempt,IP,attemptDate

Thanks very much for any help!!
mike123

SELECT count(*) as AttemptCount FROM tblInvalidLoginAttempts WHERE IP = @IP and password ='password' and attemptDate > dateadd(dd,-1,getDate())

brings back

AttemptCount
33

SELECT nameonline, count(*) as AttemptCount FROM tblInvalidLoginAttempts WHERE IP = @IP and password ='password' and attemptDate > dateadd(dd,-1,getDate()) group by nameonline

brings back

attemptCount / NameOnline

1 xxxxx1
1 xxxxx2
1 xxxxx3
1 xxxxx4
1 xxxxx5

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-25 : 19:58:46

This will give you the IP with more than 5 attempts for the past 24 hours


select IP, count(*) as AttemptCount
from   table
where  password    = 'password'
and    attemptDate > dateadd(day, -1, getdate())
group by IP
having count(*) > 5

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-09-25 : 20:47:54

Hi khtan,

I'm looking to pass the IP in question the sproc, and focus on its activity specifically.

I'd like my data returned to look something like this. I think this is probably the best way. Make sense?

nameonline, attempts, IP ,totalAttempts
xxxxx1, 1, 55.55.55.55, 4
xxxxx2, 1, 55.55.55.55, 4
xxxxx3, 1, 55.55.55.55, 4
xxxxx4, 1, 55.55.55.55, 4

Thanks again,
mike123

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-26 : 11:17:01

something like this ?

select nameonline, count(*) as attempts, @IP as IP,
      totalAttempts = (select count(*) from table where IP = @IP)
from   table
where  IP = @IP
group by nameonline
having count(*) > 5

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-09-26 : 17:03:01

perfect, thanks once again!

mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources