Re: Spiking the mySQL parameter

All of the comments here have been useful and I'm absorbing them fully--thanks to everyone. However, this one by Zaxo about a user spiking a param with an extra mySQL statement to be inserted at the end of my mySQL statement, I can't figure out how to resolve. Talking it over with cerberus who works with me, we figured out that we can do something like this:

my $sort = $q->param("sort");

if($sort ne "emp_id" || $sort ne "emp_last" || $sort ne "dept") {
   $sort = "emp_last"; }
[download]

This would get rid of any destructive mySQL statements a hacker might throw into the CGI parameter. However, what about scripts where we're taking in a search parameter? I thined out some pieces of the script above, emp-list.cgi, for a shorter and more concise post. One piece I left out is a search feature which I feel I should now post for this side question:

my $search_text = param("search_text") || "";

if($search_text ne "") {
   $sql_stmnt = "SELECT emp_id, CONCAT(emp_first, ' ', emp_last)
                 FROM sys_main.humans
                 WHERE emp_first LIKE '%$search_text%'
                 OR emp_last LIKE '%$search_text%'";
$sth = $dbh->prepare($sql_stmnt);
$sth->execute();

while(@emp_matches = $sth->fetchrow_array()) {
      $emp_matches{$emp_matches[0]} = $emp_matches[1]; } }
[download]

Here I'm basically getting a list of matching names and putting them in a hash for the user to choose the specific employee she wants to view details on. In this case, we wouldn't know all of the acceptable answers and couldn't filter out hacking attempts so easily. Any thoughts?

-Spenser

Update
To answer my own question for future reference by others, I believe I've figured out how to stop a user from appending a CGI/mySQL query statement with the following as Zaxo suggested:

...;delete from sysmain.humans where '1'

You just change the user permissions in mySQL not to allow deletion of records by the CGI script user.

Comment on Re: Spiking the mySQL parameter Select or Download Code

Replies are listed 'Best First'.
Re: Re: Spiking the mySQL parameter by dws (Chancellor) on Sep 12, 2002 at 20:14 UTC
You can rewrite `$sql_stmnt = "SELECT emp_id, CONCAT(emp_first, ' ', emp_last) FROM sys_main.humans WHERE emp_first LIKE '%$search_text%' OR emp_last LIKE '%$search_text%'"; $sth = $dbh->prepare($sql_stmnt); $sth->execute();` [download] as `$sql_stmnt = "SELECT emp_id, CONCAT(emp_first, ' ', emp_last) FROM sys_main.humans WHERE emp_first LIKE ? OR emp_last LIKE ?"; $sth = $dbh->prepare($sql_stmnt); $sth->execute("%$search_text%", "%$search_text%");` [download] and get the benefit of having the values automatically quoted for you.	[reply] [d/l] [select]
Re: Re: Spiking the mySQL parameter by Hero Zzyzzx (Curate) on Sep 13, 2002 at 16:45 UTC
This is how I deal with user-defined sort orders, which I (personally) think is a little more readable. . .This is taken straight from production code. . .Plus, to add more sorts you only need to add them to the drop-down and the hash. my %sorts=( form=>'form_list.form_title', rform=>'form_list.form_title desc', name=>'stored_forms.client_name', rname=>'stored_forms.client_name desc', prepared=>'stored_forms.advocate_name', rprepared=>'stored_forms.advocate_name desc', date=>'stored_forms.created', rdate=>'stored_forms.created desc' ); my $sort=$sorts{$q->param('sort')} \|\| $sorts{'form'}; my $forms=$dbh->prepare("select stored_forms.client_name, form_lis +t.form_title, stored_forms.id, stored_forms.advocate_name,date_format(stored_forms.created,'%c/%e/%Y' +) as save_date, stored_forms.client_accountnum,form_list.form_location from form_list, stored_forms where form_list.form_type = stored_forms.form_type and stored_forms.client_accountnum like ? order by stored_forms.client_accountnum,".$sort); [download] HTH -Any sufficiently advanced technology is indistinguishable from doubletalk.	[reply] [d/l]