jb20147
asked on
How can I work around a "derived field in view" error when attempting an INSERT/UPDATE?
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?
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?
ASKER
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",
Where RowID = 1234
</cfquery>
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",
Where RowID = 1234
</cfquery>
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
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
ASKER
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_
convert(nvarchar,decryptby
from OrigTable
ALTER view [dbo].[MyTable]
as Select *, convert(nvarchar, decryptbykeyautocert(cert_
convert(nvarchar,decryptby
from OrigTable
> 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
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
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
ASKER
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
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
UPDATE OrigTable
SET Column1 = i.Column1
FROM INSERTED i
where i.rowid = origTable.rowId
SET Column1 = i.Column1
FROM INSERTED i
where i.rowid = origTable.rowId
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for those possible solutions but they all return the same error.
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
UPDATE OrigTable
SET Column1 = sub.Column1
, column2 = sub.column2
from (select i
from inserted) sub
where sub.rowId = origTable.rowId
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
Btw: The sample code above was tested w/MS SQL 2005
no recognition - so sad - I only gave you the approach first and gave you the code sample first
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.