Icon View Incident Report

Serious Serious
Reported By: Daniel Kram
Reported On: 10/12/2009
For: Version 2.03 Build 4
# 3085 Sensitive Query Result Sets Show Incorrect Results for Generated Columns in Base Table

When I view data in the ElevateDB Manager, sometimes a column defined as TimeStamp shows null, other times the data is present. I have not yet tried to retrieve said data through the program, just verifying my importing and found this possible issue.

The following shows how I created the table and the indexes placed on the table.

Before loading, since I am importing data, I temporarily remove the CURRENT_TIMESTAMP() from the Generated clause of the DateLastUpdated Column.

Load the data using params.

Put CURRENT_TIMESTAMP() back onto the DateLastUpdated column.

EXECUTE IMMEDIATE 'CREATE TABLE "tblForTim"
(
"idtbllogfiles" INTEGER GENERATED BY DEFAULT AS IDENTITY
(START WITH 0, INCREMENT BY 1) NOT NULL,
"LogType" VARCHAR(3) COLLATE "ANSI_CI" NOT NULL,
"DateLastUpdated" TIMESTAMP,
"LastUpdatedBy" VARCHAR(3) COLLATE "ANSI_CI" NOT NULL,
"ResNumber" INTEGER NOT NULL,
"Description" CLOB COLLATE "ANSI_CI" COMPRESSION 6 NOT NULL
)
VERSION 1
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 6
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';

EXECUTE IMMEDIATE 'CREATE TEXT INDEX "ndxDescription" ON "tblForTim" ("Description" COLLATE "ANSI_CI")
INDEXED WORD LENGTH 30
WORD GENERATOR "Default"';

EXECUTE IMMEDIATE 'CREATE INDEX "ndxResNumber" ON "tblForTim" ("ResNumber")';

EXECUTE IMMEDIATE 'ALTER TABLE "tblForTim"
ADD CONSTRAINT "PRIMARY" PRIMARY KEY ("idtbllogfiles")';

ALTER TABLE "tblForTim" ALTER COLUMN "DateLastUpdated" AS TIMESTAMP

{ in Delphi I do this once }
INSERT INTO tblForTim ("LogType","DateLastUpdated","LastUpdatedBy","ResNumber","Description")
Values(:LogType, :DateLastUpdated, :LastUpdatedBy, :ResNumber, :Description );
TEDBQuery(qryTemp).SQL.Text := sSQL;
TEDBQuery(qryTemp).Prepare;

{ then each subsequent call I execute }
    TEDBQuery(qryTemp).ParamByName('LogType').AsString := p_recLogFile.LogType;
    TEDBQuery(qryTemp).ParamByName('DateLastUpdated').AsDateTime := p_recLogFile.DateLastUpdated;
    TEDBQuery(qryTemp).ParamByName('LastUpdatedBy').AsString := p_recLogFile.LastUpdatedBy;
    TEDBQuery(qryTemp).ParamByName('ResNumber').AsString := IntToStr(p_recLogFile.ResNumber);
    TEDBQuery(qryTemp).ParamByName('Description').AsString := sWork;
    TEDBQuery(qryTemp).ExecSQL;

{ when all done, I put things back }
ALTER TABLE "tbllogfiles" ALTER COLUMN "DateLastUpdated" AS TIMESTAMP GENERATED ALWAYS AS CURRENT_TIMESTAMP()



Comments Comments
The problem was that the generated columns were being improperly recalculated when included in a query that generated a sensitive result set.


Resolution Resolution
Fixed Problem on 10/13/2009 in version 2.03 build 5


Products Affected Products Affected
ElevateDB Additional Software and Utilities
ElevateDB DAC Client-Server
ElevateDB DAC Client-Server with Source
ElevateDB DAC Standard
ElevateDB DAC Standard with Source
ElevateDB DAC Trial
ElevateDB LCL Standard with Source
ElevateDB VCL Client-Server
ElevateDB VCL Client-Server with Source
ElevateDB VCL Standard
ElevateDB VCL Standard with Source
ElevateDB VCL Trial

Image