Re^10: CGI Action call

[tultalk]

See what you are saying

If the stmt was

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

instead, it would fail with

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

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.

So what would be lost by simply using:

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

where all the parameter would be defined at prepare?

[Corion]

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.

[tultalk]

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.

[poj]

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

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

[davies]

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

[marto]

Obligatory Bobby Tables link.