Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical
 
PerlMonks  

Variable interpolation in a file to be read in

by tapolyaip (Initiate)
on Oct 06, 2011 at 15:43 UTC ( [id://930008]=perlquestion: print w/replies, xml ) Need Help??

tapolyaip has asked for the wisdom of the Perl Monks concerning the following question:

Hello: I have, I believe, a variable interpolation question. I have a perl program that reads in a file that contains sql statments. I pull each SQL and push it into a hash, where the hash is a transaction_number and the associated SQL. The sql statments however hold Perl variables such as:
... WHERE asof_date = '$test_date' ...
Of course, $test_date is defined in the program, and my hope is that once the sqls are read in, the variables in the sqls take the approproate value that is assigned to them in the program. I can make the program work fine if I replace the actual string of variable with the variable like this in the loop while I am reading in the file:
... s/\$test_date/$test_date/g; ...
But that is not really what I wanted to do. I was hoping that the variable in the sql file is interpolated and takes on the value what is defined in the program. Here is a skelatal of the code:
#===============================================# $ss_source_file = "C:/code/mla/tmp.sql"; $test_date = "30-APR-2011"; $schema = "DEV"; ##################### %trancd_sql = (); # each SQL in a hash with a # transaction number and associated SQL open(SS_SOURCE, "< $ss_source_file ") || die "Can't open SQL source fi +le! $!\n"; while (<SS_SOURCE>) { if (/^;/) { # the end of each SQL $end_sql = 1; $begin_sql = 0; $trancd_sql{$tran_code} = $sql; ## push each tran_code/SQL into + hash $sql = ''; $tran_code = ''; } ## beginning of an sql if (/^\s*-{2,}\s*tran_code\s*\d{4,}/) { ($tran_code) = $_ =~ /^\s*-{2,}\s*tran_code\s*(\d{4})/; $begin_sql = 1; $end_sql = 0; } if (($begin_sql == 1) && ($end_sql != 1)) { #s/\$test_date/$test_date/g; # works if uncommented #s/\$schema/$schema/g; # works if uncommented $sql .= $_; } } close(SS_SOURCE); $trcd = 1; while ($trcd > 0) { print "Enter Tran code, or '0' to exit: "; $trcd = <STDIN>; chomp($trcd); print $trancd_sql{$trcd}; } #====================================================#
and here is the data file with he sqls in it.
#=====================================================# -- tran_code 1752 SELECT * FROM $schema.TRANSACTOPMS WHERE TRANSACTION_CD IN ('146','147') AND AMT < 0 AND ASOF_DATE = '$test_date' ; -- tran_code 1753 SELECT * FROM $schema.TRAN WHERE TRANSACTION_CD IN ('196','197') AND ASOF_DATE = '$test_date' AND RECORD_VERSION_NBR = '0' ; -- tran_code 1758 SELECT COUNT(SS_NO), SUM(ABS(SS_PRINCIPAL)) FROM $schema.SS_MLA_TRAN WHERE INVESTOR_ID = '195' AND MAN_CD = '4' AND ASOF_DATE = '$test_date' AND RECORD_VERSION_NBR = '0' and GROUP_ID = 'PCC' ; -- tran_code 1765 ... and so on, you get the idea ; #==================================================#
What I wanted to see as output is:
SELECT * FROM DEV.TRANSACTOPMS WHERE TRANSACTION_CD IN ('146','147') AND AMT < 0 AND ASOF_DATE = '30-APR-2011'
instead of the:
SELECT * FROM $schema.TRANSACTOPMS WHERE TRANSACTION_CD IN ('146','147') AND AMT < 0 AND ASOF_DATE = '$test_date'
As I mentioned before, I can make this work by replacing the actual string (which is a variable name in the sql), but my objective here is getting a more elegant solution and for my own personal learning as well. Any ideas of what I am missing? Greatly appreciate your input!

Replies are listed 'Best First'.
Re: Variable interpolation in a file to be read in
by davido (Cardinal) on Oct 06, 2011 at 17:09 UTC

    It's incredible how often this comes up in one form or another. Actually it's always just a variation on the same form; someone is passing malformed SQL to their database, with the solution being either use placeholders (bind values) or proper SQL quoting.

    Two weeks ago a "friend" (the kind whom I hear from when he's got a programming problem, but not otherwise) called me. The call goes something like this:

    "Dave, I have a PHP web application where people are able to select songs to sample, but whenever the song they select has a quote or apostrophe in the name, the application crashes."

    Now I hate working with PHP, and never even bother looking for PHP jobs to do, but I'm too soft with old friends.

    "It sounds like you've got a problem with how you're passing SQL to your database. Where did you get this application? You should be aware that if you're accepting song names via a web page and they are being incorporated into your SQL, you're open to SQL injection attacks. Even if you are validating your input with JavaScript, or some other client-side means, you're still open to attack since a malicious user could just form his own HTTP request that bypasses your client-side safeguards."

    So next thing I know I've got a 400 line PHP program in my inbox. I found 65 places within the spaghetti code written by some freelancer in Ukraine where he was passing unescaped SQL to the database that contained user input. The quick solution was to quote it properly (PHP oddly has a different quoting function for every database flavor). That's the free solution he got from me. It should have been rewritten with bind values, but that would have taken longer, and I didn't hear any offer to employ me. Besides, I don't really want to invest more time in brushing up on PHP.

    I don't know what more can be done to save people from themselves. Discussion on proper quoting and the use of some form of placeholders can easily be found in Perl's DBI documentation, and for those PHP kiddies, PHP's documentation of dealing with databases also discusses it. People are learning Perl and PHP somewhere. I wonder what source they're using that teaches them database access without discussing this important issue.


    Dave

      Dave, I appreciate your concern on the code quality. Perhaps, I haven't made it clear that the code was a skeletal program, in other words a quick "look and see" about the variables that I need to work with. The program was quickly written up so I don't need to keep explaining what I wanted to do and is in no form part of the "real" program

      Another point I'd like to make is that the file containing sqls could easily be just a lot of text of some kind where you have to have some variables placed. So in light of the question posted, along with the sample code, there is no unsanitized sqls passed to the dabase for execution. There is not even a single database connecion in the sample code, in fact DBI is not even called here.

      So if I can paraphrase the original question: I read in a file that contains text and some perl variables. The text is split into records into a hash. How can display the hash values so they interpolate the variables into the text?

        "...read in a file that contains text and some perl variables."
        Just don't do that. :-) Use a template system e.g. HTML::Template*. That is, imo, the best way to go for that part of your problem.

        But it still leaves you with the next problem you'll have and which experienced monks are warning you about: quoting SQL properly. For that, placeholders are the way to go.

        * Don't worry about the HTML part of the name, it is, again imo, a handy templating system. Of course there are many others. It is just one that I am very familiar with.

Re: Variable interpolation in a file to be read in
by Anonymous Monk on Oct 06, 2011 at 15:58 UTC
      And if not placeholders and bind values, then at least quote

        quote() is evil. Well, not exactly, but using quote() manually causes more trouble than it is worth. Always use placeholders and you never again have to think about quoting values for SQL statements. Plus, DBI, DBD or database can cache prepared statements for better application performance.

        Alexander

        --
        Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)
      Thanks for the input. In the sample code I posted I am not calling any database handler - yet. I just want to display the sql for now that I am pulling from a hash. So where would the placeholder go in that code?
        E.g., instead of:
        WHERE asof_date = '$test_date'
        do:
        WHERE asof_date = ?
        and when you execute the sql statement:
        $sth->execute($test_date);
Re: Variable interpolation in a file to be read in
by zwon (Abbot) on Oct 06, 2011 at 15:58 UTC

    What if some of these variables have value like this:

    Robert');drop table students; --
    http://xkcd.com/327/

    Seriously, your idea doesn't look especially elegant to me. You should somehow control which variables can be used in your file to avoid occasional collisions. Also you should escape values if they contain special characters.

Re: Variable interpolation in a file to be read in
by Anonymous Monk on Oct 06, 2011 at 22:38 UTC

    You can always use eval if the text does not contains any other characters that double quoted strings handle specially (e.g. ['%@])

    if (($begin_sql == 1) && ($end_sql != 1)) { my $interpolated = eval(qq/$_/); $sql .= $interpolated; }

      You can always use eval

      You can always avoid eval :)

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://930008]
Approved by zwon
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others romping around the Monastery: (2)
As of 2024-03-19 05:57 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found