Thread: Going crazy comparing bytea columns

Hi,
I am new to Postgresql, so pls be patient.

I am using npgsql with C# to insert a bytea value into a
column which will serve as an encrypted password. This works
well. However, when I retrieve the value, it is different.
In other words, "select pwd from table where pwd like @pwd"
does not work.

Is it not possible to compare bytea columns?

...get dbConn...

NpgsqlCommand cmd = new NpgsqlCommand("select pwd from table
where usr = @usr and pwd like @pwd",dbConn);
cmd.Parameters.Add(new NpgsqlParameter("usr",DbType.String);
cmd.Parameters.Add(new
NpgsqlParameter("pwd",NpgsqlTypes.NpgsqlDbType.Bytea);
cmd.prepare()
... set param values, call executereader...

Thanks,

JM


----------------------------------------
Upgrade your account today for increased storage; mail
forwarding or POP enabled e-mail with automatic virus
scanning. Visit our member benefits page at
https://members.canada.com/benefits.aspx for more
information.

scomp@canada.com writes:
> I am using npgsql with C# to insert a bytea value into a
> column which will serve as an encrypted password. This works
> well. However, when I retrieve the value, it is different.
> In other words, "select pwd from table where pwd like @pwd"
> does not work.

Why are you using LIKE when you apparently want simple equality?
ISTM that LIKE opens up a whole can of worms with possible appearance
of the wildcards (% and _, not to mention \) in the string.

Another likely source of trouble is that you're not dealing with
escaping of non-ASCII byte values the same way when you insert
the password as when you try to look it up.

            regards, tom lane

On Wed, Mar 01, 2006 at 10:40:39AM -0800, scomp@canada.com wrote:
> I am using npgsql with C# to insert a bytea value into a
> column which will serve as an encrypted password. This works
> well. However, when I retrieve the value, it is different.

Different how?  Without knowing more I'd wonder it's a matter of
escaped vs. non-escaped bytea values.

> In other words, "select pwd from table where pwd like @pwd"
> does not work.
>
> Is it not possible to compare bytea columns?

I can't help with npgsql but it's certainly possible to compare
bytea columns.  Incidentally, I don't know if this could be causing
any problems, but why are you using LIKE instead of "="?

> NpgsqlCommand cmd = new NpgsqlCommand("select pwd from table
> where usr = @usr and pwd like @pwd",dbConn);

What's the output of the following query?

SELECT pwd, @pwd FROM table WHERE usr = @usr

That should return the value in the database (pwd) and the value
you're providing (@pwd).  Let's see how they differ.

--
Michael Fuhr