STUFF query using EncryptByPassphrase

Transact-SQL
1

Hi,
I'm using STUFF to create a list with names pulled from another table. I have used the STUFF function successfully before exepct this time I am using EncryptByPassphrase functionality to encrypt certain names. The list I want returned needs to be Decrypted first. Unfortunately whats returned isn't exactly what I want even tho what I want is included in the middle. Here's a snippet of my code.

SELECT
cpCompanyPublicKey,
CONVERT(varchar(255),DecryptByPassphrase('test pass', cpCompanyName)) AS 'cpCompanyName',
(Stuff ((SELECT ', ' + cast(cpReferenceCustomerSiteID as nvarchar) FROM cpCompanySites sb WHERE sb.cpCompanyID=cc.cpCompanyID and sb.active=1 FOR XML PATH('')), 1, 1, '')) AS sites_list,
(Stuff ((SELECT ', ' + CONVERT(varchar(255),DecryptByPassphrase('test pass', cpReferenceCustomerSiteName)) as cpReferenceCustomerSiteName FROM cpCompanySites sb left join cpReferenceCustomerSite s on sb.cpReferenceCustomerSiteID=s.cpReferenceCustomerSiteID WHERE sb.cpCompanyID=cc.cpCompanyID FOR XML PATH('')), 1, 1, '')) AS site_names
FROM cpCompany cc

My cpCompany table has company details and my cpCompanySites tables acts as the bus table and may have mutliple site entries per company.

Whats being returned is

cpReferenceCustomerSiteName>, Ireland

which is close to what should be returned. Ireland is all that should be returned so its like there is some formatting issue which I can't seem to solve.

Anyone see anything obvious why cpReferenceCustomerSiteName> and is also being returned? The cpReferenceCustomerSiteName field in the cpReferenceCustomerSite table is encrypted as are all my encryoted fields using varbinary(500)

Thanks

2

Remove the alias "cpReferenceCustomerSiteName" as shown below

SELECT
    cpCompanyPublicKey ,
    CONVERT(VARCHAR(255), DECRYPTBYPASSPHRASE('test pass', cpCompanyName)) AS 'cpCompanyName' ,
    ( STUFF(( SELECT
                ', ' + CAST(cpReferenceCustomerSiteID AS NVARCHAR)
              FROM
                cpCompanySites sb
              WHERE
                sb.cpCompanyID = cc.cpCompanyID
                AND sb.active = 1
            FOR
              XML PATH('') ), 1, 1, '') ) AS sites_list ,
    ( STUFF(( SELECT
                ', ' + CONVERT(VARCHAR(255), DECRYPTBYPASSPHRASE('test pass',
                        cpReferenceCustomerSiteName)) --- remove this alias AS cpReferenceCustomerSiteName
              FROM
                cpCompanySites sb
                LEFT JOIN cpReferenceCustomerSite s
                    ON sb.cpReferenceCustomerSiteID = s.cpReferenceCustomerSiteID
              WHERE
                sb.cpCompanyID = cc.cpCompanyID
            FOR
              XML PATH('') ), 1, 1, '') ) AS site_names
FROM
    cpCompany cc
1 Like
3

Thanks James. Works now. Thought I had tried that but mustn't have.