PHP db_real_escape函数代码示例

            ON (status.id = ticket.status_id) ' . ' LEFT JOIN ' . TABLE_PREFIX . 'ticket__cdata cdata ON (cdata.ticket_id = ticket.ticket_id)' . ' LEFT JOIN ' . DEPT_TABLE . ' dept ON (ticket.dept_id=dept.dept_id) ' . ' LEFT JOIN ' . TICKET_COLLABORATOR_TABLE . ' collab
        ON (collab.ticket_id = ticket.ticket_id
                AND collab.user_id =' . $thisclient->getId() . ' )';
$qwhere = sprintf(' WHERE ( ticket.user_id=%d OR collab.user_id=%d )', $thisclient->getId(), $thisclient->getId());
$states = array('open' => 'open', 'closed' => 'closed');
if ($status && isset($states[$status])) {
    $qwhere .= ' AND status.state=' . db_input($states[$status]);
}
$search = $_REQUEST['a'] == 'search' && $_REQUEST['q'];
if ($search) {
    $qs += array('a' => $_REQUEST['a'], 'q' => $_REQUEST['q']);
    if (is_numeric($_REQUEST['q'])) {
        $qwhere .= " AND ticket.`number` LIKE '{$queryterm}%'";
    } else {
        //Deep search!
        $queryterm = db_real_escape($_REQUEST['q'], false);
        //escape the term ONLY...no quotes.
        $qwhere .= ' AND ( ' . " cdata.subject LIKE '%{$queryterm}%'" . " OR thread.body LIKE '%{$queryterm}%'" . ' ) ';
        $deep_search = true;
        //Joins needed for search
        $qfrom .= ' LEFT JOIN ' . TICKET_THREAD_TABLE . ' thread ON (' . 'ticket.ticket_id=thread.ticket_id AND thread.thread_type IN ("M","R"))';
    }
}
TicketForm::ensureDynamicDataView();
$total = db_count('SELECT count(DISTINCT ticket.ticket_id) ' . $qfrom . ' ' . $qwhere);
$page = $_GET['p'] && is_numeric($_GET['p']) ? $_GET['p'] : 1;
$pageNav = new Pagenate($total, $page, PAGE_LIMIT);
$qstr = '&' . Http::build_query($qs);
$qs += array('sort' => $_REQUEST['sort'], 'order' => $_REQUEST['order']);
$pageNav->setURL('tickets.php', $qs);
//more stuff...

    Released under the GNU General Public License WITHOUT ANY WARRANTY.
    See LICENSE.TXT for details.

    vim: expandtab sw=4 ts=4 sts=4:
    $Id: $
**********************************************************************/
require 'staff.inc.php';
$nav->setTabActive('directory');
$nav->addSubMenu(array('desc' => 'Staff Members', 'href' => 'directory.php', 'iconclass' => 'staff'));
$WHERE = ' WHERE isvisible=1 ';
$sql = ' SELECT staff.staff_id,staff.dept_id, firstname,lastname,email,phone,phone_ext,mobile,dept_name,onvacation ' . ' FROM ' . STAFF_TABLE . ' staff LEFT JOIN  ' . DEPT_TABLE . ' USING(dept_id)';
if ($_POST && $_POST['a'] == 'search') {
    $searchTerm = $_POST['query'];
    if ($searchTerm) {
        $query = db_real_escape($searchTerm, false);
        //escape the term ONLY...no quotes.
        if (is_numeric($searchTerm)) {
            $WHERE .= " AND staff.phone LIKE '%{$query}%'";
        } elseif (strpos($searchTerm, '@') && Validator::is_email($searchTerm)) {
            $WHERE .= " AND staff.email='{$query}'";
        } else {
            $WHERE .= " AND ( staff.email LIKE '%{$query}%'" . " OR staff.lastname LIKE '%{$query}%'" . " OR staff.firstname LIKE '%{$query}%'" . ' ) ';
        }
    }
    if ($_POST['dept'] && is_numeric($_POST['dept'])) {
        $WHERE .= ' AND staff.dept_id=' . db_input($_POST['dept']);
    }
}
$users = db_query("{$sql} {$WHERE} ORDER BY lastname,firstname");
//Render the page.

$where = '';
//make sure the search query is 3 chars min...defaults to no query with warning message
if ($_REQUEST['a'] == 'search') {
    if (!$_REQUEST['query'] || strlen($_REQUEST['query']) < 3) {
        $errors['err'] = 'Search term must be more than 3 chars';
    } else {
        //Do the search
        $search = true;
        $qstr .= '&a=' . urlencode($_REQUEST['a']);
        $qstr .= '&query=' . urlencode($_REQUEST['query']);
        $searchTerm = trim($_REQUEST['query']);
        if (strpos($searchTerm, '@') && Validator::is_email($searchTerm)) {
            //pulling all tricks!
            $where = ' WHERE email=' . db_input($searchTerm);
        } else {
            $where = ' WHERE email LIKE \'%' . db_real_escape($searchTerm, false) . '%\'';
        }
    }
}
//I admit this crap sucks...but who cares??
$sortOptions = array('date' => 'added', 'email' => 'email');
$orderWays = array('DESC' => 'DESC', 'ASC' => 'ASC');
//Sorting options...
if ($_REQUEST['sort']) {
    $order_column = $sortOptions[$_REQUEST['sort']];
}
if ($_REQUEST['order']) {
    $order = $orderWays[$_REQUEST['order']];
}
$order_column = $order_column ? $order_column : 'added';
$order = $order ? $order : 'DESC';

     */
    if(!($cfg->showAssignedTickets() || $thisstaff->showAssignedTickets())) {
        $qwhere.=' AND ticket.staff_id=0 '; //XXX: NOT factoring in team assignments - only staff assignments.
        $showassigned=false; //Not showing Assigned To column since assigned tickets are not part of open queue
    }
}

//Search?? Somebody...get me some coffee
$deep_search=false;
$order_by=$order=null;
if($search):
    $qs += array('a' => $_REQUEST['a'], 't' => $_REQUEST['t']);
    //query
    if($searchTerm){
        $qs += array('query' => $searchTerm);
        $queryterm=db_real_escape($searchTerm,false); //escape the term ONLY...no quotes.
        if (is_numeric($searchTerm)) {
            $qwhere.=" AND ticket.`number` LIKE '$queryterm%'";
        } elseif (strpos($searchTerm,'@') && Validator::is_email($searchTerm)) {
            //pulling all tricks!
            # XXX: What about searching for email addresses in the body of
            #      the thread message
            $qwhere.=" AND email.address='$queryterm'";
        } else {//Deep search!
            //This sucks..mass scan! search anything that moves!
            require_once(INCLUDE_DIR.'ajax.tickets.php');

            $tickets = TicketsAjaxApi::_search(array('query'=>$queryterm));
            if (count($tickets)) {
                $ticket_ids = implode(',',db_input($tickets));
                $qwhere .= ' AND ticket.ticket_id IN ('.$ticket_ids.')';

 function _search($req)
 {
     global $thisstaff, $cfg;
     $result = array();
     $select = 'SELECT ticket.ticket_id';
     $from = ' FROM ' . TICKET_TABLE . ' ticket ';
     //Access control.
     $where = ' WHERE ( (ticket.staff_id=' . db_input($thisstaff->getId()) . ' AND ticket.status="open" )';
     if (($teams = $thisstaff->getTeams()) && count(array_filter($teams))) {
         $where .= ' OR (ticket.team_id IN (' . implode(',', db_input(array_filter($teams))) . ' ) AND ticket.status="open")';
     }
     if (!$thisstaff->showAssignedOnly() && ($depts = $thisstaff->getDepts())) {
         $where .= ' OR ticket.dept_id IN (' . implode(',', db_input($depts)) . ')';
     }
     $where .= ' ) ';
     //Department
     if ($req['deptId']) {
         $where .= ' AND ticket.dept_id=' . db_input($req['deptId']);
     }
     //Help topic
     if ($req['topicId']) {
         $where .= ' AND ticket.topic_id=' . db_input($req['topicId']);
     }
     //Status
     switch (strtolower($req['status'])) {
         case 'open':
             $where .= ' AND ticket.status="open" ';
             break;
         case 'answered':
             $where .= ' AND ticket.status="open" AND ticket.isanswered=1 ';
             break;
         case 'overdue':
             $where .= ' AND ticket.status="open" AND ticket.isoverdue=1 ';
             break;
         case 'closed':
             $where .= ' AND ticket.status="closed" ';
             break;
     }
     //Assignee
     if (isset($req['assignee']) && strcasecmp($req['status'], 'closed')) {
         $id = preg_replace("/[^0-9]/", "", $req['assignee']);
         $assignee = $req['assignee'];
         $where .= ' AND ( ( ticket.status="open" ';
         if ($assignee[0] == 't') {
             $where .= ' AND ticket.team_id=' . db_input($id);
         } elseif ($assignee[0] == 's') {
             $where .= ' AND ticket.staff_id=' . db_input($id);
         } elseif (is_numeric($id)) {
             $where .= ' AND ticket.staff_id=' . db_input($id);
         }
         $where .= ')';
         if ($req['staffId'] && !$req['status']) {
             //Assigned TO + Closed By
             $where .= ' OR (ticket.staff_id=' . db_input($req['staffId']) . ' AND ticket.status="closed") ';
         } elseif (isset($req['staffId'])) {
             // closed by any
             $where .= ' OR ticket.status="closed" ';
         }
         $where .= ' ) ';
     } elseif ($req['staffId']) {
         $where .= ' AND (ticket.staff_id=' . db_input($req['staffId']) . ' AND ticket.status="closed") ';
     }
     //dates
     $startTime = $req['startDate'] && strlen($req['startDate']) >= 8 ? strtotime($req['startDate']) : 0;
     $endTime = $req['endDate'] && strlen($req['endDate']) >= 8 ? strtotime($req['endDate']) : 0;
     if ($startTime && $startTime > time() or $startTime > $endTime && $endTime > 0) {
         $startTime = $endTime = 0;
     }
     if ($startTime) {
         $where .= ' AND ticket.created>=FROM_UNIXTIME(' . $startTime . ')';
     }
     if ($endTime) {
         $where .= ' AND ticket.created<=FROM_UNIXTIME(' . $endTime . ')';
     }
     //Query
     $joins = array();
     if ($req['query']) {
         $queryterm = db_real_escape($req['query'], false);
         // Setup sets of joins and queries
         $joins[] = array('from' => 'LEFT JOIN ' . TICKET_THREAD_TABLE . ' thread ON (ticket.ticket_id=thread.ticket_id )', 'where' => "thread.title LIKE '%{$queryterm}%' OR thread.body LIKE '%{$queryterm}%'");
         $joins[] = array('from' => 'LEFT JOIN ' . FORM_ENTRY_TABLE . ' tentry ON (tentry.object_id = ticket.ticket_id AND tentry.object_type="T")
                 LEFT JOIN ' . FORM_ANSWER_TABLE . ' tans ON (tans.entry_id = tentry.id AND tans.value_id IS NULL)', 'where' => "tans.value LIKE '%{$queryterm}%'");
         $joins[] = array('from' => 'LEFT JOIN ' . FORM_ENTRY_TABLE . ' uentry ON (uentry.object_id = ticket.user_id
                AND uentry.object_type="U")
                LEFT JOIN ' . FORM_ANSWER_TABLE . ' uans ON (uans.entry_id = uentry.id
                AND uans.value_id IS NULL)
                LEFT JOIN ' . USER_TABLE . ' user ON (ticket.user_id = user.id)
                LEFT JOIN ' . USER_EMAIL_TABLE . ' uemail ON (user.id = uemail.user_id)', 'where' => "uemail.address LIKE '%{$queryterm}%' OR user.name LIKE '%{$queryterm}%' OR uans.value LIKE '%{$queryterm}%'");
     }
     // Dynamic fields
     $cdata_search = false;
     foreach (TicketForm::getInstance()->getFields() as $f) {
         if (isset($req[$f->getFormName()]) && ($val = $req[$f->getFormName()])) {
             $name = $f->get('name') ? $f->get('name') : 'field_' . $f->get('id');
             if ($f->getImpl()->hasIdValue() && is_numeric($val)) {
                 $cwhere = "cdata.`{$name}_id` = " . db_input($val);
             } else {
                 $cwhere = "cdata.`{$name}` LIKE '%" . db_real_escape($val) . "%'";
             }
             $where .= ' AND (' . $cwhere . ')';
//.........这里部分代码省略.........

function db_input($param, $quote = true)
{
    //is_numeric doesn't work all the time...9e8 is considered numeric..which is correct...but not expected.
    if ($param && preg_match("/^\\d+(\\.\\d+)?\$/", $param)) {
        return $param;
    }
    if ($param && is_array($param)) {
        reset($param);
        while (list($key, $value) = each($s)) {
            $param[$key] = db_input($value, $quote);
        }
        return $param;
    }
    return db_real_escape($param, $quote);
}

function db_input($var, $quote = true)
{
    if (is_array($var)) {
        return array_map('db_input', $var, array_fill(0, count($var), $quote));
    } elseif ($var && preg_match("/^(?:\\d+\\.\\d+|[1-9]\\d*)\$/S", $var)) {
        return $var;
    }
    return db_real_escape($var, $quote);
}

//.........这里部分代码省略.........
                 $where .= ' AND ticket.isoverdue =1 ';
                 $criteria['isoverdue'] = 1;
                 $criteria['state'] = 'open';
                 $where .= ' AND status.state="open" ';
                 break;
         }
     }
     //Assignee
     if ($req['assignee'] && strcasecmp($req['status'], 'closed')) {
         # assigned-to
         $id = preg_replace("/[^0-9]/", "", $req['assignee']);
         $assignee = $req['assignee'];
         $where .= ' AND ( ( status.state="open" ';
         if ($assignee[0] == 't') {
             $where .= ' AND ticket.team_id=' . db_input($id);
             $criteria['team_id'] = $id;
         } elseif ($assignee[0] == 's' || is_numeric($id)) {
             $where .= ' AND ticket.staff_id=' . db_input($id);
             $criteria['staff_id'] = $id;
         }
         $where .= ')';
         if ($req['staffId'] && !$req['status']) {
             //Assigned TO + Closed By
             $where .= ' OR (ticket.staff_id=' . db_input($req['staffId']) . ' AND status.state IN("closed")) ';
         } elseif ($req['staffId']) {
             // closed by any
             $where .= ' OR status.state IN("closed") ';
         }
         $where .= ' ) ';
     } elseif ($req['staffId']) {
         # closed-by
         $where .= ' AND (ticket.staff_id=' . db_input($req['staffId']) . ' AND
             status.state IN("closed")) ';
         $criteria['state__in'] = array('closed');
         $criteria['staff_id'] = $req['staffId'];
     }
     //dates
     $startTime = $req['startDate'] && strlen($req['startDate']) >= 8 ? strtotime($req['startDate']) : 0;
     $endTime = $req['endDate'] && strlen($req['endDate']) >= 8 ? strtotime($req['endDate']) : 0;
     if ($endTime) {
         // $endTime should be the last second of the day, not the first like $startTime
         $endTime += 60 * 60 * 24 - 1;
     }
     if ($startTime && $startTime > time() or $startTime > $endTime && $endTime > 0) {
         $startTime = $endTime = 0;
     }
     if ($startTime) {
         $where .= ' AND ticket.created>=FROM_UNIXTIME(' . $startTime . ')';
         $criteria['created__gte'] = $startTime;
     }
     if ($endTime) {
         $where .= ' AND ticket.created<=FROM_UNIXTIME(' . $endTime . ')';
         $criteria['created__lte'] = $startTime;
     }
     // Dynamic fields
     $cdata_search = false;
     foreach (TicketForm::getInstance()->getFields() as $f) {
         if (isset($req[$f->getFormName()]) && ($val = $req[$f->getFormName()])) {
             $name = $f->get('name') ? $f->get('name') : 'field_' . $f->get('id');
             if (is_array($val)) {
                 $cwhere = '(' . implode(' OR ', array_map(function ($k) use($name) {
                     return sprintf('FIND_IN_SET(%s, `%s`)', db_input($k), $name);
                 }, $val)) . ')';
                 $criteria["cdata.{$name}"] = $val;
             } else {
                 $cwhere = "cdata.`{$name}` LIKE '%" . db_real_escape($val) . "%'";
                 $criteria["cdata.{$name}"] = $val;
             }
             $where .= ' AND (' . $cwhere . ')';
             $cdata_search = true;
         }
     }
     if ($cdata_search) {
         $from .= 'LEFT JOIN ' . TABLE_PREFIX . 'ticket__cdata ' . " cdata ON (cdata.ticket_id = ticket.ticket_id)";
     }
     //Query
     $joins = array();
     if ($req['query']) {
         // Setup sets of joins and queries
         if ($s = $ost->searcher) {
             return $s->find($req['query'], $criteria, 'Ticket');
         }
     }
     $sections = array();
     foreach ($joins as $j) {
         $sections[] = "{$select} {$from} {$j['from']} {$where} AND ({$j['where']})";
     }
     if (!$joins) {
         $sections[] = "{$select} {$from} {$where}";
     }
     $sql = implode(' union ', $sections);
     if (!($res = db_query($sql))) {
         return TicketForm::dropDynamicDataView();
     }
     $tickets = array();
     while ($row = db_fetch_row($res)) {
         $tickets[] = $row[0];
     }
     return $tickets;
 }

 function search()
 {
     global $thisstaff, $cfg;
     $result = array();
     $select = 'SELECT count( DISTINCT ticket.ticket_id) as tickets ';
     $from = ' FROM ' . TICKET_TABLE . ' ticket ';
     $where = ' WHERE 1 ';
     //Access control.
     $where .= ' AND ( ticket.staff_id=' . db_input($thisstaff->getId());
     if (($teams = $thisstaff->getTeams()) && count(array_filter($teams))) {
         $where .= ' OR ticket.team_id IN(' . implode(',', db_input(array_filter($teams))) . ')';
     }
     if (!$thisstaff->showAssignedOnly() && ($depts = $thisstaff->getDepts())) {
         $where .= ' OR ticket.dept_id IN (' . implode(',', db_input($depts)) . ')';
     }
     $where .= ' ) ';
     //Department
     if ($_REQUEST['deptId']) {
         $where .= ' AND ticket.dept_id=' . db_input($_REQUEST['deptId']);
     }
     //Help topic
     if ($_REQUEST['topicId']) {
         $where .= ' AND ticket.topic_id=' . db_input($_REQUEST['topicId']);
     }
     //Status
     switch (strtolower($_REQUEST['status'])) {
         case 'open':
             $where .= ' AND ticket.status="open" ';
             break;
         case 'answered':
             $where .= ' AND ticket.status="open" AND ticket.isanswered=1 ';
             break;
         case 'overdue':
             $where .= ' AND ticket.status="open" AND ticket.isoverdue=1 ';
             break;
         case 'closed':
             $where .= ' AND ticket.status="closed" ';
             break;
     }
     //Assignee
     if (isset($_REQUEST['assignee']) && strcasecmp($_REQUEST['status'], 'closed')) {
         $id = preg_replace("/[^0-9]/", "", $_REQUEST['assignee']);
         $assignee = $_REQUEST['assignee'];
         $where .= ' AND ( ( ticket.status="open" ';
         if ($assignee[0] == 't') {
             $where .= ' AND ticket.team_id=' . db_input($id);
         } elseif ($assignee[0] == 's') {
             $where .= ' AND ticket.staff_id=' . db_input($id);
         } elseif (is_numeric($id)) {
             $where .= ' AND ticket.staff_id=' . db_input($id);
         }
         $where .= ')';
         if ($_REQUEST['staffId'] && !$_REQUEST['status']) {
             //Assigned TO + Closed By
             $where .= ' OR (ticket.staff_id=' . db_input($_REQUEST['staffId']) . ' AND ticket.status="closed") ';
         } elseif (isset($_REQUEST['staffId'])) {
             // closed by any
             $where .= ' OR ticket.status="closed" ';
         }
         $where .= ' ) ';
     } elseif ($_REQUEST['staffId']) {
         $where .= ' AND (ticket.staff_id=' . db_input($_REQUEST['staffId']) . ' AND ticket.status="closed") ';
     }
     //dates
     $startTime = $_REQUEST['startDate'] && strlen($_REQUEST['startDate']) >= 8 ? strtotime($_REQUEST['startDate']) : 0;
     $endTime = $_REQUEST['endDate'] && strlen($_REQUEST['endDate']) >= 8 ? strtotime($_REQUEST['endDate']) : 0;
     if ($startTime && $startTime > time() or $startTime > $endTime && $endTime > 0) {
         $startTime = $endTime = 0;
     }
     if ($startTime) {
         $where .= ' AND ticket.created>=FROM_UNIXTIME(' . $startTime . ')';
     }
     if ($endTime) {
         $where .= ' AND ticket.created<=FROM_UNIXTIME(' . $endTime . ')';
     }
     //Query
     if ($_REQUEST['query']) {
         $queryterm = db_real_escape($_REQUEST['query'], false);
         $from .= ' LEFT JOIN ' . TICKET_THREAD_TABLE . ' thread ON (ticket.ticket_id=thread.ticket_id )';
         $where .= " AND (  ticket.email LIKE '%{$queryterm}%'" . " OR ticket.name LIKE '%{$queryterm}%'" . " OR ticket.subject LIKE '%{$queryterm}%'" . " OR thread.title LIKE '%{$queryterm}%'" . " OR thread.body LIKE '%{$queryterm}%'" . ' )';
     }
     $sql = "{$select} {$from} {$where}";
     if ($tickets = db_result(db_query($sql))) {
         $result['success'] = sprintf("Search criteria matched %s - <a href='tickets.php?%s'>view</a>", $tickets > 1 ? "{$tickets} tickets" : "{$tickets} ticket", str_replace(array('&amp;', '&'), array('&', '&amp;'), $_SERVER['QUERY_STRING']));
     } else {
         $result['fail'] = 'No tickets found matching your search criteria.';
     }
     return $this->json_encode($result);
 }