CODEWITHSUNDEEP
perlcgidbi
jon
jon

Reputation: 21

Perl cgi bind dynamic number of columns

I'm trying to make a simple select from a database, the thing is that I want the same script to be able to select any of the tables in it. I have gotten everything solved up until the point when I need to bind the columns to variables, since they must be generated dynamically I just don't know how to do it.

here's the code:

 if($op eq "SELECT"){
    if ($whr){
    $query1 = "SELECT $colsf FROM $tab WHERE $whr";
    }else{
    $query1 = "SELECT $colsf FROM $tab";
    }
    $seth = $dbh->prepare($query1);
    $seth->execute();
    foreach $cajas(@columnas){
    $seth->bind_col(*$dynamically_generated_var*);
    }
    print $q->br();
    print $q->br();
    print $q->br();

The variable @columans contains the name of the selected columns (which varies a lot), and I need a variable assigned for each of the columns on the $seth->bind_col().

How can I acheive this?

Upvotes: 0

Views: 416

Answers (1)

simbabque
simbabque

Reputation: 54323

Using bind_col will not gain you anything here. As you have already figured out, that's used to bind a fixed number of results to a set of variables. But you do not have a fixed set.

Thinking in terms of oh, I can just create them dynamically is a very common mistake. It will get you into all kinds of trouble later. Perl has a data structure specifically for this use case: the hash.

DBI has a bunch of functions built in for retrieving data after execute. One of those is fetchrow_hashref. It will return the results as a hash reference, with one key per column, one row at a time.

while (my $res = $sth->fetchrow_hashref) {
  p $res; # p is from Data::Printer
}

Let's assume the result looks like this:

$res = {
  id => 1,
  color => 'red',
}

You can access the color by saying $res->{color}. The perldocs on perlref and perlreftut have a lot of info about this.

Note that the best practice for naming statement handle variables is $sth.

In your case, you have a dynamic number of columns. Those have to be joined to be in the format of col1, col2, col3. I guess you have already done that in $colsf. The table is pretty obvious in $tab, so we only have the $whr left.

This part is tricky. It's important to always sanitize your input, especially in a CGI environment. With DBI this is best done by using placeholders. They will take care of all the escaping for you, and they are easy to use.

my $sth = $dbi->prepare('select cars from garage where color=?');
$sth->execute($color);

Now we don't need to care if the color is red, blue or ' and 1; --, which might have broken stuff. If it's all very dynamic, use $dbi->quote instead.

Let's put this together in your code.

use strict;
use warnings;
use DBI;

# ...

# the columns
my $colsf = join ',', @some_list_of_column_names; # also check those!

# the table name
my $table = $q->param('table');
die 'invalid table name' if $table =~ /[^a-zA-Z0-9_]/; # input checking

# where
# I'm skipping this part as I don't know where it is comming from

if ($op eq 'SELECT') {
  my $sql = 'SELECT $colsf FROM $table';
  $sql .= ' WHERE $whr' if $whr;

  my $sth = $dbh->prepare($sql) or die $dbi->errstr;
  $sth->execute;

  my @headings = $sth->{NAME}; # see https://metacpan.org/pod/DBI#NAME1
  while (my $res = $sth->fetchrow_hashref) {
    # do stuff here
  }
}

Upvotes: 2

Related Questions