|
•Re: username and password verification from a table in Postgres
by merlyn (Sage) on May 22, 2002 at 17:27 UTC
|
Well, let's at least properly escape the username using a placeholder:
my $count = $dbh->selectrow_array('select count(*) from users where us
+erid = ?', undef, $username);
if ($count) {
print "$username is valid\n";
}
-- Randal L. Schwartz, Perl hacker | [reply]
[d/l] |
|
|
Why do you use the placeholder, and then suply the value in the same statement? Granted, I didn't even know this was possible, so I appriciate haveing seen it.
But why not just do?:
my $count = $dbh->selectrow_array("select count(*) from
users where userid = $username");
Thanks,
Sean
| [reply] |
|
|
| [reply]
[d/l] |
|
|
Hmmm, looking at your message and my reply side by side, I think I get your intent now. You use the placeholder to escape (I didn't apply your use of this word properly) the username, in the sense that you take advantage of the automatic quoteing of values substituted for placeholders, as a way to protect possibly invalid characters in the username?
This jumped out at me when I looked at my own post and realized that it would be an invalid statement.
For Anonymous Monk: Another way of doing this in advance is to use the DBI's quote method, like:
my $quoted_username = $dbh->quote($username);
Hmm, this reminds me I had thought to post a general question about a MySQL DBI driver patch I used to add an extra placeholder that does not get quoted, and so is useful for table names and such...
Anywho, my apologies for the line noise.
Sean<BR | [reply] |
Re: username and password verification from a table in Postgres
by spq (Friar) on May 22, 2002 at 18:21 UTC
|
In the context of the other replies on the code you have already received, and that you seem to be interested in general advice.
It is almost never the best idea use 'SELECT *' in a coded database query where you plan on using the values of any particular fields. It can cause a lot of headaches if the table layout ever changes, not to mention reads more like handwaving if anyone has to maintain the code someday. It's preferable to request fields specifically, such as 'SELECT passwd,user', even if you really want all the fields in the table.
Other than the question I raise in my direct reply, for what you seem to be trying to do, Randals' suggestion count(*) seems the most concise. If you can borrow a copy of 'Writing Apache Modules with Perl and C' (or but it if it is directly appropriate for your task), it contains a section on authenticating users that has some lessons and further references that you might find useful.
HTH,
Sean
| [reply] |
Re: username and password verification from a table in Postgres
by tradez (Pilgrim) on May 22, 2002 at 17:05 UTC
|
$query = qq(SELECT * FROM users WHERE userid = \'$username\');
$sth = $dbh->prepare($query);
$sth->execute;
if(($sth->fetchrow)[0]){
print "$userName is a valid user \n";
exit;
}
print "$userName is an invalid user \n";
Tradez
"Never underestimate the predicability of stupidity"
- Bullet Tooth Tony, Snatch (2001) | [reply]
[d/l] |
|
|
cause i am kinda a newbie and not sure of all the different ways to write perl.
thanks!
| [reply] |
|
|
Didn't mean to offend, just throwing out an option. Good luck with this, and all your future perl endevours.
Tradez
"Never underestimate the predicability of stupidity"
- Bullet Tooth Tony, Snatch (2001)
| [reply] |
Re: username and password verification from a table in Postgres
by stajich (Chaplain) on May 23, 2002 at 17:12 UTC
|
You didn't ask about the password part specifically, although your subject hints at it.
If you want to verify the password, the previous resp are right on - you just might want to use Digest::MD5 to build a one-way hash to store your password strings rather than plain-text strings. A lost password would need to get reset rather than recovered but this gives you a more reasonable level of security for a little bit of code. Have to use the same md5 function when building the user/password table.
use Digest::MD5 qw(md5);
# ... assume that $dbh was initialize previously ...
my $query = qq(SELECT count(*) FROM user WHERE username=? AND password
+=?);
my $sth = $dbh->prepare($query);
$sth->execute($user,md5($password));
($rc) = $sth->fetchrow_array;
if( $rc == 1 ) {
# user and pass matched
} else {
# user and pass did not match (rc == 0 ) or
# you don't have a unique key for user/pass (rc > 1)
}
| [reply]
[d/l] |