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

My guess is that $searchfield contains the string id or some other string that you want to be interpreted as column name.

You are correct, however:

Regarding placeholdrs: You stated and the perl docs state that placeholders cannot be used for elements of the SQL statement such as field names.

The code below demonstates use of a placeholder for a field name (lastname) and this works.

I am asking because I have a much more complicated statement with 6 placeholders (4 field names and 2 values) which fails.It works fine with hard coded field names. I am trying to use one statement for 3 different queries each using different sets of fields/values

I am trying to understand why the one below works

sub updatetable_167 {
    warn("Entered updatetable_167");
    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';    
    }
    
    #Sort Index For The Three Kinds Of ORDER Supposed to be global.
    
    $sortindex = $kind;
    warn("sortindex =  '$sortindex'");
    
    warn("searchfield =  '$searchfield'");
    
    my $stmt = "SELECT * FROM users WHERE $searchfield = ? ORDER BY ? 
+ASC";
    
    warn("statement =  '$stmt'");    
    
    my $sth = $dbh->prepare ($stmt) or die "Error Preparing:\n" . $stm
+t . "\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]

warn log


[Mon Mar 19 19:01:04 2018] update_tables.cgi: statement =  'SELECT * F
+ROM users WHERE lastname = ? ORDER BY ? ASC' at update_tables.cgi lin
+e 462.

[Mon Mar 19 19:01:04 2018] update_tables.cgi: count = '1' at update_ta
+bles.cgi line 486.

[Mon Mar 19 19:01:04 2018] update_tables.cgi: Finished print 

{"DD":"2018-01-30","DP":"2018-12-31","MD":"120.00","MJ":"2017-01-30","
+address1":"1345 Griffin Ave","address2":"$47","business":"ZI Inc","ci
+ty":"Deluth","comments":"Another test at the Z enc","email":"tz@voyag
+er.net","forename":"Adam","id":58,"lastname":"Zorky","password":"xxxx
+xx","phone_cell":"(517) 240-1004","phone_home":"(345) 247-1655","pin"
+:null,"position":"General Member","state":"MN","user_id":2,"username"
+:"bwm2","zip":"38456"} at update_tables.cgi line 492.

[download]

Replies are listed 'Best First'.
Re^9: CGI Action call
by poj (Abbot) on Mar 20, 2018 at 09:31 UTC
    The code below demonstrates use of a placeholder for a field name (lastname) and this works. 
    my $stmt= "SELECT * FROM users WHERE $searchfield = ? ORDER BY ? ASC";
my $sth = $dbh->prepare($stmt);
$sth->execute($searchterm, $searchfield);

    [download]

    I guess you are referring to the ORDER BY ?.That would be prepared as ORDER BY 'lastname' meaning, "order by the word lastname", not the value of a column named lastname. You can prove this by trying

    $sth->execute($searchterm, 'rubbish');

    You may 'think' it is working since you are only retrieving one record. See single-quotes-in-mysql-order-by-clause

    poj
[reply]
[d/l]
[select]
Re^9: CGI Action call
by Corion (Patriarch) on Mar 20, 2018 at 08:07 UTC
    The code below demonstates use of a placeholder for a field name (lastname) and this works. ... 
    my $stmt = "SELECT * FROM users WHERE $searchfield = ? ORDER BY ? ASC"
+;
warn("statement =  '$stmt'");

    [download]
    [Mon Mar 19 19:01:04 2018] update_tables.cgi: statement =  'SELECT * F
+ROM users WHERE lastname = ? ORDER BY ? ASC' at update_tables.cgi lin
+e 462.

    [download]

    No. It does not use a placeholder for lastname. It interpolates $searchfield directly into the query. It uses a placeholder for the column value, but that is different from the column name.

[reply]
[d/l]
[select]

      See what you are saying

      If the stmt was

       my $stmt = "SELECT * FROM users WHERE ? = ? ORDER BY ? 
+ASC";

      [download]

      instead, it would fail with

      $sth->execute($searchfield, $searchterm, $searchfield) or die "Unable 
+to execute
+ query: " . $sth->errstr;

      [download]

      But this works as the field name is assigned early.

      [Mon Mar 19 19:01:04 2018] update_tables.cgi: statement =  'SELECT * F
+ROM users WHERE lastname = ? ORDER BY ? ASC' at update_tables.cgi lin
+e 462.

      [download]

      So what would be lost by simply using:

      'SELECT * F
+ROM users WHERE $searchfield = $searchterm ORDER BY $searchfield ASC'

      [download]

      where all the parameter would be defined at prepare?

[reply]
[d/l]
[select]

        Because $searchterm is user-supplied, I could supply O'Reilly to break your SQL query or  1; delete from users -- to wipe all users from the user table or  1; update users set is_admin=1 -- to make all accounts administrator accounts.

        Interpolating user-supplied data into SQL statements is a problematic thing and best avoided.

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

In Section Seekers of Perl Wisdom