Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

Using variables within a mysql query

by sulfericacid (Deacon)
on May 11, 2008 at 14:50 UTC ( #685955=perlquestion: print w/ replies, xml ) Need Help??
sulfericacid has asked for the wisdom of the Perl Monks concerning the following question:

SELECT id, name, location FROM book WHERE 1 ORDER BY $orderfield DESC
The above doesn't work when $orderfield is used, but when swapped with the actual field name "id" it works just fine. By not working, I mean it returns with "query returned empty".

After a test print, it shows that $orderfield eq "id" which is the exact name of the column.

Any ideas what might be hanging this up? And would it be possible to also change ASC/DESC to a variable as well?



"Age is nothing more than an inaccurate number bestowed upon us at birth as just another means for others to judge and classify us"

sulfericacid

Comment on Using variables within a mysql query
Download Code
Re: Using variables withing a mysql query
by wfsp (Abbot) on May 11, 2008 at 15:13 UTC
    Is the SQL quoted with single or double quotes?
      The statement is actually
      my $data = qq( mysql_here );


      "Age is nothing more than an inaccurate number bestowed upon us at birth as just another means for others to judge and classify us"

      sulfericacid

        This should be equivalent to double quotes. What do you get from a print $data; after your statement above?

        If you get exactly the query (SELECT id, name, location FROM book WHERE 1 ORDER BY id DESC) and it doesn't work as expected, perhaps you should post more of the surrounding code...

Re: Using variables withing a mysql query
by psini (Deacon) on May 11, 2008 at 16:31 UTC

    If the string is enclosed in double quotes the embedded variable should be replaced whit its value.

    Did you try a test print of the entire query before submitting it to the DBMS?

    BTW, I hope you are aware that generated queries are a potential security issue (risks from SQL injection) and should be used with care

Re: Using variables withing a mysql query
by Fletch (Chancellor) on May 11, 2008 at 16:46 UTC

    Print the string you're sending to the DB before hand and make sure it's what you think it's supposed to be. See also the documentation for the trace method in the DBI docs (a value of 2 usually should be sufficiently verbose to show what's being sent to the backend).

    (And also I think you mean $orderfield eq "id", not ==; but that's neither here nor there</nit> :)

    The cake is a lie.
    The cake is a lie.
    The cake is a lie.

Re: Using variables within a mysql query
by hesco (Deacon) on May 11, 2008 at 20:30 UTC
    Try something like this:

    my $table = 'book'; my $condition = '1'; my $sort_field = 'id'; my $sort_order = 'DESC"; my $sql = "SELECT id, name, location FROM $table WHERE $condition ORDE +R BY $sort_field $sort_order"; print $sql,"\n"; my $sth = $dbh->prepare($sql); $sth->execute() or warn "Unable to query $table" $dbh->errstr . "\n" . + $!; while (my $row = $sth->fetchrow_hashref()) { print $row->{'id'},"\t",$row->{'name'},"\t",$row->{'location'},"\n"; +; }
    Remember: single quotes give you a literal, double quotes interpolates included variables. Also, if this is untrusted user input you are manipulating (say the kind collected at a browser) you would be much safer to use bind variables, by replacing these two lines like so:

    my $sql = "SELECT id, name, location FROM $table WHERE ? ORDER BY ? ?" +; . . . $sth->execute($condition,$sort_field,$sort_order);
    Note: I've never tried to feed a $sort_order as a bind variable and have no idea if that would work. But a few tests ought to let you know if it will. I do know that the rest of this should work, although I have not tested this code, leaving that to you.

    -- Hugh

    if( $lal && $lol ) { $life++; }

      I really doubt that this could work. From DBI's POD:

      "With most drivers, placeholders can't be used for any element of a statement that would prevent the database server from validating the statement and creating a query execution plan for it"

      And if it works, it should be a literal substitution, so probably can't be used to avoid sql injection

      I, in my code, use plain old sprintf to build the query when I need to change table or column names and bind only the scalar values

      Obviously everything is included with sprintf must be validated if coming from outside the script

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (6)
As of 2014-10-24 08:49 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    For retirement, I am banking on:










    Results (131 votes), past polls