karincayazilim karincayazilim - 3 months ago 18
SQL Question

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

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

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
Comments