How to run string math expression in SQL Server (trigger or function)

Question

I want to run:

 select Mycalculatefunction('((3*4)-3)*5') 
 select ('((3*4)-3)*5')

OUTPUT: ((3*4)-3)*5 wrong (not int value)

My desired output is: 45

I defined a stored procedure:

create PROCEDURE dbo.Eval
    (@exp varchar(MAX))
AS
    SET NOCOUNT ON

    DECLARE @SQLString NVARCHAR(MAX)
    SET @SQLString = 'SELECT '+@exp

    EXEC sp_executesql @SQLString

I call it:

exec dbo.Eval '((3*4)-3)*5'

How can I do in this process is the trigger?

Answer 1

image 1

 ALTER FUNCTION [dbo].[Calculate]
    ( @expression AS VARCHAR(MAX) 
    )
    RETURNS xml
    AS
    BEGIN
      -- routine body goes here, e.g.
      -- SELECT 'Navicat for SQL Server'
    DECLARE @result xml
    declare @x xml=''
    --I can not pass as a parameter
    select @result=@x.query(('(4*3)*5-10')) 

    return   @result; 


END

i call this function:

  SELECT  CAST(CAST(CAST(dbo.[Calculate]('How do I pass parameters') AS XML) AS VARCHAR(100)) AS DECIMAL(4,2))

Output:

50.00

select  @result=@x.query('sql:variable("@expression")') 

result: '2+2' :(((

Answer 2

Your SP is vulnerable to injection. F.e. I pass exec dbo.Eval '1;DROP TABLE some_table;'. Better use xml.query:

CREATE PROCEDURE dbo.Eval 
    @formula nvarchar(max)
AS
DECLARE @sql nvarchar(max)

SELECT @sql = N'
DECLARE @x xml = ''''
SELECT CAST(@x.query('''+@formula+''') as nvarchar(max))'

EXEC sp_executesql @sql

Then

EXEC dbo.Eval '((3*4)-3)*5'

Output:

45

Triggers part (as there were no info about your tables, just general explanation, I add full batch with comments):

--Create table that will store Formulas
CREATE TABLE Formulas (
    ID int IDENTITY(1,1) NOT NULL,
    Formula nvarchar(max) NULL,
    CONSTRAINT PK_ID PRIMARY KEY (ID)
) 
GO
--Create table to store results of the formulas
CREATE TABLE Results (
    T1_ID int NOT NULL,
    Result int NULL
) 
GO
--Linked by ID
ALTER TABLE Results ADD CONSTRAINT FK_Formulas_Results FOREIGN KEY (T1_ID)
REFERENCES Formulas (ID)
GO
--Create a Table Valued Parameter
CREATE TYPE FormulaResults AS TABLE ( 
    ID int NOT NULL,
    Formula nvarchar(max) NULL
)
GO
--Create a procedure to do the count
CREATE PROCEDURE dbo.GetResults 
    @TVP FormulaResults READONLY
AS
DECLARE @sql nvarchar(max)

SELECT @sql = N'DECLARE @x xml = '''' '

SELECT @sql = @sql + 'SELECT '+CAST(ID as nvarchar(max))+' as ID, CAST(@x.query('''+Formula+''') as nvarchar(max)) UNION ALL '
FROM @TVP

SELECT @sql = LEFT(@sql,LEN(@sql)-LEN('UNION ALL '))

EXEC sp_executesql @sql
GO
--Create a trigger that will count formula after insert and update
CREATE TRIGGER GetResultsTrigger
ON Formulas
AFTER INSERT, UPDATE
AS
DECLARE @FormulaTVP AS FormulaResults
DECLARE @Results TABLE(
    T1_ID int NOT NULL,
    Result int NULL
) 


INSERT INTO @FormulaTVP
SELECT *
FROM inserted

INSERT INTO @Results
EXEC dbo.GetResults @FormulaTVP

MERGE Results r
USING @Results s
ON r.T1_ID = s.T1_ID
WHEN NOT MATCHED THEN
    INSERT VALUES (s.T1_ID, s.Result)
WHEN MATCHED THEN
    UPDATE SET Result = s.Result;

After that run:

INSERT INTO [Formulas] VALUES
('1+3'),('2+2*8')


SELECT  [ID],
        [Formula]
FROM [Test].[dbo].[Formulas]

SELECT [T1_ID],
        [Result]
FROM [Test].[dbo].[Results]

Output:

ID  Formula
1   1+3
2   2+2*8

T1_ID   Result
1       4
2       18