convert string to binary

[Guest]

I am having a problem converting string to binary and I am hoping someone can
help me out

I have a sql query that does an update that updates a binary field calles
password

password=CAST(@password as binary)

However when I go to sql server and convert that binary field to varchar,
all I get is the first letter of the password that I typed in. How do I
convert a string field so that when I do that query in sql server, I get the
appropriate value?

Any help would be appreciated

 

[Morten Wennevik [C# MVP]]

I am having a problem converting string to binary and I am hoping someone can
help me out

I have a sql query that does an update that updates a binary field calles
password

password=CAST(@password as binary)

However when I go to sql server and convert that binary field to varchar,
all I get is the first letter of the password that I typed in. How doI
convert a string field so that when I do that query in sql server, I get the
appropriate value?

Any help would be appreciated

Hi,

I suggest using SqlParameter and set the parameter type to the proper SqlDBType
You may need to convert the string to a byte[] before sending it to the sql server, in which case Encoding plays an important role.

 

[Guest]

Your cast is specifying a fixed length of 1, thus the single character - you
need to specify length, and I would hazard a guess you may want it to be
variable width:

CAST(@password AS varbinary(100))

100 is just for example, you may want 12, 24, 42, 99, ...

 

[Nicholas Paldino [.NET/C# MVP]]

bbdobuddy,

What is the type of the @password parameter? My guess here is that it
is of type nvarchar. Because of this, the first letter is going to be
represented as two bytes, the second byte most likely being a value of 0
(since you probably are using strings that are encodable in ASCII as well).

Now, when you are casting that binary value back, you are probably
casting it to varchar, which is looking at the first byte (the letter) and
then the second byte as a string terminator (since it is 0).

Because of this, you have to be consistent with the parameters that you
are casting to binary, and how you are casting them on the way out.

I would be remiss if I didn't say that storing passwords in a database
is a BAD idea. It looks like you are trying to remedy the situation by
storing them in a binary format, but looking at the field with the naked
eye, you could figure out pretty quickly how the data is stored.

If you MUST store a password in the database, then at least encrypt the
column. Sql server has facilities to do this, and you can do it on the
client as well.

Hope this helps.

 

[Guest]

I found a solution

Your cast is specifying a fixed length of 1, thus the single character - you
need to specify length, and I would hazard a guess you may want it to be
variable width:

CAST(@password AS varbinary(100))

100 is just for example, you may want 12, 24, 42, 99, ...

 

[Nicholas Paldino [.NET/C# MVP]]

KH,

That's not true. Run the following query against SQL Server, and you
will get 'hey' returned to you, which can't be the case if it was taking
only the first character:

select cast(cast('hey' as binary) as varchar)