Seumas has asked for the wisdom of the Perl Monks concerning the following question:
Using: PostgreSQL 7.4.3 and DBI 1.42
I have a table that contains UID and USERNAME columns. Several users have names like the following:
foo_
foob
fook
When foo_ tries to authenticate with their credentials, the following is called:
But the UID returned is not for foo_ - it's for one of the other foo* users.
Now, in PostgreSQL, the underscore character specifies a match on any one character so foo_ would also match foob. However, DBI should escape the underscore in the code above, correct?
I assumed this would be so and that what DBI would be submitting to PostgreSQL would be foo\_. So I tried this manually in pgsql. It behaved the same way!
The only solution that worked in pgsql was to do foo\\_ which then returned only the desired result of foo_. But this doesn't make sense to me. The documentation states that a double backslash is translated into a literal backslash. So rather than matching real usernames of foo_ shouldn't the above match a user named foo\_ where the backslash is a literal part of the name and not used to escape the following character?
This is incredibly confusing and it's preventing a lot of my users from logging in tonight. I really don't want to have to write code in perl to pre-parse what $dbh->quote() or $dbh->execute() should already be parsing sucks.
Can anyone shed some light on this for me? I tried to seek help in #postgresql but none of the several dozen people in those channels ever says a word for days at a time.
Thanks for your time.
I have a table that contains UID and USERNAME columns. Several users have names like the following:
When foo_ tries to authenticate with their credentials, the following is called:
my $sth = $dbh->prepare("SELECT uid FROM user_accounts WHERE username +~~* ?"); $sth->execute($username); my ($uid) = $sth->fetchrow_array;
But the UID returned is not for foo_ - it's for one of the other foo* users.
Now, in PostgreSQL, the underscore character specifies a match on any one character so foo_ would also match foob. However, DBI should escape the underscore in the code above, correct?
I assumed this would be so and that what DBI would be submitting to PostgreSQL would be foo\_. So I tried this manually in pgsql. It behaved the same way!
The only solution that worked in pgsql was to do foo\\_ which then returned only the desired result of foo_. But this doesn't make sense to me. The documentation states that a double backslash is translated into a literal backslash. So rather than matching real usernames of foo_ shouldn't the above match a user named foo\_ where the backslash is a literal part of the name and not used to escape the following character?
This is incredibly confusing and it's preventing a lot of my users from logging in tonight. I really don't want to have to write code in perl to pre-parse what $dbh->quote() or $dbh->execute() should already be parsing sucks.
Can anyone shed some light on this for me? I tried to seek help in #postgresql but none of the several dozen people in those channels ever says a word for days at a time.
Thanks for your time.
Back to
Seekers of Perl Wisdom