homepage Welcome to WebmasterWorld Guest from 54.204.94.228
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Binary mode and Regex
exact matching
typomaniac




msg:4363959
 8:12 am on Sep 18, 2011 (gmt 0)

Hi, I'm attempting to do an exact match for profile updating and need to match (case, character for character beginning to end) exactly user input. The code I'm using is:

btw.....all values, table and column names are ficticious and only for demnstration purposes.

if(length($email) >0){
$query = "SELECT email" . "FROM members WHERE binary email REGEXP '^$email$'";
($email) = $dbh->selectrow_array($query);
if(defined($email))
{
print"That email address is already being used";
}


The problem is that no matter what is entered into the form I get the error. If I change the line

if(defined($email)) to if(!defined($email))

I get no error and it doesn't matter if the email address exists or not.

I'm using the same basic syntax for the main login and it seems to be working good.

$query = "select username, password" . " from members where binary username = '$username' and binary password = '$password' limit 1";
($username, $userword) = $dbh->selectrow_array($query);

if((!defined($username)) || (!defined($password)))
{
$loginmsg="Invalid Login Information";
}
elsif((defined($username)) && (defined($password))){
print"You Have Provided Accurate Information";
}


I'm no pro at this, in fact I pretty much got the login syntax from a book I found titled MySQL by Paul DuBois. The syntax for regex was found in the MySql documentation. Is this even a good way of doing this? I'm using perl for this.

 

lucy24




msg:4363973
 10:38 am on Sep 18, 2011 (gmt 0)

REGEXP '^$email$'

Does that really work? It's not unheard-of for the same symbol to have different meanings depending on where it's used (here non-RegEx $ for "string" and then RegEx $ for "end of expression"), but just looking at it gives me the willies ;)

typomaniac




msg:4363990
 12:29 pm on Sep 18, 2011 (gmt 0)

The part for the main login part works perfect--its either go if the right info is provided or no-go if the not the right info. That was the part:

$query = "select username, password" . " from members where binary username = '$username' and binary password = '$password' limit 1";
($username, $userword) = $dbh->selectrow_array($query);

if((!defined($username)) || (!defined($password)))
{
$loginmsg="Invalid Login Information";
}
elsif((defined($username)) && (defined($password))){
print"You Have Provided Accurate Information";
}


That works great. I'm trying to follow the same basic pattern for creating new users (or users modifying details) and if an email address or username is listed in a row by another user...print out an error and if the email address or username is not already taken then (as in the case of email addresses) validate it before entereing it into the db. I can't understand why something will work one time and not the next. Shouldn't I just be able to modify the code given to do something like:

$query = "select email" . " from members where binary email_addresses = '$email'limit 1";
($email) = $dbh->selectrow_array($query);

if(!defined($email)
{
print"That email address is already listed";
}
elsif(defined($email)
{
#validate the email address
}


I can't understand why something works in one script and not another. That was when I started (2 days ago) searching information about exact (case, char for char, etc.) and that is where I came up with some of the "atrocities" in the original post. Really frustrating as this is the final hurtle in my project.

penders




msg:4363992
 12:40 pm on Sep 18, 2011 (gmt 0)

REGEXP '^$email$'


@lucy24: Yes, that does look confusing. However, I think the "$email" should be replaced by the value of the $email variable by Perl (variable interpolation in double quoted strings in Perl, variable parsing in PHP) - although this would perhaps be worth checking?

Just curious regarding the SQL... why would you use a regular expression rather than a straight forward comparison when checking for an exact match?

typomaniac




msg:4364000
 2:19 pm on Sep 18, 2011 (gmt 0)

hmmm. I went back to a "barebones" type of select/results type of statement and it is doing what it is supposed to do(imagine that). I'm going to wait till morning to pick up on it again. Armed with the fact that it is doing what it is expected to do I think it will be a matter of a little bit of re-structuring my validation/error methods. I'll repost after further investigation on the matter and include the answer.

brotherhood of LAN




msg:4364053
 9:23 pm on Sep 18, 2011 (gmt 0)

good to hear that you have a working solution typomaniac

why would you use a regular expression rather than a straight forward comparison when checking for an exact match


I would wonder the same, as it can introduce new problems such as $email being 'example@example.com', the dot is going to match any character and would mean example@example.com is the same as example@exampleZcom

typomaniac




msg:4364087
 11:49 pm on Sep 18, 2011 (gmt 0)

When this "problem" first surfaced I wasn't using any regex or anything fancy--just a smiple

my $query = "SELECT pobox" . " FROM adminmembers WHERE binary pobox = '$address' limit 1";
$address = $dbh->selectrow_array($query);
if(defined($address))
{
#print error message---i.e., that email address is already registered to another user(or something similar)
}


Like I said, it worked before so why not now? Truth is it was working but the problem began when in the

if(!defined($address))
{ #problem begins here as I tried to validate user input email information
}


I eliminated the part "if(!defined) because I only needed to know if it WAS defined. I was putting the email validation codes inside the brackets of !undefined and that is where the problem began. I didn't need the !undefined part because if not defined---well, its either night or day. I learned a big lesson on this one.
As far as the regex? Well, instead of just going back to basics and using something like

if(defined($address))
{
print"That Address Is Taken";
}


And based on the fact that if it did not come true by not being defined just run the email validation instead of being concerned about it not being defined. Actually, I didn't know anything about using regex with mysql queries before I began searching for answers but I'm glad I did for future reference

Thanx for the replies. I learned as much from them as I did the searches.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved