Basically I would like to ask whether parameters can be used to pass the value of the 'symmetric key id', 'certificate' and optionally 'password' to a stored procedure that uses encryption functions.
The reason this is appealing is that when encryption keys etc change over time (we have a requirement to decrypt data, destroy and create new keys, then encrypt data every time we lose a staff - don't ask), as we would be passing the value of keys, passwords and certificates as parameters to a standard stored procedure.
Hardcoded Example (Working)
USE PSS
GO
CREATE PROC insert_payer_ba
-- define parameters
@.param_rec_id NVARCHAR(MAX),
@.param_bsb NVARCHAR(MAX),
@.param_account NVARCHAR(MAX),
@.param_account_name NVARCHAR(MAX)
AS
BEGIN
OPEN SYMMETRIC KEY bartlett_sym
DECRYPTION BY CERTIFICATE bartlett_cert
WITH PASSWORD = 'Bartlett12_3';
DECLARE @.en_rec_id varbinary(max);
SELECT @.en_rec_id = EncryptByKey(Key_GUID('bartlett_sym'),@.param_rec_id);
DECLARE @.en_bsb varbinary(max);
SELECT @.en_bsb = EncryptByKey(Key_GUID('bartlett_sym'),@.param_bsb);
DECLARE @.en_account varbinary(max);
SELECT @.en_account = EncryptByKey(Key_GUID('bartlett_sym'),@.param_account);
DECLARE @.en_account_name varbinary(max);
SELECT @.en_account_name = EncryptByKey(Key_GUID('bartlett_sym'),@.param_account_name);
INSERT INTO [PSS].[dbo].[payer_ba_sym]
(rec_id, bsb, account, account_name)
VALUES (
@.en_rec_id,
@.en_bsb,
@.en_account,
@.en_account_name
);
CLOSE SYMMETRIC KEY bartlett_sym;
END
GO
PROPOSED USAGE (WHICH DOESN'T WORK)
USE PSS
GO
CREATE PROC insert_payer_ba
-- define parameters
@.param_symkeyguid NVARCHAR(MAX), --(tried VARBINARY as well)
@.param_cert NVARCHAR(MAX),
@.param_certpass VARBINARY(MAX),
@.param_rec_id NVARCHAR(MAX),
@.param_bsb NVARCHAR(MAX),
@.param_account NVARCHAR(MAX),
@.param_account_name NVARCHAR(MAX)
AS
BEGIN
OPEN SYMMETRIC KEY @.param_symkeyguid
DECRYPTION BY CERTIFICATE @.param_cert
WITH PASSWORD = @.param_certpass;
end
DECLARE @.en_rec_id varbinary(max);
SELECT @.en_rec_id = EncryptByKey(Key_GUID(@.param_symkeyguid),@.param_rec_id);
DECLARE @.en_bsb varbinary(max);
SELECT @.en_bsb = EncryptByKey(Key_GUID(@.param_symkeyguid),@.param_bsb);
DECLARE @.en_account varbinary(max);
SELECT @.en_account = EncryptByKey(Key_GUID(@.param_symkeyguid),@.param_account);
DECLARE @.en_account_name varbinary(max);
SELECT @.en_account_name = EncryptByKey(Key_GUID(@.param_symkeyguid),@.param_account_name);
INSERT INTO payer_ba_sym
(rec_id, bsb, account, account_name)
VALUES (
@.en_rec_id,
@.en_bsb,
@.en_account,
@.en_account_name
);
CLOSE SYMMETRIC KEY @.param_symkeyguid;
END
GO
Any assistance in correcting this syntax (if indeed these functions accept parameters would be greatly appreciated).
- Andrew
You can do this if you use dynamic SQL. That is build the SQL query as a string, then pass to the "EXEC" statement. For example:
DECLARE @.sqlstring NVARCHAR(60);
SET @.sqlstring = 'OPEN SYMMETRIC KEY ' + @.param_symkeyguid + ' DECRYPTION BY CERTIFICATE ' + @.param_cert;
EXEC (@.sqlstring);
Note that this procedure is a bit vulnerable especially because you are passing in a password as a parameter. You could make this somewhat more secure by having the certificate be encrypted by the database master key encrypted by the service master key. This will avoid the password issue.
In general, I would hesitate to pass sensitive information in as parameters (this includes the symmetric key and certificate ids). You should be sure that the proc checks the parameters very careful and that the permission on the procedure and the underlying objects are tightly controlled.
Please let me know if you would like further info.
Thanks,
Sung
|||
Also note that the other reason you need to check parameters and monitor permissions very closely is that this sort of parsing is VERY vulnerable to SQL injection attacks.
Sung
|||
Thanks Sung,
You are right about passing paswords as variables. In fact I will only pass the certifcate and key names as vars.
The syntax seems to work, I shall do some more testing, thanks mate.
- Andrew
|||
Hey Andrew,
I would still be careful about possible SQL injection attacks. One way you can minimize this is by doing a simple parameter check such as
if (cert_id(@.cert_name) is not null) ... <your code here>
else ... <your error code here>
This should be done for each type on all names passed in. This, at a minimum, checks to make sure that people are actually passing in a valid object names.
Hope this helps,
Sung
|||
I am including a couple of links for SQL injection articles that may be helpful. I highly recommend reading the second link even if you are already familiar with SQL injection.
· SQL Injection http://msdn2.microsoft.com/en-us/library/ms161953.aspx
· New SQL Truncation Attacks And How To Avoid Them http://msdn.microsoft.com/msdnmag/issues/06/11/SQLSecurity/default.aspx
Thanks,
-Raul Garcia
SDE/T
SQL Server Engine
|||
Hello Sung,
My old syntax prior to passing parameters for the certificate and key was this:
DECLARE @.en_rec_id varbinary(max);
SELECT @.en_rec_id = EncryptByKey(Key_GUID(bartlett_sym),@.param_rec_id); it worked but values for key etc were hardcoded
If I apply your method to the encryption statements:
DECLARE $sqlstring NVARCHAR(MAX);
SET @.sqlstring = 'EncryptByKey(Key_GUID(' + "'" @.param_bartlett_sym + "'" + '),' + @.param_rec_id + ')';
DECLARE @.en_rec_id varbinary(max);
SELECT @.en_rec_id = EXEC(@.sqlstring); -- when I run the code it bombs here near EXEC, so I need some help with this line
Any assistance appreciated.
- Andrew
|||
Hey Andrew,
It's actually a little easier than that. Only DDL and perhaps a few other statement types don't support dynamic SQL. Built-ins should already support dynamic SQL so you could simply directly call:
SELECT @.en_rec_id = EncryptByKey(Key_GUID(@.param_bartlett_sym),@.param_rec_id)
Also, Laurentiu suggested another website to check:
http://www.sommarskog.se/dynamic_sql.html
You can also look into the "sp_executesql" and "quotename" functions as they might help you.
Sung
|||
Thanks Sung,
Sorry about the delayed reply. Actually passing the sym key in the manner described eg
SELECT @.en_rec_id = EncryptByKey(Key_GUID(@.param_bartlett_sym),@.param_rec_id)
results in null.
So I'm not sure how to get a result...
This doesn't work (might be on the right track thought).
DECLARE @.en_rec_id varbinary(max);
DECLARE @.str_cert NVARCHAR(MAX);
SET @.str_cert = 'SELECT @.en_rec_id = EncryptByKey(Key_GUID(' + "'" + @.param_cert + "'" + '),' + @.param_rec_id + ')';
EXECUTE sp_executesql @.str_cert, N'@.en_rec_id VARBINARY OUTPUT', @.en_rec_id OUTPUT;
Basically I wish to run the dynamic query and have it pass the value of @.en_rec_id as output for later use in an insert statement in the same stored procedure.
See original post above.
Any help greatly appreciated.
- Andrew
|||
Hey Andrew,
Sorry for the late response.
I did a quick test and it seems to work for me. Quick question, where do you open the key? You can verify the key is actually open by checking the sys.open_keys catalog view. You will need to have the key open prior to encrypting with it.
Thanks,
Sung
