A bad, but awesome, thing. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

Other Topics

A bad, but awesome, thing.

Author

Topic

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-20 : 06:44:42

My DBA came up with a challenge recently.... Write a function to evaluate a mathematical expression (basically wrap something like:


SELECT 1 / 0.3
SELECT 5 + 3

But able to run against a column of such expressions.

This is obviously a BAD THING (tm) but very interesting.

I decided to write a CLR. It was exceedingly simple:



using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(
        DataAccess = DataAccessKind.Read,
        SystemDataAccess = SystemDataAccessKind.Read
        )
    ]
    public static object EvaluateScalar(string scalarExpression)
    {
        using (var conn = new SqlConnection("context connection=true"))
        {
            conn.Open();
            var cmd = new SqlCommand("SELECT " + scalarExpression, conn);
            return cmd.ExecuteScalar();   
        }
    }
}

And then you can do stuff like this!



CREATE FUNCTION dbo.EvaluateScalar(@sql nvarchar(max)) 
RETURNS SQL_VARIANT
AS 
EXTERNAL  NAME EvalExpression.[UserDefinedFunctions].EvaluateScalar 
GO

DECLARE @foo TABLE (
      [label] VARCHAR(255)
      , [expression] NVARCHAR(MAX)
      )
INSERT @foo ([label], [expression])
VALUES ('a', '1 + 1')
     , ('b', '34 * 545.5')
     , ('c', '10 / 453.3')

SELECT
      f.[label]
      , f.[expression]
      , dbo.EvaluateScalar(f.[expression])
FROM
      @foo AS f

Which produced these results


label expression 
----- ---------- ----------
a     1 + 1      2
b     34 * 545.5 18547.0
c     10 / 453.3 0.022060

I doubt this will ever be useful to anyone but I found it really enjoyable to write!

Definitely NOT PRODUCTION CODE though.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2012-01-20 : 09:12:41

I'm not sure Its A Bad Thing (TM-to-you!)

We have plenty of instances where Client is best served by being able to enter a Formula for a calculation. For example, for Discount Vouchers we allow the Client to include a Percentage and a Fixed-Amount discount (so you can have 10% off, or $5 discount - or both) ... but then the Clients want "10% up to $100 order total, and 15% above that" or "15% if total more than $100, otherwise 10%" ... building all the possibilities into our system means we don't have good response-time to new requests from a client (our standard response is "In the next release" which is always at least 6 months away, or "$99999999 for expedited rollout" which the client is rarely happy with (we have to run full QA cycle and built a rollout-package, which takes a couple of days; its fine if the MODs the client wants are huge, not so good for a single Discount Voucher Formula)

So I'm in the "This May Be A Good Thing" (tm) camp

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-20 : 10:04:51

well, by all means, take it and run with it. It even lets you call stored procs from a function and run dynamic sql.

you can even do stuff like

SELECT dbo.EvaluateScalar('23 * ( SELECT TOP 1 [Price] FROM MyTable WHERE [clientID] = 342 )')

I think it's safe in that it *shouldn't* be able to change the db (the access on the function is set to
DataAccess = DataAccessKind.Read,
SystemDataAccess = SystemDataAccessKind.Read

so.... hopefully OK.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2012-01-20 : 10:16:45

"SELECT dbo.EvaluateScalar('23 * ( SELECT TOP 1 [Price] FROM MyTable WHERE [clientID] = 342 )')"

Client will have a field day, and the sort-out consultancy costs will probably be astronomic. I'm liking it already !!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-20 : 10:28:10

Can I also do this?

select dbo.EvaluateScalar('2*2; drop CharliesMostImportantTable;')

Or would that get stopped (I hope it does) because it is within a function?

Assuming it does not get stopped, Charlie, you might need to go into witness protection program, because of all the people who may be trying to find you to get revenge. And, you would be just like Eric Gustafson! http://www.youtube.com/watch?v=8N4mE6tnS4g

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-20 : 10:40:36

I don't think you can do that.

I tried to drop a table after an evaluation -- it didn't error but the table didn't drop. Because the CLR is marked as READ with no WRITE permission then this fails


select dbo.EvaluateScalar('4 AS [woo] INTO _foo')

Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "EvaluateScalar":
System.Data.SqlClient.SqlException: Invalid use of a side-effecting operator 'SELECT INTO' within a function.
System.Data.SqlClient.SqlException:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlDataReaderSmi.InternalNextResult(Boolean ignoreNonFatalMessages)
at System.Data.SqlClient.SqlDataReaderSmi.NextResult()
at System.Data.SqlClient.SqlCommand.RunExecuteReaderSmi(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteScalar()
at UserDefinedFunctions.EvaluateScalar(String scalarExpression)
.

But how much to trust I do not know......

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2012-01-20 : 10:44:26

I'm sure your government won't allow you to be extradited T.C. <Thud!>

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-20 : 10:57:57

Well, not unless I do something heinous like hosting links to suspect materials. The fact that it's legal in the country that I live and pay taxes in is, *of course* absolutely irrelevant.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources