Codage Base64 dans SQL Server 2005 T-SQL

Je voudrais écrire une requête T-SQL où j'encode une chaîne en tant que chaîne Base64. Étonnamment, Je ne trouve aucune fonction T-SQL native pour faire l'encodage Base64. Un natif de la fonction existe? Sinon, quelle est la meilleure façon de faire l'encodage Base64 dans T-SQL?

102
demandé sur Jacob 2011-02-22 21:37:37

8 réponses

Je sais que cela a déjà été répondu, mais je viens de passer plus de temps que je ne veux admettre à venir avec des instructions SQL à une seule ligne pour accomplir cela, donc je vais les partager ici au cas où quelqu'un d'autre a besoin de faire la même chose: 

-- Encode the string "TestData" in Base64 to get "VGVzdERhdGE="
SELECT
    CAST(N'' AS XML).value(
          'xs:base64Binary(xs:hexBinary(sql:column("bin")))'
        , 'VARCHAR(MAX)'
    )   Base64Encoding
FROM (
    SELECT CAST('TestData' AS VARBINARY(MAX)) AS bin
) AS bin_sql_server_temp;

-- Decode the Base64-encoded string "VGVzdERhdGE=" to get back "TestData"
SELECT 
    CAST(
        CAST(N'' AS XML).value(
            'xs:base64Binary("VGVzdERhdGE=")'
          , 'VARBINARY(MAX)'
        ) 
        AS VARCHAR(MAX)
    )   ASCIIEncoding
;

J'ai dû utiliser une table générée par une sous-requête dans la première requête (encodage) car je n'ai trouvé aucun moyen de convertir la valeur d'origine ("TestData") en sa représentation de chaîne hexadécimale ("5465737444617461") pour inclure comme argument à xs: hexBinary () dans la XQuery déclaration.

J'espère que cela aide quelqu'un!

164
répondu mercurial 2012-02-03 00:07:29

Le moyen le plus simple et le plus court que J'ai pu trouver pour SQL Server 2012 et supérieur est BINARY BASE64 :

SELECT CAST('string' as varbinary(max)) FOR XML PATH(''), BINARY BASE64

Pour Base64 à la chaîne 

SELECT CAST( CAST( 'c3RyaW5n' as XML ).value('.','varbinary(max)') AS varchar(max) )

(ou nvarchar(max) pour les chaînes Unicode)

62
répondu Slai 2016-10-27 14:08:59

Voici une modification de la réponse de mercurial qui utilise également la sous-requête sur le décodage, permettant l'utilisation de variables dans les deux cas.

DECLARE
    @EncodeIn VARCHAR(100) = 'Test String In',
    @EncodeOut VARCHAR(500),
    @DecodeOut VARCHAR(200)    

SELECT @EncodeOut = 
    CAST(N'' AS XML).value(
          'xs:base64Binary(xs:hexBinary(sql:column("bin")))'
        , 'VARCHAR(MAX)'
    )
FROM (
    SELECT CAST(@EncodeIn AS VARBINARY(MAX)) AS bin
) AS bin_sql_server_temp;

PRINT @EncodeOut

SELECT @DecodeOut = 
CAST(
    CAST(N'' AS XML).value(
        'xs:base64Binary(sql:column("bin"))'
      , 'VARBINARY(MAX)'
    ) 
    AS VARCHAR(MAX)
) 
FROM (
    SELECT CAST(@EncodeOut AS VARCHAR(MAX)) AS bin
) AS bin_sql_server_temp;

PRINT @DecodeOut
22
répondu Joey Gennari 2012-07-06 19:51:23

Voici le code pour les fonctions qui feront le travail

-- To Base64 string
CREATE FUNCTION [dbo].[fn_str_TO_BASE64]
(
    @STRING VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
    RETURN (
        SELECT
            CAST(N'' AS XML).value(
                  'xs:base64Binary(xs:hexBinary(sql:column("bin")))'
                , 'VARCHAR(MAX)'
            )   Base64Encoding
        FROM (
            SELECT CAST(@STRING AS VARBINARY(MAX)) AS bin
        ) AS bin_sql_server_temp
    )
END
GO

-- From Base64 string
CREATE FUNCTION [dbo].[fn_str_FROM_BASE64]
(
    @BASE64_STRING VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
    RETURN (
        SELECT 
            CAST(
                CAST(N'' AS XML).value('xs:base64Binary(sql:variable("@BASE64_STRING"))', 'VARBINARY(MAX)') 
            AS VARCHAR(MAX)
            )   UTF8Encoding
    )
END
19
répondu Oleg 2015-06-19 17:20:17

Non, il n'y a pas de fonction native, cette méthode a fonctionné pour moi dans le passé: http://www.motobit.com/help/scptutl/sa306.htm
donc, a cette méthode:
http://www.vbforums.com/showthread.php?t=554886

4
répondu ntziolis 2011-02-22 18:44:42

J'ai adoré la réponse de @ Slai. Je n'ai eu qu'à apporter des modifications très mineures aux one-liners que je cherchais. Je pensais partager ce que j'ai fini avec au cas où cela aiderait quelqu'un d'autre à trébucher sur cette page comme je l'ai fait: 

DECLARE @Source VARCHAR(50) = '12345'
DECLARE @Encoded VARCHAR(500) = CONVERT(VARCHAR(500), (SELECT CONVERT(VARBINARY, @Source) FOR XML PATH(''), BINARY BASE64))
DECLARE @Decoded VARCHAR(500) = CONVERT(VARCHAR(500), CONVERT(XML, @Encoded).value('.','varbinary(max)'))
SELECT @Source AS [Source], @Encoded AS [Encoded], @Decoded AS [Decoded]
4
répondu Jason W 2016-02-22 17:10:09 
DECLARE @source varbinary(max),  
@encoded_base64 varchar(max),  
@decoded varbinary(max) 
SET @source = CONVERT(varbinary(max), 'welcome') 
-- Convert from varbinary to base64 string 
SET @encoded_base64 = CAST(N'' AS xml).value('xs:base64Binary(sql:variable       
("@source"))', 'varchar(max)') 
  -- Convert back from base64 to varbinary 
   SET @decoded = CAST(N'' AS xml).value('xs:base64Binary(sql:variable             
  ("@encoded_base64"))', 'varbinary(max)') 

 SELECT
  CONVERT(varchar(max), @source) AS [Source varchar], 
   @source AS [Source varbinary], 
     @encoded_base64 AS [Encoded base64], 
     @decoded AS [Decoded varbinary], 
     CONVERT(varchar(max), @decoded) AS [Decoded varchar]

Ceci est utile pour encoder et décoder.

Par Bharat J

1
répondu Bharat J 2013-04-11 18:22:34

J'ai fait un script pour convertir un hachage encodés en base64 décimal, il peut être utile:

SELECT LOWER(SUBSTRING(CONVERT(NVARCHAR(42), CAST( [COLUMN_NAME] as XML ).value('.','varbinary(max)'), 1), 3, 40)) from TABLE
0
répondu Phate01 2018-01-03 11:26:25