diff options
| author | Igor Scheller <igor.scheller@igorshp.de> | 2017-01-21 13:58:53 +0100 |
|---|---|---|
| committer | Igor Scheller <igor.scheller@igorshp.de> | 2017-01-21 13:58:53 +0100 |
| commit | 9a3ad8883403949a59e8935497a548ec536f1d40 (patch) | |
| tree | d3c27912c925e53bc240640ccc1133d8f87f1fd3 | |
| parent | f7c09cb7ff84db1004a4fa83a70735475702023f (diff) | |
Changed from mysqli to PDO, some refactorings, faster sql queries
45 files changed, 1948 insertions, 1301 deletions
diff --git a/config/config.default.php b/config/config.default.php index 5287b51b..bcfcc89c 100644 --- a/config/config.default.php +++ b/config/config.default.php @@ -1,6 +1,8 @@ <?php +// Set to development to enable debugging messages +$environment = 'production'; -// Enable maintenance mode (showin a static page) +// Enable maintenance mode (show a static page) $maintenance_mode = false; // URL to the angel faq and job description diff --git a/includes/controller/shift_entries_controller.php b/includes/controller/shift_entries_controller.php index 8cec5f10..0df1f430 100644 --- a/includes/controller/shift_entries_controller.php +++ b/includes/controller/shift_entries_controller.php @@ -1,5 +1,7 @@ <?php +use Engelsystem\Database\DB; + /** * Sign up for a shift. * @@ -17,7 +19,7 @@ function shift_entry_add_controller() } // Locations laden - $rooms = sql_select('SELECT * FROM `Room` WHERE `show`=\'Y\' ORDER BY `Name`'); + $rooms = Rooms(); $room_array = []; foreach ($rooms as $room) { $room_array[$room['RID']] = $room['Name']; @@ -39,22 +41,24 @@ function shift_entry_add_controller() if (in_array('user_shifts_admin', $privileges) || in_array('shiftentry_edit_angeltype_supporter', $privileges)) { $type = AngelType($type_id); } else { - $type = sql_select(" - SELECT * FROM `UserAngelTypes` + // TODO: Move queries to model + $type = DB::select(' + SELECT * + FROM `UserAngelTypes` JOIN `AngelTypes` ON (`UserAngelTypes`.`angeltype_id` = `AngelTypes`.`id`) - WHERE `AngelTypes`.`id` = '" . sql_escape($type_id) . "' + WHERE `AngelTypes`.`id` = ? AND ( `AngelTypes`.`restricted` = 0 OR ( - `UserAngelTypes`.`user_id` = '" . sql_escape($user['UID']) . "' + `UserAngelTypes`.`user_id` = ? AND NOT `UserAngelTypes`.`confirm_user_id` IS NULL ) ) - "); - $type = $type[0]; + ', [$type_id, $user['UID']]); + $type = array_shift($type); } - if ($type == null) { + if (empty($type)) { redirect(page_link_to('user_shifts')); } @@ -93,25 +97,32 @@ function shift_entry_add_controller() if (in_array('user_shifts_admin', $privileges) || in_array('shiftentry_edit_angeltype_supporter', $privileges) ) { - if (sql_num_query("SELECT * FROM `User` WHERE `UID`='" . sql_escape($user_id) . "' LIMIT 1") == 0) { + + if (count(DB::select('SELECT `UID` FROM `User` WHERE `UID`=? LIMIT 1', [$user_id])) == 0) { redirect(page_link_to('user_shifts')); } if ( isset($_REQUEST['angeltype_id']) && test_request_int('angeltype_id') - && sql_num_query("SELECT * FROM `AngelTypes` WHERE `id`='" . sql_escape($_REQUEST['angeltype_id']) . "' LIMIT 1") > 0 + && count(DB::select( + 'SELECT `id` FROM `AngelTypes` WHERE `id`=? LIMIT 1', + [$_REQUEST['angeltype_id']] + )) > 0 ) { $selected_type_id = $_REQUEST['angeltype_id']; } } - if (sql_num_query("SELECT * FROM `ShiftEntry` WHERE `SID`='" . sql_escape($shift['SID']) . "' AND `UID` = '" . sql_escape($user_id) . "'")) { + if (count(DB::select( + 'SELECT `id` FROM `ShiftEntry` WHERE `SID`= ? AND `UID` = ?', + [$shift['SID'], $user_id])) + ) { return error("This angel does already have an entry for this shift.", true); } - $freeloaded = $shift['freeloaded']; - $freeload_comment = $shift['freeload_comment']; + $freeloaded = isset($shift['freeloaded']) ? $shift['freeloaded'] : false; + $freeload_comment = isset($shift['freeload_comment']) ? $shift['freeload_comment'] : ''; if (in_array("user_shifts_admin", $privileges)) { $freeloaded = isset($_REQUEST['freeloaded']); $freeload_comment = strip_request_item_nl('freeload_comment'); @@ -132,27 +143,40 @@ function shift_entry_add_controller() if ( $type['restricted'] == 0 - && sql_num_query(" - SELECT * FROM `UserAngelTypes` + && count(DB::select(' + SELECT `id` FROM `UserAngelTypes` INNER JOIN `AngelTypes` ON `AngelTypes`.`id` = `UserAngelTypes`.`angeltype_id` - WHERE `angeltype_id` = '" . sql_escape($selected_type_id) . "' - AND `user_id` = '" . sql_escape($user_id) . "' - ") == 0 + WHERE `angeltype_id` = ? + AND `user_id` = ? + ', [$selected_type_id, $user_id])) == 0 ) { - sql_query("INSERT INTO `UserAngelTypes` (`user_id`, `angeltype_id`) VALUES ('" . sql_escape($user_id) . "', '" . sql_escape($selected_type_id) . "')"); + DB::insert( + 'INSERT INTO `UserAngelTypes` (`user_id`, `angeltype_id`) VALUES (?, ?)', + [$user_id, $selected_type_id] + ); } $user_source = User($user_id); - engelsystem_log('User ' . User_Nick_render($user_source) . ' signed up for shift ' . $shift['name'] . ' from ' . date('Y-m-d H:i', - $shift['start']) . ' to ' . date('Y-m-d H:i', $shift['end'])); + engelsystem_log( + 'User ' . User_Nick_render($user_source) + . ' signed up for shift ' . $shift['name'] + . ' from ' . date('Y-m-d H:i', $shift['start']) + . ' to ' . date('Y-m-d H:i', $shift['end']) + ); success(_('You are subscribed. Thank you!') . ' <a href="' . page_link_to('user_myshifts') . '">' . _('My shifts') . ' »</a>'); redirect(shift_link($shift)); } $angeltype_select = ''; if (in_array('user_shifts_admin', $privileges)) { - $users = sql_select(' - SELECT *, (SELECT count(*) FROM `ShiftEntry` WHERE `freeloaded`=1 AND `ShiftEntry`.`UID`=`User`.`UID`) AS `freeloaded` + $users = DB::select(' + SELECT *, + ( + SELECT count(*) + FROM `ShiftEntry` + WHERE `freeloaded`=1 + AND `ShiftEntry`.`UID`=`User`.`UID` + ) AS `freeloaded` FROM `User` ORDER BY `Nick` '); @@ -162,7 +186,7 @@ function shift_entry_add_controller() } $user_text = html_select_key('user_id', 'user_id', $users_select, $user['UID']); - $angeltypes_source = sql_select('SELECT * FROM `AngelTypes` ORDER BY `name`'); + $angeltypes_source = DB::select('SELECT `id`, `name` FROM `AngelTypes` ORDER BY `name`'); $angeltypes = []; foreach ($angeltypes_source as $angeltype) { $angeltypes[$angeltype['id']] = $angeltype['name']; @@ -218,7 +242,7 @@ function shift_entry_delete_controller() } $entry_id = $_REQUEST['entry_id']; - $shift_entry_source = sql_select(" + $shift_entry_source = DB::select(' SELECT `User`.`Nick`, `ShiftEntry`.`Comment`, @@ -234,9 +258,11 @@ function shift_entry_delete_controller() JOIN `Shifts` ON (`ShiftEntry`.`SID` = `Shifts`.`SID`) JOIN `ShiftTypes` ON (`ShiftTypes`.`id` = `Shifts`.`shifttype_id`) JOIN `Room` ON (`Shifts`.`RID` = `Room`.`RID`) - WHERE `ShiftEntry`.`id`='" . sql_escape($entry_id) . "'"); + WHERE `ShiftEntry`.`id`=?', + [$entry_id] + ); if (count($shift_entry_source) > 0) { - $shift_entry_source = $shift_entry_source[0]; + $shift_entry_source = array_shift($shift_entry_source); if (!in_array('user_shifts_admin', $privileges) && (!in_array('shiftentry_edit_angeltype_supporter', $privileges) || !User_is_AngelType_supporter($user, AngelType($shift_entry_source['angeltype_id']))) diff --git a/includes/controller/shifts_controller.php b/includes/controller/shifts_controller.php index 56ee1452..989f1a69 100644 --- a/includes/controller/shifts_controller.php +++ b/includes/controller/shifts_controller.php @@ -7,7 +7,11 @@ use Engelsystem\ShiftSignupState; */ function shift_link($shift) { - return page_link_to('shifts') . '&action=view&shift_id=' . $shift['SID']; + $link = page_link_to('shifts') . '&action=view'; + if (isset($shift['SID'])) { + $link .= '&shift_id=' . $shift['SID']; + } + return $link; } /** @@ -253,8 +257,15 @@ function shift_controller() $needed_angeltype = NeededAngeltype_by_Shift_and_Angeltype($shift, $angeltype); $shift_entries = ShiftEntries_by_shift_and_angeltype($shift['SID'], $angeltype['id']); - $angeltype_signup_state = Shift_signup_allowed($user, $shift, $angeltype, null, $user_shifts, $needed_angeltype, - $shift_entries); + $angeltype_signup_state = Shift_signup_allowed( + $user, + $shift, + $angeltype, + null, + $user_shifts, + $needed_angeltype, + $shift_entries + ); if ($shift_signup_state == null) { $shift_signup_state = $angeltype_signup_state; } else { @@ -304,11 +315,8 @@ function shift_next_controller() } $upcoming_shifts = ShiftEntries_upcoming_for_user($user); - if ($upcoming_shifts === false) { - return false; - } - if (count($upcoming_shifts) > 0) { + if (empty($upcoming_shifts)) { redirect(shift_link($upcoming_shifts[0])); } diff --git a/includes/controller/shifttypes_controller.php b/includes/controller/shifttypes_controller.php index 55bea389..e6ba716f 100644 --- a/includes/controller/shifttypes_controller.php +++ b/includes/controller/shifttypes_controller.php @@ -21,9 +21,6 @@ function shifttype_delete_controller() } $shifttype = ShiftType($_REQUEST['shifttype_id']); - if ($shifttype === false) { - engelsystem_error('Unable to load shifttype.'); - } if ($shifttype == null) { redirect(page_link_to('shifttypes')); @@ -31,7 +28,7 @@ function shifttype_delete_controller() if (isset($_REQUEST['confirmed'])) { $result = ShiftType_delete($shifttype['id']); - if ($result === false) { + if (empty($result)) { engelsystem_error('Unable to delete shifttype.'); } @@ -62,9 +59,6 @@ function shifttype_edit_controller() if (isset($_REQUEST['shifttype_id'])) { $shifttype = ShiftType($_REQUEST['shifttype_id']); - if ($shifttype === false) { - engelsystem_error('Unable to load shifttype.'); - } if ($shifttype == null) { error(_('Shifttype not found.')); redirect(page_link_to('shifttypes')); @@ -130,9 +124,6 @@ function shifttype_controller() redirect(page_link_to('shifttypes')); } $shifttype = ShiftType($_REQUEST['shifttype_id']); - if ($shifttype === false) { - engelsystem_error('Unable to load shifttype.'); - } if ($shifttype == null) { redirect(page_link_to('shifttypes')); } diff --git a/includes/controller/user_angeltypes_controller.php b/includes/controller/user_angeltypes_controller.php index 0855e4c9..cf2c0a3c 100644 --- a/includes/controller/user_angeltypes_controller.php +++ b/includes/controller/user_angeltypes_controller.php @@ -153,9 +153,6 @@ function user_angeltype_confirm_controller() if (isset($_REQUEST['confirmed'])) { $result = UserAngelType_confirm($user_angeltype['id'], $user); - if ($result === false) { - engelsystem_error('Unable to confirm user angeltype.'); - } engelsystem_log(sprintf( '%s confirmed for angeltype %s', diff --git a/includes/controller/users_controller.php b/includes/controller/users_controller.php index 72bddd14..b80fdb4d 100644 --- a/includes/controller/users_controller.php +++ b/includes/controller/users_controller.php @@ -1,4 +1,6 @@ <?php + +use Engelsystem\Database\DB; use Engelsystem\ShiftCalendarRenderer; use Engelsystem\ShiftsFilter; @@ -191,20 +193,25 @@ function user_controller() $shifts = Shifts_by_user($user_source, in_array('user_shifts_admin', $privileges)); foreach ($shifts as &$shift) { // TODO: Move queries to model - $shift['needed_angeltypes'] = sql_select(" + $shift['needed_angeltypes'] = DB::select(' SELECT DISTINCT `AngelTypes`.* FROM `ShiftEntry` JOIN `AngelTypes` ON `ShiftEntry`.`TID`=`AngelTypes`.`id` - WHERE `ShiftEntry`.`SID`='" . sql_escape($shift['SID']) . "' + WHERE `ShiftEntry`.`SID` = ? ORDER BY `AngelTypes`.`name` - "); + ', + [$shift['SID']] + ); foreach ($shift['needed_angeltypes'] as &$needed_angeltype) { - $needed_angeltype['users'] = sql_select(" - SELECT `ShiftEntry`.`freeloaded`, `User`.* - FROM `ShiftEntry` - JOIN `User` ON `ShiftEntry`.`UID`=`User`.`UID` - WHERE `ShiftEntry`.`SID`='" . sql_escape($shift['SID']) . "' - AND `ShiftEntry`.`TID`='" . sql_escape($needed_angeltype['id']) . "'"); + $needed_angeltype['users'] = DB::select(' + SELECT `ShiftEntry`.`freeloaded`, `User`.* + FROM `ShiftEntry` + JOIN `User` ON `ShiftEntry`.`UID`=`User`.`UID` + WHERE `ShiftEntry`.`SID` = ? + AND `ShiftEntry`.`TID` = ? + ', + [$shift['SID'], $needed_angeltype['id']] + ); } } @@ -387,9 +394,6 @@ function load_user() } $user = User($_REQUEST['user_id']); - if ($user === false) { - engelsystem_error('Unable to load user.'); - } if ($user == null) { error(_('User doesn\'t exist.')); diff --git a/includes/engelsystem_provider.php b/includes/engelsystem_provider.php index 3f46e4ab..3537f100 100644 --- a/includes/engelsystem_provider.php +++ b/includes/engelsystem_provider.php @@ -1,17 +1,17 @@ <?php +use Engelsystem\Database\Db; use Engelsystem\Exceptions\Handler as ExceptionHandler; /** * This file includes all needed functions, connects to the db etc. */ + if (!is_readable(__DIR__ . '/../vendor/autoload.php')) { die('Please run composer.phar install'); } require __DIR__ . '/../vendor/autoload.php'; -require_once realpath(__DIR__ . '/../includes/mysqli_provider.php'); - require_once realpath(__DIR__ . '/../includes/sys_auth.php'); require_once realpath(__DIR__ . '/../includes/sys_form.php'); require_once realpath(__DIR__ . '/../includes/sys_log.php'); @@ -107,10 +107,15 @@ $errorHandler = new ExceptionHandler( ) ); +Db::connect( + 'mysql:host=' . $config['host'] . ';dbname=' . $config['db'] . ';charset=utf8', + $config['user'], + $config['pw'] +) || die('Error: Unable to connect to database'); +Db::getPdo()->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); + session_start(); gettext_init(); -sql_connect($config['host'], $config['user'], $config['pw'], $config['db']); - load_auth(); diff --git a/includes/model/AngelType_model.php b/includes/model/AngelType_model.php index c3270863..411c69ea 100644 --- a/includes/model/AngelType_model.php +++ b/includes/model/AngelType_model.php @@ -1,4 +1,6 @@ <?php + +use Engelsystem\Database\DB; use Engelsystem\ValidationResult; /** @@ -75,43 +77,56 @@ function AngelType_contact_info($angeltype) * Delete an Angeltype. * * @param array $angeltype - * @return mysqli_result + * @return bool */ function AngelType_delete($angeltype) { - $result = sql_query(" + $result = DB::delete(' DELETE FROM `AngelTypes` - WHERE `id`='" . sql_escape($angeltype['id']) . "' + WHERE `id`=? LIMIT 1 - "); - if ($result === false) { + ', [$angeltype['id']]); + if (is_null($result)) { engelsystem_error('Unable to delete angeltype.'); } engelsystem_log('Deleted angeltype: ' . AngelType_name_render($angeltype)); - return $result; + return true; } /** * Update Angeltype. * * @param array $angeltype The angeltype - * @return mysqli_result + * @return bool */ function AngelType_update($angeltype) { - $result = sql_query(" - UPDATE `AngelTypes` SET - `name`='" . sql_escape($angeltype['name']) . "', - `restricted`=" . sql_bool($angeltype['restricted']) . ", - `description`='" . sql_escape($angeltype['description']) . "', - `requires_driver_license`=" . sql_bool($angeltype['requires_driver_license']) . ", - `no_self_signup`=" . sql_bool($angeltype['no_self_signup']) . ", - `contact_user_id`=" . sql_null($angeltype['contact_user_id']) . ", - `contact_name`=" . sql_null($angeltype['contact_name']) . ", - `contact_dect`=" . sql_null($angeltype['contact_dect']) . ", - `contact_email`=" . sql_null($angeltype['contact_email']) . " - WHERE `id`='" . sql_escape($angeltype['id']) . "'"); - if ($result === false) { + $result = DB::update(' + UPDATE `AngelTypes` SET + `name` = ?, + `restricted` = ?, + `description` = ?, + `requires_driver_license` = ?, + `no_self_signup` = ?, + `contact_user_id` = ?, + `contact_name` = ?, + `contact_dect` = ?, + `contact_email` = ? + WHERE `id` = ?', + [ + $angeltype['name'], + $angeltype['restricted'], + $angeltype['description'], + $angeltype['requires_driver_license'], + $angeltype['no_self_signup'], + $angeltype['contact_user_id'], + $angeltype['contact_name'], + $angeltype['contact_dect'], + $angeltype['contact_email'], + $angeltype['id'], + ] + ); + if (is_null($result)) { engelsystem_error('Unable to update angeltype.'); } engelsystem_log( @@ -119,7 +134,7 @@ function AngelType_update($angeltype) . ($angeltype['no_self_signup'] ? ', no_self_signup' : '') . ($angeltype['requires_driver_license'] ? ', requires driver license' : '') ); - return $result; + return true; } /** @@ -130,24 +145,41 @@ function AngelType_update($angeltype) */ function AngelType_create($angeltype) { - $result = sql_query(" - INSERT INTO `AngelTypes` SET - `name`='" . sql_escape($angeltype['name']) . "', - `restricted`=" . sql_bool($angeltype['restricted']) . ", - `description`='" . sql_escape($angeltype['description']) . "', - `requires_driver_license`=" . sql_bool($angeltype['requires_driver_license']) . ", - `no_self_signup`=" . sql_bool($angeltype['no_self_signup']) . ", - `contact_user_id`=" . sql_null($angeltype['contact_user_id']) . ", - `contact_name`=" . sql_null($angeltype['contact_name']) . ", - `contact_dect`=" . sql_null($angeltype['contact_dect']) . ", - `contact_email`=" . sql_null($angeltype['contact_email'])); - if ($result === false) { - engelsystem_error("Unable to create angeltype."); + $result = DB::insert(' + INSERT INTO `AngelTypes` ( + `name`, + `restricted`, + `description`, + `requires_driver_license`, + `no_self_signup`, + `contact_user_id`, + `contact_name`, + `contact_dect`, + `contact_email` + ) + VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) + ', + [ + $angeltype['name'], + (bool)$angeltype['restricted'], + $angeltype['description'], + (bool)$angeltype['requires_driver_license'], + (bool)$angeltype['no_self_signup'], + $angeltype['contact_user_id'], + $angeltype['contact_name'], + $angeltype['contact_dect'], + $angeltype['contact_email'], + ] + ); + if (is_null($result)) { + engelsystem_error('Unable to create angeltype.'); } - $angeltype['id'] = sql_id(); + $angeltype['id'] = DB::getPdo()->lastInsertId(); engelsystem_log( - 'Created angeltype: ' . $angeltype['name'] . ($angeltype['restricted'] ? ', restricted' : '') - . ($angeltype['requires_driver_license'] ? ', requires driver license' : '')); + 'Created angeltype: ' . $angeltype['name'] + . ($angeltype['restricted'] ? ', restricted' : '') + . ($angeltype['requires_driver_license'] ? ', requires driver license' : '') + ); return $angeltype; } @@ -167,19 +199,20 @@ function AngelType_validate_name($name, $angeltype) return new ValidationResult(false, ''); } if ($angeltype != null && isset($angeltype['id'])) { - $valid = sql_num_query(" - SELECT * - FROM `AngelTypes` - WHERE `name`='" . sql_escape($name) . "' - AND NOT `id`='" . sql_escape($angeltype['id']) . "' - LIMIT 1") == 0; + $valid = (count(DB::select(' + SELECT `id` + FROM `AngelTypes` + WHERE `name`=? + AND NOT `id`=? + LIMIT 1 + ', [$name, $angeltype['id']])) == 0); return new ValidationResult($valid, $name); } - $valid = sql_num_query(" + $valid = (count(DB::select(' SELECT `id` FROM `AngelTypes` - WHERE `name`='" . sql_escape($name) . "' - LIMIT 1") == 0; + WHERE `name`=? + LIMIT 1', [$name])) == 0); return new ValidationResult($valid, $name); } @@ -191,16 +224,17 @@ function AngelType_validate_name($name, $angeltype) */ function AngelTypes_with_user($user) { - $result = sql_select(" + $result = DB::select(' SELECT `AngelTypes`.*, `UserAngelTypes`.`id` AS `user_angeltype_id`, `UserAngelTypes`.`confirm_user_id`, `UserAngelTypes`.`supporter` FROM `AngelTypes` LEFT JOIN `UserAngelTypes` ON `AngelTypes`.`id`=`UserAngelTypes`.`angeltype_id` - AND `UserAngelTypes`.`user_id`=" . $user['UID'] . " - ORDER BY `name`"); - if ($result === false) { + AND `UserAngelTypes`.`user_id` = ? + ORDER BY `name`', [$user['UID']]); + + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load angeltypes.'); } return $result; @@ -213,11 +247,12 @@ function AngelTypes_with_user($user) */ function AngelTypes() { - $result = sql_select(" + $result = DB::select(' SELECT * FROM `AngelTypes` - ORDER BY `name`"); - if ($result === false) { + ORDER BY `name`'); + + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load angeltypes.'); } return $result; @@ -230,8 +265,9 @@ function AngelTypes() */ function AngelType_ids() { - $result = sql_select("SELECT `id` FROM `AngelTypes`"); - if ($result === false) { + $result = DB::select('SELECT `id` FROM `AngelTypes`'); + + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load angeltypes.'); } return select_array($result, 'id', 'id'); @@ -241,16 +277,22 @@ function AngelType_ids() * Returns angelType by id. * * @param int $angeltype_id angelType ID - * @return array + * @return array|null */ function AngelType($angeltype_id) { - $angelType_source = sql_select("SELECT * FROM `AngelTypes` WHERE `id`='" . sql_escape($angeltype_id) . "'"); - if ($angelType_source === false) { + $angelType_source = DB::select( + 'SELECT * FROM `AngelTypes` WHERE `id`=?', + [$angeltype_id] + ); + + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load angeltype.'); } - if (count($angelType_source) > 0) { - return $angelType_source[0]; + + if (empty($angelType_source)) { + return null; } - return null; + + return array_shift($angelType_source); } diff --git a/includes/model/EventConfig_model.php b/includes/model/EventConfig_model.php index 330863a9..773ee2e0 100644 --- a/includes/model/EventConfig_model.php +++ b/includes/model/EventConfig_model.php @@ -1,21 +1,25 @@ <?php +use Engelsystem\Database\DB; + /** * Get event config. * - * łreturn array|false|null + * @return array|null */ function EventConfig() { - $event_config = sql_select('SELECT * FROM `EventConfig` LIMIT 1'); - if ($event_config === false) { + $event_config = DB::select('SELECT * FROM `EventConfig` LIMIT 1'); + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load event config.'); - return false; + return null; } - if (count($event_config) > 0) { - return $event_config[0]; + + if (empty($event_config)) { + return null; } - return null; + + return array_shift($event_config); } /** @@ -27,7 +31,7 @@ function EventConfig() * @param int $event_end_date * @param int $teardown_end_date * @param string $event_welcome_msg - * @return mysqli_result|false + * @return bool */ function EventConfig_update( $event_name, @@ -38,20 +42,44 @@ function EventConfig_update( $event_welcome_msg ) { if (EventConfig() == null) { - return sql_query("INSERT INTO `EventConfig` SET - `event_name`=" . sql_null($event_name) . ", - `buildup_start_date`=" . sql_null($buildup_start_date) . ", - `event_start_date`=" . sql_null($event_start_date) . ", - `event_end_date`=" . sql_null($event_end_date) . ", - `teardown_end_date`=" . sql_null($teardown_end_date) . ", - `event_welcome_msg`=" . sql_null($event_welcome_msg)); + return DB::insert(' + INSERT INTO `EventConfig` ( + `event_name`, + `buildup_start_date`, + `event_start_date`, + `event_end_date`, + `teardown_end_date`, + `event_welcome_msg` + ) + VALUES (?, ?, ?, ?, ?, ?) + ', + [ + $event_name, + $buildup_start_date, + $event_start_date, + $event_end_date, + $teardown_end_date, + $event_welcome_msg + ] + ); } - return sql_query("UPDATE `EventConfig` SET - `event_name`=" . sql_null($event_name) . ", - `buildup_start_date`=" . sql_null($buildup_start_date) . ", - `event_start_date`=" . sql_null($event_start_date) . ", - `event_end_date`=" . sql_null($event_end_date) . ", - `teardown_end_date`=" . sql_null($teardown_end_date) . ", - `event_welcome_msg`=" . sql_null($event_welcome_msg)); + return (bool)DB::update(' + UPDATE `EventConfig` SET + `event_name` = ?, + `buildup_start_date` = ?, + `event_start_date` = ?, + `event_end_date` = ?, + `teardown_end_date` = ?, + `event_welcome_msg` = ? + ', + [ + $event_name, + $buildup_start_date, + $event_start_date, + $event_end_date, + $teardown_end_date, + $event_welcome_msg, + ] + ); } diff --git a/includes/model/LogEntries_model.php b/includes/model/LogEntries_model.php index 920b1945..0e11bf8e 100644 --- a/includes/model/LogEntries_model.php +++ b/includes/model/LogEntries_model.php @@ -1,59 +1,62 @@ <?php +use Engelsystem\Database\DB; + /** * Creates a log entry. * * @param string $nick Username * @param string $message Log Message - * @return mysqli_result|false + * @return bool */ function LogEntry_create($nick, $message) { - return sql_query(" - INSERT INTO `LogEntries` - SET - `timestamp`='" . sql_escape(time()) . "', - `nick`='" . sql_escape($nick) . "', - `message`='" . sql_escape($message) . "' - "); + return DB::insert(' + INSERT INTO `LogEntries` (`timestamp`, `nick`, `message`) + VALUES(?, ?, ?) + ', [time(), $nick, $message]); } /** * Returns log entries with maximum count of 10000. * - * @return array|false + * @return array */ function LogEntries() { - return sql_select('SELECT * FROM `LogEntries` ORDER BY `timestamp` DESC LIMIT 10000'); + return DB::select('SELECT * FROM `LogEntries` ORDER BY `timestamp` DESC LIMIT 10000'); } /** * Returns log entries filtered by a keyword * * @param string $keyword - * @return array|false + * @return array */ function LogEntries_filter($keyword) { if ($keyword == '') { return LogEntries(); } - return sql_select(" - SELECT * - FROM `LogEntries` - WHERE `nick` LIKE '%" . sql_escape($keyword) . "%' - OR `message` LIKE '%" . sql_escape($keyword) . "%' - ORDER BY `timestamp` DESC - "); + + $keyword = '%' . $keyword . '%'; + return DB::select(' + SELECT * + FROM `LogEntries` + WHERE `nick` LIKE ? + OR `message` LIKE ? + ORDER BY `timestamp` DESC + ', + [$keyword, $keyword] + ); } /** * Delete all log entries. * - * @return mysqli_result|false + * @return bool */ function LogEntries_clear_all() { - return sql_query('TRUNCATE `LogEntries`'); + return DB::statement('TRUNCATE `LogEntries`'); } diff --git a/includes/model/Message_model.php b/includes/model/Message_model.php index e998ba04..a7da63b8 100644 --- a/includes/model/Message_model.php +++ b/includes/model/Message_model.php @@ -1,31 +1,30 @@ <?php +use Engelsystem\Database\DB; + /** * Returns Message id array * - * @return array|false + * @return array */ function Message_ids() { - return sql_select('SELECT `id` FROM `Messages`'); + return DB::select('SELECT `id` FROM `Messages`'); } /** * Returns message by id. * * @param int $message_id message ID - * @return array|false|null + * @return array|null */ function Message($message_id) { - $message_source = sql_select("SELECT * FROM `Messages` WHERE `id`='" . sql_escape($message_id) . "' LIMIT 1"); - if ($message_source === false) { - return false; - } - if (count($message_source) > 0) { - return $message_source[0]; + $message_source = DB::select('SELECT * FROM `Messages` WHERE `id`=? LIMIT 1', [$message_id]); + if (empty($message_source)) { + return null; } - return null; + return array_shift($message_source); } /** @@ -46,22 +45,25 @@ function Message_send($receiver_user_id, $text) if ( ($text != '' && is_numeric($receiver_user_id)) - && (sql_num_query(" - SELECT * + && count(DB::select(' + SELECT `UID` FROM `User` - WHERE `UID`='" . sql_escape($receiver_user_id) . "' - AND NOT `UID`='" . sql_escape($user['UID']) . "' + WHERE `UID` = ? + AND NOT `UID` = ? LIMIT 1 - ") > 0) + ', [$receiver_user_id, $user['UID']])) > 0 ) { - sql_query(" - INSERT INTO `Messages` - SET `Datum`='" . sql_escape(time()) . "', - `SUID`='" . sql_escape($user['UID']) . "', - `RUID`='" . sql_escape($receiver_user_id) . "', - `Text`='" . sql_escape($text) . "' - "); - return true; + return DB::insert(' + INSERT INTO `Messages` (`Datum`, `SUID`, `RUID`, `Text`) + VALUES(?, ?, ?, ?) + ', + [ + time(), + $user['UID'], + $receiver_user_id, + $text + ] + ); } return false; diff --git a/includes/model/NeededAngelTypes_model.php b/includes/model/NeededAngelTypes_model.php index f65efc41..97b085f0 100644 --- a/includes/model/NeededAngelTypes_model.php +++ b/includes/model/NeededAngelTypes_model.php @@ -1,5 +1,7 @@ <?php +use Engelsystem\Database\DB; + /** * Entity needed angeltypes describes how many angels of given type are needed for a shift or in a room. */ @@ -7,50 +9,54 @@ /** * Insert a new needed angel type. * - * @param int $shift_id - * The shift. Can be null, but then a room_id must be given. - * @param int $angeltype_id - * The angeltype - * @param int $room_id - * The room. Can be null, but then a shift_id must be given. - * @param int $count - * How many angels are needed? - * @return false|int + * @param int $shift_id The shift. Can be null, but then a room_id must be given. + * @param int $angeltype_id The angeltype + * @param int $room_id The room. Can be null, but then a shift_id must be given. + * @param int $count How many angels are needed? + * @return int|false */ function NeededAngelType_add($shift_id, $angeltype_id, $room_id, $count) { - $result = sql_query(" - INSERT INTO `NeededAngelTypes` SET - `shift_id`=" . sql_null($shift_id) . ", - `angel_type_id`='" . sql_escape($angeltype_id) . "', - `room_id`=" . sql_null($room_id) . ", - `count`='" . sql_escape($count) . "'"); + $result = DB::insert(' + INSERT INTO `NeededAngelTypes` ( `shift_id`, `angel_type_id`, `room_id`, `count`) + VALUES (?, ?, ?, ?) + ', + [ + $shift_id, + $angeltype_id, + $room_id, + $count, + ]); if ($result === false) { return false; } - return sql_id(); + + return DB::getPdo()->lastInsertId(); } /** * Deletes all needed angel types from given shift. * * @param int $shift_id id of the shift - * @return mysqli_result|false + * @return int count of affected rows */ function NeededAngelTypes_delete_by_shift($shift_id) { - return sql_query("DELETE FROM `NeededAngelTypes` WHERE `shift_id`='" . sql_escape($shift_id) . "'"); + return (int)DB::delete('DELETE FROM `NeededAngelTypes` WHERE `shift_id` = ?', [$shift_id]); } /** * Deletes all needed angel types from given room. * * @param int $room_id id of the room - * @return mysqli_result|false + * @return int count of affected rows */ function NeededAngelTypes_delete_by_room($room_id) { - return sql_query("DELETE FROM `NeededAngelTypes` WHERE `room_id`='" . sql_escape($room_id) . "'"); + return (int)DB::delete( + 'DELETE FROM `NeededAngelTypes` WHERE `room_id` = ?', + [$room_id] + ); } /** @@ -61,30 +67,31 @@ function NeededAngelTypes_delete_by_room($room_id) */ function NeededAngelTypes_by_shift($shiftId) { - $needed_angeltypes_source = sql_select(" + $needed_angeltypes_source = DB::select(' SELECT `NeededAngelTypes`.*, `AngelTypes`.`id`, `AngelTypes`.`name`, `AngelTypes`.`restricted`, `AngelTypes`.`no_self_signup` FROM `NeededAngelTypes` JOIN `AngelTypes` ON `AngelTypes`.`id` = `NeededAngelTypes`.`angel_type_id` - WHERE `shift_id`='" . sql_escape($shiftId) . "' + WHERE `shift_id` = ? AND `count` > 0 - ORDER BY `room_id` DESC - "); - if ($needed_angeltypes_source === false) { + ORDER BY `room_id` DESC', + [$shiftId] + ); + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load needed angeltypes.'); } // Use settings from room if (count($needed_angeltypes_source) == 0) { - $needed_angeltypes_source = sql_select(" + $needed_angeltypes_source = DB::select(' SELECT `NeededAngelTypes`.*, `AngelTypes`.`name`, `AngelTypes`.`restricted` FROM `NeededAngelTypes` JOIN `AngelTypes` ON `AngelTypes`.`id` = `NeededAngelTypes`.`angel_type_id` JOIN `Shifts` ON `Shifts`.`RID` = `NeededAngelTypes`.`room_id` - WHERE `Shifts`.`SID`='" . sql_escape($shiftId) . "' + WHERE `Shifts`.`SID` = ? AND `count` > 0 ORDER BY `room_id` DESC - "); - if ($needed_angeltypes_source === false) { + ', [$shiftId]); + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load needed angeltypes.'); } } diff --git a/includes/model/Room_model.php b/includes/model/Room_model.php index 3eb9f452..c8399bc4 100644 --- a/includes/model/Room_model.php +++ b/includes/model/Room_model.php @@ -1,53 +1,56 @@ <?php +use Engelsystem\Database\DB; + /** * returns a list of rooms. * * @param boolean $show_all returns also hidden rooms when true - * @return array|false + * @return array */ function Rooms($show_all = false) { - return sql_select("SELECT * FROM `Room`" . ($show_all ? "" : " WHERE `show`='Y'") . " ORDER BY `Name`"); + return DB::select('SELECT * FROM `Room`' . ($show_all ? '' : ' WHERE `show`=\'Y\'') . ' ORDER BY `Name`'); } /** * Delete a room * * @param int $room_id - * @return mysqli_result|false + * @return bool */ function Room_delete($room_id) { - return sql_query('DELETE FROM `Room` WHERE `RID`=' . sql_escape($room_id)); + return DB::delete('DELETE FROM `Room` WHERE `RID` = ?', [$room_id]); } /** * Create a new room * - * @param string $name - * Name of the room - * @param boolean $from_frab - * Is this a frab imported room? - * @param boolean $public - * Is the room visible for angels? - * @param int $number - * Room number + * @param string $name Name of the room + * @param boolean $from_frab Is this a frab imported room? + * @param boolean $public Is the room visible for angels? + * @param int $number Room number * @return false|int */ function Room_create($name, $from_frab, $public, $number = null) { - $result = sql_query(" - INSERT INTO `Room` SET - `Name`='" . sql_escape($name) . "', - `FromPentabarf`='" . sql_escape($from_frab ? 'Y' : '') . "', - `show`='" . sql_escape($public ? 'Y' : '') . "', - `Number`=" . (int)$number + $result = DB::insert(' + INSERT INTO `Room` (`Name`, `FromPentabarf`, `show`, `Number`) + VALUES (?, ?, ?, ?) + ', + [ + $name, + $from_frab ? 'Y' : '', + $public ? 'Y' : '', + (int)$number, + ] ); - if ($result === false) { + if (!$result) { return false; } - return sql_id(); + + return DB::getPdo()->lastInsertId(); } /** @@ -59,18 +62,21 @@ function Room_create($name, $from_frab, $public, $number = null) */ function Room($room_id, $show_only = true) { - $room_source = sql_select(" + $room_source = DB::select(' SELECT * FROM `Room` - WHERE `RID`='" . sql_escape($room_id) . "' - " . ($show_only ? "AND `show` = 'Y'" : '') + WHERE `RID` = ? + ' . ($show_only ? 'AND `show` = \'Y\'' : ''), + [$room_id] ); - if ($room_source === false) { + if (DB::getStm()->errorCode() != '00000') { return false; } - if (count($room_source) > 0) { - return $room_source[0]; + + if (empty($room_source)) { + return null; } - return null; + + return array_shift($room_source); } diff --git a/includes/model/ShiftEntry_model.php b/includes/model/ShiftEntry_model.php index acdb4160..87e186ac 100644 --- a/includes/model/ShiftEntry_model.php +++ b/includes/model/ShiftEntry_model.php @@ -1,5 +1,7 @@ <?php +use Engelsystem\Database\DB; + /** * Returns an array with the attributes of shift entries. * FIXME! Needs entity object. @@ -26,67 +28,100 @@ function ShiftEntry_new() */ function ShiftEntries_freeleaded_count() { - return (int)sql_select_single_cell('SELECT COUNT(*) FROM `ShiftEntry` WHERE `freeloaded` = 1'); + $result = DB::select('SELECT COUNT(*) FROM `ShiftEntry` WHERE `freeloaded` = 1'); + $result = array_shift($result); + + if (!is_array($result)) { + return 0; + } + + return (int)array_shift($result); } /** * List users subsribed to a given shift. * * @param int $shift_id - * @return array|false + * @return array */ function ShiftEntries_by_shift($shift_id) { - return sql_select(" - SELECT - `User`.`Nick`, - `User`.`email`, - `User`.`email_shiftinfo`, - `User`.`Sprache`, - `User`.`Gekommen`, - `ShiftEntry`.`UID`, - `ShiftEntry`.`TID`, - `ShiftEntry`.`SID`, - `AngelTypes`.`name` AS `angel_type_name`, - `ShiftEntry`.`Comment`, - `ShiftEntry`.`freeloaded` - FROM `ShiftEntry` - JOIN `User` ON `ShiftEntry`.`UID`=`User`.`UID` - JOIN `AngelTypes` ON `ShiftEntry`.`TID`=`AngelTypes`.`id` - WHERE `ShiftEntry`.`SID`='" . sql_escape($shift_id) . "'"); + return DB::select(" + SELECT + `User`.`Nick`, + `User`.`email`, + `User`.`email_shiftinfo`, + `User`.`Sprache`, + `User`.`Gekommen`, + `ShiftEntry`.`UID`, + `ShiftEntry`.`TID`, + `ShiftEntry`.`SID`, + `AngelTypes`.`name` AS `angel_type_name`, + `ShiftEntry`.`Comment`, + `ShiftEntry`.`freeloaded` + FROM `ShiftEntry` + JOIN `User` ON `ShiftEntry`.`UID`=`User`.`UID` + JOIN `AngelTypes` ON `ShiftEntry`.`TID`=`AngelTypes`.`id` + WHERE `ShiftEntry`.`SID` = ?", + [$shift_id] + ); } /** * Create a new shift entry. * * @param array $shift_entry - * @return mysqli_result|false + * @return bool */ function ShiftEntry_create($shift_entry) { mail_shift_assign(User($shift_entry['UID']), Shift($shift_entry['SID'])); - return sql_query("INSERT INTO `ShiftEntry` SET - `SID`='" . sql_escape($shift_entry['SID']) . "', - `TID`='" . sql_escape($shift_entry['TID']) . "', - `UID`='" . sql_escape($shift_entry['UID']) . "', - `Comment`='" . sql_escape($shift_entry['Comment']) . "', - `freeload_comment`='" . sql_escape($shift_entry['freeload_comment']) . "', - `freeloaded`=" . sql_bool($shift_entry['freeloaded'])); + return DB::insert(' + INSERT INTO `ShiftEntry` ( + `SID`, + `TID`, + `UID`, + `Comment`, + `freeload_comment`, + `freeloaded` + ) + VALUES(?, ?, ?, ?, ?, ?) + ', + [ + $shift_entry['SID'], + $shift_entry['TID'], + $shift_entry['UID'], + $shift_entry['Comment'], + $shift_entry['freeload_comment'], + $shift_entry['freeloaded'], + ] + ); } /** * Update a shift entry. * * @param array $shift_entry - * @return false|mysqli_result + * @return bool */ function ShiftEntry_update($shift_entry) { - return sql_query("UPDATE `ShiftEntry` SET - `Comment`='" . sql_escape($shift_entry['Comment']) . "', - `freeload_comment`='" . sql_escape($shift_entry['freeload_comment']) . "', - `freeloaded`=" . sql_bool($shift_entry['freeloaded']) . " - WHERE `id`='" . sql_escape($shift_entry['id']) . "'"); + DB::update(' + UPDATE `ShiftEntry` + SET + `Comment` = ?, + `freeload_comment` = ?, + `freeloaded` = ? + WHERE `id` = ?', + [ + $shift_entry['Comment'], + $shift_entry['freeload_comment'], + $shift_entry['freeloaded'], + $shift_entry['id'] + ] + ); + + return (DB::getStm()->errorCode() == '00000'); } /** @@ -97,11 +132,11 @@ function ShiftEntry_update($shift_entry) */ function ShiftEntry($shift_entry_id) { - $shift_entry = sql_select("SELECT * FROM `ShiftEntry` WHERE `id`='" . sql_escape($shift_entry_id) . "'"); - if ($shift_entry === false) { + $shift_entry = DB::select('SELECT * FROM `ShiftEntry` WHERE `id` = ?', [$shift_entry_id]); + if (DB::getStm()->errorCode() != '00000') { return false; } - if (count($shift_entry) == 0) { + if (empty($shift_entry)) { return null; } return $shift_entry[0]; @@ -111,52 +146,62 @@ function ShiftEntry($shift_entry_id) * Delete a shift entry. * * @param int $shift_entry_id - * @return mysqli_result|false + * @return bool */ function ShiftEntry_delete($shift_entry_id) { $shift_entry = ShiftEntry($shift_entry_id); mail_shift_removed(User($shift_entry['UID']), Shift($shift_entry['SID'])); - return sql_query("DELETE FROM `ShiftEntry` WHERE `id`='" . sql_escape($shift_entry_id) . "'"); + return DB::delete('DELETE FROM `ShiftEntry` WHERE `id` = ?', [$shift_entry_id]); } /** * Returns next (or current) shifts of given user. * * @param array $user - * @return array|false + * @return array */ function ShiftEntries_upcoming_for_user($user) { - return sql_select(" - SELECT * - FROM `ShiftEntry` - JOIN `Shifts` ON (`Shifts`.`SID` = `ShiftEntry`.`SID`) - JOIN `ShiftTypes` ON `ShiftTypes`.`id` = `Shifts`.`shifttype_id` - WHERE `ShiftEntry`.`UID`=" . sql_escape($user['UID']) . " - AND `Shifts`.`end` > " . sql_escape(time()) . " - ORDER BY `Shifts`.`end` - "); + return DB::select(' + SELECT * + FROM `ShiftEntry` + JOIN `Shifts` ON (`Shifts`.`SID` = `ShiftEntry`.`SID`) + JOIN `ShiftTypes` ON `ShiftTypes`.`id` = `Shifts`.`shifttype_id` + WHERE `ShiftEntry`.`UID` = ? + AND `Shifts`.`end` > ? + ORDER BY `Shifts`.`end` + ', + [ + $user['UID'], + time(), + ] + ); } /** * Returns shifts completed by the given user. * * @param array $user - * @return array|false + * @return array */ function ShiftEntries_finished_by_user($user) { - return sql_select(" - SELECT * - FROM `ShiftEntry` - JOIN `Shifts` ON (`Shifts`.`SID` = `ShiftEntry`.`SID`) - JOIN `ShiftTypes` ON `ShiftTypes`.`id` = `Shifts`.`shifttype_id` - WHERE `ShiftEntry`.`UID`=" . sql_escape($user['UID']) . " - AND `Shifts`.`end` < " . sql_escape(time()) . " - AND `ShiftEntry`.`freeloaded` = 0 - ORDER BY `Shifts`.`end` - "); + return DB::select(' + SELECT * + FROM `ShiftEntry` + JOIN `Shifts` ON (`Shifts`.`SID` = `ShiftEntry`.`SID`) + JOIN `ShiftTypes` ON `ShiftTypes`.`id` = `Shifts`.`shifttype_id` + WHERE `ShiftEntry`.`UID` = ? + AND `Shifts`.`end` < ? + AND `ShiftEntry`.`freeloaded` = 0 + ORDER BY `Shifts`.`end` + ', + [ + $user['UID'], + time(), + ] + ); } /** @@ -164,17 +209,22 @@ function ShiftEntries_finished_by_user($user) * * @param int $shift_id * @param int $angeltype_id - * @return array|false + * @return array */ function ShiftEntries_by_shift_and_angeltype($shift_id, $angeltype_id) { - $result = sql_select(" - SELECT * - FROM `ShiftEntry` - WHERE `SID`=" . sql_escape($shift_id) . " - AND `TID`=" . sql_escape($angeltype_id) . " - "); - if ($result === false) { + $result = DB::select(' + SELECT * + FROM `ShiftEntry` + WHERE `SID` = ? + AND `TID` = ? + ', + [ + $shift_id, + $angeltype_id, + ] + ); + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load shift entries.'); } return $result; @@ -184,12 +234,18 @@ function ShiftEntries_by_shift_and_angeltype($shift_id, $angeltype_id) * Returns all freeloaded shifts for given user. * * @param array $user - * @return array|false + * @return array */ function ShiftEntries_freeloaded_by_user($user) { - return sql_select("SELECT * - FROM `ShiftEntry` - WHERE `freeloaded` = 1 - AND `UID`=" . sql_escape($user['UID'])); + return DB::select(' + SELECT * + FROM `ShiftEntry` + WHERE `freeloaded` = 1 + AND `UID` = ? + ', + [ + $user['UID'] + ] + ); } diff --git a/includes/model/ShiftTypes_model.php b/includes/model/ShiftTypes_model.php index 03a98bd8..4919875b 100644 --- a/includes/model/ShiftTypes_model.php +++ b/includes/model/ShiftTypes_model.php @@ -1,14 +1,16 @@ <?php +use Engelsystem\Database\DB; + /** * Delete a shift type. * * @param int $shifttype_id - * @return mysqli_result|false + * @return bool */ function ShiftType_delete($shifttype_id) { - return sql_query("DELETE FROM `ShiftTypes` WHERE `id`='" . sql_escape($shifttype_id) . "'"); + return DB::delete('DELETE FROM `ShiftTypes` WHERE `id`=?', [$shifttype_id]); } /** @@ -18,17 +20,26 @@ function ShiftType_delete($shifttype_id) * @param string $name * @param int $angeltype_id * @param string $description - * @return mysqli_result|false + * @return bool */ function ShiftType_update($shifttype_id, $name, $angeltype_id, $description) { - return sql_query(" + DB::update(' UPDATE `ShiftTypes` SET - `name`='" . sql_escape($name) . "', - `angeltype_id`=" . sql_null($angeltype_id) . ", - `description`='" . sql_escape($description) . "' - WHERE `id`='" . sql_escape($shifttype_id) . "' - "); + `name`=?, + `angeltype_id`=?, + `description`=? + WHERE `id`=? + ', + [ + $name, + $angeltype_id, + $description, + $shifttype_id, + ] + ); + + return DB::getStm()->errorCode() == '00000'; } /** @@ -41,16 +52,22 @@ function ShiftType_update($shifttype_id, $name, $angeltype_id, $description) */ function ShiftType_create($name, $angeltype_id, $description) { - $result = sql_query(" - INSERT INTO `ShiftTypes` SET - `name`='" . sql_escape($name) . "', - `angeltype_id`=" . sql_null($angeltype_id) . ", - `description`='" . sql_escape($description) . "' - "); + $result = DB::insert(' + INSERT INTO `ShiftTypes` (`name`, `angeltype_id`, `description`) + VALUES(?, ?, ?) + ', + [ + $name, + $angeltype_id, + $description + ] + ); + if ($result === false) { return false; } - return sql_id(); + + return DB::getPdo()->lastInsertId(); } /** @@ -61,14 +78,14 @@ function ShiftType_create($name, $angeltype_id, $description) */ function ShiftType($shifttype_id) { - $shifttype = sql_select("SELECT * FROM `ShiftTypes` WHERE `id`='" . sql_escape($shifttype_id) . "'"); - if ($shifttype === false) { + $shifttype = DB::select('SELECT * FROM `ShiftTypes` WHERE `id`=?', [$shifttype_id]); + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load shift type.'); } - if ($shifttype == null) { + if (empty($shifttype)) { return null; } - return $shifttype[0]; + return array_shift($shifttype); } /** @@ -78,5 +95,11 @@ function ShiftType($shifttype_id) */ function ShiftTypes() { - return sql_select('SELECT * FROM `ShiftTypes` ORDER BY `name`'); + $result = DB::select('SELECT * FROM `ShiftTypes` ORDER BY `name`'); + + if (DB::getStm()->errorCode() != '00000') { + return false; + } + + return $result; } diff --git a/includes/model/Shifts_model.php b/includes/model/Shifts_model.php index 462b2f65..3f199803 100644 --- a/includes/model/Shifts_model.php +++ b/includes/model/Shifts_model.php @@ -1,4 +1,6 @@ <?php + +use Engelsystem\Database\DB; use Engelsystem\ShiftsFilter; use Engelsystem\ShiftSignupState; @@ -8,8 +10,8 @@ use Engelsystem\ShiftSignupState; */ function Shifts_by_room($room) { - $result = sql_select('SELECT * FROM `Shifts` WHERE `RID`=' . sql_escape($room['RID']) . ' ORDER BY `start`'); - if ($result === false) { + $result = DB::select('SELECT * FROM `Shifts` WHERE `RID`=? ORDER BY `start`', [$room['RID']]); + if (empty($result)) { engelsystem_error('Unable to load shifts.'); } return $result; @@ -21,7 +23,8 @@ function Shifts_by_room($room) */ function Shifts_by_ShiftsFilter(ShiftsFilter $shiftsFilter) { - $SQL = "SELECT * FROM ( + //@TODO + $sql = "SELECT * FROM ( SELECT DISTINCT `Shifts`.*, `ShiftTypes`.`name`, `Room`.`Name` AS `room_name` FROM `Shifts` JOIN `Room` USING (`RID`) @@ -47,8 +50,8 @@ function Shifts_by_ShiftsFilter(ShiftsFilter $shiftsFilter) AND NOT `Shifts`.`PSID` IS NULL) AS tmp_shifts ORDER BY `start`"; - $result = sql_select($SQL); - if ($result === false) { + $result = DB::select($sql); + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load shifts by filter.'); } return $result; @@ -56,11 +59,12 @@ function Shifts_by_ShiftsFilter(ShiftsFilter $shiftsFilter) /** * @param ShiftsFilter $shiftsFilter - * @return array + * @return array[] */ function NeededAngeltypes_by_ShiftsFilter(ShiftsFilter $shiftsFilter) { - $SQL = " + //@TODO + $sql = " SELECT `NeededAngelTypes`.*, `Shifts`.`SID`, @@ -90,8 +94,8 @@ function NeededAngeltypes_by_ShiftsFilter(ShiftsFilter $shiftsFilter) WHERE `Shifts`.`RID` IN (" . implode(',', $shiftsFilter->getRooms()) . ") AND `start` BETWEEN " . $shiftsFilter->getStartTime() . " AND " . $shiftsFilter->getEndTime() . " AND NOT `Shifts`.`PSID` IS NULL"; - $result = sql_select($SQL); - if ($result === false) { + $result = DB::select($sql); + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load needed angeltypes by filter.'); } return $result; @@ -104,40 +108,48 @@ function NeededAngeltypes_by_ShiftsFilter(ShiftsFilter $shiftsFilter) */ function NeededAngeltype_by_Shift_and_Angeltype($shift, $angeltype) { - $result = sql_select(" - SELECT - `NeededAngelTypes`.*, - `Shifts`.`SID`, - `AngelTypes`.`id`, - `AngelTypes`.`name`, - `AngelTypes`.`restricted`, - `AngelTypes`.`no_self_signup` - FROM `Shifts` - JOIN `NeededAngelTypes` ON `NeededAngelTypes`.`shift_id`=`Shifts`.`SID` - JOIN `AngelTypes` ON `AngelTypes`.`id`= `NeededAngelTypes`.`angel_type_id` - WHERE `Shifts`.`SID`=" . sql_escape($shift['SID']) . " - AND `AngelTypes`.`id`=" . sql_escape($angeltype['id']) . " - AND `Shifts`.`PSID` IS NULL - - UNION - - SELECT - `NeededAngelTypes`.*, - `Shifts`.`SID`, - `AngelTypes`.`id`, - `AngelTypes`.`name`, - `AngelTypes`.`restricted`, - `AngelTypes`.`no_self_signup` - FROM `Shifts` - JOIN `NeededAngelTypes` ON `NeededAngelTypes`.`room_id`=`Shifts`.`RID` - JOIN `AngelTypes` ON `AngelTypes`.`id`= `NeededAngelTypes`.`angel_type_id` - WHERE `Shifts`.`SID`=" . sql_escape($shift['SID']) . " - AND `AngelTypes`.`id`=" . sql_escape($angeltype['id']) . " - AND NOT `Shifts`.`PSID` IS NULL"); - if ($result === false) { + $result = DB::select(' + SELECT + `NeededAngelTypes`.*, + `Shifts`.`SID`, + `AngelTypes`.`id`, + `AngelTypes`.`name`, + `AngelTypes`.`restricted`, + `AngelTypes`.`no_self_signup` + FROM `Shifts` + JOIN `NeededAngelTypes` ON `NeededAngelTypes`.`shift_id`=`Shifts`.`SID` + JOIN `AngelTypes` ON `AngelTypes`.`id`= `NeededAngelTypes`.`angel_type_id` + WHERE `Shifts`.`SID`=? + AND `AngelTypes`.`id`=? + AND `Shifts`.`PSID` IS NULL + + UNION + + SELECT + `NeededAngelTypes`.*, + `Shifts`.`SID`, + `AngelTypes`.`id`, + `AngelTypes`.`name`, + `AngelTypes`.`restricted`, + `AngelTypes`.`no_self_signup` + FROM `Shifts` + JOIN `NeededAngelTypes` ON `NeededAngelTypes`.`room_id`=`Shifts`.`RID` + JOIN `AngelTypes` ON `AngelTypes`.`id`= `NeededAngelTypes`.`angel_type_id` + WHERE `Shifts`.`SID`=? + AND `AngelTypes`.`id`=? + AND NOT `Shifts`.`PSID` IS NULL + ', + [ + $shift['SID'], + $angeltype['id'], + $shift['SID'], + $angeltype['id'] + ] + ); + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load needed angeltypes by filter.'); } - if (count($result) == 0) { + if (empty($result)) { return null; } return $result[0]; @@ -149,7 +161,8 @@ function NeededAngeltype_by_Shift_and_Angeltype($shift, $angeltype) */ function ShiftEntries_by_ShiftsFilter(ShiftsFilter $shiftsFilter) { - $SQL = " + // @TODO + $sql = " SELECT `User`.`Nick`, `User`.`email`, @@ -167,8 +180,8 @@ function ShiftEntries_by_ShiftsFilter(ShiftsFilter $shiftsFilter) WHERE `Shifts`.`RID` IN (" . implode(',', $shiftsFilter->getRooms()) . ") AND `start` BETWEEN " . $shiftsFilter->getStartTime() . " AND " . $shiftsFilter->getEndTime() . " ORDER BY `Shifts`.`start`"; - $result = sql_select($SQL); - if ($result === false) { + $result = DB::select($sql); + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load shift entries by filter.'); } return $result; @@ -374,25 +387,31 @@ function Shift_signup_allowed( * Delete a shift by its external id. * * @param int $shift_psid - * @return mysqli_result|false + * @return bool */ function Shift_delete_by_psid($shift_psid) { - return sql_query("DELETE FROM `Shifts` WHERE `PSID`='" . sql_escape($shift_psid) . "'"); + DB::delete('DELETE FROM `Shifts` WHERE `PSID`=?', [$shift_psid]); + + if (DB::getStm()->errorCode() != '00000') { + return false; + } + + return true; } /** * Delete a shift. * * @param int $shift_id - * @return mysqli_result + * @return bool */ function Shift_delete($shift_id) { mail_shift_delete(Shift($shift_id)); - $result = sql_query("DELETE FROM `Shifts` WHERE `SID`='" . sql_escape($shift_id) . "'"); - if ($result === false) { + $result = DB::delete('DELETE FROM `Shifts` WHERE `SID`=?', [$shift_id]); + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to delete shift.'); } return $result; @@ -402,7 +421,7 @@ function Shift_delete($shift_id) * Update a shift. * * @param array $shift - * @return mysqli_result|false + * @return bool */ function Shift_update($shift) { @@ -410,36 +429,51 @@ function Shift_update($shift) $shift['name'] = ShiftType($shift['shifttype_id'])['name']; mail_shift_change(Shift($shift['SID']), $shift); - return sql_query(" + return (bool)DB::update(' UPDATE `Shifts` SET - `shifttype_id`='" . sql_escape($shift['shifttype_id']) . "', - `start`='" . sql_escape($shift['start']) . "', - `end`='" . sql_escape($shift['end']) . "', - `RID`='" . sql_escape($shift['RID']) . "', - `title`=" . sql_null($shift['title']) . ", - `URL`=" . sql_null($shift['URL']) . ", - `PSID`=" . sql_null($shift['PSID']) . ", - `edited_by_user_id`='" . sql_escape($user['UID']) . "', - `edited_at_timestamp`=" . time() . " - WHERE `SID`='" . sql_escape($shift['SID']) . "' - "); + `shifttype_id` = ?, + `start` = ?, + `end` = ?, + `RID` = ?, + `title` = ?, + `URL` = ?, + `PSID` = ?, + `edited_by_user_id` = ?, + `edited_at_timestamp` = ? + WHERE `SID` = ? + ', + [ + $shift['shifttype_id'], + $shift['start'], + $shift['end'], + $shift['RID'], + $shift['title'], + $shift['URL'], + $shift['PSID'], + $user['UID'], + time(), + $shift['SID'] + ] + ); } /** * Update a shift by its external id. * * @param array $shift - * @return mysqli_result|false|null + * @return bool|null */ function Shift_update_by_psid($shift) { - $shift_source = sql_select("SELECT `SID` FROM `Shifts` WHERE `PSID`=" . $shift['PSID']); - if ($shift_source === false) { + $shift_source = DB::select('SELECT `SID` FROM `Shifts` WHERE `PSID`=?', [$shift['PSID']]); + if (DB::getStm()->errorCode() != '00000') { return false; } - if (count($shift_source) == 0) { + + if (empty($shift_source)) { return null; } + $shift['SID'] = $shift_source[0]['SID']; return Shift_update($shift); } @@ -453,22 +487,36 @@ function Shift_update_by_psid($shift) function Shift_create($shift) { global $user; - $result = sql_query(" - INSERT INTO `Shifts` SET - `shifttype_id`='" . sql_escape($shift['shifttype_id']) . "', - `start`='" . sql_escape($shift['start']) . "', - `end`='" . sql_escape($shift['end']) . "', - `RID`='" . sql_escape($shift['RID']) . "', - `title`=" . sql_null($shift['title']) . ", - `URL`=" . sql_null($shift['URL']) . ", - `PSID`=" . sql_null($shift['PSID']) . ", - `created_by_user_id`='" . sql_escape($user['UID']) . "', - `created_at_timestamp`=" . time() + DB::insert(' + INSERT INTO `Shifts` ( + `shifttype_id`, + `start`, + `end`, + `RID`, + `title`, + `URL`, + `PSID`, + `created_by_user_id`, + `created_at_timestamp` + ) + VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) + ', + [ + $shift['shifttype_id'], + $shift['start'], + $shift['end'], + $shift['RID'], + $shift['title'], + $shift['URL'], + $shift['PSID'], + $user['UID'], + time(), + ] ); - if ($result === false) { + if (DB::getStm()->errorCode() != '00000') { return false; } - return sql_id(); + return DB::getPdo()->lastInsertId(); } /** @@ -480,7 +528,7 @@ function Shift_create($shift) */ function Shifts_by_user($user, $include_freeload_comments = false) { - $result = sql_select(' + $result = DB::select(' SELECT `ShiftTypes`.`id` AS `shifttype_id`, `ShiftTypes`.`name`, `ShiftEntry`.`id`, `ShiftEntry`.`SID`, `ShiftEntry`.`TID`, `ShiftEntry`.`UID`, `ShiftEntry`.`freeloaded`, `ShiftEntry`.`Comment`, ' . ($include_freeload_comments ? '`ShiftEntry`.`freeload_comment`, ' : '') . ' @@ -489,10 +537,14 @@ function Shifts_by_user($user, $include_freeload_comments = false) JOIN `Shifts` ON (`ShiftEntry`.`SID` = `Shifts`.`SID`) JOIN `ShiftTypes` ON (`ShiftTypes`.`id` = `Shifts`.`shifttype_id`) JOIN `Room` ON (`Shifts`.`RID` = `Room`.`RID`) - WHERE `UID`=\'' . sql_escape($user['UID']) . '\' + WHERE `UID` = ? ORDER BY `start` - '); - if ($result === false) { + ', + [ + $user['UID'] + ] + ); + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load users shifts.'); } return $result; @@ -506,14 +558,13 @@ function Shifts_by_user($user, $include_freeload_comments = false) */ function Shift($shift_id) { - $shifts_source = sql_select(" + $shifts_source = DB::select(' SELECT `Shifts`.*, `ShiftTypes`.`name` FROM `Shifts` JOIN `ShiftTypes` ON (`ShiftTypes`.`id` = `Shifts`.`shifttype_id`) - WHERE `SID`='" . sql_escape($shift_id) . "'"); - $shiftsEntry_source = sql_select("SELECT `id`, `TID` , `UID` , `freeloaded` FROM `ShiftEntry` WHERE `SID`='" . sql_escape($shift_id) . "'"); + WHERE `SID`=?', [$shift_id]); - if ($shifts_source === false) { + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load shift.'); } @@ -523,16 +574,21 @@ function Shift($shift_id) $result = $shifts_source[0]; + $shiftsEntry_source = DB::select(' + SELECT `id`, `TID` , `UID` , `freeloaded` + FROM `ShiftEntry` + WHERE `SID`=?', [$shift_id]); + $result['ShiftEntry'] = $shiftsEntry_source; $result['NeedAngels'] = []; - $temp = NeededAngelTypes_by_shift($shift_id); - foreach ($temp as $e) { + $angelTypes = NeededAngelTypes_by_shift($shift_id); + foreach ($angelTypes as $type) { $result['NeedAngels'][] = [ - 'TID' => $e['angel_type_id'], - 'count' => $e['count'], - 'restricted' => $e['restricted'], - 'taken' => $e['taken'] + 'TID' => $type['angel_type_id'], + 'count' => $type['count'], + 'restricted' => $type['restricted'], + 'taken' => $type['taken'] ]; } @@ -546,22 +602,19 @@ function Shift($shift_id) */ function Shifts() { - $shifts_source = sql_select(' + $shifts_source = DB::select(' SELECT `ShiftTypes`.`name`, `Shifts`.*, `Room`.`RID`, `Room`.`Name` AS `room_name` FROM `Shifts` JOIN `ShiftTypes` ON (`ShiftTypes`.`id` = `Shifts`.`shifttype_id`) JOIN `Room` ON `Room`.`RID` = `Shifts`.`RID` '); - if ($shifts_source === false) { + + if (DB::getStm()->errorCode() != '00000') { return false; } foreach ($shifts_source as &$shift) { $needed_angeltypes = NeededAngelTypes_by_shift($shift['SID']); - if ($needed_angeltypes === false) { - return false; - } - $shift['angeltypes'] = $needed_angeltypes; } diff --git a/includes/model/UserAngelTypes_model.php b/includes/model/UserAngelTypes_model.php index 9ae21772..b27724c3 100644 --- a/includes/model/UserAngelTypes_model.php +++ b/includes/model/UserAngelTypes_model.php @@ -1,5 +1,7 @@ <?php +use Engelsystem\Database\DB; + /** * User angeltypes model */ @@ -13,12 +15,12 @@ */ function UserAngelType_exists($user, $angeltype) { - return sql_num_query(" + return count(DB::select(' SELECT `id` FROM `UserAngelTypes` - WHERE `UserAngelTypes`.`user_id`='" . sql_escape($user['UID']) . "' - AND `angeltype_id`='" . sql_escape($angeltype['id']) . "' - ") > 0; + WHERE `UserAngelTypes`.`user_id`=? + AND `angeltype_id`=? + ', [$user['UID'], $angeltype['id']])) > 0; } /** @@ -29,16 +31,18 @@ function UserAngelType_exists($user, $angeltype) */ function User_angeltypes($user) { - $result = sql_select(" + $result = DB::select(' SELECT `AngelTypes`.*, `UserAngelTypes`.`confirm_user_id`, `UserAngelTypes`.`supporter` FROM `UserAngelTypes` JOIN `AngelTypes` ON `UserAngelTypes`.`angeltype_id` = `AngelTypes`.`id` - WHERE `UserAngelTypes`.`user_id`='" . sql_escape($user['UID']) . "' - "); - if ($result === false) { + WHERE `UserAngelTypes`.`user_id`=? + ', [$user['UID']]); + + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load user angeltypes.'); return false; } + return $result; } @@ -50,7 +54,7 @@ function User_angeltypes($user) */ function User_unconfirmed_AngelTypes($user) { - $result = sql_select(" + $result = DB::select(' SELECT `UserAngelTypes`.*, `AngelTypes`.`name`, @@ -58,16 +62,18 @@ function User_unconfirmed_AngelTypes($user) FROM `UserAngelTypes` JOIN `AngelTypes` ON `UserAngelTypes`.`angeltype_id`=`AngelTypes`.`id` JOIN `UserAngelTypes` AS `UnconfirmedMembers` ON `UserAngelTypes`.`angeltype_id`=`UnconfirmedMembers`.`angeltype_id` - WHERE `UserAngelTypes`.`user_id`='" . sql_escape($user['UID']) . "' + WHERE `UserAngelTypes`.`user_id`=? AND `UserAngelTypes`.`supporter`=TRUE AND `AngelTypes`.`restricted`=TRUE AND `UnconfirmedMembers`.`confirm_user_id` IS NULL GROUP BY `UserAngelTypes`.`angeltype_id` ORDER BY `AngelTypes`.`name` - "); - if ($result === false) { + ', [$user['UID']]); + + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load user angeltypes.'); } + return $result; } @@ -83,14 +89,20 @@ function User_is_AngelType_supporter(&$user, $angeltype) if (!isset($user['privileges'])) { $user['privileges'] = privileges_for_user($user['UID']); } - return (sql_num_query(" - SELECT `id` - FROM `UserAngelTypes` - WHERE `user_id`='" . sql_escape($user['UID']) . "' - AND `angeltype_id`='" . sql_escape($angeltype['id']) . "' - AND `supporter`=TRUE - LIMIT 1 - ") > 0) || in_array('admin_user_angeltypes', $user['privileges']); + return (count(DB::select(' + SELECT `id` + FROM `UserAngelTypes` + WHERE `user_id`=? + AND `angeltype_id`=? + AND `supporter`=TRUE + LIMIT 1 + ', + [ + $user['UID'], + $angeltype['id'] + ] + )) > 0) + || in_array('admin_user_angeltypes', $user['privileges']); } /** @@ -98,19 +110,21 @@ function User_is_AngelType_supporter(&$user, $angeltype) * * @param int $user_angeltype_id * @param bool $supporter - * @return mysqli_result + * @return int */ function UserAngelType_update($user_angeltype_id, $supporter) { - $result = sql_query(" + $result = DB::update(' UPDATE `UserAngelTypes` - SET `supporter`=" . sql_bool($supporter) . " - WHERE `id`='" . sql_escape($user_angeltype_id) . "' + SET `supporter`=? + WHERE `id`=? LIMIT 1 - "); - if ($result === false) { + ', [$supporter, $user_angeltype_id]); + + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to update supporter rights.'); } + return $result; } @@ -118,19 +132,21 @@ function UserAngelType_update($user_angeltype_id, $supporter) * Delete all unconfirmed UserAngelTypes for given Angeltype. * * @param int $angeltype_id - * @return mysqli_result + * @return bool */ function UserAngelTypes_delete_all($angeltype_id) { - $result = sql_query(" + DB::delete(' DELETE FROM `UserAngelTypes` - WHERE `angeltype_id`='" . sql_escape($angeltype_id) . "' + WHERE `angeltype_id`=? AND `confirm_user_id` IS NULL - "); - if ($result === false) { + ', [$angeltype_id]); + + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to delete all unconfirmed users.'); } - return $result; + + return true; } /** @@ -138,20 +154,22 @@ function UserAngelTypes_delete_all($angeltype_id) * * @param int $angeltype_id * @param array $confirm_user - * @return mysqli_result + * @return bool */ function UserAngelTypes_confirm_all($angeltype_id, $confirm_user) { - $result = sql_query(" + $result = DB::update(' UPDATE `UserAngelTypes` - SET `confirm_user_id`='" . sql_escape($confirm_user['UID']) . "' - WHERE `angeltype_id`='" . sql_escape($angeltype_id) . "' + SET `confirm_user_id`=? + WHERE `angeltype_id`=? AND `confirm_user_id` IS NULL - "); - if ($result === false) { + ', [$confirm_user['UID'], $angeltype_id]); + + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to confirm all users.'); } - return $result; + + return (bool)$result; } /** @@ -159,33 +177,33 @@ function UserAngelTypes_confirm_all($angeltype_id, $confirm_user) * * @param int $user_angeltype_id * @param array $confirm_user - * @return mysqli_result + * @return bool */ function UserAngelType_confirm($user_angeltype_id, $confirm_user) { - $result = sql_query(" + $result = DB::update(' UPDATE `UserAngelTypes` - SET `confirm_user_id`='" . sql_escape($confirm_user['UID']) . "' - WHERE `id`='" . sql_escape($user_angeltype_id) . "' - LIMIT 1"); - if ($result === false) { + SET `confirm_user_id`=? + WHERE `id`=? + LIMIT 1', [$confirm_user['UID'], $user_angeltype_id]); + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to confirm user angeltype.'); } - return $result; + return (bool)$result; } /** * Delete an UserAngelType. * * @param array $user_angeltype - * @return mysqli_result|false + * @return bool */ function UserAngelType_delete($user_angeltype) { - return sql_query(" + return (bool)DB::delete(' DELETE FROM `UserAngelTypes` - WHERE `id`='" . sql_escape($user_angeltype['id']) . "' - LIMIT 1"); + WHERE `id`=? + LIMIT 1', [$user_angeltype['id']]); } /** @@ -197,14 +215,21 @@ function UserAngelType_delete($user_angeltype) */ function UserAngelType_create($user, $angeltype) { - $result = sql_query(" - INSERT INTO `UserAngelTypes` SET - `user_id`='" . sql_escape($user['UID']) . "', - `angeltype_id`='" . sql_escape($angeltype['id']) . "'"); - if ($result === false) { + DB::insert(' + INSERT INTO `UserAngelTypes` (`user_id`, `angeltype_id`) + VALUES (?, ?) + ', + [ + $user['UID'], + $angeltype['id'] + ] + ); + + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to create user angeltype.'); } - return sql_id(); + + return DB::getPdo()->lastInsertId(); } /** @@ -215,17 +240,20 @@ function UserAngelType_create($user, $angeltype) */ function UserAngelType($user_angeltype_id) { - $angeltype = sql_select(" + $angeltype = DB::select(' SELECT * FROM `UserAngelTypes` - WHERE `id`='" . sql_escape($user_angeltype_id) . "' - LIMIT 1"); - if ($angeltype === false) { + WHERE `id`=? + LIMIT 1', [$user_angeltype_id]); + + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load user angeltype.'); } - if (count($angeltype) == 0) { + + if (empty($angeltype)) { return null; } + return $angeltype[0]; } @@ -238,17 +266,26 @@ function UserAngelType($user_angeltype_id) */ function UserAngelType_by_User_and_AngelType($user, $angeltype) { - $angeltype = sql_select(" - SELECT * - FROM `UserAngelTypes` - WHERE `user_id`='" . sql_escape($user['UID']) . "' - AND `angeltype_id`='" . sql_escape($angeltype['id']) . "' - LIMIT 1"); - if ($angeltype === false) { + $angeltype = DB::select(' + SELECT * + FROM `UserAngelTypes` + WHERE `user_id`=? + AND `angeltype_id`=? + LIMIT 1 + ', + [ + $user['UID'], + $angeltype['id'] + ] + ); + + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load user angeltype.'); } - if (count($angeltype) == 0) { + + if (empty($angeltype)) { return null; } - return $angeltype[0]; + + return array_shift($angeltype); } diff --git a/includes/model/UserDriverLicenses_model.php b/includes/model/UserDriverLicenses_model.php index 8091736c..5ff4df35 100644 --- a/includes/model/UserDriverLicenses_model.php +++ b/includes/model/UserDriverLicenses_model.php @@ -1,5 +1,7 @@ <?php +use Engelsystem\Database\DB; + /** * Returns a new empty UserDriverLicense * FIXME entity object needed @@ -43,17 +45,21 @@ function UserDriverLicense_valid($user_driver_license) */ function UserDriverLicense($user_id) { - $user_driver_license = sql_select("SELECT * FROM `UserDriverLicenses` WHERE `user_id`='" . sql_escape($user_id) . "'"); - if ($user_driver_license === false) { + $user_driver_license = DB::select(' + SELECT * + FROM `UserDriverLicenses` + WHERE `user_id`=?', [$user_id]); + + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load user driver license.'); return false; } - if (count($user_driver_license) == 0) { + if (empty($user_driver_license)) { return null; } - return $user_driver_license[0]; + return array_shift($user_driver_license); } /** @@ -66,18 +72,32 @@ function UserDriverLicense($user_id) function UserDriverLicenses_create($user_driver_license, $user) { $user_driver_license['user_id'] = $user['UID']; - $result = sql_query(" - INSERT INTO `UserDriverLicenses` SET - `user_id`=" . sql_escape($user_driver_license['user_id']) . ", - `has_car`=" . sql_bool($user_driver_license['has_car']) . ", - `has_license_car`=" . sql_bool($user_driver_license['has_license_car']) . ", - `has_license_3_5t_transporter`=" . sql_bool($user_driver_license['has_license_3_5t_transporter']) . ", - `has_license_7_5t_truck`=" . sql_bool($user_driver_license['has_license_7_5t_truck']) . ", - `has_license_12_5t_truck`=" . sql_bool($user_driver_license['has_license_12_5t_truck']) . ", - `has_license_forklift`=" . sql_bool($user_driver_license['has_license_forklift'])); - if ($result === false) { + DB::insert(' + INSERT INTO `UserDriverLicenses` ( + `user_id`, + `has_car`, + `has_license_car`, + `has_license_3_5t_transporter`, + `has_license_7_5t_truck`, + `has_license_12_5t_truck`, + `has_license_forklift` + ) + VALUES (?, ?, ?, ?, ?, ?, ?) + ', + [ + $user_driver_license['user_id'], + (bool)$user_driver_license['has_car'], + (bool)$user_driver_license['has_license_car'], + (bool)$user_driver_license['has_license_3_5t_transporter'], + (bool)$user_driver_license['has_license_7_5t_truck'], + (bool)$user_driver_license['has_license_12_5t_truck'], + (bool)$user_driver_license['has_license_forklift'], + ] + ); + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to create user driver license'); } + return $user_driver_license; } @@ -85,19 +105,32 @@ function UserDriverLicenses_create($user_driver_license, $user) * Update a user's driver license entry * * @param array $user_driver_license The UserDriverLicense to update - * @return mysqli_result + * @return bool */ function UserDriverLicenses_update($user_driver_license) { - $result = sql_query("UPDATE `UserDriverLicenses` SET - `has_car`=" . sql_bool($user_driver_license['has_car']) . ", - `has_license_car`=" . sql_bool($user_driver_license['has_license_car']) . ", - `has_license_3_5t_transporter`=" . sql_bool($user_driver_license['has_license_3_5t_transporter']) . ", - `has_license_7_5t_truck`=" . sql_bool($user_driver_license['has_license_7_5t_truck']) . ", - `has_license_12_5t_truck`=" . sql_bool($user_driver_license['has_license_12_5t_truck']) . ", - `has_license_forklift`=" . sql_bool($user_driver_license['has_license_forklift']) . " - WHERE `user_id`='" . sql_escape($user_driver_license['user_id']) . "'"); - if ($result === false) { + $result = DB::update(' + UPDATE `UserDriverLicenses` + SET + `has_car`=?, + `has_license_car`=?, + `has_license_3_5t_transporter`=?, + `has_license_7_5t_truck`=?, + `has_license_12_5t_truck`=?, + `has_license_forklift`=? + WHERE `user_id`=? + ', + [ + (bool)$user_driver_license['has_car'], + (bool)$user_driver_license['has_license_car'], + (bool)$user_driver_license['has_license_3_5t_transporter'], + (bool)$user_driver_license['has_license_7_5t_truck'], + (bool)$user_driver_license['has_license_12_5t_truck'], + (bool)$user_driver_license['has_license_forklift'], + $user_driver_license['user_id'], + ] + ); + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to update user driver license information'); } return $result; @@ -107,12 +140,12 @@ function UserDriverLicenses_update($user_driver_license) * Delete a user's driver license entry * * @param int $user_id - * @return mysqli_result + * @return bool */ function UserDriverLicenses_delete($user_id) { - $result = sql_query("DELETE FROM `UserDriverLicenses` WHERE `user_id`=" . sql_escape($user_id)); - if ($result === false) { + $result = DB::delete('DELETE FROM `UserDriverLicenses` WHERE `user_id`=?', [$user_id]); + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to remove user driver license information'); } return $result; diff --git a/includes/model/UserGroups_model.php b/includes/model/UserGroups_model.php index c390cd20..d4baf638 100644 --- a/includes/model/UserGroups_model.php +++ b/includes/model/UserGroups_model.php @@ -1,18 +1,22 @@ <?php +use Engelsystem\Database\DB; + /** * Returns users groups * * @param array $user - * @return array|false + * @return array */ function User_groups($user) { - return sql_select(' - SELECT `Groups`.* - FROM `UserGroups` - JOIN `Groups` ON `Groups`.`UID`=`UserGroups`.`group_id` - WHERE `UserGroups`.`uid`=\'' . sql_escape($user['UID']) . '\' - ORDER BY `UserGroups`.`group_id` - '); + return DB::select(' + SELECT `Groups`.* + FROM `UserGroups` + JOIN `Groups` ON `Groups`.`UID`=`UserGroups`.`group_id` + WHERE `UserGroups`.`uid`=? + ORDER BY `UserGroups`.`group_id` + ', + [$user['UID']] + ); } diff --git a/includes/model/User_model.php b/includes/model/User_model.php index 22299664..836ca914 100644 --- a/includes/model/User_model.php +++ b/includes/model/User_model.php @@ -1,4 +1,6 @@ <?php + +use Engelsystem\Database\DB; use Engelsystem\ValidationResult; /** @@ -9,90 +11,154 @@ use Engelsystem\ValidationResult; * Delete a user * * @param int $user_id - * @return mysqli_result|false + * @return bool */ function User_delete($user_id) { - return sql_query("DELETE FROM `User` WHERE `UID`='" . sql_escape($user_id) . "'"); + DB::delete('DELETE FROM `User` WHERE `UID`=?', [$user_id]); + + return DB::getStm()->errorCode() == '00000'; } /** * Update user. * * @param array $user - * @return mysqli_result|false + * @return bool */ function User_update($user) { - return sql_query(" - UPDATE `User` SET - `Nick`='" . sql_escape($user['Nick']) . "', - `Name`='" . sql_escape($user['Name']) . "', - `Vorname`='" . sql_escape($user['Vorname']) . "', - `Alter`='" . sql_escape($user['Alter']) . "', - `Telefon`='" . sql_escape($user['Telefon']) . "', - `DECT`='" . sql_escape($user['DECT']) . "', - `Handy`='" . sql_escape($user['Handy']) . "', - `email`='" . sql_escape($user['email']) . "', - `email_shiftinfo`=" . sql_bool($user['email_shiftinfo']) . ", - `email_by_human_allowed`=" . sql_bool($user['email_by_human_allowed']) . ", - `jabber`='" . sql_escape($user['jabber']) . "', - `Size`='" . sql_escape($user['Size']) . "', - `Gekommen`='" . sql_escape($user['Gekommen']) . "', - `Aktiv`='" . sql_escape($user['Aktiv']) . "', - `force_active`=" . sql_bool($user['force_active']) . ", - `Tshirt`='" . sql_escape($user['Tshirt']) . "', - `color`='" . sql_escape($user['color']) . "', - `Sprache`='" . sql_escape($user['Sprache']) . "', - `Hometown`='" . sql_escape($user['Hometown']) . "', - `got_voucher`='" . sql_escape($user['got_voucher']) . "', - `arrival_date`='" . sql_escape($user['arrival_date']) . "', - `planned_arrival_date`='" . sql_escape($user['planned_arrival_date']) . "', - `planned_departure_date`=" . sql_null($user['planned_departure_date']) . " - WHERE `UID`='" . sql_escape($user['UID']) . "' - "); + return (bool)DB::update(" + UPDATE `User` SET + `Nick`=?, + `Name`=?, + `Vorname`=?, + `Alter`=?, + `Telefon`=?, + `DECT`=?, + `Handy`=?, + `email`=?, + `email_shiftinfo`=?, + `email_by_human_allowed`=?, + `jabber`=?, + `Size`=?, + `Gekommen`=?, + `Aktiv`=?, + `force_active`=?, + `Tshirt`=?, + `color`=?, + `Sprache`=?, + `Hometown`=?, + `got_voucher`=?, + `arrival_date`=?, + `planned_arrival_date`=?, + `planned_departure_date`=? + WHERE `UID`=? + ", + [ + $user['Nick'], + $user['Name'], + $user['Vorname'], + $user['Alter'], + $user['Telefon'], + $user['DECT'], + $user['Handy'], + $user['email'], + (bool)$user['email_shiftinfo'], + (bool)$user['email_by_human_allowed'], + $user['jabber'], + $user['Size'], + $user['Gekommen'], + $user['Aktiv'], + (bool)$user['force_active'], + $user['Tshirt'], + $user['color'], + $user['Sprache'], + $user['Hometown'], + $user['got_voucher'], + $user['arrival_date'], + $user['planned_arrival_date'], + $user['planned_departure_date'], + $user['UID'], + ] + ); } /** * Counts all forced active users. * - * @return string|null + * @return int */ function User_force_active_count() { - return sql_select_single_cell('SELECT COUNT(*) FROM `User` WHERE `force_active` = 1'); + $result = DB::select('SELECT COUNT(*) FROM `User` WHERE `force_active` = 1'); + $result = array_shift($result); + + if (empty($result)) { + return 0; + } + + return (int)array_shift($result); } /** - * @return string|null + * @return int */ function User_active_count() { - return sql_select_single_cell('SELECT COUNT(*) FROM `User` WHERE `Aktiv` = 1'); + $result = DB::select('SELECT COUNT(*) FROM `User` WHERE `Aktiv` = 1'); + $result = array_shift($result); + + if (empty($result)) { + return 0; + } + + return (int)array_shift($result); } /** - * @return string|null + * @return int */ function User_got_voucher_count() { - return sql_select_single_cell('SELECT SUM(`got_voucher`) FROM `User`'); + $result = DB::select('SELECT SUM(`got_voucher`) FROM `User`'); + $result = array_shift($result); + + if (empty($result)) { + return 0; + } + + return (int)array_shift($result); } /** - * @return string|null + * @return int */ function User_arrived_count() { - return sql_select_single_cell('SELECT COUNT(*) FROM `User` WHERE `Gekommen` = 1'); + $result = DB::select('SELECT COUNT(*) FROM `User` WHERE `Gekommen` = 1'); + $result = array_shift($result); + + if (empty($result)) { + return 0; + } + + return (int)array_shift($result); } /** - * @return string|null + * @return int */ function User_tshirts_count() { - return sql_select_single_cell('SELECT COUNT(*) FROM `User` WHERE `Tshirt` = 1'); + $result = DB::select('SELECT COUNT(*) FROM `User` WHERE `Tshirt` = 1'); + $result = array_shift($result); + + if (empty($result)) { + return 0; + } + + return (int)array_shift($result); } /** @@ -126,7 +192,19 @@ function User_sortable_columns() */ function Users($order_by = 'Nick') { - return sql_select("SELECT * FROM `User` ORDER BY `" . sql_escape($order_by) . "` ASC"); + $result = DB::select(sprintf(' + SELECT * + FROM `User` + ORDER BY `%s` ASC + ', + trim(DB::getPdo()->quote($order_by), '\'') + )); + + if (DB::getStm()->errorCode() != '00000') { + return false; + } + + return $result; } /** @@ -150,14 +228,19 @@ function User_is_freeloader($user) */ function Users_by_angeltype_inverted($angeltype) { - $result = sql_select(" - SELECT `User`.* - FROM `User` - LEFT JOIN `UserAngelTypes` - ON (`User`.`UID`=`UserAngelTypes`.`user_id` AND `angeltype_id`='" . sql_escape($angeltype['id']) . "') - WHERE `UserAngelTypes`.`id` IS NULL - ORDER BY `Nick`"); - if ($result === false) { + $result = DB::select(' + SELECT `User`.* + FROM `User` + LEFT JOIN `UserAngelTypes` + ON (`User`.`UID`=`UserAngelTypes`.`user_id` AND `angeltype_id`=?) + WHERE `UserAngelTypes`.`id` IS NULL + ORDER BY `Nick` + ', + [ + $angeltype['id'] + ] + ); + if (DB::getStm()->errorCode() != '00000') { engelsystem_error("Unable to load users."); } return $result; @@ -171,19 +254,24 @@ function Users_by_angeltype_inverted($angeltype) */ function Users_by_angeltype($angeltype) { - $result = sql_select(" - SELECT - `User`.*, - `UserAngelTypes`.`id` AS `user_angeltype_id`, - `UserAngelTypes`.`confirm_user_id`, - `UserAngelTypes`.`supporter`, - `UserDriverLicenses`.* - FROM `User` - JOIN `UserAngelTypes` ON `User`.`UID`=`UserAngelTypes`.`user_id` - LEFT JOIN `UserDriverLicenses` ON `User`.`UID`=`UserDriverLicenses`.`user_id` - WHERE `UserAngelTypes`.`angeltype_id`='" . sql_escape($angeltype['id']) . "' - ORDER BY `Nick`"); - if ($result === false) { + $result = DB::select(' + SELECT + `User`.*, + `UserAngelTypes`.`id` AS `user_angeltype_id`, + `UserAngelTypes`.`confirm_user_id`, + `UserAngelTypes`.`supporter`, + `UserDriverLicenses`.* + FROM `User` + JOIN `UserAngelTypes` ON `User`.`UID`=`UserAngelTypes`.`user_id` + LEFT JOIN `UserDriverLicenses` ON `User`.`UID`=`UserDriverLicenses`.`user_id` + WHERE `UserAngelTypes`.`angeltype_id`=? + ORDER BY `Nick` + ', + [ + $angeltype['id'] + ] + ); + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load members.'); } return $result; @@ -192,11 +280,11 @@ function Users_by_angeltype($angeltype) /** * Returns User id array * - * @return array|false + * @return array */ function User_ids() { - return sql_select('SELECT `UID` FROM `User`'); + return DB::select('SELECT `UID` FROM `User`'); } /** @@ -207,7 +295,7 @@ function User_ids() */ function User_validate_Nick($nick) { - return preg_replace('/([^a-z0-9üöäß. _+*-]{1,})/ui', '', $nick); + return preg_replace('/([^\wüöäß. +*-]{1,})/ui', '', $nick); } /** @@ -311,14 +399,17 @@ function User_validate_planned_departure_date($planned_arrival_date, $planned_de */ function User($user_id) { - $user_source = sql_select("SELECT * FROM `User` WHERE `UID`='" . sql_escape($user_id) . "' LIMIT 1"); - if ($user_source === false) { + $user_source = DB::select('SELECT * FROM `User` WHERE `UID`=? LIMIT 1', [$user_id]); + + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load user.'); } - if (count($user_source) > 0) { - return $user_source[0]; + + if (empty($user_source)) { + return null; } - return null; + + return array_shift($user_source); } /** @@ -330,13 +421,16 @@ function User($user_id) */ function User_by_api_key($api_key) { - $user = sql_select("SELECT * FROM `User` WHERE `api_key`='" . sql_escape($api_key) . "' LIMIT 1"); - if ($user === false) { + $user = DB::select('SELECT * FROM `User` WHERE `api_key`=? LIMIT 1', [$api_key]); + + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to find user by api key.'); } - if (count($user) == 0) { + + if (empty($user)) { return null; } + return $user[0]; } @@ -348,14 +442,17 @@ function User_by_api_key($api_key) */ function User_by_email($email) { - $user = sql_select("SELECT * FROM `User` WHERE `email`='" . sql_escape($email) . "' LIMIT 1"); - if ($user === false) { + $user = DB::select('SELECT * FROM `User` WHERE `email`=? LIMIT 1', [$email]); + + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load user.'); } - if (count($user) == 0) { + + if (empty($user)) { return null; } - return $user[0]; + + return array_shift($user); } /** @@ -366,14 +463,17 @@ function User_by_email($email) */ function User_by_password_recovery_token($token) { - $user = sql_select("SELECT * FROM `User` WHERE `password_recovery_token`='" . sql_escape($token) . "' LIMIT 1"); - if ($user === false) { + $user = DB::select('SELECT * FROM `User` WHERE `password_recovery_token`=? LIMIT 1', [$token]); + + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to load user.'); } - if (count($user) == 0) { + + if (empty($user)) { return null; } - return $user[0]; + + return array_shift($user); } /** @@ -386,8 +486,19 @@ function User_by_password_recovery_token($token) function User_reset_api_key(&$user, $log = true) { $user['api_key'] = md5($user['Nick'] . time() . rand()); - $result = sql_query("UPDATE `User` SET `api_key`='" . sql_escape($user['api_key']) . "' WHERE `UID`='" . sql_escape($user['UID']) . "' LIMIT 1"); - if ($result === false) { + DB::update( + ' + UPDATE `User` + SET `api_key`=? + WHERE `UID`=? + LIMIT 1 + ', + [ + $user['api_key'], + $user['UID'] + ] + ); + if (DB::getStm()->errorCode() != '00000') { return false; } @@ -407,13 +518,18 @@ function User_reset_api_key(&$user, $log = true) function User_generate_password_recovery_token(&$user) { $user['password_recovery_token'] = md5($user['Nick'] . time() . rand()); - $result = sql_query(" - UPDATE `User` - SET `password_recovery_token`='" . sql_escape($user['password_recovery_token']) . "' - WHERE `UID`='" . sql_escape($user['UID']) . "' - LIMIT 1 - "); - if ($result === false) { + DB::update(' + UPDATE `User` + SET `password_recovery_token`=? + WHERE `UID`=? + LIMIT 1 + ', + [ + $user['password_recovery_token'], + $user['UID'], + ] + ); + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to generate password recovery token.'); } engelsystem_log('Password recovery for ' . User_Nick_render($user) . ' started.'); diff --git a/includes/mysqli_provider.php b/includes/mysqli_provider.php deleted file mode 100644 index 0efb670a..00000000 --- a/includes/mysqli_provider.php +++ /dev/null @@ -1,250 +0,0 @@ -<?php -/** @var mysqli $sql_connection */ -$sql_connection = null; - -/** - * Close connection. - * - * @return bool - */ -function sql_close() -{ - global $sql_connection; - - return $sql_connection->close(); -} - -/** - * Return NULL if given value is null. - * - * @param mixed $value - * @return bool - */ -function sql_null($value = null) -{ - return $value == null ? 'NULL' : ("'" . sql_escape($value) . "'"); -} - -/** - * Start new transaction. - * - * @return mysqli_result|bool - */ -function sql_transaction_start() -{ - global $sql_nested_transaction_level; - - if ($sql_nested_transaction_level++ == 0) { - return sql_query('BEGIN'); - } - - return true; -} - -/** - * Commit transaction. - * - * @return mysqli_result|bool - */ -function sql_transaction_commit() -{ - global $sql_nested_transaction_level; - - if (--$sql_nested_transaction_level == 0) { - return sql_query('COMMIT'); - } - - return true; -} - -/** - * Stop transaction, revert database. - * - * @return mysqli_result|bool - */ -function sql_transaction_rollback() -{ - global $sql_nested_transaction_level; - - if (--$sql_nested_transaction_level == 0) { - return sql_query('ROLLBACK'); - } - - return true; -} - -/** - * Logs an sql error. - * - * @param string $message - * @return false - */ -function sql_error($message) -{ - // @TODO: Bad idea.. - sql_close(); - - $message = trim($message) . "\n"; - $message .= debug_string_backtrace() . "\n"; - - error_log('mysql_provider error: ' . $message); - - return false; -} - -/** - * Connect to mysql server. - * - * @param string $host Host - * @param string $user Username - * @param string $pass Password - * @param string $db_name DB to select - * @return mysqli|false The connection handler - */ -function sql_connect($host, $user, $pass, $db_name) -{ - global $sql_connection; - - $sql_connection = new mysqli($host, $user, $pass, $db_name); - if ($sql_connection->connect_errno) { - error('Unable to connect to MySQL: ' . $sql_connection->connect_error); - return sql_error('Unable to connect to MySQL: ' . $sql_connection->connect_error); - } - - $result = $sql_connection->query('SET CHARACTER SET utf8;'); - if (!$result) { - return sql_error('Unable to set utf8 character set (' . $sql_connection->errno . ') ' . $sql_connection->error); - } - - $result = $sql_connection->set_charset('utf8'); - if (!$result) { - return sql_error('Unable to set utf8 names (' . $sql_connection->errno . ') ' . $sql_connection->error); - } - - return $sql_connection; -} - -/** - * Change the selected db in current mysql-connection. - * - * @param $db_name - * @return bool true on success, false on error - */ -function sql_select_db($db_name) -{ - global $sql_connection; - if (!$sql_connection->select_db($db_name)) { - return sql_error('No database selected.'); - } - return true; -} - -/** - * MySQL SELECT query - * - * @param string $query - * @return array|false Result array or false on error - */ -function sql_select($query) -{ - global $sql_connection; - - $result = $sql_connection->query($query); - if ($result) { - $data = []; - while ($line = $result->fetch_assoc()) { - array_push($data, $line); - } - return $data; - } - - return sql_error('MySQL-query error: ' . $query . ' (' . $sql_connection->errno . ') ' . $sql_connection->error); -} - -/** - * MySQL execute a query - * - * @param string $query - * @return mysqli_result|false boolean resource or false on error - */ -function sql_query($query) -{ - global $sql_connection; - - $result = $sql_connection->query($query); - if ($result) { - return $result; - } - - return sql_error('MySQL-query error: ' . $query . ' (' . $sql_connection->errno . ') ' . $sql_connection->error); -} - -/** - * Returns last inserted id. - * - * @return int - */ -function sql_id() -{ - global $sql_connection; - return $sql_connection->insert_id; -} - -/** - * Escape a string for a sql query. - * - * @param string $query - * @return string - */ -function sql_escape($query) -{ - global $sql_connection; - return $sql_connection->real_escape_string($query); -} - -/** - * Convert a boolean for mysql-queries. - * - * @param boolean $boolean - * @return string - */ -function sql_bool($boolean) -{ - return $boolean == true ? 'TRUE' : 'FALSE'; -} - -/** - * Count query result lines. - * - * @param string $query - * @return int Count of result lines - */ -function sql_num_query($query) -{ - return sql_query($query)->num_rows; -} - -function sql_select_single_col($query) -{ - $result = sql_select($query); - return array_map('array_shift', $result); -} - -/** - * @param string $query - * @return string|null - */ -function sql_select_single_cell($query) -{ - $result = sql_select($query); - if ($result == false) { - return null; - } - - $result = array_shift($result); - if (!is_array($result)) { - return null; - } - - return array_shift($result); -} diff --git a/includes/pages/admin_active.php b/includes/pages/admin_active.php index c4b6e119..d3b290f9 100644 --- a/includes/pages/admin_active.php +++ b/includes/pages/admin_active.php @@ -1,5 +1,7 @@ <?php +use Engelsystem\Database\DB; + /** * @return string */ @@ -17,7 +19,7 @@ function admin_active() $msg = ''; $search = ''; - $forced_count = sql_num_query('SELECT * FROM `User` WHERE `force_active`=1'); + $forced_count = count(DB::select('SELECT `UID` FROM `User` WHERE `force_active`=1')); $count = $forced_count; $limit = ''; $set_active = ''; @@ -49,22 +51,31 @@ function admin_active() $limit = ' LIMIT ' . $count; } if (isset($_REQUEST['ack'])) { - sql_query('UPDATE `User` SET `Aktiv` = 0 WHERE `Tshirt` = 0'); - $users = sql_select(" - SELECT `User`.*, COUNT(`ShiftEntry`.`id`) as `shift_count`, $shift_sum_formula as `shift_length` - FROM `User` - LEFT JOIN `ShiftEntry` ON `User`.`UID` = `ShiftEntry`.`UID` - LEFT JOIN `Shifts` ON `ShiftEntry`.`SID` = `Shifts`.`SID` - WHERE `User`.`Gekommen` = 1 AND `User`.`force_active`=0 - GROUP BY `User`.`UID` - ORDER BY `force_active` DESC, `shift_length` DESC" . $limit - ); + DB::update('UPDATE `User` SET `Aktiv` = 0 WHERE `Tshirt` = 0'); + $users = DB::select(sprintf( + ' + SELECT + `User`.*, + COUNT(`ShiftEntry`.`id`) AS `shift_count`, + %s AS `shift_length` + FROM `User` + LEFT JOIN `ShiftEntry` ON `User`.`UID` = `ShiftEntry`.`UID` + LEFT JOIN `Shifts` ON `ShiftEntry`.`SID` = `Shifts`.`SID` + WHERE `User`.`Gekommen` = 1 + AND `User`.`force_active`=0 + GROUP BY `User`.`UID` + ORDER BY `force_active` DESC, `shift_length` DESC + %s + ', + $shift_sum_formula, + $limit + )); $user_nicks = []; foreach ($users as $usr) { - sql_query('UPDATE `User` SET `Aktiv` = 1 WHERE `UID`=\'' . sql_escape($usr['UID']) . '\''); + DB::update('UPDATE `User` SET `Aktiv` = 1 WHERE `UID`=?', [$usr['UID']]); $user_nicks[] = User_Nick_render($usr); } - sql_query('UPDATE `User` SET `Aktiv`=1 WHERE `force_active`=TRUE'); + DB::update('UPDATE `User` SET `Aktiv`=1 WHERE `force_active`=TRUE'); engelsystem_log('These angels are active now: ' . join(', ', $user_nicks)); $limit = ''; @@ -82,7 +93,7 @@ function admin_active() $user_id = $_REQUEST['active']; $user_source = User($user_id); if ($user_source != null) { - sql_query('UPDATE `User` SET `Aktiv`=1 WHERE `UID`=\'' . sql_escape($user_id) . '\' LIMIT 1'); + DB::update('UPDATE `User` SET `Aktiv`=1 WHERE `UID`=? LIMIT 1', [$user_id]); engelsystem_log('User ' . User_Nick_render($user_source) . ' is active now.'); $msg = success(_('Angel has been marked as active.'), true); } else { @@ -92,7 +103,7 @@ function admin_active() $user_id = $_REQUEST['not_active']; $user_source = User($user_id); if ($user_source != null) { - sql_query("UPDATE `User` SET `Aktiv`=0 WHERE `UID`='" . sql_escape($user_id) . "' LIMIT 1"); + DB::update('UPDATE `User` SET `Aktiv`=0 WHERE `UID`=? LIMIT 1', [$user_id]); engelsystem_log('User ' . User_Nick_render($user_source) . ' is NOT active now.'); $msg = success(_('Angel has been marked as not active.'), true); } else { @@ -102,7 +113,7 @@ function admin_active() $user_id = $_REQUEST['tshirt']; $user_source = User($user_id); if ($user_source != null) { - sql_query("UPDATE `User` SET `Tshirt`=1 WHERE `UID`='" . sql_escape($user_id) . "' LIMIT 1"); + DB::update('UPDATE `User` SET `Tshirt`=1 WHERE `UID`=? LIMIT 1', [$user_id]); engelsystem_log('User ' . User_Nick_render($user_source) . ' has tshirt now.'); $msg = success(_('Angel has got a t-shirt.'), true); } else { @@ -112,7 +123,7 @@ function admin_active() $user_id = $_REQUEST['not_tshirt']; $user_source = User($user_id); if ($user_source != null) { - sql_query("UPDATE `User` SET `Tshirt`=0 WHERE `UID`='" . sql_escape($user_id) . "' LIMIT 1"); + DB::update('UPDATE `User` SET `Tshirt`=0 WHERE `UID`=? LIMIT 1', [$user_id]); engelsystem_log('User ' . User_Nick_render($user_source) . ' has NO tshirt.'); $msg = success(_('Angel has got no t-shirt.'), true); } else { @@ -120,18 +131,22 @@ function admin_active() } } - $users = sql_select(" - SELECT - `User`.*, - COUNT(`ShiftEntry`.`id`) AS `shift_count`, - ${shift_sum_formula} AS `shift_length` - FROM `User` LEFT JOIN `ShiftEntry` ON `User`.`UID` = `ShiftEntry`.`UID` - LEFT JOIN `Shifts` ON `ShiftEntry`.`SID` = `Shifts`.`SID` " - . ($show_all_shifts ? "" : "AND (`Shifts`.`end` < " . time() . " OR `Shifts`.`end` IS NULL)") . " - WHERE `User`.`Gekommen` = 1 - GROUP BY `User`.`UID` - ORDER BY `force_active` DESC, `shift_length` DESC" . $limit - ); + $users = DB::select(sprintf(' + SELECT + `User`.*, + COUNT(`ShiftEntry`.`id`) AS `shift_count`, + %s AS `shift_length` + FROM `User` LEFT JOIN `ShiftEntry` ON `User`.`UID` = `ShiftEntry`.`UID` + LEFT JOIN `Shifts` ON `ShiftEntry`.`SID` = `Shifts`.`SID` ' + . ($show_all_shifts ? '' : 'AND (`Shifts`.`end` < ' . time() . " OR `Shifts`.`end` IS NULL)") . ' + WHERE `User`.`Gekommen` = 1 + GROUP BY `User`.`UID` + ORDER BY `force_active` DESC, `shift_length` DESC + %s + ', + $shift_sum_formula, + $limit + )); $matched_users = []; if ($search == '') { $tokens = []; @@ -194,21 +209,36 @@ function admin_active() $shirt_statistics = []; foreach (array_keys($tshirt_sizes) as $size) { if ($size != '') { + $sc = DB::select( + 'SELECT count(*) FROM `User` WHERE `Size`=? AND `Gekommen`=1', + [$size] + ); + $sc = array_shift($sc); + $sc = array_shift($sc); + + $gc = DB::select( + 'SELECT count(*) FROM `User` WHERE `Size`=? AND `Tshirt`=1', + [$size] + ); + $gc = array_shift($gc); + $gc = array_shift($gc); + $shirt_statistics[] = [ 'size' => $size, - 'needed' => sql_select_single_cell( - "SELECT count(*) FROM `User` WHERE `Size`='" . sql_escape($size) . "' AND `Gekommen`=1" - ), - 'given' => sql_select_single_cell( - "SELECT count(*) FROM `User` WHERE `Size`='" . sql_escape($size) . "' AND `Tshirt`=1" - ) + 'needed' => (int)$sc, + 'given' => (int)$gc ]; } } + + $uc = DB::select('SELECT count(*) FROM `User` WHERE `Tshirt`=1'); + $uc = array_shift($uc); + $uc = array_shift($uc); + $shirt_statistics[] = [ 'size' => '<b>' . _('Sum') . '</b>', 'needed' => '<b>' . User_arrived_count() . '</b>', - 'given' => '<b>' . sql_select_single_cell('SELECT count(*) FROM `User` WHERE `Tshirt`=1') . '</b>' + 'given' => '<b>' . (int)$uc . '</b>' ]; return page_with_title(admin_active_title(), [ diff --git a/includes/pages/admin_arrive.php b/includes/pages/admin_arrive.php index a17408e7..0080ccf9 100644 --- a/includes/pages/admin_arrive.php +++ b/includes/pages/admin_arrive.php @@ -1,5 +1,7 @@ <?php +use Engelsystem\Database\DB; + /** * @return string */ @@ -23,12 +25,12 @@ function admin_arrive() $user_id = $_REQUEST['reset']; $user_source = User($user_id); if ($user_source != null) { - sql_query(" + DB::update(' UPDATE `User` SET `Gekommen`=0, `arrival_date` = NULL - WHERE `UID`='" . sql_escape($user_id) . "' + WHERE `UID`=? LIMIT 1 - "); + ', [$user_id]); engelsystem_log('User set to not arrived: ' . User_Nick_render($user_source)); success(_('Reset done. Angel has not arrived.')); redirect(user_link($user_source)); @@ -39,12 +41,12 @@ function admin_arrive() $user_id = $_REQUEST['arrived']; $user_source = User($user_id); if ($user_source != null) { - sql_query(" + DB::update(' UPDATE `User` - SET `Gekommen`=1, `arrival_date`='" . time() . "' - WHERE `UID`='" . sql_escape($user_id) . "' + SET `Gekommen`=1, `arrival_date`=? + WHERE `UID`=? LIMIT 1 - "); + ', [time(), $user_id]); engelsystem_log('User set has arrived: ' . User_Nick_render($user_source)); success(_('Angel has been marked as arrived.')); redirect(user_link($user_source)); @@ -53,7 +55,7 @@ function admin_arrive() } } - $users = sql_select('SELECT * FROM `User` ORDER BY `Nick`'); + $users = DB::select('SELECT * FROM `User` ORDER BY `Nick`'); $arrival_count_at_day = []; $planned_arrival_count_at_day = []; $planned_departure_count_at_day = []; diff --git a/includes/pages/admin_free.php b/includes/pages/admin_free.php index 67d91006..630603ba 100644 --- a/includes/pages/admin_free.php +++ b/includes/pages/admin_free.php @@ -1,5 +1,7 @@ <?php +use Engelsystem\Database\DB; + /** * @return string */ @@ -20,19 +22,20 @@ function admin_free() $search = strip_request_item('search'); } - $angeltypesearch = ''; + $angelTypeSearch = ''; if (empty($_REQUEST['angeltype'])) { $_REQUEST['angeltype'] = ''; } else { - $angeltypesearch = ' INNER JOIN `UserAngelTypes` ON (`UserAngelTypes`.`angeltype_id` = \'' - . sql_escape($_REQUEST['angeltype']) . "' AND `UserAngelTypes`.`user_id` = `User`.`UID`"; + $angelTypeSearch = ' INNER JOIN `UserAngelTypes` ON (`UserAngelTypes`.`angeltype_id` = ' + . DB::getPdo()->quote($_REQUEST['angeltype']) + . ' AND `UserAngelTypes`.`user_id` = `User`.`UID`'; if (isset($_REQUEST['confirmed_only'])) { - $angeltypesearch .= ' AND `UserAngelTypes`.`confirm_user_id`'; + $angelTypeSearch .= ' AND `UserAngelTypes`.`confirm_user_id`'; } - $angeltypesearch .= ') '; + $angelTypeSearch .= ') '; } - $angel_types_source = sql_select('SELECT `id`, `name` FROM `AngelTypes` ORDER BY `name`'); + $angel_types_source = DB::select('SELECT `id`, `name` FROM `AngelTypes` ORDER BY `name`'); $angel_types = [ '' => 'alle Typen' ]; @@ -40,20 +43,27 @@ function admin_free() $angel_types[$angel_type['id']] = $angel_type['name']; } - $users = sql_select(" - SELECT `User`.* - FROM `User` - ${angeltypesearch} - LEFT JOIN `ShiftEntry` ON `User`.`UID` = `ShiftEntry`.`UID` - LEFT JOIN `Shifts` - ON ( - `ShiftEntry`.`SID` = `Shifts`.`SID` - AND `Shifts`.`start` < '" . sql_escape(time()) . "' - AND `Shifts`.`end` > '" . sql_escape(time()) . "' - ) - WHERE `User`.`Gekommen` = 1 AND `Shifts`.`SID` IS NULL - GROUP BY `User`.`UID` - ORDER BY `Nick`"); + $users = DB::select(' + SELECT `User`.* + FROM `User` + ' . $angelTypeSearch . ' + LEFT JOIN `ShiftEntry` ON `User`.`UID` = `ShiftEntry`.`UID` + LEFT JOIN `Shifts` + ON ( + `ShiftEntry`.`SID` = `Shifts`.`SID` + AND `Shifts`.`start` < ? + AND `Shifts`.`end` > ? + ) + WHERE `User`.`Gekommen` = 1 + AND `Shifts`.`SID` IS NULL + GROUP BY `User`.`UID` + ORDER BY `Nick` + ', + [ + time(), + time(), + ] + ); $free_users_table = []; if ($search == '') { diff --git a/includes/pages/admin_groups.php b/includes/pages/admin_groups.php index 8e578cb2..bc33a2b0 100644 --- a/includes/pages/admin_groups.php +++ b/includes/pages/admin_groups.php @@ -1,5 +1,7 @@ <?php +use Engelsystem\Database\DB; + /** * @return string */ @@ -14,20 +16,20 @@ function admin_groups_title() function admin_groups() { $html = ''; - $groups = sql_select('SELECT * FROM `Groups` ORDER BY `Name`'); + $groups = DB::select('SELECT * FROM `Groups` ORDER BY `Name`'); if (!isset($_REQUEST['action'])) { $groups_table = []; foreach ($groups as $group) { - $privileges = sql_select(" - SELECT * + $privileges = DB::select(' + SELECT `name` FROM `GroupPrivileges` JOIN `Privileges` ON (`GroupPrivileges`.`privilege_id` = `Privileges`.`id`) - WHERE `group_id`='" . sql_escape($group['UID']) . "' - "); + WHERE `group_id`=? + ', [$group['UID']]); $privileges_html = []; - foreach ($privileges as $priv) { - $privileges_html[] = $priv['name']; + foreach ($privileges as $privilege) { + $privileges_html[] = $privilege['name']; } $groups_table[] = [ @@ -57,33 +59,33 @@ function admin_groups() return error('Incomplete call, missing Groups ID.', true); } - $group = sql_select("SELECT * FROM `Groups` WHERE `UID`='" . sql_escape($group_id) . "' LIMIT 1"); - if (count($group) > 0) { - $privileges = sql_select(" + $group = DB::select('SELECT * FROM `Groups` WHERE `UID`=? LIMIT 1', [$group_id]); + if (!empty($group)) { + $privileges = DB::select(' SELECT `Privileges`.*, `GroupPrivileges`.`group_id` FROM `Privileges` LEFT OUTER JOIN `GroupPrivileges` ON ( `Privileges`.`id` = `GroupPrivileges`.`privilege_id` - AND `GroupPrivileges`.`group_id`='" . sql_escape($group_id) . "' + AND `GroupPrivileges`.`group_id`=? ) ORDER BY `Privileges`.`name` - "); + ', [$group_id]); $privileges_html = ''; $privileges_form = []; - foreach ($privileges as $priv) { + foreach ($privileges as $privilege) { $privileges_form[] = form_checkbox( 'privileges[]', - $priv['desc'] . ' (' . $priv['name'] . ')', - $priv['group_id'] != '', - $priv['id'] + $privilege['desc'] . ' (' . $privilege['name'] . ')', + $privilege['group_id'] != '', + $privilege['id'] ); $privileges_html .= sprintf( '<tr><td><input type="checkbox" name="privileges[]" value="%s" %s /></td> <td>%s</td> <td>%s</td></tr>', - $priv['id'], - ($priv['group_id'] != '' ? 'checked="checked"' : ''), - $priv['name'], - $priv['desc'] + $privilege['id'], + ($privilege['group_id'] != '' ? 'checked="checked"' : ''), + $privilege['name'], + $privilege['desc'] ); } @@ -103,20 +105,27 @@ function admin_groups() return error('Incomplete call, missing Groups ID.', true); } - $group = sql_select("SELECT * FROM `Groups` WHERE `UID`='" . sql_escape($group_id) . "' LIMIT 1"); + $group = DB::select('SELECT * FROM `Groups` WHERE `UID`=? LIMIT 1', [$group_id]); if (!is_array($_REQUEST['privileges'])) { $_REQUEST['privileges'] = []; } - if (count($group) > 0) { - list($group) = $group; - sql_query("DELETE FROM `GroupPrivileges` WHERE `group_id`='" . sql_escape($group_id) . "'"); + if (!empty($group)) { + $group = array_shift($group); + DB::delete('DELETE FROM `GroupPrivileges` WHERE `group_id`=?', [$group_id]); $privilege_names = []; - foreach ($_REQUEST['privileges'] as $priv) { - if (preg_match("/^[0-9]{1,}$/", $priv)) { - $group_privileges_source = sql_select("SELECT * FROM `Privileges` WHERE `id`='" . sql_escape($priv) . "' LIMIT 1"); - if (count($group_privileges_source) > 0) { - sql_query("INSERT INTO `GroupPrivileges` SET `group_id`='" . sql_escape($group_id) . "', `privilege_id`='" . sql_escape($priv) . "'"); - $privilege_names[] = $group_privileges_source[0]['name']; + foreach ($_REQUEST['privileges'] as $privilege) { + if (preg_match("/^[0-9]{1,}$/", $privilege)) { + $group_privileges_source = DB::select( + 'SELECT `name` FROM `Privileges` WHERE `id`=? LIMIT 1', + [$privilege] + ); + if (!empty($group_privileges_source)) { + $group_privileges_source = array_shift($group_privileges_source); + DB::insert( + 'INSERT INTO `GroupPrivileges` (`group_id`, `privilege_id`) VALUES (?, ?)', + [$group_id, $privilege] + ); + $privilege_names[] = $group_privileges_source['name']; } } } diff --git a/includes/pages/admin_import.php b/includes/pages/admin_import.php index 2e37572f..7a246b4b 100644 --- a/includes/pages/admin_import.php +++ b/includes/pages/admin_import.php @@ -1,5 +1,7 @@ <?php +use Engelsystem\Database\DB; + /** * @return string */ @@ -252,10 +254,10 @@ function admin_import() if ($result === false) { engelsystem_error('Unable to create room.'); } - $rooms_import[trim($room)] = sql_id(); + $rooms_import[trim($room)] = $result; } foreach ($rooms_deleted as $room) { - sql_query("DELETE FROM `Room` WHERE `Name`='" . sql_escape($room) . "' LIMIT 1"); + DB::delete('DELETE FROM `Room` WHERE `Name`=? LIMIT 1', [$room]); } list($events_new, $events_updated, $events_deleted) = prepare_events( @@ -317,11 +319,11 @@ function prepare_rooms($file) $data = read_xml($file); // Load rooms from db for compare with input - $rooms = sql_select('SELECT * FROM `Room` WHERE `FromPentabarf`=\'Y\''); + $rooms = DB::select('SELECT `Name`, `RID` FROM `Room` WHERE `FromPentabarf`=\'Y\''); $rooms_db = []; $rooms_import = []; foreach ($rooms as $room) { - $rooms_db[] = (string)$room['Name']; + $rooms_db[] = $room['Name']; $rooms_import[$room['Name']] = $room['RID']; } @@ -356,7 +358,7 @@ function prepare_events($file, $shifttype_id, $add_minutes_start, $add_minutes_e global $rooms_import; $data = read_xml($file); - $rooms = sql_select('SELECT * FROM `Room`'); + $rooms = Rooms(true); $rooms_db = []; foreach ($rooms as $room) { $rooms_db[$room['Name']] = $room['RID']; @@ -378,7 +380,7 @@ function prepare_events($file, $shifttype_id, $add_minutes_start, $add_minutes_e ]; } - $shifts = sql_select('SELECT * FROM `Shifts` WHERE `PSID` IS NOT NULL ORDER BY `start`'); + $shifts = DB::select('SELECT * FROM `Shifts` WHERE `PSID` IS NOT NULL ORDER BY `start`'); $shifts_db = []; foreach ($shifts as $shift) { $shifts_db[$shift['PSID']] = $shift; diff --git a/includes/pages/admin_news.php b/includes/pages/admin_news.php index 6d52bcf5..86631d6b 100644 --- a/includes/pages/admin_news.php +++ b/includes/pages/admin_news.php @@ -1,5 +1,7 @@ <?php +use Engelsystem\Database\DB; + /** * @return string */ @@ -18,14 +20,14 @@ function admin_news() return error('Incomplete call, missing News ID.', true); } - $news = sql_select("SELECT * FROM `News` WHERE `ID`='" . sql_escape($news_id) . "' LIMIT 1"); + $news = DB::select('SELECT * FROM `News` WHERE `ID`=? LIMIT 1', [$news_id]); if (empty($news)) { return error('No News found.', true); } switch ($_REQUEST['action']) { case 'edit': - list($news) = $news; + $news = array_shift($news); $user_source = User($news['UID']); $html .= form([ @@ -43,21 +45,32 @@ function admin_news() break; case 'save': - sql_query("UPDATE `News` SET - `Datum`='" . sql_escape(time()) . "', - `Betreff`='" . sql_escape($_POST["eBetreff"]) . "', - `Text`='" . sql_escape($_POST["eText"]) . "', - `UID`='" . sql_escape($user['UID']) . "', - `Treffen`='" . sql_escape($_POST["eTreffen"]) . "' - WHERE `ID`='" . sql_escape($news_id) . "'"); + DB::update(' + UPDATE `News` SET + `Datum`=?, + `Betreff`=?, + `Text`=?, + `UID`=?, + `Treffen`=? + WHERE `ID`=? + ', + [ + time(), + $_POST["eBetreff"], + $_POST["eText"], + $user['UID'], + isset($_POST["eTreffen"]) ? 1 : 0, + $news_id + ] + ); engelsystem_log('News updated: ' . $_POST['eBetreff']); success(_('News entry updated.')); redirect(page_link_to('news')); break; case 'delete': - list($news) = $news; - sql_query("DELETE FROM `News` WHERE `ID`='" . sql_escape($news_id) . "' LIMIT 1"); + $news = array_shift($news); + DB::delete('DELETE FROM `News` WHERE `ID`=? LIMIT 1', [$news_id]); engelsystem_log('News deleted: ' . $news['Betreff']); success(_('News entry deleted.')); redirect(page_link_to('news')); diff --git a/includes/pages/admin_questions.php b/includes/pages/admin_questions.php index 7dcb3057..ef84b111 100644 --- a/includes/pages/admin_questions.php +++ b/includes/pages/admin_questions.php @@ -1,5 +1,7 @@ <?php +use Engelsystem\Database\DB; + /** * @return string */ @@ -19,7 +21,7 @@ function admin_new_questions() if ($page != 'admin_questions') { if (in_array('admin_questions', $privileges)) { - $new_messages = sql_num_query('SELECT * FROM `Questions` WHERE `AID` IS NULL'); + $new_messages = count(DB::select('SELECT `QID` FROM `Questions` WHERE `AID` IS NULL')); if ($new_messages > 0) { return '<a href="' . page_link_to("admin_questions") . '">' . _('There are unanswered questions!') . '</a>'; @@ -39,7 +41,7 @@ function admin_questions() if (!isset($_REQUEST['action'])) { $unanswered_questions_table = []; - $questions = sql_select("SELECT * FROM `Questions` WHERE `AID` IS NULL"); + $questions = DB::select('SELECT * FROM `Questions` WHERE `AID` IS NULL'); foreach ($questions as $question) { $user_source = User($question['UID']); @@ -59,7 +61,7 @@ function admin_questions() } $answered_questions_table = []; - $questions = sql_select("SELECT * FROM `Questions` WHERE NOT `AID` IS NULL"); + $questions = DB::select('SELECT * FROM `Questions` WHERE NOT `AID` IS NULL'); foreach ($questions as $question) { $user_source = User($question['UID']); $answer_user_source = User($question['AID']); @@ -102,7 +104,10 @@ function admin_questions() return error('Incomplete call, missing Question ID.', true); } - $question = sql_select("SELECT * FROM `Questions` WHERE `QID`='" . sql_escape($question_id) . "' LIMIT 1"); + $question = DB::select( + 'SELECT * FROM `Questions` WHERE `QID`=? LIMIT 1', + [$question_id] + ); if (count($question) > 0 && $question[0]['AID'] == null) { $answer = trim( preg_replace("/([^\p{L}\p{P}\p{Z}\p{N}\n]{1,})/ui", @@ -111,12 +116,19 @@ function admin_questions() )); if ($answer != '') { - sql_query(" - UPDATE `Questions` - SET `AID`='" . sql_escape($user['UID']) . "', `Answer`='" . sql_escape($answer) . "' - WHERE `QID`='" . sql_escape($question_id) . "' - LIMIT 1 - "); + DB::update( + ' + UPDATE `Questions` + SET `AID`=?, `Answer`=? + WHERE `QID`=? + LIMIT 1 + ', + [ + $user['UID'], + $answer, + $question_id, + ] + ); engelsystem_log('Question ' . $question[0]['Question'] . ' answered: ' . $answer); redirect(page_link_to('admin_questions')); } else { @@ -133,9 +145,12 @@ function admin_questions() return error('Incomplete call, missing Question ID.', true); } - $question = sql_select("SELECT * FROM `Questions` WHERE `QID`='" . sql_escape($question_id) . "' LIMIT 1"); + $question = DB::select( + 'SELECT * FROM `Questions` WHERE `QID`=? LIMIT 1', + [$question_id] + ); if (count($question) > 0) { - sql_query("DELETE FROM `Questions` WHERE `QID`='" . sql_escape($question_id) . "' LIMIT 1"); + DB::delete('DELETE FROM `Questions` WHERE `QID`=? LIMIT 1', [$question_id]); engelsystem_log('Question deleted: ' . $question[0]['Question']); redirect(page_link_to('admin_questions')); } else { diff --git a/includes/pages/admin_rooms.php b/includes/pages/admin_rooms.php index 61923689..50be15f3 100644 --- a/includes/pages/admin_rooms.php +++ b/includes/pages/admin_rooms.php @@ -1,5 +1,7 @@ <?php +use Engelsystem\Database\DB; + /** * @return string */ @@ -13,7 +15,7 @@ function admin_rooms_title() */ function admin_rooms() { - $rooms_source = sql_select('SELECT * FROM `Room` ORDER BY `Name`'); + $rooms_source = DB::select('SELECT * FROM `Room` ORDER BY `Name`'); $rooms = []; foreach ($rooms_source as $room) { $rooms[] = [ @@ -36,7 +38,7 @@ function admin_rooms() $number = ''; $room_id = 0; - $angeltypes_source = sql_select('SELECT * FROM `AngelTypes` ORDER BY `name`'); + $angeltypes_source = DB::select('SELECT `id`, `name` FROM `AngelTypes` ORDER BY `name`'); $angeltypes = []; $angeltypes_count = []; foreach ($angeltypes_source as $angeltype) { @@ -59,7 +61,10 @@ function admin_rooms() $public = $room['show']; $number = $room['Number']; - $needed_angeltypes = sql_select("SELECT * FROM `NeededAngelTypes` WHERE `room_id`='" . sql_escape($room_id) . "'"); + $needed_angeltypes = DB::select( + 'SELECT `angel_type_id`, `count` FROM `NeededAngelTypes` WHERE `room_id`=?', + [$room_id] + ); foreach ($needed_angeltypes as $needed_angeltype) { $angeltypes_count[$needed_angeltype['angel_type_id']] = $needed_angeltype['count']; } @@ -71,7 +76,13 @@ function admin_rooms() if (isset($_REQUEST['name']) && strlen(strip_request_item('name')) > 0) { $name = strip_request_item('name'); - if (isset($room) && sql_num_query("SELECT * FROM `Room` WHERE `Name`='" . sql_escape($name) . "' AND NOT `RID`=" . sql_escape($room_id)) > 0) { + if ( + isset($room) + && count(DB::select( + 'SELECT RID FROM `Room` WHERE `Name`=? AND NOT `RID`=?', + [$name, $room_id] + )) > 0 + ) { $valid = false; $msg .= error(_('This name is already in use.'), true); } @@ -111,17 +122,23 @@ function admin_rooms() } if ($valid) { - if (isset($room_id)) { - sql_query(" + if (!empty($room_id)) { + DB::update(' UPDATE `Room` SET - `Name`='" . sql_escape($name) . "', - `FromPentabarf`='" . sql_escape($from_pentabarf) . "', - `show`='" . sql_escape($public) . "', - `Number`='" . sql_escape($number) . "' - WHERE `RID`='" . sql_escape($room_id) . "' + `Name`=?, + `FromPentabarf`=?, + `show`=?, + `Number`=? + WHERE `RID`=? LIMIT 1 - "); + ', [ + $name, + $from_pentabarf, + $public, + $number, + $room_id, + ]); engelsystem_log( 'Room updated: ' . $name . ', pentabarf import: ' . $from_pentabarf diff --git a/includes/pages/admin_shifts.php b/includes/pages/admin_shifts.php index 1e19c5e4..c543e827 100644 --- a/includes/pages/admin_shifts.php +++ b/includes/pages/admin_shifts.php @@ -1,5 +1,7 @@ <?php +use Engelsystem\Database\DB; + /** * @return string */ @@ -27,14 +29,14 @@ function admin_shifts() $shifttype_id = null; // Locations laden (auch unsichtbare - fuer Erzengel ist das ok) - $rooms = sql_select('SELECT * FROM `Room` ORDER BY `Name`'); + $rooms = DB::select('SELECT `RID`, `Name` FROM `Room` ORDER BY `Name`'); $room_array = []; foreach ($rooms as $room) { $room_array[$room['RID']] = $room['Name']; } // Engeltypen laden - $types = sql_select('SELECT * FROM `AngelTypes` ORDER BY `name`'); + $types = DB::select('SELECT * FROM `AngelTypes` ORDER BY `name`'); $needed_angel_types = []; foreach ($types as $type) { $needed_angel_types[$type['id']] = 0; @@ -53,9 +55,6 @@ function admin_shifts() if (isset($_REQUEST['preview']) || isset($_REQUEST['back'])) { if (isset($_REQUEST['shifttype_id'])) { $shifttype = ShiftType($_REQUEST['shifttype_id']); - if ($shifttype === false) { - engelsystem_error('Unable to load shift type.'); - } if ($shifttype == null) { $valid = false; error(_('Please select a shift type.')); @@ -168,7 +167,13 @@ function admin_shifts() if ($valid) { if ($angelmode == 'location') { $needed_angel_types = []; - $needed_angel_types_location = sql_select("SELECT * FROM `NeededAngelTypes` WHERE `room_id`='" . sql_escape($rid) . "'"); + $needed_angel_types_location = DB::select(' + SELECT `angel_type_id`, `count` + FROM `NeededAngelTypes` + WHERE `room_id`=? + ', + [$rid] + ); foreach ($needed_angel_types_location as $type) { $needed_angel_types[$type['angel_type_id']] = $type['count']; } @@ -300,7 +305,12 @@ function admin_shifts() ]); } } elseif (isset($_REQUEST['submit'])) { - if (!is_array($_SESSION['admin_shifts_shifts']) || !is_array($_SESSION['admin_shifts_types'])) { + if ( + !isset($_SESSION['admin_shifts_shifts']) + || !isset($_SESSION['admin_shifts_types']) + || !is_array($_SESSION['admin_shifts_shifts']) + || !is_array($_SESSION['admin_shifts_types']) + ) { redirect(page_link_to('admin_shifts')); } @@ -321,10 +331,23 @@ function admin_shifts() ); foreach ($_SESSION['admin_shifts_types'] as $type_id => $count) { - $angel_type_source = sql_select("SELECT * FROM `AngelTypes` WHERE `id`='" . sql_escape($type_id) . "' LIMIT 1"); - if (count($angel_type_source) > 0) { - sql_query("INSERT INTO `NeededAngelTypes` SET `shift_id`='" . sql_escape($shift_id) . "', `angel_type_id`='" . sql_escape($type_id) . "', `count`='" . sql_escape($count) . "'"); - $needed_angel_types_info[] = $angel_type_source[0]['name'] . ": " . $count; + $angel_type_source = DB::select(' + SELECT * + FROM `AngelTypes` + WHERE `id` = ? + LIMIT 1', [$type_id]); + if (!empty($angel_type_source)) { + DB::insert(' + INSERT INTO `NeededAngelTypes` (`shift_id`, `angel_type_id`, `count`) + VALUES (?, ?, ?) + ', + [ + $shift_id, + $type_id, + $count + ] + ); + $needed_angel_types_info[] = $angel_type_source[0]['name'] . ': ' . $count; } } } diff --git a/includes/pages/admin_user.php b/includes/pages/admin_user.php index 8e11c5f3..192becb0 100644 --- a/includes/pages/admin_user.php +++ b/includes/pages/admin_user.php @@ -1,5 +1,7 @@ <?php +use Engelsystem\Database\DB; + /** * @return string */ @@ -106,12 +108,18 @@ function admin_user() $html .= '<hr />'; - $my_highest_group = sql_select("SELECT * FROM `UserGroups` WHERE `uid`='" . sql_escape($user['UID']) . "' ORDER BY `group_id` LIMIT 1"); + $my_highest_group = DB::select( + 'SELECT group_id FROM `UserGroups` WHERE `uid`=? ORDER BY `group_id` LIMIT 1', + [$user['UID']] + ); if (count($my_highest_group) > 0) { $my_highest_group = $my_highest_group[0]['group_id']; } - $his_highest_group = sql_select("SELECT * FROM `UserGroups` WHERE `uid`='" . sql_escape($user_id) . "' ORDER BY `group_id` LIMIT 1"); + $his_highest_group = DB::select( + 'SELECT `group_id` FROM `UserGroups` WHERE `uid`=? ORDER BY `group_id` LIMIT 1', + [$user_id] + ); if (count($his_highest_group) > 0) { $his_highest_group = $his_highest_group[0]['group_id']; } @@ -121,16 +129,21 @@ function admin_user() . page_link_to('admin_user') . '&action=save_groups&id=' . $user_id . '" method="post">' . "\n"; $html .= '<table>'; - $groups = sql_select(" - SELECT * - FROM `Groups` - LEFT OUTER JOIN `UserGroups` ON ( - `UserGroups`.`group_id` = `Groups`.`UID` - AND `UserGroups`.`uid` = '" . sql_escape($user_id) . "' - ) - WHERE `Groups`.`UID` >= '" . sql_escape($my_highest_group) . "' - ORDER BY `Groups`.`Name` - "); + $groups = DB::select(' + SELECT * + FROM `Groups` + LEFT OUTER JOIN `UserGroups` ON ( + `UserGroups`.`group_id` = `Groups`.`UID` + AND `UserGroups`.`uid` = ? + ) + WHERE `Groups`.`UID` >= ? + ORDER BY `Groups`.`Name` + ', + [ + $user_id, + $my_highest_group, + ] + ); foreach ($groups as $group) { $html .= '<tr><td><input type="checkbox" name="groups[]" value="' . $group['UID'] . '" ' . ($group['group_id'] != '' ? ' checked="checked"' : '') @@ -154,20 +167,37 @@ function admin_user() switch ($_REQUEST['action']) { case 'save_groups': if ($user_id != $user['UID']) { - $my_highest_group = sql_select("SELECT * FROM `UserGroups` WHERE `uid`='" . sql_escape($user['UID']) . "' ORDER BY `group_id`"); - $his_highest_group = sql_select("SELECT * FROM `UserGroups` WHERE `uid`='" . sql_escape($user_id) . "' ORDER BY `group_id`"); - - if (count($my_highest_group) > 0 && (count($his_highest_group) == 0 || ($my_highest_group[0]['group_id'] <= $his_highest_group[0]['group_id']))) { - $groups_source = sql_select(" - SELECT * - FROM `Groups` - LEFT OUTER JOIN `UserGroups` ON ( - `UserGroups`.`group_id` = `Groups`.`UID` - AND `UserGroups`.`uid` = '" . sql_escape($user_id) . "' - ) - WHERE `Groups`.`UID` >= '" . sql_escape($my_highest_group[0]['group_id']) . "' - ORDER BY `Groups`.`Name` - "); + $my_highest_group = DB::select( + 'SELECT * FROM `UserGroups` WHERE `uid`=? ORDER BY `group_id`', + [$user['UID']] + ); + $his_highest_group = DB::select( + 'SELECT * FROM `UserGroups` WHERE `uid`=? ORDER BY `group_id`', + [$user_id] + ); + + if ( + count($my_highest_group) > 0 + && ( + count($his_highest_group) == 0 + || ($my_highest_group[0]['group_id'] <= $his_highest_group[0]['group_id']) + ) + ) { + $groups_source = DB::select(' + SELECT * + FROM `Groups` + LEFT OUTER JOIN `UserGroups` ON ( + `UserGroups`.`group_id` = `Groups`.`UID` + AND `UserGroups`.`uid` = ? + ) + WHERE `Groups`.`UID` >= ? + ORDER BY `Groups`.`Name` + ', + [ + $user_id, + $my_highest_group[0]['group_id'], + ] + ); $groups = []; $grouplist = []; foreach ($groups_source as $group) { @@ -179,11 +209,14 @@ function admin_user() $_REQUEST['groups'] = []; } - sql_query("DELETE FROM `UserGroups` WHERE `uid`='" . sql_escape($user_id) . "'"); + DB::delete('DELETE FROM `UserGroups` WHERE `uid`=?', [$user_id]); $user_groups_info = []; foreach ($_REQUEST['groups'] as $group) { if (in_array($group, $grouplist)) { - sql_query("INSERT INTO `UserGroups` SET `uid`='" . sql_escape($user_id) . "', `group_id`='" . sql_escape($group) . "'"); + DB::insert( + 'INSERT INTO `UserGroups` (`uid`, `group_id`) VALUES (?, ?)', + [$user_id, $group] + ); $user_groups_info[] = $groups[$group]['Name']; } } @@ -206,25 +239,42 @@ function admin_user() if (in_array('admin_active', $privileges)) { $force_active = $_REQUEST['force_active']; } - $SQL = "UPDATE `User` SET - `Nick` = '" . sql_escape($_POST["eNick"]) . "', - `Name` = '" . sql_escape($_POST["eName"]) . "', - `Vorname` = '" . sql_escape($_POST["eVorname"]) . "', - `Telefon` = '" . sql_escape($_POST["eTelefon"]) . "', - `Handy` = '" . sql_escape($_POST["eHandy"]) . "', - `Alter` = '" . sql_escape($_POST["eAlter"]) . "', - `DECT` = '" . sql_escape($_POST["eDECT"]) . "', - " . ($user_source['email_by_human_allowed'] ? "`email` = '" . sql_escape($_POST["eemail"]) . "'," : "") . " - `jabber` = '" . sql_escape($_POST["ejabber"]) . "', - `Size` = '" . sql_escape($_POST["eSize"]) . "', - `Gekommen`= '" . sql_escape($_POST["eGekommen"]) . "', - `Aktiv`= '" . sql_escape($_POST["eAktiv"]) . "', - `force_active`= " . sql_escape($force_active) . ", - `Tshirt` = '" . sql_escape($_POST["eTshirt"]) . "', - `Hometown` = '" . sql_escape($_POST["Hometown"]) . "' - WHERE `UID` = '" . sql_escape($user_id) . "' - LIMIT 1"; - sql_query($SQL); + $sql = ' + UPDATE `User` SET + `Nick` = ?, + `Name` = ?, + `Vorname` = ?, + `Telefon` = ?, + `Handy` = ?, + `Alter` =?, + `DECT` = ?, + ' . ($user_source['email_by_human_allowed'] ? '`email` = ' . DB::getPdo()->quote($_POST["eemail"]) . ',' : '') . ' + `jabber` = ?, + `Size` = ?, + `Gekommen`= ?, + `Aktiv`= ?, + `force_active`= ?, + `Tshirt` = ?, + `Hometown` = ? + WHERE `UID` = ? + LIMIT 1'; + DB::update($sql, [ + $_POST['eNick'], + $_POST['eName'], + $_POST['eVorname'], + $_POST['eTelefon'], + $_POST['eHandy'], + $_POST['eAlter'], + $_POST['eDECT'], + $_POST['ejabber'], + $_POST['eSize'], + $_POST['eGekommen'], + $_POST['eAktiv'], + $force_active, + $_POST['eTshirt'], + $_POST['Hometown'], + $user_id, + ]); engelsystem_log( 'Updated user: ' . $_POST['eNick'] . ', ' . $_POST['eSize'] . ', arrived: ' . $_POST['eGekommen'] diff --git a/includes/pages/guest_login.php b/includes/pages/guest_login.php index d202d92d..f08f9260 100644 --- a/includes/pages/guest_login.php +++ b/includes/pages/guest_login.php @@ -1,5 +1,7 @@ <?php +use Engelsystem\Database\DB; + /** * @return string */ @@ -37,8 +39,8 @@ function guest_register() $msg = ''; $nick = ''; - $lastname = ''; - $prename = ''; + $lastName = ''; + $preName = ''; $age = ''; $tel = ''; $dect = ''; @@ -68,7 +70,7 @@ function guest_register() if (isset($_REQUEST['nick']) && strlen(User_validate_Nick($_REQUEST['nick'])) > 1) { $nick = User_validate_Nick($_REQUEST['nick']); - if (sql_num_query("SELECT * FROM `User` WHERE `Nick`='" . sql_escape($nick) . "' LIMIT 1") > 0) { + if (count(DB::select('SELECT `UID` FROM `User` WHERE `Nick`=? LIMIT 1', [$nick])) > 0) { $valid = false; $msg .= error(sprintf(_('Your nick "%s" already exists.'), $nick), true); } @@ -148,10 +150,10 @@ function guest_register() // Trivia if (isset($_REQUEST['lastname'])) { - $lastname = strip_request_item('lastname'); + $lastName = strip_request_item('lastname'); } if (isset($_REQUEST['prename'])) { - $prename = strip_request_item('prename'); + $preName = strip_request_item('prename'); } if (isset($_REQUEST['age']) && preg_match("/^[0-9]{0,4}$/", $_REQUEST['age'])) { $age = strip_request_item('age'); @@ -173,38 +175,65 @@ function guest_register() } if ($valid) { - sql_query(" - INSERT INTO `User` SET - `color`='" . sql_escape($default_theme) . "', - `Nick`='" . sql_escape($nick) . "', - `Vorname`='" . sql_escape($prename) . "', - `Name`='" . sql_escape($lastname) . "', - `Alter`='" . sql_escape($age) . "', - `Telefon`='" . sql_escape($tel) . "', - `DECT`='" . sql_escape($dect) . "', - `Handy`='" . sql_escape($mobile) . "', - `email`='" . sql_escape($mail) . "', - `email_shiftinfo`=" . sql_bool($email_shiftinfo) . ", - `email_by_human_allowed`=" . sql_bool($email_by_human_allowed) . ", - `jabber`='" . sql_escape($jabber) . "', - `Size`='" . sql_escape($tshirt_size) . "', - `Passwort`='" . sql_escape($password_hash) . "', - `kommentar`='" . sql_escape($comment) . "', - `Hometown`='" . sql_escape($hometown) . "', - `CreateDate`=NOW(), - `Sprache`='" . sql_escape($_SESSION["locale"]) . "', - `arrival_date`=NULL, - `planned_arrival_date`='" . sql_escape($planned_arrival_date) . "'"); + DB::insert(' + INSERT INTO `User` ( + `color`, + `Nick`, + `Vorname`, + `Name`, + `Alter`, + `Telefon`, + `DECT`, + `Handy`, + `email`, + `email_shiftinfo`, + `email_by_human_allowed`, + `jabber`, + `Size`, + `Passwort`, + `kommentar`, + `Hometown`, + `CreateDate`, + `Sprache`, + `arrival_date`, + `planned_arrival_date` + ) + VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), ?, NULL, ?) + ', + [ + $default_theme, + $nick, + $preName, + $lastName, + $age, + $tel, + $dect, + $mobile, + $mail, + (bool)$email_shiftinfo, + (bool)$email_by_human_allowed, + $jabber, + $tshirt_size, + $password_hash, + $comment, + $hometown, + $_SESSION['locale'], + $planned_arrival_date, + ] + ); // Assign user-group and set password - $user_id = sql_id(); - sql_query("INSERT INTO `UserGroups` SET `uid`='" . sql_escape($user_id) . "', `group_id`=-2"); + $user_id = DB::getPdo()->lastInsertId(); + DB::insert('INSERT INTO `UserGroups` (`uid`, `group_id`) VALUES (?, -2)', [$user_id]); set_password($user_id, $_REQUEST['password']); // Assign angel-types $user_angel_types_info = []; foreach ($selected_angel_types as $selected_angel_type_id) { - sql_query("INSERT INTO `UserAngelTypes` SET `user_id`='" . sql_escape($user_id) . "', `angeltype_id`='" . sql_escape($selected_angel_type_id) . "'"); + DB::insert( + 'INSERT INTO `UserAngelTypes` (`user_id`, `angeltype_id`) VALUES (?, ?)', + [$user_id, $selected_angel_type_id] + ); $user_angel_types_info[] = $angel_types[$selected_angel_type_id]; } @@ -316,10 +345,10 @@ function guest_register() form_text('jabber', _('Jabber'), $jabber), div('row', [ div('col-sm-6', [ - form_text('prename', _('First name'), $prename) + form_text('prename', _('First name'), $preName) ]), div('col-sm-6', [ - form_text('lastname', _('Last name'), $lastname) + form_text('lastname', _('Last name'), $lastName) ]) ]), div('row', [ @@ -361,7 +390,7 @@ function guest_login() if (isset($_REQUEST['submit'])) { if (isset($_REQUEST['nick']) && strlen(User_validate_Nick($_REQUEST['nick'])) > 0) { $nick = User_validate_Nick($_REQUEST['nick']); - $login_user = sql_select("SELECT * FROM `User` WHERE `Nick`='" . sql_escape($nick) . "'"); + $login_user = DB::select('SELECT * FROM `User` WHERE `Nick`=?', [$nick]); if (count($login_user) > 0) { $login_user = $login_user[0]; if (isset($_REQUEST['password'])) { diff --git a/includes/pages/guest_stats.php b/includes/pages/guest_stats.php index c4e1af74..4add3e97 100644 --- a/includes/pages/guest_stats.php +++ b/includes/pages/guest_stats.php @@ -1,5 +1,7 @@ <?php +use Engelsystem\Database\DB; + function guest_stats() { global $api_key; @@ -8,21 +10,23 @@ function guest_stats() if ($_REQUEST['api_key'] == $api_key) { $stats = []; - list($user_count) = sql_select('SELECT count(*) AS `user_count` FROM `User`'); + list($user_count) = DB::select('SELECT count(*) AS `user_count` FROM `User`'); $stats['user_count'] = $user_count['user_count']; - list($arrived_user_count) = sql_select('SELECT count(*) AS `user_count` FROM `User` WHERE `Gekommen`=1'); + list($arrived_user_count) = DB::select('SELECT count(*) AS `user_count` FROM `User` WHERE `Gekommen`=1'); $stats['arrived_user_count'] = $arrived_user_count['user_count']; - $done_shifts_seconds = sql_select_single_cell(' + $done_shifts_seconds = DB::select(' SELECT SUM(`Shifts`.`end` - `Shifts`.`start`) FROM `ShiftEntry` JOIN `Shifts` USING (`SID`) WHERE `Shifts`.`end` < UNIX_TIMESTAMP() '); + $done_shifts_seconds = array_shift($done_shifts_seconds); + $done_shifts_seconds = (int)array_shift($done_shifts_seconds); $stats['done_work_hours'] = round($done_shifts_seconds / (60 * 60), 0); - $users_in_action = sql_select(' + $users_in_action = DB::select(' SELECT `Shifts`.`start`, `Shifts`.`end` FROM `ShiftEntry` JOIN `Shifts` ON `Shifts`.`SID`=`ShiftEntry`.`SID` diff --git a/includes/pages/user_atom.php b/includes/pages/user_atom.php index 913a8821..04edf03f 100644 --- a/includes/pages/user_atom.php +++ b/includes/pages/user_atom.php @@ -1,5 +1,7 @@ <?php +use Engelsystem\Database\DB; + /** * Publically available page to feed the news to feedreaders */ @@ -20,12 +22,12 @@ function user_atom() engelsystem_error('No privilege for atom.'); } - $news = sql_select(" + $news = DB::select(' SELECT * FROM `News` - " . (empty($_REQUEST['meetings']) ? '' : 'WHERE `Treffen` = 1 ') . " + ' . (empty($_REQUEST['meetings']) ? '' : 'WHERE `Treffen` = 1 ') . ' ORDER BY `ID` - DESC LIMIT " . (int)$display_news + DESC LIMIT ' . (int)$display_news ); $output = make_atom_entries_from_news($news); diff --git a/includes/pages/user_messages.php b/includes/pages/user_messages.php index 00dbafe8..e4669411 100644 --- a/includes/pages/user_messages.php +++ b/includes/pages/user_messages.php @@ -1,5 +1,7 @@ <?php +use Engelsystem\Database\DB; + /** * @return string */ @@ -16,7 +18,10 @@ function user_unread_messages() global $user; if (isset($user)) { - $new_messages = sql_num_query("SELECT * FROM `Messages` WHERE isRead='N' AND `RUID`='" . sql_escape($user['UID']) . "'"); + $new_messages = count(DB::select( + 'SELECT `id` FROM `Messages` WHERE isRead=\'N\' AND `RUID`=?', + [$user['UID']] + )); if ($new_messages > 0) { return ' <span class="badge danger">' . $new_messages . '</span>'; } @@ -32,7 +37,10 @@ function user_messages() global $user; if (!isset($_REQUEST['action'])) { - $users = sql_select("SELECT * FROM `User` WHERE NOT `UID`='" . sql_escape($user['UID']) . "' ORDER BY `Nick`"); + $users = DB::select( + 'SELECT `UID`, `Nick` FROM `User` WHERE NOT `UID`=? ORDER BY `Nick`', + [$user['UID']] + ); $to_select_data = [ '' => _('Select recipient...') @@ -44,13 +52,18 @@ function user_messages() $to_select = html_select_key('to', 'to', $to_select_data, ''); - $messages = sql_select(" + $messages = DB::select(' SELECT * FROM `Messages` - WHERE `SUID`='" . sql_escape($user['UID']) . "' - OR `RUID`='" . sql_escape($user['UID']) . "' + WHERE `SUID`=? + OR `RUID`=? ORDER BY `isRead`,`Datum` DESC - "); + ', + [ + $user['UID'], + $user['UID'], + ] + ); $messages_table = [ [ @@ -116,9 +129,15 @@ function user_messages() return error(_('Incomplete call, missing Message ID.'), true); } - $message = sql_select("SELECT * FROM `Messages` WHERE `id`='" . sql_escape($message_id) . "' LIMIT 1"); + $message = DB::select( + 'SELECT `RUID` FROM `Messages` WHERE `id`=? LIMIT 1', + [$message_id] + ); if (count($message) > 0 && $message[0]['RUID'] == $user['UID']) { - sql_query("UPDATE `Messages` SET `isRead`='Y' WHERE `id`='" . sql_escape($message_id) . "' LIMIT 1"); + DB::update( + 'UPDATE `Messages` SET `isRead`=\'Y\' WHERE `id`=? LIMIT 1', + [$message_id] + ); redirect(page_link_to('user_messages')); } else { return error(_('No Message found.'), true); @@ -132,9 +151,12 @@ function user_messages() return error(_('Incomplete call, missing Message ID.'), true); } - $message = sql_select("SELECT * FROM `Messages` WHERE `id`='" . sql_escape($message_id) . "' LIMIT 1"); + $message = DB::select( + 'SELECT `SUID` FROM `Messages` WHERE `id`=? LIMIT 1', + [$message_id] + ); if (count($message) > 0 && $message[0]['SUID'] == $user['UID']) { - sql_query("DELETE FROM `Messages` WHERE `id`='" . sql_escape($message_id) . "' LIMIT 1"); + DB::delete('DELETE FROM `Messages` WHERE `id`=? LIMIT 1', [$message_id]); redirect(page_link_to('user_messages')); } else { return error(_('No Message found.'), true); @@ -142,7 +164,7 @@ function user_messages() break; case 'send': - if (Message_send($_REQUEST['to'], $_REQUEST['text']) === true) { + if (Message_send($_REQUEST['to'], $_REQUEST['text'])) { redirect(page_link_to('user_messages')); } else { return error(_('Transmitting was terminated with an Error.'), true); diff --git a/includes/pages/user_myshifts.php b/includes/pages/user_myshifts.php index 2079c789..76b79032 100644 --- a/includes/pages/user_myshifts.php +++ b/includes/pages/user_myshifts.php @@ -1,5 +1,7 @@ <?php +use Engelsystem\Database\DB; + /** * @return string */ @@ -22,14 +24,15 @@ function user_myshifts() isset($_REQUEST['id']) && in_array('user_shifts_admin', $privileges) && preg_match('/^[0-9]{1,}$/', $_REQUEST['id']) - && sql_num_query("SELECT * FROM `User` WHERE `UID`='" . sql_escape($_REQUEST['id']) . "'") > 0 + && count(DB::select('SELECT `UID` FROM `User` WHERE `UID`=?', [$_REQUEST['id']])) > 0 ) { $user_id = $_REQUEST['id']; } else { $user_id = $user['UID']; } - list($shifts_user) = sql_select("SELECT * FROM `User` WHERE `UID`='" . sql_escape($user_id) . "' LIMIT 1"); + $shifts_user = DB::select('SELECT * FROM `User` WHERE `UID`=? LIMIT 1', [$user_id]); + $shifts_user = array_shift($shifts_user); if (isset($_REQUEST['reset'])) { if ($_REQUEST['reset'] == 'ack') { @@ -46,24 +49,32 @@ function user_myshifts() ]); } elseif (isset($_REQUEST['edit']) && preg_match('/^[0-9]*$/', $_REQUEST['edit'])) { $user_id = $_REQUEST['edit']; - $shift = sql_select("SELECT - `ShiftEntry`.`freeloaded`, - `ShiftEntry`.`freeload_comment`, - `ShiftEntry`.`Comment`, - `ShiftEntry`.`UID`, - `ShiftTypes`.`name`, - `Shifts`.*, - `Room`.`Name`, - `AngelTypes`.`name` AS `angel_type` - FROM `ShiftEntry` - JOIN `AngelTypes` ON (`ShiftEntry`.`TID` = `AngelTypes`.`id`) - JOIN `Shifts` ON (`ShiftEntry`.`SID` = `Shifts`.`SID`) - JOIN `ShiftTypes` ON (`ShiftTypes`.`id` = `Shifts`.`shifttype_id`) - JOIN `Room` ON (`Shifts`.`RID` = `Room`.`RID`) - WHERE `ShiftEntry`.`id`='" . sql_escape($user_id) . "' - AND `UID`='" . sql_escape($shifts_user['UID']) . "' LIMIT 1"); + $shift = DB::select(' + SELECT + `ShiftEntry`.`freeloaded`, + `ShiftEntry`.`freeload_comment`, + `ShiftEntry`.`Comment`, + `ShiftEntry`.`UID`, + `ShiftTypes`.`name`, + `Shifts`.*, + `Room`.`Name`, + `AngelTypes`.`name` AS `angel_type` + FROM `ShiftEntry` + JOIN `AngelTypes` ON (`ShiftEntry`.`TID` = `AngelTypes`.`id`) + JOIN `Shifts` ON (`ShiftEntry`.`SID` = `Shifts`.`SID`) + JOIN `ShiftTypes` ON (`ShiftTypes`.`id` = `Shifts`.`shifttype_id`) + JOIN `Room` ON (`Shifts`.`RID` = `Room`.`RID`) + WHERE `ShiftEntry`.`id`=? + AND `UID`=? + LIMIT 1 + ', + [ + $user_id, + $shifts_user['UID'], + ] + ); if (count($shift) > 0) { - $shift = $shift[0]; + $shift = array_shift($shift); $freeloaded = $shift['freeloaded']; $freeload_comment = $shift['freeload_comment']; @@ -120,13 +131,19 @@ function user_myshifts() } } elseif (isset($_REQUEST['cancel']) && preg_match('/^[0-9]*$/', $_REQUEST['cancel'])) { $user_id = $_REQUEST['cancel']; - $shift = sql_select(" - SELECT * - FROM `Shifts` - INNER JOIN `ShiftEntry` USING (`SID`) - WHERE `ShiftEntry`.`id`='" . sql_escape($user_id) . "' AND `UID`='" . sql_escape($shifts_user['UID']) . "'"); + $shift = DB::select(' + SELECT * + FROM `Shifts` + INNER JOIN `ShiftEntry` USING (`SID`) + WHERE `ShiftEntry`.`id`=? AND `UID`=? + ', + [ + $user_id, + $shifts_user['UID'], + ] + ); if (count($shift) > 0) { - $shift = $shift[0]; + $shift = array_shift($shift); if (($shift['start'] > time() + $last_unsubscribe * 3600) || in_array('user_shifts_admin', $privileges)) { $result = ShiftEntry_delete($user_id); if ($result === false) { diff --git a/includes/pages/user_news.php b/includes/pages/user_news.php index ceed75f2..3828e293 100644 --- a/includes/pages/user_news.php +++ b/includes/pages/user_news.php @@ -1,5 +1,7 @@ <?php +use Engelsystem\Database\DB; + /** * @return string */ @@ -39,18 +41,20 @@ function user_meetings() $page = 0; } - $news = sql_select(" + $news = DB::select(sprintf(' SELECT * FROM `News` WHERE `Treffen`=1 ORDER BY `Datum`DESC - LIMIT " . sql_escape($page * $display_news) . ", " . sql_escape($display_news) - ); + LIMIT %u, %u', + $page * $display_news, + $display_news + )); foreach ($news as $entry) { $html .= display_news($entry); } - $dis_rows = ceil(sql_num_query('SELECT * FROM `News`') / $display_news); + $dis_rows = ceil(count(DB::select('SELECT `ID` FROM `News`')) / $display_news); $html .= '<div class="text-center">' . '<ul class="pagination">'; for ($i = 0; $i < $dis_rows; $i++) { if (isset($_REQUEST['page']) && $i == $_REQUEST['page']) { @@ -98,7 +102,7 @@ function display_news($news) . '<span class="glyphicon glyphicon-comment"></span> ' . _('Comments') . ' »</a> ' . '<span class="badge">' - . sql_num_query("SELECT * FROM `NewsComments` WHERE `Refid`='" . sql_escape($news['ID']) . "'") + . count(DB::select('SELECT `ID` FROM `NewsComments` WHERE `Refid`=?', [$news['ID']])) . '</span>'; } $html .= '</div>'; @@ -117,28 +121,34 @@ function user_news_comments() if ( isset($_REQUEST['nid']) && preg_match('/^[0-9]{1,}$/', $_REQUEST['nid']) - && sql_num_query("SELECT * FROM `News` WHERE `ID`='" . sql_escape($_REQUEST['nid']) . "' LIMIT 1") > 0 + && count(DB::select('SELECT `ID` FROM `News` WHERE `ID`=? LIMIT 1', [$_REQUEST['nid']])) > 0 ) { $nid = $_REQUEST['nid']; - list($news) = sql_select("SELECT * FROM `News` WHERE `ID`='" . sql_escape($nid) . "' LIMIT 1"); + $news = DB::select('SELECT * FROM `News` WHERE `ID`=? LIMIT 1', [$nid]); + $news = array_shift($news); if (isset($_REQUEST['text'])) { $text = preg_replace("/([^\p{L}\p{P}\p{Z}\p{N}\n]{1,})/ui", '', strip_tags($_REQUEST['text'])); - sql_query(" - INSERT INTO `NewsComments` (`Refid`, `Datum`, `Text`, `UID`) - VALUES ( - '" . sql_escape($nid) . "', - '" . date("Y-m-d H:i:s") . "', - '" . sql_escape($text) . "', - '" . sql_escape($user["UID"]) . "' - ) - "); + DB::insert(' + INSERT INTO `NewsComments` (`Refid`, `Datum`, `Text`, `UID`) + VALUES (?, ?, ?, ?) + ', + [ + $nid, + date("Y-m-d H:i:s"), + $text, + $user["UID"], + ] + ); engelsystem_log('Created news_comment: ' . $text); $html .= success(_('Entry saved.'), true); } $html .= display_news($news); - $comments = sql_select("SELECT * FROM `NewsComments` WHERE `Refid`='" . sql_escape($nid) . "' ORDER BY 'ID'"); + $comments = DB::select( + 'SELECT * FROM `NewsComments` WHERE `Refid`=? ORDER BY \'ID\'', + [$nid] + ); foreach ($comments as $comment) { $user_source = User($comment['UID']); @@ -176,16 +186,18 @@ function user_news() if (!isset($_POST['treffen']) || !in_array('admin_news', $privileges)) { $_POST['treffen'] = 0; } - sql_query(" + DB::insert(' INSERT INTO `News` (`Datum`, `Betreff`, `Text`, `UID`, `Treffen`) - VALUES ( - '" . sql_escape(time()) . "', - '" . sql_escape($_POST["betreff"]) . "', - '" . sql_escape($_POST["text"]) . "', - '" . sql_escape($user['UID']) . "', - '" . sql_escape($_POST["treffen"]) . "' - ) - "); + VALUES (?, ?, ?, ?, ?) + ', + [ + time(), + $_POST['betreff'], + $_POST['text'], + $user['UID'], + $_POST['treffen'], + ] + ); engelsystem_log('Created news: ' . $_POST['betreff'] . ', treffen: ' . $_POST['treffen']); success(_('Entry saved.')); redirect(page_link_to('news')); @@ -197,17 +209,20 @@ function user_news() $page = 0; } - $news = sql_select(" - SELECT * - FROM `News` - ORDER BY `Datum` - DESC LIMIT " . sql_escape($page * $display_news) . ", " . sql_escape($display_news) - ); + $news = DB::select(sprintf(' + SELECT * + FROM `News` + ORDER BY `Datum` + DESC LIMIT %u, %u + ', + $page * $display_news, + $display_news + )); foreach ($news as $entry) { $html .= display_news($entry); } - $dis_rows = ceil(sql_num_query('SELECT * FROM `News`') / $display_news); + $dis_rows = ceil(count(DB::select('SELECT `ID` FROM `News`')) / $display_news); $html .= '<div class="text-center">' . '<ul class="pagination">'; for ($i = 0; $i < $dis_rows; $i++) { if (isset($_REQUEST['page']) && $i == $_REQUEST['page']) { diff --git a/includes/pages/user_questions.php b/includes/pages/user_questions.php index b8ebe92d..04ae8914 100644 --- a/includes/pages/user_questions.php +++ b/includes/pages/user_questions.php @@ -1,5 +1,7 @@ <?php +use Engelsystem\Database\DB; + /** * @return string */ @@ -16,12 +18,14 @@ function user_questions() global $user; if (!isset($_REQUEST['action'])) { - $open_questions = sql_select( - "SELECT * FROM `Questions` WHERE `AID` IS NULL AND `UID`='" . sql_escape($user['UID']) . "'" + $open_questions = DB::select( + 'SELECT * FROM `Questions` WHERE `AID` IS NULL AND `UID`=?', + [$user['UID']] ); - $answered_questions = sql_select( - "SELECT * FROM `Questions` WHERE NOT `AID` IS NULL AND `UID`='" . sql_escape($user['UID']) . "'" + $answered_questions = DB::select( + 'SELECT * FROM `Questions` WHERE NOT `AID` IS NULL AND `UID`=?', + [$user['UID']] ); foreach ($answered_questions as &$question) { $answer_user_source = User($question['AID']); @@ -34,11 +38,13 @@ function user_questions() case 'ask': $question = strip_request_item_nl('question'); if ($question != '') { - $result = sql_query(" - INSERT INTO `Questions` - SET `UID`='" . sql_escape($user['UID']) . "', `Question`='" . sql_escape($question) . "' - "); - if ($result === false) { + $result = DB::insert(' + INSERT INTO `Questions` (`UID`, `Question`) + VALUES (?, ?) + ', + [$user['UID'], $question] + ); + if (!$result) { engelsystem_error(_('Unable to save question.')); } success(_('You question was saved.')); @@ -56,9 +62,15 @@ function user_questions() return error(_('Incomplete call, missing Question ID.'), true); } - $question = sql_select("SELECT * FROM `Questions` WHERE `QID`='" . sql_escape($question_id) . "' LIMIT 1"); + $question = DB::select( + 'SELECT `UID` FROM `Questions` WHERE `QID`=? LIMIT 1', + [$question_id] + ); if (count($question) > 0 && $question[0]['UID'] == $user['UID']) { - sql_query("DELETE FROM `Questions` WHERE `QID`='" . sql_escape($question_id) . "' LIMIT 1"); + DB::delete( + 'DELETE FROM `Questions` WHERE `QID`=? LIMIT 1', + [$question_id] + ); redirect(page_link_to('user_questions')); } else { return page_with_title(questions_title(), [ diff --git a/includes/pages/user_settings.php b/includes/pages/user_settings.php index b848ff5f..5d4ba368 100644 --- a/includes/pages/user_settings.php +++ b/includes/pages/user_settings.php @@ -1,5 +1,7 @@ <?php +use Engelsystem\Database\DB; + /** * @return string */ @@ -131,11 +133,16 @@ function user_settings_theme($user_source, $themes) } if ($valid) { - sql_query(" + DB::update(' UPDATE `User` - SET `color`='" . sql_escape($user_source['color']) . "' - WHERE `UID`='" . sql_escape($user_source['UID']) . "' - "); + SET `color`=? + WHERE `UID`=? + ', + [ + $user_source['color'], + $user_source['UID'], + ] + ); success(_('Theme changed.')); redirect(page_link_to('user_settings')); @@ -162,11 +169,16 @@ function user_settings_locale($user_source, $locales) } if ($valid) { - sql_query(" + DB::update(' UPDATE `User` - SET `Sprache`='" . sql_escape($user_source['Sprache']) . "' - WHERE `UID`='" . sql_escape($user_source['UID']) . "' - "); + SET `Sprache`=? + WHERE `UID`=? + ', + [ + $user_source['Sprache'], + $user_source['UID'], + ] + ); $_SESSION['locale'] = $user_source['Sprache']; success('Language changed.'); diff --git a/includes/pages/user_shifts.php b/includes/pages/user_shifts.php index bfb33f12..16af0197 100644 --- a/includes/pages/user_shifts.php +++ b/includes/pages/user_shifts.php @@ -1,4 +1,6 @@ <?php + +use Engelsystem\Database\DB; use Engelsystem\ShiftsFilter; /** @@ -70,12 +72,9 @@ function update_ShiftsFilter_timerange(ShiftsFilter $shiftsFilter, $days) /** * Update given ShiftsFilter with filter params from user input * - * @param ShiftsFilter $shiftsFilter - * The shifts filter to update from request data - * @param boolean $user_shifts_admin - * Has the user user_shift_admin privilege? - * @param string[] $days - * An array of available filter days + * @param ShiftsFilter $shiftsFilter The shifts filter to update from request data + * @param boolean $user_shifts_admin Has the user user_shift_admin privilege? + * @param string[] $days An array of available filter days */ function update_ShiftsFilter(ShiftsFilter $shiftsFilter, $user_shifts_admin, $days) { @@ -91,8 +90,10 @@ function update_ShiftsFilter(ShiftsFilter $shiftsFilter, $user_shifts_admin, $da */ function load_rooms() { - $rooms = sql_select('SELECT `RID` AS `id`, `Name` AS `name` FROM `Room` WHERE `show`=\'Y\' ORDER BY `Name`'); - if (!$rooms || count($rooms) == 0) { + $rooms = DB::select( + 'SELECT `RID` AS `id`, `Name` AS `name` FROM `Room` WHERE `show`=\'Y\' ORDER BY `Name`' + ); + if (empty($rooms)) { error(_('The administration has not configured any rooms yet.')); redirect('?'); } @@ -104,12 +105,14 @@ function load_rooms() */ function load_days() { - $days = sql_select_single_col(' + $days = DB::select(' SELECT DISTINCT DATE(FROM_UNIXTIME(`start`)) AS `id`, DATE(FROM_UNIXTIME(`start`)) AS `name` FROM `Shifts` ORDER BY `start` '); - if (count($days) == 0) { + $days = array_map('array_shift', $days); + + if (empty($days)) { error(_('The administration has not configured any shifts yet.')); redirect('?'); } @@ -123,31 +126,35 @@ function load_types() { global $user; - if (sql_num_query('SELECT `id`, `name` FROM `AngelTypes` WHERE `restricted` = 0') == 0) { + if (!count(DB::select('SELECT `id`, `name` FROM `AngelTypes` WHERE `restricted` = 0'))) { error(_('The administration has not configured any angeltypes yet - or you are not subscribed to any angeltype.')); redirect('?'); } - $types = sql_select(" - SELECT - `AngelTypes`.`id`, - `AngelTypes`.`name`, - ( - `AngelTypes`.`restricted`=0 - OR ( - NOT `UserAngelTypes`.`confirm_user_id` IS NULL - OR `UserAngelTypes`.`id` IS NULL + $types = DB::select(' + SELECT + `AngelTypes`.`id`, + `AngelTypes`.`name`, + ( + `AngelTypes`.`restricted`=0 + OR ( + NOT `UserAngelTypes`.`confirm_user_id` IS NULL + OR `UserAngelTypes`.`id` IS NULL + ) + ) AS `enabled` + FROM `AngelTypes` + LEFT JOIN `UserAngelTypes` + ON ( + `UserAngelTypes`.`angeltype_id`=`AngelTypes`.`id` + AND `UserAngelTypes`.`user_id`=? ) - ) AS `enabled` - FROM `AngelTypes` - LEFT JOIN `UserAngelTypes` - ON ( - `UserAngelTypes`.`angeltype_id`=`AngelTypes`.`id` - AND `UserAngelTypes`.`user_id`='" . sql_escape($user['UID']) . "' - ) - ORDER BY `AngelTypes`.`name` - "); + ORDER BY `AngelTypes`.`name` + ', + [ + $user['UID'], + ] + ); if (empty($types)) { - return sql_select('SELECT `id`, `name` FROM `AngelTypes` WHERE `restricted` = 0'); + return DB::select('SELECT `id`, `name` FROM `AngelTypes` WHERE `restricted` = 0'); } return $types; } diff --git a/includes/sys_auth.php b/includes/sys_auth.php index a478226c..083c1b8d 100644 --- a/includes/sys_auth.php +++ b/includes/sys_auth.php @@ -1,5 +1,7 @@ <?php +use Engelsystem\Database\DB; + /** * Testet ob ein User eingeloggt ist und lädt die entsprechenden Privilegien */ @@ -9,16 +11,19 @@ function load_auth() $user = null; if (isset($_SESSION['uid'])) { - $user = sql_select("SELECT * FROM `User` WHERE `UID`='" . sql_escape($_SESSION['uid']) . "' LIMIT 1"); + $user = DB::select('SELECT * FROM `User` WHERE `UID`=? LIMIT 1', [$_SESSION['uid']]); if (count($user) > 0) { // User ist eingeloggt, Datensatz zur Verfügung stellen und Timestamp updaten - list($user) = $user; - sql_query(" + $user = array_shift($user); + DB::update(' UPDATE `User` - SET " . "`lastLogIn` = '" . time() . "'" . " - WHERE `UID` = '" . sql_escape($_SESSION['uid']) . "' + SET `lastLogIn` = ? + WHERE `UID` = ? LIMIT 1 - "); + ', [ + time(), + $_SESSION['uid'], + ]); $privileges = privileges_for_user($user['UID']); return; } @@ -50,19 +55,24 @@ function generate_salt($length = 16) * * @param int $uid * @param string $password - * @return mysqli_result + * @return bool */ function set_password($uid, $password) { global $crypt_alg; - $result = sql_query(" + $result = DB::update(' UPDATE `User` - SET `Passwort` = '" . sql_escape(crypt($password, $crypt_alg . '$' . generate_salt(16) . '$')) . "', + SET `Passwort` = ?, `password_recovery_token`=NULL - WHERE `UID` = " . intval($uid) . " + WHERE `UID` = ? LIMIT 1 - "); - if ($result === false) { + ', + [ + crypt($password, $crypt_alg . '$' . generate_salt(16) . '$'), + $uid + ] + ); + if (DB::getStm()->errorCode() != '00000') { engelsystem_error('Unable to update password.'); } return $result; @@ -93,13 +103,19 @@ function verify_password($password, $salt, $uid = null) // this password is stored in another format than we want it to be. // let's update it! // we duplicate the query from the above set_password() function to have the extra safety of checking the old hash - sql_query(" - UPDATE `User` - SET `Passwort` = '" . sql_escape(crypt($password, $crypt_alg . '$' . generate_salt() . '$')) . "' - WHERE `UID` = " . intval($uid) . " - AND `Passwort` = '" . sql_escape($salt) . "' - LIMIT 1 - "); + DB::update(' + UPDATE `User` + SET `Passwort` = ? + WHERE `UID` = ? + AND `Passwort` = ? + LIMIT 1 + ', + [ + crypt($password, $crypt_alg . '$' . generate_salt() . '$'), + $uid, + $salt, + ] + ); } return $correct; } @@ -111,16 +127,16 @@ function verify_password($password, $salt, $uid = null) function privileges_for_user($user_id) { $privileges = []; - $user_privs = sql_select(" + $user_privileges = DB::select(' SELECT `Privileges`.`name` FROM `User` JOIN `UserGroups` ON (`User`.`UID` = `UserGroups`.`uid`) JOIN `GroupPrivileges` ON (`UserGroups`.`group_id` = `GroupPrivileges`.`group_id`) JOIN `Privileges` ON (`GroupPrivileges`.`privilege_id` = `Privileges`.`id`) - WHERE `User`.`UID`='" . sql_escape($user_id) . "' - "); - foreach ($user_privs as $user_priv) { - $privileges[] = $user_priv['name']; + WHERE `User`.`UID`=? + ', [$user_id]); + foreach ($user_privileges as $user_privilege) { + $privileges[] = $user_privilege['name']; } return $privileges; } @@ -132,14 +148,14 @@ function privileges_for_user($user_id) function privileges_for_group($group_id) { $privileges = []; - $groups_privs = sql_select(" - SELECT * + $groups_privileges = DB::select(' + SELECT `name` FROM `GroupPrivileges` JOIN `Privileges` ON (`GroupPrivileges`.`privilege_id` = `Privileges`.`id`) - WHERE `group_id`='" . sql_escape($group_id) . "' - "); - foreach ($groups_privs as $guest_priv) { - $privileges[] = $guest_priv['name']; + WHERE `group_id`=? + ', [$group_id]); + foreach ($groups_privileges as $guest_privilege) { + $privileges[] = $guest_privilege['name']; } return $privileges; } diff --git a/includes/sys_log.php b/includes/sys_log.php index b253d6ad..c4ef890e 100644 --- a/includes/sys_log.php +++ b/includes/sys_log.php @@ -16,25 +16,3 @@ function engelsystem_log($message) } LogEntry_create($nick, $message); } - -/** - * Generates a PHP Stacktrace. - * - * @return string - */ -function debug_string_backtrace() -{ - ob_start(); - debug_print_backtrace(); - $trace = ob_get_contents(); - ob_end_clean(); - - // Remove first item from backtrace as it's this function which - // is redundant. - $trace = preg_replace('/^#0\s+' . __FUNCTION__ . "[^\n]*\n/", '', $trace, 1); - - // Renumber backtrace items. - // $trace = preg_replace('/^#(\d+)/me', '\'#\' . ($1 - 1)', $trace); - - return $trace; -} diff --git a/includes/sys_page.php b/includes/sys_page.php index 7bc2b9cb..fd03e291 100644 --- a/includes/sys_page.php +++ b/includes/sys_page.php @@ -61,7 +61,7 @@ function redirect($url) * * @param String $output String to display */ -function raw_output($output) +function raw_output($output = '') { echo $output; die(); @@ -78,11 +78,11 @@ function raw_output($output) */ function select_array($data, $key_name, $value_name) { - $ret = []; + $return = []; foreach ($data as $value) { - $ret[$value[$key_name]] = $value[$value_name]; + $return[$value[$key_name]] = $value[$value_name]; } - return $ret; + return $return; } /** diff --git a/includes/view/User_view.php b/includes/view/User_view.php index ca32b80e..832569a6 100644 --- a/includes/view/User_view.php +++ b/includes/view/User_view.php @@ -282,26 +282,25 @@ function Users_table_header_link($column, $label, $order_by) function User_shift_state_render($user) { $upcoming_shifts = ShiftEntries_upcoming_for_user($user); - if ($upcoming_shifts === false) { - return false; - } - if (count($upcoming_shifts) == 0) { + if (empty($upcoming_shifts)) { return '<span class="text-success">' . _('Free') . '</span>'; } - if ($upcoming_shifts[0]['start'] > time()) { - if ($upcoming_shifts[0]['start'] - time() > 3600) { - return '<span class="text-success moment-countdown" data-timestamp="' . $upcoming_shifts[0]['start'] . '">' . _('Next shift %c') . '</span>'; + $nextShift = array_shift($upcoming_shifts); + + if ($nextShift['start'] > time()) { + if ($nextShift['start'] - time() > 3600) { + return '<span class="text-success moment-countdown" data-timestamp="' . $nextShift['start'] . '">' . _('Next shift %c') . '</span>'; } - return '<span class="text-warning moment-countdown" data-timestamp="' . $upcoming_shifts[0]['start'] . '">' . _('Next shift %c') . '</span>'; + return '<span class="text-warning moment-countdown" data-timestamp="' . $nextShift['start'] . '">' . _('Next shift %c') . '</span>'; } - $halfway = ($upcoming_shifts[0]['start'] + $upcoming_shifts[0]['end']) / 2; + $halfway = ($nextShift['start'] + $nextShift['end']) / 2; if (time() < $halfway) { - return '<span class="text-danger moment-countdown" data-timestamp="' . $upcoming_shifts[0]['start'] . '">' . _('Shift starts %c') . '</span>'; + return '<span class="text-danger moment-countdown" data-timestamp="' . $nextShift['start'] . '">' . _('Shift starts %c') . '</span>'; } - return '<span class="text-danger moment-countdown" data-timestamp="' . $upcoming_shifts[0]['end'] . '">' . _('Shift ends %c') . '</span>'; + return '<span class="text-danger moment-countdown" data-timestamp="' . $nextShift['end'] . '">' . _('Shift ends %c') . '</span>'; } /** diff --git a/src/Database/Db.php b/src/Database/Db.php new file mode 100644 index 00000000..c1efa058 --- /dev/null +++ b/src/Database/Db.php @@ -0,0 +1,170 @@ +<?php + +namespace Engelsystem\Database; + +use PDO; +use PDOException; +use PDOStatement; + +class Db +{ + /** @var PDO */ + protected static $db; + + /** @var PDOStatement */ + protected static $stm = null; + + /** @var bool */ + protected static $lastStatus = true; + + /** + * Connect to database + * + * @param string $dsn + * @param string $username + * @param string $password + * @param array $options + * @return bool + */ + public static function connect($dsn, $username = null, $password = null, $options = []) + { + try { + self::$db = new PDO($dsn, $username, $password, $options); + } catch (PDOException $e) { + return false; + } + + return true; + } + + /** + * Run a prepared query + * + * @param string $query + * @param array $bindings + * @return PDOStatement + */ + public static function query($query, array $bindings = []) + { + self::$stm = self::$db->prepare($query); + self::$lastStatus = self::$stm->execute($bindings); + + return self::$stm; + } + + /** + * Run a sql query + * + * @param string $query + * @return bool + */ + public static function unprepared($query) + { + self::$stm = self::$db->query($query); + self::$lastStatus = (self::$stm instanceof PDOStatement); + + return self::$lastStatus; + } + + /** + * Run a select query + * + * @param string $query + * @param array $bindings + * @return array + */ + public static function select($query, array $bindings = []) + { + self::query($query, $bindings); + + return self::$stm->fetchAll(PDO::FETCH_ASSOC); + } + + /** + * Run a insert query + * + * @param string $query + * @param array $bindings + * @return bool + */ + public static function insert($query, array $bindings = []) + { + self::query($query, $bindings); + + return self::$lastStatus; + } + + /** + * Run a update query + * + * @param string $query + * @param array $bindings + * @return int|null + */ + public static function update($query, array $bindings = []) + { + self::query($query, $bindings); + + return (self::$lastStatus ? self::$stm->rowCount() : null); + } + + /** + * Run a delete query + * + * @param string $query + * @param array $bindings + * @return int|null + */ + public static function delete($query, array $bindings = []) + { + self::query($query, $bindings); + + return (self::$lastStatus ? self::$stm->rowCount() : null); + } + + /** + * Run a single statement + * + * @param string $query + * @param array $bindings + * @return bool + */ + public static function statement($query, array $bindings = []) + { + self::query($query, $bindings); + + return self::$lastStatus; + } + + /** + * Returns the last error + * + * @return array + */ + public static function getError() + { + if (!self::$stm instanceof PDOStatement) { + return [-1, null, null]; + } + + return self::$stm->errorInfo(); + } + + /** + * Get the PDO instance + * + * @return PDO + */ + public static function getPdo() + { + return self::$db; + } + + /** + * @return PDOStatement|false|null + */ + public static function getStm() + { + return self::$stm; + } +} |