This assumes that the strings returned from your database actually do use single quotes in a consistent way around the citations of variables. (The snippet below demonstrates that references to subroutines do not work the way you'd want.)

#!/usr/bin/perl

use strict;
use warnings;

my %in;
my %out;
my $testsub;
my $constant = "fixed";

$in{cid} = 12345;
$out{this}{that}[0] = 678;
$testsub = sub { return "this is foobar" };

print "normally, \$testsub returns: " . &$testsub . "\n\n";
while (<DATA>) {
    s/(?<=')(\$[^']+)/(ref($1) eq 'CODE') ? &{$1} : $1/ee;
    print;
}

__DATA__
select field from table where id!='$in{cid}'
update table set field = null where id = '$out{this}{that}[0]'
delete from table where value = '$constant'
select something from somewhere where status='$testsub'
[download]

normally, $testsub returns: this is foobar

select field from table where id!='12345'
update table set field = null where id = '678'
delete from table where value = 'fixed'
select something from somewhere where status='CODE(0x7ff80903f0e8)'
[download]

    s/(?<=')(\$[^']+)/$1/ee;
[download]

Works like a Charm!

Tried that simpler solution and I think that is it!

Ofc I have to test that first solution, but I have to do it tomorrow, cause it is over 6 in a morning here and I should also sleep.

Thank you very much for your effort.

templates are templates, like String::Interpolate/ String::Interpolate::RE , String::Defer, Interpolation, Template,Text::Xslate...

as long as you don't forget to DBI->quote because of placeholders and bobby-tables.com: A guide to preventing SQL injection in Perl

That sql-query is stored in database, so I fetch it in order to use it to generate options to html-select.

It's much easier when you can manipulate sql-queries in 'admin-panel' and you don't have to wrote those to program-code.

Actually main idea is to make dynamic form-maker. So those queries have to be in databases.
It's like using db to query db.

One and maybe even easiest way is to implement limited variable-support with regexp.
Just wondering if this works:~s/($\w+)/$1/g
Maybe not, but there are ways...
At-least if one needs only limited number of variables.
It's ugly hack, but it'll work.

Just trying to find elegant solution which would deal all variables, like normal interpolation.

I'm still trying to understand what you really want to do, but whatever it is, I'm sure there's a better way than having script-internal variable expressions stored in string values in a database, and having the script 'eval' those strings.

I think a "dynamic form-maker" is a sensible thing to build, but the typical approach for that would be a tool that allows the author/admin person to create a page layout with some inventory of elements to present the end-users with suitable instructions, input methods, and displays of results.

Throughout the authoring process, maintain a set of labels (simple strings) for identifying the specific inputs-from and outputs-to the end-user. The labels can be tracked in the database in whatever way makes sense for the application; the script can use the labels as hash keys for storing whatever the script needs for mediating between the user and the database - subroutine refs, data structures, query strings, etc.

I agree, that this approach is in a thin ice.

It is doable in more traditional ways, but this was partly a test and also I think that this gives greatest freedom, couse you can use db directly.

I have 'layout-maker' in which user manipulates form-objects, but but this is just for populating options to select, cause in essence it is just name and value.

When I iterated in my mind, it always simplified to result of sql-query.

I know very well dangers of letting ppl to make queries on their own.
This will be, in the end admin-tool, in their intranet and even there is a way to manipulate those values and queries it needs a bit of knowledge
to make working sql-query (=select, insert, update or delete), when you don't know db-schema etc.

I don't think that anyone else than I'll touch to those sql-queries.
Only one, that I could imagine is worker who want to sabotage system and firm before leaving.

my $dbh = DBI->connect( $whatever… )
...
my $sth = $dbh->prepare( "select field from table where id != ?" );
$sth->execute( '$in{cid}' );  # an 8-character string is the placehold
+er value
...
[download]

my %in;
$in{cid} = "something";

my $dbh = DBI->connect( $whatever… )
...
my $sth = $dbh->prepare( "select field from table where id != ?" );
$sth->execute( $in{cid} );  # the hash element value is the placeholde
+r value
...
[download]

Sorry - I'll try to respond again, now that I understand the question.

No worries. I just hoping to find something like this:
$sql=${"$per->{value}"} or something...

See all the problems dynamic SQL causes? :) To remove issues and make it more secure, use a prepared query with placeholders, and supply the value upon execution. Further, if the query will be executed multiple times, a prepared query can run a bit faster, as the optimizer is not usually required after the first run.

Anyway, instead of storing queries in a table, why not just make them views (or stored procedures)? That's what they're for. And, it makes running them a lot easier. If worse comes to worse, you can store the name of the view (or stored procedure) in a table for easy finding. Or, perhaps, the name of the script's internal procedure that executes it.

Side comments on the query itself: The query uses an outer query just to get ids and pass them to the inner query that also has id. Why use two tables? And, order by id desc limit 1 to fetch name can be done via an analytical function. How does this look?:

select distinct
    foo_id        key,
    last_value(value)
     over
     (
      partition by
        foo_id
      order by
        id
     )            value
from
    foo_data
where
    foo_id        <> ?
  and    aktivity    is true
  and    key        = 'name';
[download]

Update: Query missed the order by id when fetching name. Here it is again with an analytical function.

I'm quite aware how dangerous is to let pp make own queries.
This is only for admins of that software.

Why two tables? There are one table which contains only id of that item and its' activity.
Other table contains all fields in key-value-way, so one can add fields as many as one wants without altering db-schema.
That order and limit gets only latest value, cause there is no alter or delete, just inserts, so it builds history of changes.

The other table is simply not required. It contains no information that the inner table does not have. The only benefit it might have is id is already unique, but at the cost of loading another table and doing a join, it probably hurts more than it helps.

Queries get more and more complicated over time as they are used for more and more things. This adds complexity which makes understanding the queries take longer and hampers their revision. The simpler the query, the easier on the programmer and the database's optimizer.

Just do it like this:   (a sketch ... not code-writing services)

First, write your query to use a placeholder:
select foo.id as key,(select value from foo_data where aktivity is true
and foo.id=foo_id and key='name'
order by id desc limit 1) as value
from foo where id!=?

Now, when you’t called $dbh->prepare($sql); with this SQL string to get your statement-handle, $sth, call $dbh->execute('xyzzy';, which, as you can see, provides a @bind_values array as shown in the perldoc.   The first value in the array (in this case, 'xyzzy', will be provided to the database engine so that it will use that value in place of the first placeholder '?' and so-on.   (It doesn’t do a textual substitution ... the SQL string is compiled only once, but it has “an input-variable,” so to speak.   The question-mark is not quoted:   it is not a “literal string.”

You should n-e-v-e-r construct an SQL string yourself, using any input provided by ... well ... anyone.   This is what placeholders are for.   It neatly avoids the Bobby Tables problem.

Notice also that, having prepared the statement-handle, you can execute() it as many times as you want to, providing different bind-parameters each time.   This is efficient, because the SQL engine builds its execution-plan for the query only once, then re-executes it multiple times.

I know placeholders and those hold their place quite well..
Unfortunately I haven't seen any real performance or other improves with those. (Only in repetitive task.)
Those are good, if you want in some strange reason to put binary-stuff to db or do same insert many times etc.

Usually quote is enough with a bit of sanity-check like ~/^\d+/.
Ofc one have to be aware of sql-injection and not only depend audition where someone else is trying to hack into system.

Actually I made once geocaching-mystery, which includes sql-injection and it was quite hard, cause I learnt to avoid those.

This approach, which I chosen is a test, in which I try to different direction.
Ofc I could use pre-made queries with placeholder and all that jazz, but clients needs are changing and I can't foresee any of those different variations they might need.
I'll be ending to do lots of different variations of queries beforehand and when need arices I have to do the needed one, cause I cannot anticipate their needs exactly.

I'll be quite sure, that only I'll do those queries.
It'll be quite like changing code afterwards, but quicker and easier.

And yes, access to those queries is quite limited.
Ofc it's hackable, cause everything is, but then whole system is compromised, like source-code and database.