Re^6: CGI Action call

[tultalk]

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

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;

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":"[e-mail]","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"}

[Corion]

What is the value of $searchfield? You don't show it to us so we have to guess. This makes it much harder to provide you concise help. Please adjust your debugging process and tell us what you see/output instead of making us guess.

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

Did you read the section about placeholders in DBI?

Most likely, you skipped over the part where it tells you where placeholders can be used:

Placeholders, also called parameter markers, are used to indicate values in a database statement that will be supplied later, ...
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.


Naturally, you can't supply the name of a column as a placeholder because the database can't validate your statement before seeing the column name.

What happens instead is that your database treats all placeholders as values and never compares the columns but just values of the placeholders.

[tultalk]

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);
    }

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":"[phone]","phone_home":"[phone]","pin"
+:null,"position":"General Member","state":"MN","user_id":2,"username"
+:"bwm2","zip":"38456"} at update_tables.cgi line 492.

[poj]

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);

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

[Corion]

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'");

[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.

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.