[ic] SQL query returning no results

[graham hadgraft]

On 27/10/06, Peter <peter at pajamian.dhs.org> wrote:
> On 10/27/2006 03:24 AM, graham hadgraft wrote:
> > I am trying to perform a query that takes the string from a series of
> > checkboxes and returns rowes which have a field which contains all
> > these strings in any order.
> >
> > This is the code i am using:
> >
> > [perl tables="products"]
> >     @array = split(/-/,$CGI->{from});
> >     my $res = '';
> >     $sql = "SELECT * FROM products WHERE icons LIKE '%'";
> >
> >     foreach(@array){
> >         $sql .= " AND icons LIKE '%" . $_ . "%'";
> >     }
> >
> >     $Tag->query({sql=>$sql,hashref=>'results',table=>'products'});
> >
> >     foreach my $row (@{$Tmp->{results}})
> >     {
> >         $res .= $row->{description} . "test<br>";
> >     }
> >     return $res;
> > [/perl]
> >
> >
> > This returns no string.  I have changed the return to return the sql
> > string and then ran the same sql query in directsql and this returns
> > the correct products.  Changing it back to return res brings back no
> > string.
>
> I can't say for sure what's causing your problem without seeing your
> error logs.  I can tell you a better way to do it, though.
>
> First off you're introducing an SQL injection vulnerability by taking a
> CGI value and using it directly in your SQL.  how this can be exploited
> iws very limited, but it is still possible for someone to craft some
> rather clever SQL to inject that could do all sorts of things (ie I'd
> have to really work at it but I can see it being possible to inject a
> subquery that might return data from the userdb table, and thereby allow
> access to other usernames and passwords).
>
> I recommend that you do something like this instead (untested):
>
> [perl tables=products interpolate=0]
>         # interpolate=0 because you don't have any tags in this block
>         # that need to be parsed ahead of time.
>         my @array = split(/-/,$CGI->{from});
>         my $db = $Db{products};
>         # Specify your return fields to avoid unecessary overhead,
>         # and also you know what order they come in that way.
>         my $sql = "SELECT description FROM products WHERE icons LIKE '%'";
>
>         foreach (@array) {
>                 # Quote any untrusted input to prevent an SQL injection
>                 # attack.
>                 my $test = $db->quote("\%$_\%");
>                 $sql .= " AND icons LIKE $test";
>         }
>
>         # This is a better way of sending a query from a [perl] block.
>         # it returns an array ref of array refs (rather than hash refs)
>         # so it is handy to know what order the fields get returned in.
>         my $ref = $db->query($sql);
>
>         # This is just another way of joining up the results.  You can
>         # assign it to a variable and return the variable, or return
>         # this code directly, or just leave it like this and the return
>         # value will still be the same because it falls off the end of
>         # the [perl] block.
>         join ('', map { $_->[0] . 'test<br>' } @$ref);
> [/perl]
>
>
> Peter
> _______________________________________________
> interchange-users mailing list
> interchange-users at icdevgroup.org
> http://www.icdevgroup.org/mailman/listinfo/interchange-users
>


Using this code if the checkbox had only one value selected the code
works however when more than one check box is selected it stops
working despite the query being used working in direct sql.

Any idea why this would happen.  Trying this with the previous code i
had it also does the same.  Sql injection will not be a problem as
this page is only available on a page only used by me and someone else
in my company as this is on the admin page.