[ic] SQL query returning no results

[Peter]

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