Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Column Encryption

6 posts in General Discussion Last posting was on 2008-03-14 03:08:52.0Z
Herman Miller Posted on 2008-03-12 19:19:47.0Z
Sender: 40b3.47d82bb2.1804289383@sybase.com
From: Herman Miller
Newsgroups: ianywhere.public.general
Subject: Column Encryption
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <47d82cd3.40d5.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 12 Mar 2008 11:19:47 -0800
X-Trace: forums-1-dub 1205349587 10.22.241.41 (12 Mar 2008 11:19:47 -0800)
X-Original-Trace: 12 Mar 2008 11:19:47 -0800, 10.22.241.41
Lines: 9
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6756
Article PK: 4999

I need to encrypt a column in a table. It seems that you
need to include in the Select, Update, Insert statement the
encryption key with the ENCRYPT/DECRYPT keyword. We are
thinking of using an sproc to update and retrieve this info.
Is there a way to hide the or encrypt the sproc in SQL
Anywhere version 9.0.2.3534 or is there a better way to
encrypt column level data

Thks


Chris Keating (Sybase iAnywhere) Posted on 2008-03-12 19:43:34.0Z
From: "Chris Keating (Sybase iAnywhere)" <keating_spam_free@ianywhere.com>
User-Agent: Thunderbird 2.0.0.12 (Windows/20080213)
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: Column Encryption
References: <47d82cd3.40d5.1681692777@sybase.com>
In-Reply-To: <47d82cd3.40d5.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <47d83266$1@forums-1-dub>
Date: 12 Mar 2008 11:43:34 -0800
X-Trace: forums-1-dub 1205351014 10.22.241.152 (12 Mar 2008 11:43:34 -0800)
X-Original-Trace: 12 Mar 2008 11:43:34 -0800, vip152.sybase.com
Lines: 15
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6757
Article PK: 5001

The following statement will hide the sp syntax. It is irreversible so
you will need to maintain the source outside of the database.

ALTER PROCEDURE [ owner.]procedure-name SET HIDDEN

Herman Miller wrote:
> I need to encrypt a column in a table. It seems that you
> need to include in the Select, Update, Insert statement the
> encryption key with the ENCRYPT/DECRYPT keyword. We are
> thinking of using an sproc to update and retrieve this info.
> Is there a way to hide the or encrypt the sproc in SQL
> Anywhere version 9.0.2.3534 or is there a better way to
> encrypt column level data
>
> Thks


Herman Miller Posted on 2008-03-12 20:55:17.0Z
Sender: 40b3.47d82bb2.1804289383@sybase.com
From: Herman Miller
Newsgroups: ianywhere.public.general
Subject: Re: Column Encryption
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <47d84335.434f.1681692777@sybase.com>
References: <47d83266$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 12 Mar 2008 12:55:17 -0800
X-Trace: forums-1-dub 1205355317 10.22.241.41 (12 Mar 2008 12:55:17 -0800)
X-Original-Trace: 12 Mar 2008 12:55:17 -0800, 10.22.241.41
Lines: 29
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6758
Article PK: 5002

When I decrypt the column I get the following message: input
must be a multiple of 16bytes in length for AES, however no
error occurs during encypt. This is what I use to encrypt
and decrypt:

Encrypt: Update dba.clt_creditcards set
account=ENCRYPT(account,'85724rohT4')

Decrypt: select CAST( DECRYPT( account,'85724rohT4' ) AS
CHAR(100) ) from clt_creditcards

what am I doing wrong?

> The following statement will hide the sp syntax. It is
> irreversible so you will need to maintain the source
> outside of the database.
>
> ALTER PROCEDURE [ owner.]procedure-name SET HIDDEN
>
> Herman Miller wrote:
> > I need to encrypt a column in a table. It seems that you
> > need to include in the Select, Update, Insert statement
> > the encryption key with the ENCRYPT/DECRYPT keyword. We
> > are thinking of using an sproc to update and retrieve
> > this info. Is there a way to hide the or encrypt the
> > sproc in SQL Anywhere version 9.0.2.3534 or is there a
> > better way to encrypt column level data
> >
> > Thks


Mark Culp Posted on 2008-03-13 01:23:14.0Z
Message-ID: <47D88201.FEF45060@iAnywhere.com>
From: Mark Culp <reply_to_newsgroups_only_please_nospam_mark.culp@iAnywhere.com>
X-Mailer: Mozilla 4.75 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: Column Encryption
References: <47d83266$1@forums-1-dub> <47d84335.434f.1681692777@sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 12 Mar 2008 17:23:14 -0800
X-Trace: forums-1-dub 1205371394 10.22.241.152 (12 Mar 2008 17:23:14 -0800)
X-Original-Trace: 12 Mar 2008 17:23:14 -0800, vip152.sybase.com
Lines: 48
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6759
Article PK: 5003

What is the size of your "account" column/variable?

Note that the output of the encrypt() function will be larger
than its input - the size is approx sizeof(input)+16 rounded
_up_ to the next multiple of 16, and the output is binary.

I suspect that your account variable/column is too small to
hold the output and is therefore being truncated, and so the
decrypt is not getting the entire encrypted value.
--
Mark Culp
SQLAnywhere Research and Development
iAnywhere Solutions Engineering
-------------------------------------------------------------------------
** Whitepapers, TechDocs, bug fixes are all available through the **
** iAnywhere Developer Community at http://www.ianywhere.com/developer **
-------------------------------------------------------------------------

Herman, Miller wrote:
>
> When I decrypt the column I get the following message: input
> must be a multiple of 16bytes in length for AES, however no
> error occurs during encypt. This is what I use to encrypt
> and decrypt:
>
> Encrypt: Update dba.clt_creditcards set
> account=ENCRYPT(account,'85724rohT4')
>
> Decrypt: select CAST( DECRYPT( account,'85724rohT4' ) AS
> CHAR(100) ) from clt_creditcards
>
> what am I doing wrong?
>
> > The following statement will hide the sp syntax. It is
> > irreversible so you will need to maintain the source
> > outside of the database.
> >
> > ALTER PROCEDURE [ owner.]procedure-name SET HIDDEN
> >
> > Herman Miller wrote:
> > > I need to encrypt a column in a table. It seems that you
> > > need to include in the Select, Update, Insert statement
> > > the encryption key with the ENCRYPT/DECRYPT keyword. We
> > > are thinking of using an sproc to update and retrieve
> > > this info. Is there a way to hide the or encrypt the
> > > sproc in SQL Anywhere version 9.0.2.3534 or is there a
> > > better way to encrypt column level data
> > >
> > > Thks


Herman Miller Posted on 2008-03-13 19:37:07.0Z
Sender: 61a0.47d9804a.1804289383@sybase.com
From: herman miller
Newsgroups: ianywhere.public.general
Subject: Re: Column Encryption
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <47d98263.61ca.1681692777@sybase.com>
References: <47D88201.FEF45060@iAnywhere.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 13 Mar 2008 11:37:07 -0800
X-Trace: forums-1-dub 1205437027 10.22.241.41 (13 Mar 2008 11:37:07 -0800)
X-Original-Trace: 13 Mar 2008 11:37:07 -0800, 10.22.241.41
Lines: 53
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6760
Article PK: 5004

the size of account column is 100? please send an example of
what the decrypt code would look like.

> What is the size of your "account" column/variable?
>
> Note that the output of the encrypt() function will be
> larger than its input - the size is approx
> sizeof(input)+16 rounded _up_ to the next multiple of 16,
> and the output is binary.
>
> I suspect that your account variable/column is too small
> to hold the output and is therefore being truncated, and
> so the decrypt is not getting the entire encrypted value.
> --
> Mark Culp
> SQLAnywhere Research and Development
> iAnywhere Solutions Engineering
> ----------------------------------------------------------
> --------------- ** Whitepapers, TechDocs, bug fixes are
> all available through the ** ** iAnywhere Developer
> Community at http://www.ianywhere.com/developer **
> ----------------------------------------------------------
> --------------- Herman, Miller wrote:
> >
> > When I decrypt the column I get the following message:
> > input must be a multiple of 16bytes in length for AES,
> > however no error occurs during encypt. This is what I
> > use to encrypt and decrypt:
> >
> > Encrypt: Update dba.clt_creditcards set
> > account=ENCRYPT(account,'85724rohT4')
> >
> > Decrypt: select CAST( DECRYPT( account,'85724rohT4' )
> > AS CHAR(100) ) from clt_creditcards
> >
> > what am I doing wrong?
> >
> > > The following statement will hide the sp syntax. It is
> > > irreversible so you will need to maintain the source
> > > outside of the database.
> > >
> > > ALTER PROCEDURE [ owner.]procedure-name SET HIDDEN
> > >
> > > Herman Miller wrote:
> > > > I need to encrypt a column in a table. It seems that
> > > > you need to include in the Select, Update, Insert
> > > > statement the encryption key with the
> > > > ENCRYPT/DECRYPT keyword. We are thinking of using an
> > > > sproc to update and retrieve this info. Is there a
> > > > way to hide the or encrypt the sproc in SQL Anywhere
> > > > version 9.0.2.3534 or is there a better way to
> > > encrypt column level data >
> > > > Thks


Mark Culp Posted on 2008-03-14 03:08:52.0Z
Message-ID: <47D9EC43.96C47191@iAnywhere.com>
From: Mark Culp <reply_to_newsgroups_only_please_nospam_mark.culp@iAnywhere.com>
X-Mailer: Mozilla 4.75 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: Column Encryption
References: <47D88201.FEF45060@iAnywhere.com> <47d98263.61ca.1681692777@sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 13 Mar 2008 19:08:52 -0800
X-Trace: forums-1-dub 1205464132 10.22.241.152 (13 Mar 2008 19:08:52 -0800)
X-Original-Trace: 13 Mar 2008 19:08:52 -0800, vip152.sybase.com
Lines: 83
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6761
Article PK: 5005

I guess I don't understand your request because you have
already shown an example of how to use DECRYPT()....
but here goes a simple (untested!) example:

begin
declare @input varchar(100);
declare @encrypted binary(128); -- min size 16*(1+int((100+15)/16))
declare @output varchar(100);
declare @key varchar(20);

set @input = 'some random data';
set @key = 'my secure key';

set @encrypted = ENCRYPT( @input, @key );
set @output = DECRYPT( @encrypted, @key );

if @input != @output then
message 'something is wrong!';
else
message 'looks ok';
end if;
exception
when others then
message 'got exception: ' || errormsg();
end;

- Mark

herman, miller wrote:
>
> the size of account column is 100? please send an example of
> what the decrypt code would look like.
>
> > What is the size of your "account" column/variable?
> >
> > Note that the output of the encrypt() function will be
> > larger than its input - the size is approx
> > sizeof(input)+16 rounded _up_ to the next multiple of 16,
> > and the output is binary.
> >
> > I suspect that your account variable/column is too small
> > to hold the output and is therefore being truncated, and
> > so the decrypt is not getting the entire encrypted value.
> > --
> > Mark Culp
> > SQLAnywhere Research and Development
> > iAnywhere Solutions Engineering
> > ----------------------------------------------------------
> > --------------- ** Whitepapers, TechDocs, bug fixes are
> > all available through the ** ** iAnywhere Developer
> > Community at http://www.ianywhere.com/developer **
> > ----------------------------------------------------------
> > --------------- Herman, Miller wrote:
> > >
> > > When I decrypt the column I get the following message:
> > > input must be a multiple of 16bytes in length for AES,
> > > however no error occurs during encypt. This is what I
> > > use to encrypt and decrypt:
> > >
> > > Encrypt: Update dba.clt_creditcards set
> > > account=ENCRYPT(account,'85724rohT4')
> > >
> > > Decrypt: select CAST( DECRYPT( account,'85724rohT4' )
> > > AS CHAR(100) ) from clt_creditcards
> > >
> > > what am I doing wrong?
> > >
> > > > The following statement will hide the sp syntax. It is
> > > > irreversible so you will need to maintain the source
> > > > outside of the database.
> > > >
> > > > ALTER PROCEDURE [ owner.]procedure-name SET HIDDEN
> > > >
> > > > Herman Miller wrote:
> > > > > I need to encrypt a column in a table. It seems that
> > > > > you need to include in the Select, Update, Insert
> > > > > statement the encryption key with the
> > > > > ENCRYPT/DECRYPT keyword. We are thinking of using an
> > > > > sproc to update and retrieve this info. Is there a
> > > > > way to hide the or encrypt the sproc in SQL Anywhere
> > > > > version 9.0.2.3534 or is there a better way to
> > > > encrypt column level data >
> > > > > Thks