Programming Homework Help
Programming Homework Help. Encrypting the data when inserting to SQL DB
I’m working on a small assignment
Requirements are as follows
I have a SQL server 2016 table in which i have (ID, APP_ID, App_Name , P_Name, Date_Processed, and Encrypted_Data_Row) columns
Table structure:
USE [MasterInvDB]
GO
/****** Object: Table [dbo].[MasterInv] Script Date: 7/31/2020 4:31:15 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MasterInv](
[ID] [int] NOT NULL,
[APP_ID] [int] NOT NULL,
[App_Name] [varchar](30) NOT NULL,
[P_Name] [varchar](30) NOT NULL,
[Date_Processed] [datetime] NULL,
[Encrypted_Data_Row] [varbinary](max) NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH
(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
I get XML file from 15 unique applications and each application has its own PGP or public/private keys.
Sample XML.
<ns0:MasterRec xmlns_ns0=”http://HISMaster.Inventory.com“>
<AppID>101</AppID>
<App_Name>CTMS</App_Name>
<P_Name>XORISXC</P_Name>
<Date_Processed>07312020</Date_Processed>
</ns0:MasterRec>
Requirement is we have to encrypt the xml using the application specific key and insert to Encrypted_Data_Row column. ID column is auto generated , AppID, App_Name, P_Name, Date_Processed should be taken from the XML and insert to the table unencrypted before encrypting the XML and insert to Encrypted_Data_Row column.
If required you can create lookup tables to store the keys.
we have SQL Server 2016 and .net framework 4.6 with VS 2015 environment.
I’m looking for a C# component to do this or a SQL Stored procedure or a function is also fine . I need program for both encryption and decryption.
I want the best solution which can scale up or down in future
I can provide 2 PGP keys for 2 applications respectively.
I need clear explanation
Please let me know so that i can share the keys with passphrase for testing or you can create your own keys.