encrypted value storage

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bill - 19 Feb 2008 22:50 GMT

I have a varchar column that I would like to encrypt and store within
the same column. Encryption functions return binary. I would change
the column datatype , but certain parties will not change the datatype
to varbinary. I can store the value after explicitly converting the
binary to varchar...[ convert(varchar(), EncryptByKey(...)) ]. It
seems to work. Will this break one day? The data is normally
accessed through an abstracting UDF. Will this scheme survive?

Thanks,
Bill

Reply to this Message

Mike C# - 20 Feb 2008 03:04 GMT

Yes it will break. By definition SQL strips whitespace characters from the
end of VARCHARs. As soon as you get an encrypted value like
0x4041424320202020 (just a very shortened example), SQL Server will only
store the bytes for 0x40414243 in the VARCHAR column. This will break as
soon as you try to decrypt it since the VARCHAR won't be the correct block
length. If the data is normally accessed through a UDF, then converting the
underlying column to VARBINARY shouldn't be an issue for certain parties.
If it is a big issue, then the other parties need to consider another
strategy like converting the encrypted data to and from Base64 or using some
3rd party tools to encrypt the whole database instead. Otherwise, the other
parties need to decide if encryption of this column is important or not.
Straight BINARY/VARBINARY to VARCHAR will not work, and it will break one
day.

>I have a varchar column that I would like to encrypt and store within
> the same column. Encryption functions return binary. I would change
[quoted text clipped - 6 lines]
> Thanks,
> Bill

Reply to this Message

Erland Sommarskog - 20 Feb 2008 23:38 GMT

> Yes it will break. By definition SQL strips whitespace characters from
> the end of VARCHARs. As soon as you get an encrypted value like
> 0x4041424320202020 (just a very shortened example), SQL Server will only
> store the bytes for 0x40414243 in the VARCHAR column.

I don't think you are correct here.

As long as the setting ANSI_PADDING is ON, SQL Server does not trim trailing
spaces when you store data in a varchar:

CREATE TABLE #a(mydata varchar(20) NOT NULL)
go
INSERT #a(mydata) VALUES ('trailing spaces ')
go
SELECT '<' + mydata + '>' FROM #a
go
DROP TABLE #a

This setting is the default in most contexts. Beware that the setting
that counts is the one that was in force when the column was created.

It may be worth noting that if you ANSI_PADDING off, encryption will
break with binary columns as well, as in that case trailinf 0x00 will
be stripped.

There may still be other reasons for not storing the encrypted binary
value as a string, though.

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Reply to this Message

Mike C# - 21 Feb 2008 01:04 GMT

That's true, I forgot about the ANSI_PADDING setting. As long as SQL Server
doesn't try to do any type of collation conversions on the binary data, he
should be OK with ANSI_PADDING on then?

>> Yes it will break. By definition SQL strips whitespace characters from
>> the end of VARCHARs. As soon as you get an encrypted value like
[quoted text clipped - 24 lines]
> There may still be other reasons for not storing the encrypted binary
> value as a string, though.

Reply to this Message

Erland Sommarskog - 21 Feb 2008 23:19 GMT

> That's true, I forgot about the ANSI_PADDING setting. As long as SQL
> Server doesn't try to do any type of collation conversions on the binary
> data, he should be OK with ANSI_PADDING on then?

I don't know. I can't think of any problems that could lead to data
loss, but I don't feel like putting two fingers on the bible and say
"It will work".

I can see problems for innocent GUIs who think the column has character
data, and then are smacked in the face by a NUL character, but that is
not any major concern.

And, in case it was not clear from my post, ANSI_PADDING OFF is a killer
for encryption also with binary columns.

Signature

Reply to this Message

Mike C# - 22 Feb 2008 02:41 GMT

My concern with collation conversion is that I've seen character data
converted to strange codes during the insert process due to code page
conversions. I'm not sure how SQL would handle binary data being cast to
character and inserted/updated into a table ... would it apply similar code
page conversions in some instances? Where most of your character data might
normally be in the range of 0x20 to 0x7f, binary encrypted data will cover
the whole range regularly. I've seen data loss when converting character
data to a different code page, but I don't know that code page conversions
would be a problem with binary to character conversions. I haven't tested
though, and I'm not 100% on how SQL handles binary to character code page
issues, or if it even needs to.

>> That's true, I forgot about the ANSI_PADDING setting. As long as SQL
>> Server doesn't try to do any type of collation conversions on the binary
[quoted text clipped - 10 lines]
> And, in case it was not clear from my post, ANSI_PADDING OFF is a killer
> for encryption also with binary columns.

Reply to this Message

Erland Sommarskog - 22 Feb 2008 23:00 GMT

> My concern with collation conversion is that I've seen character data
> converted to strange codes during the insert process due to code page
> conversions.

These conversions typically happens in the client API. Say that your
ANSI code page is 1252, but the server collation is Polish_CI_AS which
uses page 1250. In this the letter "ñ" will be converted to "n". (Despite
the fact that the character on that code point in Latin-2, N WITH ACUTE
ACCENT, has exactly the same pronouciation as ñ.)

Note that this does never happen with Unicode data.

> I'm not sure how SQL would handle binary data being cast to character
> and inserted/updated into a table ... would it apply similar code page
> conversions in some instances?

That should definitely not happen my opinion.

Signature

Reply to this Message

pravin bhaskar Dandgavhal - 26 May 2008 07:04 GMT

Hi all

i want to insert password value in sql server2005 using asp.net c# any one
can help mi.

url:http://www.ureader.com/msg/11581539.aspx

Reply to this Message

Dan Guzman - 26 May 2008 14:12 GMT

> i want to insert password value in sql server2005 using asp.net c# any
> one
> can help mi.

Rather than store the encrypted password, consider storing a hashed password
value. This is more secure because it allows you to validate the password
without actually persisting the value. Note that you can also hash the
value in you C# code.

CREATE TABLE dbo.Users
(
UserName nvarchar(30) NOT NULL
CONSTRAINT PK_Users PRIMARY KEY,
PasswordHash varbinary(8000) NOT NULL
)
GO

INSERT INTO Users(UserName, PasswordHash)
VALUES(N'SomeUser', HashBytes('MD5', N'secretpassword'))
GO

--validation example
IF EXISTS(
SELECT *
FROM dbo.Users
WHERE
UserName = N'SomeUser'
AND PasswordHash = HashBytes('MD5', N'secretpassword')
)
BEGIN
PRINT 'password matches'
END
ELSE
BEGIN
PRINT 'invalid UserName or Password'
END
GO

Signature

Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

> Hi all
>
[quoted text clipped - 3 lines]
>
> url:http://www.ureader.com/msg/11581539.aspx

Reply to this Message