|Think about Loose Coupling|
Backslash and Underscore problem with DBI and PostgreSQL.by Seumas (Curate)
|on Jun 21, 2004 at 04:16 UTC||Need Help??|
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:
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.