PHP Database::escape_string方法代码示例

function save_rsvp(Database $db)
{
    $values['people'] = $db->escape_string($_POST['people']);
    $values['partysize'] = $db->escape_string($_POST['partysize']);
    $values['is_attending'] = $db->escape_string($_POST['is_attending']);
    $values['contact_email'] = $db->escape_string($_POST['contact_email']);
    $values['extra_info'] = $db->escape_string($_POST['extra_info']);
    return $db->insert("rsvp_data", $values);
}

 public static function verify_user($username, $password)
 {
     $database = new Database();
     $username = $database->escape_string($username);
     $password = $database->escape_string($password);
     $sql = "SELECT * FROM users WHERE ";
     $sql .= "username = '{$username}' ";
     $sql .= "AND password = '{$password}' ";
     $sql .= "LIMIT 1";
     $the_result_array = self::execute_query($sql);
     return !empty($the_result_array) ? array_shift($the_result_array) : false;
 }

/**
 * Update the file or directory path in the document db document table
 *
 * @author - Hugues Peeters <peeters@ipm.ucl.ac.be>
 * @param  - action (string) - action type require : 'delete' or 'update'
 * @param  - old_path (string) - old path info stored to change
 * @param  - new_path (string) - new path info to substitute
 * @desc Update the file or directory path in the document db document table
 *
 */
function update_db_info($action, $old_path, $new_path = '')
{
    $dbTable = Database::get_course_table(TABLE_DOCUMENT);
    $course_id = api_get_course_int_id();
    switch ($action) {
        case 'delete':
            $old_path = Database::escape_string($old_path);
            $to_delete = "WHERE c_id = {$course_id} AND (path LIKE BINARY '" . $old_path . "' OR path LIKE BINARY '" . $old_path . "/%')";
            $query = "DELETE FROM {$dbTable} " . $to_delete;
            $result = Database::query("SELECT id FROM {$dbTable} " . $to_delete);
            if (Database::num_rows($result)) {
                require_once api_get_path(INCLUDE_PATH) . '../metadata/md_funcs.php';
                $mdStore = new mdstore(TRUE);
                // create if needed
                $md_type = substr($dbTable, -13) == 'scormdocument' ? 'Scorm' : 'Document';
                while ($row = Database::fetch_array($result)) {
                    $eid = $md_type . '.' . $row['id'];
                    $mdStore->mds_delete($eid);
                    $mdStore->mds_delete_offspring($eid);
                }
            }
            Database::query($query);
            break;
        case 'update':
            if ($new_path[0] == '.') {
                $new_path = substr($new_path, 1);
            }
            $new_path = str_replace('//', '/', $new_path);
            // Attempt to update	- tested & working for root	dir
            $new_path = Database::escape_string($new_path);
            $query = "UPDATE {$dbTable} SET\n                        path = CONCAT('" . $new_path . "', SUBSTRING(path, LENGTH('" . $old_path . "')+1) )\n                    WHERE c_id = {$course_id} AND (path LIKE BINARY '" . $old_path . "' OR path LIKE BINARY '" . $old_path . "/%')";
            Database::query($query);
            break;
    }
}

/**
 * Get the classes to display on the current page.
 */
function get_class_data($from, $number_of_items, $column, $direction)
{
    $class_table = Database::get_main_table(TABLE_MAIN_CLASS);
    $course_class_table = Database::get_main_table(TABLE_MAIN_COURSE_CLASS);
    $class_user_table = Database::get_main_table(TABLE_MAIN_CLASS_USER);
    $courseCode = api_get_course_id();
    $sql = "SELECT * FROM {$course_class_table} WHERE course_code = '" . $courseCode . "'";
    $res = Database::query($sql);
    $subscribed_classes = array();
    while ($obj = Database::fetch_object($res)) {
        $subscribed_classes[] = $obj->class_id;
    }
    $sql = "SELECT\n                c.id AS col0,\n                c.name   AS col1,\n                COUNT(cu.user_id) AS col2,\n                c.id AS col3\n            FROM {$class_table} c ";
    $sql .= " LEFT JOIN {$class_user_table} cu ON cu.class_id = c.id";
    $sql .= " WHERE 1 = 1";
    if (isset($_GET['keyword'])) {
        $keyword = Database::escape_string(trim($_GET['keyword']));
        $sql .= " AND (c.name LIKE '%" . $keyword . "%')";
    }
    if (count($subscribed_classes) > 0) {
        $sql .= " AND c.id NOT IN ('" . implode("','", $subscribed_classes) . "')";
    }
    $sql .= " GROUP BY c.id, c.name ";
    $sql .= " ORDER BY col{$column} {$direction} ";
    $sql .= " LIMIT {$from},{$number_of_items}";
    $res = Database::query($sql);
    $classes = array();
    while ($class = Database::fetch_row($res)) {
        $classes[] = $class;
    }
    return $classes;
}

    /**
     * Generate an array of attendances that a teacher hasn't created a link for.
     * @return array 2-dimensional array - every element contains 2 subelements (id, name)
     * @todo seems to be depracated
     */
    public function get_not_created_links()
    {
        return false;
        if (empty($this->course_code)) {
            die('Error in get_not_created_links() : course code not set');
        }
        $tbl_grade_links = Database::get_main_table(TABLE_MAIN_GRADEBOOK_LINK);
        $sql = 'SELECT att.id, att.name, att.attendance_qualify_title
				FROM ' . $this->get_attendance_table() . ' att
				WHERE
					att.c_id = ' . $this->course_id . ' AND
					att.id NOT IN (
						SELECT ref_id FROM ' . $tbl_grade_links . '
						WHERE
							type = ' . LINK_ATTENDANCE . ' AND
							course_code = "' . Database::escape_string($this->get_course_code()) . '"
					)
				AND att.session_id=' . api_get_session_id() . '';
        $result = Database::query($sql);
        $cats = array();
        while ($data = Database::fetch_array($result)) {
            if (isset($data['attendance_qualify_title']) && $data['attendance_qualify_title'] != '') {
                $cats[] = array($data['id'], $data['attendance_qualify_title']);
            } else {
                $cats[] = array($data['id'], $data['name']);
            }
        }
        return $cats;
    }

 protected function build_create()
 {
     $this->addElement('header', get_lang('MakeLink'));
     $select = $this->addElement('select', 'select_link', get_lang('ChooseLink'), null, array('onchange' => 'document.create_link.submit()'));
     $linktypes = LinkFactory::get_all_types();
     $select->addoption('[' . get_lang('ChooseLink') . ']', 0);
     $cc = $this->category_object->get_course_code();
     foreach ($linktypes as $linktype) {
         $link = LinkFactory::create($linktype);
         if (!empty($cc)) {
             $link->set_course_code($cc);
         } elseif (!empty($_GET['course_code'])) {
             $link->set_course_code(Database::escape_string($_GET['course_code']));
         }
         // disable this element if the link works with a dropdownlist
         // and if there are no links left
         if (!$link->needs_name_and_description() && count($link->get_all_links()) == '0') {
             $select->addoption($link->get_type_name(), $linktype, 'disabled');
         } else {
             $select->addoption($link->get_type_name(), $linktype);
         }
     }
     if (isset($this->extra)) {
         $this->setDefaults(array('select_link' => $this->extra));
     }
 }

function search_sessions($needle, $type)
{
    global $tbl_session_rel_access_url, $tbl_session, $user_id;
    $xajax_response = new xajaxResponse();
    $return = '';
    if (!empty($needle) && !empty($type)) {
        $needle = Database::escape_string($needle);
        $assigned_sessions_to_hrm = SessionManager::get_sessions_followed_by_drh($user_id);
        $assigned_sessions_id = array_keys($assigned_sessions_to_hrm);
        $without_assigned_sessions = '';
        if (count($assigned_sessions_id) > 0) {
            $without_assigned_sessions = " AND s.id NOT IN(" . implode(',', $assigned_sessions_id) . ")";
        }
        if (api_is_multiple_url_enabled()) {
            $sql = " SELECT s.id, s.name FROM {$tbl_session} s\n                        LEFT JOIN {$tbl_session_rel_access_url} a ON (s.id = a.session_id)\n                        WHERE  s.name LIKE '{$needle}%' {$without_assigned_sessions} AND access_url_id = " . api_get_current_access_url_id() . "";
        } else {
            $sql = "SELECT s.id, s.name FROM {$tbl_session} s\n                    WHERE  s.name LIKE '{$needle}%' {$without_assigned_sessions} ";
        }
        $rs = Database::query($sql);
        $return .= '<select class="form-control" id="origin" name="NoAssignedSessionsList[]" multiple="multiple" size="20">';
        while ($session = Database::fetch_array($rs)) {
            $return .= '<option value="' . $session['id'] . '" title="' . htmlspecialchars($session['name'], ENT_QUOTES) . '">' . $session['name'] . '</option>';
        }
        $return .= '</select>';
        $xajax_response->addAssign('ajax_list_sessions_multiple', 'innerHTML', api_utf8_encode($return));
    }
    return $xajax_response;
}

 /**
  * Search for a list of available courses by title or code, based on
  * a given string
  * @param string String to search for
  * @param int Deprecated param
  * @return string A formatted, xajax answer block
  * @assert () === false
  */
 function search_courses($needle, $id)
 {
     global $tbl_course;
     $xajax_response = new XajaxResponse();
     $return = '';
     if (!empty($needle)) {
         // xajax send utf8 datas... datas in db can be non-utf8 datas
         $charset = api_get_system_encoding();
         $needle = api_convert_encoding($needle, $charset, 'utf-8');
         $needle = Database::escape_string($needle);
         // search courses where username or firstname or lastname begins likes $needle
         $sql = 'SELECT code, title FROM ' . $tbl_course . ' u ' . ' WHERE (title LIKE "' . $needle . '%" ' . ' OR code LIKE "' . $needle . '%" ' . ' ) ' . ' ORDER BY title, code ' . ' LIMIT 11';
         $rs = Database::query($sql);
         $i = 0;
         while ($course = Database::fetch_array($rs)) {
             $i++;
             if ($i <= 10) {
                 $return .= '<a href="javascript: void(0);" onclick="javascript: add_user_to_url(\'' . addslashes($course['code']) . '\',\'' . addslashes($course['title']) . ' (' . addslashes($course['code']) . ')' . '\')">' . $course['title'] . ' (' . $course['code'] . ')</a><br />';
             } else {
                 $return .= '...<br />';
             }
         }
     }
     $xajax_response->addAssign('ajax_list_courses', 'innerHTML', api_utf8_encode($return));
     return $xajax_response;
 }

 /**
  *
  * @param int $user_id
  * @param string $api_key
  * @param string $api_service
  * @param string $api_end_point
  * @param int $validity_start_date
  * @param int $validity_end_date
  * @param string $description
  * @return AccessToken 
  */
 public static function create_token($user_id = null, $api_key = null, $api_service = null, $api_end_point = null, $validity_start_date = null, $validity_end_date = null, $description = '')
 {
     $time = time();
     $user_id = $user_id ? $user_id : Chamilo::user()->user_id();
     $api_key = $api_key ? $api_key : uniqid('', true);
     $api_service = $api_service ? $api_service : self::default_service();
     $api_end_point = $api_end_point ? $api_end_point : '';
     $validity_start_date = $validity_start_date ? $validity_start_date : $time;
     $validity_end_date = $validity_end_date ? $validity_end_date : self::end_of_time();
     $created_date = $time;
     $user_id = (int) $user_id;
     $api_key = Database::escape_string($api_key);
     $api_service = Database::escape_string($api_service);
     $api_end_point = Database::escape_string($api_end_point);
     $validity_start_date = date('Y-m-d H:i:s', $validity_start_date);
     $validity_end_date = date('Y-m-d H:i:s', $validity_end_date);
     $created_date = date('Y-m-d H:i:s', $created_date);
     $values = array();
     $values['user_id'] = $user_id;
     $values['api_key'] = $api_key;
     $values['api_service'] = $api_service;
     $values['api_end_point'] = $api_end_point;
     $values['validity_start_date'] = $validity_start_date;
     $values['validity_end_date'] = $validity_end_date;
     $values['created_date'] = $created_date;
     $table = Database::get_main_table(TABLE_MAIN_USER_API_KEY);
     $id = Database::insert($table, $values);
     return AccessToken::create($id, $user_id, $api_key);
 }

 /**
  * Search users by username, firstname or lastname, based on the given
  * search string
  * @param string Search string
  * @param int Deprecated param
  * @return string Xajax response block
  * @assert () === false
  */
 public static function search_users($needle, $id)
 {
     global $tbl_user, $tbl_access_url_rel_user;
     $xajax_response = new XajaxResponse();
     $return = '';
     if (!empty($needle)) {
         // xajax send utf8 datas... datas in db can be non-utf8 datas
         $charset = api_get_system_encoding();
         $needle = api_convert_encoding($needle, $charset, 'utf-8');
         $needle = Database::escape_string($needle);
         // search users where username or firstname or lastname begins likes $needle
         $order_clause = api_sort_by_first_name() ? ' ORDER BY firstname, lastname, username' : ' ORDER BY lastname, firstname, username';
         $sql = 'SELECT u.user_id, username, lastname, firstname FROM ' . $tbl_user . ' u ' . ' WHERE (username LIKE "' . $needle . '%" ' . ' OR firstname LIKE "' . $needle . '%" ' . ' OR lastname LIKE "' . $needle . '%") ' . $order_clause . ' LIMIT 11';
         $rs = Database::query($sql);
         $i = 0;
         while ($user = Database::fetch_array($rs)) {
             $i++;
             if ($i <= 10) {
                 $return .= '<a href="javascript: void(0);" onclick="javascript: add_user_to_url(\'' . addslashes($user['user_id']) . '\',\'' . api_get_person_name(addslashes($user['firstname']), addslashes($user['lastname'])) . ' (' . addslashes($user['username']) . ')' . '\')">' . api_get_person_name($user['firstname'], $user['lastname']) . ' (' . $user['username'] . ')</a><br />';
             } else {
                 $return .= '...<br />';
             }
         }
     }
     $xajax_response->addAssign('ajax_list_users', 'innerHTML', api_utf8_encode($return));
     return $xajax_response;
 }

function search_courses($needle, $type)
{
    global $tbl_course, $tbl_course_rel_access_url, $user_id;
    $xajax_response = new xajaxResponse();
    $return = '';
    if (!empty($needle) && !empty($type)) {
        // xajax send utf8 datas... datas in db can be non-utf8 datas
        $needle = Database::escape_string($needle);
        $assigned_courses_to_hrm = CourseManager::get_courses_followed_by_drh($user_id);
        $assigned_courses_code = array_keys($assigned_courses_to_hrm);
        foreach ($assigned_courses_code as &$value) {
            $value = "'" . $value . "'";
        }
        $without_assigned_courses = '';
        if (count($assigned_courses_code) > 0) {
            $without_assigned_courses = " AND c.code NOT IN(" . implode(',', $assigned_courses_code) . ")";
        }
        if (api_is_multiple_url_enabled()) {
            $sql = "SELECT c.code, c.title\n                    FROM {$tbl_course} c\n\t\t\t\t\tLEFT JOIN {$tbl_course_rel_access_url} a\n                    ON (a.c_id = c.id)\n                \tWHERE\n                \t\tc.code LIKE '{$needle}%' {$without_assigned_courses} AND\n                \t\taccess_url_id = " . api_get_current_access_url_id();
        } else {
            $sql = "SELECT c.code, c.title\n            \t\tFROM {$tbl_course} c\n                \tWHERE\n                \t\tc.code LIKE '{$needle}%'\n                \t\t{$without_assigned_courses} ";
        }
        $rs = Database::query($sql);
        $return .= '<select id="origin" name="NoAssignedCoursesList[]" multiple="multiple" size="20" style="width:340px;">';
        while ($course = Database::fetch_array($rs)) {
            $return .= '<option value="' . $course['code'] . '" title="' . htmlspecialchars($course['title'], ENT_QUOTES) . '">' . $course['title'] . ' (' . $course['code'] . ')</option>';
        }
        $return .= '</select>';
        $xajax_response->addAssign('ajax_list_courses_multiple', 'innerHTML', api_utf8_encode($return));
    }
    return $xajax_response;
}

 /**
  * Search sessions by name, based on a search string
  * @param string Search string
  * @param int Deprecated param
  * @return string Xajax response block
  * @assert () === false
  */
 function search_sessions($needle, $id)
 {
     global $tbl_session;
     $xajax_response = new XajaxResponse();
     $return = '';
     if (!empty($needle)) {
         // xajax send utf8 datas... datas in db can be non-utf8 datas
         $charset = api_get_system_encoding();
         $needle = api_convert_encoding($needle, $charset, 'utf-8');
         $needle = Database::escape_string($needle);
         // search sessiones where username or firstname or lastname begins likes $needle
         $sql = 'SELECT id, name FROM ' . $tbl_session . ' u
                 WHERE (name LIKE "' . $needle . '%")
                 ORDER BY name, id
                 LIMIT 11';
         $rs = Database::query($sql);
         $i = 0;
         while ($session = Database::fetch_array($rs)) {
             $i++;
             if ($i <= 10) {
                 $return .= '<a href="#" onclick="add_user_to_url(\'' . addslashes($session['id']) . '\',\'' . addslashes($session['name']) . ' (' . addslashes($session['id']) . ')' . '\')">' . $session['name'] . ' </a><br />';
             } else {
                 $return .= '...<br />';
             }
         }
     }
     $xajax_response->addAssign('ajax_list_courses', 'innerHTML', api_utf8_encode($return));
     return $xajax_response;
 }

/**
 * Get the classes to display on the current page.
 */
function get_class_data($from, $number_of_items, $column, $direction)
{
    $class_table = Database::get_main_table(TABLE_MAIN_CLASS);
    $class_user_table = Database::get_main_table(TABLE_MAIN_CLASS_USER);
    $courseId = api_get_course_int_id();
    $em = Database::getManager();
    $res = $em->getRepository('ChamiloCoreBundle:CourseRelClass')->findBy(['courseId' => $courseId]);
    $subscribed_classes = array();
    foreach ($res as $obj) {
        $subscribed_classes[] = $obj->getClassId();
    }
    $sql = "SELECT\n                c.id AS col0,\n                c.name   AS col1,\n                COUNT(cu.user_id) AS col2,\n                c.id AS col3\n            FROM {$class_table} c ";
    $sql .= " LEFT JOIN {$class_user_table} cu ON cu.class_id = c.id";
    $sql .= " WHERE 1 = 1";
    if (isset($_GET['keyword'])) {
        $keyword = Database::escape_string(trim($_GET['keyword']));
        $sql .= " AND (c.name LIKE '%" . $keyword . "%')";
    }
    if (count($subscribed_classes) > 0) {
        $sql .= " AND c.id NOT IN ('" . implode("','", $subscribed_classes) . "')";
    }
    $sql .= " GROUP BY c.id, c.name ";
    $sql .= " ORDER BY col{$column} {$direction} ";
    $sql .= " LIMIT {$from},{$number_of_items}";
    $res = Database::query($sql);
    $classes = array();
    while ($class = Database::fetch_row($res)) {
        $classes[] = $class;
    }
    return $classes;
}

/**
 *
 */
function get_course_usage($course_code, $session_id = 0)
{
    $table = Database::get_main_table(TABLE_MAIN_COURSE);
    $course_code = Database::escape_string($course_code);
    $sql = "SELECT * FROM {$table} WHERE code='" . $course_code . "'";
    $res = Database::query($sql);
    $course = Database::fetch_object($res);
    // Learnpaths
    $table = Database::get_course_table(TABLE_LP_MAIN);
    $usage[] = array(get_lang(ucfirst(TOOL_LEARNPATH)), CourseManager::count_rows_course_table($table, $session_id, $course->id));
    // Forums
    $table = Database::get_course_table(TABLE_FORUM);
    $usage[] = array(get_lang('Forums'), CourseManager::count_rows_course_table($table, $session_id, $course->id));
    // Quizzes
    $table = Database::get_course_table(TABLE_QUIZ_TEST);
    $usage[] = array(get_lang(ucfirst(TOOL_QUIZ)), CourseManager::count_rows_course_table($table, $session_id, $course->id));
    // Documents
    $table = Database::get_course_table(TABLE_DOCUMENT);
    $usage[] = array(get_lang(ucfirst(TOOL_DOCUMENT)), CourseManager::count_rows_course_table($table, $session_id, $course->id));
    // Groups
    $table = Database::get_course_table(TABLE_GROUP);
    $usage[] = array(get_lang(ucfirst(TOOL_GROUP)), CourseManager::count_rows_course_table($table, $session_id, $course->id));
    // Calendar
    $table = Database::get_course_table(TABLE_AGENDA);
    $usage[] = array(get_lang(ucfirst(TOOL_CALENDAR_EVENT)), CourseManager::count_rows_course_table($table, $session_id, $course->id));
    // Link
    $table = Database::get_course_table(TABLE_LINK);
    $usage[] = array(get_lang(ucfirst(TOOL_LINK)), CourseManager::count_rows_course_table($table, $session_id, $course->id));
    // Announcements
    $table = Database::get_course_table(TABLE_ANNOUNCEMENT);
    $usage[] = array(get_lang(ucfirst(TOOL_ANNOUNCEMENT)), CourseManager::count_rows_course_table($table, $session_id, $course->id));
    return $usage;
}

 /**
  * Get document information
  */
 private function get_information($course_id, $link_id)
 {
     $course_information = api_get_course_info($course_id);
     $course_id = $course_information['real_id'];
     $course_id_alpha = $course_information['id'];
     if (!empty($course_information)) {
         $item_property_table = Database::get_course_table(TABLE_ITEM_PROPERTY);
         $link_id = Database::escape_string($link_id);
         $sql = "SELECT insert_user_id FROM {$item_property_table}\n              \t\tWHERE ref = {$link_id} AND tool = '" . TOOL_LINK . "' AND c_id = {$course_id}\n              \t\tLIMIT 1";
         $name = get_lang('Links');
         $url = api_get_path(WEB_PATH) . 'main/link/link.php?cidReq=%s';
         $url = sprintf($url, $course_id_alpha);
         // Get the image path
         $thumbnail = api_get_path(WEB_IMG_PATH) . 'link.gif';
         $image = $thumbnail;
         //FIXME: use big images
         // get author
         $author = '';
         $item_result = Database::query($sql);
         if ($row = Database::fetch_array($item_result)) {
             $user_data = api_get_user_info($row['insert_user_id']);
             $author = api_get_person_name($user_data['firstName'], $user_data['lastName']);
         }
         return array($thumbnail, $image, $name, $author, $url);
     } else {
         return array();
     }
 }