Changeset: c504cb7190d4 https://sourceforge.net/p/mrbs/hg-code/ci/c504cb7190d4905c444a692cf67fa17e82c26769 Author: John Beranek <jbera...@users.sourceforge.net> Date: Sat Sep 17 18:31:25 2016 +0100 Log message:
Paramaterised more SQL statements diffstat: web/del.php | 14 +++++++------- web/edit_area_room.php | 4 ++-- web/edit_entry.php | 4 ++-- web/edit_entry_handler.php | 2 +- web/functions.inc | 22 +++++++++++----------- web/functions_mail.inc | 8 ++++---- web/mrbs_sql.inc | 22 +++++++++++++--------- web/upgrade.inc | 7 +++---- web/view_entry.php | 17 ++++++++++------- 9 files changed, 53 insertions(+), 47 deletions(-) diffs (truncated from 357 to 300 lines): diff -r d2b0254ffe16 -r c504cb7190d4 web/del.php --- a/web/del.php Sat Sep 17 17:51:22 2016 +0100 +++ b/web/del.php Sat Sep 17 18:31:25 2016 +0100 @@ -24,11 +24,11 @@ // They have confirmed it already, so go blast! sql_begin(); // First take out all appointments for this room - sql_command("delete from $tbl_entry where room_id=$room"); - sql_command("delete from $tbl_repeat where room_id=$room"); + sql_command("DELETE FROM $tbl_entry WHERE room_id=?", array($room)); + sql_command("DELETE FROM $tbl_repeat WHERE room_id=?", array($room)); // Now take out the room itself - sql_command("delete from $tbl_room where id=$room"); + sql_command("DELETE FROM $tbl_room WHERE id=?",array($room)); sql_commit(); // Go back to the admin page @@ -41,8 +41,8 @@ // We tell them how bad what they're about to do is // Find out how many appointments would be deleted - $sql = "select name, start_time, end_time from $tbl_entry where room_id=$room"; - $res = sql_query($sql); + $sql = "SELECT name, start_time, end_time FROM $tbl_entry WHERE room_id=?"; + $res = sql_query($sql, array($room)); if (! $res) { trigger_error(sql_error(), E_USER_WARNING); @@ -81,11 +81,11 @@ { // We are only going to let them delete an area if there are // no rooms. its easier - $n = sql_query1("select count(*) from $tbl_room where area_id=$area"); + $n = sql_query1("SELECT COUNT(*) FROM $tbl_room WHERE area_id=?", array($area)); if ($n == 0) { // OK, nothing there, lets blast it away - sql_command("delete from $tbl_area where id=$area"); + sql_command("DELETE FROM $tbl_area WHERE id=?", array($area)); // Redirect back to the admin page header("Location: admin.php"); diff -r d2b0254ffe16 -r c504cb7190d4 web/edit_area_room.php --- a/web/edit_area_room.php Sat Sep 17 17:51:22 2016 +0100 +++ b/web/edit_area_room.php Sat Sep 17 18:31:25 2016 +0100 @@ -536,8 +536,8 @@ && sql_query1("SELECT COUNT(*) FROM $tbl_room WHERE" . sql_syntax_casesensitive_equals("room_name", $room_name) . " - AND area_id=$new_area - LIMIT 1", array($room_name)) > 0) + AND area_id=? + LIMIT 1", array($room_name, $new_area)) > 0) { $valid_room_name = FALSE; } diff -r d2b0254ffe16 -r c504cb7190d4 web/edit_entry.php --- a/web/edit_entry.php Sat Sep 17 17:51:22 2016 +0100 +++ b/web/edit_entry.php Sat Sep 17 18:31:25 2016 +0100 @@ -816,10 +816,10 @@ $sql = "SELECT rep_type, start_time, end_time, end_date, rep_opt, rep_num_weeks, month_absolute, month_relative FROM $tbl_repeat - WHERE id=$rep_id + WHERE id=? LIMIT 1"; - $res = sql_query($sql); + $res = sql_query($sql, array($rep_id)); if (! $res) { trigger_error(sql_error(), E_USER_WARNING); diff -r d2b0254ffe16 -r c504cb7190d4 web/edit_entry_handler.php --- a/web/edit_entry_handler.php Sat Sep 17 17:51:22 2016 +0100 +++ b/web/edit_entry_handler.php Sat Sep 17 18:31:25 2016 +0100 @@ -414,7 +414,7 @@ { // Editing an existing booking: get the room_id from the database (you can't // get it from $rooms because they are the new rooms) - $target_room = sql_query1("SELECT room_id FROM $tbl_entry WHERE id=$id LIMIT 1"); + $target_room = sql_query1("SELECT room_id FROM $tbl_entry WHERE id=? LIMIT 1", array($id)); if ($target_room < 0) { fatal_error(FALSE, get_vocab("fatal_db_error")); diff -r d2b0254ffe16 -r c504cb7190d4 web/functions.inc --- a/web/functions.inc Sat Sep 17 17:51:22 2016 +0100 +++ b/web/functions.inc Sat Sep 17 18:31:25 2016 +0100 @@ -1668,11 +1668,11 @@ { $area = sql_query1("SELECT area_id FROM $tbl_room R, $tbl_area A - WHERE R.id=$default_room + WHERE R.id=? AND R.area_id = A.id AND R.disabled = 0 AND A.disabled = 0 - LIMIT 1"); + LIMIT 1", array($default_room)); if ($area >= 0) { return $area; @@ -1700,9 +1700,9 @@ $room = sql_query1("SELECT id FROM $tbl_room WHERE id=$default_room - AND area_id=$area + AND area_id=? AND disabled=0 - LIMIT 1"); + LIMIT 1", array($area)); if ($room >= 0) { return $room; @@ -1711,10 +1711,10 @@ // Otherwise just return the first room (in sortkey order) in the area $room = sql_query1("SELECT id FROM $tbl_room - WHERE area_id=$area + WHERE area_id=? AND disabled=0 ORDER BY sort_key - LIMIT 1"); + LIMIT 1", array($area)); return ($room < 0 ? 0 : $room); } @@ -1722,7 +1722,7 @@ function get_area($room) { global $tbl_room; - $area = sql_query1("SELECT area_id FROM $tbl_room WHERE id=$room LIMIT 1"); + $area = sql_query1("SELECT area_id FROM $tbl_room WHERE id=? LIMIT 1", array($room)); return ($area < 0 ? 0 : $area); } @@ -1813,10 +1813,10 @@ $sql = "SELECT " . implode(',', $columns) . " FROM $tbl_area - WHERE id=$area + WHERE id=? LIMIT 1"; - $res = sql_query($sql); + $res = sql_query($sql, array($area)); if (!$res || (sql_count($res) == 0)) { // We still need to set the timezone even if the query didn't @@ -2236,12 +2236,12 @@ $out_html = ''; $sql = "SELECT R.id, R.room_name, R.description FROM $tbl_room R, $tbl_area A - WHERE R.area_id=$area + WHERE R.area_id=? AND R.area_id=A.id AND R.disabled=0 AND A.disabled=0 ORDER BY R.sort_key"; - $res = sql_query($sql); + $res = sql_query($sql, array($area)); // Only show the rooms if there's more than one of them, otherwise // there's no point if ($res && (sql_count($res) > 1)) diff -r d2b0254ffe16 -r c504cb7190d4 web/functions_mail.inc --- a/web/functions_mail.inc Sat Sep 17 17:51:22 2016 +0100 +++ b/web/functions_mail.inc Sat Sep 17 18:31:25 2016 +0100 @@ -235,11 +235,11 @@ // ...use the repeat table $sql .= ", $tbl_repeat rep "; } - $sql .= "WHERE ${id_table}.id=$id + $sql .= "WHERE ${id_table}.id=? AND r.id=${id_table}.room_id AND a.id=r.area_id LIMIT 1"; - $email = sql_query1($sql); + $email = sql_query1($sql, array($id)); return ($email == -1) ? '' : $email; } @@ -262,10 +262,10 @@ // ...use the repeat table $sql .= ", $tbl_repeat rep "; } - $sql .= "WHERE ${id_table}.id=$id + $sql .= "WHERE ${id_table}.id=? AND r.id=${id_table}.room_id LIMIT 1"; - $email = sql_query1($sql); + $email = sql_query1($sql, array($id)); return ($email == -1) ? '' : $email; } diff -r d2b0254ffe16 -r c504cb7190d4 web/mrbs_sql.inc --- a/web/mrbs_sql.inc Sat Sep 17 17:51:22 2016 +0100 +++ b/web/mrbs_sql.inc Sat Sep 17 18:31:25 2016 +0100 @@ -28,8 +28,6 @@ get_area_settings(get_area($room_id)); - $sql_params = array(); - // Select any meetings which overlap for this room: $sql = "SELECT E.id, name, start_time, create_by, status, room_name FROM $tbl_entry E, $tbl_room R @@ -182,6 +180,7 @@ if (!isset($existing[$location][$interval_type][$interval_start])) { + $sql_params = array(); $sql = "SELECT COUNT(*) FROM $tbl_entry E, $tbl_room R WHERE E.start_time<$interval_end @@ -189,20 +188,25 @@ AND E.create_by=? AND E.room_id=R.id AND R.disabled=0"; + $sql_params[] = $booking['create_by']; + if ($only_area) { - $sql .= " AND R.area_id=$area_id"; + $sql .= " AND R.area_id=?"; + $sql_params[] = $area_id; } if ($ignore > 0) { - $sql .= " AND E.id <> $ignore"; + $sql .= " AND E.id <> ?"; + $sql_params[] = $ignore; } if ($repignore > 0) { - $sql .= " AND (E.repeat_id IS NULL OR E.repeat_id <> $repignore)"; + $sql .= " AND (E.repeat_id IS NULL OR E.repeat_id <> ?)"; + $sql_params[] = $repignore; } - $existing[$location][$interval_type][$interval_start] = sql_query1($sql, array($booking['create_by'])); + $existing[$location][$interval_type][$interval_start] = sql_query1($sql, $sql_params); if ($existing[$location][$interval_type][$interval_start] < 0) { fatal_error(FALSE, get_vocab("fatal_db_error")); @@ -1120,10 +1124,10 @@ if ($series) { $sql = "UPDATE $tbl_repeat - SET reminded=$now, + SET reminded=?, ical_sequence=ical_sequence+1 WHERE id=?"; - if (sql_command($sql, array($id)) >= 0) + if (sql_command($sql, array($now, $id)) >= 0) { $sql = "UPDATE $tbl_entry SET reminded=?, @@ -1231,7 +1235,7 @@ ical_sequence=ical_sequence+1 WHERE $condition"; // PostgreSQL does not support LIMIT with UPDATE - if (sql_command($sql) < 0) + if (sql_command($sql, $params) < 0) { trigger_error(sql_error(), E_USER_WARNING); fatal_error(FALSE, get_vocab("fatal_db_error")); diff -r d2b0254ffe16 -r c504cb7190d4 web/upgrade.inc --- a/web/upgrade.inc Sat Sep 17 17:51:22 2016 +0100 +++ b/web/upgrade.inc Sat Sep 17 18:31:25 2016 +0100 @@ -78,7 +78,7 @@ // an empty query) if (preg_match("/\S/", $query)) { - $res = sql_query($query, $upgrade_handle); + $res = sql_query($query, array(), $upgrade_handle); if ($res === FALSE) { // No need to localise, should hopefully never happen @@ -93,8 +93,8 @@ if ($ver > 1) { $variable_name = ($local) ? "local_db_version" : "db_version"; - $res = sql_command("UPDATE $tbl_variables SET variable_content = '$ver' ". - "WHERE variable_name = '$variable_name'"); + $res = sql_command("UPDATE $tbl_variables SET variable_content = ? ". + "WHERE variable_name = ?", array($ver, $variable_name)); if ($res == -1) { // No need to localise, should never happen @@ -112,4 +112,3 @@ } return TRUE; } - diff -r d2b0254ffe16 -r c504cb7190d4 web/view_entry.php --- a/web/view_entry.php Sat Sep 17 17:51:22 2016 +0100 +++ b/web/view_entry.php Sat Sep 17 18:31:25 2016 +0100 @@ -153,10 +153,10 @@ // as per the original series settings $sql = "SELECT id ------------------------------------------------------------------------------ _______________________________________________ Mrbs-commits mailing list Mrbs-commits@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/mrbs-commits