SQL Server

Validate a Google ID Token

See more OAuth2 Examples

Demonstrates how to verify the signature of a Google id token. SQL Server

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @http int
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- First get the public key we'll be needing..
    DECLARE @jwkStr nvarchar(4000)
    EXEC sp_OAMethod @http, 'QuickGetStr', @jwkStr OUT, 'https://www.googleapis.com/oauth2/v3/certs'
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        RETURN
      END

    -- We have the following:

    --     {
    --       "keys": [
    -- 	{
    -- 	  "kid": "e8732db06287515556213b80acbcfd08cfb302a9",
    -- 	  "n": "4RIrO30287Wsq3gqXCMkUYMVAeI3H8...w2mbMNEBQ",
    -- 	  "kty": "RSA",
    -- 	  "e": "AQAB",
    -- 	  "alg": "RS256",
    -- 	  "use": "sig"
    -- 	},
    -- 	{
    -- 	  "kid": "8462a71da4f6d611fc0fecf0fc4ba9c37d65e6cd",
    -- 	  "e": "AQAB",
    -- 	  "n": "xT_ngLZNmT5GBtJZeTB...Ft4gK0eoFi0d3l8bcw",
    -- 	  "alg": "RS256",
    -- 	  "use": "sig",
    -- 	  "kty": "RSA"
    -- 	}
    --       ]
    --     }

    DECLARE @json int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    EXEC sp_OAMethod @json, 'Load', @success OUT, @jwkStr

    -- -------------------------------------------------

    -- Load the following..

    --  {
    --   "access_token": "ya29.a0...0f",
    --   "expires_in": 3599,
    --   "scope": "openid https://www.googleapis.com/auth/userinfo.email",
    --   "token_type": "Bearer",
    --   "id_token": "eyJhb...o5nQ"
    -- }

    DECLARE @jsonToken int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonToken OUT

    EXEC sp_OAMethod @jsonToken, 'LoadFile', @success OUT, 'qa_data/tokens/google_sample_id_token.json'
    IF @success = 0
      BEGIN

        PRINT 'Failed to load the JSON file...'
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @jsonToken
        RETURN
      END

    -- Get the id_token;
    DECLARE @sbIdToken int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbIdToken OUT

    EXEC sp_OAMethod @jsonToken, 'StringOf', @sTmp0 OUT, 'id_token'
    EXEC sp_OAMethod @sbIdToken, 'Append', @success OUT, @sTmp0

    -- Get the signature in base64url format.
    -- The header + payload remains in sbIdToken.
    DECLARE @sig_b64Url nvarchar(4000)
    EXEC sp_OAMethod @sbIdToken, 'GetAfterFinal', @sig_b64Url OUT, '.', 1
    DECLARE @headerPlusPayload nvarchar(4000)
    EXEC sp_OAMethod @sbIdToken, 'GetAsString', @headerPlusPayload OUT


    PRINT @sig_b64Url

    PRINT @headerPlusPayload

    -- ---------------------------------------------

    -- Try validating with each cert's public key.
    -- Hopefully one will be the key that verifies.

    DECLARE @rsa int
    EXEC @hr = sp_OACreate 'Chilkat.Rsa', @rsa OUT

    EXEC sp_OASetProperty @rsa, 'EncodingMode', 'base64url'

    DECLARE @jsonKey int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonKey OUT

    DECLARE @pubKey int
    EXEC @hr = sp_OACreate 'Chilkat.PublicKey', @pubKey OUT

    DECLARE @numKeys int
    EXEC sp_OAMethod @json, 'SizeOfArray', @numKeys OUT, 'keys'
    DECLARE @i int
    SELECT @i = 0
    WHILE @i < @numKeys
      BEGIN
        EXEC sp_OASetProperty @json, 'I', @i

        EXEC sp_OAMethod @json, 'ObjectOf2', @success OUT, 'keys[i]', @jsonKey

        EXEC sp_OAMethod @jsonKey, 'Emit', @sTmp0 OUT
        EXEC sp_OAMethod @pubKey, 'LoadFromString', @success OUT, @sTmp0
        IF @success = 0
          BEGIN
            EXEC sp_OAGetProperty @pubKey, 'LastErrorText', @sTmp0 OUT
            PRINT @sTmp0
            EXEC @hr = sp_OADestroy @http
            EXEC @hr = sp_OADestroy @json
            EXEC @hr = sp_OADestroy @jsonToken
            EXEC @hr = sp_OADestroy @sbIdToken
            EXEC @hr = sp_OADestroy @rsa
            EXEC @hr = sp_OADestroy @jsonKey
            EXEC @hr = sp_OADestroy @pubKey
            RETURN
          END


        PRINT @i
        EXEC sp_OAMethod @pubKey, 'GetPem', @sTmp0 OUT, 1
        PRINT @sTmp0

        EXEC sp_OAMethod @rsa, 'UsePublicKey', @success OUT, @pubKey

        DECLARE @bVerified int
        EXEC sp_OAMethod @rsa, 'VerifyStringENC', @bVerified OUT, @headerPlusPayload, 'sha256', @sig_b64Url

        PRINT 'bVerified = ' + @bVerified

        SELECT @i = @i + 1
      END

    -- The output is:

    -- 0
    -- -----BEGIN RSA PUBLIC KEY-----
    -- MIIBCgKCAQEA4RIrO30287Wsq3gqXCMkUYMVAeI3H8LVE6IXR1krdFeGnZLiGUPw
    -- cbkeVpXf3lmJdsStOg+jijces2DZCfPyIBiQuLYfxxmAZE6ErJ0QJFg1stwli2Pz
    -- 9ncYhFoqi8pXr7kEzEJBTzX4thuw56ydbGsshSEznPXoerCJOc7UI2+n0wFCWQ4Y
    -- LHbh/PrWt4vdadyUUUW/QpQHXQLdD8q/Qwqdj0O9zlJE7R6Elw2E9EqnHyIGu1hm
    -- LxhqrTru1M18SUhONYbVskV/BCEdVKs//X96849HorWQDCAgVMWfGsdMVq55FAdJ
    -- 680N5UmQDRynIZ4+PeNGN4S9iw2mbMNEBQIDAQAB
    -- -----END RSA PUBLIC KEY-----
    -- 
    -- bVerified = True
    -- 1
    -- -----BEGIN RSA PUBLIC KEY-----
    -- MIIBCgKCAQEAxT/ngLZNmT5GBdkLtJZjNeTB+8B5yWgrq/e5eMZ1hrZhcmLK+dSn
    -- IkpOPV8/OekV67EnQ7I4II2rcNJnHGrGKZziXO3XN2gtUHE+mBJC99oULSbX/QwB
    -- Kz7gC/IBPq9EuxTt6Oq6fPkVQ9DbRIgWJSEGBF/KRaNl3kyAlIZfpY7XgHyJTTv8
    -- E7yAcYKPR+36gzdl+ps0sDLKzUuAtZNq8llK0u80z6AtAUIYwWdkEhM9upy6keKI
    -- TasIxcsO7M6kZPINUSbh6t5VAm8FuqRmxpgg+9c9/GQSGd89InVypoVzWLQ+wOGg
    -- 5G4H6JqIgtj0TRFt4gK0eoFi2U0d3l8bcwIDAQAB
    -- -----END RSA PUBLIC KEY-----
    -- 
    -- bVerified = False

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @jsonToken
    EXEC @hr = sp_OADestroy @sbIdToken
    EXEC @hr = sp_OADestroy @rsa
    EXEC @hr = sp_OADestroy @jsonKey
    EXEC @hr = sp_OADestroy @pubKey


END
GO