Solved

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

Posted on 2011-02-17
14
2,174 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
  • 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
 

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now