[ic] SQL query returning no results
ic at 3edge.com
ic at 3edge.com
Fri Oct 27 07:28:08 EDT 2006
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
More information about the interchange-users
mailing list