Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

    Unanswered: Opinions on UNSAFE CLR permission

    Hi

    I have some CLR code that references System.Web.HttpUtility.UrlDecode. In order to register the Assembly I have to set permission to UNSAFE (this is due to referencing the System.Web namespace).

    How comfy (or not) would you guys be with that? Is there a real, tangible risk? If so, I can roll my own UrlDecode function, but I'd rather not if poss.

    Ta chucks
    Testimonial:
    pootle flump
    ur codings are working excelent.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    There is a lot of powerful stuff in System.Web but you would have to be a smarter bloke than me to know how to exploit it through a registered compiled c# thingy within the sql server process. and that is the question. what door does it open and how do you get through it?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Real simple Sean - it ain't the smarts.

    Code:
    namespace OhPootie
    {
    
        public class WeLoveYou
        {
    
            [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
            public static string URLDecode(SqlString sqlInput)
            {
    
                string input = sqlInput.ToString();
     
                return System.Web.HTTPUtility(URLDecode(input);
    
            }
    
        }
    }
    Code:
    CREATE ASSEMBLY WeLoveYouPootieWeDo
    AUTHORIZATION dbo
    FROM 'E:\DLLS\WeLoveYouPootieWeDo.DLL'
    WITH PERMISSION_SET = UNSAFE
    GO
    
    CREATE FUNCTION dbo.clrURLDecode
        (
            @input     AS NVARCHAR(MAX)
        )
    RETURNS NVARCHAR(MAX)
    AS
    
        EXTERNAL NAME  WeLoveYouPootieWeDo.[OhPootie.WeLoveYou].URLDecode
    GO
    If you use SAFE instead of UNSAFE then you get an error and all sorts of warnings.

    I have decided to roll my own code in the end. It is significantly slower than the inbuilt function (about 3 times as long to run), but massively faster than the T-SQL (about .01% of the time to run) on 100k rows.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by pootle flump
    ...[OhPootie.WeLoveYou]...
    OK, you win
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    how many times would I puke debugging your code?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Thrasymachus
    how many times would I puke debugging your code?
    I'm not sure that there is any finite limit.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Standard induction materials here at the office:
    1 Ring Binder, black
    1 Organisation Chart, laminated
    1 Bucket, voluminous
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    what's with URLDecode() though? that name sucks.

    How about NoDontStopYesThatsItYESSSS()

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    will SOMEBODY please talk to me?

    I feel so alone in this topic

    What for example can you do with a CLR that you can't do with T-SQL

    And how (and now this is me being lazy) do you execute it, is it in an independent thread, does it commit on completion, and is data moved at all, is data manipulated in base, temp or table variables? Arrays?

    Tanks and Yews
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    CLR is good for things that T-SQL is not good at. I use them for 2 things mainly:

    1. complicated math
    2. complicated string parsing

    Also we have a CLR for converting between UTC and local times for any date in the past or future, for any timezone. Doing that is more complicated than you at first imagine, because of insane places like Indiana.

    EDIT: another big reason to use CLR in sql is code reuse. The class that does our time conversions is used by all our managed assemblies, not just in procs. Since it is exposed as a CLR assembly in SQL, the same code is used. It would be a huge PITA to re-implement that in T-SQL and have to maintain two code bases that do the same thing in different languages.
    Last edited by jezemine; 10-22-09 at 22:54.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Brett Kaiser
    What for example can you do with a CLR that you can't do with T-SQL
    Not much. It just does some things much better, especially string manipulation as Jesse states. I've used it for distance algorithms for example.

    Quote Originally Posted by Brett Kaiser
    And how (and now this is me being lazy) do you execute it
    For the above example:
    Code:
    SELECT dbo.clrURLDecode('Oh+pootie+we+love+you%21')
    Quote Originally Posted by Brett Kaiser
    is it in an independent thread
    Yes
    Quote Originally Posted by Brett Kaiser
    does it commit on completion
    depends if you rollback
    Quote Originally Posted by Brett Kaiser
    is data moved at all
    depends if you move data
    Quote Originally Posted by Brett Kaiser
    is data manipulated in base, temp or table variables?
    depends if you use base, temp or table variables
    Quote Originally Posted by Brett Kaiser
    Arrays?
    Hurray!
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by Brett Kaiser
    What for example can you do with a CLR that you can't do with T-SQL
    I have also had to do some rather tricky find and replace stuff with regular expressions this year. I could't imagine coding that stuff in sql and running it on the amount of data I am dealing with and it adds the convience of calling the functions directly from sql. I have not used any for production code yet. Just back of the house operations stuff.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •