(SQL Server) Aadhaar Paperless Offline e-kyc

Opens an encrypted .zip containing Aadhaar Paperless Offline e-KYC XML. Gets the XML and validates the digital signature. Then computes the hash for the mobile number and Email ID.

For more information, see https://uidai.gov.in/ecosystem/authentication-devices-documents/about-aadhaar-paperless-offline-e-kyc.html

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

// 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
    DECLARE @sTmp0 nvarchar(max)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- Open the .zip containing the Aadhaar Paperless Offline e-KYC XML.
    -- The .zip is encrypted using the "Share Phrase".
    DECLARE @zip int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Zip', @zip OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @zip, 'OpenZip', @success OUT, 'qa_data/xml_dsig/offline_paperless_kyc.zip'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @zip, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @zip
        RETURN
      END

    -- The .zip should contain 1 XML file.
    DECLARE @entry int
    EXEC sp_OAMethod @zip, 'GetEntryByIndex', @entry OUT, 0
    EXEC sp_OAGetProperty @zip, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @zip, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @zip
        RETURN
      END

    -- To get the contents, we need to specify the Share Phrase.
    DECLARE @sharePhrase nvarchar(max)
    SELECT @sharePhrase = 'Lock@487'
    EXEC sp_OASetProperty @zip, 'DecryptPassword', @sharePhrase

    DECLARE @bdXml int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.BinData', @bdXml OUT

    -- The XML file will be unzipped into the bdXml object.
    EXEC sp_OAMethod @entry, 'UnzipToBd', @success OUT, @bdXml
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @entry, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @entry

        EXEC @hr = sp_OADestroy @zip
        EXEC @hr = sp_OADestroy @bdXml
        RETURN
      END

    EXEC @hr = sp_OADestroy @entry

    -- First verify the XML digital signature.
    DECLARE @dsig int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.XmlDSig', @dsig OUT

    EXEC sp_OAMethod @dsig, 'LoadSignatureBd', @success OUT, @bdXml
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @dsig, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @zip
        EXEC @hr = sp_OADestroy @bdXml
        EXEC @hr = sp_OADestroy @dsig
        RETURN
      END

    -- The UIDAI XML signature does not contain the KeyInfo, so we must load the uidai certificate
    -- and indicate that its public key is to be used for verifying the signature.
    DECLARE @cert int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Cert', @cert OUT

    EXEC sp_OAMethod @cert, 'LoadFromFile', @success OUT, 'qa_data/xml_dsig/uidai_auth_sign_prod_2023.cer'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @zip
        EXEC @hr = sp_OADestroy @bdXml
        EXEC @hr = sp_OADestroy @dsig
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END

    -- Get the certificate's public key.
    DECLARE @pubKey int
    EXEC sp_OAMethod @cert, 'ExportPublicKey', @pubKey OUT
    EXEC sp_OAMethod @dsig, 'SetPublicKey', @success OUT, @pubKey
    EXEC @hr = sp_OADestroy @pubKey

    -- The XML in this example contains only 1 signature.
    DECLARE @bVerifyReferenceDigests int
    SELECT @bVerifyReferenceDigests = 1
    DECLARE @bVerified int
    EXEC sp_OAMethod @dsig, 'VerifySignature', @bVerified OUT, @bVerifyReferenceDigests
    IF @bVerified = 0
      BEGIN
        EXEC sp_OAGetProperty @dsig, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0

        PRINT 'The signature was not valid.'
        EXEC @hr = sp_OADestroy @zip
        EXEC @hr = sp_OADestroy @bdXml
        EXEC @hr = sp_OADestroy @dsig
        EXEC @hr = sp_OADestroy @cert
        RETURN
      END


    PRINT 'The XML digital signature is valid.'

    -- Let's compute the hash for the Mobile Number.

    -- 	Hashing logic for Mobile Number :
    -- 	Sha256(Sha256(Mobile+SharePhrase))*number of times last digit of Aadhaar number
    -- 	(Ref ID field contains last 4 digits).
    -- 
    -- 	Example :
    -- 	Mobile: 1234567890
    -- 	Aadhaar Number:XXXX XXXX 3632
    -- 	Passcode : Lock@487
    -- 	Hash: Sha256(Sha256(1234567890Lock@487))*2
    -- 	In case of Aadhaar number ends with Zero we will hashed one time.

    DECLARE @crypt int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Crypt2', @crypt OUT

    EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'sha256'
    EXEC sp_OASetProperty @crypt, 'EncodingMode', 'hexlower'

    DECLARE @strToHash nvarchar(max)
    SELECT @strToHash = '1234567890Lock@487'
    DECLARE @bdHash int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.BinData', @bdHash OUT

    EXEC sp_OAMethod @bdHash, 'AppendString', @success OUT, @strToHash, 'utf-8'

    -- Hash a number of times equal to the last digit of your Aadhaar number.
    -- If the Aadhaar number ends with 0, then hash one time.
    -- For this example, we'll just set the number of times to hash
    -- for the case where an Aadhaar number ends in "9"
    DECLARE @numTimesToHash int
    SELECT @numTimesToHash = 9
    DECLARE @i int

    SELECT @i = 1
    WHILE @i <= @numTimesToHash
      BEGIN
        DECLARE @tmpStr nvarchar(max)
        EXEC sp_OAMethod @crypt, 'HashBdENC', @tmpStr OUT, @bdHash
        EXEC sp_OAMethod @bdHash, 'Clear', @success OUT
        EXEC sp_OAMethod @bdHash, 'AppendString', @success OUT, @tmpStr, 'utf-8'
        SELECT @i = @i + 1
      END


    EXEC sp_OAMethod @bdHash, 'GetString', @sTmp0 OUT, 'utf-8'
    PRINT 'Computed Mobile hash = ' + @sTmp0

    -- Let's get the mobile hash stored in the XML and compare it with our computed hash.
    DECLARE @xml int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Xml', @xml OUT

    EXEC sp_OAMethod @xml, 'LoadBd', @success OUT, @bdXml, 1
    DECLARE @m_hash nvarchar(max)
    EXEC sp_OAMethod @xml, 'ChilkatPath', @m_hash OUT, 'UidData|Poi|(m)'


    PRINT 'Stored Mobile hash   = ' + @m_hash

    -- Now do the same thing for the email hash:

    SELECT @strToHash = 'abc@gm.comLock@487'
    EXEC sp_OAMethod @bdHash, 'Clear', @success OUT
    EXEC sp_OAMethod @bdHash, 'AppendString', @success OUT, @strToHash, 'utf-8'

    SELECT @i = 1
    WHILE @i <= @numTimesToHash
      BEGIN
        DECLARE @tmpStr nvarchar(max)
        EXEC sp_OAMethod @crypt, 'HashBdENC', @tmpStr OUT, @bdHash
        EXEC sp_OAMethod @bdHash, 'Clear', @success OUT
        EXEC sp_OAMethod @bdHash, 'AppendString', @success OUT, @tmpStr, 'utf-8'
        SELECT @i = @i + 1
      END


    EXEC sp_OAMethod @bdHash, 'GetString', @sTmp0 OUT, 'utf-8'
    PRINT 'Computed Email hash = ' + @sTmp0

    DECLARE @e_hash nvarchar(max)
    EXEC sp_OAMethod @xml, 'ChilkatPath', @e_hash OUT, 'UidData|Poi|(e)'

    PRINT 'Stored Email hash   = ' + @e_hash

    EXEC @hr = sp_OADestroy @zip
    EXEC @hr = sp_OADestroy @bdXml
    EXEC @hr = sp_OADestroy @dsig
    EXEC @hr = sp_OADestroy @cert
    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @bdHash
    EXEC @hr = sp_OADestroy @xml


END
GO