[ic] SQL query returning no results

[ic at 3edge.com]

graham hadgraft writes: 

> 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.

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