[haku]

I just spent five ours figuring out this:

$usernames = mysql_query("SELECT lg.username, lg.id FROM login lg WHERE lg.deleted='0' AND lg.id !='" . $_SESSION['id'] . "' AND lg.id IN (SELECT au.user_id FROM authorization au WHERE au.area NOT IN (SELECT se.area FROM sections se WHERE se.area NOT IN (SELECT au.area FROM authorization au WHERE au.authorization_level='administrator' AND au.user_id='" . $_SESSION['id'] . "'))) AND lg.id NOT IN (SELECT an.user_id FROM authorization an WHERE an.area IN (SELECT st.area FROM sections st WHERE st.area NOT IN (SELECT az.area FROM authorization az WHERE az.authorization_level='administrator' AND az.user_id ='" . $_SESSION['id'] . "')))");

Was that ever a headache! Hardest mysql_query I've put together so far.

The point of this thread: None really. But I was really stoked when I got that to work, and I wanted to share this with someone, and no one in my office would have any idea of what the hell I was talking about! We have the 'fuck off' thread and the 'fuck on' thread, maybe this can be our 'fuck ya!' thread.

[haku]

Actually, now that I think of it, I do have a question. I referred to some tables multiple times in that query. I wasn't sure if I could give an abbreviation once and then use the same abbreviation each time I referred to that table, or if I had to keep giving the abbreviation each time I referred to the table, and in the latter case I wasn't sure if I could use the same abbreviation each time. So I went with giving the tables a new abbreviation each time I referred to them, just to be on the safe side. It works, but is this the most efficient way to do this? Can I just give the table abbreviation once? Or if I have to give it each time, can I give it the same abbreviation each time?

[freelancr]

Oh I do hope you will be validating and escaping this before putting it into the query string!

$_SESSION['id']

[haku]

Hmm. Well the page itself cant be accessed if that variable is set (the user is required to be logged in). But do you think someone could potentially screw with it in my system? With the way its set, its just supposed to be numeric.

[Larixk]

^ had a discussion about session variables the other day:

A teacher at my computer sciences - internet programming - class said session variables are safe and needn't to be checked like posts, gets, cookies, and such. His idea was that they're stored and managed server-side so are unfuckwithable.

I had my doubts but no strong points to convince him otherwise. Are there?

[d3mcfadden]

That query is terrible.... you need to look at inner joins for this.

and yes, you can abbreviate (alias) once.

[haku]

That query is terrible.... you need to look at inner joins for this.

I was so proud of that! You're killing me.

But thanks for the feed back, I definitely will look into them. I haven't used them yet, but apparently I need to, cause that was a beeatch to write.

[d3mcfadden]

Im not trying to kick you in the balls.. but you have combined 7 queries into on there on something that could be done with a few joins.

Write out what you are trying to accomplish in pseudo code and I can help you with the query.

[haku]

Thanks! I'll try and figure it out first, then come back here after I've realized I don't know what the hell I'm doing

[Hunch]

It's impressive that it works, but yes, it could probably be a lot shorter.

If I can drag myself away from World of Warcraft for a couple of hours this afternoon, I'll try and do a bit more work on that mysqli/prepared statements tutorial I was planning to write. Then you can kiss goodbye to having to filter/escape everything.

[datahound]

you guys..

[Cborrow]

A session can be hijacked. With the help of session fixation.

Chris Shiflett: Security Corner: Session Fixation
Chris Shiflett: Security Corner: Session Hijacking

[haku]

Thanks! I'll try and figure it out first, then come back here after I've realized I don't know what the hell I'm doing

Back. Don't know what the hell I'm doing! I wish I even had some code to show, but I've been playing around with this for quite a while and have come up with nothing.

Here is the deal (simplified to the basics):

Table 1: Users (username, user_id)
Table 2: authorization(user_id, area, level)

'Users' is pretty self-explanatory I think - each user has one row that holds their username and ID. Authorization is a table that registers a user ID with an area of the site, and their authorization level within that area (mod or admin). Users will have one row for each area of the site for which they have an authorization level. ex:

user_id area authorization_level
1 A mod
1 B admin
1 C admin
2 A mod
3 A admin
3 D admin

Lets say I am logged in as user 1. What I want to do is get the username and user_id for users that have rows in any of the areas I (user1) have authorization for, but no users that have rows in areas that I DONT have authorization for.

In the above example, I am user 1 with authorization for areas A, B and C
The other users have authorization for the following areas:

User 2: area A
User 3: Area A, Area D

Since all of user 2's authorizations lie in areas that I (user1) have authorization for, I would want their username and ID to be pulled from the database. But since user 3 has authorization for an area I dont have authorization for (area D), then I don't want their info pulled out of the database, even though they also have permission for an area I have permission for.

To break it down, it basically comes down to this:

Get the username and ID for any users that have permissions that are equal to or less than my own.

There is of course more to this such as the authorization levels themselves, but I think I can figure that out myself after the fact.

Thanks to anyone who is willing to help me out on this!

[d3mcfadden]

SELECT u.userID, u.username 
FROM Users u
WHERE u.userID IN 
(
	SELECT u.userID 
	FROM Users u
	JOIN Authorization au
	ON au.userID = u.userID
	WHERE au.userLevel IN 
	(
		SELECT userLevel 
		FROM Authorization au
		WHERE userID = 1 (OR YOUR USER ID)
	)
)

this should sort you.

[haku]

Thanks Mr. McFadden

...but it only partially works. It works in that is selects all users that have authorizations that lie within the current user's, but it also selects users that have permissions that lie outside the current users.

For example, from my above example, its selecting both user2 and user3, since they both have permission for area A. But it shouldn't select user3, because they have permission for area D, and the current user doesn't have access to area D.