MySQL join not returning rows

Question

I'm attempting to create an anti-bruteforcer for the login page on a website. Unfortunately, my query is not working as expected. I would like to test how many times an IP address has attempted to login, and also return the ID of the user for my next step in the login process.

However, I'm having a problem with the query... for one thing, this would only return rows if it was the same user as they had been trying to login to before. I need it to be any user. Secondly, regardless of whether I use LEFT JOIN, RIGHT JOIN, INNER JOIN or JOIN, it will not return the user's ID unless there is a row for the user in login_attempts.

SELECT COUNT(`la`.`id`), 
       `u`.`id`
FROM `users` AS `u` 
LEFT JOIN `login_attempts` AS `la` ON `u`.`id` = `la`.`user_id`
WHERE `u`.`username` = 'admin' 
  AND `la`.`ip_address` = '127.0.0.1' 
  AND `la`.`timestamp` >= '1'

Here's the output from DESC login_attempts

Field       Type                Null    Key Default Extra
id          int(10) unsigned    NO      PRI NULL    auto_increment
user_id     int(10) unsigned    NO      MUL NULL     
ip_address  varchar(15)         NO      MUL NULL     
timestamp   int(10)             NO          NULL

This query does the same thing, except does not even select the ID if there is a row in login_attempts corresponding to it:

SELECT COUNT(`la`.`id`), 
       `u`.`id`
FROM `users` AS `u` 
LEFT OUTER JOIN `login_attempts` AS `la` ON `u`.`id` = `la`.`user_id` 
  AND `la`.`ip_address` = '127.0.0.1' 
  AND `la`.`timestamp` >= '1'
WHERE `u`.`username` = 'admin'

Why is LOGIN_ATTEMPTS.timestamp a string? Pls post the output from: DESC LOGIN_ATTEMPTS. — OMG Ponies
– OMG Ponies, Commented Jun 3, 2010 at 0:11
A string simply because I'm not sure of the syntax. However, the way I'm sending variables via the script sends it as a signed integer, which it is. One second and I'll post the describe. — John M.
– John M., Commented Jun 3, 2010 at 0:12
Helpful link explaining joins if you haven't seen it: codinghorror.com/blog/2007/10/… — OMG Ponies
– OMG Ponies, Commented Jun 3, 2010 at 0:26
I've seen it, and I tried the one I thought would work first, it didn't, I tried the rest, and none of them worked as expected, as I said in my question. — John M.
– John M., Commented Jun 3, 2010 at 0:27
Are you sure you have data in LOGIN_ATTEMPTS that has both a timestamp value of 1 or more, and the ip_address is 127.0.0.1? Start there, and then add what you need in incremental steps. — OMG Ponies
– OMG Ponies, Commented Jun 3, 2010 at 0:28

Vin-G · Accepted Answer · 2010-06-03 01:11:30Z

3

Don't use a join, and separate the queries. Do the counting of number of attempts in one, and returning the user id in the other:

sql 1:

select count(id) from login_attempts where ip_address="<ipaddress>"

sql 2:

select id from users where username="<username>"

If you really insist in using joins, for which i think it really is unnecessary:

select count(login_attempts.id), users.id
from users
cross join login_attempts
where users.username="<username>"
and login_attempts.ip_address="<ipaddress>"

Which i also think would be inefficient.

edited Jun 3, 2010 at 1:11

answered Jun 3, 2010 at 1:01

Vin-G

4,9502 gold badges24 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John M. Over a year ago

Thanks. I figured going with the extra query wouldn't hurt too much.

BillThor · Accepted Answer · 2010-06-03 00:12:48Z

0

Try moving the ip_address and timestamp into the join condition. You need an outer join to login_attempts.

answered Jun 3, 2010 at 0:12

BillThor

7,6562 gold badges28 silver badges19 bronze badges

1 Comment

John M. Over a year ago

Tried it, posted the results in the question.

Collectives™ on Stack Overflow

MySQL join not returning rows

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related