phpbb_acl_options table and auth_option field

Do not post support requests, bug reports or feature requests. Discuss phpBB here. Non-phpBB related discussion goes in General Discussion!
Ideas Centre
dcz
Registered User
Posts: 787
Joined: Sun Feb 13, 2005 5:37 am
Contact:

phpbb_acl_options table and auth_option field

Post by dcz » Fri Feb 13, 2009 9:36 am

Hello all,
I recently faced an issue with the phpbb_acl_options table and auth_option field. Nothing really due to phpBB3, but that could be dealt with better (excuse my ignorance if I'm wrong).

Here is the thing, I was playing with a mod conversion (from phpBB2), and find out that this mod did not check the phpbb_acl_options table before it will add the new auth_option in it. So, running the converter multiple times added multiple occurrences of the same auth_option values. I later find out that many mod did this, since mod author did not find out about umil at this time.

This kind of behaviour, even though not legitimate, can cause great trouble in auth settings, and are pretty hard to detect on the first time.
So I was wondering if it would not be wise to add a UNIQUE key on the auth_option field in that table, could help out to prevent such messing and as far as I understood the whole thing, it would not jeopardize any other process, since auth_option looks like meant to be unique.

Would be a cheap and powerful self defense trick on the phpbb_acl_options on which other table depends.

Good or bad guess ?

++

phpBB SEO || phpBB3 SEO Premod || SEO phpBB3
GYM Sitemaps & RSS for phpBB3: GYM Sitemaps & RSS

dcz
Registered User
Posts: 787
Joined: Sun Feb 13, 2005 5:37 am
Contact:

Re: phpbb_acl_options table and auth_option field

Post by dcz » Sat Feb 14, 2009 9:44 am

no one on this matter ?

phpBB SEO || phpBB3 SEO Premod || SEO phpBB3
GYM Sitemaps & RSS for phpBB3: GYM Sitemaps & RSS

User avatar
EXreaction
Former Team Member
Posts: 5666
Joined: Sun Aug 21, 2005 9:31 pm
Location: Wisconsin, U.S.
Name: Nathan

Re: phpbb_acl_options table and auth_option field

Post by EXreaction » Sat Feb 14, 2009 9:22 pm

I believe we've discussed this before with some of the developers and other mod team members, but I don't really remember what the reason was for why that column isn't unique (can't think of any reason it couldn't be right now).

There are also a few bugs in the auth admin permission add function as well, though I have not been able to find out exactly what the bug is...

dcz
Registered User
Posts: 787
Joined: Sun Feb 13, 2005 5:37 am
Contact:

Re: phpbb_acl_options table and auth_option field

Post by dcz » Sun Feb 15, 2009 10:50 am

EXreaction wrote:(can't think of any reason it couldn't be right now).
Same for me, I could not find any legitimate use for a duplicate auth_option key.
What I'm sure of is that if a UNIQUE key on this field is possible, then it would prevent some really bad experiences to low skills phpBB user that would have tried a dummy mod adding auth. Yes it is bad to try mods without backups, but we all know many do it, and worst, some could discover the effect quite some time after they tried it. Would as well simplify support since such matter are again hard to detect if you did not actually face it once (duplicated auth_option fields).

So my point is, if it's doable to add this UNIQUE key, it's a real good thing to do, even though no legitimate use should mess up with this field, preventing potentially heavy mess up (dealing with table that depends on the auth_option ids) once and for all would be great.

I guess we now need the dev team to answer on that.

Regards,

++

phpBB SEO || phpBB3 SEO Premod || SEO phpBB3
GYM Sitemaps & RSS for phpBB3: GYM Sitemaps & RSS

dcz
Registered User
Posts: 787
Joined: Sun Feb 13, 2005 5:37 am
Contact:

Re: phpbb_acl_options table and auth_option field

Post by dcz » Tue Feb 17, 2009 3:40 pm

Maybe I should post this in the bug tracker, what do you think ?

phpBB SEO || phpBB3 SEO Premod || SEO phpBB3
GYM Sitemaps & RSS for phpBB3: GYM Sitemaps & RSS

User avatar
EXreaction
Former Team Member
Posts: 5666
Joined: Sun Aug 21, 2005 9:31 pm
Location: Wisconsin, U.S.
Name: Nathan

Re: phpbb_acl_options table and auth_option field

Post by EXreaction » Tue Feb 17, 2009 6:01 pm

That's probably a good idea. :)

dcz
Registered User
Posts: 787
Joined: Sun Feb 13, 2005 5:37 am
Contact:

Re: phpbb_acl_options table and auth_option field

Post by dcz » Wed Feb 18, 2009 12:02 am

mm, looking at the bug tracker, there is no "data base structure" report category, and this could as well be considered as not a (phpBB) bug. It's kind of an edge case.
But before all, I do not want to stress any body here, so let's say we wait few more days for the report.

Hopefully someone will pass by.

++

phpBB SEO || phpBB3 SEO Premod || SEO phpBB3
GYM Sitemaps & RSS for phpBB3: GYM Sitemaps & RSS

User avatar
EXreaction
Former Team Member
Posts: 5666
Joined: Sun Aug 21, 2005 9:31 pm
Location: Wisconsin, U.S.
Name: Nathan

Re: phpbb_acl_options table and auth_option field

Post by EXreaction » Wed Feb 18, 2009 3:29 am

Just put it under Other.

dcz
Registered User
Posts: 787
Joined: Sun Feb 13, 2005 5:37 am
Contact:

Re: phpbb_acl_options table and auth_option field

Post by dcz » Thu Feb 19, 2009 8:01 am


phpBB SEO || phpBB3 SEO Premod || SEO phpBB3
GYM Sitemaps & RSS for phpBB3: GYM Sitemaps & RSS

dcz
Registered User
Posts: 787
Joined: Sun Feb 13, 2005 5:37 am
Contact:

Re: phpbb_acl_options table and auth_option field

Post by dcz » Sat Feb 21, 2009 8:19 am

This topic must be cursed :lol:

Too bad.
I know there is nothing really urgent here, but the duplicated auth_option key matter is, IMHO, a critical one since it's really complex to fix authorisations when you'd find out that duplicated auth key where accidentally added ages ago.

Of course, I won't be tricked by this, and I fortunately discovered this on testing boards, but I'd really would not want to be the guy facing this on a large online forum.

Because even if you took precautions, and installed a mod that would do this on a local server, you could miss the matter while testing and go online with it for some time before you find out about it ... would be very bad time for the guy.

++

phpBB SEO || phpBB3 SEO Premod || SEO phpBB3
GYM Sitemaps & RSS for phpBB3: GYM Sitemaps & RSS

User avatar
Lumpy Burgertushie
Registered User
Posts: 66921
Joined: Mon May 02, 2005 3:11 am
Contact:

Re: phpbb_acl_options table and auth_option field

Post by Lumpy Burgertushie » Sun Feb 22, 2009 3:34 am

I know absolutely nothing about this, however, I would assume that if it was much of a problem or even much of a potential problem, that someone would have found it before now.


robert
I'm baaaaaccckkkk. still doing work on donation basis. PM your needs.

Premium phpBB 3.2 Styles by PlanetStyles.net

If a tree falls in the forest and nobody is there, does it make a sound?

User avatar
Eelke
QA Team
Posts: 2903
Joined: Thu Dec 20, 2001 8:00 am
Location: NL, Bussum
Name: Eelke Blok
Contact:

Re: phpbb_acl_options table and auth_option field

Post by Eelke » Sun Feb 22, 2009 1:09 pm

Like was said above, it is one of those border line cases. It will certainly not stop the board from functioning properly if everyone codes properly. That's a big "if", though :)

In general, it is a good idea to make rules, that affect your database's data, explicit within the database. In this case, apparently there is supposed to be only one database row with a particular value for the column auth_option. If duplicate entries were to end up in the database, very obscure bugs might result, because code making use of the data is assuming there will be only one row, where there might be multiple.

People writing code to insert values into that table will have to check if there already is a row with that particular value, or delete any value that might be left, before inserting new data. One approach to stopping multiple values from ending up in the table is to basically assume that everyone will code properly. This is a very decentralized approach and prone to problems. If it does go wrong, the earlier mentioned obscure bugs can be the result.

The other approach, which generally is the preferred approach, is to have the database enforcing the rules that apply to the data within, i.e. making the rules explicit that were otherwise implicit. Marking the column UNIQUE, means that the database will make sure that the data in that column will in fact be unique, and will stop duplicate rows getting into the database, even if people write code that otherwise would result in an inconsistent database. It is enormously helpful to spot problems in the code, because it raises a flag the moment the "damage is done" instead of causing obscure bugs somewhere down the line.

Of course, this is very much a theoretical presentation of the matter, maybe there are specific reasons to not use these mechanisms. For example, I wouldn't know what the performance implications would be of marking a column UNIQUE (although I would expect that it primarily has implications upon writing a database row, which in this case only happens sporadically (I think?), and that reading of the table is not impacted).

dcz
Registered User
Posts: 787
Joined: Sun Feb 13, 2005 5:37 am
Contact:

Re: phpbb_acl_options table and auth_option field

Post by dcz » Sun Feb 22, 2009 4:40 pm

That's the whole point about it, if there are no reason to use duplicated auth_option keys, then a UNIQUE key on that filed is just a strong and cheap self defense trick, that can save many many time to even very very few people.

Since it's just a minor tweak in the db structure, it's IMO worth it even if very few people may be affected, cause authorizations are a very critical matter.

++

phpBB SEO || phpBB3 SEO Premod || SEO phpBB3
GYM Sitemaps & RSS for phpBB3: GYM Sitemaps & RSS

User avatar
Eelke
QA Team
Posts: 2903
Joined: Thu Dec 20, 2001 8:00 am
Location: NL, Bussum
Name: Eelke Blok
Contact:

Re: phpbb_acl_options table and auth_option field

Post by Eelke » Mon Feb 23, 2009 8:10 am

I agree and I am very curious to see some input from the dev team.

dcz
Registered User
Posts: 787
Joined: Sun Feb 13, 2005 5:37 am
Contact:

Re: phpbb_acl_options table and auth_option field

Post by dcz » Wed Feb 25, 2009 7:34 pm

Me too :)

phpBB SEO || phpBB3 SEO Premod || SEO phpBB3
GYM Sitemaps & RSS for phpBB3: GYM Sitemaps & RSS

Post Reply

Return to “phpBB Discussion”