Re: [ic] SQL query returning no results

[suppressed]

graham hadgraft writes: 

> On 27/10/06, Peter <suppressed> 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
> > suppressed
> > 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.

Have you tried something like: 

my $hashres = $Tag->query({sql=>$sql,hashref=>'results',table=>'products'}); 

foreach my $row (@{$hashres}) {
..... 


And sure thing no worries for SQL injection, but adapting this defensive 
programming style even in a safe environment helps in not forgetting the 
same in a less safe environment. 

CU, 

Gert 


_______________________________________________
interchange-users mailing list
suppressed
http://www.icdevgroup.org/mailman/listinfo/interchange-users