Cannot get classic ASP parameterized queries working when using Symmetric key encryption

Question

I have the key and certificate setup ok on the database and I can encrypt ok when entering queries directly using HeidiSQL. But the problem I am having is getting this to work using classic asp. I have setup a simple table for testing which contains an auto increment identity column and a var binary max column for holding the encrypted data.

Here is my code:

chtest.commandtext="OPEN SYMMETRIC KEY SymmetricKey DECRYPTION BY CERTIFICATE [Certificate];"

set rschtest = chtest.execute

chtest.commandtext="INSERT into thetable(thevalue) values(?)"

chtest.Parameters.Append chtest.CreateParameter ("@thevalue", adVarBinary, adParamInput,8000, "EncryptByKey(Key_GUID('SymmetricKey'), 'some text here')"  )

set rschtest = chtest.execute

This seems to work as binary data gets entered into the table but when decrypting it directly on the server then it just shows 'Null'.

Doing these queries directly on the server does work, for example:

OPEN SYMMETRIC KEY SymmetricKey DECRYPTION BY CERTIFICATE Certificate
INSERT INTO thetable (thevalue) VALUES (EncryptByKey(Key_GUID('SymmetricKey'), 'some text here'))

and then...

OPEN SYMMETRIC KEY SymmetricKey DECRYPTION BY CERTIFICATE [Certificate]
SELECT CONVERT(varchar, DecryptByKey(thevalue)) AS thevalue FROM thetable

works and shows the correct decrypted value.

I think the problem when using the classic asp method is something to do with having to insert the encrypted string into the varbinary as a varchar? Or something like this.

Also, if I don't use parameterized queries then everything works ok but obviously I want to use prepared queries for security.

Note this this is a legacy application so I have to use classic asp.

Any help appreciated.

The `CreateParameter()` call is completely wrong if you are wanting to parameterise the value you need to apply that to the base value being passed so what data type is `thevalue` before `EncryptByKey(Key_GUID('SymmetricKey'), ...)`?, looks to me to be a `varchar` string of `'some text here'`. — user692942, Nov 22 '16 at 14:35

user692942 · Accepted Answer · 2016-11-22T15:01:17.810

You need a bit of restructuring of the ADODB.Command at the moment the .CreateParameter() call makes no sense. You should always pass the base data type of a value through parameters any manipulation afterwards (in this case by EncryptByKey()) should be done inside the query defined by CommandText.

Dim sql: sql = ""

sql = sql & "OPEN SYMMETRIC KEY SymmetricKey DECRYPTION BY CERTIFICATE [Certificate]" & vbCrLf
sql = sql & "INSERT INTO thetable (thevalue) VALUES (EncryptByKey(Key_GUID('SymmetricKey'), ?))"

With chtest
  .ActiveConnection = your_connection_string
  .CommandText = sql
  .CommandType = adCmdText
  Call .Parameters.Append(.CreateParameter("@thevalue", adVarChar, adParamInput, 8000, "some text here")

  'As you are doing an INSERT use adExecuteNoRecords to cut down on processing 
  'and improve performance.
  Set rschtest = chtest.Execute(, , adExecuteNoRecords)
End With

You will notice that I've changed

.CreateParameter ("@thevalue", adVarBinary, adParamInput,8000, "EncryptByKey(Key_GUID('SymmetricKey'), 'some text here')"  )

to

.CreateParameter("@thevalue", adVarChar, adParamInput, 8000, "some text here")

This is because when you are manipulating a base value in this case the string 'some text here' any manipulation of it needs to be done outside of passing the base value. That is why the .CommandText now contains EncryptByKey(Key_GUID('SymmetricKey'), ?) so only the base value is passed via the Parameter collection during execution.

@jzxz234 No problem, also if you want to decrypt you will need to use the same principle, pass the `DecryptByKey(?)` in your `CommandText` and then the parameter would be binary this time so something like `.CreateParameter("@thevalue", adVarBinary, adParamInput, 8000, the_binary_value)`. — user692942, Nov 22 '16 at 14:56
The answers explanation is a bit wordy need to workout a cleaner way of explaining passing the base data type through to the query. — user692942, Nov 22 '16 at 14:58

Cannot get classic ASP parameterized queries working when using Symmetric key encryption

1 Answers1