Switch to standard view 
  Sybase logo
 
 
 



Urgent from Sybase: Data corruption issue with ASE 12.5.3a when using Encrypted Columns in conjunction with certain select statements

Summary: This document describes two separate scenarios where the use of certain Transact-SQL statements in conjunction with encrypted columns will lead to invalid data in Adaptive Server Enterprise (ASE) 12.5.3a. The statements are:

  1. An INSERT-SELECT statement that uses a UNION operator to insert data into a target table containing encrypted columns
  2. A SELECT-INTO statement that uses a UNION operator to insert data into a target table containing encrypted columns

Contents

This document contains the following sections:

  • Customer Alert
  • Recommendation

Customer Alert

Use of INSERT-SELECT

An INSERT-SELECT statement that uses a UNION operator inserts invalid data into the target table if the target table contains encrypted columns. For example:

create table tresult (col1 int encrypt with k1)
go

-- tables t1 and t2 may or may not have encrypted columns
insert tresult
select t1.col1 from t1
union
select t2.col1 from t2
go

select * from tresult
go

Msg 15413, Level 18, State 5:
Line 1:
An internal error occurred during an encryption operation (error code -1).
Please contact Sybase Technical Support.

This problem is not applicable to target tables which do not contain any encrypted columns. This issue is being tracked under Sybase CR# 419846.

Workaround: The problem described affects only ASE 12.5.3a. If you intend to remain on this version, you must do the insert as a two-step process as follows:

  1. Select unioned data into an intermediate temporary table without encrypted columns
  2. Insert the data from the temporary table into the desired target table containing encrypted columns

For example:

select t1.col1 into #t from t1
union
select t2.col1 from t2
go

insert tresult select col1 from #t
go

Symptoms: If you have already executed a command like the INSERT-SELECT-UNION mentioned above to insert data into a target table containing encrypted columns, you will already have invalid data in your target tables. A select * query on the target table will report error 15413 (see above for the error message).

Use of SELECT-INTO

A SELECT-INTO statement that uses a UNION operator inserts invalid data into the target table if the target table contains encrypted columns. For example:

-- tables t1 and t2 may or may not have encrypted columns
select t1.col1
into tresult (col1 encrypt with k1)
from t1
union select t2.col1 from t2
go

select * from tresult
go

Msg 15413, Level 18, State 5:
Line 1:
An internal error occurred during an encryption operation (error code -1).
Please contact Sybase Technical Support.

This problem is not applicable to target tables which do not contain any encrypted columns. This issue is being tracked under Sybase CR# 421076.

Workaround: The problem described affects only ASE 12.5.3a. If you intend to remain on this version, you must do the insert as a two-step process:

  1. Select unioned data into an intermediate temporary table without encrypted columns
  2. Insert the data from the temporary table into the desired target table containing encrypted columns

For example:

select t1.col1 into #t from t1
union
select t2.col1 from t2
go

select col1
into tresult (col1 encrypt with k1)
from #t
go

Symptoms: If you have already executed a command like the SELECT-INTO-UNION mentioned above to create a target table containing encrypted columns, you will already have invalid data in your target tables. A select * query on the target table will report error 15413 (see above for the error message).


Recommendation

Fixes for CR# 419846 and CR# 421076 will be made available in the following EBFs.

Platform EBF#
Solaris 32-bit 13374
Solaris 64-bit 13375
AIX 64-bit 13376
HP-UX 32-bit 13377
HP-UX 64-bit 13378
Windows x86 32-bit 13379
Linux x86 32-bit 13380

These EBFs are due to be available towards the end of May 2006. Sybase strongly recommends that either the work arounds detailed above are used, or a version of ASE that contains fixes for CR# 419846 and CR# 421076 is used.

Once the EBFs are available they can be obtained from the Sybase EBFs and Maintenance site.

http://downloads.sybase.com/

Follow the instructions in the EBF cover letter to install the EBF.


If you require further assistance please contact your local support center. The contact numbers can be found in the About Support section under Support & Services at the www.sybase.com website.

http://www.sybase.com/contactus/support



Back to Top
© Copyright 2010, Sybase Inc.