SQL issues in uc_restrict_qt [#742922]

Dear friends,

First, thanks for this wonderful module.

I noticed this little SQL error:

query: SELECT SUM(uop.qty) FROM uc_orders uo LEFT JOIN uc_order_products uop  
ON uo.order_id = uop.order_id WHERE uo.order_status =  
"completed" AND uo.uid = 1 AND uop.nid = 31 ORDER BY  
uop.nid in  
/home/html/gooze.eu/www/sites/all/modules/uc_restrict_qty/uc_restrict_qty.module  
on line 284.

In fact, there are two issues:
* First, double-quotes are used for column names, not values
so the query should be uo.order_status = 'completed'
as explained here: http://drupal.org/node/555548

* Secondly, under PostgreSQL there is a need for an additional GROUP BY
as explained here: http://drupal.org/node/555530

so the query should be GROUP BY uop.nid ORDER BY uop.nid

In fact, the final PHP code is:
284 $bought_qty = db_result(db_query('SELECT SUM(uop.qty) FROM {uc_orders} uo LEFT JOIN {uc_order_products} uop ON uo.order_id = uop.order_id WHERE uo.or der_status = 'completed' AND uo.uid = %d AND uop.nid = %d GROUP BY uop.nid ORDER BY uop.nid', $user->uid, $form_values['nid']));

Feel free to apply ASAP, as it is safe.

Comment	File	Size	Author
#6	uc_restrict_qty.diff	1.09 KB	grub3
#2	uc_restrict_qty.diff	1.11 KB	grub3

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Comment #1

grub3 CreditAttribution: grub3 commented 15 March 2010 at 15:58

Sorry, the code is:

284 $bought_qty = db_result(db_query("SELECT SUM(uop.qty) FROM {uc_orders} uo LEFT JOIN {uc_order_products} uop ON uo.order_id = uop.order_id WHERE uo.or der_status = 'completed' AND uo.uid = %d AND uop.nid = %d GROUP BY uop.nid ORDER BY uop.nid", $user->uid, $form_values['nid']));

Tested on a production server.

Comment #2

grub3 CreditAttribution: grub3 commented 17 August 2010 at 15:14

Status:

Needs review

» Reviewed & tested by the community

File	Size
uc_restrict_qty.diff	1.11 KB

Please apply this patch. Setting it to reviewed as it is very straightforward.

Comment #3

grub3 CreditAttribution: grub3 commented 17 August 2010 at 15:18

Sorry, this is the patch file:

Comment #4

grub3 CreditAttribution: grub3 commented 17 August 2010 at 15:28

The patch did not seem to upload. Here it is in text:

Index: uc_restrict_qty.module
===================================================================
--- uc_restrict_qty.module	(revision 1448)
+++ uc_restrict_qty.module	(working copy)
@@ -294,7 +294,7 @@
 
   $data = db_fetch_array(db_query("SELECT qty, lifetime FROM {uc_restrict_qty_products} WHERE nid = %d", $form_values['nid']));
   if ($data['lifetime']) {
-    $bought_qty = db_result(db_query('SELECT SUM(uop.qty) FROM {uc_orders} uo LEFT JOIN {uc_order_products} uop ON uo.order_id = uop.order_id WHERE uo.order_status = "completed" AND uo.uid = %d AND uop.nid = %d ORDER BY uop.nid', $user->uid, $form_values['nid']));
+    $bought_qty = db_result(db_query("SELECT SUM(uop.qty) FROM {uc_orders} uo LEFT JOIN {uc_order_products} uop ON uo.order_id = uop.order_id WHERE uo.order_status = 'completed' AND uo.uid = %d AND uop.nid = %d", $user->uid, $form_values['nid']));
     $data['rest'] = $data['qty'] - $bought_qty;
   }
 
@@ -323,4 +323,4 @@
  */
 function uc_restrict_qty_feature_delete($pfid) {
   db_query("DELETE FROM {uc_restrict_qty_products} WHERE pfid = %d", $pfid);
-}
\ No newline at end of file
+}

There is no way we should add 'ORDER BY uop.nid' as it is useless and it makes an SQL error under PostgreSQL.