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

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?

Replies are listed 'Best First'.
Re^11: CGI Action call
by Corion (Patriarch) on Mar 20, 2018 at 09:50 UTC

    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]

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

      How do you avoid having a user (administrator only in this case) enter a user supplied search term like a last name?

      Perhaps I don't understand your statement.

[reply]
        my $kind = $query->param('kind');
my $searchterm = $query->param('searchterm');
my $searchfield;
if ($kind == 0) {
  $searchfield = 'user_id';
} elsif ($kind == 1) {
  $searchfield = 'lastname';    
} elsif ($kind == 2) 
  $searchfield = 'business';    
}
my $stmt  = "
  SELECT * FROM users 
  WHERE $searchfield = ? 
  ORDER by $searchfield";  
my $sth = $dbh->prepare($stmt);
$sth->execute($searchterm);

        [download]

        In the above, the interpolated field $searchfield is not user supplied. The user supplied $searchterm uses a placeholder so no problem searching for the lastname O'Reilly.

        poj
[reply]
[d/l]
[select]
        You say: Interpolating user-supplied data into SQL statements is a problematic thing and best avoided.

        No. He says: User data may, maliciously or accidentally, including programmer error, be problematic and must be sanitised.

        Regards,

        John Davies

[reply]
Re^11: CGI Action call
by marto (Cardinal) on Mar 20, 2018 at 10:08 UTC
[reply]

In Section Seekers of Perl Wisdom