locked
Encrypt/Encode, AES, Passphrase, Expiring, ASP.net, Javascript, T-SQL RRS feed

  • Question

  • User1045460610 posted

    I have a Javascript that will create a encoded url with a passphrase and expiring date. I want to figure out how to do something similar using T-SQL without creating and functions. It looks like I may be able to use ENCRYPTBYPASSPHRASE but I don't see an AES compliant encoding function in the SQL Server 2017 Cryptographic reference. I want to create that URL with T-SQL and show it on a ASP.net web page as a Check-In hyperlink. It's for a script to go in a third party portal where we can't add any user defined functions.

    <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

    <!DOCTYPE html>

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
    <title>Encryption / Decryption Test</title>
    </head>
    <body>
    <form id="form1" runat="server">
    <%--HostID:
    <input type="text" id="hostId" name="hostId" />--%>

    <h2>URL string</h2>

    <%-- <button type='button' id="now_link" onclick="displaynow()">Display Now</button>
    Display now: <span id="display_now"></span>--%>
    <br />
    <br />

    <button type='button' id="encode_link" onclick="encrypt()">Encode</button>
    <br />
    Display hostid: <span id="display_hostid"></span>
    <br />
    Display now: <span id="display_now"></span>
    <br />
    Display key: <span id="display_key"></span>
    <br />
    Display url: <span id="display_url"></span>
    <br />
    <br />
    Create link on page load, show link as &#39;Check In&#39; label:
    <br />
    Check In
    <br />
    <br />
    Or, Create link when user presses button, redirect to link:
    <br />
    <br />
    <button type='button' id="decode_link" onclick="decrypt()">Decode</button>
    Decrypt result: <span id="decrypt_result"></span>

    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.4.0/jquery.min.js"></script>
    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/crypto-js/3.1.2/rollups/aes.js"></script>
    <script type="text/javascript">
    function encrypt(hostId) {
    var now = new Date(),
    //value = document.getElementById('hostId').value + '|' + now.toISOString(),
    value = '123456',
    key = 'iaUdrdMy7H';

    var encrypted = CryptoJS.AES.encrypt(value, key);
    location.href = '/?q=' + encodeURIComponent(encrypted.toString());

    document.getElementById('display_hostid').innerText = value;
    document.getElementById('display_now').innerText = now;
    document.getElementById('display_key').innerText = key;
    document.getElementById('display_url').innerText = encrypted;
    }

    function decrypt() {
    var params = new URLSearchParams(window.location.search),
    queryString = params.get('q'),
    key = 'iaUdrdMy7H';

    if (queryString) {
    var decrypted = CryptoJS.AES.decrypt(queryString, key).toString(CryptoJS.enc.Utf8),
    temp = decrypted.split('|'),
    hostId = temp[0],
    timestamp = new Date(temp[1]),
    expired = diff_minutes(new Date(), timestamp) > 30;
    document.getElementById('decrypt_result').innerText = 'Host ID was sent ' + hostId + ', Url ' + (expired ? 'expired' : 'not expired');
    }
    }

    function diff_minutes(dt2, dt1) {
    var diff = (dt2.getTime() - dt1.getTime()) / 1000;
    diff /= 60;
    return Math.abs(Math.round(diff));
    }
    </script>
    </form>
    </body>
    </html>

    Monday, May 20, 2019 9:32 PM

Answers

  • User839733648 posted

    Hi Tom4IT,

    According to your description, I suggest that you could use ENCRYPTBYKEY() and DECRYPTBYKEY() function to achieve your requirement.

    There are several key points you should notice:

    • Create a Symmetric Key, like: 
    CREATE SYMMETRIC KEY SK03  
    WITH  
        ALGORITHM = AES_256  
        ENCRYPTION BY PASSWORD = 'ThisIsMyAES_256EncryptionTest';  
    • Create a table to save the data
    • Create a Stored Procedure to Encrypt the data, main code:
    ENCRYPTBYKEY(Key_GUID('SK03'), CONVERT(VARBINARY,@InputString2))
    • The Encode data SQL is like:
    OPEN SYMMETRIC KEY SK03 DECRYPTION BY PASSWORD='ThisIsMyAES_256EncryptionTest'  
    SELECT   
        [OriginalValue]   
        ,CONVERT(varchar(MAX), DECRYPTBYKEY([EncryptedValue]))  
    FROM  
        _EncryptionTest  
    CLOSE SYMMETRIC KEY SK03; 

    For more, you could refer to this link: https://dba.stackexchange.com/questions/167920/sql-aes-256-encryption-and-decryption

    Best Regards,

    Jenifer

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 21, 2019 8:38 AM