From 54d84ff9a67620ab38c676cdbcf87853632724f0 Mon Sep 17 00:00:00 2001 From: Janusz Dobrowolski Date: Thu, 15 Oct 2009 12:22:42 +0000 Subject: [PATCH] Security statements update against sql injection attacks. --- admin/attachments.php | 27 +++--- admin/db/company_db.inc | 6 +- admin/db/printers_db.inc | 10 +-- admin/db/users_db.inc | 14 +-- admin/db/voiding_db.inc | 6 +- admin/payment_terms.php | 8 +- admin/print_profiles.php | 2 +- admin/printers.php | 4 +- admin/shipping_companies.php | 10 +-- admin/view_print_transaction.php | 6 +- dimensions/includes/dimensions_db.inc | 19 +++-- dimensions/inquiry/search_dimensions.php | 6 +- gl/bank_account_reconcile.php | 13 +-- gl/gl_budget.php | 29 ++++--- gl/includes/db/gl_db_account_types.inc | 21 ++--- gl/includes/db/gl_db_accounts.inc | 24 +++--- gl/includes/db/gl_db_bank_accounts.inc | 52 ++++++------ gl/includes/db/gl_db_bank_trans.inc | 21 +++-- gl/includes/db/gl_db_banking.inc | 2 +- gl/includes/db/gl_db_currencies.inc | 13 +-- gl/includes/db/gl_db_rates.inc | 15 ++-- gl/includes/db/gl_db_trans.inc | 85 +++++++++++-------- gl/inquiry/bank_inquiry.php | 5 +- gl/view/bank_transfer_view.php | 2 +- gl/view/gl_trans_view.php | 6 +- inventory/cost_update.php | 2 +- inventory/includes/db/items_category_db.inc | 8 +- inventory/includes/db/items_codes_db.inc | 31 +++---- inventory/includes/db/items_db.inc | 44 +++++----- inventory/includes/db/items_locations_db.inc | 20 +++-- inventory/includes/db/items_prices_db.inc | 17 ++-- inventory/includes/db/items_trans_db.inc | 16 ++-- inventory/includes/db/items_units_db.inc | 16 ++-- inventory/includes/db/movement_types_db.inc | 6 +- inventory/inquiry/stock_movements.php | 8 +- inventory/manage/item_categories.php | 2 +- inventory/manage/item_units.php | 6 ++ inventory/manage/items.php | 8 +- inventory/manage/locations.php | 18 ++-- inventory/manage/movement_types.php | 2 +- inventory/purchasing_data.php | 26 +++--- manufacturing/includes/db/work_centres_db.inc | 6 +- .../includes/db/work_order_issues_db.inc | 31 ++++--- .../db/work_order_produce_items_db.inc | 15 ++-- .../db/work_order_requirements_db.inc | 18 ++-- manufacturing/includes/db/work_orders_db.inc | 61 +++++++------ .../includes/db/work_orders_quick_db.inc | 5 +- manufacturing/inquiry/where_used_inquiry.php | 2 +- manufacturing/manage/bom_edit.php | 29 ++++--- manufacturing/manage/work_centres.php | 4 +- manufacturing/search_work_orders.php | 6 +- purchasing/includes/db/grn_db.inc | 64 ++++++++------ purchasing/includes/db/invoice_db.inc | 32 ++++--- purchasing/includes/db/invoice_items_db.inc | 14 +-- purchasing/includes/db/po_db.inc | 39 ++++----- purchasing/includes/db/supp_trans_db.inc | 20 +++-- purchasing/includes/db/suppalloc_db.inc | 31 ++++--- purchasing/includes/db/suppliers_db.inc | 8 +- purchasing/includes/purchasing_db.inc | 25 +++--- purchasing/inquiry/po_search.php | 6 +- purchasing/inquiry/po_search_completed.php | 6 +- .../inquiry/supplier_allocation_inquiry.php | 2 +- purchasing/inquiry/supplier_inquiry.php | 2 +- purchasing/manage/suppliers.php | 8 +- purchasing/po_entry_items.php | 2 +- purchasing/po_receive_items.php | 2 +- purchasing/supplier_credit.php | 2 +- purchasing/supplier_invoice.php | 10 +-- 68 files changed, 597 insertions(+), 489 deletions(-) diff --git a/admin/attachments.php b/admin/attachments.php index fca57867..0e1b4e28 100644 --- a/admin/attachments.php +++ b/admin/attachments.php @@ -112,25 +112,27 @@ if ($Mode == 'ADD_ITEM' || $Mode == 'UPDATE_ITEM') if ($Mode == 'ADD_ITEM') { $sql = "INSERT INTO ".TB_PREF."attachments (type_no, trans_no, description, filename, unique_name, - filesize, filetype, tran_date) VALUES (".$_POST['filterType'].",".$_POST['trans_no'].",". - db_escape($_POST['description']).", '$filename', '$unique_name', '$filesize', '$filetype', '$date')"; + filesize, filetype, tran_date) VALUES (".db_escape($_POST['filterType'])."," + .db_escape($_POST['trans_no']).",".db_escape($_POST['description']).", " + .db_escape($filename).", ".db_escape($unique_name).", ".db_escape($filesize) + .", ".db_escape($filetype).", '$date')"; db_query($sql, "Attachment could not be inserted"); display_notification(_("Attachment has been inserted.")); } else { $sql = "UPDATE ".TB_PREF."attachments SET - type_no=".$_POST['filterType'].", - trans_no=".$_POST['trans_no'].", + type_no=".db_escape($_POST['filterType']).", + trans_no=".db_escape($_POST['trans_no']).", description=".db_escape($_POST['description']).", "; if ($filename != "") { - $sql .= "filename='$filename', - unique_name='$unique_name', - filesize='$filesize', - filetype='$filetype', "; + $sql .= "filename=".db_escape($filename).", + unique_name=".db_escape($unique_name).", + filesize=".db_escape($filesize).", + filetype=".db_escape($filetype); } - $sql .= "tran_date='$date' WHERE id=$selected_id"; + $sql .= "tran_date='$date' WHERE id=".db_escape($selected_id); db_query($sql, "Attachment could not be updated"); display_notification(_("Attachment has been updated.")); } @@ -143,7 +145,7 @@ if ($Mode == 'Delete') $dir = $comp_path."/".user_company(). "/attachments"; if (file_exists($dir."/".$row['unique_name'])) unlink($dir."/".$row['unique_name']); - $sql = "DELETE FROM ".TB_PREF."attachments WHERE id = $selected_id"; + $sql = "DELETE FROM ".TB_PREF."attachments WHERE id = ".db_escape($selected_id); db_query($sql, "Could not delete attachment"); display_notification(_("Attachment has been deleted.")); $Mode = 'RESET'; @@ -173,13 +175,14 @@ function viewing_controls() function get_attached_documents($type) { - $sql = "SELECT * FROM ".TB_PREF."attachments WHERE type_no=$type ORDER BY trans_no"; + $sql = "SELECT * FROM ".TB_PREF."attachments WHERE type_no=".db_escape($type) + ." ORDER BY trans_no"; return db_query($sql, "Could not retrieve attachments"); } function get_attachment($id) { - $sql = "SELECT * FROM ".TB_PREF."attachments WHERE id=$id"; + $sql = "SELECT * FROM ".TB_PREF."attachments WHERE id=".db_escape($id); $result = db_query($sql, "Could not retrieve attachments"); return db_fetch($result); } diff --git a/admin/db/company_db.inc b/admin/db/company_db.inc index 672d9545..6a0a4ac4 100644 --- a/admin/db/company_db.inc +++ b/admin/db/company_db.inc @@ -118,14 +118,14 @@ function add_fiscalyear($from_date, $to_date, $closed) $to = date2sql($to_date); $sql = "INSERT INTO ".TB_PREF."fiscal_year (begin, end, closed) - VALUES (".db_escape($from).",".db_escape($to).", $closed)"; + VALUES (".db_escape($from).",".db_escape($to).", ".db_escape($closed).")"; db_query($sql, "could not add fiscal year"); } function update_fiscalyear($id, $closed) { - $sql = "UPDATE ".TB_PREF."fiscal_year SET closed=$closed + $sql = "UPDATE ".TB_PREF."fiscal_year SET closed=".db_escape($closed)." WHERE id=".db_escape($id); db_query($sql, "could not update fiscal year"); @@ -151,7 +151,7 @@ function get_current_fiscalyear() { $year = get_company_pref('f_year'); - $sql = "SELECT * FROM ".TB_PREF."fiscal_year WHERE id=$year"; + $sql = "SELECT * FROM ".TB_PREF."fiscal_year WHERE id=".db_escape($year); $result = db_query($sql, "could not get current fiscal year"); diff --git a/admin/db/printers_db.inc b/admin/db/printers_db.inc index f605f658..572dca5e 100644 --- a/admin/db/printers_db.inc +++ b/admin/db/printers_db.inc @@ -15,13 +15,13 @@ function write_printer_def($id, $name, $descr, $queue, $host, $port, $timeout) if ($id>0) $sql = "UPDATE ".TB_PREF."printers SET description=".db_escape($descr) .",name=".db_escape($name).",queue=".db_escape($queue) - .",host=".db_escape($host).",port='$port',timeout='$timeout' " - ."WHERE id=$id"; + .",host=".db_escape($host).",port=".db_escape($port).",timeout=".db_escape($timeout) + ." WHERE id=".db_escape($id); else $sql = "INSERT INTO ".TB_PREF."printers (" ."name,description,queue,host,port,timeout) " ."VALUES (".db_escape($name).",".db_escape($descr)."," - .db_escape($queue).",".db_escape($host).",'$port','$timeout')"; + .db_escape($queue).",".db_escape($host).",".db_escape($port).",".db_escape($timeout).")"; return db_query($sql,"could not write printer definition"); } @@ -35,7 +35,7 @@ function get_all_printers() function get_printer($id) { $sql = "SELECT * FROM ".TB_PREF."printers - WHERE id=$id"; + WHERE id=".db_escape($id); $result = db_query($sql,"could not get printer definition"); return db_fetch($result); @@ -71,7 +71,7 @@ function update_printer_profile($name, $dest) function get_report_printer($profile, $report) { $sql = "SELECT printer FROM ".TB_PREF."print_profiles WHERE " - ."profile=".db_escape($profile)." AND report="; + ."profile=".db_escape($profile)." AND report=".db_escape($report); $result = db_query($sql.db_escape($report), 'report printer lookup failed'); diff --git a/admin/db/users_db.inc b/admin/db/users_db.inc index e9ea2e8f..ed6d85ce 100644 --- a/admin/db/users_db.inc +++ b/admin/db/users_db.inc @@ -14,10 +14,10 @@ function add_user($user_id, $real_name, $password, $phone, $email, $full_access, $language, $profile, $rep_popup, $pos) { $sql = "INSERT INTO ".TB_PREF."users (user_id, real_name, password" - .", phone, email, role_id, language, pos, print_profile, rep_popup) + .", phone, email, full_access, language, pos, print_profile, rep_popup) VALUES (".db_escape($user_id).", ".db_escape($real_name).", ".db_escape($password) .",".db_escape($phone) - .",".db_escape($email).", ".db_escape($role_id).", ".db_escape($language) + .",".db_escape($email).", ".db_escape($full_access).", ".db_escape($language) .", ".db_escape($pos).",".db_escape($profile).",".db_escape($rep_popup) ." )"; @@ -42,11 +42,11 @@ function update_user($user_id, $real_name, $phone, $email, $full_access, $sql = "UPDATE ".TB_PREF."users SET real_name=".db_escape($real_name). ", phone=".db_escape($phone).", email=".db_escape($email).", - full_access=$full_access, + full_access=".db_escape($full_access).", language=".db_escape($language).", print_profile=".db_escape($profile).", - rep_popup=$rep_popup, - pos=$pos + rep_popup=".db_escape($rep_popup).", + pos=".db_escape($pos)." WHERE user_id = ".db_escape($user_id); db_query($sql, "could not update user for $user_id"); } @@ -96,7 +96,7 @@ function get_users() function get_user($user_id) { - $sql = "SELECT * FROM ".TB_PREF."users WHERE user_id = '$user_id'"; + $sql = "SELECT * FROM ".TB_PREF."users WHERE user_id = ".db_escape($user_id); $result = db_query($sql, "could not get user for $user_id"); @@ -107,7 +107,7 @@ function get_user($user_id) function delete_user($user_id) { - $sql="DELETE FROM ".TB_PREF."users WHERE user_id='$user_id'"; + $sql="DELETE FROM ".TB_PREF."users WHERE user_id=".db_escape($user_id); db_query($sql, "could not delete user $user_id"); } diff --git a/admin/db/voiding_db.inc b/admin/db/voiding_db.inc index fafac430..59e3680d 100644 --- a/admin/db/voiding_db.inc +++ b/admin/db/voiding_db.inc @@ -110,7 +110,8 @@ function void_transaction($type, $type_no, $date_, $memo_) function get_voided_entry($type, $type_no) { - $sql = "SELECT * FROM ".TB_PREF."voided WHERE type=$type AND id=$type_no"; + $sql = "SELECT * FROM ".TB_PREF."voided WHERE type=".db_escape($type) + ." AND id=".db_escape($type_no); $result = db_query($sql, "could not query voided transaction table"); @@ -123,7 +124,8 @@ function add_voided_entry($type, $type_no, $date_, $memo_) { $date = date2sql($date_); $sql = "INSERT INTO ".TB_PREF."voided (type, id, date_, memo_) - VALUES ($type, $type_no, ".db_escape($date).", ".db_escape($memo_).")"; + VALUES (".db_escape($type).", ".db_escape($type_no).", " + .db_escape($date).", ".db_escape($memo_).")"; db_query($sql, "could not add voided transaction entry"); } diff --git a/admin/payment_terms.php b/admin/payment_terms.php index 6939c9bd..3a4daf1f 100644 --- a/admin/payment_terms.php +++ b/admin/payment_terms.php @@ -103,7 +103,7 @@ if ($Mode == 'Delete') { // PREVENT DELETES IF DEPENDENT RECORDS IN debtors_master - $sql= "SELECT COUNT(*) FROM ".TB_PREF."debtors_master WHERE payment_terms = '$selected_id'"; + $sql= "SELECT COUNT(*) FROM ".TB_PREF."debtors_master WHERE payment_terms = ".db_escape($selected_id); $result = db_query($sql,"check failed"); $myrow = db_fetch_row($result); if ($myrow[0] > 0) @@ -112,7 +112,7 @@ if ($Mode == 'Delete') } else { - $sql= "SELECT COUNT(*) FROM ".TB_PREF."suppliers WHERE payment_terms = '$selected_id'"; + $sql= "SELECT COUNT(*) FROM ".TB_PREF."suppliers WHERE payment_terms = ".db_escape($selected_id); $result = db_query($sql,"check failed"); $myrow = db_fetch_row($result); if ($myrow[0] > 0) @@ -123,7 +123,7 @@ if ($Mode == 'Delete') { //only delete if used in neither customer or supplier accounts - $sql="DELETE FROM ".TB_PREF."payment_terms WHERE terms_indicator='$selected_id'"; + $sql="DELETE FROM ".TB_PREF."payment_terms WHERE terms_indicator=".db_escape($selected_id); db_query($sql,"could not delete a payment terms"); display_notification(_('Selected payment terms have been deleted')); } @@ -195,7 +195,7 @@ if ($selected_id != -1) if ($Mode == 'Edit') { //editing an existing payment terms $sql = "SELECT * FROM ".TB_PREF."payment_terms - WHERE terms_indicator='$selected_id'"; + WHERE terms_indicator=".db_escape($selected_id); $result = db_query($sql,"could not get payment term"); $myrow = db_fetch($result); diff --git a/admin/print_profiles.php b/admin/print_profiles.php index 8052a38a..2cdb011b 100644 --- a/admin/print_profiles.php +++ b/admin/print_profiles.php @@ -77,7 +77,7 @@ function check_delete($name) { // check if selected profile is used by any user if ($name=='') return 0; // cannot delete system default profile - $sql = "SELECT * FROM ".TB_PREF."users WHERE print_profile='$name'"; + $sql = "SELECT * FROM ".TB_PREF."users WHERE print_profile=".db_escape($name); $res = db_query($sql,'cannot check printing profile usage'); return db_num_rows($res); } diff --git a/admin/printers.php b/admin/printers.php index 227c1d65..9958d8a9 100644 --- a/admin/printers.php +++ b/admin/printers.php @@ -59,7 +59,7 @@ if ($Mode == 'Delete') { // PREVENT DELETES IF DEPENDENT RECORDS IN print_profiles - $sql= "SELECT COUNT(*) FROM ".TB_PREF."print_profiles WHERE printer = '$selected_id'"; + $sql= "SELECT COUNT(*) FROM ".TB_PREF."print_profiles WHERE printer = ".db_escape($selected_id); $result = db_query($sql,"check printers relations failed"); $myrow = db_fetch_row($result); if ($myrow[0] > 0) @@ -68,7 +68,7 @@ if ($Mode == 'Delete') } else { - $sql="DELETE FROM ".TB_PREF."printers WHERE id='$selected_id'"; + $sql="DELETE FROM ".TB_PREF."printers WHERE id=".db_escape($selected_id); db_query($sql,"could not delete printer definition"); display_notification(_('Selected printer definition has been deleted')); } diff --git a/admin/shipping_companies.php b/admin/shipping_companies.php index 17726cc3..646e7b11 100644 --- a/admin/shipping_companies.php +++ b/admin/shipping_companies.php @@ -53,7 +53,7 @@ if ($Mode=='UPDATE_ITEM' && can_process()) contact =" . db_escape($_POST['contact']). " , phone =" . db_escape($_POST['phone']). " , address =" . db_escape($_POST['address']). " - WHERE shipper_id = $selected_id"; + WHERE shipper_id = ".db_escape($selected_id); db_query($sql,"The shipping company could not be updated"); display_notification(_('Selected shipping company has been updated')); @@ -66,7 +66,7 @@ if ($Mode == 'Delete') { // PREVENT DELETES IF DEPENDENT RECORDS IN 'sales_orders' - $sql= "SELECT COUNT(*) FROM ".TB_PREF."sales_orders WHERE ship_via='$selected_id'"; + $sql= "SELECT COUNT(*) FROM ".TB_PREF."sales_orders WHERE ship_via=".db_escape($selected_id); $result = db_query($sql,"check failed"); $myrow = db_fetch_row($result); if ($myrow[0] > 0) @@ -78,7 +78,7 @@ if ($Mode == 'Delete') { // PREVENT DELETES IF DEPENDENT RECORDS IN 'debtor_trans' - $sql= "SELECT COUNT(*) FROM ".TB_PREF."debtor_trans WHERE ship_via='$selected_id'"; + $sql= "SELECT COUNT(*) FROM ".TB_PREF."debtor_trans WHERE ship_via=".db_escape($selected_id); $result = db_query($sql,"check failed"); $myrow = db_fetch_row($result); if ($myrow[0] > 0) @@ -88,7 +88,7 @@ if ($Mode == 'Delete') } else { - $sql="DELETE FROM ".TB_PREF."shippers WHERE shipper_id=$selected_id"; + $sql="DELETE FROM ".TB_PREF."shippers WHERE shipper_id=".db_escape($selected_id); db_query($sql,"could not delete shipper"); display_notification(_('Selected shipping company has been deleted')); } @@ -140,7 +140,7 @@ if ($selected_id != -1) if ($Mode == 'Edit') { //editing an existing Shipper - $sql = "SELECT * FROM ".TB_PREF."shippers WHERE shipper_id=$selected_id"; + $sql = "SELECT * FROM ".TB_PREF."shippers WHERE shipper_id=".db_escape($selected_id); $result = db_query($sql, "could not get shipper"); $myrow = db_fetch($result); diff --git a/admin/view_print_transaction.php b/admin/view_print_transaction.php index 94c22416..99f2be1d 100644 --- a/admin/view_print_transaction.php +++ b/admin/view_print_transaction.php @@ -109,11 +109,11 @@ function handle_search() $sql .= " ,$trans_ref "; $sql .= ", ".$_POST['filterType']." as type FROM $table_name - WHERE $trans_no_name >= " . $_POST['FromTransNo']. " - AND $trans_no_name <= " . $_POST['ToTransNo']; + WHERE $trans_no_name >= ".db_escape($_POST['FromTransNo']). " + AND $trans_no_name <= ".db_escape($_POST['ToTransNo']); if ($type_name != null) - $sql .= " AND `$type_name` = " . $_POST['filterType']; + $sql .= " AND `$type_name` = ".db_escape($_POST['filterType']); $sql .= " ORDER BY $trans_no_name"; diff --git a/dimensions/includes/dimensions_db.inc b/dimensions/includes/dimensions_db.inc index 2dfe6838..9b08207e 100644 --- a/dimensions/includes/dimensions_db.inc +++ b/dimensions/includes/dimensions_db.inc @@ -17,7 +17,8 @@ function add_dimension($reference, $name, $type_, $date_, $due_date, $memo_) $duedate = date2sql($due_date); $sql = "INSERT INTO ".TB_PREF."dimensions (reference, name, type_, date_, due_date) - VALUES (".db_escape($reference).", ".db_escape($name).", $type_, '$date', '$duedate')"; + VALUES (".db_escape($reference).", ".db_escape($name).", ".db_escape($type_) + .", ".db_escape($date_).", ".db_escape($due_date).")"; db_query($sql, "could not add dimension"); $id = db_insert_id(); @@ -39,10 +40,10 @@ function update_dimension($id, $name, $type_, $date_, $due_date, $memo_) $duedate = date2sql($due_date); $sql = "UPDATE ".TB_PREF."dimensions SET name=".db_escape($name).", - type_ = $type_, - date_='$date', - due_date='$duedate' - WHERE id = $id"; + type_ = ".db_escape($type_).", + date_=".db_escape($date_).", + due_date=".db_escape($due_date)." + WHERE id = ".db_escape($id); db_query($sql, "could not update dimension"); @@ -58,7 +59,7 @@ function delete_dimension($id) begin_transaction(); // delete the actual dimension - $sql="DELETE FROM ".TB_PREF."dimensions WHERE id=$id"; + $sql="DELETE FROM ".TB_PREF."dimensions WHERE id=".db_escape($id); db_query($sql,"The dimension could not be deleted"); delete_comments(systypes::dimension(), $id); @@ -70,7 +71,7 @@ function delete_dimension($id) function get_dimension($id, $allow_null=false) { - $sql = "SELECT * FROM ".TB_PREF."dimensions WHERE id=$id"; + $sql = "SELECT * FROM ".TB_PREF."dimensions WHERE id=".db_escape($id); $result = db_query($sql, "The dimension could not be retrieved"); @@ -120,7 +121,7 @@ function dimension_has_deposits($id) function dimension_has_payments($id) { - $sql = "SELECT SUM(amount) FROM ".TB_PREF."gl_trans WHERE dimension_id = $id"; + $sql = "SELECT SUM(amount) FROM ".TB_PREF."gl_trans WHERE dimension_id = ".db_escape($id); $res = db_query($sql, "Transactions could not be calculated"); $row = db_fetch_row($res); return ($row[0] != 0.0); @@ -136,7 +137,7 @@ function dimension_is_closed($id) function close_dimension($id) { - $sql = "UPDATE ".TB_PREF."dimensions SET closed='1' WHERE id = $id"; + $sql = "UPDATE ".TB_PREF."dimensions SET closed='1' WHERE id = ".db_escape($id); db_query($sql, "could not close dimension"); } diff --git a/dimensions/inquiry/search_dimensions.php b/dimensions/inquiry/search_dimensions.php index 1219b807..750eb458 100644 --- a/dimensions/inquiry/search_dimensions.php +++ b/dimensions/inquiry/search_dimensions.php @@ -139,7 +139,7 @@ $sql = "SELECT dim.id, if (isset($_POST['OrderNumber']) && $_POST['OrderNumber'] != "") { - $sql .= " AND reference LIKE '%". $_POST['OrderNumber'] . "%'"; + $sql .= " AND reference LIKE ".db_escape("%". $_POST['OrderNumber'] . "%"); } else { if ($dim == 1) @@ -152,14 +152,14 @@ if (isset($_POST['OrderNumber']) && $_POST['OrderNumber'] != "") if (isset($_POST['type_']) && ($_POST['type_'] > 0)) { - $sql .= " AND type_=" . $_POST['type_']; + $sql .= " AND type_=".db_escape($_POST['type_']); } if (isset($_POST['OverdueOnly'])) { $today = date2sql(Today()); - $sql .= " AND due_date < '$today' "; + $sql .= " AND due_date < '$today'"; } $sql .= " AND date_ >= '" . date2sql($_POST['FromDate']) . "' diff --git a/gl/bank_account_reconcile.php b/gl/bank_account_reconcile.php index a195292b..bdd7c88e 100644 --- a/gl/bank_account_reconcile.php +++ b/gl/bank_account_reconcile.php @@ -115,14 +115,15 @@ function change_tpl_flag($reconcile_id) $_POST['bank_date'] = date2sql(get_post('reconcile_date')); $reconcile_value = check_value("rec_".$reconcile_id) ? ("'".$_POST['bank_date'] ."'") : 'NULL'; - $sql = "UPDATE ".TB_PREF."bank_trans SET reconciled=$reconcile_value WHERE id=$reconcile_id"; + $sql = "UPDATE ".TB_PREF."bank_trans SET reconciled=".db_escape($reconcile_value) + ." WHERE id=".db_escape($reconcile_id); db_query($sql, "Can't change reconciliation status"); // save last reconcilation status (date, end balance) $sql2="UPDATE ".TB_PREF."bank_accounts SET last_reconciled_date='" .date2sql($_POST["reconcile_date"])."', ending_reconcile_balance=".input_num("end_balance") - ." WHERE id=".$_POST["bank_account"]; + ." WHERE id=".db_escape($_POST["bank_account"]); $result = db_query($sql2,"Error updating reconciliation information"); $Ajax->activate('reconciled'); @@ -180,7 +181,7 @@ $sql = "SELECT MAX(reconciled) as last_date, SUM(IF(reconciled<'$date', amount, 0)) as beg_balance, SUM(amount) as total FROM ".TB_PREF."bank_trans trans - WHERE bank_act=".$_POST['bank_account']; + WHERE bank_act=".db_escape($_POST['bank_account']); // ." AND trans.reconciled IS NOT NULL"; $result = db_query($sql,"Cannot retrieve reconciliation data"); @@ -195,8 +196,8 @@ if ($row = db_fetch($result)) { if (get_post('bank_date')) { // if it is the last updated bank statement retrieve ending balance $sql = "SELECT ending_reconcile_balance - FROM ".TB_PREF."bank_accounts WHERE id=".$_POST['bank_account'] - . " AND last_reconciled_date='".$_POST['bank_date']."'"; + FROM ".TB_PREF."bank_accounts WHERE id=".db_escape($_POST['bank_account']) + . " AND last_reconciled_date=".db_escape($_POST['bank_date']); $result = db_query($sql,"Cannot retrieve last reconciliation"); $row = db_fetch($result); if($row) { @@ -242,7 +243,7 @@ if (!isset($_POST['bank_account'])) $sql = "SELECT type, trans_no, ref, trans_date, amount, person_id, person_type_id, reconciled, id FROM ".TB_PREF."bank_trans - WHERE ".TB_PREF."bank_trans.bank_act = '" . $_POST['bank_account'] . "' + WHERE ".TB_PREF."bank_trans.bank_act = ".db_escape($_POST['bank_account']) . " AND (reconciled IS NULL OR reconciled='". $date ."') ORDER BY trans_date,".TB_PREF."bank_trans.id"; // or ORDER BY reconciled desc, trans_date,".TB_PREF."bank_trans.id"; diff --git a/gl/gl_budget.php b/gl/gl_budget.php index 6c4db527..7945f171 100644 --- a/gl/gl_budget.php +++ b/gl/gl_budget.php @@ -27,8 +27,9 @@ check_db_has_gl_account_groups(_("There are no account groups defined. Please de function exists_gl_budget($date_, $account, $dimension, $dimension2) { - $sql = "SELECT account FROM ".TB_PREF."budget_trans WHERE account='$account' AND tran_date='$date_' AND - dimension_id=$dimension AND dimension2_id=$dimension2"; + $sql = "SELECT account FROM ".TB_PREF."budget_trans WHERE account=".db_escape($account) + ." AND tran_date='$date_' AND + dimension_id=".db_escape($dimension)." AND dimension2_id=".db_escape($dimension2); $result = db_query($sql, "Cannot retreive a gl transaction"); return (db_num_rows($result) > 0); @@ -39,12 +40,16 @@ function add_update_gl_budget_trans($date_, $account, $dimension, $dimension2, $ $date = date2sql($date_); if (exists_gl_budget($date, $account, $dimension, $dimension2)) - $sql = "UPDATE ".TB_PREF."budget_trans SET amount=$amount WHERE account='$account' AND - dimension_id=$dimension AND dimension2_id=$dimension2 AND tran_date='$date'"; + $sql = "UPDATE ".TB_PREF."budget_trans SET amount=".db_escape($amount) + ." WHERE account=".db_escape($account) + ." AND dimension_id=".db_escape($dimension) + ." AND dimension2_id=".db_escape($dimension2) + ." AND tran_date='$date'"; else $sql = "INSERT INTO ".TB_PREF."budget_trans (tran_date, account, dimension_id, dimension2_id, amount, memo_) VALUES ('$date', - '$account', $dimension, $dimension2, $amount, '')"; + ".db_escape($account).", ".db_escape($dimension).", " + .db_escape($dimension2).", ".db_escape($amount).", '')"; db_query($sql, "The GL budget transaction could not be saved"); } @@ -53,8 +58,10 @@ function delete_gl_budget_trans($date_, $account, $dimension, $dimension2) { $date = date2sql($date_); - $sql = "DELETE FROM ".TB_PREF."budget_trans WHERE account='$account' AND - dimension_id=$dimension AND dimension2_id=$dimension2 AND tran_date='$date'"; + $sql = "DELETE FROM ".TB_PREF."budget_trans WHERE account=".db_escape($account) + ." AND dimension_id=".db_escape($dimension) + ." AND dimension2_id=".db_escape($dimension2) + ." AND tran_date='$date'"; db_query($sql, "The GL budget transaction could not be deleted"); } @@ -65,8 +72,10 @@ function get_only_budget_trans_from_to($from_date, $to_date, $account, $dimensio $to = date2sql($to_date); $sql = "SELECT SUM(amount) FROM ".TB_PREF."budget_trans - WHERE account='$account' AND tran_date >= '$from' AND tran_date <= '$to' - AND dimension_id = $dimension AND dimension2_id = $dimension2"; + WHERE account=".db_escape($account) + ." AND tran_date >= '$from' AND tran_date <= '$to' + AND dimension_id = ".db_escape($dimension) + ." AND dimension2_id = ".db_escape($dimension2); $result = db_query($sql,"No budget accounts were returned"); $row = db_fetch_row($result); @@ -142,7 +151,7 @@ if (db_has_gl_accounts()) table_header($th); $year = $_POST['fyear']; if (get_post('update') == '') { - $sql = "SELECT * FROM ".TB_PREF."fiscal_year WHERE id=$year"; + $sql = "SELECT * FROM ".TB_PREF."fiscal_year WHERE id=".db_escape($year); $result = db_query($sql, "could not get current fiscal year"); diff --git a/gl/includes/db/gl_db_account_types.inc b/gl/includes/db/gl_db_account_types.inc index ae6011cd..edf4334f 100644 --- a/gl/includes/db/gl_db_account_types.inc +++ b/gl/includes/db/gl_db_account_types.inc @@ -12,7 +12,7 @@ function add_account_type($id, $name, $class_id, $parent) { $sql = "INSERT INTO ".TB_PREF."chart_types (id, name, class_id, parent) - VALUES ($id, ".db_escape($name).", $class_id, $parent)"; + VALUES ($id, ".db_escape($name).", ".db_escape($class_id).", ".db_escape($parent).")"; db_query($sql, "could not add account type"); } @@ -20,7 +20,8 @@ function add_account_type($id, $name, $class_id, $parent) function update_account_type($id, $name, $class_id, $parent) { $sql = "UPDATE ".TB_PREF."chart_types SET name=".db_escape($name).", - class_id=$class_id, parent=$parent WHERE id = $id"; + class_id=".db_escape($class_id).", parent=".db_escape($parent) + ." WHERE id = ".db_escape($id); db_query($sql, "could not update account type"); } @@ -34,7 +35,7 @@ function get_account_types() function get_account_type($id) { - $sql = "SELECT * FROM ".TB_PREF."chart_types WHERE id = $id"; + $sql = "SELECT * FROM ".TB_PREF."chart_types WHERE id = ".db_escape($id); $result = db_query($sql, "could not get account type"); @@ -43,7 +44,7 @@ function get_account_type($id) function get_account_type_name($id) { - $sql = "SELECT name FROM ".TB_PREF."chart_types WHERE id = $id"; + $sql = "SELECT name FROM ".TB_PREF."chart_types WHERE id = ".db_escape($id); $result = db_query($sql, "could not get account type"); @@ -53,7 +54,7 @@ function get_account_type_name($id) function delete_account_type($id) { - $sql = "DELETE FROM ".TB_PREF."chart_types WHERE id = $id"; + $sql = "DELETE FROM ".TB_PREF."chart_types WHERE id = ".db_escape($id); db_query($sql, "could not delete account type"); } @@ -61,7 +62,7 @@ function delete_account_type($id) function add_account_class($id, $name, $balance) { $sql = "INSERT INTO ".TB_PREF."chart_class (cid, class_name, balance_sheet) - VALUES ($id, ".db_escape($name).", $balance)"; + VALUES (".db_escape($id).", ".db_escape($name).", ".db_escape($balance).")"; db_query($sql, "could not add account type"); } @@ -69,7 +70,7 @@ function add_account_class($id, $name, $balance) function update_account_class($id, $name, $balance) { $sql = "UPDATE ".TB_PREF."chart_class SET class_name=".db_escape($name).", - balance_sheet=$balance WHERE cid = $id"; + balance_sheet=".db_escape($balance)." WHERE cid = ".db_escape($id); db_query($sql, "could not update account type"); } @@ -83,7 +84,7 @@ function get_account_classes() function get_account_class($id) { - $sql = "SELECT * FROM ".TB_PREF."chart_class WHERE cid = $id"; + $sql = "SELECT * FROM ".TB_PREF."chart_class WHERE cid = ".db_escape($id); $result = db_query($sql, "could not get account type"); @@ -92,7 +93,7 @@ function get_account_class($id) function get_account_class_name($id) { - $sql = "SELECT class_name FROM ".TB_PREF."chart_class WHERE cid = $id"; + $sql = "SELECT class_name FROM ".TB_PREF."chart_class WHERE cid =".db_escape($id); $result = db_query($sql, "could not get account type"); @@ -102,7 +103,7 @@ function get_account_class_name($id) function delete_account_class($id) { - $sql = "DELETE FROM ".TB_PREF."chart_class WHERE cid = $id"; + $sql = "DELETE FROM ".TB_PREF."chart_class WHERE cid = ".db_escape($id); db_query($sql, "could not delete account type"); } diff --git a/gl/includes/db/gl_db_accounts.inc b/gl/includes/db/gl_db_accounts.inc index 45fb8301..7b1cb63b 100644 --- a/gl/includes/db/gl_db_accounts.inc +++ b/gl/includes/db/gl_db_accounts.inc @@ -13,7 +13,8 @@ function add_gl_account($account_code, $account_name, $account_type, $account_co { $account_name = db_escape($account_name); $sql = "INSERT INTO ".TB_PREF."chart_master (account_code, account_code2, account_name, account_type) - VALUES (".db_escape($account_code).", ".db_escape($account_code2).", $account_name, $account_type)"; + VALUES (".db_escape($account_code).", ".db_escape($account_code2).", " + .db_escape($account_name).", ".db_escape($account_type).")"; db_query($sql, "could not add gl account"); } @@ -21,16 +22,16 @@ function add_gl_account($account_code, $account_name, $account_type, $account_co function update_gl_account($account_code, $account_name, $account_type, $account_code2) { $account_name = db_escape($account_name); - $sql = "UPDATE ".TB_PREF."chart_master SET account_name=$account_name, - account_type=$account_type, account_code2=".db_escape($account_code2) - ." WHERE account_code = '$account_code'"; + $sql = "UPDATE ".TB_PREF."chart_master SET account_name=".db_escape($account_name) + .",account_type=".db_escape($account_type).", account_code2=".db_escape($account_code2) + ." WHERE account_code = ".db_escape($account_code); db_query($sql, "could not update gl account"); } function delete_gl_account($code) { - $sql = "DELETE FROM ".TB_PREF."chart_master WHERE account_code='$code'"; + $sql = "DELETE FROM ".TB_PREF."chart_master WHERE account_code=".db_escape($code); db_query($sql, "could not delete gl account"); } @@ -41,9 +42,9 @@ function get_gl_accounts($from=null, $to=null) FROM ".TB_PREF."chart_master,".TB_PREF."chart_types WHERE ".TB_PREF."chart_master.account_type=".TB_PREF."chart_types.id"; if ($from != null) - $sql .= " AND ".TB_PREF."chart_master.account_code >= '$from'"; + $sql .= " AND ".TB_PREF."chart_master.account_code >= ".db_escape($from); if ($to != null) - $sql .= " AND ".TB_PREF."chart_master.account_code <= '$to'"; + $sql .= " AND ".TB_PREF."chart_master.account_code <= ".db_escape($to); $sql .= " ORDER BY account_code"; return db_query($sql, "could not get gl accounts"); @@ -71,7 +72,7 @@ function get_gl_accounts_all($balance=-1) function get_gl_account($code) { - $sql = "SELECT * FROM ".TB_PREF."chart_master WHERE account_code='$code'"; + $sql = "SELECT * FROM ".TB_PREF."chart_master WHERE account_code=".db_escape($code); $result = db_query($sql, "could not get gl account"); return db_fetch($result); @@ -79,10 +80,11 @@ function get_gl_account($code) function is_account_balancesheet($code) { - $sql = "SELECT ".TB_PREF."chart_class.balance_sheet FROM ".TB_PREF."chart_class, ".TB_PREF."chart_types, ".TB_PREF."chart_master + $sql = "SELECT ".TB_PREF."chart_class.balance_sheet FROM ".TB_PREF."chart_class, " + .TB_PREF."chart_types, ".TB_PREF."chart_master WHERE ".TB_PREF."chart_master.account_type=".TB_PREF."chart_types.id AND ".TB_PREF."chart_types.class_id=".TB_PREF."chart_class.cid - AND ".TB_PREF."chart_master.account_code='$code'"; + AND ".TB_PREF."chart_master.account_code=".db_escape($code); $result = db_query($sql,"could not retreive the account class for $code"); $row = db_fetch_row($result); @@ -91,7 +93,7 @@ function is_account_balancesheet($code) function get_gl_account_name($code) { - $sql = "SELECT account_name from ".TB_PREF."chart_master WHERE account_code='$code'"; + $sql = "SELECT account_name from ".TB_PREF."chart_master WHERE account_code=".db_escape($code); $result = db_query($sql,"could not retreive the account name for $code"); diff --git a/gl/includes/db/gl_db_bank_accounts.inc b/gl/includes/db/gl_db_bank_accounts.inc index 53f3facf..5e6fadfb 100644 --- a/gl/includes/db/gl_db_bank_accounts.inc +++ b/gl/includes/db/gl_db_bank_accounts.inc @@ -15,8 +15,10 @@ function add_bank_account($account_code, $account_type, $bank_account_name, $ban $bank_address, $bank_curr_code) { $sql = "INSERT INTO ".TB_PREF."bank_accounts (account_code, account_type, bank_account_name, bank_name, bank_account_number, bank_address, bank_curr_code) - VALUES (".db_escape($account_code).", $account_type, ".db_escape($bank_account_name).", ".db_escape($bank_name).", ".db_escape($bank_account_number).", - ".db_escape($bank_address).", '$bank_curr_code')"; + VALUES (".db_escape($account_code).", ".db_escape($account_type).", " + .db_escape($bank_account_name).", ".db_escape($bank_name).", " + .db_escape($bank_account_number).",".db_escape($bank_address). + ", ".db_escape($bank_curr_code).")"; db_query($sql, "could not add a bank account for $account_code"); } @@ -26,11 +28,11 @@ function add_bank_account($account_code, $account_type, $bank_account_name, $ban function update_bank_account($id, $account_code, $account_type, $bank_account_name, $bank_name, $bank_account_number, $bank_address, $bank_curr_code) { - $sql = "UPDATE ".TB_PREF."bank_accounts SET account_type = $account_type, + $sql = "UPDATE ".TB_PREF."bank_accounts SET account_type = ".db_escape($account_type).", account_code=".db_escape($account_code).", bank_account_name=".db_escape($bank_account_name).", bank_name=".db_escape($bank_name).", - bank_account_number=".db_escape($bank_account_number).", bank_curr_code='$bank_curr_code', - bank_address=".db_escape($bank_address)." WHERE id = '$id'"; + bank_account_number=".db_escape($bank_account_number).", bank_curr_code=".db_escape($bank_curr_code).", + bank_address=".db_escape($bank_address)." WHERE id = ".db_escape($id); db_query($sql, "could not update bank account for $account_code"); } @@ -39,7 +41,7 @@ function update_bank_account($id, $account_code, $account_type, $bank_account_na function delete_bank_account($id) { - $sql = "DELETE FROM ".TB_PREF."bank_accounts WHERE id='$id'"; + $sql = "DELETE FROM ".TB_PREF."bank_accounts WHERE id=".db_escape($id); db_query($sql,"could not delete bank account for $id"); } @@ -49,7 +51,7 @@ function delete_bank_account($id) function get_bank_account($id) { - $sql = "SELECT * FROM ".TB_PREF."bank_accounts WHERE id='$id'"; + $sql = "SELECT * FROM ".TB_PREF."bank_accounts WHERE id=".db_escape($id); $result = db_query($sql, "could not retreive bank account for $id"); @@ -59,7 +61,7 @@ function get_bank_account($id) //--------------------------------------------------------------------------------------------- function get_bank_gl_account($id) { - $sql = "SELECT account_code FROM ".TB_PREF."bank_accounts WHERE id='$id'"; + $sql = "SELECT account_code FROM ".TB_PREF."bank_accounts WHERE id=".db_escape($id); $result = db_query($sql, "could not retreive bank account for $id"); @@ -73,7 +75,7 @@ function get_bank_gl_account($id) function add_quick_entry($description, $type, $base_amount, $base_desc) { $sql = "INSERT INTO ".TB_PREF."quick_entries (description, type, base_amount, base_desc) - VALUES (".db_escape($description).", $type, " + VALUES (".db_escape($description).", ".db_escape($type).", " .db_escape($base_amount).", ".db_escape($base_desc).")"; db_query($sql, "could not insert quick entry for $description"); @@ -84,8 +86,9 @@ function add_quick_entry($description, $type, $base_amount, $base_desc) function update_quick_entry($selected_id, $description, $type, $base_amount, $base_desc) { $sql = "UPDATE ".TB_PREF."quick_entries SET description = ".db_escape($description).", - type=$type, base_amount=".db_escape($base_amount).", base_desc=".db_escape($base_desc)." - WHERE id = $selected_id"; + type=".db_escape($type).", base_amount=".db_escape($base_amount) + .", base_desc=".db_escape($base_desc)." + WHERE id = ".db_escape($selected_id); db_query($sql, "could not update quick entry for $selected_id"); } @@ -94,7 +97,7 @@ function update_quick_entry($selected_id, $description, $type, $base_amount, $ba function delete_quick_entry($selected_id) { - $sql = "DELETE FROM ".TB_PREF."quick_entries WHERE id=$selected_id"; + $sql = "DELETE FROM ".TB_PREF."quick_entries WHERE id=".db_escape($selected_id); db_query($sql,"could not delete quick entry $selected_id"); } @@ -107,7 +110,7 @@ function add_quick_entry_line($qid, $action, $dest_id, $amount, $dim, $dim2) (qid, action, dest_id, amount, dimension_id, dimension2_id) VALUES ($qid, ".db_escape($action).",".db_escape($dest_id).", - $amount, $dim, $dim2)"; + ".db_escape($amount).", ".db_escape($dim).", ".db_escape($dim2).")"; db_query($sql, "could not insert quick entry line for $qid"); } @@ -116,9 +119,11 @@ function add_quick_entry_line($qid, $action, $dest_id, $amount, $dim, $dim2) function update_quick_entry_line($selected_id, $qid, $action, $dest_id, $amount, $dim, $dim2) { - $sql = "UPDATE ".TB_PREF."quick_entry_lines SET qid = $qid, action=".db_escape($action).", - dest_id=".db_escape($dest_id).", amount=$amount, dimension_id=$dim, dimension2_id=$dim2 - WHERE id = $selected_id"; + $sql = "UPDATE ".TB_PREF."quick_entry_lines SET qid = ".db_escape($qid) + .", action=".db_escape($action).", + dest_id=".db_escape($dest_id).", amount=".db_escape($amount) + .", dimension_id=".db_escape($dim).", dimension2_id=".db_escape($dim2)." + WHERE id = ".db_escape($selected_id); db_query($sql, "could not update quick entry line for $selected_id"); } @@ -127,7 +132,7 @@ function update_quick_entry_line($selected_id, $qid, $action, $dest_id, $amount, function delete_quick_entry_line($selected_id) { - $sql = "DELETE FROM ".TB_PREF."quick_entry_lines WHERE id=$selected_id"; + $sql = "DELETE FROM ".TB_PREF."quick_entry_lines WHERE id=".db_escape($selected_id); db_query($sql,"could not delete quick entry line $selected_id"); } @@ -138,7 +143,7 @@ function has_quick_entries($type=null) { $sql = "SELECT id FROM ".TB_PREF."quick_entries"; if ($type != null) - $sql .= " WHERE type=$type"; + $sql .= " WHERE type=".db_escape($type); $result = db_query($sql, "could not retreive quick entries"); return db_num_rows($result) > 0; @@ -148,7 +153,7 @@ function get_quick_entries($type = null) { $sql = "SELECT * FROM ".TB_PREF."quick_entries"; if ($type != null) - $sql .= " WHERE type=$type"; + $sql .= " WHERE type=".db_escape($type); $sql .= " ORDER BY description"; return db_query($sql, "could not retreive quick entries"); @@ -156,7 +161,7 @@ function get_quick_entries($type = null) function get_quick_entry($selected_id) { - $sql = "SELECT * FROM ".TB_PREF."quick_entries WHERE id=$selected_id"; + $sql = "SELECT * FROM ".TB_PREF."quick_entries WHERE id=".db_escape($selected_id); $result = db_query($sql, "could not retreive quick entry $selected_id"); @@ -173,15 +178,14 @@ function get_quick_entry_lines($qid) LEFT JOIN ".TB_PREF."tax_types ON ".TB_PREF."quick_entry_lines.dest_id = ".TB_PREF."tax_types.id WHERE - qid=$qid - ORDER by id"; + qid=".db_escape($qid)." ORDER by id"; return db_query($sql, "could not retreive quick entries"); } function has_quick_entry_lines($qid) { - $sql = "SELECT id FROM ".TB_PREF."quick_entry_lines WHERE qid=$qid"; + $sql = "SELECT id FROM ".TB_PREF."quick_entry_lines WHERE qid=".db_escape($qid); $result = db_query($sql, "could not retreive quick entries"); return db_num_rows($result) > 0; @@ -191,7 +195,7 @@ function has_quick_entry_lines($qid) function get_quick_entry_line($selected_id) { - $sql = "SELECT * FROM ".TB_PREF."quick_entry_lines WHERE id=$selected_id"; + $sql = "SELECT * FROM ".TB_PREF."quick_entry_lines WHERE id=".db_escape($selected_id); $result = db_query($sql, "could not retreive quick entry for $selected_id"); diff --git a/gl/includes/db/gl_db_bank_trans.inc b/gl/includes/db/gl_db_bank_trans.inc index c9f550c8..ad369861 100644 --- a/gl/includes/db/gl_db_bank_trans.inc +++ b/gl/includes/db/gl_db_bank_trans.inc @@ -41,7 +41,8 @@ function add_bank_trans($type, $trans_no, $bank_act, $ref, $date_, trans_date, amount, person_type_id, person_id) "; $sql .= "VALUES ($type, $trans_no, '$bank_act', ".db_escape($ref).", '$sqlDate', - $amount_bank, $person_type_id, ". db_escape($person_id).")"; + ".db_escape($amount_bank).", ".db_escape($person_type_id) + .", ". db_escape($person_id).")"; if ($err_msg == "") $err_msg = "The bank transaction could not be inserted"; @@ -53,8 +54,8 @@ function add_bank_trans($type, $trans_no, $bank_act, $ref, $date_, function exists_bank_trans($type, $type_no) { - $sql = "SELECT trans_no FROM ".TB_PREF."bank_trans WHERE type=$type - AND trans_no=$type_no"; + $sql = "SELECT trans_no FROM ".TB_PREF."bank_trans WHERE type=".db_escape($type) + ." AND trans_no=".db_escape($type_no); $result = db_query($sql, "Cannot retreive a bank transaction"); return (db_num_rows($result) > 0); @@ -68,13 +69,13 @@ function get_bank_trans($type, $trans_no=null, $person_type_id=null, $person_id= FROM ".TB_PREF."bank_trans, ".TB_PREF."bank_accounts WHERE ".TB_PREF."bank_accounts.id=".TB_PREF."bank_trans.bank_act "; if ($type != null) - $sql .= " AND type=$type "; + $sql .= " AND type=".db_escape($type); if ($trans_no != null) - $sql .= " AND ".TB_PREF."bank_trans.trans_no = $trans_no "; + $sql .= " AND ".TB_PREF."bank_trans.trans_no = ".db_escape($trans_no); if ($person_type_id != null) - $sql .= " AND ".TB_PREF."bank_trans.person_type_id = $person_type_id "; + $sql .= " AND ".TB_PREF."bank_trans.person_type_id = ".db_escape($person_type_id); if ($person_id != null) - $sql .= " AND ".TB_PREF."bank_trans.person_id = '$person_id'"; + $sql .= " AND ".TB_PREF."bank_trans.person_id = ".db_escape($person_id); $sql .= " ORDER BY trans_date, ".TB_PREF."bank_trans.id"; return db_query($sql, "query for bank transaction"); @@ -84,7 +85,9 @@ function get_bank_trans($type, $trans_no=null, $person_type_id=null, $person_id= function get_gl_trans_value($account, $type, $trans_no) { - $sql = "SELECT SUM(amount) FROM ".TB_PREF."gl_trans WHERE account='$account' AND type=$type AND type_no=$trans_no"; + $sql = "SELECT SUM(amount) FROM ".TB_PREF."gl_trans WHERE account=" + .db_escape($account)." AND type=".db_escape($type) + ." AND type_no=".db_escape($trans_no); $result = db_query($sql, "query for gl trans value"); @@ -100,7 +103,7 @@ function void_bank_trans($type, $type_no, $nested=false) begin_transaction(); $sql = "UPDATE ".TB_PREF."bank_trans SET amount=0 - WHERE type=$type AND trans_no=$type_no"; + WHERE type=".db_escape($type)." AND trans_no=".db_escape($type_no); $result = db_query($sql, "could not void bank transactions for type=$type and trans_no=$type_no"); diff --git a/gl/includes/db/gl_db_banking.inc b/gl/includes/db/gl_db_banking.inc index defbcd84..52869000 100644 --- a/gl/includes/db/gl_db_banking.inc +++ b/gl/includes/db/gl_db_banking.inc @@ -18,7 +18,7 @@ function add_exchange_variation($trans_type, $trans_no, $date_, $acc_id, $accoun $date_ = Today(); $rate = get_exchange_rate_from_home_currency($currency, $date_); $result = db_query("SELECT SUM(amount) FROM ".TB_PREF."bank_trans WHERE - bank_act='$acc_id' AND trans_date<='".date2sql($date_)."'", + bank_act=".db_escape($acc_id)." AND trans_date<='".date2sql($date_)."'", "Transactions for account $account could not be calculated"); $row = db_fetch_row($result); $foreign_amount = $row[0]; diff --git a/gl/includes/db/gl_db_currencies.inc b/gl/includes/db/gl_db_currencies.inc index 0c386f83..69b4e4a5 100644 --- a/gl/includes/db/gl_db_currencies.inc +++ b/gl/includes/db/gl_db_currencies.inc @@ -13,8 +13,10 @@ function update_currency($curr_abrev, $symbol, $currency, $country, $hundreds_name) { - $sql = "UPDATE ".TB_PREF."currencies SET currency=".db_escape($currency).", curr_symbol='$symbol', - country=".db_escape($country).", hundreds_name=".db_escape($hundreds_name)." WHERE curr_abrev = '$curr_abrev'"; + $sql = "UPDATE ".TB_PREF."currencies SET currency=" + .db_escape($currency).", curr_symbol=".db_escape($symbol).", + country=".db_escape($country).", hundreds_name=".db_escape($hundreds_name) + ." WHERE curr_abrev = ".db_escape($curr_abrev); db_query($sql, "could not update currency for $curr_abrev"); } @@ -24,7 +26,8 @@ function update_currency($curr_abrev, $symbol, $currency, $country, $hundreds_na function add_currency($curr_abrev, $symbol, $currency, $country, $hundreds_name) { $sql = "INSERT INTO ".TB_PREF."currencies (curr_abrev, curr_symbol, currency, country, hundreds_name) - VALUES (".db_escape($curr_abrev).", '$symbol', ".db_escape($currency).", ".db_escape($country).", ".db_escape($hundreds_name).")"; + VALUES (".db_escape($curr_abrev).", ".db_escape($symbol).", " + .db_escape($currency).", ".db_escape($country).", ".db_escape($hundreds_name).")"; db_query($sql, "could not add currency for $curr_abrev"); } @@ -33,7 +36,7 @@ function add_currency($curr_abrev, $symbol, $currency, $country, $hundreds_name) function delete_currency($curr_code) { - $sql="DELETE FROM ".TB_PREF."currencies WHERE curr_abrev='$curr_code'"; + $sql="DELETE FROM ".TB_PREF."currencies WHERE curr_abrev=".db_escape($curr_code); db_query($sql, "could not delete currency $curr_code"); $sql="DELETE FROM ".TB_PREF."exchange_rates WHERE curr_code='$curr_code'"; @@ -44,7 +47,7 @@ function delete_currency($curr_code) function get_currency($curr_code) { - $sql = "SELECT * FROM ".TB_PREF."currencies WHERE curr_abrev='$curr_code'"; + $sql = "SELECT * FROM ".TB_PREF."currencies WHERE curr_abrev=".db_escape($curr_code); $result = db_query($sql, "could not get currency $curr_code"); $row = db_fetch($result); diff --git a/gl/includes/db/gl_db_rates.inc b/gl/includes/db/gl_db_rates.inc index 0c9ae686..bd0775ea 100644 --- a/gl/includes/db/gl_db_rates.inc +++ b/gl/includes/db/gl_db_rates.inc @@ -12,7 +12,7 @@ //--------------------------------------------------------------------------------------------- function get_exchange_rate($rate_id) { - $sql = "SELECT * FROM ".TB_PREF."exchange_rates WHERE id=$rate_id"; + $sql = "SELECT * FROM ".TB_PREF."exchange_rates WHERE id=".db_escape($rate_id); $result = db_query($sql, "could not get exchange rate for $rate_id"); return db_fetch($result); @@ -22,8 +22,8 @@ function get_exchange_rate($rate_id) function get_date_exchange_rate($curr_code, $date_) { $date = date2sql($date_); - $sql = "SELECT rate_buy FROM ".TB_PREF."exchange_rates WHERE curr_code='$curr_code' - AND date_='$date'"; + $sql = "SELECT rate_buy FROM ".TB_PREF."exchange_rates WHERE curr_code=".db_escape($curr_code) + ." AND date_='$date'"; $result = db_query($sql, "could not get exchange rate for $curr_code - $date_"); if(db_num_rows($result) == 0) @@ -41,8 +41,8 @@ function update_exchange_rate($curr_code, $date_, $buy_rate, $sell_rate) $date = date2sql($date_); - $sql = "UPDATE ".TB_PREF."exchange_rates SET rate_buy=$buy_rate, rate_sell=$sell_rate - WHERE curr_code='$curr_code' AND date_='$date'"; + $sql = "UPDATE ".TB_PREF."exchange_rates SET rate_buy=$buy_rate, rate_sell=".db_escape($sell_rate) + ." WHERE curr_code=".db_escape($curr_code)." AND date_='$date'"; db_query($sql, "could not add exchange rate for $curr_code"); } @@ -57,7 +57,8 @@ function add_exchange_rate($curr_code, $date_, $buy_rate, $sell_rate) $date = date2sql($date_); $sql = "INSERT INTO ".TB_PREF."exchange_rates (curr_code, date_, rate_buy, rate_sell) - VALUES ('$curr_code', '$date', $buy_rate, $sell_rate)"; + VALUES (".db_escape($curr_code).", '$date', ".db_escape($buy_rate) + .", ".db_escape($sell_rate).")"; db_query($sql, "could not add exchange rate for $curr_code"); } @@ -65,7 +66,7 @@ function add_exchange_rate($curr_code, $date_, $buy_rate, $sell_rate) function delete_exchange_rate($rate_id) { - $sql = "DELETE FROM ".TB_PREF."exchange_rates WHERE id=$rate_id"; + $sql = "DELETE FROM ".TB_PREF."exchange_rates WHERE id=".db_escape($rate_id); db_query($sql, "could not delete exchange rate $rate_id"); } diff --git a/gl/includes/db/gl_db_trans.inc b/gl/includes/db/gl_db_trans.inc index 297eeb38..82dbc830 100644 --- a/gl/includes/db/gl_db_trans.inc +++ b/gl/includes/db/gl_db_trans.inc @@ -50,11 +50,13 @@ function add_gl_trans($type, $trans_id, $date_, $account, $dimension, $dimension $sql .= ") "; - $sql .= "VALUES ($type, $trans_id, '$date', - '$account', $dimension, $dimension2, ".db_escape($memo_).", $amount_in_home_currency"; + $sql .= "VALUES (".db_escape($type).", ".db_escape($trans_id).", '$date', + ".db_escape($account).", ".db_escape($dimension).", " + .db_escape($dimension2).", ".db_escape($memo_).", " + .db_escape($amount_in_home_currency); if ($person_type_id != null) - $sql .= ", $person_type_id, ". db_escape($person_id); + $sql .= ", ".db_escape($person_type_id).", ". db_escape($person_id); $sql .= ") "; @@ -100,24 +102,26 @@ function get_gl_transactions($from_date, $to_date, $trans_no=0, $from = date2sql($from_date); $to = date2sql($to_date); - $sql = "SELECT ".TB_PREF."gl_trans.*, ".TB_PREF."chart_master.account_name FROM ".TB_PREF."gl_trans, ".TB_PREF."chart_master + $sql = "SELECT ".TB_PREF."gl_trans.*, " + .TB_PREF."chart_master.account_name FROM ".TB_PREF."gl_trans, " + .TB_PREF."chart_master WHERE ".TB_PREF."chart_master.account_code=".TB_PREF."gl_trans.account AND tran_date >= '$from' AND tran_date <= '$to'"; if ($trans_no > 0) - $sql .= " AND ".TB_PREF."gl_trans.type_no LIKE '%$trans_no'"; + $sql .= " AND ".TB_PREF."gl_trans.type_no LIKE ".db_escape('%'.$trans_no); if ($account != null) - $sql .= " AND ".TB_PREF."gl_trans.account = '$account'"; + $sql .= " AND ".TB_PREF."gl_trans.account = ".db_escape($account); if ($dimension > 0) - $sql .= " AND ".TB_PREF."gl_trans.dimension_id = $dimension"; + $sql .= " AND ".TB_PREF."gl_trans.dimension_id = ".db_escape($dimension); if ($dimension2 > 0) - $sql .= " AND ".TB_PREF."gl_trans.dimension2_id = $dimension2"; + $sql .= " AND ".TB_PREF."gl_trans.dimension2_id = ".db_escape($dimension2); if ($filter_type != null AND is_numeric($filter_type)) - $sql .= " AND ".TB_PREF."gl_trans.type= $filter_type"; + $sql .= " AND ".TB_PREF."gl_trans.type= ".db_escape($filter_type); $sql .= " ORDER BY tran_date"; @@ -129,9 +133,12 @@ function get_gl_transactions($from_date, $to_date, $trans_no=0, function get_gl_trans($type, $trans_id) { - $sql = "SELECT ".TB_PREF."gl_trans.*, ".TB_PREF."chart_master.account_name FROM ".TB_PREF."gl_trans, ".TB_PREF."chart_master + $sql = "SELECT ".TB_PREF."gl_trans.*, " + .TB_PREF."chart_master.account_name FROM " + .TB_PREF."gl_trans, ".TB_PREF."chart_master WHERE ".TB_PREF."chart_master.account_code=".TB_PREF."gl_trans.account - AND ".TB_PREF."gl_trans.type=$type AND ".TB_PREF."gl_trans.type_no=$trans_id"; + AND ".TB_PREF."gl_trans.type=".db_escape($type) + ." AND ".TB_PREF."gl_trans.type_no=".db_escape($trans_id); return db_query($sql, "The gl transactions could not be retrieved"); } @@ -140,12 +147,14 @@ function get_gl_trans($type, $trans_id) function get_gl_wo_cost_trans($trans_id, $person_id=-1) { - $sql = "SELECT ".TB_PREF."gl_trans.*, ".TB_PREF."chart_master.account_name FROM ".TB_PREF."gl_trans, ".TB_PREF."chart_master + $sql = "SELECT ".TB_PREF."gl_trans.*, ".TB_PREF."chart_master.account_name FROM " + .TB_PREF."gl_trans, ".TB_PREF."chart_master WHERE ".TB_PREF."chart_master.account_code=".TB_PREF."gl_trans.account - AND ".TB_PREF."gl_trans.type=".systypes::work_order()." AND ".TB_PREF."gl_trans.type_no=$trans_id + AND ".TB_PREF."gl_trans.type=".systypes::work_order()." AND " + .TB_PREF."gl_trans.type_no=".db_escape($trans_id)." AND ".TB_PREF."gl_trans.person_type_id=".payment_person_types::WorkOrder(); if ($person_id != -1) - $sql .= " AND ".TB_PREF."gl_trans.person_id=$person_id"; + $sql .= " AND ".TB_PREF."gl_trans.person_id=".db_escape($person_id); $sql .= " AND amount < 0"; return db_query($sql, "The gl transactions could not be retrieved"); @@ -163,9 +172,9 @@ function get_gl_balance_from_to($from_date, $to_date, $account, $dimension=0, $d if ($to_date != "") $sql .= " AND tran_date < '$to'"; if ($dimension > 0) - $sql .= " AND dimension_id = $dimension"; + $sql .= " AND dimension_id = ".db_escape($dimension); if ($dimension2 > 0) - $sql .= " AND dimension2_id = $dimension2"; + $sql .= " AND dimension2_id = ".db_escape($dimension2); $result = db_query($sql, "The starting balance for account $account could not be calculated"); @@ -187,9 +196,9 @@ function get_gl_trans_from_to($from_date, $to_date, $account, $dimension=0, $dim if ($to_date != "") $sql .= " AND tran_date <= '$to'"; if ($dimension > 0) - $sql .= " AND dimension_id = $dimension"; + $sql .= " AND dimension_id = ".db_escape($dimension); if ($dimension2 > 0) - $sql .= " AND dimension2_id = $dimension2"; + $sql .= " AND dimension2_id = ".db_escape($dimension2); $result = db_query($sql, "Transactions for account $account could not be calculated"); @@ -200,17 +209,20 @@ function get_gl_trans_from_to($from_date, $to_date, $account, $dimension=0, $dim //---------------------------------------------------------------------------------------------------- function get_balance($account, $dimension, $dimension2, $from, $to, $from_incl=true, $to_incl=true) { - $sql = "SELECT SUM(IF(amount >= 0, amount, 0)) as debit, SUM(IF(amount < 0, -amount, 0)) as credit, SUM(amount) as balance - FROM ".TB_PREF."gl_trans,".TB_PREF."chart_master,".TB_PREF."chart_types, ".TB_PREF."chart_class - WHERE ".TB_PREF."gl_trans.account=".TB_PREF."chart_master.account_code AND ".TB_PREF."chart_master.account_type=".TB_PREF."chart_types.id + $sql = "SELECT SUM(IF(amount >= 0, amount, 0)) as debit, + SUM(IF(amount < 0, -amount, 0)) as credit, SUM(amount) as balance + FROM ".TB_PREF."gl_trans,".TB_PREF."chart_master," + .TB_PREF."chart_types, ".TB_PREF."chart_class + WHERE ".TB_PREF."gl_trans.account=".TB_PREF."chart_master.account_code AND " + .TB_PREF."chart_master.account_type=".TB_PREF."chart_types.id AND ".TB_PREF."chart_types.class_id=".TB_PREF."chart_class.cid AND"; if ($account != null) - $sql .= " account='$account' AND"; + $sql .= " account=".db_escape($account)." AND"; if ($dimension > 0) - $sql .= " dimension_id=$dimension AND"; + $sql .= " dimension_id=".db_escape($dimension)." AND"; if ($dimension2 > 0) - $sql .= " dimension2_id=$dimension2 AND"; + $sql .= " dimension2_id=".db_escape($dimension2)." AND"; $from_date = date2sql($from); if ($from_incl) $sql .= " tran_date >= '$from_date' AND"; @@ -236,15 +248,15 @@ function get_budget_trans_from_to($from_date, $to_date, $account, $dimension=0, $to = date2sql($to_date); $sql = "SELECT SUM(amount) FROM ".TB_PREF."budget_trans - WHERE account='$account' "; + WHERE account=".db_escape($account); if ($from_date != "") $sql .= " AND tran_date >= '$from' "; if ($to_date != "") $sql .= " AND tran_date <= '$to' "; if ($dimension > 0) - $sql .= " AND dimension_id = $dimension"; + $sql .= " AND dimension_id = ".db_escape($dimension); if ($dimension2 > 0) - $sql .= " AND dimension2_id = $dimension2"; + $sql .= " AND dimension2_id = ".db_escape($dimension2); $result = db_query($sql,"No budget accounts were returned"); $row = db_fetch_row($result); @@ -291,7 +303,7 @@ function add_trans_tax_details($trans_type, $trans_no, $tax_id, $rate, $included included_in_price, net_amount, amount, memo) VALUES (".db_escape($trans_type)."," . db_escape($trans_no).",'" .date2sql($tran_date)."',".db_escape($tax_id)."," - .$rate.",".$ex_rate.",".($included ? 1:0)."," + .db_escape($rate).",".db_escape($ex_rate).",".($included ? 1:0)."," .db_escape($net_amount)."," .db_escape($amount).",".db_escape($memo).")"; @@ -302,10 +314,11 @@ function add_trans_tax_details($trans_type, $trans_no, $tax_id, $rate, $included function get_trans_tax_details($trans_type, $trans_no) { - $sql = "SELECT ".TB_PREF."trans_tax_details.*, ".TB_PREF."tax_types.name AS tax_type_name + $sql = "SELECT ".TB_PREF."trans_tax_details.*, " + .TB_PREF."tax_types.name AS tax_type_name FROM ".TB_PREF."trans_tax_details,".TB_PREF."tax_types - WHERE trans_type = $trans_type - AND trans_no = $trans_no + WHERE trans_type = ".db_escape($trans_type)." + AND trans_no = ".db_escape($trans_no)." AND (net_amount != 0 OR amount != 0) AND ".TB_PREF."tax_types.id = ".TB_PREF."trans_tax_details.tax_type_id"; @@ -317,8 +330,8 @@ function get_trans_tax_details($trans_type, $trans_no) function void_trans_tax_details($type, $type_no) { $sql = "UPDATE ".TB_PREF."trans_tax_details SET amount=0, net_amount=0 - WHERE trans_no=$type_no - AND trans_type=$type"; + WHERE trans_no=".db_escape($type_no) + ." AND trans_type=".db_escape($type); db_query($sql, "The transaction tax details could not be voided"); } @@ -424,7 +437,8 @@ function add_journal_entries($items, $date_, $ref, $reverse, $memo_=null) function exists_gl_trans($type, $trans_id) { - $sql = "SELECT type_no FROM ".TB_PREF."gl_trans WHERE type=$type AND type_no=$trans_id"; + $sql = "SELECT type_no FROM ".TB_PREF."gl_trans WHERE type=".db_escape($type) + ." AND type_no=".db_escape($trans_id); $result = db_query($sql, "Cannot retreive a gl transaction"); return (db_num_rows($result) > 0); @@ -437,7 +451,8 @@ function void_gl_trans($type, $trans_id, $nested=false) if (!$nested) begin_transaction(); - $sql = "UPDATE ".TB_PREF."gl_trans SET amount=0 WHERE type=$type AND type_no=$trans_id"; + $sql = "UPDATE ".TB_PREF."gl_trans SET amount=0 WHERE type=".db_escape($type) + ." AND type_no=".db_escape($trans_id); db_query($sql, "could not void gl transactions for type=$type and trans_no=$trans_id"); diff --git a/gl/inquiry/bank_inquiry.php b/gl/inquiry/bank_inquiry.php index 94ccef8d..475c4818 100644 --- a/gl/inquiry/bank_inquiry.php +++ b/gl/inquiry/bank_inquiry.php @@ -59,7 +59,7 @@ $date_to = date2sql($_POST['TransToDate']); if (!isset($_POST['bank_account'])) $_POST['bank_account'] = ""; $sql = "SELECT ".TB_PREF."bank_trans.* FROM ".TB_PREF."bank_trans - WHERE ".TB_PREF."bank_trans.bank_act = '" . $_POST['bank_account'] . "' + WHERE ".TB_PREF."bank_trans.bank_act = ".db_escape($_POST['bank_account']) . " AND trans_date >= '$date_after' AND trans_date <= '$date_to' ORDER BY trans_date,".TB_PREF."bank_trans.id"; @@ -76,7 +76,8 @@ $th = array(_("Type"), _("#"), _("Reference"), _("Date"), _("Debit"), _("Credit"), _("Balance"), _("Person/Item"), ""); table_header($th); -$sql = "SELECT SUM(amount) FROM ".TB_PREF."bank_trans WHERE bank_act='" . $_POST['bank_account'] . "' +$sql = "SELECT SUM(amount) FROM ".TB_PREF."bank_trans WHERE bank_act=" + .db_escape($_POST['bank_account']) . " AND trans_date < '$date_after'"; $before_qty = db_query($sql, "The starting balance on hand could not be calculated"); diff --git a/gl/view/bank_transfer_view.php b/gl/view/bank_transfer_view.php index 318c93d0..b656a644 100644 --- a/gl/view/bank_transfer_view.php +++ b/gl/view/bank_transfer_view.php @@ -28,7 +28,7 @@ if (isset($_GET["trans_no"])){ $result = get_bank_trans(systypes::bank_transfer(), $trans_no); if (db_num_rows($result) != 2) - display_db_error("Bank transfer does not contain two records", $sql); + display_db_error("Bank transfer does not contain two records"); $trans1 = db_fetch($result); $trans2 = db_fetch($result); diff --git a/gl/view/gl_trans_view.php b/gl/view/gl_trans_view.php index e48b09bf..4feed339 100644 --- a/gl/view/gl_trans_view.php +++ b/gl/view/gl_trans_view.php @@ -47,7 +47,11 @@ function display_gl_heading($myrow) end_table(1); } -$sql = "SELECT ".TB_PREF."gl_trans.*, account_name FROM ".TB_PREF."gl_trans, ".TB_PREF."chart_master WHERE ".TB_PREF."gl_trans.account = ".TB_PREF."chart_master.account_code AND type= " . $_GET['type_id'] . " AND type_no = " . $_GET['trans_no'] . " ORDER BY counter"; +$sql = "SELECT ".TB_PREF."gl_trans.*, account_name FROM " + .TB_PREF."gl_trans, ".TB_PREF."chart_master WHERE " + .TB_PREF."gl_trans.account = ".TB_PREF."chart_master.account_code AND type= " + .db_escape($_GET['type_id']) . " AND type_no = ".db_escape($_GET['trans_no']) + . " ORDER BY counter"; $result = db_query($sql,"could not get transactions"); //alert("sql = ".$sql); diff --git a/inventory/cost_update.php b/inventory/cost_update.php index c35e81b5..432bf652 100644 --- a/inventory/cost_update.php +++ b/inventory/cost_update.php @@ -91,7 +91,7 @@ set_global_stock_item($_POST['stock_id']); $sql = "SELECT description, units, material_cost, labour_cost, overhead_cost, mb_flag FROM ".TB_PREF."stock_master - WHERE stock_id='" . $_POST['stock_id'] . "' + WHERE stock_id=".db_escape($_POST['stock_id']) . " GROUP BY description, units, material_cost, labour_cost, overhead_cost, mb_flag"; $result = db_query($sql); check_db_error("The cost details for the item could not be retrieved", $sql); diff --git a/inventory/includes/db/items_category_db.inc b/inventory/includes/db/items_category_db.inc index 014b7520..20f469b0 100644 --- a/inventory/includes/db/items_category_db.inc +++ b/inventory/includes/db/items_category_db.inc @@ -22,21 +22,21 @@ function update_item_category($ItemCategory, $description) { $sql = "UPDATE ".TB_PREF."stock_category SET description = ".db_escape($description)." - WHERE category_id = '$ItemCategory'"; + WHERE category_id = ".db_escape($ItemCategory); db_query($sql,"an item category could not be updated"); } function delete_item_category($ItemCategory) { - $sql="DELETE FROM ".TB_PREF."stock_category WHERE category_id='$ItemCategory'"; + $sql="DELETE FROM ".TB_PREF."stock_category WHERE category_id=".db_escape($ItemCategory); db_query($sql,"an item category could not be deleted"); } function get_item_category($ItemCategory) { - $sql="SELECT * FROM ".TB_PREF."stock_category WHERE category_id='$ItemCategory'"; + $sql="SELECT * FROM ".TB_PREF."stock_category WHERE category_id=".db_escape($ItemCategory); $result = db_query($sql,"an item category could not be retrieved"); @@ -45,7 +45,7 @@ function get_item_category($ItemCategory) function get_category_name($id) { - $sql = "SELECT description FROM ".TB_PREF."stock_category WHERE category_id=$id"; + $sql = "SELECT description FROM ".TB_PREF."stock_category WHERE category_id=".db_escape($id); $result = db_query($sql, "could not get sales type"); diff --git a/inventory/includes/db/items_codes_db.inc b/inventory/includes/db/items_codes_db.inc index d262ee71..5169e2d0 100644 --- a/inventory/includes/db/items_codes_db.inc +++ b/inventory/includes/db/items_codes_db.inc @@ -19,7 +19,7 @@ function update_item_code($id, $item_code, $stock_id, $description, $category, $ item_code = ".db_escape($item_code).", stock_id = ".db_escape($stock_id).", description = ".db_escape($description).", - category_id = $category, + category_id = ".db_escape($category).", quantity = ".db_escape($qty).", is_foreign = ".db_escape($foreign)." WHERE "; @@ -28,7 +28,7 @@ function update_item_code($id, $item_code, $stock_id, $description, $category, $ $sql .= "item_code = ".db_escape($item_code) ." AND stock_id = ".db_escape($stock_id); else - $sql .= "id = $id"; + $sql .= "id = ".db_escape($id); db_query($sql,"an item code could not be updated"); } @@ -38,20 +38,21 @@ function add_item_code($item_code, $stock_id, $description, $category, $qty, $fo $sql = "INSERT INTO ".TB_PREF."item_codes (item_code, stock_id, description, category_id, quantity, is_foreign) VALUES( ".db_escape($item_code).",".db_escape($stock_id).", - ".db_escape($description).",$category,".db_escape($qty).",".$foreign.")"; + ".db_escape($description).",".db_escape($category) + .",".db_escape($qty).",".db_escape($foreign).")"; db_query($sql,"an item code could not be added"); } function delete_item_code($id) { - $sql="DELETE FROM ".TB_PREF."item_codes WHERE id='$id'"; + $sql="DELETE FROM ".TB_PREF."item_codes WHERE id=".db_escape($id); db_query($sql,"an item code could not be deleted"); } function get_item_code($id) { - $sql="SELECT * FROM ".TB_PREF."item_codes WHERE id='$id'"; + $sql="SELECT * FROM ".TB_PREF."item_codes WHERE id=".db_escape($id); $result = db_query($sql,"item code could not be retrieved"); @@ -63,9 +64,9 @@ function get_all_item_codes($stock_id, $foreign=1) $sql="SELECT i.*, c.description as cat_name FROM " .TB_PREF."item_codes as i," .TB_PREF."stock_category as c - WHERE stock_id='$stock_id' + WHERE stock_id=".db_escape($stock_id)." AND i.category_id=c.category_id - AND i.is_foreign=$foreign"; + AND i.is_foreign=".db_escape($foreign); $result = db_query($sql,"all item codes could not be retrieved"); @@ -74,7 +75,7 @@ function get_all_item_codes($stock_id, $foreign=1) function delete_item_kit($item_code) { - $sql="DELETE FROM ".TB_PREF."item_codes WHERE item_code='$item_code'"; + $sql="DELETE FROM ".TB_PREF."item_codes WHERE item_code=".db_escape($item_code); db_query($sql,"an item kit could not be deleted"); } @@ -90,7 +91,7 @@ function get_item_kit($item_code) item.stock_id=comp.item_code WHERE kit.stock_id=comp.item_code - AND kit.item_code='$item_code'"; + AND kit.item_code=".db_escape($item_code); $result = db_query($sql,"item kit could not be retrieved"); @@ -101,7 +102,7 @@ function get_item_code_dflts($stock_id) { $sql = "SELECT units, decimals, description, category_id FROM ".TB_PREF."stock_master,".TB_PREF."item_units - WHERE stock_id='$stock_id'"; + WHERE stock_id=".db_escape($stock_id); $result = db_query($sql,"item code defaults could not be retrieved"); return db_fetch($result); @@ -137,7 +138,7 @@ function check_item_in_kit($old_id, $kit_code, $item_code, $recurse=false) function get_kit_props($kit_code) { $sql = "SELECT description, category_id FROM ".TB_PREF."item_codes " - . " WHERE item_code='$kit_code'"; + . " WHERE item_code=".db_escape($kit_code); $res = db_query($sql, "kit name query failed"); return db_fetch($res); } @@ -145,8 +146,8 @@ function get_kit_props($kit_code) function update_kit_props($kit_code, $name, $category) { $sql = "UPDATE ".TB_PREF."item_codes SET description=" - . db_escape($name).",category_id=".db_escape($category) - . " WHERE item_code='$kit_code'"; + . db_escape($name).",category_id=".db_escape($category) + . " WHERE item_code=".db_escape($kit_code); db_query($sql, "kit name update failed"); } @@ -154,8 +155,8 @@ function get_where_used($item_code) { $sql = "SELECT item_code, description FROM " .TB_PREF."item_codes " - . " WHERE stock_id='$item_code' - AND item_code!='$item_code'"; + . " WHERE stock_id=".db_escape($item_code)." + AND item_code!=".db_escape($item_code); return db_query($sql, "where used query failed"); } ?> \ No newline at end of file diff --git a/inventory/includes/db/items_db.inc b/inventory/includes/db/items_db.inc index e3361552..dfe25948 100644 --- a/inventory/includes/db/items_db.inc +++ b/inventory/includes/db/items_db.inc @@ -15,16 +15,16 @@ function update_item($stock_id, $description, $long_description, $category_id, $ { $sql = "UPDATE ".TB_PREF."stock_master SET long_description=".db_escape($long_description).", description=".db_escape($description).", - category_id='$category_id', - sales_account='$sales_account', - inventory_account='$inventory_account', - cogs_account='$cogs_account', - adjustment_account='$adjustment_account', - assembly_account='$assembly_account', - dimension_id=$dimension_id, - dimension2_id=$dimension2_id, - tax_type_id=$tax_type_id - WHERE stock_id='$stock_id'"; + category_id=".db_escape($category_id).", + sales_account=".db_escape($sales_account).", + inventory_account=".db_escape($inventory_account).", + cogs_account=".db_escape($cogs_account).", + adjustment_account=".db_escape($adjustment_account).", + assembly_account=".db_escape($assembly_account).", + dimension_id=".db_escape($dimension_id).", + dimension2_id=".db_escape($dimension2_id).", + tax_type_id=".db_escape($tax_type_id)." + WHERE stock_id=".db_escape($stock_id); db_query($sql, "The item could not be updated"); @@ -39,14 +39,18 @@ function add_item($stock_id, $description, $long_description, $category_id, $tax tax_type_id, units, mb_flag, sales_account, inventory_account, cogs_account, adjustment_account, assembly_account, dimension_id, dimension2_id) VALUES (".db_escape($stock_id).", ".db_escape($description).", ".db_escape($long_description).", - '$category_id', $tax_type_id, '$units', '$mb_flag', - '$sales_account', '$inventory_account', '$cogs_account', - '$adjustment_account', '$assembly_account', $dimension_id, $dimension2_id)"; + ".db_escape($category_id).", ".db_escape($tax_type_id).", " + .db_escape($units).", ".db_escape($mb_flag).", + ".db_escape($sales_account).", ".db_escape($inventory_account) + .", ".db_escape($cogs_account).",".db_escape($adjustment_account) + .", ".db_escape($assembly_account).", " + .db_escape($dimension_id).", ".db_escape($dimension2_id).")"; db_query($sql, "The item could not be added"); $sql = "INSERT INTO ".TB_PREF."loc_stock (loc_code, stock_id) - SELECT ".TB_PREF."locations.loc_code, '$stock_id' FROM ".TB_PREF."locations"; + SELECT ".TB_PREF."locations.loc_code, ".db_escape($stock_id) + ." FROM ".TB_PREF."locations"; db_query($sql, "The item locstock could not be added"); @@ -55,23 +59,23 @@ function add_item($stock_id, $description, $long_description, $category_id, $tax function delete_item($stock_id) { - $sql="DELETE FROM ".TB_PREF."stock_master WHERE stock_id='$stock_id'"; + $sql="DELETE FROM ".TB_PREF."stock_master WHERE stock_id=".db_escape($stock_id); db_query($sql, "could not delete stock item"); /*and cascade deletes in loc_stock */ - $sql ="DELETE FROM ".TB_PREF."loc_stock WHERE stock_id='$stock_id'"; + $sql ="DELETE FROM ".TB_PREF."loc_stock WHERE stock_id=".db_escape($stock_id); db_query($sql, "could not delete stock item loc stock"); /*and cascade deletes in purch_data */ - $sql ="DELETE FROM ".TB_PREF."purch_data WHERE stock_id='$stock_id'"; + $sql ="DELETE FROM ".TB_PREF."purch_data WHERE stock_id=".db_escape($stock_id); db_query($sql, "could not delete stock item purch data"); /*and cascade deletes in prices */ - $sql ="DELETE FROM ".TB_PREF."prices WHERE stock_id='$stock_id'"; + $sql ="DELETE FROM ".TB_PREF."prices WHERE stock_id=".db_escape($stock_id); db_query($sql, "could not delete stock item prices"); /*and cascade delete the bill of material if any */ - $sql = "DELETE FROM ".TB_PREF."bom WHERE parent='$stock_id'"; + $sql = "DELETE FROM ".TB_PREF."bom WHERE parent=".db_escape($stock_id); db_query($sql, "could not delete stock item bom"); delete_item_kit($stock_id); @@ -82,7 +86,7 @@ function get_item($stock_id) $sql = "SELECT ".TB_PREF."stock_master.*,".TB_PREF."item_tax_types.name AS tax_type_name FROM ".TB_PREF."stock_master,".TB_PREF."item_tax_types WHERE ".TB_PREF."item_tax_types.id=".TB_PREF."stock_master.tax_type_id - AND stock_id='$stock_id'"; + AND stock_id=".db_escape($stock_id); $result = db_query($sql,"an item could not be retreived"); return db_fetch($result); diff --git a/inventory/includes/db/items_locations_db.inc b/inventory/includes/db/items_locations_db.inc index 3df27803..ae7d352e 100644 --- a/inventory/includes/db/items_locations_db.inc +++ b/inventory/includes/db/items_locations_db.inc @@ -12,13 +12,14 @@ function add_item_location($loc_code, $location_name, $delivery_address, $phone, $fax, $email, $contact) { $sql = "INSERT INTO ".TB_PREF."locations (loc_code, location_name, delivery_address, phone, fax, email, contact) - VALUES (".db_escape($loc_code).", ".db_escape($location_name).", ".db_escape($delivery_address).", ".db_escape($phone).", ".db_escape($fax).", ".db_escape($email).", ".db_escape($contact).")"; - + VALUES (".db_escape($loc_code).", ".db_escape($location_name) + .", ".db_escape($delivery_address).", ".db_escape($phone) + .", ".db_escape($fax).", ".db_escape($email).", ".db_escape($contact).")"; db_query($sql,"a location could not be added"); /* Also need to add loc_stock records for all existing items */ $sql = "INSERT INTO ".TB_PREF."loc_stock (loc_code, stock_id, reorder_level) - SELECT '$loc_code', ".TB_PREF."stock_master.stock_id, 0 FROM ".TB_PREF."stock_master"; + SELECT ".db_escape($loc_code).", ".TB_PREF."stock_master.stock_id, 0 FROM ".TB_PREF."stock_master"; db_query($sql,"a location could not be added"); } @@ -32,7 +33,7 @@ function update_item_location($loc_code, $location_name, $delivery_address, $pho delivery_address=".db_escape($delivery_address).", phone=".db_escape($phone).", fax=".db_escape($fax).", email=".db_escape($email).", contact=".db_escape($contact)." - WHERE loc_code = '$loc_code'"; + WHERE loc_code = ".db_escape($loc_code); db_query($sql,"a location could not be updated"); } @@ -41,10 +42,10 @@ function update_item_location($loc_code, $location_name, $delivery_address, $pho function delete_item_location($item_location) { - $sql="DELETE FROM ".TB_PREF."locations WHERE loc_code='$item_location'"; + $sql="DELETE FROM ".TB_PREF."locations WHERE loc_code=".db_escape($item_location); db_query($sql,"a location could not be deleted"); - $sql = "DELETE FROM ".TB_PREF."loc_stock WHERE loc_code ='$item_location'"; + $sql = "DELETE FROM ".TB_PREF."loc_stock WHERE loc_code =".db_escape($item_location); db_query($sql,"a location could not be deleted"); } @@ -52,7 +53,7 @@ function delete_item_location($item_location) function get_item_location($item_location) { - $sql="SELECT * FROM ".TB_PREF."locations WHERE loc_code='$item_location'"; + $sql="SELECT * FROM ".TB_PREF."locations WHERE loc_code=".db_escape($item_location); $result = db_query($sql,"a location could not be retrieved"); @@ -64,7 +65,7 @@ function get_item_location($item_location) function set_reorder_level($stock_id, $loc_code, $reorder_level) { $sql = "UPDATE ".TB_PREF."loc_stock SET reorder_level = $reorder_level - WHERE stock_id = '$stock_id' AND loc_code = '$loc_code'"; + WHERE stock_id = ".db_escape($stock_id)." AND loc_code = ".db_escape($loc_code); db_query($sql,"an item reorder could not be set"); } @@ -76,7 +77,8 @@ function get_loc_details($stock_id) $sql = "SELECT ".TB_PREF."loc_stock.*, ".TB_PREF."locations.location_name FROM ".TB_PREF."loc_stock, ".TB_PREF."locations WHERE ".TB_PREF."loc_stock.loc_code=".TB_PREF."locations.loc_code - AND ".TB_PREF."loc_stock.stock_id = '" . $stock_id . "' ORDER BY ".TB_PREF."loc_stock.loc_code"; + AND ".TB_PREF."loc_stock.stock_id = ".db_escape($stock_id) + ." ORDER BY ".TB_PREF."loc_stock.loc_code"; return db_query($sql,"an item reorder could not be retreived"); } diff --git a/inventory/includes/db/items_prices_db.inc b/inventory/includes/db/items_prices_db.inc index 13fa14da..9b670d24 100644 --- a/inventory/includes/db/items_prices_db.inc +++ b/inventory/includes/db/items_prices_db.inc @@ -12,24 +12,24 @@ function add_item_price($stock_id, $sales_type_id, $curr_abrev, $price) { $sql = "INSERT INTO ".TB_PREF."prices (stock_id, sales_type_id, curr_abrev, price) - VALUES ('$stock_id', $sales_type_id, '$curr_abrev', $price)"; + VALUES (".db_escape($stock_id).", ".db_escape($sales_type_id) + .", ".db_escape($curr_abrev).", ".db_escape($price).")"; db_query($sql,"an item price could not be added"); } function update_item_price($price_id, $sales_type_id, $curr_abrev, $price) { - $sql = "UPDATE ".TB_PREF."prices SET sales_type_id=$sales_type_id, - curr_abrev='$curr_abrev', - price=$price - WHERE id=$price_id"; + $sql = "UPDATE ".TB_PREF."prices SET sales_type_id=".db_escape($sales_type_id).", + curr_abrev=".db_escape($curr_abrev).", + price=".db_escape($price)." WHERE id=".db_escape($price_id); db_query($sql,"an item price could not be updated"); } function delete_item_price($price_id) { - $sql="DELETE FROM ".TB_PREF."prices WHERE id= $price_id"; + $sql="DELETE FROM ".TB_PREF."prices WHERE id= ".db_escape($price_id); db_query($sql,"an item price could not be deleted"); } @@ -38,14 +38,15 @@ function get_prices($stock_id) $sql = "SELECT ".TB_PREF."sales_types.sales_type, ".TB_PREF."prices.* FROM ".TB_PREF."prices, ".TB_PREF."sales_types WHERE ".TB_PREF."prices.sales_type_id = ".TB_PREF."sales_types.id - AND stock_id='$stock_id' ORDER BY curr_abrev, sales_type_id"; + AND stock_id=".db_escape($stock_id) + ." ORDER BY curr_abrev, sales_type_id"; return db_query($sql,"item prices could not be retreived"); } function get_stock_price($price_id) { - $sql = "SELECT * FROM ".TB_PREF."prices WHERE id=$price_id"; + $sql = "SELECT * FROM ".TB_PREF."prices WHERE id=".db_escape($price_id); $result = db_query($sql,"price could not be retreived"); diff --git a/inventory/includes/db/items_trans_db.inc b/inventory/includes/db/items_trans_db.inc index 9a16d7b3..55fa5fb1 100644 --- a/inventory/includes/db/items_trans_db.inc +++ b/inventory/includes/db/items_trans_db.inc @@ -25,11 +25,11 @@ function stock_cost_update($stock_id, $material_cost, $labour_cost, $overhead_co begin_transaction(); - $sql = "UPDATE ".TB_PREF."stock_master SET material_cost=$material_cost, - labour_cost=$labour_cost, - overhead_cost=$overhead_cost, - last_cost=$last_cost - WHERE stock_id='$stock_id'"; + $sql = "UPDATE ".TB_PREF."stock_master SET material_cost=".db_escape($material_cost).", + labour_cost=".db_escape($labour_cost).", + overhead_cost=".db_escape($overhead_cost).", + last_cost=".db_escape($last_cost)." + WHERE stock_id=".db_escape($stock_id); db_query($sql,"The cost details for the inventory item could not be updated"); $qoh = get_qoh_on_date($_POST['stock_id']); @@ -53,12 +53,12 @@ function stock_cost_update($stock_id, $material_cost, $labour_cost, $overhead_co $stock_gl_code["dimension_id"], $stock_gl_code["dimension2_id"], $memo_, (-$value_of_change)); add_gl_trans_std_cost(systypes::cost_update(), $update_no, $date_, $stock_gl_code["inventory_account"], 0, 0, $memo_, - $value_of_change); - } + $value_of_change); + } commit_transaction(); - return $update_no; + return $update_no; } //------------------------------------------------------------------------------------------------------------- diff --git a/inventory/includes/db/items_units_db.inc b/inventory/includes/db/items_units_db.inc index 2d232102..b3df604a 100644 --- a/inventory/includes/db/items_units_db.inc +++ b/inventory/includes/db/items_units_db.inc @@ -15,26 +15,26 @@ function write_item_unit($selected, $abbr, $description, $decimals) $sql = "UPDATE ".TB_PREF."item_units SET abbr = ".db_escape($abbr).", name = ".db_escape($description).", - decimals = $decimals - WHERE abbr = '$selected'"; + decimals = ".db_escape($decimals)." + WHERE abbr = ".db_escape($selected); else $sql = "INSERT INTO ".TB_PREF."item_units (abbr, name, decimals) VALUES( ".db_escape($abbr).", - ".db_escape($description).", $decimals)"; + ".db_escape($description).", ".db_escape($decimals).")"; db_query($sql,"an item unit could not be updated"); } function delete_item_unit($unit) { - $sql="DELETE FROM ".TB_PREF."item_units WHERE abbr='$unit'"; + $sql="DELETE FROM ".TB_PREF."item_units WHERE abbr=".db_escape($unit); db_query($sql,"an unit of measure could not be deleted"); } function get_item_unit($unit) { - $sql="SELECT * FROM ".TB_PREF."item_units WHERE abbr='$unit'"; + $sql="SELECT * FROM ".TB_PREF."item_units WHERE abbr=".db_escape($unit); $result = db_query($sql,"an unit of measure could not be retrieved"); @@ -43,7 +43,7 @@ function get_item_unit($unit) function get_unit_descr($unit) { - $sql = "SELECT description FROM ".TB_PREF."item_units WHERE abbr='$unit'"; + $sql = "SELECT description FROM ".TB_PREF."item_units WHERE abbr=".db_escape($unit); $result = db_query($sql, "could not unit description"); @@ -52,7 +52,7 @@ function get_unit_descr($unit) } function item_unit_used($unit) { - $sql= "SELECT COUNT(*) FROM ".TB_PREF."stock_master WHERE units='$unit'"; + $sql= "SELECT COUNT(*) FROM ".TB_PREF."stock_master WHERE units=".db_escape($unit); $result = db_query($sql, "could not query stock master"); $myrow = db_fetch_row($result); return ($myrow[0] > 0); @@ -66,7 +66,7 @@ function get_all_item_units() { function get_unit_dec($stock_id) { $sql = "SELECT decimals FROM ".TB_PREF."item_units, ".TB_PREF."stock_master - WHERE abbr=units AND stock_id='$stock_id' LIMIT 1"; + WHERE abbr=units AND stock_id=".db_escape($stock_id)." LIMIT 1"; $result = db_query($sql, "could not get unit decimals"); $row = db_fetch_row($result); diff --git a/inventory/includes/db/movement_types_db.inc b/inventory/includes/db/movement_types_db.inc index 78e55a97..f6562b18 100644 --- a/inventory/includes/db/movement_types_db.inc +++ b/inventory/includes/db/movement_types_db.inc @@ -20,7 +20,7 @@ function add_movement_type($name) function update_movement_type($type_id, $name) { $sql = "UPDATE ".TB_PREF."movement_types SET name=".db_escape($name)." - WHERE id=$type_id"; + WHERE id=".db_escape($type_id); db_query($sql, "could not update item movement type"); } @@ -34,7 +34,7 @@ function get_all_movement_type() function get_movement_type($type_id) { - $sql = "SELECT * FROM ".TB_PREF."movement_types WHERE id=$type_id"; + $sql = "SELECT * FROM ".TB_PREF."movement_types WHERE id=".db_escape($type_id); $result = db_query($sql, "could not get item movement type"); @@ -43,7 +43,7 @@ function get_movement_type($type_id) function delete_movement_type($type_id) { - $sql="DELETE FROM ".TB_PREF."movement_types WHERE id=$type_id"; + $sql="DELETE FROM ".TB_PREF."movement_types WHERE id=".db_escape($type_id); db_query($sql, "could not delete item movement type"); } diff --git a/inventory/inquiry/stock_movements.php b/inventory/inquiry/stock_movements.php index e9c05c49..bed42358 100644 --- a/inventory/inquiry/stock_movements.php +++ b/inventory/inquiry/stock_movements.php @@ -64,10 +64,10 @@ $after_date = date2sql($_POST['AfterDate']); $sql = "SELECT type, trans_no, tran_date, person_id, qty, reference FROM ".TB_PREF."stock_moves - WHERE loc_code='" . $_POST['StockLocation'] . "' + WHERE loc_code=".db_escape($_POST['StockLocation'])." AND tran_date >= '". $after_date . "' AND tran_date <= '" . $before_date . "' - AND stock_id = '" . $_POST['stock_id'] . "' ORDER BY tran_date,trans_id"; + AND stock_id = ".db_escape($_POST['stock_id']) . " ORDER BY tran_date,trans_id"; $result = db_query($sql, "could not query stock moves"); check_db_error("The stock movements for the selected criteria could not be retrieved",$sql); @@ -79,8 +79,8 @@ $th = array(_("Type"), _("#"), _("Reference"), _("Date"), _("Detail"), table_header($th); -$sql = "SELECT SUM(qty) FROM ".TB_PREF."stock_moves WHERE stock_id='" . $_POST['stock_id'] . "' - AND loc_code='" . $_POST['StockLocation'] . "' +$sql = "SELECT SUM(qty) FROM ".TB_PREF."stock_moves WHERE stock_id=".db_escape($_POST['stock_id']) . " + AND loc_code=".db_escape( $_POST['StockLocation']) . " AND tran_date < '" . $after_date . "'"; $before_qty = db_query($sql, "The starting quantity on hand could not be calculated"); diff --git a/inventory/manage/item_categories.php b/inventory/manage/item_categories.php index f5de480a..149e23e6 100644 --- a/inventory/manage/item_categories.php +++ b/inventory/manage/item_categories.php @@ -57,7 +57,7 @@ if ($Mode == 'Delete') { // PREVENT DELETES IF DEPENDENT RECORDS IN 'stock_master' - $sql= "SELECT COUNT(*) FROM ".TB_PREF."stock_master WHERE category_id='$selected_id'"; + $sql= "SELECT COUNT(*) FROM ".TB_PREF."stock_master WHERE category_id=".db_escape($selected_id); $result = db_query($sql, "could not query stock master"); $myrow = db_fetch_row($result); if ($myrow[0] > 0) diff --git a/inventory/manage/item_units.php b/inventory/manage/item_units.php index 95473885..688fda3b 100644 --- a/inventory/manage/item_units.php +++ b/inventory/manage/item_units.php @@ -34,6 +34,12 @@ if ($Mode=='ADD_ITEM' || $Mode=='UPDATE_ITEM') display_error(_("The unit of measure code cannot be empty.")); set_focus('abbr'); } + if (strlen(db_escape($_POST['abbr']))>(20+2)) + { + $input_error = 1; + display_error(_("The unit of measure code is too long.")); + set_focus('abbr'); + } if (strlen($_POST['description']) == 0) { $input_error = 1; diff --git a/inventory/manage/items.php b/inventory/manage/items.php index 112e1b2c..70ecb4e6 100644 --- a/inventory/manage/items.php +++ b/inventory/manage/items.php @@ -185,7 +185,7 @@ if (isset($_POST['addupdate'])) function can_delete($stock_id) { - $sql= "SELECT COUNT(*) FROM ".TB_PREF."stock_moves WHERE stock_id='$stock_id'"; + $sql= "SELECT COUNT(*) FROM ".TB_PREF."stock_moves WHERE stock_id=".db_escape($stock_id); $result = db_query($sql, "could not query stock moves"); $myrow = db_fetch_row($result); if ($myrow[0] > 0) @@ -194,7 +194,7 @@ function can_delete($stock_id) return false; } - $sql= "SELECT COUNT(*) FROM ".TB_PREF."bom WHERE component='$stock_id'"; + $sql= "SELECT COUNT(*) FROM ".TB_PREF."bom WHERE component=".db_escape($stock_id); $result = db_query($sql, "could not query boms"); $myrow = db_fetch_row($result); if ($myrow[0] > 0) @@ -203,7 +203,7 @@ function can_delete($stock_id) return false; } - $sql= "SELECT COUNT(*) FROM ".TB_PREF."sales_order_details WHERE stk_code='$stock_id'"; + $sql= "SELECT COUNT(*) FROM ".TB_PREF."sales_order_details WHERE stk_code=".db_escape($stock_id); $result = db_query($sql, "could not query sales orders"); $myrow = db_fetch_row($result); if ($myrow[0] > 0) @@ -212,7 +212,7 @@ function can_delete($stock_id) return false; } - $sql= "SELECT COUNT(*) FROM ".TB_PREF."purch_order_details WHERE item_code='$stock_id'"; + $sql= "SELECT COUNT(*) FROM ".TB_PREF."purch_order_details WHERE item_code=".db_escape($stock_id); $result = db_query($sql, "could not query purchase orders"); $myrow = db_fetch_row($result); if ($myrow[0] > 0) diff --git a/inventory/manage/locations.php b/inventory/manage/locations.php index 47eedc64..79384703 100644 --- a/inventory/manage/locations.php +++ b/inventory/manage/locations.php @@ -33,10 +33,10 @@ if ($Mode=='ADD_ITEM' || $Mode=='UPDATE_ITEM') //first off validate inputs sensible $_POST['loc_code'] = strtoupper($_POST['loc_code']); - if (strlen($_POST['loc_code']) > 5) + if (strlen(db_escape($_POST['loc_code'])) > 7) //check length after conversion { $input_error = 1; - display_error( _("The location code must be five characters or less long.")); + display_error( _("The location code must be five characters or less long (including converted special chars).")); set_focus('loc_code'); } elseif (strlen($_POST['location_name']) == 0) @@ -71,7 +71,7 @@ if ($Mode=='ADD_ITEM' || $Mode=='UPDATE_ITEM') function can_delete($selected_id) { - $sql= "SELECT COUNT(*) FROM ".TB_PREF."stock_moves WHERE loc_code='$selected_id'"; + $sql= "SELECT COUNT(*) FROM ".TB_PREF."stock_moves WHERE loc_code=".db_escape($selected_id); $result = db_query($sql, "could not query stock moves"); $myrow = db_fetch_row($result); if ($myrow[0] > 0) @@ -80,7 +80,7 @@ function can_delete($selected_id) return false; } - $sql= "SELECT COUNT(*) FROM ".TB_PREF."workorders WHERE loc_code='$selected_id'"; + $sql= "SELECT COUNT(*) FROM ".TB_PREF."workorders WHERE loc_code=".db_escape($selected_id); $result = db_query($sql, "could not query work orders"); $myrow = db_fetch_row($result); if ($myrow[0] > 0) @@ -98,7 +98,7 @@ function can_delete($selected_id) return false; } - $sql= "SELECT COUNT(*) FROM ".TB_PREF."bom WHERE loc_code='$selected_id'"; + $sql= "SELECT COUNT(*) FROM ".TB_PREF."bom WHERE loc_code=".db_escape($selected_id); $result = db_query($sql, "could not query bom"); $myrow = db_fetch_row($result); if ($myrow[0] > 0) @@ -106,7 +106,7 @@ function can_delete($selected_id) display_error(_("Cannot delete this location because it is used by some related records in other tables.")); return false; } - $sql= "SELECT COUNT(*) FROM ".TB_PREF."grn_batch WHERE loc_code='$selected_id'"; + $sql= "SELECT COUNT(*) FROM ".TB_PREF."grn_batch WHERE loc_code=".db_escape($selected_id); $result = db_query($sql, "could not query grn batch"); $myrow = db_fetch_row($result); if ($myrow[0] > 0) @@ -114,7 +114,7 @@ function can_delete($selected_id) display_error(_("Cannot delete this location because it is used by some related records in other tables.")); return false; } - $sql= "SELECT COUNT(*) FROM ".TB_PREF."purch_orders WHERE into_stock_location='$selected_id'"; + $sql= "SELECT COUNT(*) FROM ".TB_PREF."purch_orders WHERE into_stock_location=".db_escape($selected_id); $result = db_query($sql, "could not query purch orders"); $myrow = db_fetch_row($result); if ($myrow[0] > 0) @@ -122,7 +122,7 @@ function can_delete($selected_id) display_error(_("Cannot delete this location because it is used by some related records in other tables.")); return false; } - $sql= "SELECT COUNT(*) FROM ".TB_PREF."sales_orders WHERE from_stk_loc='$selected_id'"; + $sql= "SELECT COUNT(*) FROM ".TB_PREF."sales_orders WHERE from_stk_loc=".db_escape($selected_id); $result = db_query($sql, "could not query sales orders"); $myrow = db_fetch_row($result); if ($myrow[0] > 0) @@ -130,7 +130,7 @@ function can_delete($selected_id) display_error(_("Cannot delete this location because it is used by some related records in other tables.")); return false; } - $sql= "SELECT COUNT(*) FROM ".TB_PREF."sales_pos WHERE pos_location='$selected_id'"; + $sql= "SELECT COUNT(*) FROM ".TB_PREF."sales_pos WHERE pos_location=".db_escape($selected_id); $result = db_query($sql, "could not query sales pos"); $myrow = db_fetch_row($result); if ($myrow[0] > 0) diff --git a/inventory/manage/movement_types.php b/inventory/manage/movement_types.php index 69e8fe16..b1eb260f 100644 --- a/inventory/manage/movement_types.php +++ b/inventory/manage/movement_types.php @@ -57,7 +57,7 @@ if ($Mode=='ADD_ITEM' || $Mode=='UPDATE_ITEM') function can_delete($selected_id) { $sql= "SELECT COUNT(*) FROM ".TB_PREF."stock_moves - WHERE type=" . systypes::inventory_adjustment(). " AND person_id=$selected_id"; + WHERE type=" . systypes::inventory_adjustment(). " AND person_id=".db_escape($selected_id); $result = db_query($sql, "could not query stock moves"); $myrow = db_fetch_row($result); if ($myrow[0] > 0) diff --git a/inventory/purchasing_data.php b/inventory/purchasing_data.php index 84d67482..be3e7a03 100644 --- a/inventory/purchasing_data.php +++ b/inventory/purchasing_data.php @@ -66,20 +66,21 @@ if ($Mode=='ADD_ITEM' || $Mode=='UPDATE_ITEM') $sql = "INSERT INTO ".TB_PREF."purch_data (supplier_id, stock_id, price, suppliers_uom, conversion_factor, supplier_description) VALUES ("; - $sql .= "'".$_POST['supplier_id']."', '" . $_POST['stock_id'] . "', " . - input_num('price',0) . ", '" . $_POST['suppliers_uom'] . "', " . - input_num('conversion_factor') . ", " . db_escape($_POST['supplier_description']) . ")"; + $sql .= db_escape($_POST['supplier_id']).", ".db_escape($_POST['stock_id']). ", " + .input_num('price',0) . ", ".db_escape( $_POST['suppliers_uom'] ). ", " + .input_num('conversion_factor') . ", " + .db_escape($_POST['supplier_description']) . ")"; db_query($sql,"The supplier purchasing details could not be added"); display_notification(_("This supplier purchasing data has been added.")); } else { $sql = "UPDATE ".TB_PREF."purch_data SET price=" . input_num('price',0) . ", - suppliers_uom='" . $_POST['suppliers_uom'] . "', + suppliers_uom=".db_escape($_POST['suppliers_uom']) . ", conversion_factor=" . input_num('conversion_factor') . ", supplier_description=" . db_escape($_POST['supplier_description']) . " - WHERE stock_id='" . $_POST['stock_id'] . "' AND - supplier_id='$selected_id'"; + WHERE stock_id=".db_escape($_POST['stock_id']) . " AND + supplier_id=".db_escape($selected_id); db_query($sql,"The supplier purchasing details could not be updated"); display_notification(_("Supplier purchasing data has been updated.")); @@ -93,8 +94,8 @@ if ($Mode=='ADD_ITEM' || $Mode=='UPDATE_ITEM') if ($Mode == 'Delete') { - $sql = "DELETE FROM ".TB_PREF."purch_data WHERE supplier_id='$selected_id' - AND stock_id='" . $_POST['stock_id'] . "'"; + $sql = "DELETE FROM ".TB_PREF."purch_data WHERE supplier_id=".db_escape($selected_id)." + AND stock_id=".db_escape($_POST['stock_id']); db_query($sql,"could not delete purchasing data"); display_notification(_("The purchasing data item has been sucessfully deleted.")); @@ -138,10 +139,11 @@ if ($mb_flag == -1) else { - $sql = "SELECT ".TB_PREF."purch_data.*,".TB_PREF."suppliers.supp_name,".TB_PREF."suppliers.curr_code + $sql = "SELECT ".TB_PREF."purch_data.*,".TB_PREF."suppliers.supp_name," + .TB_PREF."suppliers.curr_code FROM ".TB_PREF."purch_data INNER JOIN ".TB_PREF."suppliers ON ".TB_PREF."purch_data.supplier_id=".TB_PREF."suppliers.supplier_id - WHERE stock_id = '" . $_POST['stock_id'] . "'"; + WHERE stock_id = ".db_escape($_POST['stock_id']); $result = db_query($sql, "The supplier purchasing details for the selected part could not be retrieved"); div_start('price_table'); @@ -195,8 +197,8 @@ if ($Mode =='Edit') $sql = "SELECT ".TB_PREF."purch_data.*,".TB_PREF."suppliers.supp_name FROM ".TB_PREF."purch_data INNER JOIN ".TB_PREF."suppliers ON ".TB_PREF."purch_data.supplier_id=".TB_PREF."suppliers.supplier_id - WHERE ".TB_PREF."purch_data.supplier_id='$selected_id' - AND ".TB_PREF."purch_data.stock_id='" . $_POST['stock_id'] . "'"; + WHERE ".TB_PREF."purch_data.supplier_id=".db_escape($selected_id)." + AND ".TB_PREF."purch_data.stock_id=".db_escape($_POST['stock_id']); $result = db_query($sql, "The supplier purchasing details for the selected supplier and item could not be retrieved"); diff --git a/manufacturing/includes/db/work_centres_db.inc b/manufacturing/includes/db/work_centres_db.inc index c01ad50f..31736af5 100644 --- a/manufacturing/includes/db/work_centres_db.inc +++ b/manufacturing/includes/db/work_centres_db.inc @@ -20,7 +20,7 @@ function add_work_centre($name, $description) function update_work_centre($type_id, $name, $description) { $sql = "UPDATE ".TB_PREF."workcentres SET name=".db_escape($name).", description=".db_escape($description)." - WHERE id=$type_id"; + WHERE id=".db_escape($type_id); db_query($sql, "could not update work centre"); } @@ -34,7 +34,7 @@ function get_all_work_centres() function get_work_centre($type_id) { - $sql = "SELECT * FROM ".TB_PREF."workcentres WHERE id=$type_id"; + $sql = "SELECT * FROM ".TB_PREF."workcentres WHERE id=".db_escape($type_id); $result = db_query($sql, "could not get work centre"); @@ -43,7 +43,7 @@ function get_work_centre($type_id) function delete_work_centre($type_id) { - $sql="DELETE FROM ".TB_PREF."workcentres WHERE id=$type_id"; + $sql="DELETE FROM ".TB_PREF."workcentres WHERE id=".db_escape($type_id); db_query($sql, "could not delete work centre"); } diff --git a/manufacturing/includes/db/work_order_issues_db.inc b/manufacturing/includes/db/work_order_issues_db.inc index 0df06846..23d07caa 100644 --- a/manufacturing/includes/db/work_order_issues_db.inc +++ b/manufacturing/includes/db/work_order_issues_db.inc @@ -34,8 +34,8 @@ function add_work_order_issue($woid, $ref, $to_work_order, $items, $location, $w // insert the actual issue $sql = "INSERT INTO ".TB_PREF."wo_issues (workorder_id, reference, issue_date, loc_code, workcentre_id) - VALUES ($woid, ".db_escape($ref).", '" . - date2sql($date_) . "', ".db_escape($location).", $workcentre)"; + VALUES (".db_escape($woid).", ".db_escape($ref).", '" . + date2sql($date_) . "', ".db_escape($location).", ".db_escape($workcentre).")"; db_query($sql,"The work order issue could not be added"); $number = db_insert_id(); @@ -51,7 +51,8 @@ function add_work_order_issue($woid, $ref, $to_work_order, $items, $location, $w $location, $date_, $memo_, -$item->quantity, 0); $sql = "INSERT INTO ".TB_PREF."wo_issue_items (issue_id, stock_id, qty_issued) - VALUES ('$number', '$item->stock_id', $item->quantity)"; + VALUES (".db_escape($number).", ".db_escape($item->stock_id).", " + .db_escape($item->quantity).")"; db_query($sql,"A work order issue item could not be added"); } @@ -67,7 +68,8 @@ function add_work_order_issue($woid, $ref, $to_work_order, $items, $location, $w function get_work_order_issues($woid) { - $sql = "SELECT * FROM ".TB_PREF."wo_issues WHERE workorder_id=$woid ORDER BY issue_no"; + $sql = "SELECT * FROM ".TB_PREF."wo_issues WHERE workorder_id=".db_escape($woid) + ." ORDER BY issue_no"; return db_query($sql, "The work order issues could not be retrieved"); } @@ -76,7 +78,8 @@ function get_additional_issues($woid) $sql = "SELECT ".TB_PREF."wo_issues.*, ".TB_PREF."wo_issue_items.* FROM ".TB_PREF."wo_issues, ".TB_PREF."wo_issue_items WHERE ".TB_PREF."wo_issues.issue_no=".TB_PREF."wo_issue_items.issue_id - AND ".TB_PREF."wo_issues.workorder_id=$woid ORDER BY ".TB_PREF."wo_issue_items.id"; + AND ".TB_PREF."wo_issues.workorder_id=".db_escape($woid) + ." ORDER BY ".TB_PREF."wo_issue_items.id"; return db_query($sql, "The work order issues could not be retrieved"); } //-------------------------------------------------------------------------------------- @@ -84,9 +87,11 @@ function get_additional_issues($woid) function get_work_order_issue($issue_no) { $sql = "SELECT DISTINCT ".TB_PREF."wo_issues.*, ".TB_PREF."workorders.stock_id, - ".TB_PREF."stock_master.description, ".TB_PREF."locations.location_name, ".TB_PREF."workcentres.name AS WorkCentreName - FROM ".TB_PREF."wo_issues, ".TB_PREF."workorders, ".TB_PREF."stock_master, ".TB_PREF."locations, ".TB_PREF."workcentres - WHERE issue_no='$issue_no' + ".TB_PREF."stock_master.description, ".TB_PREF."locations.location_name, " + .TB_PREF."workcentres.name AS WorkCentreName + FROM ".TB_PREF."wo_issues, ".TB_PREF."workorders, ".TB_PREF."stock_master, " + .TB_PREF."locations, ".TB_PREF."workcentres + WHERE issue_no=".db_escape($issue_no)." AND ".TB_PREF."workorders.id = ".TB_PREF."wo_issues.workorder_id AND ".TB_PREF."locations.loc_code = ".TB_PREF."wo_issues.loc_code AND ".TB_PREF."workcentres.id = ".TB_PREF."wo_issues.workcentre_id @@ -100,9 +105,10 @@ function get_work_order_issue($issue_no) function get_work_order_issue_details($issue_no) { - $sql = "SELECT ".TB_PREF."wo_issue_items.*,".TB_PREF."stock_master.description, ".TB_PREF."stock_master.units + $sql = "SELECT ".TB_PREF."wo_issue_items.*," + .TB_PREF."stock_master.description, ".TB_PREF."stock_master.units FROM ".TB_PREF."wo_issue_items, ".TB_PREF."stock_master - WHERE issue_id=$issue_no + WHERE issue_id=".db_escape($issue_no)." AND ".TB_PREF."stock_master.stock_id=".TB_PREF."wo_issue_items.stock_id ORDER BY ".TB_PREF."wo_issue_items.id"; return db_query($sql, "The work order issue items could not be retrieved"); @@ -112,7 +118,7 @@ function get_work_order_issue_details($issue_no) function exists_work_order_issue($issue_no) { - $sql = "SELECT issue_no FROM ".TB_PREF."wo_issues WHERE issue_no=$issue_no"; + $sql = "SELECT issue_no FROM ".TB_PREF."wo_issues WHERE issue_no=".db_escape($issue_no); $result = db_query($sql, "Cannot retreive a wo issue"); return (db_num_rows($result) > 0); @@ -125,7 +131,8 @@ function void_work_order_issue($type_no) begin_transaction(); // void the actual issue items and their quantities - $sql = "UPDATE ".TB_PREF."wo_issue_items Set qty_issued = 0 WHERE issue_id=$type_no"; + $sql = "UPDATE ".TB_PREF."wo_issue_items Set qty_issued = 0 WHERE issue_id=" + .db_escape($type_no); db_query($sql,"A work order issue item could not be voided"); // void all related stock moves diff --git a/manufacturing/includes/db/work_order_produce_items_db.inc b/manufacturing/includes/db/work_order_produce_items_db.inc index ef5640c3..d95ec1bd 100644 --- a/manufacturing/includes/db/work_order_produce_items_db.inc +++ b/manufacturing/includes/db/work_order_produce_items_db.inc @@ -31,7 +31,8 @@ function work_order_produce($woid, $ref, $quantity, $date_, $memo_, $close_wo) $date = date2sql($date_); $sql = "INSERT INTO ".TB_PREF."wo_manufacture (workorder_id, reference, quantity, date_) - VALUES ($woid, ".db_escape($ref).", $quantity, '$date')"; + VALUES (".db_escape($woid).", ".db_escape($ref).", ".db_escape($quantity) + .", '$date')"; db_query($sql,"A work order manufacture could not be added"); @@ -62,11 +63,12 @@ function work_order_produce($woid, $ref, $quantity, $date_, $memo_, $close_wo) function get_work_order_produce($id) { - $sql = "SELECT ".TB_PREF."wo_manufacture.*,".TB_PREF."workorders.stock_id, ".TB_PREF."stock_master.description AS StockDescription + $sql = "SELECT ".TB_PREF."wo_manufacture.*,".TB_PREF."workorders.stock_id, " + .TB_PREF."stock_master.description AS StockDescription FROM ".TB_PREF."wo_manufacture, ".TB_PREF."workorders, ".TB_PREF."stock_master WHERE ".TB_PREF."wo_manufacture.workorder_id=".TB_PREF."workorders.id AND ".TB_PREF."stock_master.stock_id=".TB_PREF."workorders.stock_id - AND ".TB_PREF."wo_manufacture.id=$id"; + AND ".TB_PREF."wo_manufacture.id=".db_escape($id); $result = db_query($sql, "The work order production could not be retrieved"); return db_fetch($result); @@ -76,7 +78,8 @@ function get_work_order_produce($id) function get_work_order_productions($woid) { - $sql = "SELECT * FROM ".TB_PREF."wo_manufacture WHERE workorder_id=$woid ORDER BY id"; + $sql = "SELECT * FROM ".TB_PREF."wo_manufacture WHERE workorder_id=" + .db_escape($woid)." ORDER BY id"; return db_query($sql, "The work order issues could not be retrieved"); } @@ -84,7 +87,7 @@ function get_work_order_productions($woid) function exists_work_order_produce($id) { - $sql = "SELECT id FROM ".TB_PREF."wo_manufacture WHERE id=$id"; + $sql = "SELECT id FROM ".TB_PREF."wo_manufacture WHERE id=".db_escape($id); $result = db_query($sql, "Cannot retreive a wo production"); return (db_num_rows($result) > 0); @@ -102,7 +105,7 @@ function void_work_order_produce($type_no) work_order_update_finished_quantity($row["workorder_id"], -$row["quantity"]); // clear the production record - $sql = "UPDATE ".TB_PREF."wo_manufacture SET quantity=0 WHERE id=$type_no"; + $sql = "UPDATE ".TB_PREF."wo_manufacture SET quantity=0 WHERE id=".db_escape($type_no); db_query($sql, "Cannot void a wo production"); // void all related stock moves diff --git a/manufacturing/includes/db/work_order_requirements_db.inc b/manufacturing/includes/db/work_order_requirements_db.inc index 71f9e50a..ba53b5a2 100644 --- a/manufacturing/includes/db/work_order_requirements_db.inc +++ b/manufacturing/includes/db/work_order_requirements_db.inc @@ -15,9 +15,10 @@ function get_wo_requirements($woid) ".TB_PREF."stock_master.mb_flag, ".TB_PREF."locations.location_name, ".TB_PREF."workcentres.name AS WorkCentreDescription FROM - (".TB_PREF."wo_requirements, ".TB_PREF."locations, ".TB_PREF."workcentres) INNER JOIN ".TB_PREF."stock_master ON + (".TB_PREF."wo_requirements, ".TB_PREF."locations, " + .TB_PREF."workcentres) INNER JOIN ".TB_PREF."stock_master ON ".TB_PREF."wo_requirements.stock_id = ".TB_PREF."stock_master.stock_id - WHERE workorder_id=$woid + WHERE workorder_id=".db_escape($woid)." AND ".TB_PREF."locations.loc_code = ".TB_PREF."wo_requirements.loc_code AND ".TB_PREF."workcentres.id=workcentre"; @@ -35,7 +36,7 @@ function create_wo_requirements($woid, $stock_id) { $sql = "INSERT INTO ".TB_PREF."wo_requirements (workorder_id, stock_id, workcentre, units_req, loc_code) - VALUES ($woid, '" . + VALUES (".db_escape($woid).", '" . $myrow["component"] . "', '" . $myrow["workcentre_added"] . "', '" . $myrow["quantity"] . "', '" . @@ -49,7 +50,7 @@ function create_wo_requirements($woid, $stock_id) function delete_wo_requirements($woid) { - $sql="DELETE FROM ".TB_PREF."wo_requirements WHERE workorder_id=$woid"; + $sql="DELETE FROM ".TB_PREF."wo_requirements WHERE workorder_id=".db_escape($woid); db_query($sql,"The work order requirements could not be deleted"); } @@ -58,8 +59,8 @@ function delete_wo_requirements($woid) function update_wo_requirement_issued($woReqID, $quantity) { - $sql = "UPDATE ".TB_PREF."wo_requirements SET units_issued = units_issued + $quantity - WHERE id = '$woReqID'"; + $sql = "UPDATE ".TB_PREF."wo_requirements SET units_issued = units_issued + ".db_escape($quantity)." + WHERE id = ".db_escape($woReqID); db_query($sql, "The work requirements issued quantity couldn't be updated"); } @@ -68,8 +69,9 @@ function update_wo_requirement_issued($woReqID, $quantity) function void_wo_requirements($woid) { - $sql = "UPDATE ".TB_PREF."wo_requirements SET units_issued = 0 WHERE workorder_id = $woid"; - + $sql = "UPDATE ".TB_PREF."wo_requirements SET units_issued = 0 WHERE workorder_id = " + .db_escape($woid); + db_query($sql, "The work requirements issued quantity couldn't be voided"); } diff --git a/manufacturing/includes/db/work_orders_db.inc b/manufacturing/includes/db/work_orders_db.inc index 1c4fe7e2..8ca45b93 100644 --- a/manufacturing/includes/db/work_orders_db.inc +++ b/manufacturing/includes/db/work_orders_db.inc @@ -20,7 +20,8 @@ function add_material_cost($stock_id, $qty, $date_) $standard_cost = get_standard_cost($bom_item['component']); $m_cost += ($bom_item['quantity'] * $standard_cost); } - $sql = "SELECT material_cost FROM ".TB_PREF."stock_master WHERE stock_id = '$stock_id'"; + $sql = "SELECT material_cost FROM ".TB_PREF."stock_master WHERE stock_id = " + .db_escape($stock_id); $result = db_query($sql); $myrow = db_fetch($result); $material_cost = $myrow['material_cost']; @@ -30,7 +31,7 @@ function add_material_cost($stock_id, $qty, $date_) else $material_cost = ($qoh * $material_cost + $qty * $m_cost) / ($qoh + $qty); $sql = "UPDATE ".TB_PREF."stock_master SET material_cost=$material_cost - WHERE stock_id='$stock_id'"; + WHERE stock_id=".db_escape($stock_id); db_query($sql,"The cost details for the inventory item could not be updated"); } @@ -38,7 +39,8 @@ function add_overhead_cost($stock_id, $qty, $date_, $costs) { if ($qty != 0) $costs /= $qty; - $sql = "SELECT overhead_cost FROM ".TB_PREF."stock_master WHERE stock_id = '$stock_id'"; + $sql = "SELECT overhead_cost FROM ".TB_PREF."stock_master WHERE stock_id = " + .db_escape($stock_id); $result = db_query($sql); $myrow = db_fetch($result); $overhead_cost = $myrow['overhead_cost']; @@ -47,8 +49,8 @@ function add_overhead_cost($stock_id, $qty, $date_, $costs) $overhead_cost = 0; else $overhead_cost = ($qoh * $overhead_cost + $qty * $costs) / ($qoh + $qty); - $sql = "UPDATE ".TB_PREF."stock_master SET overhead_cost=$overhead_cost - WHERE stock_id='$stock_id'"; + $sql = "UPDATE ".TB_PREF."stock_master SET overhead_cost=".db_escape($overhead_cost)." + WHERE stock_id=".db_escape($stock_id); db_query($sql,"The cost details for the inventory item could not be updated"); } @@ -56,7 +58,8 @@ function add_labour_cost($stock_id, $qty, $date_, $costs) { if ($qty != 0) $costs /= $qty; - $sql = "SELECT labour_cost FROM ".TB_PREF."stock_master WHERE stock_id = '$stock_id'"; + $sql = "SELECT labour_cost FROM ".TB_PREF."stock_master WHERE stock_id = " + .db_escape($stock_id); $result = db_query($sql); $myrow = db_fetch($result); $labour_cost = $myrow['labour_cost']; @@ -65,8 +68,8 @@ function add_labour_cost($stock_id, $qty, $date_, $costs) $labour_cost = 0; else $labour_cost = ($qoh * $labour_cost + $qty * $costs) / ($qoh + $qty); - $sql = "UPDATE ".TB_PREF."stock_master SET labour_cost=$labour_cost - WHERE stock_id='$stock_id'"; + $sql = "UPDATE ".TB_PREF."stock_master SET labour_cost=".db_escape($labour_cost)." + WHERE stock_id=".db_escape($stock_id); db_query($sql,"The cost details for the inventory item could not be updated"); } @@ -74,7 +77,8 @@ function add_issue_cost($stock_id, $qty, $date_, $costs) { if ($qty != 0) $costs /= $qty; - $sql = "SELECT material_cost FROM ".TB_PREF."stock_master WHERE stock_id = '$stock_id'"; + $sql = "SELECT material_cost FROM ".TB_PREF."stock_master WHERE stock_id = " + .db_escape($stock_id); $result = db_query($sql); $myrow = db_fetch($result); $material_cost = $myrow['material_cost']; @@ -83,8 +87,9 @@ function add_issue_cost($stock_id, $qty, $date_, $costs) $material_cost = 0; else $material_cost = ($qty * $costs) / ($qoh + $qty); - $sql = "UPDATE ".TB_PREF."stock_master SET material_cost=material_cost+$material_cost - WHERE stock_id='$stock_id'"; + $sql = "UPDATE ".TB_PREF."stock_master SET material_cost=material_cost+" + .db_escape($material_cost) + ." WHERE stock_id=".db_escape($stock_id); db_query($sql,"The cost details for the inventory item could not be updated"); } @@ -103,8 +108,9 @@ function add_work_order($wo_ref, $loc_code, $units_reqd, $stock_id, $sql = "INSERT INTO ".TB_PREF."workorders (wo_ref, loc_code, units_reqd, stock_id, type, date_, required_by) - VALUES (".db_escape($wo_ref).", ".db_escape($loc_code).", $units_reqd, '$stock_id', - $type, '$date', '$required')"; + VALUES (".db_escape($wo_ref).", ".db_escape($loc_code).", " + .db_escape($units_reqd).", ".db_escape($stock_id).", + ".db_escape($type).", '$date', ".db_escape($required).")"; db_query($sql, "could not add work order"); $woid = db_insert_id(); @@ -132,10 +138,10 @@ function update_work_order($woid, $loc_code, $units_reqd, $stock_id, $required = date2sql($required_by); $sql = "UPDATE ".TB_PREF."workorders SET loc_code=".db_escape($loc_code).", - units_reqd=$units_reqd, stock_id='$stock_id', - required_by='$required', + units_reqd=".db_escape($units_reqd).", stock_id=".db_escape($stock_id).", + required_by=".db_escape($required).", date_='$date' - WHERE id = $woid"; + WHERE id = ".db_escape($woid); db_query($sql, "could not update work order"); @@ -154,7 +160,7 @@ function delete_work_order($woid) delete_wo_requirements($woid); // delete the actual work order - $sql = "DELETE FROM ".TB_PREF."workorders WHERE id=$woid"; + $sql = "DELETE FROM ".TB_PREF."workorders WHERE id=".db_escape($woid); db_query($sql,"The work order could not be deleted"); delete_comments(systypes::work_order(), $woid); @@ -171,7 +177,7 @@ function get_work_order($woid, $allow_null=false) FROM ".TB_PREF."workorders, ".TB_PREF."stock_master, ".TB_PREF."locations WHERE ".TB_PREF."stock_master.stock_id=".TB_PREF."workorders.stock_id AND ".TB_PREF."locations.loc_code=".TB_PREF."workorders.loc_code - AND ".TB_PREF."workorders.id=$woid + AND ".TB_PREF."workorders.id=".db_escape($woid)." GROUP BY ".TB_PREF."workorders.id"; $result = db_query($sql, "The work order issues could not be retrieved"); @@ -186,7 +192,7 @@ function get_work_order($woid, $allow_null=false) function work_order_has_productions($woid) { - $sql = "SELECT COUNT(*) FROM ".TB_PREF."wo_manufacture WHERE workorder_id=$woid"; + $sql = "SELECT COUNT(*) FROM ".TB_PREF."wo_manufacture WHERE workorder_id=".db_escape($woid); $result = db_query($sql, "query work order for productions"); $myrow = db_fetch_row($result); @@ -198,7 +204,7 @@ function work_order_has_productions($woid) function work_order_has_issues($woid) { - $sql = "SELECT COUNT(*) FROM ".TB_PREF."wo_issues WHERE workorder_id=$woid"; + $sql = "SELECT COUNT(*) FROM ".TB_PREF."wo_issues WHERE workorder_id=".db_escape($woid); $result = db_query($sql, "query work order for issues"); $myrow = db_fetch_row($result); @@ -226,7 +232,7 @@ function release_work_order($woid, $releaseDate, $memo_) $date = date2sql($releaseDate); $sql = "UPDATE ".TB_PREF."workorders SET released_date='$date', - released=1 WHERE id = $woid"; + released=1 WHERE id = ".db_escape($woid); db_query($sql, "could not release work order"); // create Work Order Requirements based on the bom @@ -241,7 +247,7 @@ function release_work_order($woid, $releaseDate, $memo_) function close_work_order($woid) { - $sql = "UPDATE ".TB_PREF."workorders SET closed=1 WHERE id = $woid"; + $sql = "UPDATE ".TB_PREF."workorders SET closed=1 WHERE id = ".db_escape($woid); db_query($sql, "could not close work order"); } @@ -249,7 +255,7 @@ function close_work_order($woid) function work_order_is_closed($woid) { - $sql = "SELECT closed FROM ".TB_PREF."workorders WHERE id = $woid"; + $sql = "SELECT closed FROM ".TB_PREF."workorders WHERE id = ".db_escape($woid); $result = db_query($sql, "could not query work order"); $row = db_fetch_row($result); return ($row[0] > 0); @@ -259,9 +265,9 @@ function work_order_is_closed($woid) function work_order_update_finished_quantity($woid, $quantity, $force_close=0) { - $sql = "UPDATE ".TB_PREF."workorders SET units_issued = units_issued + $quantity, - closed = ((units_issued >= units_reqd) OR $force_close) - WHERE id = $woid"; + $sql = "UPDATE ".TB_PREF."workorders SET units_issued = units_issued + ".db_escape($quantity).", + closed = ((units_issued >= units_reqd) OR ".db_escape($force_close).") + WHERE id = ".db_escape($woid); db_query($sql, "The work order issued quantity couldn't be updated"); } @@ -276,7 +282,8 @@ function void_work_order($woid) { begin_transaction(); - $sql = "UPDATE ".TB_PREF."workorders SET closed=1,units_issued=0 WHERE id = $woid"; + $sql = "UPDATE ".TB_PREF."workorders SET closed=1,units_issued=0 WHERE id = " + .db_escape($woid); db_query($sql, "The work order couldn't be voided"); // void all related stock moves diff --git a/manufacturing/includes/db/work_orders_quick_db.inc b/manufacturing/includes/db/work_orders_quick_db.inc index 6e361013..152c586d 100644 --- a/manufacturing/includes/db/work_orders_quick_db.inc +++ b/manufacturing/includes/db/work_orders_quick_db.inc @@ -31,8 +31,9 @@ function add_work_order_quick($wo_ref, $loc_code, $units_reqd, $stock_id, $type, $sql = "INSERT INTO ".TB_PREF."workorders (wo_ref, loc_code, units_reqd, units_issued, stock_id, type, additional_costs, date_, released_date, required_by, released, closed) - VALUES (".db_escape($wo_ref).", ".db_escape($loc_code).", $units_reqd, $units_reqd, '$stock_id', - $type, $costs, '$date', '$date', '$date', 1, 1)"; + VALUES (".db_escape($wo_ref).", ".db_escape($loc_code).", ".db_escape($units_reqd) + .", ".db_escape($units_reqd).", ".db_escape($stock_id).", + ".db_escape($type).", ".db_escape($costs).", '$date', '$date', '$date', 1, 1)"; db_query($sql, "could not add work order"); $woid = db_insert_id(); diff --git a/manufacturing/inquiry/where_used_inquiry.php b/manufacturing/inquiry/where_used_inquiry.php index c792330b..c8ee4125 100644 --- a/manufacturing/inquiry/where_used_inquiry.php +++ b/manufacturing/inquiry/where_used_inquiry.php @@ -50,7 +50,7 @@ $sql = "SELECT WHERE bom.parent = parent.stock_id AND bom.workcentre_added = workcentre.id AND bom.loc_code = location.loc_code - AND bom.component='" . $_POST['stock_id'] . "'"; + AND bom.component=".db_escape($_POST['stock_id']); $cols = array( _("Parent Item") => array('fun'=>'select_link'), diff --git a/manufacturing/manage/bom_edit.php b/manufacturing/manage/bom_edit.php index f3ace9fd..e771878c 100644 --- a/manufacturing/manage/bom_edit.php +++ b/manufacturing/manage/bom_edit.php @@ -68,7 +68,7 @@ function check_for_recursive_bom($ultimate_parent, $component_to_check) /* returns true ie 1 if the bom contains the parent part as a component ie the bom is recursive otherwise false ie 0 */ - $sql = "SELECT component FROM ".TB_PREF."bom WHERE parent='$component_to_check'"; + $sql = "SELECT component FROM ".TB_PREF."bom WHERE parent=".db_escape($component_to_check); $result = db_query($sql,"could not check recursive bom"); if ($result != 0) @@ -139,11 +139,11 @@ function on_submit($selected_parent, $selected_component=-1) if ($selected_component != -1) { - $sql = "UPDATE ".TB_PREF."bom SET workcentre_added='" . $_POST['workcentre_added'] . "', - loc_code='" . $_POST['loc_code'] . "', + $sql = "UPDATE ".TB_PREF."bom SET workcentre_added=".db_escape($_POST['workcentre_added']) + . ",loc_code=".db_escape($_POST['loc_code']) . ", quantity= " . input_num('quantity') . " - WHERE parent='" . $selected_parent . "' - AND id='" . $selected_component . "'"; + WHERE parent=".db_escape($selected_parent) . " + AND id=".db_escape($selected_component); check_db_error("Could not update this bom component", $sql); db_query($sql,"could not update bom"); @@ -163,17 +163,17 @@ function on_submit($selected_parent, $selected_component=-1) /*Now check to see that the component is not already on the bom */ $sql = "SELECT component FROM ".TB_PREF."bom - WHERE parent='$selected_parent' - AND component='" . $_POST['component'] . "' - AND workcentre_added='" . $_POST['workcentre_added'] . "' - AND loc_code='" . $_POST['loc_code'] . "'" ; + WHERE parent=".db_escape($selected_parent)." + AND component=".db_escape($_POST['component']) . " + AND workcentre_added=".db_escape($_POST['workcentre_added']) . " + AND loc_code=".db_escape($_POST['loc_code']); $result = db_query($sql,"check failed"); if (db_num_rows($result) == 0) { $sql = "INSERT INTO ".TB_PREF."bom (parent, component, workcentre_added, loc_code, quantity) - VALUES ('$selected_parent', '" . $_POST['component'] . "', '" - . $_POST['workcentre_added'] . "', '" . $_POST['loc_code'] . "', " + VALUES (".db_escape($selected_parent).", ".db_escape($_POST['component']) . "," + .db_escape($_POST['workcentre_added']) . ", ".db_escape($_POST['loc_code']) . ", " . input_num('quantity') . ")"; db_query($sql,"check failed"); @@ -198,7 +198,7 @@ function on_submit($selected_parent, $selected_component=-1) if ($Mode == 'Delete') { - $sql = "DELETE FROM ".TB_PREF."bom WHERE id='" . $selected_id. "'"; + $sql = "DELETE FROM ".TB_PREF."bom WHERE id=".db_escape($selected_id); db_query($sql,"Could not delete this bom components"); display_notification(_("The component item has been deleted from this bom")); @@ -246,8 +246,9 @@ start_form(); { if ($Mode == 'Edit') { //editing a selected component from the link to the line item - $sql = "SELECT ".TB_PREF."bom.*,".TB_PREF."stock_master.description FROM ".TB_PREF."bom,".TB_PREF."stock_master - WHERE id='$selected_id' + $sql = "SELECT ".TB_PREF."bom.*,".TB_PREF."stock_master.description FROM " + .TB_PREF."bom,".TB_PREF."stock_master + WHERE id=".db_escape($selected_id)." AND ".TB_PREF."stock_master.stock_id=".TB_PREF."bom.component"; $result = db_query($sql, "could not get bom"); diff --git a/manufacturing/manage/work_centres.php b/manufacturing/manage/work_centres.php index 19227df8..017b96c8 100644 --- a/manufacturing/manage/work_centres.php +++ b/manufacturing/manage/work_centres.php @@ -56,7 +56,7 @@ if ($Mode=='ADD_ITEM' || $Mode=='UPDATE_ITEM') function can_delete($selected_id) { - $sql= "SELECT COUNT(*) FROM ".TB_PREF."bom WHERE workcentre_added='$selected_id'"; + $sql= "SELECT COUNT(*) FROM ".TB_PREF."bom WHERE workcentre_added=".db_escape($selected_id); $result = db_query($sql, "check can delete work centre"); $myrow = db_fetch_row($result); if ($myrow[0] > 0) @@ -65,7 +65,7 @@ function can_delete($selected_id) return false; } - $sql= "SELECT COUNT(*) FROM ".TB_PREF."wo_requirements WHERE workcentre='$selected_id'"; + $sql= "SELECT COUNT(*) FROM ".TB_PREF."wo_requirements WHERE workcentre=".db_escape($selected_id); $result = db_query($sql, "check can delete work centre"); $myrow = db_fetch_row($result); if ($myrow[0] > 0) diff --git a/manufacturing/search_work_orders.php b/manufacturing/search_work_orders.php index ea3aae22..447dbd07 100644 --- a/manufacturing/search_work_orders.php +++ b/manufacturing/search_work_orders.php @@ -183,17 +183,17 @@ if (check_value('OpenOnly') || $outstanding_only != 0) if (isset($_POST['StockLocation']) && $_POST['StockLocation'] != $all_items) { - $sql .= " AND workorder.loc_code='" . $_POST['StockLocation'] . "' "; + $sql .= " AND workorder.loc_code=".db_escape($_POST['StockLocation']); } if (isset($_POST['OrderNumber']) && $_POST['OrderNumber'] != "") { - $sql .= " AND workorder.wo_ref LIKE '%". $_POST['OrderNumber'] . "%'"; + $sql .= " AND workorder.wo_ref LIKE ".db_escape('%'.$_POST['OrderNumber'].'%'); } if (isset($_POST['SelectedStockItem']) && $_POST['SelectedStockItem'] != $all_items) { - $sql .= " AND workorder.stock_id='". $_POST['SelectedStockItem'] . "'"; + $sql .= " AND workorder.stock_id=".db_escape($_POST['SelectedStockItem']); } if (check_value('OverdueOnly')) diff --git a/purchasing/includes/db/grn_db.inc b/purchasing/includes/db/grn_db.inc index 23ca2e22..48d941b2 100644 --- a/purchasing/includes/db/grn_db.inc +++ b/purchasing/includes/db/grn_db.inc @@ -21,7 +21,7 @@ function update_average_material_cost($supplier, $stock_id, $price, $qty, $date, $price_in_home_currency = to_home_currency($price, $currency, $date); else $price_in_home_currency = $price; - $sql = "SELECT material_cost FROM ".TB_PREF."stock_master WHERE stock_id='$stock_id'"; + $sql = "SELECT material_cost FROM ".TB_PREF."stock_master WHERE stock_id=".db_escape($stock_id); $result = db_query($sql); $myrow = db_fetch($result); $material_cost = $myrow['material_cost']; @@ -44,7 +44,7 @@ function update_average_material_cost($supplier, $stock_id, $price, $qty, $date, $material_cost = ($qoh * $material_cost + $qty * $price_in_home_currency) / ($qoh + $qty); $sql = "UPDATE ".TB_PREF."stock_master SET material_cost=".db_escape($material_cost)." - WHERE stock_id='$stock_id'"; + WHERE stock_id=".db_escape($stock_id); db_query($sql,"The cost details for the inventory item could not be updated"); return $material_cost; } @@ -121,15 +121,17 @@ function add_grn_detail_item($grn_batch_id, $po_detail_item, $item_code, $descri $quantity_received, $price) { $sql = "UPDATE ".TB_PREF."purch_order_details - SET quantity_received = quantity_received + $quantity_received, - std_cost_unit=$standard_unit_cost, - act_price=$price - WHERE po_detail_item = $po_detail_item"; + SET quantity_received = quantity_received + ".db_escape($quantity_received).", + std_cost_unit=".db_escape($standard_unit_cost).", + act_price=".db_escape($price)." + WHERE po_detail_item = ".db_escape($po_detail_item); db_query($sql, "a purchase order details record could not be updated. This receipt of goods has not been processed "); $sql = "INSERT INTO ".TB_PREF."grn_items (grn_batch_id, po_detail_item, item_code, description, qty_recd) - VALUES ($grn_batch_id, $po_detail_item, ".db_escape($item_code).", ".db_escape($description).", $quantity_received)"; + VALUES (".db_escape($grn_batch_id).", " + .db_escape($po_detail_item).", ".db_escape($item_code).", ".db_escape($description) + .", ".db_escape($quantity_received).")"; db_query($sql, "A GRN detail item could not be inserted."); @@ -139,7 +141,7 @@ function add_grn_detail_item($grn_batch_id, $po_detail_item, $item_code, $descri //---------------------------------------------------------------------------------------- function get_grn_batch_from_item($item) { - $sql = "SELECT grn_batch_id FROM ".TB_PREF."grn_items WHERE id=$item"; + $sql = "SELECT grn_batch_id FROM ".TB_PREF."grn_items WHERE id=".db_escape($item); $result = db_query($sql, "Could not retreive GRN batch id"); $row = db_fetch_row($result); return $row[0]; @@ -147,7 +149,7 @@ function get_grn_batch_from_item($item) function get_grn_batch($grn) { - $sql = "SELECT * FROM ".TB_PREF."grn_batch WHERE id=$grn"; + $sql = "SELECT * FROM ".TB_PREF."grn_batch WHERE id=".db_escape($grn); $result = db_query($sql, "Could not retreive GRN batch id"); return db_fetch($result); } @@ -160,23 +162,26 @@ function set_grn_item_credited(&$entered_grn, $supplier, $transno, $date) $sql = "SELECT ".TB_PREF."grn_batch.*, ".TB_PREF."grn_items.* FROM ".TB_PREF."grn_batch, ".TB_PREF."grn_items WHERE ".TB_PREF."grn_items.grn_batch_id=".TB_PREF."grn_batch.id - AND ".TB_PREF."grn_items.id=$entered_grn->id - AND ".TB_PREF."grn_items.item_code='$entered_grn->item_code' "; + AND ".TB_PREF."grn_items.id=".db_escape($entered_grn->id)." + AND ".TB_PREF."grn_items.item_code=".db_escape($entered_grn->item_code); $result = db_query($sql, "Could not retreive GRNS"); $myrow = db_fetch($result); $sql = "UPDATE ".TB_PREF."purch_order_details - SET quantity_received = quantity_received + $entered_grn->this_quantity_inv, - quantity_ordered = quantity_ordered + $entered_grn->this_quantity_inv, - qty_invoiced = qty_invoiced + $entered_grn->this_quantity_inv, - std_cost_unit=$mcost, - act_price=$entered_grn->chg_price + SET quantity_received = quantity_received + " + .db_escape($entered_grn->this_quantity_inv).", + quantity_ordered = quantity_ordered + " + .db_escape($entered_grn->this_quantity_inv).", + qty_invoiced = qty_invoiced + ".db_escape($entered_grn->this_quantity_inv).", + std_cost_unit=".db_escape($mcost).", + act_price=".db_escape($entered_grn->chg_price)." WHERE po_detail_item = ".$myrow["po_detail_item"]; db_query($sql, "a purchase order details record could not be updated. This receipt of goods has not been processed "); //$sql = "UPDATE ".TB_PREF."grn_items SET qty_recd=0, quantity_inv=0 WHERE id=$entered_grn->id"; - $sql = "UPDATE ".TB_PREF."grn_items SET qty_recd=qty_recd+$entered_grn->this_quantity_inv, - quantity_inv=quantity_inv+$entered_grn->this_quantity_inv WHERE id=$entered_grn->id"; + $sql = "UPDATE ".TB_PREF."grn_items SET qty_recd=qty_recd+".db_escape($entered_grn->this_quantity_inv) + .",quantity_inv=quantity_inv+".db_escape($entered_grn->this_quantity_inv) + ." WHERE id=".db_escape($entered_grn->id); db_query($sql); add_stock_move(21, $entered_grn->item_code, $transno, $myrow['loc_code'], $date, "", @@ -186,9 +191,11 @@ function set_grn_item_credited(&$entered_grn, $supplier, $transno, $date) function get_grn_items($grn_batch_id=0, $supplier_id="", $outstanding_only=false, $is_invoiced_only=false, $invoice_no=0, $begin="", $end="") { - $sql = "SELECT ".TB_PREF."grn_batch.*, ".TB_PREF."grn_items.*, ".TB_PREF."purch_order_details.unit_price, + $sql = "SELECT ".TB_PREF."grn_batch.*, ".TB_PREF."grn_items.*, " + .TB_PREF."purch_order_details.unit_price, ".TB_PREF."purch_order_details.std_cost_unit, units - FROM ".TB_PREF."grn_batch, ".TB_PREF."grn_items, ".TB_PREF."purch_order_details, ".TB_PREF."stock_master"; + FROM ".TB_PREF."grn_batch, ".TB_PREF."grn_items, " + .TB_PREF."purch_order_details, ".TB_PREF."stock_master"; if ($invoice_no != 0) $sql .= ", ".TB_PREF."supp_invoice_items"; $sql .= " WHERE ".TB_PREF."grn_items.grn_batch_id=".TB_PREF."grn_batch.id @@ -204,7 +211,8 @@ function get_grn_items($grn_batch_id=0, $supplier_id="", $outstanding_only=false if ($end != "") $sql .= " AND ".TB_PREF."grn_batch.delivery_date<='".date2sql($end)."'"; if ($grn_batch_id != 0) - $sql .= " AND ".TB_PREF."grn_batch.id=$grn_batch_id AND ".TB_PREF."grn_items.grn_batch_id=$grn_batch_id "; + $sql .= " AND ".TB_PREF."grn_batch.id=".db_escape($grn_batch_id) + ." AND ".TB_PREF."grn_items.grn_batch_id=".db_escape($grn_batch_id); if ($is_invoiced_only) $sql .= " AND ".TB_PREF."grn_items.quantity_inv > 0"; @@ -213,7 +221,7 @@ function get_grn_items($grn_batch_id=0, $supplier_id="", $outstanding_only=false $sql .= " AND ".TB_PREF."grn_items.qty_recd - ".TB_PREF."grn_items.quantity_inv > 0"; if ($supplier_id != "") - $sql .= " AND ".TB_PREF."grn_batch.supplier_id ='$supplier_id' "; + $sql .= " AND ".TB_PREF."grn_batch.supplier_id =".db_escape($supplier_id); $sql .= " ORDER BY ".TB_PREF."grn_batch.delivery_date, ".TB_PREF."grn_batch.id, ".TB_PREF."grn_items.id"; @@ -232,7 +240,7 @@ function get_grn_item_detail($grn_item_no) FROM ".TB_PREF."grn_items, ".TB_PREF."purch_order_details, ".TB_PREF."stock_master WHERE ".TB_PREF."grn_items.po_detail_item=".TB_PREF."purch_order_details.po_detail_item AND ".TB_PREF."stock_master.stock_id=".TB_PREF."grn_items.item_code - AND ".TB_PREF."grn_items.id=$grn_item_no"; + AND ".TB_PREF."grn_items.id=".db_escape($grn_item_no); $result = db_query($sql, "could not retreive grn item details"); return db_fetch($result); @@ -275,7 +283,7 @@ function read_grn_items_to_order($grn_batch, &$order) function read_grn($grn_batch, &$order) { - $sql= "SELECT * FROM ".TB_PREF."grn_batch WHERE id=$grn_batch"; + $sql= "SELECT * FROM ".TB_PREF."grn_batch WHERE id=".db_escape($grn_batch); $result = db_query($sql, "The grn sent is not valid"); @@ -301,7 +309,7 @@ function read_grn($grn_batch, &$order) function get_po_grns($po_number) { - $sql = "SELECT * FROM ".TB_PREF."grn_batch WHERE purch_order_no=$po_number"; + $sql = "SELECT * FROM ".TB_PREF."grn_batch WHERE purch_order_no=".db_escape($po_number); return db_query($sql, "The grns for the po $po_number could not be retreived"); } @@ -310,7 +318,7 @@ function get_po_grns($po_number) function exists_grn($grn_batch) { - $sql = "SELECT id FROM ".TB_PREF."grn_batch WHERE id=$grn_batch"; + $sql = "SELECT id FROM ".TB_PREF."grn_batch WHERE id=".db_escape($grn_batch); $result = db_query($sql, "Cannot retreive a grn"); return (db_num_rows($result) > 0); @@ -323,7 +331,7 @@ function exists_grn_on_invoices($grn_batch) $sql = "SELECT ".TB_PREF."supp_invoice_items.id FROM ".TB_PREF."supp_invoice_items,".TB_PREF."grn_items WHERE ".TB_PREF."supp_invoice_items.grn_item_id=".TB_PREF."grn_items.id AND quantity != 0 - AND grn_batch_id=$grn_batch"; + AND grn_batch_id=".db_escape($grn_batch); $result = db_query($sql, "Cannot query GRNs"); return (db_num_rows($result) > 0); @@ -362,7 +370,7 @@ function void_grn($grn_batch) // clear the quantities in the grn items $sql = "UPDATE ".TB_PREF."grn_items SET qty_recd=0, quantity_inv=0 - WHERE grn_batch_id=$grn_batch"; + WHERE grn_batch_id=".db_escape($grn_batch); db_query($sql, "A grn detail item could not be voided."); diff --git a/purchasing/includes/db/invoice_db.inc b/purchasing/includes/db/invoice_db.inc index 4703f802..cc5c8549 100644 --- a/purchasing/includes/db/invoice_db.inc +++ b/purchasing/includes/db/invoice_db.inc @@ -15,13 +15,14 @@ include_once($path_to_root . "/purchasing/includes/db/invoice_items_db.inc"); function read_supplier_details_to_trans(&$supp_trans, $supplier_id) { - $sql = "SELECT ".TB_PREF."suppliers.supp_name, ".TB_PREF."payment_terms.terms, ".TB_PREF."payment_terms.days_before_due, + $sql = "SELECT ".TB_PREF."suppliers.supp_name, ".TB_PREF."payment_terms.terms, " + .TB_PREF."payment_terms.days_before_due, ".TB_PREF."payment_terms.day_in_following_month, ".TB_PREF."suppliers.tax_group_id, ".TB_PREF."tax_groups.name As tax_group_name From ".TB_PREF."suppliers, ".TB_PREF."payment_terms, ".TB_PREF."tax_groups WHERE ".TB_PREF."suppliers.tax_group_id = ".TB_PREF."tax_groups.id AND ".TB_PREF."suppliers.payment_terms=".TB_PREF."payment_terms.terms_indicator - AND ".TB_PREF."suppliers.supplier_id = '" . $supplier_id . "'"; + AND ".TB_PREF."suppliers.supplier_id = ".db_escape($supplier_id); $result = db_query($sql, "The supplier record selected: " . $supplier_id . " cannot be retrieved"); @@ -61,7 +62,7 @@ function update_supp_received_items_for_invoice($id, $po_detail_item, $qty_invoi if ($chg_price != null) { $sql = "SELECT act_price, unit_price FROM ".TB_PREF."purch_order_details WHERE - po_detail_item = $po_detail_item"; + po_detail_item = ".db_escape($po_detail_item); $result = db_query($sql, "The old actual price of the purchase order line could not be retrieved"); $row = db_fetch_row($result); $ret = $row[0]; @@ -69,7 +70,8 @@ function update_supp_received_items_for_invoice($id, $po_detail_item, $qty_invoi $unit_price = $row[1]; //Added by Rasmus $sql = "SELECT delivery_date FROM ".TB_PREF."grn_batch,".TB_PREF."grn_items WHERE - ".TB_PREF."grn_batch.id = ".TB_PREF."grn_items.grn_batch_id AND ".TB_PREF."grn_items.id=$id"; + ".TB_PREF."grn_batch.id = ".TB_PREF."grn_items.grn_batch_id AND " + .TB_PREF."grn_items.id=".db_escape($id); $result = db_query($sql, "The old delivery date from the received record cout not be retrieved"); $row = db_fetch_row($result); $date = $row[0]; @@ -81,17 +83,17 @@ function update_supp_received_items_for_invoice($id, $po_detail_item, $qty_invoi $unit_price = 0; // Added by Rasmus } $sql = "UPDATE ".TB_PREF."purch_order_details - SET qty_invoiced = qty_invoiced + $qty_invoiced "; + SET qty_invoiced = qty_invoiced + ".db_escape($qty_invoiced); if ($chg_price != null) - $sql .= " , act_price = $chg_price "; + $sql .= " , act_price = ".db_escape($chg_price); - $sql .= " WHERE po_detail_item = $po_detail_item"; + $sql .= " WHERE po_detail_item = ".db_escape($po_detail_item); db_query($sql, "The quantity invoiced of the purchase order line could not be updated"); $sql = "UPDATE ".TB_PREF."grn_items - SET quantity_inv = quantity_inv + $qty_invoiced - WHERE id = $id"; + SET quantity_inv = quantity_inv + ".db_escape($qty_invoiced)." + WHERE id = ".db_escape($id); db_query($sql, "The quantity invoiced off the items received record could not be updated"); return array($ret, $date, $unit_price); } @@ -101,7 +103,7 @@ function get_deliveries_between($stock_id, $from, $to) $from = date2sql($from); $to = date2sql($to); $sql = "SELECT SUM(-qty), SUM(-qty*standard_cost) FROM ".TB_PREF."stock_moves - WHERE type=13 AND stock_id='$stock_id' AND + WHERE type=13 AND stock_id=".db_escape($stock_id)." AND tran_date>='$from' AND tran_date<='$to' GROUP BY stock_id"; $result = db_query($sql, "The deliveries could not be updated"); return db_fetch_row($result); @@ -338,11 +340,12 @@ function get_po_invoices_credits($po_number) $sql = "SELECT DISTINCT ".TB_PREF."supp_trans.trans_no, ".TB_PREF."supp_trans.type, ov_amount+ov_discount+ov_gst AS Total, ".TB_PREF."supp_trans.tran_date - FROM ".TB_PREF."supp_trans, ".TB_PREF."supp_invoice_items, ".TB_PREF."purch_order_details, ".TB_PREF."purch_orders + FROM ".TB_PREF."supp_trans, ".TB_PREF."supp_invoice_items, " + .TB_PREF."purch_order_details, ".TB_PREF."purch_orders WHERE ".TB_PREF."supp_invoice_items.supp_trans_no = ".TB_PREF."supp_trans.trans_no AND ".TB_PREF."supp_invoice_items.po_detail_item_id = ".TB_PREF."purch_order_details.po_detail_item AND ".TB_PREF."purch_orders.supplier_id = ".TB_PREF."supp_trans.supplier_id - AND ".TB_PREF."purch_order_details.order_no = $po_number"; + AND ".TB_PREF."purch_order_details.order_no = ".db_escape($po_number); return db_query($sql, "The invoices/credits for the po $po_number could not be retreived"); } @@ -352,7 +355,7 @@ function get_po_invoices_credits($po_number) function read_supp_invoice($trans_no, $trans_type, &$supp_trans) { $sql = "SELECT ".TB_PREF."supp_trans.*, supp_name FROM ".TB_PREF."supp_trans,".TB_PREF."suppliers - WHERE trans_no = $trans_no AND type = $trans_type + WHERE trans_no = ".db_escape($trans_no)." AND type = ".db_escape($trans_type)." AND ".TB_PREF."suppliers.supplier_id=".TB_PREF."supp_trans.supplier_id"; $result = db_query($sql, "Cannot retreive a supplier transaction"); @@ -412,7 +415,8 @@ function read_supp_invoice($trans_no, $trans_type, &$supp_trans) function get_matching_invoice_item($stock_id, $po_item_id) { $sql = "SELECT *, tran_date FROM ".TB_PREF."supp_invoice_items, ".TB_PREF."supp_trans - WHERE supp_trans_type = 20 AND stock_id = '$stock_id' AND po_detail_item_id = $po_item_id + WHERE supp_trans_type = 20 AND stock_id = " + .db_escape($stock_id)." AND po_detail_item_id = ".db_escape($po_item_id)." AND supp_trans_no = trans_no"; $result = db_query($sql, "Cannot retreive supplier transaction detail records"); return db_fetch($result); diff --git a/purchasing/includes/db/invoice_items_db.inc b/purchasing/includes/db/invoice_items_db.inc index 0d69d868..efcdcb4b 100644 --- a/purchasing/includes/db/invoice_items_db.inc +++ b/purchasing/includes/db/invoice_items_db.inc @@ -17,9 +17,11 @@ function add_supp_invoice_item($supp_trans_type, $supp_trans_no, $stock_id, $des { $sql = "INSERT INTO ".TB_PREF."supp_invoice_items (supp_trans_type, supp_trans_no, stock_id, description, gl_code, unit_price, unit_tax, quantity, grn_item_id, po_detail_item_id, memo_) "; - $sql .= "VALUES ($supp_trans_type, $supp_trans_no, ".db_escape($stock_id). - ", ".db_escape($description).", ".db_escape($gl_code).", $unit_price, $unit_tax, $quantity, - $grn_item_id, $po_detail_item_id, ".db_escape($memo_).")"; + $sql .= "VALUES (".db_escape($supp_trans_type).", ".db_escape($supp_trans_no).", " + .db_escape($stock_id). + ", ".db_escape($description).", ".db_escape($gl_code).", ".db_escape($unit_price) + .", ".db_escape($unit_tax).", ".db_escape($quantity).", + ".db_escape($grn_item_id).", ".db_escape($po_detail_item_id).", ".db_escape($memo_).")"; if ($err_msg == "") $err_msg = "Cannot insert a supplier transaction detail record"; @@ -43,8 +45,8 @@ function add_supp_invoice_gl_item($supp_trans_type, $supp_trans_no, $gl_code, $a function get_supp_invoice_items($supp_trans_type, $supp_trans_no) { $sql = "SELECT *, unit_price AS FullUnitPrice FROM ".TB_PREF."supp_invoice_items - WHERE supp_trans_type = $supp_trans_type - AND supp_trans_no = $supp_trans_no ORDER BY id"; + WHERE supp_trans_type = ".db_escape($supp_trans_type)." + AND supp_trans_no = ".db_escape($supp_trans_no)." ORDER BY id"; return db_query($sql, "Cannot retreive supplier transaction detail records"); } @@ -53,7 +55,7 @@ function get_supp_invoice_items($supp_trans_type, $supp_trans_no) function void_supp_invoice_items($type, $type_no) { $sql = "UPDATE ".TB_PREF."supp_invoice_items SET quantity=0, unit_price=0 - WHERE supp_trans_type = $type AND supp_trans_no=$type_no"; + WHERE supp_trans_type = ".db_escape($type)." AND supp_trans_no=".db_escape($type_no); db_query($sql, "could not void supptrans details"); } diff --git a/purchasing/includes/db/po_db.inc b/purchasing/includes/db/po_db.inc index e13bea1a..0116e9fb 100644 --- a/purchasing/includes/db/po_db.inc +++ b/purchasing/includes/db/po_db.inc @@ -13,10 +13,10 @@ function delete_po($po) { - $sql = "DELETE FROM ".TB_PREF."purch_orders WHERE order_no=" . $po; + $sql = "DELETE FROM ".TB_PREF."purch_orders WHERE order_no=".db_escape($po); db_query($sql, "The order header could not be deleted"); - $sql = "DELETE FROM ".TB_PREF."purch_order_details WHERE order_no =" . $po; + $sql = "DELETE FROM ".TB_PREF."purch_order_details WHERE order_no =".db_escape($po); db_query($sql, "The order detail lines could not be deleted"); } @@ -30,8 +30,8 @@ function add_po(&$po_obj) $sql = "INSERT INTO ".TB_PREF."purch_orders (supplier_id, Comments, ord_date, reference, requisition_no, into_stock_location, delivery_address) VALUES("; $sql .= db_escape($po_obj->supplier_id) . "," . db_escape($po_obj->Comments) . ",'" . - date2sql($po_obj->orig_order_date) . "', '" . - $po_obj->reference . "', " . + date2sql($po_obj->orig_order_date) . "', " . + db_escape($po_obj->reference) . ", " . db_escape($po_obj->requisition_no) . ", " . db_escape($po_obj->Location) . ", " . db_escape($po_obj->delivery_address) . ")"; @@ -50,8 +50,8 @@ function add_po(&$po_obj) $sql .= $po_obj->order_no . ", " . db_escape($po_line->stock_id). "," . db_escape($po_line->item_description). ",'" . date2sql($po_line->req_del_date) . "'," . - $po_line->price . ", " . - $po_line->quantity . ")"; + db_escape($po_line->price) . ", " . + db_escape($po_line->quantity). ")"; db_query($sql, "One of the purchase order detail records could not be inserted"); } } @@ -89,7 +89,7 @@ function update_po(&$po_obj) // Sherifoz 21.06.03 Handle deleting existing lines if ($po_line->po_detail_rec!='') { - $sql = "DELETE FROM ".TB_PREF."purch_order_details WHERE po_detail_item='" . $po_line->po_detail_rec . "'"; + $sql = "DELETE FROM ".TB_PREF."purch_order_details WHERE po_detail_item=".db_escape($po_line->po_detail_rec); db_query($sql, "could not query purch order details"); } } @@ -101,16 +101,16 @@ function update_po(&$po_obj) db_escape($po_line->stock_id). "," . db_escape($po_line->item_description). ",'" . date2sql($po_line->req_del_date) . "'," . - $po_line->price . ", " . $po_line->quantity . ")"; + db_escape($po_line->price) . ", ".db_escape($po_line->quantity) . ")"; } else { - $sql = "UPDATE ".TB_PREF."purch_order_details SET item_code='" . $po_line->stock_id . "', + $sql = "UPDATE ".TB_PREF."purch_order_details SET item_code=".db_escape($po_line->stock_id).", description =" . db_escape($po_line->item_description). ", delivery_date ='" . date2sql($po_line->req_del_date) . "', - unit_price=" . $po_line->price . ", - quantity_ordered=" . $po_line->quantity . " - WHERE po_detail_item=" . $po_line->po_detail_rec; + unit_price=".db_escape($po_line->price).", + quantity_ordered=".db_escape($po_line->quantity) . " + WHERE po_detail_item=".db_escape($po_line->po_detail_rec); } db_query($sql, "One of the purchase order detail records could not be updated"); } @@ -131,7 +131,7 @@ function read_po_header($order_no, &$order) FROM ".TB_PREF."purch_orders, ".TB_PREF."suppliers, ".TB_PREF."locations WHERE ".TB_PREF."purch_orders.supplier_id = ".TB_PREF."suppliers.supplier_id AND ".TB_PREF."locations.loc_code = into_stock_location - AND ".TB_PREF."purch_orders.order_no = " . $order_no; + AND ".TB_PREF."purch_orders.order_no = ".db_escape($order_no); $result = db_query($sql, "The order cannot be retrieved"); @@ -169,7 +169,7 @@ function read_po_items($order_no, &$order, $open_items_only=false) FROM ".TB_PREF."purch_order_details LEFT JOIN ".TB_PREF."stock_master ON ".TB_PREF."purch_order_details.item_code=".TB_PREF."stock_master.stock_id - WHERE order_no =$order_no "; + WHERE order_no =".db_escape($order_no); if ($open_items_only) $sql .= " AND (".TB_PREF."purch_order_details.quantity_ordered > ".TB_PREF."purch_order_details.quantity_received) "; @@ -183,6 +183,7 @@ function read_po_items($order_no, &$order, $open_items_only=false) while ($myrow = db_fetch($result)) { + $data = get_purchase_data($order->supplier_id, $myrow['item_code']); if ($data !== false) { @@ -200,13 +201,13 @@ function read_po_items($order_no, &$order, $open_items_only=false) $units = $myrow["units"]; } - $order->add_to_order($order->lines_on_order+1, $myrow["item_code"], + if ($order->add_to_order($order->lines_on_order+1, $myrow["item_code"], $myrow["quantity_ordered"],$myrow["description"], $myrow["unit_price"],$units, sql2date($myrow["delivery_date"]), - $myrow["qty_invoiced"], $myrow["quantity_received"]); - - $order->line_items[$order->lines_on_order]->po_detail_rec = $myrow["po_detail_item"]; - $order->line_items[$order->lines_on_order]->standard_cost = $myrow["std_cost_unit"]; /*Needed for receiving goods and GL interface */ + $myrow["qty_invoiced"], $myrow["quantity_received"])) { + $order->line_items[$order->lines_on_order]->po_detail_rec = $myrow["po_detail_item"]; + $order->line_items[$order->lines_on_order]->standard_cost = $myrow["std_cost_unit"]; /*Needed for receiving goods and GL interface */ + } } /* line po from purchase order details */ } //end of checks on returned data set } diff --git a/purchasing/includes/db/supp_trans_db.inc b/purchasing/includes/db/supp_trans_db.inc index 207cd7dc..9c51f904 100644 --- a/purchasing/includes/db/supp_trans_db.inc +++ b/purchasing/includes/db/supp_trans_db.inc @@ -30,8 +30,10 @@ function add_supp_trans($type, $supplier_id, $date_, $due_date, $reference, $sup $sql = "INSERT INTO ".TB_PREF."supp_trans (trans_no, type, supplier_id, tran_date, due_date, reference, supp_reference, ov_amount, ov_gst, rate, ov_discount) "; - $sql .= "VALUES ($trans_no, $type, $supplier_id, '$date', '$due_date', - ".db_escape($reference).", ".db_escape($supp_reference).", $amount, $amount_tax, $rate, $discount)"; + $sql .= "VALUES (".db_escape($trans_no).", ".db_escape($type) + .", ".db_escape($supplier_id).", '$date', '$due_date', + ".db_escape($reference).", ".db_escape($supp_reference).", ".db_escape($amount) + .", ".db_escape($amount_tax).", ".db_escape($rate).", ".db_escape($discount).")"; if ($err_msg == "") $err_msg = "Cannot insert a supplier transaction record"; @@ -64,17 +66,17 @@ function get_supp_trans($trans_no, $trans_type=-1) $sql .= ", ".TB_PREF."bank_trans, ".TB_PREF."bank_accounts"; } - $sql .= " WHERE ".TB_PREF."supp_trans.trans_no=$trans_no + $sql .= " WHERE ".TB_PREF."supp_trans.trans_no=".db_escape($trans_no)." AND ".TB_PREF."supp_trans.supplier_id=".TB_PREF."suppliers.supplier_id"; if ($trans_type > 0) - $sql .= " AND ".TB_PREF."supp_trans.type=$trans_type "; + $sql .= " AND ".TB_PREF."supp_trans.type=".db_escape($trans_type); if ($trans_type == 22) { // it's a payment so also get the bank account - $sql .= " AND ".TB_PREF."bank_trans.trans_no =$trans_no - AND ".TB_PREF."bank_trans.type=$trans_type + $sql .= " AND ".TB_PREF."bank_trans.trans_no =".db_escape($trans_no)." + AND ".TB_PREF."bank_trans.type=".db_escape($trans_type)." AND ".TB_PREF."bank_accounts.id=".TB_PREF."bank_trans.bank_act "; } @@ -104,8 +106,8 @@ function exists_supp_trans($type, $type_no) if ($type == 25) return exists_grn($type_no); - $sql = "SELECT trans_no FROM ".TB_PREF."supp_trans WHERE type=$type - AND trans_no=$type_no"; + $sql = "SELECT trans_no FROM ".TB_PREF."supp_trans WHERE type=".db_escape($type)." + AND trans_no=".db_escape($type_no); $result = db_query($sql, "Cannot retreive a supplier transaction"); return (db_num_rows($result) > 0); @@ -116,7 +118,7 @@ function exists_supp_trans($type, $type_no) function void_supp_trans($type, $type_no) { $sql = "UPDATE ".TB_PREF."supp_trans SET ov_amount=0, ov_discount=0, ov_gst=0, - alloc=0 WHERE type=$type AND trans_no=$type_no"; + alloc=0 WHERE type=".db_escape($type)." AND trans_no=".db_escape($type_no); db_query($sql, "could not void supp transactions for type=$type and trans_no=$type_no"); } diff --git a/purchasing/includes/db/suppalloc_db.inc b/purchasing/includes/db/suppalloc_db.inc index 91b53396..c11c7d16 100644 --- a/purchasing/includes/db/suppalloc_db.inc +++ b/purchasing/includes/db/suppalloc_db.inc @@ -18,7 +18,9 @@ function add_supp_allocation($amount, $trans_type_from, $trans_no_from, $sql = "INSERT INTO ".TB_PREF."supp_allocations ( amt, date_alloc, trans_type_from, trans_no_from, trans_no_to, trans_type_to) - VALUES ($amount, '$date', $trans_type_from, $trans_no_from, $trans_no_to, $trans_type_to)"; + VALUES (".db_escape($amount).", '$date', " + .db_escape($trans_type_from).", ".db_escape($trans_no_from).", " + .db_escape($trans_no_to).", ".db_escape($trans_type_to).")"; db_query($sql, "A supplier allocation could not be added to the database"); } @@ -28,7 +30,7 @@ function add_supp_allocation($amount, $trans_type_from, $trans_no_from, function delete_supp_allocation($trans_id) { - $sql = "DELETE FROM ".TB_PREF."supp_allocations WHERE id = " . $trans_id; + $sql = "DELETE FROM ".TB_PREF."supp_allocations WHERE id = ".db_escape($trans_id); db_query($sql, "The existing allocation $trans_id could not be deleted"); } @@ -37,7 +39,8 @@ function delete_supp_allocation($trans_id) function get_supp_trans_allocation_balance($trans_type, $trans_no) { $sql = "SELECT (ov_amount+ov_gst-ov_discount-alloc) AS BalToAllocate - FROM ".TB_PREF."supp_trans WHERE trans_no=$trans_no AND type=$trans_type"; + FROM ".TB_PREF."supp_trans WHERE trans_no=" + .db_escape($trans_no)." AND type=".db_escape($trans_type); $result = db_query($sql,"calculate the allocation"); $myrow = db_fetch_row($result); @@ -48,8 +51,8 @@ function get_supp_trans_allocation_balance($trans_type, $trans_no) function update_supp_trans_allocation($trans_type, $trans_no, $alloc) { - $sql = "UPDATE ".TB_PREF."supp_trans SET alloc = alloc + $alloc - WHERE type=$trans_type AND trans_no = $trans_no"; + $sql = "UPDATE ".TB_PREF."supp_trans SET alloc = alloc + ".db_escape($alloc)." + WHERE type=".db_escape($trans_type)." AND trans_no = ".db_escape($trans_no); db_query($sql, "The supp transaction record could not be modified for the allocation against it"); } @@ -67,7 +70,7 @@ function clear_supp_alloctions($type, $type_no, $date="") // clear any allocations for this transaction $sql = "SELECT * FROM ".TB_PREF."supp_allocations WHERE (trans_type_from=$type AND trans_no_from=$type_no) - OR (trans_type_to=$type AND trans_no_to=$type_no)"; + OR (trans_type_to=".db_escape($type)." AND trans_no_to=".db_escape($type_no).")"; $result = db_query($sql, "could not void supp transactions for type=$type and trans_no=$type_no"); while ($row = db_fetch($result)) @@ -88,8 +91,8 @@ function clear_supp_alloctions($type, $type_no, $date="") // remove any allocations for this transaction $sql = "DELETE FROM ".TB_PREF."supp_allocations - WHERE (trans_type_from=$type AND trans_no_from=$type_no) - OR (trans_type_to=$type AND trans_no_to=$type_no)"; + WHERE (trans_type_from=".db_escape($type)." AND trans_no_from=".db_escape($type_no).") + OR (trans_type_to=".db_escape($type)." AND trans_no_to=".db_escape($type_no).")"; db_query($sql, "could not void supp transactions for type=$type and trans_no=$type_no"); } @@ -124,7 +127,7 @@ function get_alloc_supp_sql($extra_fields=null, $extra_conditions=null, $extra_t $sql .= " WHERE trans.supplier_id=supplier.supplier_id"; if ($extra_conditions) - $sql .= " AND $extra_conditions "; + $sql .= " AND $extra_conditions"; return $sql; } @@ -142,7 +145,7 @@ function get_allocatable_from_supp_sql($supplier_id, $settled) $supp_sql = ""; if ($supplier_id != null) - $supp_sql = " AND trans.supplier_id = $supplier_id"; + $supp_sql = " AND trans.supplier_id = ".db_escape($supplier_id); $sql = get_alloc_supp_sql("round(ABS(ov_amount+ov_gst+ov_discount)-alloc,6) <= 0 AS settled", "(type=22 OR type=21 OR type=1) AND (ov_amount < 0) " . $settled_sql . $supp_sql); @@ -158,10 +161,10 @@ function get_allocatable_to_supp_transactions($supplier_id, $trans_no=null, $typ { $sql = get_alloc_supp_sql("amt, supp_reference", "trans.trans_no = alloc.trans_no_to AND trans.type = alloc.trans_type_to - AND alloc.trans_no_from=$trans_no - AND alloc.trans_type_from=$type - AND trans.supplier_id=$supplier_id", - "".TB_PREF."supp_allocations as alloc"); + AND alloc.trans_no_from=".db_escape($trans_no)." + AND alloc.trans_type_from=".db_escape($type)." + AND trans.supplier_id=".db_escape($supplier_id), + TB_PREF."supp_allocations as alloc"); } else { diff --git a/purchasing/includes/db/suppliers_db.inc b/purchasing/includes/db/suppliers_db.inc index eaead9ec..207d9942 100644 --- a/purchasing/includes/db/suppliers_db.inc +++ b/purchasing/includes/db/suppliers_db.inc @@ -60,7 +60,7 @@ function get_supplier_details($supplier_id, $to=null) ".TB_PREF."payment_terms WHERE ".TB_PREF."suppliers.payment_terms = ".TB_PREF."payment_terms.terms_indicator - AND ".TB_PREF."suppliers.supplier_id = '$supplier_id'"; + AND ".TB_PREF."suppliers.supplier_id = ".db_escape($supplier_id); $result = db_query($sql,"The customer details could not be retrieved"); @@ -86,7 +86,7 @@ function get_supplier_details($supplier_id, $to=null) function get_supplier($supplier_id) { - $sql = "SELECT * FROM ".TB_PREF."suppliers WHERE supplier_id=$supplier_id"; + $sql = "SELECT * FROM ".TB_PREF."suppliers WHERE supplier_id=".db_escape($supplier_id); $result = db_query($sql, "could not get supplier"); @@ -95,7 +95,7 @@ function get_supplier($supplier_id) function get_supplier_name($supplier_id) { - $sql = "SELECT supp_name AS name FROM ".TB_PREF."suppliers WHERE supplier_id=$supplier_id"; + $sql = "SELECT supp_name AS name FROM ".TB_PREF."suppliers WHERE supplier_id=".db_escape($supplier_id); $result = db_query($sql, "could not get supplier"); @@ -106,7 +106,7 @@ function get_supplier_name($supplier_id) function get_supplier_accounts($supplier_id) { - $sql = "SELECT payable_account,purchase_account,payment_discount_account FROM ".TB_PREF."suppliers WHERE supplier_id=$supplier_id"; + $sql = "SELECT payable_account,purchase_account,payment_discount_account FROM ".TB_PREF."suppliers WHERE supplier_id=".db_escape($supplier_id); $result = db_query($sql, "could not get supplier"); diff --git a/purchasing/includes/purchasing_db.inc b/purchasing/includes/purchasing_db.inc index dc9ceccd..01323db1 100644 --- a/purchasing/includes/purchasing_db.inc +++ b/purchasing/includes/purchasing_db.inc @@ -47,8 +47,8 @@ function add_gl_trans_supplier($type, $type_no, $date_, $account, $dimension, $d function get_purchase_price($supplier_id, $stock_id) { $sql = "SELECT price, conversion_factor FROM ".TB_PREF."purch_data - WHERE supplier_id = '" . $supplier_id . "' - AND stock_id = '". $stock_id . "'"; + WHERE supplier_id = ".db_escape($supplier_id) . " + AND stock_id = ".db_escape($stock_id); $result = db_query($sql, "The supplier pricing details for " . $stock_id . " could not be retrieved"); if (db_num_rows($result) == 1) @@ -65,8 +65,8 @@ function get_purchase_price($supplier_id, $stock_id) function get_purchase_conversion_factor($supplier_id, $stock_id) { $sql = "SELECT conversion_factor FROM ".TB_PREF."purch_data - WHERE supplier_id = '" . $supplier_id . "' - AND stock_id = '". $stock_id . "'"; + WHERE supplier_id = ".db_escape($supplier_id)." + AND stock_id = ".db_escape($stock_id); $result = db_query($sql, "The supplier pricing details for " . $stock_id . " could not be retrieved"); if (db_num_rows($result) == 1) @@ -84,8 +84,8 @@ function get_purchase_conversion_factor($supplier_id, $stock_id) function get_purchase_data($supplier_id, $stock_id) { $sql = "SELECT * FROM ".TB_PREF."purch_data - WHERE supplier_id = '" . $supplier_id . "' - AND stock_id = '". $stock_id . "'"; + WHERE supplier_id = ".db_escape($supplier_id) . " + AND stock_id = ".db_escape($stock_id); $result = db_query($sql, "The supplier pricing details for " . $stock_id . " could not be retrieved"); return db_fetch($result); @@ -97,18 +97,19 @@ function add_or_update_purchase_data($supplier_id, $stock_id, $price, $descripti if ($data === false) { $sql = "INSERT INTO ".TB_PREF."purch_data (supplier_id, stock_id, price, suppliers_uom, - conversion_factor, supplier_description) VALUES ('$supplier_id', '$stock_id', - $price, '$uom', 1, ".db_escape($description).")"; + conversion_factor, supplier_description) VALUES (".db_escape($supplier_id) + .", ".db_escape($stock_id).", ".db_escape($price).", " + .db_escape($uom).", 1, ".db_escape($description).")"; db_query($sql,"The supplier purchasing details could not be added"); return; } - $price = round($price * $data['conversion_factor'], user_price_dec()); - $sql = "UPDATE ".TB_PREF."purch_data SET price=$price"; + $price = round($price * $data['conversion_factor'], user_price_dec()); + $sql = "UPDATE ".TB_PREF."purch_data SET price=".db_escape($price); if ($uom != "") - $sql .= ",suppliers_uom='$uom'"; + $sql .= ",suppliers_uom=".db_escape($uom); if ($description != "") $sql .= ",supplier_description=".db_escape($description); - $sql .= " WHERE stock_id='$stock_id' AND supplier_id='$supplier_id'"; + $sql .= " WHERE stock_id=".db_escape($stock_id)." AND supplier_id=".db_escape($supplier_id); db_query($sql,"The supplier purchasing details could not be updated"); return true; } diff --git a/purchasing/inquiry/po_search.php b/purchasing/inquiry/po_search.php index 3f23f5bb..9b498c0b 100644 --- a/purchasing/inquiry/po_search.php +++ b/purchasing/inquiry/po_search.php @@ -142,7 +142,7 @@ $sql = "SELECT if (isset($order_number) && $order_number != "") { - $sql .= "AND porder.reference LIKE '%". $order_number . "%'"; + $sql .= "AND porder.reference LIKE ".db_escape('%'. $order_number . '%'); } else { @@ -154,12 +154,12 @@ else if (isset($_POST['StockLocation']) && $_POST['StockLocation'] != $all_items) { - $sql .= " AND porder.into_stock_location = '". $_POST['StockLocation'] . "' "; + $sql .= " AND porder.into_stock_location = ".db_escape($_POST['StockLocation']); } if (isset($selected_stock_item)) { - $sql .= " AND line.item_code='". $selected_stock_item ."' "; + $sql .= " AND line.item_code=".db_escape($selected_stock_item); } } //end not order number selected diff --git a/purchasing/inquiry/po_search_completed.php b/purchasing/inquiry/po_search_completed.php index a22f6d2a..c71c0cde 100644 --- a/purchasing/inquiry/po_search_completed.php +++ b/purchasing/inquiry/po_search_completed.php @@ -126,7 +126,7 @@ $sql = "SELECT if (isset($order_number) && $order_number != "") { - $sql .= "AND porder.reference LIKE '%". $order_number . "%'"; + $sql .= "AND porder.reference LIKE ".db_escape('%'. $order_number . '%'); } else { @@ -139,11 +139,11 @@ else if (isset($_POST['StockLocation']) && $_POST['StockLocation'] != reserved_words::get_all()) { - $sql .= " AND porder.into_stock_location = '". $_POST['StockLocation'] . "' "; + $sql .= " AND porder.into_stock_location = ".db_escape($_POST['StockLocation']); } if (isset($selected_stock_item)) { - $sql .= " AND line.item_code='". $selected_stock_item ."' "; + $sql .= " AND line.item_code=".db_escape($selected_stock_item); } } //end not order number selected diff --git a/purchasing/inquiry/supplier_allocation_inquiry.php b/purchasing/inquiry/supplier_allocation_inquiry.php index 55ab1678..1782ef07 100644 --- a/purchasing/inquiry/supplier_allocation_inquiry.php +++ b/purchasing/inquiry/supplier_allocation_inquiry.php @@ -141,7 +141,7 @@ function fmt_credit($row) AND trans.tran_date >= '$date_after' AND trans.tran_date <= '$date_to'"; if ($_POST['supplier_id'] != reserved_words::get_all()) - $sql .= " AND trans.supplier_id = '" . $_POST['supplier_id'] . "'"; + $sql .= " AND trans.supplier_id = ".db_escape($_POST['supplier_id']); if (isset($_POST['filterType']) && $_POST['filterType'] != reserved_words::get_all()) { if (($_POST['filterType'] == '1') || ($_POST['filterType'] == '2')) diff --git a/purchasing/inquiry/supplier_inquiry.php b/purchasing/inquiry/supplier_inquiry.php index 5fc3e0c5..138b2d64 100644 --- a/purchasing/inquiry/supplier_inquiry.php +++ b/purchasing/inquiry/supplier_inquiry.php @@ -178,7 +178,7 @@ function check_overdue($row) AND trans.tran_date <= '$date_to' AND trans.ov_amount != 0"; // exclude voided transactions if ($_POST['supplier_id'] != reserved_words::get_all()) - $sql .= " AND trans.supplier_id = '" . $_POST['supplier_id'] . "'"; + $sql .= " AND trans.supplier_id = ".db_escape($_POST['supplier_id']); if (isset($_POST['filterType']) && $_POST['filterType'] != reserved_words::get_all()) { if (($_POST['filterType'] == '1')) diff --git a/purchasing/manage/suppliers.php b/purchasing/manage/suppliers.php index dcae2536..42b386be 100644 --- a/purchasing/manage/suppliers.php +++ b/purchasing/manage/suppliers.php @@ -71,7 +71,7 @@ if (isset($_POST['submit'])) purchase_account=".db_escape($_POST['purchase_account']) . ", payment_discount_account=".db_escape($_POST['payment_discount_account']) . ", notes=".db_escape($_POST['notes']) . ", - tax_group_id=".db_escape($_POST['tax_group_id']) . " WHERE supplier_id = '" . $_POST['supplier_id'] . "'"; + tax_group_id=".db_escape($_POST['tax_group_id']) . " WHERE supplier_id = ".db_escape($_POST['supplier_id']); db_query($sql,"The supplier could not be updated"); display_notification(_("Supplier has been updated.")); @@ -121,7 +121,7 @@ elseif (isset($_POST['delete']) && $_POST['delete'] != "") // PREVENT DELETES IF DEPENDENT RECORDS IN 'supp_trans' , purch_orders - $sql= "SELECT COUNT(*) FROM ".TB_PREF."supp_trans WHERE supplier_id='" . $_POST['supplier_id'] . "'"; + $sql= "SELECT COUNT(*) FROM ".TB_PREF."supp_trans WHERE supplier_id=".db_escape($_POST['supplier_id']); $result = db_query($sql,"check failed"); $myrow = db_fetch_row($result); if ($myrow[0] > 0) @@ -132,7 +132,7 @@ elseif (isset($_POST['delete']) && $_POST['delete'] != "") } else { - $sql= "SELECT COUNT(*) FROM ".TB_PREF."purch_orders WHERE supplier_id='" . $_POST['supplier_id'] . "'"; + $sql= "SELECT COUNT(*) FROM ".TB_PREF."purch_orders WHERE supplier_id=".db_escape($_POST['supplier_id']); $result = db_query($sql,"check failed"); $myrow = db_fetch_row($result); if ($myrow[0] > 0) @@ -144,7 +144,7 @@ elseif (isset($_POST['delete']) && $_POST['delete'] != "") } if ($cancel_delete == 0) { - $sql="DELETE FROM ".TB_PREF."suppliers WHERE supplier_id='" . $_POST['supplier_id']. "'"; + $sql="DELETE FROM ".TB_PREF."suppliers WHERE supplier_id=".db_escape($_POST['supplier_id']); db_query($sql,"check failed"); unset($_SESSION['supplier_id']); diff --git a/purchasing/po_entry_items.php b/purchasing/po_entry_items.php index 2cb5a364..6f5fe76f 100644 --- a/purchasing/po_entry_items.php +++ b/purchasing/po_entry_items.php @@ -243,7 +243,7 @@ function handle_add_new_item() if ($allow_update == true) { $sql = "SELECT description, units, mb_flag - FROM ".TB_PREF."stock_master WHERE stock_id = '". $_POST['stock_id'] . "'"; + FROM ".TB_PREF."stock_master WHERE stock_id = ".db_escape($_POST['stock_id']); $result = db_query($sql,"The stock details for " . $_POST['stock_id'] . " could not be retrieved"); diff --git a/purchasing/po_receive_items.php b/purchasing/po_receive_items.php index af897d7a..c2cf62ec 100644 --- a/purchasing/po_receive_items.php +++ b/purchasing/po_receive_items.php @@ -124,7 +124,7 @@ function check_po_changed() // Otherwise if you try to fullfill item quantities separately will give error. $sql = "SELECT item_code, quantity_ordered, quantity_received, qty_invoiced FROM ".TB_PREF."purch_order_details - WHERE order_no=" . $_SESSION['PO']->order_no + WHERE order_no=".db_escape($_SESSION['PO']->order_no) ." ORDER BY po_detail_item"; $result = db_query($sql, "could not query purch order details"); diff --git a/purchasing/supplier_credit.php b/purchasing/supplier_credit.php index bc60f4de..1f615ec9 100644 --- a/purchasing/supplier_credit.php +++ b/purchasing/supplier_credit.php @@ -110,7 +110,7 @@ if (isset($_POST['AddGLCodeToTrans'])){ $Ajax->activate('gl_items'); $input_error = false; - $sql = "SELECT account_code, account_name FROM ".TB_PREF."chart_master WHERE account_code='" . $_POST['gl_code'] . "'"; + $sql = "SELECT account_code, account_name FROM ".TB_PREF."chart_master WHERE account_code=".db_escape($_POST['gl_code']); $result = db_query($sql,"get account information"); if (db_num_rows($result) == 0) { diff --git a/purchasing/supplier_invoice.php b/purchasing/supplier_invoice.php index b1bd2ece..ab2409d9 100644 --- a/purchasing/supplier_invoice.php +++ b/purchasing/supplier_invoice.php @@ -110,7 +110,7 @@ if (isset($_POST['AddGLCodeToTrans'])){ $Ajax->activate('gl_items'); $input_error = false; - $sql = "SELECT account_code, account_name FROM ".TB_PREF."chart_master WHERE account_code='" . $_POST['gl_code'] . "'"; + $sql = "SELECT account_code, account_name FROM ".TB_PREF."chart_master WHERE account_code=".db_escape($_POST['gl_code']); $result = db_query($sql,"get account information"); if (db_num_rows($result) == 0) { @@ -195,10 +195,10 @@ function check_data() return false; } - $sql = "SELECT Count(*) FROM ".TB_PREF."supp_trans WHERE supplier_id='" - . $_SESSION['supp_trans']->supplier_id . "' AND supp_reference='" - . $_POST['supp_reference'] - . "' AND ov_amount!=0"; // ignore voided invoice references + $sql = "SELECT Count(*) FROM ".TB_PREF."supp_trans WHERE supplier_id=" + .db_escape($_SESSION['supp_trans']->supplier_id) . " AND supp_reference=" + .db_escape( $_POST['supp_reference']) + . " AND ov_amount!=0"; // ignore voided invoice references $result=db_query($sql,"The sql to check for the previous entry of the same invoice failed"); -- 2.30.2