When running a query with EncryptByKey and cfqueryparam, the value appears to be getting truncated.
For example:
<cfset customer_number = 123 />
<cfset soc_sec_number = "123-45-6789" />
<cfquery datasource="web_applications">
OPEN SYMMETRIC KEY SSNKey
DECRYPTION BY CERTIFICATE SSNCert;
UPDATE
Customers
SET
SSN_Encrypted = EncryptByKey( Key_GUID( 'SSNKey' ), <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#soc_sec_number#" > )
WHERE
customer_number = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#customer_number#" > ;
CLOSE SYMMETRIC KEY SSNKey;
</cfquery>
The resulting database value, when decrypted, is "1". If I remove the <cfqueryparam>, the whole value is stored without issue. Additionally, this issue only occurs when used in conjunction with EncryptByKey.
We're using CF 2018 and sql 2016. This just began to be a problem yesterday. It may or may not be related, but our power was lost yesterday. Is is possible that some cf file was corrupted, or some encoding setting was changed? How would I check for this?
UPDATE:
When performing the following query:
<cfquery name="get_ssn">
OPEN SYMMETRIC KEY SSNKey
DECRYPTION BY CERTIFICATE SSNCert;
SELECT
CONVERT( VARCHAR, DecryptByKey( [ssnEncrypted] ) ) AS decrypted_ssn
FROM
customers
WHERE
Customer.customer_number= <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#customer_number#" > ;
CLOSE SYMMETRIC KEY SSNKey;
</cfquery>
The resulting value of decrypted_ssn is as follows:
<cfdump var="#get_ssn.decrypted_ssn#"> -> 123-45-6789
<cfdump var="#right(get_ssn.decrypted_ssn, 4)#"> -> 89
in sql studio -> 1
I suspect some encoding issue at this point, since coldfusion returns the full value but thinks that right(123-45-6789, 4) is 89 rather than 6789
I'm not sure how to test this theory.
UPDATE:
I've confirmed this issue is arising on more than one table, and with more than one symmetric key.
UPDATE again:
The string saved using cfqueryparam, when the LEN() command is used, outputs 22, whereas the value WITHOUT cfqueryparam outputs 11.
UPDATE: I followed the solution Here, which seemed to resolve my issue. But I want to understand how this happened. The connection didn't seem to require this until yesterday, when the power went out and the server restarted.
