Encryption and bulk insert

Hello,

I need to build a table with encrypted data with the source data coming from a .csv. The account number from the .csv will need to be encrypted in the final table and I will also need to have a hashed or MAC index on the account number. I'm guessing that I can do this with a stored procedure using a temp table to load the unedited data and then load the actual table from there. The table will be built from a download every evening. The key and certificate for the encryption are already set. I have been able to load data directly to the table providing values. But, I have been struggling getting a bulk insert to work.

I'd appreciate any ideas or examples.

Thanks!

Could you please explain what problems you are having with bulk insert? Do you have any error messages that can help us diagnose the problem? How are you doing the bulk insert? Are you using bcp or BULK INSERT or OPENROWSET(BULK)? Please post a simple repro of the problem if possible also.|||

Thanks for your quick reply! I have been working with encryption examples from Raul Garcia's demo for the encryption basics. But I need to be able to bulk load a lot of data and end up with an encrypted account number, and also need to be able to do look ups by the account number which is the reason for the MAC portion. This is what I'm working with. The bulk insert piece works fine. I got past that point when I was trying to use a trigger to do the encryption steps. I was able to get one record inserted when I did it that way. But then received an error about a duplicate key on the second record. A regular insert statement specifying values works fine also. With the code below I'm getting a syntax error on the select statement. Query analyzer doesn't like the two lines following the select. This is the error message:

Incorrect syntax near ','.
and it occurs for either line after the select. I experimented by commenting them out one at a time just to be sure. Any suggestions you have will be appreciated!

create procedure test_sp as
create table #InsertTmp
( acct_no_macHolder varchar(20), -- will build mac later
acct_no nvarchar(60), -- (unencrytped acctNo)
acct_lname char(24) ,
acct_fname char(24) )
BULK INSERT #InsertTmp
FROM 'd:\YukonTSQLLibrary\BulkTest.csv'
with (
DATAFILETYPE = 'char',
FIELDTERMINATOR='<F',
ROWTERMINATOR = '\n' )
GO
Insert into buildfiche (acct_no_index, acct_no_cipher, lname, fname)
select (
( dbo.MAC (acct_no, object_id('buildfiche') ) ), --LINE ERROR OCCURS ON
(encryptbykey(key_guid('key_Encryption'), acct_no)), --OTHER LINE ERROR OCCURS ON
lname,
fname)
from #InsertTmp
GO

|||

I don't know why you have so many paranthesis in the SELECT list. It makes it really hard to read. And looks like the columns in the SELECT list is also wrong. I simplified the SELECT statement to:

select dbo.MAC (acct_no, object_id('buildfiche')), --LINE ERROR OCCURS ON
encryptbykey(key_guid('key_Encryption'), acct_no), --OTHER LINE ERROR OCCURS ON
acct_lname,
acct_fname
from #InsertTmp

Also, I am not sure how you are using the OBJECT_ID value in the dbo.MAC UDF. You need to be aware of the fact that the object identifier is not guaranteed to be same on multiple database that contain the same table or even in the same database if you happen to drop & recreate the table. So the acct_no_index value that you generate on one server will be different from another and so on.

|||

Thanks for your help. Getting much closer . . .

When I get a good working example, I'll post it.

Encrypt/decrypt MS SQL 2005

hi guyz!! is it posible to ecnrypt data everytime i insert it to a table and decrypt it everytime I select it using the MS SQL 2005 alone?

like for example i have this query statement below

insert username,password users values ('daimous','my_password')

what i want is every time i insert a value to the password column that value should be encrypted first.

select username,password from users

everytime i select the value of the password column should decrypted.
Thanks in advance!!!Passwords should never need to be decrypted.

The standard method of handling them is to store the encrypted value in the database. When a user logs in, the password they submit is encrypted using the same algorithm and compared to the stored encrypted value.

Passwords can thus use one-way encryption algorithms, which are more secure than two-way algorithms.

If you would like a function for one-way password encryption, I can post one for you.|||SQL 2005 comes with two functions "EncryptByKey" and "DecryptByKey". If you have BOL installed on your machine, look over this article:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/38e9bf58-10c6-46ed-83cb-e2d76cda0adc.htm|||how can i check is BOL is installed in my sql server?|||Query Analyzer/help.

And it should be in your Windows Start menu as well.|||Does SQL Express 2005 edition has it?|||Dunno. But here it is on Microsoft's site, and I'm sure you can download it somewhere. I downloaded 2005's Books Online.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ga-gz_4z51.asp|||I've already got an idea on how to encrypt data by using the EncryptByKey but when i try to decrpyt it using the DecryptByKey it doesn't return the original text. I've already posted that problem HERE (http://www.dbforums.com/showthread.php?t=1606236). COuld anyone help me on this..thanks!!!

encrypt/ decrypt fields or VB function "StrReverse".

I need to encrypt some fields on insert/ update and decrypt on select.
I have developed a function in visual basic but I need it to be a SQL Server
function. Is there any built-in functions availables to encrypt/ decrypt
fields or a substitution to visual basic's function "StrReverse" .
Kind regards
Khurram ButtThe REVERSE function in T-SQL is equivalent to the StrReverse function
from Visual Basic.
In SQL Server 2000, there are some undocumented functions for one-way
encryption of passwords (and other similar data): pwdencrypt and
pwdcompare. See this page (for example):
http://weblogs.asp.net/bdesmond/arc...8/15/24177.aspx
In SQL Server 2005, things are much better: there are functions for
encryption and decryption (using symmetric or asymmetric keys) and also
for digital signing.
Razvan|||No there are no such built-in functions in SQL Server 2000, probably you
have to handle this in your VB application or by creating extended stored
procedures.
"Khurram Shahzad" <Khurram.Shahzad@.360training.com> wrote in message
news:eFFHjbXrFHA.1236@.TK2MSFTNGP10.phx.gbl...
>I need to encrypt some fields on insert/ update and decrypt on select.
> I have developed a function in visual basic but I need it to be a SQL
> Server function. Is there any built-in functions availables to encrypt/
> decrypt fields or a substitution to visual basic's function "StrReverse" .
> Kind regards
> Khurram Butt
>

Encrypt password field

hello guys! i have a question hope you'll help me..how can i encrypt the data that is stored in my password field everytime i insert value to it and decrypt it if i want to retrieve it? thanks in advance!!I dont think you can in simple SQL. BUT some vendors (like Oracle) provide with functions specifically for that. E.g If you use Oracle 10g, you can use the Encrypt function, dbms_crypto...etc.

if you do use oracle 10g, here is a good link for that:

Mysql has a far simpler and equally powerful Encrypt function. just check out the manual if you do use Mysql.

or here is the link:

http://dev.mysql.com/doc/refman/5.0/en/encryption-functions.html

good luck|||Im sorry, somehow the link didnt show up. Here it is if you use 10g:

http://www.oracle.com/technology/pub/articles/oracle_php_cookbook/ullman_encrypt.html|||daimous, looking at all your other posts, i'm going to guess that you are using microsoft sql server, and therefore move this thread to that forum|||... in which case this will probably interest you :D

http://www.dbforums.com/showthread.php?t=1217730|||hello guys! i have a question hope you'll help me..how can i encrypt the data that is stored in my password field everytime i insert value to it and decrypt it if i want to retrieve it? thanks in advance!!
If you are decrypting passwords, you don't understand how they are supposed to be used. Passwords should not need to be decrypted.|||i need to decrypt my password from my database since i encrypted it before storing it to my database, in the first place. So before i can get the "REAL" password i need to decrypt it..right? well anyway, can i get some more inputs..|||i need to decrypt my password from my database since i encrypted it before storing it to my database, in the first place. So before i can get the "REAL" password i need to decrypt it..right?
Wrong.

You encrypt your password using some algorithm and store the results in the database.
When your user logs in with a password, thier password is encrypted using the same algorithm. If the results match what is stored in the database, the user's login is verified.

You can write a sproc to encrypt the submitted password and return success or failure after comparing to the stored encryption string.

Thus there is no need to decrypt passwords.

Encrypt data in a Stored Procedure

I am trying to insert data in a table using a stored procedure, but somehow I cannot store the values passed by the stored procedure in the table.

Table has two fields FIRST_NAME, LAST_NAME with varbinary data type(I need to encrypt the data)

My stored procedure is as follows. Please let me know what i am doing wrong!

***************************************************************

ALTER PROCEDURE [dbo].[SP_InsertInfo]
-- Add the parameters for the stored procedure here

@.FIRST_NAME varBINARY(100)
,@.LAST_NAME varBINARY(100)

AS
OPEN SYMMETRIC KEY key DECRYPTION BY CERTIFICATE cert

BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here

Insert into [dbo].[INFO] (FIRST_NAME, LAST_NAME)
Values ( encryptbykey( key_guid('key'),'@.FIRST_NAME'),
encryptbykey( key_guid('key'),'@.LAST_NAME')
)
close SYMMETRIC KEY key

END

**********************************************
EXEC sp_InsertInfo 'larry', 'Smith'

when I run the SP, the data stored in the first_name, last_name fields are @.FIRST_NAME', @.LAST_NAME' instead of larry, smith respectively.

Thanks

Insert into [dbo].[INFO] (FIRST_NAME, LAST_NAME)
Values ( encryptbykey( key_guid('key'),'@.FIRST_NAME'),
encryptbykey( key_guid('key'),'@.LAST_NAME')
)

You have single quotes around the variable names. This leads to SQL Server to see them as characters and not variables.

Replace the above with this instead

Insert into [dbo].[INFO] (FIRST_NAME, LAST_NAME)
Values ( encryptbykey( key_guid('key'),@.FIRST_NAME),
encryptbykey( key_guid('key'),@.LAST_NAME)
)

No magic, I only remove ' around @.FIRST_NAME and @.LAST_NAME

|||Thanks Andreas for your reply|||

ks06,

did it help you? Please mark the reply as answer if that is the case.

empty values in SQL sentence

i have insert/update SQL sentence, but sometimes there are empty values because there not required in the database so sometimes the sql sentence look this way:

INSERT INTO EquipmentAndPlace (EquipmentID,EquipmentEmdaNo,EquipmentPlace,EquipmentIDForRecognize, EquipmentRemarks,EquipmentLastChecked) VALUES ('3','','2','1','','12/1')

with empty values, but then it doent update in the dataBase-only if all the values appear-
what the solution of it?
ThanksCan you show us the whole part of your insert code. what kind of methods you used.

Alotaibi|||hi, u could build a dynamic SQL for this kind of problem. A stored procedure would be the best bet but let me know if u are using a sp or a SQL query inside your code. depending upon the values u pass to the stored proc, u could add the fields that need to be there in ur query. let me know if u want me to send the sp.|||for example in this code only if i have all the values its work-otherwise it doesnt update-the same thing happend if i want to insert new row data to dataBase.
shravan79 - im a beginner in ASP .net-i dint work yet with stored procedure , my senteces are SQL sentences...
as i said-this value are not requried in the database

 function UpdateDataStore(e as DataGridCommandEventArgs) _
 as boolean
 dim i,j as integer
 dim sDate1,sDone,sPeopleName,sResponse,sNextDate,sImidiate as string
 dim strText,sDate2,sAmlazot,sAct,sDesc as string
 dim blnGo as boolean = true
 dim lbGroupTemp,lbPlaceTemp,lbTypeTemp,lbLevelTemp as ListBox
 dim lbCostTemp,lbResponsableTemp,lbMainGroup As ListBox
 sDate1 = CType(e.Item.Cells(2).Controls(0), TextBox).Text
 sDone = CType(e.Item.Cells(3).Controls(1), checkBox).checked
 sPeopleName = CType(e.Item.Cells(4).Controls(0), TextBox).Text
 sResponse = CType(e.Item.Cells(5).Controls(0), TextBox).Text
 sNextDate = CType(e.Item.Cells(6).Controls(0), TextBox).Text
 sDate2 = CType(e.Item.Cells(9).Controls(0), TextBox).Text
 sImidiate = CType(e.Item.Cells(10).Controls(1), checkBox).checked
 sAmlazot = CType(e.Item.Cells(13).Controls(1), TextBox).Text
 sAct = CType(e.Item.Cells(14).Controls(1), TextBox).Text
 sDesc = CType(e.Item.Cells(19).Controls(1), TextBox).Text
 'get the list box info
 lbGroupTemp=e.Item.Cells(23).Controls(1)
 if lbGroupTemp.SelectedIndex = -1 then
 lbGroupTemp.SelectedIndex=0
 end if
 'get the list box info
 lbPlaceTemp=e.Item.Cells(21).Controls(1)
 if lbPlaceTemp.SelectedIndex = -1 then
 lbPlaceTemp.SelectedIndex=0
 end if
 'get the list box info
 lbTypeTemp=e.Item.Cells(18).Controls(1)
 if lbTypeTemp.SelectedIndex = -1 then
 lbTypeTemp.SelectedIndex=0
 end if
 'get the list box info
 lbLevelTemp=e.Item.Cells(16).Controls(1)
 if lbLevelTemp.SelectedIndex = -1 then
 lbLevelTemp.SelectedIndex=0
 end if
 'get the list box info
 lbCostTemp=e.Item.Cells(12).Controls(1)
 if lbCostTemp.SelectedIndex = -1 then
 lbCostTemp.SelectedIndex=0
 end if
 'get the list box info
 lbResponsableTemp=e.Item.Cells(8).Controls(1)
 if lbResponsableTemp.SelectedIndex = -1 then
 lbResponsableTemp.SelectedIndex=0
 end if
 'get the list box info
 lbMainGroup=e.Item.Cells(25).Controls(1)
 if lbMainGroup.SelectedIndex = -1 then
 lbMainGroup.SelectedIndex=0
 end if
 dim strSQL as string = "UPDATE SSFactory SET " & _
 "SSTypeID = '" & lbGroupTemp.selectedValue & "'," & _
 "PlaceId = '" & lbPlaceTemp.selectedValue & "'," & _
 "SSMaintypeID = '" & lbMainGroup.selectedValue & "'," & _
 "SSDesc = '" & sDesc & "'," & _
 "SSTypeID1 = '" & lbTypeTemp.selectedValue & "'," & _
 "SSShouldHandleID = '" & lbLevelTemp.selectedValue & "'," & _
 "SSHamlatzot = '" & sAmlazot & "'," & _
 "SSCostID = '" & lbCostTemp.selectedValue & "'," & _
 "SSDateToHandle = '" & sDate2 & "'," & _
 "SSResposible = '" & sResponse & "'," & _
 "SSFactoryAction = '" & sAct & "'," & _
 "SSPeopleName = '" & sPeopleName & "'," & _
 "SSDateWasDone = '" & sDate1 & "'," & _
 "SSDateShouldCheck = '" & sNextDate & "'" & _
 " WHERE SSFactoryID = " & dgData.DataKeys(e.Item.ItemIndex)
 response.write(strSQL)
 'exit function
 ExecuteStatement(strSQL)
 return blnGo
 end function

Thanks a lot!!!!|||please someone-i dont understand why if i have empty values its doesnt work...HELP!!