Solved

How can I work around a "derived field in view" error when attempting an INSERT/UPDATE?

Posted on 2011-02-17
14
2,392 Views
Last Modified: 2012-05-11
We are in the process of encrypting 2 columns in a SQL 2005 database table.  We have encrypted the data and created a VIEW of the table which decrypts COLUMN1 and COLUMN2 allowing any existing SELECT statements in our Coldfusion 9 pages to function normally.  However, when attempting to UPDATE or INSERT rows into the table we receive the following error because we are decrypting COLUMN1 and COLUMN2 within the view: “Update or insert of view or function 'MyTable' failed because it contains a derived or constant field.”

The only solution I have been able to come up with is to create a second VIEW for the INSERT and UPDATE statements with a trigger that encrypts COLUMN1 AND COLUMN2 if the value is not null for those columns, but this will require the modification of almost 300 files that contain these statements. I wanted to avoid updating an existing code.  Does anyone have a solution that can be accomplished within SQL?
0
Comment
Question by:jb20147
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 4
14 Comments
 
LVL 39

Expert Comment

by:gdemaria
ID: 34918838

If we are talking best-practices, it doesn't seem to make sense to create a view that decrypts the values.  SQL injection attacks can pull that data just it was coming from the table itself.  Not sure what benefit you're getting.  

The preferred method would be to use a stored procedure to decrypt and crypt the data ONLY when you need that particular record's value.  If it's a password that you need to verify, you don't really ever need to decrypt it, you encrypt the user's entry and compare the encrypted values.


If you have to work with what you have, for whatever reason, you could create a trigger that replaces the default insert/update action.. within that trigger you perform your own insert/update using the encrypted values.

0
 

Author Comment

by:jb20147
ID: 34920658
Unfortunately, the data needs to be encrypted in SQL, but it needs to be displayed in numerous reports and pages throughout well more than 300 pages.

How would I pass the values from this Coldfusion query to a SQL Trigger?

MyTable = View which decrypts data in OrigTable

<cfquery datasource="mydatasource" name="mydata">
Update MyTable
SET Column1="newdata2encrypt", Column2=null, Column3='something something'
Where RowID = 1234
</cfquery>
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34920782
How would I pass the values from this Coldfusion query to a SQL Trigger?

You don't.  You do the query as usual and ms sql passes the values to your trigger.  Inside your trigger is where you do the changes.  It's all done with sql

http://msdn.microsoft.com/en-us/library/aa258254%28v=sql.80%29.aspx

CREATE TRIGGER MyTrigger ON MyTable FOR INSERT, UPDATE
AS
BEGIN
     // .... code to do changes here
END
GO
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jb20147
ID: 34925187
A View can only be referenced by an Instead of Trigger, and using a Trigger on the OrigTable the the View references results in the same derived value within the View error.  Since I didn't include previously, this is the View I am using:

 ALTER view [dbo].[MyTable]
as Select *, convert(nvarchar, decryptbykeyautocert(cert_id('ColumnEncrypt1'), NULL, Column1)) AS 'Decrypt1',
convert(nvarchar,decryptbykeyautocert(cert_id('ColumnEncrypt1'),NULL,Column2)) AS 'Decrypt2'
       from OrigTable

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34925804

> A View can only be referenced by an Instead of Trigger

Yes, that's the one you want.

http://msdn.microsoft.com/en-us/library/def01zh2(v=vs.80).aspx
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34928751
A View can only be referenced by an Instead of Trigger

Right, but it's still just a trigger with the same overall syntax.  If you look at the link it covers all types:
 FOR | AFTER | INSTEAD OF

http://msdn.microsoft.com/en-us/library/aa258254%28v=sql.80%29.aspx
0
 

Author Comment

by:jb20147
ID: 34929351
I did get this working for the INSTEAD OF INSERT, however, the INSTEAD OF UPDATE only works when the update affects 1 row.  SQL throws the following error if an update affects more than one row:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

INSTEAD OF UPDATE
AS
BEGIN
      SET NOCOUNT ON;

      UPDATE OrigTable
      SET Column1 = i.Column1
      FROM INSERTED i
      inner join OrigTable d on d.rowid = i.rowid

END
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34929449
UPDATE OrigTable
   SET Column1 = i.Column1
FROM INSERTED i
where i.rowid = origTable.rowId


0
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 34930147
Update MyTable
SET Column1="newdata2encrypt", Column2=null, Column3='something something'
Where RowID = 1234


Maybe you've done this already. But if you're going to update multiple columns, the INSTEAD OF trigger must include them too. Otherwise, they won't be updated.

      UPDATE orig
      SET        orig.Column1	  = i.Column1,
                    orig.Column2          = i.Column2,
                     etc....
      FROM     OrigTable orig INNER JOIN INSERTED i ON i.rowid  = orig.rowid 

Open in new window



UPDATE OrigTable
SET Column1 = i.Column1
FROM INSERTED i  INNER JOIN OrigTable d on d.rowid = i.rowid


If you prefer the ms sql flavor join, then use the alias in the UPDATE clause.

      UPDATE orig
      SET        orig.Column1        = i.Column1
      FROM     OrigTable orig INNER JOIN INSERTED i ON i.rowid  = orig.rowid

0
 

Author Comment

by:jb20147
ID: 34930438
Thanks for those possible solutions but they all return the same error.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34930462
hmmm, how about this one..


UPDATE OrigTable
   SET Column1 = sub.Column1
     , column2 = sub.column2
from (select i
  from inserted) sub
where sub.rowId = origTable.rowId
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34930471
Maybe you're doing something different than the sample code? Can you post the exact code you're using?

--- sample table
CREATE TABLE MyTable (
	id int identity,
	Column1 varchar(100),
	OtherColumn varchar(100)
)
GO

--- sample view
CREATE VIEW MyView
AS
SELECT	ID, OtherColumn, Column1 
FROM	MyTable
GO


--- sample trigger
CREATE TRIGGER MyTrigger ON MyView INSTEAD OF UPDATE
AS
BEGIN
      SET NOCOUNT ON;

	  -- append a string to show it was modified by the trigger
      UPDATE orig
	  SET    orig.OtherColumn = i.OtherColumn,
			 orig.Column1	  = i.Column1 +' modified by trigger'
      FROM   MyTable orig INNER JOIN INSERTED i ON i.ID = orig.ID

      SET NOCOUNT OFF;
END
GO

--- insert sample rows
INSERT INTO MyTable (OtherColumn, Column1) 
SELECT 'a1', 'a2' UNION ALL
SELECT 'b1', 'b2' UNION ALL
SELECT 'c1', 'c2' 
GO

--- show values before
SELECT	* FROM MyView
GO

--- test update
UPDATE  MyView
SET     OtherColumn = 'changed value', 
		Column1 = 'changed value'
WHERE   ID = 2
GO

--- show values AFTER
SELECT	* FROM MyView
GO

Open in new window


0
 
LVL 52

Expert Comment

by:_agx_
ID: 34930485
Btw: The sample code above was tested w/MS SQL 2005
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34944047

no recognition - so sad  - I only gave you the approach first and gave you the code sample first

0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question