substitute to Eval function
-
I need a substitute for de access function eval in sql or reporting server.
I have a string like '10.10+20.20--10'. The resulte is 40.3
thanks in advance
-
Hi,
i have a similar problem and i have solve it with a user function like this:
--select dbo.fnScontoStringaInNumero('3,5+3+0', '+')
-- per motivi di performance il programmatore deve controllare dall'esterno che i dati in ingresso siano corretti
ALTER function [dbo].[fnScontoStringaInNumero]
(
@scontoEsteso varchar(20),
@separatore char(1)
 
returns decimal(8, 5)
as
begin
declare @scontoNumero decimal(19, 6)
declare @posizioneSeparatore smallint
set @scontoNumero = 100
set @scontoEsteso = replace(ltrim(rtrim(@scontoEsteso)), ',', '.')
if (@scontoEsteso <> '')
begin
-- calcolo dello sconto numerico equivalente alla stringa in ingresso
while (@scontoEsteso <> '')
begin
set @posizioneSeparatore = charindex(@separatore, @scontoEsteso)
if (@posizioneSeparatore <> 0)
begin
set @scontoNumero = @scontoNumero * (1 - (cast(substring(@scontoEsteso, 1, @posizioneSeparatore - 1) as decimal(19, 6)) / 100))
set @scontoEsteso = substring(@scontoEsteso, @posizioneSeparatore + 1, len(@scontoEsteso))
end
else
begin
set @scontoNumero = @scontoNumero * (1 - (cast(@scontoEsteso as decimal(19, 6)) / 100))
set @scontoEsteso = ''
end
end
end
if (@scontoNumero < 0)
begin
set @scontoNumero = 0
end
return cast((100 - @scontoNumero) as decimal(8, 5))
end
Bye
mandu
-
Try EXEC. Except that you have to put SELECT in front of the expression. For example:
EXEC('SELECT 10 + 20')
Will yield 30.
Note that this method gives poor performance if used in part of a larger SELECT statement. I recommend declaring a variable, and setting it to the value first.
-
Thanks a lot.
I did this.
create procedure dbo.calcula_formula
(
@formula varchar(2000)
)
as
/*
CONTROLE DE VERSÕES
VS. QUEM DATA O QUE
1.00 LCF\luiz 30/05/06
*/
begin
set @formula = replace (@formula, '--','+')
exec ('select ' + @formula )
end
-
Yup, that pretty much ought to work.
As a critical note, this method opens you up to a problem known as SQL injection. That's when a hacker is able to slip SQL commands into your input variables. This is a very common problem with the EXEC command.
For instance, you're working under the assumption your @formula will look something like:
10.10 + 20.20 * 3
A nice, neat arithmetic statement. But, suppose a hacker entered this:
1;TRUNCATE TABLE syscolumns;
Your EXEC statement would work fine. It would SELECT 1, returning a value of 1. It would then delete all of the metadata in the syscolumns table. Poof, you have one very hosed database.
The lesson is to always validate your input before you submit it to your procedure. And, if you're using EXEC, try and validate it in the procedure, too (just searching for semicolons is a good tip, but hardly bulletproof).
-
Thanks Marshall, but how could i "just searching for semicolons"?
-
Because de caracter ";" is not necessary.
Ex.:
calcula_formula '10+10/3TRUNCATE TABLE cfop'
Results
(1 row(s) affected)
Server: Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'cfop' because it is being referenced by a FOREIGN KEY constraint.
-
In your procedure, you would put something like:
IF CHARINDEX(';',@formula) 0 THEN
BEGIN
-- raise error indicating possible injection
END
ELSE
BEGIN
-- put your EXEC statement here
END
Since a semicolon is not a character you would expect to see in a standard arithmetic expression, its appearance would be enough to warrant stopping the procedure. Or, you can get fancier with more sophisticated error responses, if you want.
-
Thanks again Marshall,
But look
calcula_formula '10+10/3TRUNCATE TABLE cfop'
Results
(1 row(s) affected)
Server: Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'cfop' because it is being referenced by a FOREIGN KEY constraint.
I did not put a ';' caracter on the string, but the sql tried to execute the truncate command.
-
The point is that someone could drop the table, delete everything, update everything as he wishes. It's not a good idea to use something like this without strong validation.
-
Huh. I didn't think it would actually evaluate that. Well, there's proof that stripping semicolons is not sufficient protection.
-
Thanks Marshall.
Any idea for a strong protection?
-
Validate that all characters are either operators (+ , . X / - ( ) ) or numbers (0-9). Everything else doesn't have its place in an equation.
-
Thank you RGR´us.
But a equation isn´t that simple. Where have ABS, sin, cos, or, and, not, (x*y)^n, etc. and much more. I will have to store a dictionary in a table to check this.
-
You're starting to ask a lot for the server... Maybe you'd be better off using something like excel to do those calculations...
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply