SQL Server
SQL Server
Aadhaar Paperless Offline e-kyc
See more XML Digital Signatures Examples
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.Chilkat SQL Server Downloads
-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr 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.
-- 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.Zip', @zip OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
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 @hr = sp_OACreate 'Chilkat.ZipEntry', @entry OUT
EXEC sp_OAMethod @zip, 'EntryAt', @success OUT, 0, @entry
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @zip, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @zip
EXEC @hr = sp_OADestroy @entry
RETURN
END
-- To get the contents, we need to specify the Share Phrase.
DECLARE @sharePhrase nvarchar(4000)
SELECT @sharePhrase = 'Lock@487'
EXEC sp_OASetProperty @zip, 'DecryptPassword', @sharePhrase
DECLARE @bdXml int
EXEC @hr = sp_OACreate 'Chilkat.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 @zip
EXEC @hr = sp_OADestroy @entry
EXEC @hr = sp_OADestroy @bdXml
RETURN
END
-- First verify the XML digital signature.
DECLARE @dsig int
EXEC @hr = sp_OACreate 'Chilkat.XmlDSig', @dsig OUT
EXEC sp_OAMethod @dsig, 'LoadSignatureBd', @success OUT, @bdXml
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @dsig, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @zip
EXEC @hr = sp_OADestroy @entry
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.Cert', @cert OUT
EXEC sp_OAMethod @cert, 'LoadFromFile', @success OUT, 'qa_data/xml_dsig/uidai_auth_sign_prod_2023.cer'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @cert, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @zip
EXEC @hr = sp_OADestroy @entry
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 @hr = sp_OACreate 'Chilkat.PublicKey', @pubKey OUT
EXEC sp_OAMethod @cert, 'GetPublicKey', @success OUT, @pubKey
EXEC sp_OAMethod @dsig, 'SetPublicKey', @success OUT, @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 @entry
EXEC @hr = sp_OADestroy @bdXml
EXEC @hr = sp_OADestroy @dsig
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @pubKey
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.Crypt2', @crypt OUT
EXEC sp_OASetProperty @crypt, 'HashAlgorithm', 'sha256'
EXEC sp_OASetProperty @crypt, 'EncodingMode', 'hexlower'
DECLARE @strToHash nvarchar(4000)
SELECT @strToHash = '1234567890Lock@487'
DECLARE @bdHash int
EXEC @hr = sp_OACreate 'Chilkat.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(4000)
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.Xml', @xml OUT
EXEC sp_OAMethod @xml, 'LoadBd', @success OUT, @bdXml, 1
DECLARE @m_hash nvarchar(4000)
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(4000)
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(4000)
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 @entry
EXEC @hr = sp_OADestroy @bdXml
EXEC @hr = sp_OADestroy @dsig
EXEC @hr = sp_OADestroy @cert
EXEC @hr = sp_OADestroy @pubKey
EXEC @hr = sp_OADestroy @crypt
EXEC @hr = sp_OADestroy @bdHash
EXEC @hr = sp_OADestroy @xml
END
GO