I have a store procedure in Mysql, in this I do some query to get a string @str = 5*2+1 then I want to calculate this string and return a number.
Like in sql server I can do exec ('select'+ @str) and it returns 11
Thanks
3 Answers
PREPARE stmt1 FROM 'SELECT 5*2+1 as eval';
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
10 Comments
tadman
Why such a heavy-handed approach instead of just calling
SELECT?
John Woo
@tadman because the OP is looking for alternative of
EXEC. And also maybe because the OP is just simplifying an example.tadman
Storing SQL in your database, even a portion of it, is an extraordinarily bad idea. MySQL does not have the injection protection provided in the client layer.
John Woo
@tadman which of my answer says that it stores sql statement in the database?
tadman
If it wasn't in the database, you could simply send in the
SELECT query directly. This seems like an attempt to avoid making a permanent stored procedure and instead making a generic facility to evaluate arbitrary SQL instead, something that always ends in tears. |
You can use the following, but be careful with SQL injection when using the dynamic queries:
prepare stmt1 from 'select 5*2+1';
execute stmt1;
deallocate prepare stmt1;
Using your code example, try this (be careful with SQL injection!)
set @Calc = concat('select ', '5*2+1', ' as result');
prepare stmt1 from @Calc;
execute stmt1;
deallocate prepare stmt1;
5 Comments
Dũng
I've just try this querry but it throw out all the string, not the result: prepare calc from 'select ?'; execute calc using @Calc_; DEALLOCATE PREPARE calc; then it returns: ((1*2)/3)*100/1000
Dũng
Here is my result: nw9.upanh.com/b5.s36.d1/…
Arsen
Cannot load your screenshot. Can you show the exact code you use and how you passing that code to the MySQL?
Dũng
set @Calc_ = '5*2+1'; prepare calc from 'select ? as Results'; execute calc using @Calc_; DEALLOCATE PREPARE calc;
Yes, technically you can do this, but it is a dangerous form of SQL injection, so it's probably a very bad idea.
There's a reason people do not use eval or anything like it to do simple math.
SELECTpart. It's not clear what your goal is here. Where is the5*2+1coming from?@stryou mean what? In a stored procedure?