[opw-web] Switch to prepared statements
- From: Owen Taylor <otaylor src gnome org>
- To: commits-list gnome org
- Cc:
- Subject: [opw-web] Switch to prepared statements
- Date: Mon, 10 Mar 2014 04:31:47 +0000 (UTC)
commit 42a058a0092c799b5df9d9ffe1482a39f7565b0d
Author: Owen W. Taylor <otaylor fishsoup net>
Date: Sat Mar 8 18:41:59 2014 -0500
Switch to prepared statements
classes/class_cache.php | 37 +++++
classes/class_db.php | 47 +++++-
classes/class_user.php | 140 ++++++++++--------
cron.php | 8 +-
modules/mod_approve_mentors.php | 8 +-
modules/mod_manage_programs.php | 87 ++++++-----
modules/mod_notifications.php | 38 +++--
modules/mod_program_home.php | 37 +-----
modules/mod_timeline.php | 9 +-
modules/mod_user_ban.php | 20 ++--
modules/mod_view_participants.php | 1 -
modules/mod_view_programs.php | 2 +-
modules/mod_view_projects.php | 301 +++++++++++++++++--------------------
13 files changed, 386 insertions(+), 349 deletions(-)
---
diff --git a/classes/class_cache.php b/classes/class_cache.php
index 59c0bf8..f4da515 100644
--- a/classes/class_cache.php
+++ b/classes/class_cache.php
@@ -100,5 +100,42 @@ class cache
return false;
}
}
+
+ // Utility functions - really should go elsewhere
+ function get_program_data($id)
+ {
+ global $db;
+
+ $program_data = $this->get("program_{$id}", 'programs');
+
+ if (!$program_data)
+ {
+ $sql = "SELECT * FROM {$db->prefix}programs " .
+ "WHERE id = ?";
+ $program_data = $db->query($sql, $id, true);
+
+ $this->put("program_{$id}", $program_data, 'programs');
+ }
+
+ return $program_data ? $program_data : null;
+ }
+
+ function get_project_data($id)
+ {
+ global $db;
+
+ $project_data = $this->get("project_{$id}", 'projects');
+
+ if (!$project_data)
+ {
+ $sql = "SELECT * FROM {$db->prefix}projects " .
+ "WHERE id = ?";
+ $project_data = $db->query($sql, $id, true);
+
+ $this->put("project_{$id}", $project_data, 'projects');
+ }
+
+ return $project_data ? $project_data : null;
+ }
}
?>
diff --git a/classes/class_db.php b/classes/class_db.php
index c6c62fe..7cd334c 100644
--- a/classes/class_db.php
+++ b/classes/class_db.php
@@ -12,6 +12,12 @@ class db
var $prefix;
var $hits;
+ var $_TYPE_MAP = array(
+ 's' => PDO::PARAM_STR,
+ 'i' => PDO::PARAM_INT,
+ 'b' => PDO::PARAM_BOOL
+ );
+
// Constructor
function __construct()
{
@@ -41,8 +47,39 @@ class db
}
}
+ function _bind_params($sql, $statement, $params)
+ {
+ if (is_array($params)) {
+ if (array_key_exists(0, $params)) {
+ foreach ($params as $key => $value) {
+ $statement->bindValue($key + 1, $value);
+ }
+ } else {
+ preg_match_all('/:([A-Za-z_][A-Za-z_0-9]+)/', $sql, $matches);
+ $bound_parameters = array();
+ foreach ($matches[1] as $key) {
+ $bound_parameters[$key] = 1;
+ }
+
+ foreach ($params as $key => $value) {
+ if ($key[1] == ':') {
+ $type = $this->_TYPE_MAP[$key[0]];
+ $key = substr($key, 2);
+ } else {
+ $type = PDO::PARAM_STR;
+ }
+
+ if (array_key_exists($key, $bound_parameters))
+ $statement->bindValue($key, $value, $type);
+ }
+ }
+ } else if (!is_null($params)) {
+ $statement->bindValue(1, $params);
+ }
+ }
+
// Function to return a recordset
- function query($sql, $single = false)
+ function query($sql, $params = null, $single = false)
{
try
{
@@ -61,6 +98,7 @@ class db
// Execute the query
$statement = $this->dbh->prepare($sql);
+ $this->_bind_params($sql, $statement, $params);
$result = $statement->execute();
$this->_affected_rows = 0;
@@ -82,6 +120,7 @@ class db
else
{
$statement = $this->dbh->prepare($sql);
+ $this->_bind_params($sql, $statement, $params);
$result = $statement->execute();
// Some error occurred
@@ -118,12 +157,6 @@ class db
return $this->_affected_rows;
}
- // Function to escape a special chars string
- function escape(&$data)
- {
- $data = substr($this->dbh->quote($data), 1, -1);
- }
-
// Object destructor
function __destruct()
{
diff --git a/classes/class_user.php b/classes/class_user.php
index 60585b2..4d08c7b 100644
--- a/classes/class_user.php
+++ b/classes/class_user.php
@@ -43,23 +43,26 @@ class user
$this->sid = sha1(time() . $core->remote_ip() . $username);
$admin_flag = $is_admin ? 1 : 0;
- $db->escape($username);
+
+ $params = array('sid' => $this->sid,
+ 'is_admin' => $admin_flag,
+ 'timestamp' => $core->timestamp,
+ 'username' => $username);
// Update/insert the session ID into the DB
$sql = "UPDATE {$db->prefix}session " .
- "SET sid = '{$this->sid}', " .
- " is_admin = {$admin_flag}, " .
- " timestamp = {$core->timestamp} " .
- "WHERE username = '{$username}'";
- $db->query($sql);
+ "SET sid = :sid, " .
+ " is_admin = :is_admin, " .
+ " timestamp = :timestamp " .
+ "WHERE username = :username";
+ $db->query($sql, $params);
if ($db->affected_rows() <= 0)
{
$sql = "INSERT INTO {$db->prefix}session " .
"(username, is_admin, sid, timestamp) " .
- "VALUES ('{$username}', $admin_flag, " .
- " '{$this->sid}', {$core->timestamp})";
- $db->query($sql);
+ "VALUES (:username, :is_admin, :sid, :timestamp)";
+ $db->query($sql, $params);
}
// Save username and session ID to a cookie
@@ -76,17 +79,13 @@ class user
$username = $core->variable('username', '', true);
$sid = $core->variable('session_id', '', true);
- // Escape session ID and username
- $db->escape($username);
- $db->escape($sid);
-
// Get current session data
$sql = "SELECT * FROM {$db->prefix}session " .
- "WHERE username = '{$username}' " .
- "AND sid = '{$sid}'";
- $row = $db->query($sql, true);
+ "WHERE username = ? " .
+ "AND sid = ?";
+ $row = $db->query($sql, array($username, $sid), true);
- if ($row != null)
+ if ($row !== false)
{
// Update the DB with current time
$sql = "UPDATE {$db->prefix}session " .
@@ -100,10 +99,6 @@ class user
$this->sid = $row['sid'];
$this->is_admin = ($row['is_admin'] == 1);
$this->is_logged_in = true;
-
- // Escape the data since we might use it for queries
- $db->escape($this->username);
- $db->escape($this->sid);
}
}
@@ -112,13 +107,11 @@ class user
{
global $db;
- $db->escape($username);
-
// Check if an entry for the user exists in the ban table
$sql = "SELECT COUNT(*) AS count " .
"FROM {$db->prefix}bans " .
- "WHERE username = '{$username}'";
- $row = $db->query($sql, true);
+ "WHERE username = ?";
+ $row = $db->query($sql, $username, true);
// Return true if an entry was found
return $row['count'] > 0;
@@ -131,10 +124,10 @@ class user
$sql = "SELECT COUNT(*) AS count " .
"FROM {$db->prefix}session";
- $row = $db->query($sql, true);
+ $row = $db->query($sql, null, true);
// Return the user count
- return $row['count'];
+ return $row['count'] > 0;
}
// Get details of the user from LDAP
@@ -248,17 +241,16 @@ class user
{
global $db;
- $db->escape($provider);
- $db->escape($identifier);
-
// Get current session data
$sql = "SELECT username FROM {$db->prefix}identities " .
- "WHERE provider = '{$provider}' " .
- "AND identifier = '{$identifier}'";
- Hybrid_Logger::info("executing '${sql}'");
- $row = $db->query($sql, true);
-
- if (!is_null($row))
+ "WHERE provider = :provider " .
+ "AND identifier = :identifier";
+ $row = $db->query($sql,
+ array('provider' => $provider,
+ 'identifier' => $identifier),
+ true);
+
+ if ($row !== false)
return $row['username'];
else
return null;
@@ -268,13 +260,11 @@ class user
{
global $db;
- $db->escape($username);
-
// Get current session data
$sql = "SELECT is_admin, fullname, email FROM {$db->prefix}profiles " .
- "WHERE username = '{$username}'";
- $row = $db->query($sql, true);
- if (is_null($row)) {
+ "WHERE username = ?";
+ $row = $db->query($sql, $username, true);
+ if ($row === false) {
return null;
} else {
$values = array();
@@ -335,25 +325,23 @@ class user
else
$fullname = '';
- $db->escape($username);
- $db->escape($fullname);
- $db->escape($email);
+ $params = array('username' => $username,
+ 'fullname' => $fullname,
+ 'email' => $email,
+ 'emailVerified' => $emailVerified,
+ 'is_admin' => 0,
+ 'provider' => $provider,
+ 'identifier' => $identifier);
$sql = "INSERT INTO {$db->prefix}profiles " .
"(username, fullname, email, emailVerified, is_admin) " .
- "VALUES ('{$username}', '{$fullname}', " .
- " '{$email}', {$emailVerified}, 0)";
- Hybrid_Logger::info("running: {$sql}");
- $db->query($sql);
-
- $db->escape($provider);
- $db->escape($identifier);
+ "VALUES (:username, :fullname, :email, :emailVerified, :is_admin)";
+ $db->query($sql, $params);
$sql = "INSERT INTO {$db->prefix}identities " .
"(provider, identifier, username) " .
- "VALUES ('{$provider}', '{$identifier}', '{$username}')";
- Hybrid_Logger::info("running: {$sql}");
- $db->query($sql);
+ "VALUES (:provider, :identifier, :username)";
+ $db->query($sql, $params);
}
function finish_login($provider)
@@ -388,7 +376,7 @@ class user
}
$info = $this->lookup_user($username);
- $this->create_session($identifier, $info['is_admin']);
+ $this->create_session($username, $info['is_admin']);
return true;
}
@@ -409,15 +397,10 @@ class user
$core->unset_cookie('username');
$core->unset_cookie('session_id');
- // Escape the username and session ID
- $db->escape($username);
- $db->escape($sid);
-
// Delete session data from the DB
$sql = "DELETE FROM {$db->prefix}session " .
- "WHERE username = '{$username}' " .
- "AND sid = '{$sid}'";
- $db->query($sql);
+ "WHERE username = ? AND sid = ?";
+ $db->query($sql, array($username, $sid));
}
// Gets the profile link for a user
@@ -444,6 +427,39 @@ class user
}
}
+ // Check the role of the current user
+ function get_role($program_id) {
+ global $cache, $db;
+
+ $key = "role_{$this->username}_{$program_id}";
+ $role_data = $cache->get($key, 'roles');
+
+ if (!$role_data)
+ {
+ $sql = "SELECT role FROM {$db->prefix}roles " .
+ "WHERE username = :username " .
+ "AND program_id = :id";
+
+ $role_data = $db->query($sql,
+ array('username' => $this->username,
+ 'id' => $program_id),
+ true);
+ $cache->put($key, $role_data, 'roles');
+ }
+
+ // Check if we have a role
+ if ($role_data !== false)
+ {
+ $role = $role_data['role'];
+ }
+ else
+ {
+ $role = 'g';
+ }
+
+ return $role;
+ }
+
// Restricts a screen to a specific condition only
function restrict($condition, $admin_override = false)
{
diff --git a/cron.php b/cron.php
index eac1b37..d56c82f 100644
--- a/cron.php
+++ b/cron.php
@@ -25,9 +25,9 @@ else
// Get last run time
$sql = "SELECT timestamp " .
"FROM {$db->prefix}cron";
- $row = $db->query($sql, true);
+ $row = $db->query($sql, null, true);
- if ($row != null)
+ if ($row !== false)
{
$last_run = $row['timestamp'];
}
@@ -48,8 +48,8 @@ if (($core->timestamp - $last_run) > 60)
else
{
$sql = "UPDATE {$db->prefix}cron " .
- "SET timestamp = {$core->timestamp}";
- $db->query($sql);
+ "SET timestamp = ?";
+ $db->query($sql, array($core->timestamp));
}
// Cron tasks
diff --git a/modules/mod_approve_mentors.php b/modules/mod_approve_mentors.php
index 8e13ed7..b8b0eb3 100644
--- a/modules/mod_approve_mentors.php
+++ b/modules/mod_approve_mentors.php
@@ -15,7 +15,6 @@ if (!empty($action) && !empty($username))
{
// Process the username
$username = urldecode($username);
- $db->escape($username);
if ($action == 'approve')
{
@@ -29,9 +28,10 @@ if (!empty($action) && !empty($username))
if (isset($new_role))
{
$sql = "UPDATE {$db->prefix}roles " .
- "SET role = '{$new_role}' " .
- "WHERE username = '{$username}'";
- $db->query($sql);
+ "SET role = :role " .
+ "WHERE username = :username";
+ $db->query($sql, array('role' => $new_role,
+ 'username' => $username));
}
// Purge the roles cache
diff --git a/modules/mod_manage_programs.php b/modules/mod_manage_programs.php
index 5e95438..37ca446 100644
--- a/modules/mod_manage_programs.php
+++ b/modules/mod_manage_programs.php
@@ -30,12 +30,13 @@ if ($action == 'list')
// Get all programs
$sql = "SELECT * FROM {$db->prefix}programs " .
- "LIMIT {$limit_start}, {$config->per_page}";
- $program_data = $db->query($sql);
+ "LIMIT :start, :count";
+ $program_data = $db->query($sql, array('i:start' => $limit_start,
+ 'i:count' => $config->per_page));
// Get program count
$sql = "SELECT COUNT(*) AS count FROM {$db->prefix}programs";
- $program_count = $db->query($sql, true);
+ $program_count = $db->query($sql, null, true);
// Build the list
foreach ($program_data as $row)
@@ -93,37 +94,43 @@ else if ($action == 'editor')
}
else
{
- $db->escape($id);
- $db->escape($title);
- $db->escape($description);
-
// Determine deadline and completion flags
$deadline = $dl_student < $core->timestamp ? 1 : 0;
$complete = $end_time < $core->timestamp ? 1 : 0;
+ $params = array('id' => $id,
+ 'title' => $title,
+ 'description' => $description,
+ 'start_time' => $start_time,
+ 'end_time' => $end_time,
+ 'dl_student' => $dl_student,
+ 'dl_mentor' => $dl_mentor,
+ 'is_active' => $active,
+ 'complete' => $complete);
+
if ($id > 0)
{
// Update program data
$sql = "UPDATE {$db->prefix}programs " .
- "SET title = '{$title}', " .
- " description = '{$description}', " .
- " start_time = {$start_time}, " .
- " end_time = {$end_time}, " .
- " dl_student = {$dl_student}, " .
- " dl_mentor = {$dl_mentor}, " .
- " is_active = {$active} " .
- "WHERE id = $id";
- $db->query($sql);
+ "SET title = :title, " .
+ " description = :description, " .
+ " start_time = :start_time, " .
+ " end_time = :end_time, " .
+ " dl_student = :dl_student, " .
+ " dl_mentor = :dl_mentor, " .
+ " is_active = :active " .
+ "WHERE id = :id";
+ $db->query($sql, $params);
// Update program flags in the queue
// Usually, one entry for the program is exptected to
// be there, unless the program is updated post completion,
// which is highly unlikely.
$sql = "UPDATE {$db->prefix}queue " .
- "SET deadline = {$deadline}, " .
- " complete = {$complete} " .
- "WHERE program_id = {$id}";
- $db->query($sql);
+ "SET deadline = :deadline, " .
+ " complete = :complete " .
+ "WHERE program_id = :id";
+ $db->query($sql, $params);
}
else
{
@@ -131,18 +138,18 @@ else if ($action == 'editor')
$sql = "INSERT INTO {$db->prefix}programs " .
"(title, description, start_time, end_time, " .
" dl_student, dl_mentor, is_active) " .
- "VALUES ('{$title}', '{$description}', {$start_time}, " .
- " {$end_time}, {$dl_student}, {$dl_mentor}, {$active})";
- $db->query($sql);
+ "VALUES (:title, :description, :start_time, " .
+ " :end_time, :dl_student, :dl_mentor, :active)";
+ $db->query($sql, $params);
// Get the new program ID
- $program_id = $db->get_id();
+ $params['id'] = $db->get_id();
// Insert new entry to the queue
$sql = "INSERT INTO {$db->prefix}queue " .
"(program_id, deadline, complete) " .
- "VALUES ({$program_id}, {$deadline}, {$complete})";
- $db->query($sql);
+ "VALUES (:id, :deadline, :complete)";
+ $db->query($sql, $params);
}
// Purge the programs cache
@@ -156,11 +163,9 @@ else if ($action == 'editor')
// Load data when in edit mode
if ($id > 0)
{
- $db->escape($id);
-
$sql = "SELECT * FROM {$db->prefix}programs " .
- "WHERE id = {$id}";
- $row = $db->query($sql, true);
+ "WHERE id = ?";
+ $row = $db->query($sql, array($id), true);
// Set loaded data
$title = $row['title'];
@@ -197,27 +202,27 @@ else if ($action == 'delete')
// Deletion was confirmed
if ($confirm)
{
- $db->escape($id);
+ $params = array('id' => $id);
$sql = "DELETE FROM {$db->prefix}roles " .
- "WHERE program_id = {$id}";
- $db->query($sql);
+ "WHERE program_id = :id";
+ $db->query($sql, $params);
$sql = "DELETE FROM {$db->prefix}participants " .
- "WHERE program_id = {$id}";
- $db->query($sql);
+ "WHERE program_id = :id";
+ $db->query($sql, $params);
$sql = "DELETE FROM {$db->prefix}projects " .
- "WHERE program_id = {$id}";
- $db->query($sql);
+ "WHERE program_id = :id";
+ $db->query($sql, $params);
$sql = "DELETE FROM {$db->prefix}queue " .
- "WHERE program_id = {$id}";
- $db->query($sql);
+ "WHERE program_id = :id";
+ $db->query($sql, $params);
$sql = "DELETE FROM {$db->prefix}programs " .
- "WHERE id = {$id}";
- $db->query($sql);
+ "WHERE id = :id";
+ $db->query($sql, $params);
// Purge the cache data
$cache->purge(array('programs', 'projects', 'roles'));
diff --git a/modules/mod_notifications.php b/modules/mod_notifications.php
index 13b47ec..dd239d2 100644
--- a/modules/mod_notifications.php
+++ b/modules/mod_notifications.php
@@ -11,7 +11,6 @@ if (!defined('IN_PANDORA')) exit;
set_time_limit(3600);
// Get the program's participant list
-$db->escape($program_id);
// Set default for sending mail
$name = $config->ldap_fullname;
@@ -48,8 +47,8 @@ if (isset($_POST['process']))
" title as project_title, " .
" is_accepted " .
"FROM {$db->prefix}projects " .
- "WHERE program_id = {$program['program_id']}";
- $project_data = $db->query($sql);
+ "WHERE program_id = ?";
+ $project_data = $db->query($sql, $program['program_id']);
// Assign program name
$email->assign('program_name', $program['program_title']);
@@ -65,15 +64,18 @@ if (isset($_POST['process']))
$project['mentor'] = '';
$project['passed'] = false;
+ $params = array('program_id' => $program['program_id'],
+ 'project_id' => $project['project_id']);
+
// Get student data
$sql = "SELECT username, passed " .
"FROM {$db->prefix}participants " .
- "WHERE program_id = {$program['program_id']} " .
- "AND project_id = {$project['project_id']} " .
+ "WHERE program_id = :program_id " .
+ "AND project_id = :project_id " .
"AND role = 's'";
- $student_data = $db->query($sql, true);
+ $student_data = $db->query($sql, $params, true);
- if ($student_data != null)
+ if ($student_data !== false)
{
$project['student'] = $student_data['username'];
$project['passed'] = $student_data['passed'];
@@ -82,12 +84,12 @@ if (isset($_POST['process']))
// Get mentor data
$sql = "SELECT username " .
"FROM {$db->prefix}participants " .
- "WHERE program_id = {$program['program_id']} " .
- "AND project_id = {$project['project_id']} " .
+ "WHERE program_id = :program_id " .
+ "AND project_id = :project_id " .
"AND role = 'm'";
- $mentor_data = $db->query($sql, true);
+ $mentor_data = $db->query($sql, $params, true);
- if ($mentor_data != null)
+ if ($mentor_data !== false)
{
$project['mentor'] = $mentor_data['username'];
}
@@ -187,18 +189,20 @@ if (isset($_POST['process']))
if ($deadline == 0 || $complete == 0)
{
$sql = "UPDATE {$db->prefix}queue " .
- "SET deadline = {$deadline}, " .
- " complete = {$complete} " .
- "WHERE program_id = {$program['program_id']}";
- $db->query($sql);
+ "SET deadline = :deadline, " .
+ " complete = :complete " .
+ "WHERE program_id = :program_id";
+ $db->query($sql, array('deadline' => $deadline,
+ 'complete' => $complete,
+ 'program_id' => $program['program_id']));
}
// Both flags set, remove the item from queue
else
{
$sql = "DELETE FROM {$db->prefix}queue " .
- "WHERE program_id = {$program['program_id']}";
- $db->query($sql);
+ "WHERE program_id = :program_id";
+ $db->query($sql, $program);
}
// No mails were processed
diff --git a/modules/mod_program_home.php b/modules/mod_program_home.php
index 374f147..5204ad5 100644
--- a/modules/mod_program_home.php
+++ b/modules/mod_program_home.php
@@ -10,45 +10,12 @@ if (!defined('IN_PANDORA')) exit;
// Collect some data
$id = $core->variable('prg', 0);
-// Get program data
-$db->escape($id);
-
-$program_data = $cache->get("program_{$id}", 'programs');
-
-if (!$program_data)
-{
- $sql = "SELECT * FROM {$db->prefix}programs " .
- "WHERE id = {$id}";
- $program_data = $db->query($sql, true);
-
- $cache->put("program_{$id}", $program_data, 'programs');
-}
+$program_data = $cache->get_program_data($id);
// Was the program found?
if ($program_data != null)
{
- // Check the role of the current user
- $sql = "SELECT role FROM {$db->prefix}roles " .
- "WHERE username = '{$user->username}' " .
- "AND program_id = {$id}";
- $crc = crc32($sql);
- $role_data = $cache->get($crc, 'roles');
-
- if (!$role_data)
- {
- $role_data = $db->query($sql, true);
- $cache->put($crc, $role_data, 'roles');
- }
-
- // Check if we have a role
- if ($role_data != null)
- {
- $role = $role_data['role'];
- }
- else
- {
- $role = 'g';
- }
+ $role = $user->get_role($id);
// Set object availability based on deadlines
$show_student = true;
diff --git a/modules/mod_timeline.php b/modules/mod_timeline.php
index e0288f2..1a36282 100644
--- a/modules/mod_timeline.php
+++ b/modules/mod_timeline.php
@@ -13,16 +13,13 @@ $program_id = $core->variable('prg', 0);
// We need program ID for this module
$user->restrict($program_id > 0);
-// Escape the program ID
-$db->escape($program_id);
-
// Get program data
$sql = "SELECT * FROM {$db->prefix}programs " .
- "WHERE id = {$program_id}";
-$program_data = $db->query($sql, true);
+ "WHERE id = ?";
+$program_data = $db->query($sql, $program_id, true);
// Check if program was found
-if ($program_data != null)
+if ($program_data !== false)
{
$month = new DateTime();
$start = new DateTime();
diff --git a/modules/mod_user_ban.php b/modules/mod_user_ban.php
index 915778c..77907de 100644
--- a/modules/mod_user_ban.php
+++ b/modules/mod_user_ban.php
@@ -19,26 +19,26 @@ $is_successful = $success == 1;
// Was the ban form submitted?
if ($ban_submit)
{
- $db->escape($ban_user);
+ $params = array('ban_user' => $ban_user);
// Check if user is already banned
$sql = "SELECT COUNT(*) AS count " .
"FROM {$db->prefix}bans " .
- "WHERE username = '{$ban_user}'";
- $row = $db->query($sql, true);
+ "WHERE username = :ban_user";
+ $row = $db->query($sql, $params, true);
// Count should be 0 to ban again
if ($row['count'] == 0)
{
// Insert entry into user ban table
$sql = "INSERT INTO {$db->prefix}bans (username) " .
- "VALUES ('{$ban_user}')";
- $db->query($sql);
+ "VALUES (:ban_user)";
+ $db->query($sql, $params);
// Kill the user's session
$sql = "DELETE FROM {$db->prefix}session " .
- "WHERE username = '{$ban_user}'";
- $db->query($sql);
+ "WHERE username = :ban_user";
+ $db->query($sql, $params);
}
// Redirect to refresh
@@ -48,12 +48,12 @@ if ($ban_submit)
// Was the user unbanned
if (!empty($unban_user))
{
- $db->escape($unban_user);
+ $params = array('unban_user' => $ban_user);
// Delete the user from the ban table
$sql = "DELETE FROM {$db->prefix}bans " .
- "WHERE username = '{$unban_user}'";
- $db->query($sql);
+ "WHERE username = :unban_user";
+ $db->query($sql, $params);
// Redirect to refresh
$core->redirect("?q=user_ban");
diff --git a/modules/mod_view_participants.php b/modules/mod_view_participants.php
index 1b57574..b1c4c22 100644
--- a/modules/mod_view_participants.php
+++ b/modules/mod_view_participants.php
@@ -10,7 +10,6 @@ if (!defined('IN_PANDORA')) exit;
$program_id = $core->variable('prg', '');
// Get the program's participant list
-$db->escape($program_id);
$sql = "SELECT r.username, r.role AS role, " .
"pr.id AS project_id, pr.title AS project_title " .
diff --git a/modules/mod_view_programs.php b/modules/mod_view_programs.php
index 4084e0a..975567d 100644
--- a/modules/mod_view_programs.php
+++ b/modules/mod_view_programs.php
@@ -49,7 +49,7 @@ if (!$program_data)
if (!$program_count)
{
- $program_count = $db->query($count_sql, true);
+ $program_count = $db->query($count_sql, null, true);
$cache->put($crc_count, $program_count, 'programs');
}
diff --git a/modules/mod_view_projects.php b/modules/mod_view_projects.php
index 758cef1..118963a 100644
--- a/modules/mod_view_projects.php
+++ b/modules/mod_view_projects.php
@@ -26,10 +26,6 @@ $mentor_apply = isset($_POST['mentor_apply']);
$project_save = isset($_POST['project_save']);
$confirm = isset($_POST['yes']);
-// Escape captured data
-$db->escape($program_id);
-$db->escape($project_id);
-
// Validate project and program ID
if ($project_id > 0)
{
@@ -37,36 +33,26 @@ if ($project_id > 0)
"FROM {$db->prefix}projects prj " .
"LEFT JOIN {$db->prefix}programs prg " .
"ON prg.id = prj.program_id " .
- "WHERE prj.id = {$project_id} " .
- "AND prg.id = {$program_id} " .
+ "WHERE prj.id = :project_id " .
+ "AND prg.id = :program_id " .
(!$user->is_admin ? "AND prg.is_active = 1" : "");
}
else
{
$sql = "SELECT COUNT(*) AS count " .
"FROM {$db->prefix}programs " .
- "WHERE id = {$program_id} " .
+ "WHERE id = :program_id " .
(!$user->is_admin ? "AND is_active = 1" : "");
}
-$row = $db->query($sql, true);
+$row = $db->query($sql,
+ array('program_id' => $program_id,
+ 'project_id' => $project_id),
+ true);
$user->restrict($row['count'] > 0);
// Get the role of the user
-$sql = "SELECT * FROM {$db->prefix}roles " .
- "WHERE program_id = {$program_id} " .
- "AND username = '{$user->username}'";
-$crc = crc32($sql);
-$role_data = $cache->get($crc, 'roles');
-
-if (!$role_data)
-{
- $role_data = $db->query($sql, true);
- $cache->put($crc, $role_data, 'roles');
-}
-
-// Role is guest if no entry was found
-$role = $role_data != null ? $role_data['role'] : 'g';
+$role = $user->get_role($program_id);
// Check if the user is the owner of the project
if ($project_id > 0)
@@ -75,12 +61,15 @@ if ($project_id > 0)
"FROM {$db->prefix}projects prj " .
"LEFT JOIN {$db->prefix}participants prt " .
"ON prj.id = prt.project_id " .
- "WHERE prj.id = {$project_id} " .
- "AND prt.username = '{$user->username}' " .
+ "WHERE prj.id = :project_id " .
+ "AND prt.username = :username " .
"AND (prt.role = 's' " .
"OR (prt.role = 'm' " .
"AND prj.is_accepted = 1))";
- $owner_count = $db->query($sql, true);
+ $owner_count = $db->query($sql,
+ array('project_id' => $project_id,
+ 'username' => $username),
+ true);
$is_owner = $owner_count['count'] > 0;
}
@@ -116,8 +105,8 @@ if ($action == 'editor')
if ($role == 's')
{
$sql = "SELECT dl_student FROM {$db->prefix}programs " .
- "WHERE id = {$program_id}";
- $program_data = $db->query($sql, true);
+ "WHERE id = ?";
+ $program_data = $db->query($sql, $program_id, true);
$user->restrict($core->timestamp < $program_data['dl_student'], true);
}
@@ -128,9 +117,9 @@ if ($action == 'editor')
$sql = "SELECT * FROM {$db->prefix}projects prj " .
"LEFT JOIN {$db->prefix}participants prt " .
"ON prj.id = prt.project_id " .
- "WHERE prj.id = {$project_id} " .
+ "WHERE prj.id = ? " .
"AND prt.role = 's'";
- $project_data = $db->query($sql, true);
+ $project_data = $db->query($sql, $project_id, true);
// Do not let anyone but admins edit rejected projects`
$user->restrict($project_data['is_accepted'] != 0, true);
@@ -158,54 +147,55 @@ if ($action == 'editor')
}
else
{
- $db->escape($title);
- $db->escape($description);
- $db->escape($is_complete);
- $db->escape($is_passed);
+ $params = array('project_id' => $project_id,
+ 'title' => $title,
+ 'description' => $description,
+ 'is_complete' => $is_complete,
+ 'is_passed' => $is_passed,
+ 'new_student' => $new_student,
+ 'new_mentor' => $new_mentor);
// Are we updating?
if ($project_id > 0)
{
// Update project data
$sql = "UPDATE {$db->prefix}projects " .
- "SET title = '{$title}', " .
- " description = '{$description}', " .
- " is_complete = " . ($can_decide ? "{$is_complete} " : "is_complete ") .
- "WHERE id = {$project_id}";
- $db->query($sql);
+ "SET title = :title, " .
+ " description = :description, " .
+ " is_complete = " . ($can_decide ? ":is_complete " : "is_complete ") .
+ "WHERE id = :project_id";
+ $db->query($sql, $params);
// Update student pass status
if ($can_decide)
{
$sql = "UPDATE {$db->prefix}participants " .
- "SET passed = $is_passed " .
- "WHERE project_id = {$project_id} " .
+ "SET passed = :is_passed " .
+ "WHERE project_id = :project_id " .
"AND role = 's'";
- $db->query($sql);
+ $db->query($sql, $params);
}
// Update student name
if ($user->is_admin && !empty($new_student))
{
- $db->escape($new_student);
-
// Get existing role of the new student
$sql = "SELECT role FROM {$db->prefix}roles " .
- "WHERE username = '{$new_student}' " .
- "AND program_id = {$program_id}";
- $role_data = $db->query($sql, true);
+ "WHERE username = :new_student " .
+ "AND program_id = :program_id";
+ $role_data = $db->query($sql, $params, true);
// New student has an already defined role
- if ($role_data != null)
+ if ($role_data !== false)
{
if ($role_data['role'] != 'm')
{
- // Update role to mentor
+ // Update role to student
$sql = "UPDATE {$db->prefix}roles " .
"SET role = 's' " .
- "WHERE username = '{$new_student}' " .
- "AND program_id = {$program_id} ";
- $db->query($sql);
+ "WHERE username = :new_student " .
+ "AND program_id = :program_id ";
+ $db->query($sql, $params);
}
else
{
@@ -216,44 +206,42 @@ if ($action == 'editor')
{
$sql = "INSERT INTO {$db->prefix}roles " .
"(username, program_id, role) " .
- "VALUES ('{$new_student}', {$program_id}, 's')";
- $db->query($sql);
+ "VALUES (:new_student, :program_id, 's')";
+ $db->query($sql, $params);
}
if (empty($error_message))
{
// Update the project student
$sql = "UPDATE {$db->prefix}participants " .
- "SET username = '{$new_student}', " .
+ "SET username = :new_student, " .
" role = 's' " .
- "WHERE program_id = {$program_id} " .
- "AND project_id = {$project_id}";
- $db->query($sql);
+ "WHERE program_id = :program_id " .
+ "AND project_id = :project_id";
+ $db->query($sql, $params);
}
}
// Update mentor name
if ($user->is_admin && !empty($new_mentor))
{
- $db->escape($new_mentor);
-
// Get existing role of the new mentor
$sql = "SELECT role FROM {$db->prefix}roles " .
- "WHERE username = '{$new_mentor}' " .
- "AND program_id = {$program_id}";
- $role_data = $db->query($sql, true);
+ "WHERE username = :new_mentor' " .
+ "AND program_id = :program_id";
+ $role_data = $db->query($sql, $params, true);
// New mentor has an already defined role
- if ($role_data != null)
+ if ($role_data !== false)
{
if ($role_data['role'] != 's')
{
// Update role to mentor
$sql = "UPDATE {$db->prefix}roles " .
"SET role = 'm' " .
- "WHERE username = '{$new_mentor}' " .
- "AND program_id = {$program_id} ";
- $db->query($sql);
+ "WHERE username = :new_mentor " .
+ "AND program_id = :program_id ";
+ $db->query($sql, $params);
}
else
{
@@ -264,23 +252,23 @@ if ($action == 'editor')
{
$sql = "INSERT INTO {$db->prefix}roles " .
"(username, program_id, role) " .
- "VALUES ('{$new_mentor}', {$program_id}, 'm')";
- $db->query($sql);
+ "VALUES (:new_mentor, :program_id, 'm')";
+ $db->query($sql, $params);
}
if (empty($error_message))
{
// Delete existing mentors of this project
$sql = "DELETE FROM {$db->prefix}participants " .
- "WHERE project_id = {$project_id} " .
+ "WHERE project_id = :project_id " .
"AND role = 'm'";
- $db->query($sql);
+ $db->query($sql, $params);
// Insert the new mentor
$sql = "INSERT INTO {$db->prefix}participants " .
"(username, project_id, program_id, role) " .
- "VALUES ('{$new_mentor}', {$project_id}, {$program_id}, 'm')";
- $db->query($sql);
+ "VALUES (':new_mentor', :project_id, :program_id, 'm')";
+ $db->query($sql, $params);
}
}
@@ -297,22 +285,24 @@ if ($action == 'editor')
else
{
// Auto accept project when being created by an admin
- $is_accepted = $user->is_admin ? 1 : -1;
+ $params['is_accepted'] = $user->is_admin ? 1 : -1;
// Insert new project
$sql = "INSERT INTO {$db->prefix}projects " .
"(title, description, program_id, is_accepted, is_complete) " .
- "VALUES ('{$title}', '{$description}', {$program_id}, {$is_accepted}, 0)";
- $db->query($sql);
+ "VALUES (':title', ':description', :program_id, :is_accepted, 0)";
+ $db->query($sql, $params);
// Get the new project ID
- $new_id = $db->get_id();
+ $params['project_id'] = $db->get_id();
+ // XXX - this seems wrong when admin is creating
+ $params['username'] = $user->username;
// Insert student data
$sql = "INSERT INTO {$db->prefix}participants " .
"(username, project_id, program_id, role, passed) " .
- "VALUES ('{$user->username}', {$new_id}, {$program_id}, 's', -1)";
- $db->query($sql);
+ "VALUES (:username, :project_id, :program_id, 's', -1)";
+ $db->query($sql, $params);
$success_message = $lang->get('proposal_submitted');
$title = '';
@@ -363,15 +353,13 @@ else if ($action == 'delete')
// Deletion was confirmed
if ($confirm)
{
- $db->escape($id);
-
$sql = "DELETE FROM {$db->prefix}participants " .
- "WHERE project_id = {$project_id}";
- $db->query($sql);
+ "WHERE project_id = ?";
+ $db->query($sql, $id);
$sql = "DELETE FROM {$db->prefix}projects " .
- "WHERE id = {$project_id}";
- $db->query($sql);
+ "WHERE id = ?";
+ $db->query($sql, $id);
// Purge the project cache
$cache->purge('projects');
@@ -397,33 +385,16 @@ else if ($action == 'view')
$user->restrict($program_id > 0 && $project_id > 0);
// Get program, project and participant data
- $program_data = $cache->get("program_{$program_id}", 'programs');
- $project_data = $cache->get("project_{$project_id}", 'projects');
- $participant_data = $cache->get("participant_{$project_id}", 'projects');
+ $program_data = $cache->get_program_data($program_id);
+ $project_data = $cache->get_project_data($project_id);
- if (!$program_data)
- {
- $sql = "SELECT * FROM {$db->prefix}programs " .
- "WHERE id = {$program_id}";
- $program_data = $db->query($sql, true);
-
- $cache->put("program_{$program_id}", $program_data, 'programs');
- }
-
- if (!$project_data)
- {
- $sql = "SELECT * FROM {$db->prefix}projects " .
- "WHERE id = {$project_id}";
- $project_data = $db->query($sql, true);
-
- $cache->put("project_{$project_id}", $project_data, 'projects');
- }
+ $participant_data = $cache->get("participant_{$project_id}", 'projects');
if (!$participant_data)
{
$sql = "SELECT * FROM {$db->prefix}participants " .
- "WHERE project_id = {$project_id}";
- $participant_data = $db->query($sql);
+ "WHERE project_id = ?";
+ $participant_data = $db->query($sql, $project_id);
$cache->put("participant_{$project_id}", $participant_data, 'projects');
}
@@ -509,8 +480,10 @@ else if ($action == 'view')
{
$sql = "INSERT INTO {$db->prefix}participants " .
"(username, project_id, program_id, role) " .
- "VALUES ('{$user->username}', {$project_id}, {$program_id}, 'm')";
- $db->query($sql);
+ "VALUES (:username, :project_id, :program_id, 'm')";
+ $db->query($sql, array('username' => $user->username,
+ 'project_id' => $project_id,
+ 'program_id' => $program_id));
$success_message = $lang->get('mentor_submitted');
$can_mentor = false;
@@ -566,48 +539,51 @@ else if ($action == 'user' || $action == 'proposed' || $action == 'accepted' ||
$user->restrict($program_id > 0);
// Get program data
- $program_data = $cache->get("program_{$program_id}", 'programs');
+ $program_data = $cache->get_program_data($program_id);
- if (!$program_data)
- {
- $sql = "SELECT * FROM {$db->prefix}programs " .
- "WHERE id = {$program_id}";
- $program_data = $db->query($sql, true);
-
- $cache->put("program_{$program_id}", $program_data, 'programs');
- }
+ $params = array('program_id' => $program_id,
+ 'username' => $user->username,
+ 'i:start' => $limit_start,
+ 'i:count' => $config->per_page);
// Build the queries
$data_sql = "SELECT * FROM {$db->prefix}projects ";
$count_sql = "SELECT COUNT(*) AS count FROM {$db->prefix}projects ";
- $limit = "LIMIT {$limit_start}, {$config->per_page}";
+ $limit = "LIMIT :start, :count";
// Set action specific page title and query
// Proposals will continue to appear as 'proposed' even if it is approved
// for non-admin and non-mentor roles until the mentor deadline
if ($action == 'user')
{
+ $key = "projects_user_{$program_id}_{$user->username}";
+
$title = $lang->get('your_projects');
$filter = "WHERE id IN (SELECT project_id " .
"FROM {$db->prefix}participants " .
- "WHERE username = '{$user->username}' " .
- "AND program_id = {$program_id}) ";
+ "WHERE username = :username " .
+ "AND program_id = :program_id) ";
}
else if ($action == 'proposed')
{
+ $key = "projects_proposed_{$program_id}";
+
$is_accepted = 'is_accepted = -1';
if ($role != 'm' && !$user->is_admin && $core->timestamp < $program_data['dl_mentor'])
{
$is_accepted .= ' OR is_accepted = 1';
+ $key .= "_nonmoderator";
}
$title = $lang->get('proposed_projects');
$filter = "WHERE {$is_accepted} " .
- "AND program_id = {$program_id} ";
+ "AND program_id = :program_id ";
}
else if ($action == 'accepted')
{
+ $key = 'projects_accepted_{$program_id}';
+
$is_accepted = 'is_accepted = 1';
if ($role != 'm' && !$user->is_admin && $core->timestamp < $program_data['dl_mentor'])
@@ -615,17 +591,20 @@ else if ($action == 'user' || $action == 'proposed' || $action == 'accepted' ||
// Just use a random value for is_accepted (2)
// we don't want to fetch anything here
$is_accepted = 'is_accepted = 2';
+ $key .= "_nonmoderator";
}
$title = $lang->get('accepted_projects');
$filter = "WHERE {$is_accepted} " .
- "AND program_id = {$program_id} ";
+ "AND program_id = :program_id ";
}
else if ($action == 'rejected')
{
+ $key = 'projects_rejected_{$program_id}';
+
$title = $lang->get('rejected_projects');
$filter = "WHERE is_accepted = 0 " .
- "AND program_id = {$program_id} ";
+ "AND program_id = :program_id ";
}
// Apply filters
@@ -634,23 +613,23 @@ else if ($action == 'user' || $action == 'proposed' || $action == 'accepted' ||
$count_sql .= $filter;
// Generate the cache keys
- $crc_data = crc32($data_sql);
- $crc_count = crc32($count_sql);
+ $data_key = "data_{$key}_{$limit_start}_{$config->per_page}";
+ $count_key = "count_{$key}";
// Get list data and count
- $list_data = $cache->get($crc_data, 'projects');
- $list_count = $cache->get($crc_count, 'projects');
+ $list_data = $cache->get($data_key, 'projects');
+ $list_count = $cache->get($count_key, 'projects');
if (!$list_data)
{
- $list_data = $db->query($data_sql);
- $cache->put($crc_data, $list_data, 'projects');
+ $list_data = $db->query($data_sql, $params);
+ $cache->put($data_key, $list_data, 'projects');
}
if (!$list_count)
{
- $list_count = $db->query($count_sql, true);
- $cache->put($crc_count, $list_count, 'projects');
+ $list_count = $db->query($count_sql, $params, true);
+ $cache->put($count_key, $list_count, 'projects');
}
// Assign approve/reject flag, we need it everywhere!
@@ -660,7 +639,7 @@ else if ($action == 'user' || $action == 'proposed' || $action == 'accepted' ||
$return_url = urlencode($core->request_uri());
// Generate the project list
- $key = "skin{$crc_data}" . ($user->is_admin ? '1' : '0');
+ $key = "skin{$data_key}" . ($user->is_admin ? '1' : '0');
$projects_list = $cache->get($key, 'projects');
if (!$projects_list)
@@ -730,9 +709,10 @@ else if ($action == 'approve' || $action == 'reject')
// Set the project as approved
$sql = "UPDATE {$db->prefix}projects " .
- "SET is_accepted = {$flag} " .
- "WHERE id = {$project_id}";
- $db->query($sql);
+ "SET is_accepted = :flag " .
+ "WHERE id = :program_id";
+ $db->query($sql, array('flag' => $flag,
+ 'program_id' => $program_id));
// Purge the project cache
$cache->purge('projects');
@@ -751,17 +731,11 @@ else if ($action == 'apply')
// Validate category
$user->restrict(in_array($category, array('student', 'mentor')));
- // Get the program data
- $program_data = $cache->get("program_{$program_id}", 'programs');
-
- if (!$program_data)
- {
- $sql = "SELECT * FROM {$db->prefix}programs " .
- "WHERE id = {$program_id}";
- $program_data = $db->query($sql, true);
+ $params = array('program_id' => $program_id,
+ 'username' => $username);
- $cache->put("program_{$program_id}", $program_data, 'programs');
- }
+ // Get the program data
+ $program_data = $cache->get_program_data($program_id);
// Set the new role based on action
$new_role = $category == 'student' ? 's' : 'i';
@@ -770,12 +744,13 @@ else if ($action == 'apply')
$user->restrict(($new_role == 's' && $core->timestamp < $program_data['dl_student']) ||
($new_role == 'i' && $core->timestamp < $program_data['dl_mentor']));
+ $params['role'] = $new_role;
+
// Insert the new role
$sql = "INSERT INTO {$db->prefix}roles " .
"(username, program_id, role) " .
- "VALUES ('{$user->username}', {$program_id}, " .
- "'{$new_role}')";
- $db->query($sql);
+ "VALUES (:username, :program_id, :role)";
+ $db->query($sql, $params);
// Notify admin with email for new mentor requests
if ($new_role == 'i')
@@ -798,35 +773,39 @@ else if ($action == 'resign')
// We need program ID for this action
$user->restrict($program_id > 0);
+ $params = array('program_id' => $program_id,
+ 'username' => $user->username,
+ 'timestamp' => $core->timestamp);
+
if ($confirm)
{
// Check if program has already started
$sql = "SELECT COUNT(*) AS count " .
"FROM {$db->prefix}programs " .
- "WHERE id = {$program_id} " .
- "AND start_time <= {$core->timestamp}";
- $prog_count = $db->query($sql, true);
+ "WHERE id = :program_id " .
+ "AND start_time <= :timestamp";
+ $prog_count = $db->query($sql, $params, true);
// If program already started, mark student as failed
if ($prog_count['count'] > 0)
{
$sql = "UPDATE {$db->prefix}participants " .
"SET passed = 0 " .
- "WHERE program_id = {$program_id} " .
- "AND username = '{$user->username}'";
- $db->query($sql);
+ "WHERE program_id = :program_id " .
+ "AND username = :username";
+ $db->query($sql, $params);
}
// Else, simply delete the proposals
else
{
$sql = "SELECT * FROM {$db->prefix}participants " .
- "WHERE program_id = {$program_id} " .
- "AND username = '{$user->username}'";
- $project_data = $db->query($sql);
+ "WHERE program_id = :program_id " .
+ "AND username = :username";
+ $project_data = $db->query($sql, $params);
// Student has one or more proposals
- if ($project_data != null)
+ if ($project_data)
{
$projects_ary = array();
@@ -851,9 +830,9 @@ else if ($action == 'resign')
// Set role as resigned
$sql = "UPDATE {$db->prefix}roles " .
"SET role = 'r' " .
- "WHERE program_id = {$program_id} " .
- "AND username = '{$user->username}'";
- $db->query($sql);
+ "WHERE program_id = :program_id " .
+ "AND username = :username";
+ $db->query($sql, $params);
// Purge the projects and roles cache
$cache->purge(array('projects', 'roles'));
[
Date Prev][
Date Next] [
Thread Prev][
Thread Next]
[
Thread Index]
[
Date Index]
[
Author Index]
