Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid
 
PerlMonks  

Re^3: CGI Action call

by Corion (Patriarch)
on Mar 12, 2018 at 18:57 UTC ( [id://1210748]=note: print w/replies, xml ) Need Help??


in reply to Re^2: CGI Action call
in thread CGI Action call

You are reading untrusted user input and use that to create an SQL statement. This is highly unsafe:

...
    my $searchterm = $query->param('searchterm');
...
    my $stmt = "SELECT * FROM users WHERE $searchfield = $searchterm";
...
my $sth = $dbh->prepare ($stmt) or die "Error Preparing:\n" . $stmt . 
+"\nDBI returned: \n", $dbh->errstr;
    
    $sth->execute () or die "Unable to execute query: " . $sth->errstr
+;

[download]

Please read DBI and learn about placeholders. You should never interpolate user input into SQL (or likely, any other kind of textual data).

You should rewrite your SQL statement and then use placeholders:

    my $stmt = "SELECT * FROM users WHERE $searchfield = ?";
...
    my $sth = $dbh->prepare ($stmt) or die "Error Preparing:\n" . $stm
+t . "\nDBI returned: \n", $dbh->errstr;
    
    $sth->execute($searchterm) or die "Unable to execute query: " . $s
+th->errstr;

[download]

Replies are listed 'Best First'.
Re^4: CGI Action call
by tultalk (Monk) on Mar 12, 2018 at 20:14 UTC

    Hi

    The "user"in this case is the administrator and logged on as such.

    That aside your point is well taken.

    I don't dee that placeholders would be needed below but can you use multiple place holders as in the below code?

    
if ($kind == 2){
  #previous record
  $stmt = "SELECT * FROM users WHERE user_id < (SELECT MAX(user_id) FR
+OM users WHERE user_id < $rowpointer) ORDER BY user_id DESC LIMIT 1";
   
  }                                            
  
if ($kind == 3){
  #next record     
   $stmt = "SELECT * FROM users WHERE user_id > (SELECT MIN(user_id) F
+ROM users WHERE user_id > $rowpointer) ORDER BY user_id ASC LIMIT 1";
   
  }

    [download]

    I know there is better way through MySQL but not there yet

[reply]
[d/l]

      Yes, as is documented in DBI.

      Also, your SQL seems faulty to me as the "previous record" seems to skip users:

      create table users ( user_id integer not null primary key, name varcha
+r(16) );

insert into users values (1,'Fred');
insert into users values (2,'Barney');
insert into users values (3,'Wilma');
insert into users values (4,'Betty');
insert into users values (5,'Pebbles');


SELECT *
FROM users
WHERE user_id < (SELECT MAX(user_id)
                 FROM users WHERE user_id < 4) 
ORDER BY user_id DESC LIMIT 1

      [download]

      (SQL fiddle)

      This suggests that the "previous" entry for "Betty" (user id 4) is "Barney" (user id 2), and not user id 3 as I would expect.

      The same, but inverse holds for the other SQL.

[reply]
[d/l]

        I have a form with a navigator control I made. Works fine except when I want to scroll with a different order in place.

        The 4 SQL statements work fine in PHPAdmin. I issue an "alter table users order by lastname asc and the table is so ordered. Shows as such in browse. I execute the various SQL statements below and the returned records are as expected. next, next, previous, beginning and end. The ORDER remains in place.

        On the form I search for last name and set the alter table based on the search selected. This all works as intended per my logging. When I try to scroll the records with the navigator it is returning records based on the user_id order. I tried correcting this by reasserting the alter table again with each scroll but to no avail.

        Don't understand why the process works in PHP MyAdmin but not with my code.

        #====================================
#Navigation navigate_Records Buttons 1-4
# 1 Beginning
# 2 Previous 
# 3 Next
# 4 End    

sub navigate_Records {

    my $kind = $query->param('kind');
    my $rowpointer = $query->param('searchterm'); 
    my ($sth, $stmt);
    my @row;
    warn("Entered update_tables.cgi Open Database return First Record 
+with action: = '$action'");   
    warn("Search Parameter = '$kind'");
    warn("rowpointer = '$rowpointer'");    

    my $minrowpointer;
    my $maxrowpointer;
    my $searchfield;

    warn("sortindex =  '$sortindex'"); #Set by selection in search_Rec
+ords
    if (!$sortindex) {
      $sortindex = 0;
    }
    if ($sortindex == 0){
        $searchfield = 'user_id';
        $minrowpointer = 1;
        $maxrowpointer = 14999;
       } 
    elsif ($sortindex == 1) {
        $searchfield = 'lastname';    
        $minrowpointer = 'a%';
        $maxrowpointer = 'z%';
       }
    elsif ($sortindex == 2) {
        $searchfield = 'business';    
        $minrowpointer = 'a%';
        $maxrowpointer = 'z%';
       }        
    
    

    #kind 0 & 3 ok with direct hard coded SQL 
    
    if ($kind == 0){  
        #beginning record Tested Good

        $stmt= "SELECT * FROM ( SELECT * FROM users WHERE $searchfield
+ <= ? AND $searchfield >= ? ORDER BY $searchfield ASC LIMIT 1 ) sub O
+RDER BY $searchfield ASC"; 

        # example user_id 
#        $stmt = "SELECT * FROM ( SELECT * FROM users WHERE user_id <=
+ 14999 AND user_id >= 1 ORDER BY user_id ASC LIMIT 1 ) sub ORDER BY u
+ser_id ASC"; #Works fine hard coded
        
        #example lastname SELECT * FROM ( SELECT * FROM users WHERE la
+stname <='z%' AND lastname >= 'a%'ORDER BY lastname ASC LIMIT 1 ) sub
+ ORDER BY lastname ASC #Works fine hard coded

        #example business SELECT * FROM ( SELECT * FROM users WHERE bu
+siness <='z%' AND business >= 'a%'ORDER BY business ASC LIMIT 1 ) sub
+ ORDER BY business ASC 
    }
    
    if ($kind == 1){
        #previous record Tested Good
        $stmt = "SELECT * FROM users WHERE $searchfield <= (SELECT MAX
+($searchfield) FROM users WHERE $searchfield < ?) ORDER BY $searchfie
+ld DESC LIMIT 1";
    }                                            
  
    if ($kind == 2){
        #next record Tested Good
        $stmt = "SELECT * FROM users WHERE $searchfield >= (SELECT MIN
+($searchfield) FROM users WHERE $searchfield > ?) ORDER BY $searchfie
+ld ASC LIMIT 1";
   
    } 

    if ($kind == 3){
        #Last record Tested Good
        
        $stmt = "SELECT * FROM ( SELECT * FROM users WHERE $searchfiel
+d <= ? AND $searchfield >= ? ORDER BY $searchfield DESC LIMIT 1 ) sub
+ ORDER BY $searchfield ASC"; 
        
        #example user_id 
#        $stmt = "SELECT * FROM ( SELECT * FROM users WHERE user_id <=
+14999 AND user_id >= 1 ORDER BY user_id DESC LIMIT 1 ) sub ORDER BY u
+ser_id ASC"; #Works fine hard coded
                
        #example lastname SELECT * FROM ( SELECT * FROM users WHERE la
+stname >='z%' AND lastname >= 'a%'ORDER BY user_id DESC LIMIT 1 ) sub
+ ORDER BY lastname ASC #Works fine hard coded

        #example business SELECT * FROM ( SELECT * FROM users WHERE bu
+siness >='z%' AND business >= 'a%'ORDER BY business DESC LIMIT 1 ) su
+b ORDER BY business ASC 
    }

    warn("statement =  '$stmt'");
    
    $sth = $dbh->prepare ($stmt) or die "Error Preparing:\n" . $stmt .
+ "\nDBI returned: \n", $dbh->errstr;

#    warn("'$maxrowpointer' '$minrowpointer' '$searchfield' '$rowpoint
+er'");

       if (($kind == 0) || ($kind == 3)){
       warn("kind = '$kind' maxrowpointer = '$maxrowpointer'  minrowpo
+inter = '$minrowpointer'"); 
       $sth->execute ($maxrowpointer,$minrowpointer) or die "Unable to
+ execute query: " . $sth->errstr;
     } 
    elsif (($kind == 1) || ($kind == 2)){
       warn("kind = '$kind' rowpointer = '$rowpointer'"); 
       $sth->execute($rowpointer) or die "Unable to execute query: " .
+ $sth->errstr;    
    }
     
    my $navResult = $sth->fetchrow_hashref();
    
    warn("navResult = '$navResult'");
    
    my $count = $sth->rows;

    warn("count = '$count'");
if ($count == 0) {
      my %navFail = ( Count => $count);
      my $json = JSON->new; 
      $json->canonical(1);
      $json = encode_json(\%navFail);
        print "Content-Type: application/json\n\n";
      print $json;
      warn("Finished print 0 count $json");    
      exit(1);     }
    else {
      warn("count = '$count'");
      my $json = JSON->new; 
      $json->canonical(1);
      $json = encode_json($navResult);
      print "Content-Type: application/json\n\n";      
      print $json;
      warn("Finished print $json");    
      exit(0);
    }

}

        [download]

        More code

        #Search Records & Return Found 3 buttons
# 0 Member ID
# 1 Last Name 
# 2 Business Name 
#elsif ($action eq "search_Records")

 sub search_Records {
    warn("Entered search_Records");
    my $kind = $query->param('kind');
     my $searchterm = $query->param('searchterm');
    my $result;
    warn("searchterm = '$searchterm'");
    my $searchfield = "";

    if ($kind == 0) {
      $searchfield = 'user_id';
    } 
    if ($kind == 1) {
         $searchfield = 'lastname';    
    }
    if ($kind == 2) {
      $searchfield = 'business';    
    }

    $sortindex = $kind;
    
    restoreOrder($kind);

    #Sort Index For The Three Kinds Of ORDER Supposed to be global.
    
    warn("sortindex =  '$sortindex'");
    
    warn("searchfield =  '$searchfield'");
    
    $stmt = "SELECT * FROM users WHERE $searchfield = ? ORDER BY ? ASC
+";
#    my $stmt = "SELECT * FROM users WHERE user_id = 15 ORDER BY user_
+id ASC";
    
    warn("statement =  '$stmt'");    
    
    $sth = $dbh->prepare ($stmt) or die "Error Preparing:\n" . $stmt .
+ "\nDBI returned: \n", $dbh->errstr;
    
    $sth->execute($searchterm, $searchfield) or die "Unable to execute
+ query: " . $sth->errstr;    
#    $sth->execute() or die "Unable to execute query: " . $sth->errstr
+;    

    my $searchResult = $sth->fetchrow_hashref();

    my $count = $sth->rows;

    warn("count = '$count'");
    if ($count == 0) {
      warn("Failed Search: '$searchfield' equal to '$searchterm' "); 
      my %searchFail = ( SearchError => $kind);
      my $json = JSON->new; 
      $json->canonical(1);
      $json = encode_json(\%searchFail);
        print "Content-Type: application/json\n\n";
      print $json;
      warn("Finished print 0 count $json");    
      exit(1);
     }  #{"SearchError":0} Good
    else {
      warn("count = '$count'");
      my $json = JSON->new; 
      $json->canonical(1);
      $json = encode_json($searchResult);
      print "Content-Type: application/json\n\n";
      print $json;
      warn("Finished print $json");    
      exit(0);
    }
}

        [download]

        And yet more code

        
#==================================
#Set up "order by clause" depending on searchfield
sub restoreOrder{ 
    my $searchIndex = shift;  
   
    if ($searchIndex == 0)  {
       $searchfield = "user_id"; 

     }
    elsif ($searchIndex == 1)  {
       $searchfield = "lastname";

     }
    elsif ($searchIndex == 1)  {
       $searchfield =  "business";
    }

   warn("in restoreOrder index = '$searchIndex'");
    
    my $stmt = "alter table users order by $searchfield asc";
    
    my $sth = $dbh->prepare ($stmt) or die "Error Preparing:\n" . $stm
+t . "\nDBI returned: \n", $dbh->errstr;    
    
    DBI->trace(3,$dbitracelog); 
    
    $sth->execute() or die "Unable to execute query: " . $sth->errstr;
+    

}

        [download]
[reply]
[d/l]
[select]

        That may be. I threw it together and locked it to return related to fixed row number for testing other parts.

        Another to-do

[reply]

        Need some insight. Have been staring at this since last night. Placeholder issue

        if ($kind == 3){
        #Last record

        $stmt = "SELECT * FROM ( SELECT * FROM users WHERE ? <= ? AND 
+? >= ? ORDER BY ? ASC LIMIT 1 ) sub ORDER BY ? ASC"; 
        
        #example user_id 
#        $stmt = "SELECT * FROM ( SELECT * FROM users WHERE user_id <=
+14999 AND user_id >= 1 ORDER BY user_id DESC LIMIT 1 ) sub ORDER BY u
+ser_id ASC"; #Works fine hard coded

        [download]

        placeholder substitution

            $sth = $dbh->prepare ($stmt) or die "Error Preparing:\n" . $stmt .
+ "\nDBI returned: \n", $dbh->errstr;

    warn("'$searchfield' '$maxrowpointer' '$searchfield' '$minrowpoint
+er' '$searchfield'  '$searchfield'"); #verified correct values in var
+iables at this point
       
    $sth->execute ($searchfield, $maxrowpointer,$searchfield,$minrowpo
+inter, $searchfield, $searchfield) or die "Unable to execute query: "
+ . $sth->errstr;

    my $refresult = $sth->fetchrow_hashref();
    
    my $count = $sth->rows;

        [download]

        This works fine with hard coded vaues instead of placeholders and also testing MySQL directly. count = 1 When running with the placeholders, count = 0

        Result with hard code JSON output below

        {"DD":"2019-01-30","DP":"2018-12-31","MD":"120.00","MJ":"2018-01-30","
+address1":"1471 Meeks Rd","address2":null,"business":"JZ 

Electroplating","city":"Warran","comments":"This is a test 

entry","email":"jze@yahoo.com","forename":"John","id":57,"lastname":"Z
+inzer","password":"1234","phone_cell":"517-240-1004","phone_home":"51
+7-233-

4378","pin":"JbwmZ","position":"General Member","state":"MI","user_id"
+:19,"username":"bwm19","zip":"45789-2334"}

        [download]
[reply]
[d/l]
[select]
Re^4: CGI Action call
by tultalk (Monk) on Mar 17, 2018 at 02:23 UTC

    Speaking of placeholders. Playing around here. Just conceptualizing.

    Probably been done a million times before

    Can something sort of like this this work for automatically selecting the fields needing updating?

    Run a select * using user_id returning one record and load values into the $oldxxx variables then compare to the values submitted in the update query. 

                $placeholders;
            $replacement-fields;
            
            
            if ($password <> $oldpassword){
              $placeholders = $placeholders . "?,";
              $replacement-fields = $replacement-fields . "password = 
+'$password',";   
            }
            if ($forename <> $oldforename)){
              $placeholders = $placeholders . "?,";
              $replacement-fields = $replacement-fields . "forename = 
+'$forename',;
            }
            if ($lastname.value <> $oldlastname){
              $placeholders = $placeholders . "?,";
              $replacement-fields = $replacement-fields . "lastname = 
+'$lastname',";   
            }
            if ($business <> $oldbusiness){
              $placeholders = $placeholders . "?" 
              $replacement-fields = $replacement-fields . "business = 
+'$business',";   
            }
            if ($city <> $oldcity){
              $placeholders = $placeholders . "?," 
              $replacement-fields = $replacement-fields . "city = '$ci
+ty',";   
            }
            if ($zip <> $oldzip){
              $placeholders = $placeholders . "?,"   <----------------
+----------
              $replacement-fields = $replacement-fields . "zip = '$zip
+',";  <--- Problem with last , from last field requiring update. Pull
+ that off before using the two variables   
            }
            
            
           $query = "UPDATE users SET $placeholders where user_id = '$
+user_id'";

           $sth = $dbh->prepare($query);

           $sth->execute($replacement-fields) or die "Unable to execut
+e query: " . $sth->errstr;

    [download]
[reply]
[d/l]

      Yes you can do that.

      But to do that in a sensible way, please learn about the data structures that Perl offers and also read the DBI documentation.

      ->execute takes a list, not a comma-separated string. So, instead of building a comma-separated string, which still has the same interpolation problems, use an array to collect the changed fields.

      Also, it seems you are reinventing your own ORM. You might want to take a look at DBIx::Class or Class::DBI instead.

[reply]
[d/l]

      Use hash keys to avoid lots of variables. Push the field names, values onto arrays and use join to create the statement

      #!perl
use strict;

my $user_id = 1;

my $old = {
  password => 'password', forename  => 'forename',
  lastname => 'lastname', business => 'business',
  city     => 'city',     zip      => 'zip'
};

my $new = {
  password => 'password1',  forename  => 'forename',
  lastname => 'lastname1', business => 'business',
  city     => 'city1',     zip      => 'zip1'
};

my @fields;
my @values;
# compare old v new
for (sort keys %$new){
  if ($new->{$_} ne $old->{$_}){
    push @fields,"$_ = ?";
    push @values,$new->{$_};
  }
}

# skip if no change
if (@fields == 0){
  print "No update required\n";
} else {
  # build sql
  my $fields = join ',',@fields;
  my $stmt = "
   UPDATE table 
   SET $fields
   WHERE user_id = ?";
   # add id
   push @values,$user_id;
   
   # prepare and execute sql
   print "$stmt\n(@values)\n"; 
}

      [download]
      poj
[reply]
[d/l]

In Section Seekers of Perl Wisdom

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?

www.com | www.net | www.org
Node Status?
node history
Node Type: note [id://1210748]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? • Last hour • Other CB clients
Other Users?
Others romping around the Monastery: (3)
As of 2024-04-19 04:09 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found