From 218f69fc35e2ff9e003324618208ec97e9cbf034 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Roland=20H=C3=A4der?= Date: Fri, 23 Jul 2010 05:13:27 +0000 Subject: [PATCH] Some SQL queries improved, sponsor registration fixed: - Some SQL queries improved by removed single quotes around data that are numbers. This may help MySQL to improve speed of the query - Sponsor registration from admin (and possibly guest) area was broken - TODOs.txt updated --- DOCS/TODOs.txt | 16 ++++++++-------- inc/language/de.php | 1 + inc/language/sponsor_de.php | 1 + inc/libs/sponsor_functions.php | 15 ++++++++++----- inc/modules/admin/what-del_sponsor.php | 4 ++-- inc/modules/admin/what-edit_sponsor.php | 17 +++++++---------- inc/modules/admin/what-list_sponsor.php | 2 +- inc/modules/admin/what-list_sponsor_pay.php | 4 ++-- inc/modules/admin/what-list_sponsor_pays.php | 2 +- inc/modules/admin/what-lock_sponsor.php | 4 ++-- inc/modules/admin/what-unlock_sponsor.php | 16 ++++++++++++---- inc/modules/guest/what-sponsor_login.php | 14 ++++++++++---- inc/modules/sponsor/account.php | 2 +- templates/de/html/admin/admin_list_sponsor.tpl | 2 ++ 14 files changed, 60 insertions(+), 40 deletions(-) diff --git a/DOCS/TODOs.txt b/DOCS/TODOs.txt index fd9e0f4c99..4ad4a8c405 100644 --- a/DOCS/TODOs.txt +++ b/DOCS/TODOs.txt @@ -49,10 +49,10 @@ ./inc/functions.php:2251: // @TODO This is still very static, rewrite it somehow ./inc/gen_sql_patches.php:96:// @TODO Rewrite this to a filter ./inc/install-functions.php:59: // @TODO DEACTIVATED: changeDataInFile(getCachePath() . 'config-local.php', 'OUTPUT-MODE', "setConfigEntry('OUTPUT_MODE', '", "');", postRequestParameter('omode'), 0); -./inc/language/de.php:1118:// @TODO Rewrite these two constants -./inc/language/de.php:1133:// @TODO Rewrite these three constants +./inc/language/de.php:1119:// @TODO Rewrite these two constants +./inc/language/de.php:1134:// @TODO Rewrite these three constants ./inc/language/de.php:300: // @TODO Following two are unused? -./inc/language/de.php:810:// @TODO Are these constants longer used? +./inc/language/de.php:811:// @TODO Are these constants longer used? ./inc/language-functions.php:234: // @TODO These are all valid languages, again hard-coded ./inc/language-functions.php:46:// @TODO Rewrite all language constants to this function. ./inc/language/rallye_de.php:13: * @TODO Naming convention not applied for language strings * @@ -170,11 +170,11 @@ ./inc/modules/member/what-unconfirmed.php:143: // @TODO Try to rewrite this to $content = SQL_FETCHARRAY() ./inc/modules/order.php:76: // @TODO Unused: 2,4 ./inc/monthly/monthly_bonus.php:69: // @TODO Rewrite this to a filter -./inc/mysql-manager.php:1171: // @TODO Rewrite this to a filter -./inc/mysql-manager.php:1418: // @TODO Try to rewrite this to $content = SQL_FETCHARRAY() -./inc/mysql-manager.php:1516: // @TODO Rewrite this to a filter -./inc/mysql-manager.php:1883: // @TODO Rewrite this to a filter -./inc/mysql-manager.php:1927:// @TODO Fix inconsistency between last_module and getWhat() +./inc/mysql-manager.php:1170: // @TODO Rewrite this to a filter +./inc/mysql-manager.php:1417: // @TODO Try to rewrite this to $content = SQL_FETCHARRAY() +./inc/mysql-manager.php:1515: // @TODO Rewrite this to a filter +./inc/mysql-manager.php:1882: // @TODO Rewrite this to a filter +./inc/mysql-manager.php:1926:// @TODO Fix inconsistency between last_module and getWhat() ./inc/mysql-manager.php:370: // @TODO Try to rewrite this to one or more functions ./inc/mysql-manager.php:46:// @TODO Can we cache this? ./inc/reset/reset_beg.php:51:// @TODO This should be converted in a daily beg rallye diff --git a/inc/language/de.php b/inc/language/de.php index 8009cd2560..0a5a11b69f 100644 --- a/inc/language/de.php +++ b/inc/language/de.php @@ -489,6 +489,7 @@ addMessages(array( 'ACCOUNT_STATUS_CONFIRMED' => "Bestätigt", 'ACCOUNT_STATUS_LOCKED' => "Gesperrt", 'ACCOUNT_STATUS_DELETED' => "Gelöscht", + 'ACCOUNT_STATUS_PENDING' => "Wartend", 'ADMIN_ALL_ACCOUNTS' => "Alle Mitglieder-Accounts auflisten", 'ADMIN_NO_NONE_REGISTERED' => "Ihre Suchkriterieren ergaben keine Treffer in der Mitgliederliste. ", 'ADMIN_MEMBER_404' => "Mitglieds-Account %s existiert nicht.", diff --git a/inc/language/sponsor_de.php b/inc/language/sponsor_de.php index e46519d769..ffeceaf318 100644 --- a/inc/language/sponsor_de.php +++ b/inc/language/sponsor_de.php @@ -136,6 +136,7 @@ addMessages(array( 'GUEST_SPONSOR_RECEIVE_WARNINGS' => "Bei Datenänderung eine Bestätigung per Mail erhalten?", 'SPONSOR_ACCEPT_TERMS' => "Ich/wir habe/n die [AGBs] gelesen und akzeptiert.", 'SPONSOR_TERMS_HEADER' => "Allgemeine Geschäftsbedingungen", + 'ADMIN_SPONSOR_ACCOUNT_404_ALREADY_CONFIRMED' => "Das von Ihnen angegebene Sponsoraccount %s konnte entweder nicht gefunden werden, oder ist bereits freigegeben.", // Add payment types 'ADMIN_SPONSOR_NO_PAYTYPES' => "Noch keine Buchungspakete eingerichtet.", diff --git a/inc/libs/sponsor_functions.php b/inc/libs/sponsor_functions.php index a625a5517e..46e4a13b0f 100644 --- a/inc/libs/sponsor_functions.php +++ b/inc/libs/sponsor_functions.php @@ -137,7 +137,7 @@ function handleSponsorRequest ($postData, $update=false, $messageArray=array(), } // Remove last ", " from SQL string - $sql = substr($sql, 0, -2)." WHERE `id`='%s' LIMIT 1"; + $sql = substr($sql, 0, -2)." WHERE `id`=%s LIMIT 1"; $DATA['values'][] = bigintval(getRequestParameter('id')); // Generate message @@ -259,7 +259,7 @@ function isSponsor () { FROM `{?_MYSQL_PREFIX?}_sponsor_data` WHERE - `id`='%s' AND `password`='%s' AND `status`='CONFIRMED' + `id`=%s AND `password`='%s' AND `status`='CONFIRMED' LIMIT 1", array( bigintval(getSession('sponsor_id')), @@ -382,9 +382,14 @@ function updateSponsorLogin () { // Is sponsor? if (isSponsor()) { // Update last online timestamp - SQL_QUERY_ESC("UPDATE `{?_MYSQL_PREFIX?}_sponsor_data` -SET `last_online`=UNIX_TIMESTAMP() -WHERE `id`='%s' AND `password`='%s' LIMIT 1", + SQL_QUERY_ESC("UPDATE + `{?_MYSQL_PREFIX?}_sponsor_data` +SET + `last_online`=UNIX_TIMESTAMP() +WHERE + `id`=%s AND + `password`='%s' +LIMIT 1", array( bigintval(getSession('sponsor_id')), getSession('sponsorpass') diff --git a/inc/modules/admin/what-del_sponsor.php b/inc/modules/admin/what-del_sponsor.php index 633a7c79bc..f54fd3141b 100644 --- a/inc/modules/admin/what-del_sponsor.php +++ b/inc/modules/admin/what-del_sponsor.php @@ -47,7 +47,7 @@ addMenuDescription('admin', __FILE__); if (isGetRequestParameterSet('id')) { // Check for selected sponsor - $result = SQL_QUERY_ESC("SELECT email, gender, surname, family FROM `{?_MYSQL_PREFIX?}_sponsor_data` WHERE `id`='%s' LIMIT 1", + $result = SQL_QUERY_ESC("SELECT `id`, `email`, `gender`, `surname`, `family` FROM `{?_MYSQL_PREFIX?}_sponsor_data` WHERE `id`=%s LIMIT 1", array(bigintval(getRequestParameter('id'))), __FILE__, __LINE__); if (SQL_NUMROWS($result) == 1) { // Remove sponsor @@ -64,7 +64,7 @@ if (isGetRequestParameterSet('id')) { sendEmail($content['email'], '{--ADMIN_SPONSOR_DELETE_SUBJECT--}', $message); // Remove account - SQL_QUERY_ESC("DELETE LOW_PRIORITY FROM `{?_MYSQL_PREFIX?}_sponsor_data` WHERE `id`='%s' LIMIT 1", + SQL_QUERY_ESC("DELETE LOW_PRIORITY FROM `{?_MYSQL_PREFIX?}_sponsor_data` WHERE `id`=%s LIMIT 1", array(bigintval(getRequestParameter('id'))), __FILE__, __LINE__); // Remove orders diff --git a/inc/modules/admin/what-edit_sponsor.php b/inc/modules/admin/what-edit_sponsor.php index 88625c21ba..04707bc3c1 100644 --- a/inc/modules/admin/what-edit_sponsor.php +++ b/inc/modules/admin/what-edit_sponsor.php @@ -40,7 +40,7 @@ // Some security stuff... if ((!defined('__SECURITY')) || (!isAdmin())) { die(); -} +} // END - if // Add description as navigation point addMenuDescription('admin', __FILE__); @@ -48,14 +48,14 @@ addMenuDescription('admin', __FILE__); if ((isGetRequestParameterSet('id')) && (isGetRequestParameterSet('mode'))) { // Check for selected sponsor $result_main = SQL_QUERY_ESC("SELECT - `company`, `position`, `gender`, `surname`, `family`, + `id`, `company`, `position`, `gender`, `surname`, `family`, `street_nr1`, `street_nr2`, `zip`, `city`, `country`, `phone`, `fax`, `cell`, `email`, `url`, `tax_ident`, `receive_warnings`, `warning_interval` FROM `{?_MYSQL_PREFIX?}_sponsor_data` WHERE - `id`='%s' + `id`=%s LIMIT 1", array(bigintval(getRequestParameter('id'))), __FILE__, __LINE__); if (SQL_NUMROWS($result_main) == 1) { @@ -63,9 +63,6 @@ LIMIT 1", $content = SQL_FETCHARRAY($result_main); // Prepare all data for the template - // Sponsor's id - $content['id'] = bigintval(getRequestParameter('id')); - // Init gender foreach (array('m', 'f', 'c') as $gender) { $content['gender_' . $gender] = ''; @@ -92,7 +89,7 @@ LIMIT 1", $points = bigintval(convertCommaToDot(postRequestParameter('points'))); // Add points to account - SQL_QUERY_ESC("UPDATE `{?_MYSQL_PREFIX?}_sponsor_data` SET `points_amount`=`points_amount`+%s WHERE `id`='%s' LIMIT 1", + SQL_QUERY_ESC("UPDATE `{?_MYSQL_PREFIX?}_sponsor_data` SET `points_amount`=`points_amount`+%s WHERE `id`=%s LIMIT 1", array($points, bigintval(getRequestParameter('id'))), __FILE__, __LINE__); // Remember points /reason for the template @@ -115,7 +112,7 @@ LIMIT 1", $points = bigintval(convertCommaToDot(postRequestParameter('points'))); // Add points to account - SQL_QUERY_ESC("UPDATE `{?_MYSQL_PREFIX?}_sponsor_data` SET `points_used`=`points_used`+%s WHERE `id`='%s' LIMIT 1", + SQL_QUERY_ESC("UPDATE `{?_MYSQL_PREFIX?}_sponsor_data` SET `points_used`=`points_used`+%s WHERE `id`=%s LIMIT 1", array($points, bigintval(getRequestParameter('id'))), __FILE__, __LINE__); // Remember points /reason for the template @@ -171,10 +168,10 @@ LIMIT 1", } // END - if } elseif (isFileReadable(sprintf("%stemplates/%s/html/admin/%s.tpl", getPath(), getLanguage(), $TPL))) { // Create mailto link - $content['contact'] = '' . $content['surname'] . ' ' . $content['family'] . ''; + $content['contact'] = '' . $content['surname'] . ' ' . $content['family'] . ''; // Load mode template - loadTemplate($TPL); + loadTemplate($TPL, false, $content); } else { // Template not found! loadTemplate('admin_settings_saved', false, getMaskedMessage('ADMIN_SPONSOR_MODUS_TPL_404', getRequestParameter('mode'))); diff --git a/inc/modules/admin/what-list_sponsor.php b/inc/modules/admin/what-list_sponsor.php index 86e1357a62..864e6fe6fd 100644 --- a/inc/modules/admin/what-list_sponsor.php +++ b/inc/modules/admin/what-list_sponsor.php @@ -90,7 +90,7 @@ WHERE SQL_FREERESULT($result); } elseif (isGetRequestParameterSet('refid')) { // Search for sponsor - $result = SQL_QUERY_ESC("SELECT `id` FROM `{?_MYSQL_PREFIX?}_sponsor_data` WHERE `id`='%s' LIMIT 1", + $result = SQL_QUERY_ESC("SELECT `id` FROM `{?_MYSQL_PREFIX?}_sponsor_data` WHERE `id`=%s LIMIT 1", array(bigintval(getRequestParameter('refid'))), __FILE__, __LINE__); if (SQL_NUMROWS($result) == 1) { // Free memory diff --git a/inc/modules/admin/what-list_sponsor_pay.php b/inc/modules/admin/what-list_sponsor_pay.php index dee8f22e8c..75dea69fb2 100644 --- a/inc/modules/admin/what-list_sponsor_pay.php +++ b/inc/modules/admin/what-list_sponsor_pay.php @@ -80,7 +80,7 @@ SET `pay_min_count`='%s', `pay_currency`='%s' WHERE - `id`='%s' + `id`=%s LIMIT 1", array( postRequestParameter('name', $id), @@ -97,7 +97,7 @@ LIMIT 1", // Remove entries here... foreach (postRequestParameter('id') as $id => $sel) { // Remove entry - SQL_QUERY_ESC("DELETE LOW_PRIORITY FROM `{?_MYSQL_PREFIX?}_sponsor_paytypes` WHERE `id`='%s' LIMIT 1", + SQL_QUERY_ESC("DELETE LOW_PRIORITY FROM `{?_MYSQL_PREFIX?}_sponsor_paytypes` WHERE `id`=%s LIMIT 1", array(bigintval($id)), __FILE__, __LINE__); } diff --git a/inc/modules/admin/what-list_sponsor_pays.php b/inc/modules/admin/what-list_sponsor_pays.php index c9d3dc3c10..71df7ec455 100644 --- a/inc/modules/admin/what-list_sponsor_pays.php +++ b/inc/modules/admin/what-list_sponsor_pays.php @@ -47,7 +47,7 @@ addMenuDescription('admin', __FILE__); if (isGetRequestParameterSet('id')) { // Check for selected sponsor - $result = SQL_QUERY_ESC("SELECT `id` FROM `{?_MYSQL_PREFIX?}_sponsor_data` WHERE `id`='%s' LIMIT 1", + $result = SQL_QUERY_ESC("SELECT `id` FROM `{?_MYSQL_PREFIX?}_sponsor_data` WHERE `id`=%s LIMIT 1", array(bigintval(getRequestParameter('id'))), __FILE__, __LINE__); if (SQL_NUMROWS($result) == 1) { // ... diff --git a/inc/modules/admin/what-lock_sponsor.php b/inc/modules/admin/what-lock_sponsor.php index b62ee795c8..9248740152 100644 --- a/inc/modules/admin/what-lock_sponsor.php +++ b/inc/modules/admin/what-lock_sponsor.php @@ -48,7 +48,7 @@ $message = ''; if (isGetRequestParameterSet('id')) { // Check for selected sponsor - $result_sponsor = SQL_QUERY_ESC("SELECT `gender`, `surname`, `family`, `email`, `status` FROM `{?_MYSQL_PREFIX?}_sponsor_data` WHERE `id`='%s' LIMIT 1", + $result_sponsor = SQL_QUERY_ESC("SELECT `gender`, `surname`, `family`, `email`, `status` FROM `{?_MYSQL_PREFIX?}_sponsor_data` WHERE `id`=%s LIMIT 1", array(bigintval(getRequestParameter('id'))), __FILE__, __LINE__); if (SQL_NUMROWS($result_sponsor) == 1) { // Get sponsor's current status and let only confirmed and locked status pass @@ -80,7 +80,7 @@ if (isGetRequestParameterSet('id')) { sendEmail($content['email'], $subject, $message); // Update sponsor's account - SQL_QUERY_ESC("UPDATE `{?_MYSQL_PREFIX?}_sponsor_data` SET `status`='%s' WHERE `id`='%s' LIMIT 1", + SQL_QUERY_ESC("UPDATE `{?_MYSQL_PREFIX?}_sponsor_data` SET `status`='%s' WHERE `id`=%s LIMIT 1", array($content['status'], bigintval(getRequestParameter('id'))), __FILE__, __LINE__); } elseif (isPostRequestParameterSet('no')) { // No don't lock / unlock now! diff --git a/inc/modules/admin/what-unlock_sponsor.php b/inc/modules/admin/what-unlock_sponsor.php index 3bff5d8455..f1ec7f1407 100644 --- a/inc/modules/admin/what-unlock_sponsor.php +++ b/inc/modules/admin/what-unlock_sponsor.php @@ -59,19 +59,23 @@ if (isFormSent()) { // Load his personal data $result_main = SQL_QUERY_ESC("SELECT - `gender`, `surname`, `family`, `email`, `remote_addr`, `sponsor_created`, `points_amount`, `refid` + `id`, `gender`, `surname`, `family`, `email`, `remote_addr`, `sponsor_created`, `points_amount`, `points_used`, `refid` FROM `{?_MYSQL_PREFIX?}_sponsor_data` WHERE - `status`='PENDING' AND + `status` IN('PENDING','UNCONFIRMED') AND `id`=%s LIMIT 1", array($content['id']), __FILE__, __LINE__); + /// Is there an entry? if (SQL_NUMROWS($result_main) == 1) { // Load data $content = SQL_FETCHARRAY($result_main); + // Add total amount + $content['points'] = ($content['points_amount'] - $content['points_used']); + // Check for open payments and close them $result = SQL_QUERY_ESC("SELECT so.admin_id, so.pay_count, so.pay_ordered, so.pay_status, @@ -118,6 +122,10 @@ ORDER BY // Free memory SQL_FREERESULT($result); + } else { + // Not found + loadTemplate('admin_settings_saved', false, getMaskedMessage('ADMIN_SPONSOR_ACCOUNT_404_ALREADY_CONFIRMED', $content['id'])); + return; } // Free memory @@ -130,7 +138,7 @@ SET `status`='CONFIRMED' WHERE `id`=%s AND - `status`='PENDING' + `status` IN('PENDING','UNCONFIRMED') LIMIT 1", array($content['id']), __FILE__, __LINE__); @@ -189,7 +197,7 @@ $result = SQL_QUERY("SELECT FROM `{?_MYSQL_PREFIX?}_sponsor_data` WHERE - `status`='PENDING' OR `status`='UNCONFIRMED' + `status` IN('PENDING','UNCONFIRMED') ORDER BY `id`", __FILE__, __LINE__); diff --git a/inc/modules/guest/what-sponsor_login.php b/inc/modules/guest/what-sponsor_login.php index f93c825652..54fa6cd0d4 100644 --- a/inc/modules/guest/what-sponsor_login.php +++ b/inc/modules/guest/what-sponsor_login.php @@ -115,7 +115,7 @@ LIMIT 1", SET `status`='CONFIRMED' WHERE - `id`='%s' AND + `id`=%s AND `hash`='%s' AND `status`='EMAIL' LIMIT 1", @@ -194,9 +194,15 @@ WHERE email='%s' AND (`status`='UNCONFIRMED' OR `status`='EMAIL') LIMIT 1", if (isFormSent()) { // Check email - $result = SQL_QUERY_ESC("SELECT `id`, `hash`, `remote_addr`, `gender`, `surname`, `family`, `sponsor_created` -FROM `{?_MYSQL_PREFIX?}_sponsor_data` -WHERE `email`='%s' AND `id`='%s' AND `status`='CONFIRMED' LIMIT 1", + $result = SQL_QUERY_ESC("SELECT + `id`, `hash`, `remote_addr`, `gender`, `surname`, `family`, `sponsor_created` +FROM + `{?_MYSQL_PREFIX?}_sponsor_data` +WHERE + `email`='%s' AND + `id`=%s AND + `status`='CONFIRMED' +LIMIT 1", array(postRequestParameter('email'), bigintval(postRequestParameter('id'))), __FILE__, __LINE__); // Entry found? diff --git a/inc/modules/sponsor/account.php b/inc/modules/sponsor/account.php index 84204e4336..e76cdacf09 100644 --- a/inc/modules/sponsor/account.php +++ b/inc/modules/sponsor/account.php @@ -58,7 +58,7 @@ $result = SQL_QUERY_ESC("SELECT FROM `{?_MYSQL_PREFIX?}_sponsor_data` WHERE - `id`='%s' AND + `id`=%s AND `password`='%s' LIMIT 1", array( diff --git a/templates/de/html/admin/admin_list_sponsor.tpl b/templates/de/html/admin/admin_list_sponsor.tpl index bc2f9e2816..ca46e83b1e 100644 --- a/templates/de/html/admin/admin_list_sponsor.tpl +++ b/templates/de/html/admin/admin_list_sponsor.tpl @@ -1,3 +1,4 @@ +
@@ -13,3 +14,4 @@ $content
+
-- 2.39.5