http://qs321.pair.com?node_id=229985


in reply to Perl and MySQL - performing a search...

The reason why nonmatching rows are reading as "bad" is because of your regex... if you use your name as $str and then do a SQL lookup then the query will yield zero rows and $row will be set to undef (assuming "Richard" isn't a bad name in your database. :-D) Therefore this $str =~ /$row->{f_user} becomes logically equivalent to "Richard" =~ // And since there's an empty string somewhere in "Richard" the result will always be true... it'll always be true whenever your SQL lookup yields zero rows.

That said, it's a little weird to do a LIKE SQL on your database and then turn right around and do (nearly) the same thing in a Perl regex. If you wanna compare names to your "bad names" table remember that LIKE will only work if the "name" is the same length or shorter than an entry in your database, so "%ass%" will match "asshole" but "%asshole%" will never match "ass". Depending on what you're trying to do you might be better off if you just do the whole "tell me if it's a bad name" routine in Perl and forget trying to do it in SQL. :-D

Gary Blackburn
Trained Killer

Replies are listed 'Best First'.
Re: Re: Perl and MySQL - performing a search...
by Trimbach (Curate) on Jan 26, 2003 at 20:18 UTC
    Yeah, a lookup hash with least-common-denominator bad words might work ok. By "LCD" I mean your hash would only contain "ass" but not "asshole" or "dumbass" or whatever. That way you can do something like: 
    sub i_am_bad {
  my $name = shift;
  my %bad_words = ( .. ); # Some long list.
  foreach my $bad_word(keys %bad_words) {
     return "Bad!" if $name =~ m/$bad_word/i;
  }
  return undef;
}

# Later...
print "Bad username!" if i_am_bad($name);

    [download]
    This has the advantage of getting multiple permutations of different curse words, but keep in mind it'll also disallow innocent words, too. For example, there's probably no permutation of "fuck" that would be ok, but "ass" will disallow "passover" and "class" and "sassy". That might be a small price to pay, though, if you're running, say, a church website. :-)

    Alternatively you could just write down every possible variation of every curse word you could imagine and just do a eq instead of a m// but unless your imagination is very good, or you've spent a goodly period of time in some military service, you're not likely to cover all the objectionable possibilities. ;-)

    Gary Blackburn
    Trained Killer

[reply]
[d/l]
[select]
      Excellent, thank you!
      Before I got this, here is what I came up with.
      Please tell me what you think.

      The reason I would like it to stay in the database, is because I am making an Admin interface for our company, where we can add new names delete them, and do a ton of other things not related to the forbidden names, such as manage the registered users, change our hosting packages, domain prices and all that.

      So here is what I came up with...

          my $is_bad = 0;
    $sth = $dbh->prepare (qq{ SELECT * FROM `forbidden_usernames` });
    $sth->execute();
    while($row = $sth->fetchrow_hashref()) {
        if ($row->{f_user} =~ m/$str/i || $str =~ m/$row->{f_user}/i) 
+{
            $is_bad++;
            last;
        }
    }
    $sth->finish();

if ($is_bad != 0) {
    return("bad");
}

      [download]


      That checks it both ways.

      I have not yet tested it. But what do you think, would it work about like yours does?

      Thank you!!

      Richard.
[reply]
[d/l]
        Sure, that'll work... you can get your long list of bad words from anywhere: a database table, an external file, a __DATA__ section, whatever.

        A couple of small comments: you should avoid using "*" when you SELECT in your code. If you really want all the columns it's much better to select them by name. Not only do you conserve memory and bandwidth by selecting only what you need, but your code is better documented and you get to avoid future problems if you ever decide you want to fetchrow() instead of fetchrow_hashref().

        Secondly, I'd re-arrange your code a little: 

        $sth = $dbh->prepare (qq{ SELECT f_user FROM `forbidden_usernames` });
$sth->execute();
while($row = $sth->fetchrow_hashref()) {
  if ($row->{f_user} =~ m/$str/i || $str =~ /$row->{f_user}/i) {
      return ("bad");
  }
}

        [download]
        The $sth->finish isn't really necessary unless you have a LOT of data in your result set...but that's just a matter of style. TMWOWTDI.

        Gary Blackburn
        Trained Killer

[reply]
[d/l]
Re: Re: Perl and MySQL - performing a search...
by powerhouse (Friar) on Jan 26, 2003 at 19:55 UTC
    So, your saying I should POSSIBLY have something like a hash, with all my obscene names in it, then have perl just run through the hash with a foreach, and do a "contains" search?

    Something like that?

    Even If I did that, would not it have the same problem? if asshole =~ ass would never be true, because asshole is being searched for in the whole word ass, therefore it would never find it.

    So, what do you suggest I do, to eliminate this problem?

    I'm open to suggestions. Thanks for any you have :o)

    thx,
    Richard
[reply]
      You don't need to have a hash or to use a regular expression. All you have to check is whether the fetch found anything or not.
[reply]
      Is there a module that will do this for me, if I pass a username to it?

      Anyone Know of one?

      thx,
      Richard

[reply]

In Section Seekers of Perl Wisdom