URLEncode

  • R Michael

    SSCarpal Tunnel

    Points: 4211

    Comments posted to this topic are about the item URLEncode

    SQL guy and Houston Magician

  • Blavette

    SSC Enthusiast

    Points: 117

    Hi,

    This is a cool script, but it would be better if you had join more explanation about the table 'numbers'.

    We have deduced that we must insert number from 1 to 256 in the table. without that, of course it doesn't work;)

    Good job at all

  • alzowze-1025247

    Valued Member

    Points: 73

    Hello Robert Cary,

    Thank you for the cool TSQL URLEncode script, it was almost exactly what I was looking for.

    I slightly modified it to allieviate the need for creating/populating table numbers by selecting from a derived table using RowNum() as the incremented field [num]

    Alzowze ";0)

    ps... You wouldn't have a TSQL Is_UTF8 Function in your box of tricks would ya?

    GO

    SET ANSI_NULLS ON

    GO

    IF EXISTS (

    SELECT 1

    FROM dbo.sysobjects

    WHERE id = OBJECT_ID(N'[dbo].[URLEncode]')

    AND xtype IN (N'FN', N'IF', N'TF'))

    BEGIN

    DROP FUNCTION [dbo].[URLEncode]

    END

    GO

    CREATE FUNCTION [dbo].[URLEncode]

    (@decodedString VARCHAR(4000))

    RETURNS VARCHAR(4000)

    AS

    BEGIN

    /*******************************************************************************************************

    * dbo.URLEncode

    * Source: http://www.sqlservercentral.com/scripts/URL/62679/

    * Creator: Robert Cary

    * Date: 03/18/2008

    *

    * Notes:

    *

    *

    * Usage:

    * select dbo.URLEncode('K8%/fwO3L mEQ*.}')

    * select dbo.URLEncode('http://www.sqlservercentral.com/scripts/URL/62679/')

    *

    * Modifications:

    * Developer Name Date Brief description

    * ------------------- ----------- ------------------------------------------------------------

    *

    * Alzowze 21-Jul-09 Modifed to select from derived table.

    * No need to create/populate table numbers.

    * Used Union All, to ensure entire string gets encoded

    * if LEN(@decodedString) = Maximum of 4000

    ********************************************************************************************************/

    DECLARE @encodedString VARCHAR(4000)

    IF @decodedString LIKE '%[^a-zA-Z0-9*-.!_]%' ESCAPE '!'

    BEGIN

    SELECT @encodedString = REPLACE(

    COALESCE(@encodedString, @decodedString),

    SUBSTRING(@decodedString,num,1),

    '%' + SUBSTRING(master.dbo.fn_varbintohexstr(CONVERT(VARBINARY(1),ASCII(SUBSTRING(@decodedString,num,1)))),3,3))

    FROM (

    SELECT ROW_NUMBER() OVER(ORDER BY number DESC) AS num

    FROM master.dbo.spt_values

    UNION All

    SELECT ROW_NUMBER() OVER(ORDER BY number DESC) AS num

    FROM master.dbo.spt_values

    ) AS num

    WHERE num BETWEEN 1 AND LEN(@decodedString) AND SUBSTRING(@decodedString,num,1) like '[^a-zA-Z0-9*-.!_]' ESCAPE '!'

    END

    ELSE

    BEGIN

    SELECT @encodedString = @decodedString

    END

    RETURN @encodedString

    END

    GO

  • dinguot

    Valued Member

    Points: 55

    Modified function for languages using accented letters (french for example)

    CREATE FUNCTION dbo.URLEncode(

    @decodedString VARCHAR(4000)

    )

    RETURNS VARCHAR(4000)

    AS

    BEGIN

    DECLARE @encodedString VARCHAR(4000)

    IF @decodedString LIKE '%[^azertyuiopmlkjhgfdsqwxcvbnAZERTYUIOPMLKJHGFDSQWXCVBN0-9*-.!_]%' ESCAPE '!'

    BEGIN

    SELECT @encodedString = REPLACE(

    COALESCE(@encodedString, @decodedString),

    SUBSTRING(@decodedString,num,1),

    '%' + SUBSTRING(master.dbo.fn_varbintohexstr(CONVERT(VARBINARY(1),ASCII(SUBSTRING(@decodedString,num,1)))),3,3))

    FROM dbo.numbers

    WHERE num BETWEEN 1 AND LEN(@decodedString) AND SUBSTRING(@decodedString,num,1) like '[^azertyuiopmlkjhgfdsqwxcvbnAZERTYUIOPMLKJHGFDSQWXCVBN0-9*-.!_]' ESCAPE '!'

    END

    ELSE

    BEGIN

    SELECT @encodedString = @decodedString

    END

    RETURN @encodedString

    END

  • Karl Dirck

    SSC Journeyman

    Points: 86

    Very Handy Function, thank you!

    I found a bug though. It seems while it can URL encode UNENCODED URLs, if the URL already has an URL Encoded value, and you need to URL Encode the URL and it's already encoded value, you get erroneous and unexpected results.

    To reproduce the issue:

    DECLARE @base varchar(1024);

    DECLARE @redirector varchar(1024);

    DECLARE @t varchar(1024);

    SET @redirector = 'http://redirector.reference.com/source=';

    SET @base = 'http://www.sqlservercentral.com/scripts/URL/62679/';

    SET @t = dbo.URLEncode(@base);

    PRINT @t; -- http%3a%2f%2fwww.sqlservercentral.com%2fscripts%2fURL%2f62679%2f

    SET @t = @redirector + dbo.URLEncode(@base);

    PRINT @t; -- http://redirector.reference.com/source=http%3a%2f%2fwww.sqlservercentral.com%2fscripts%2fURL%2f62679%2f

    SET @t = @redirector + dbo.URLEncode(@t);

    PRINT @t; -- http://redirector.reference.com/source=http%252525252525253a%252525252525252f%252525252525252fredirector.reference.com%252525252525252fsource%252525252525253dhttp%252525252525253a%252525252525252f%252525252525252fwww.sqlservercentral.com%252525252525252fscripts%252525252525252fURL%252525252525252f62679%252525252525252f

    Here is the expected value calculated using ASP.Net System.Web.HttpServerUtility.UrlEncode()

    http%3a%2f%2fredirector.reference.com%2fsource%3dhttp%253a%252f%252fwww.sqlservercentral.com%252fscripts%252fURL%252f62679%252f

    I researched the W3C recommendation and tested all valid non-alphanumeric characters in several platforms then encoded all characters that were encoded by any platform. Some encoded a space char (x20) as plus (+) as per the W3C, others as %20. Based testing and for consistency, I chose to encode all spaces as %20 for this implementation. Feel free to change it if your implementation requires.

    Here is the complete solution that I am using:

    -- Drop if already exists

    IF EXISTS (

    SELECT 1

    FROM dbo.sysobjects

    WHERE id = OBJECT_ID(N'[dbo].[URLEncode]')

    AND xtype IN (N'FN', N'IF', N'TF'))

    BEGIN

    DROP FUNCTION [dbo].[URLEncode]

    END

    GO

    CREATE FUNCTION [dbo].[URLEncode]

    (@decodedString VARCHAR(4000))

    RETURNS VARCHAR(4000)

    AS

    BEGIN

    /*******************************************************************************************************

    * dbo.URLEncode

    * Creator: Robert Cary

    * Date: 03/18/2008

    *

    * Notes:

    *

    *

    * Usage:

    select dbo.URLEncode('K8%/fwO3L mEQ*.}')

    * Modifications:

    * Developer Name Date Brief description

    * ------------------- ----------- ------------------------------------------------------------

    * Karl Dirck 2013-12-12 Bug fix for encoding previously encoded values and encoded characters

    *

    ********************************************************************************************************/

    DECLARE @encodedString VARCHAR(4000)

    IF @decodedString LIKE '%[^a-zA-Z0-9.!_-\%]%' ESCAPE '!'

    BEGIN

    --- Replace % with %25, that way, we can skip replacing the

    --- % character once we're looping through the string.

    SET @decodedString = REPLACE(@decodedString, '%', '%25');

    SELECT @encodedString = REPLACE(

    COALESCE(@encodedString, @decodedString),

    SUBSTRING(@decodedString, num, 1),

    '%' + SUBSTRING(master.dbo.fn_varbintohexstr(CONVERT(VARBINARY(1),ASCII(SUBSTRING(@decodedString, num, 1)))), 3, 3))

    FROM dbo.numbers

    WHERE num BETWEEN 1 AND LEN(@decodedString) AND SUBSTRING(@decodedString, num, 1) like '[^a-zA-Z0-9.!_-\%]' ESCAPE '!'

    END

    ELSE

    BEGIN

    SELECT @encodedString = @decodedString

    END

    RETURN @encodedString

    END

    GO

    Corrected value:

    http://redirector.reference.com/source=http%3a%2f%2fredirector.reference.com%2fsource%3dhttp%253a%252f%252fwww.sqlservercentral.com%252fscripts%252fURL%252f62679%252f

    NOTE: The web site specified in the variable @redirector is fictional and used to simplify the case discovered to be an issue.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply