[ic] $db-->query inner join

[Peter]

On 03/10/2009 08:05 AM, Gert van der Spoel wrote:
>> my $q = $db->query({
>>
>> sql => 'SELECT sum(quantity) FROM inventory INNER JOIN variants WHERE
>> inventory.sku = variants.code and variants.sku=$sku',
>>
>> });
> 
> -  and variants.sku="$sku"   perhaps ... because sku I expect is not a
> number, but a string

This is bad advice for three reasons.  First off sql string quoting uses
single quotes, and secondly you should use the quote() method to avoid
sql injection attacks, and thirdly, perl won't interpolate $sql in a
single quoted perl string.

Sam, I recommend the following:

[perl inventory variants]

...

	my $db = $Db{inventory};
	my $qsku = $db->quote($sku);
	my $ref = $db->query("SELECT SUM(i.quantity) FROM inventory i INNER
JOIN variants v WHERE i.sku = v.code AND v.sku=$qsku");
	my $quantity = $ref->[0]->[0];

...

[/perl]