Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight

Storing a variable in a file and processing within the script

by (Initiate)
on Jul 17, 2013 at 14:58 UTC ( #1044831=perlquestion: print w/replies, xml ) Need Help?? has asked for the wisdom of the Perl Monks concerning the following question:

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
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..

Replies are listed 'Best First'.
Re: Storing a variable in a file and processing within the script
by Corion (Pope) on Jul 17, 2013 at 15:13 UTC

    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;
Re: Storing a variable in a file and processing within the script
by marinersk (Curate) on Jul 17, 2013 at 15:20 UTC
    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(); }
      This worked, Thanks for that..

        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.

Re: Storing a variable in a file and processing within the script
by Preceptor (Deacon) on Jul 17, 2013 at 15:21 UTC

    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.

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

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

Re: Storing a variable in a file and processing within the script
by rjt (Deacon) on Jul 17, 2013 at 15:24 UTC

    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.

      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.

        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.

Re: Storing a variable in a file and processing within the script
by clueless newbie (Chaplain) on Jul 17, 2013 at 17:10 UTC

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1044831]
Approved by Corion
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (13)
As of 2017-02-27 16:34 GMT
Find Nodes?
    Voting Booth?
    Before electricity was invented, what was the Electric Eel called?

    Results (388 votes). Check out past polls.