never Matching SQL in ttUser.class

Discussion forum about Anuko Time Tracker
Post Reply
OrangeAL
Posts: 22
Joined: Thu Jul 10, 2014 6:48 am

never Matching SQL in ttUser.class

Post by OrangeAL » Mon May 07, 2018 9:39 am

Hi,
in the function getUser is a sql that have a part that never match (for my understanding)

$sql = "select u.id, u.name, u.login, u.role_id, u.client_id, u.status, u.rate, u.email from tt_users u".
" left join tt_roles r on (u.role_id = r.id)".
" where u.id = $user_id and u.group_id = $this->group_id and u.status is not null".
" and (r.rank < $this->rank or (r.rank = $this->rank and u.id = $this->id))


The sql command is (here with Params):
select u.id, u.name, u.login, u.role_id, u.client_id, u.status, u.rate, u.email
from tt_users u
left join tt_roles r on (u.role_id = r.id)
where u.id = 795
and u.group_id = 8
and u.status is not null
and (r.rank < 512 or (r.rank = 512 and u.id = 31))

795 is the u.id from the getuser Param.
31 is the u.id of the logged on user.

The second "or" part of the sql command will never match.
Why? Because in the where clause is "u.id = 795" . Never ever would it be 31.

So the second part is wrong.
A solution is to change the sql to:

$sql = "select u.id, u.name, u.login, u.role_id, u.client_id, u.status, u.rate, u.email from tt_users u".
" left join tt_roles r on (u.role_id = r.id)".
" where u.id = $user_id and u.group_id = $this->group_id and u.status is not null".
" and (r.rank < $this->rank or (512 = (select rank from tt_roles join tt_users on tt_roles.id = tt_users.role_id where tt_users.id = $this->id))

With params:
select u.id, u.name, u.login, u.role_id, u.client_id, u.status, u.rate, u.email
from tt_users u
left join tt_roles r on (u.role_id = r.id)
where u.id = 795
and u.group_id = 8
and u.status is not null
and (r.rank < 512 or (512 = (select rank from tt_roles join tt_users on tt_roles.id = tt_users.role_id where tt_users.id = 31)))

Is my solution correct?
Nice greetings,
André Lehrmann

wrc
Posts: 249
Joined: Tue May 25, 2010 8:30 pm

Re: never Matching SQL in ttUser.class

Post by wrc » Mon May 07, 2018 12:18 pm

OrangeAL wrote:
Mon May 07, 2018 9:39 am
The second "or" part of the sql command will never match.
Why? Because in the where clause is "u.id = 795" . Never ever would it be 31.
It is an or, so not matching second part does not matter. The second part should match when you query for self ($user_id equals $this->id). For example, when editing your own record.

Am I missing something? Perhaps, provide details that explain what is wrong with the function, meaning how exactly does the problem manifests itself.

OrangeAL
Posts: 22
Joined: Thu Jul 10, 2014 6:48 am

Re: never Matching SQL in ttUser.class

Post by OrangeAL » Mon May 07, 2018 12:27 pm

When i am a Team Manager i cant edit other users.

OrangeAL
Posts: 22
Joined: Thu Jul 10, 2014 6:48 am

Re: never Matching SQL in ttUser.class

Post by OrangeAL » Mon May 07, 2018 12:37 pm

OK, i found it, the sql statement its not the reason.
I thought the sql statement has the function to prove the permission. So i changed it.
But the original sql statement is correct.

The reason for my choice was:
In my test the sql statement gives me an empty result. (i could not edit any user)
But not the sql statement syntax is the reason for the empty result.
The reason was, that the user have an non existent role id.
I changed to the original sql statement and give the user an existing role id, and now it works.

So, thanks for answer.
André Lehrmann

Post Reply