I've been working on modifying a script a buddy of mine wrote a few years back. I came across something that I want to improve upon, however, I'm not sure which is faster or more efficient.
The situation is this: He has ~150 ppl who he has granted access to a private area in his site where authentication is done via cookies. After it checks to see if the username and pw match, then it checks to see if the the user has access to that section of the site. It sends $username to a function where it performs a search on a field that holds a comma delimited list with no spaces between the names and the username of all the users who have access.
If a string in the field matches $username, then it returns 1, 0 for not listed.
A small sample of PrivNames would include:
,User1,User2,User n-1,User n
<?
$query = "select PrivNames from AccessTable";
$result = mysql_query($query);
if ($row = mysql_fetch_array($result))
{ $thenames = explode(",", $row[0]);
while (list($key, $name) = each($thenames))
{ if (strtolower(trim($name)) == strtolower($username))
{ $status = 1;
break;
}
}
}?>
I would like to replace it with this simpler version.
<?
$query = "SELECT count(*) FROM AccessTable PrivNames REGEXP '(|,)$username(,|$)'";
$result = mysql_query($query);
$row = mysql_fetch_row($result);
if ($row[0] > 0)
$status = 1;?>
He told be in previous times he used a LIKE statement and people with one off names would mistakenly be granted access. I've tried what I thought was one off names even in test queries to the db and couldn't understand what he meant.
SELECT "user#1" LIKE "user@1"
SELECT "user#1" LIKE "user#1a"
SELECT "user#1" LIKE "user#2"
And similar all return false (0) and I've tried different variations using REGEXP as well so I have no base of reference in which to test this new function correctly. I'm almost afraid of changing it only to have a complaint from my friend saying that ppl are accessing his page when they shouldn't be.
I've tried convincing him into using a serialized array with userids, but he doesn't want it and I'm forced within that constraint. On top of uname/pw auth, we need to see if that certain user has permission to view a page which is where his comma delimited list comes in. He mentioned where 'One-Off names' played a factor and I didn't have an example of that to test out this regular expression. Do you see how a person with a username that does not match the above REGEXP can access this page?
AFAIK,
(|,) should match either the beginning of the string or a comma
$username should match exactly what the username is
($|,) should match either the end of the line or another comma
So from what I guess, the only possibilities for a match is the following:
UserName$
UserName,
,UserName$
,UserName,
My regexp knowledge isn't as good as I would like it to be. It looks fine to me, but I would like some feedback from ppl more exp in these matters.
It should also be known that the username in the cookie will always match (case sensitive) what it says in the database.
