perlquestion
Seumas
Using: PostgreSQL 7.4.3 and DBI 1.42
<br><br>
I have a table that contains UID and USERNAME columns. Several users have names like the following:
<br><br>
<li>foo_
<li>foob
<li>fook
<br><br>
When <em>foo_</em> tries to authenticate with their credentials, the following is called:
<br>
<code>
my $sth = $dbh->prepare("SELECT uid FROM user_accounts WHERE username ~~* ?");
$sth->execute($username);
my ($uid) = $sth->fetchrow_array;
</code>
<br>
But the UID returned is not for <em>foo_</em> - it's for one of the other <em>foo*</em> users.
<br><br>
Now, in PostgreSQL, the underscore character specifies a match on any one character so <em>foo_</em> would also match <em>foob</em>. However, DBI should escape the underscore in the code above, correct?
<br><br>
I assumed this would be so and that what DBI would be submitting to PostgreSQL would be <em>foo\_</em>. So I tried this manually in pgsql. It behaved the same way!
<br><br>
The only solution that worked in pgsql was to do <em>foo\\_</em> which then returned only the desired result of <em>foo_</em>. 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 <em>foo_</em> shouldn't the above match a user named <em>foo\_</em> where the backslash is a literal part of the name and not used to escape the following character?
<br><br>
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.
<br><br>
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.
<br><br>
Thanks for your time.