Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
PerlMonks  

"ORDER BY" clause silently fails

by mikecorb (Initiate)
on Sep 12, 2015 at 06:44 UTC ( #1141741=note: print w/replies, xml ) Need Help??


in reply to HTML::Template Tutorial

Thank you for the tutorial. I'm new to PERL, more used to PHP, and I'm tailoring your code and making a Postgres database of songs that I perform and write to help organise my practice. But for the life of me I can't get the prepared statement to work with my Postgres query. The "ORDER BY" clause silently fails:
my $data = $DBH->selectall_arrayref(" select @{[join(',', @COLS)]} from songs order by ? ", undef, ($sort));
I've tried all sorts of things, but I can't get the sort order to work, so I resorted to the following which would be open to SQL injection:
my $data = $DBH->selectall_arrayref(" select @{[join(',', @COLS)]} from song order by $sort") or die "DBI::errstr";
Here is some DBI->Trace output, but I can't debug the problem...
... STORE DBI::db=HASH(0x11c9078) 'dbi_connect_closure' => CODE(0x11c9 +7f8) <- STORE= ( 1 ) [1 items] at /usr/local/lib/perl/5.18.2/DBI.pm lin +e 754 via at /home/XXXX/.../songs_perlmonks_org/songs.cgi line 11 -> selectall_arrayref for DBD::Pg::db (DBI::db=HASH(0x11c8f10)~0x1 +1c9078 ' select id,title,key,recorded,songwriter,vocals_needed,length from song order by ? ' HASH(0x110dd70) 'songwriter') thr#bac010 1 -> prepare for DBD::Pg::db (DBI::db=HASH(0x11c9078)~INNER ' select id,title,key,recorded,songwriter,vocals_needed,length from song order by ? ' HASH(0x110dd70)) thr#bac010 Begin dbd_st_prepare (statement: select id,title,key,recorded,songwriter,vocals_needed,length from song order by ? ) Begin pg_st_split_statement End pg_st_split_statement Immediate prepare decision: dml=1 direct=0 server_prepare=1 prepare_no +w=0 PGLIBVERSION=90309 End dbd_st_prepare 1 <- prepare= ( DBI::st=HASH(0x110df38) ) [1 items] at /home/XXXX/.. +./songs_perlmonks_org/songs.cgi line 23 Begin dbd_bind_ph (ph_name: 1) End dbd_bind_ph Begin dbd_st_execute PQexec* decision: dml=1 direct=0 server_prepare=1 numbound=0 numphs=1 +default=0 current=0 Begin _sqlstate End _sqlstate (imp_dbh->sqlstate: 00000) End _sqlstate (status: 2) End dbd_st_execute (rows: 32) Begin dbd_st_fetch End dbd_st_fetch ...

Replies are listed 'Best First'.
Re: "ORDER BY" clause silently fails
by Corion (Pope) on Sep 12, 2015 at 06:58 UTC

    A placeholder can only be a value, not the name of a column, so you will have to dynamically create the SQL for your case.

    To be safe from SQL injection, I recommend setting up the allowed values in a hash and validating against that:

    my %sortby = ( title => 'title', recorded => 'recorded', songwriter => 'songwriter', length => 'length', title_desc => 'title desc', ); my $column = $sortby{ $user_column } || 'title'; my $sql = <<SQL; select @{[join(',', @COLS)]} from songs order by $column SQL

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1141741]
help
Chatterbox?
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (3)
As of 2017-12-18 22:34 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    What programming language do you hate the most?




















    Results (500 votes). Check out past polls.

    Notices?