Storing a variable in a file and processing within the script

[[e-mail]]

Hi Guys, I have a plain text file with a sql statement and want to put a variable name within it, which should be read by my perl script.

So for example: There is a a file called input.txt with the SQL content as below. Notice the $table_name intended to be processed within the script.

input.txt Text File content :
 select * from all_tables where table_name = '$table_name'
Perl Script readSQL.pl:

my $sql;
my $table_name = "EMPLOYEE"; # setting a table name. 
my $Inputfile = 'input.txt';
    open my $filecontent, $inputfile or die "Could not open $inputfile
+: $!";

        while( my $readline = <$filecontent>)  
        {   
            print "$line \n";    
            $sql = $readline;
            executeSQL();

        }
    close $$filecontent;
}

If I execute the script, it reads the file but treats the $table_name as a string. How can I tell perl to treat it as a variable and assign the $table_name value declared at the start of the script to this value?

Thanks for any inputs on this..

[Corion]

As an aside, you may want to look at Querylet, which implements the "run SQL, create Excel" workflow.

If you still want to roll your own, have a look at various templating engines, Interpolation or this very, very basic regex:

my %variables= (
    table_name => 'EMPLOYEE', # setting a table name
);

...

$sql=~ s!\$(\w+)!$variables{ $1 } || "\$$1" !ge;
print $sql;

[marinersk]

Summary of Problem: You are not converting the text in the script.

Just add one line to your processing loop (this is untested but should get the idea across):

{   
    print "$line \n";    
    $sql = $readline;
    $sql =~ s/\$table\_name/$table_name/g;
    executeSQL();
}

[[e-mail]]

This worked, Thanks for that..

[Preceptor]

Works nicely, but bear in mind that that is essentially a placeholder - that 's/...' line is replacing the text '$table_name' with the value held in the variable '$table_name'. I'd tend to suggest that's a source of confusion later on.

[Preceptor]

What you're trying to do is not a good idea. The contents read in from '$inputfile' is text. Using that text as a reference to a variable is a way to ask for all sorts of pain.

My suggestion would be to put a plain text 'placeholder' and then do a search and replace within your script.

select * from all_tables where table_name = 'PLACEHOLDER'

$readline =~ s/PLACEHOLDER/$table_name/;

Because trust me - using variable names as 'plain text' is bad news for all sorts of reasons. If you're really set on going that way, you will want to look at 'references'. See: perlreftut.

[[e-mail]]

This is a good idea as well. Will consider this. Thank you

[Anonymous Monk]

I did that once, and just made the placeholders be \$nameofthing\$ to make it delimited on both sides and more like perl code.

[rjt]

Where is this text file generated? If its contents are affected in any way by user input, you open yourself up to SQL injection attacks. Please be careful.

That issue addressed, I don't know where this executeSQL() sub comes from, but if you were using DBI, my advice would be to use a placeholder in the query:

$sth = $dbh->prepare('SELECT * 
                      FROM   all_tables 
                      WHERE  table_name = ?');

$sth->execute($table_name);

while (@row = $sth->fetchrow_array) {
    print "@row\n";
}

If you can't use DBI, then a simple substitution will work in a pinch:

my $sql = <$filecontent>;

$sql =~ s/\$table_name/$table_name/e;

In this case, however, you now have to also ensure that $table_name is also safe from SQL injection, if it can be influenced by user input.

[Corion]

Note that a placeholder in SQL is usually only allowed where the query engine can still create a full query plan and compile+check the query with it. This usually precludes the use of placeholders for column, table or schema names, as the validity of the query cannot be checked when the value for the placeholder is only known at a later stage.

[rjt]

It's fine in this case, since the placeholder is for a column value. Even though the $table_name is the thing in question, he apparently has a table full of table names to query against. Good reminder, though.

[clueless newbie]

String::Interpolate?