PHP iaSanitize::sql方法代码示例

 public function gridRead($params, $columns, array $filterParams = array(), array $persistentConditions = array())
 {
     $params || ($params = array());
     $start = isset($params['start']) ? (int) $params['start'] : 0;
     $limit = isset($params['limit']) ? (int) $params['limit'] : 15;
     $sort = $params['sort'];
     $dir = in_array($params['dir'], array(iaDb::ORDER_ASC, iaDb::ORDER_DESC)) ? $params['dir'] : iaDb::ORDER_ASC;
     $order = $sort && $dir ? " ORDER BY `{$sort}` {$dir}" : '';
     $where = $values = array();
     foreach ($filterParams as $name => $type) {
         if (isset($params[$name]) && $params[$name]) {
             $value = iaSanitize::sql($params[$name]);
             switch ($type) {
                 case 'equal':
                     $where[] = sprintf('`%s` = :%s', $name, $name);
                     $values[$name] = $value;
                     break;
                 case 'like':
                     $where[] = sprintf('`%s` LIKE :%s', $name, $name);
                     $values[$name] = '%' . $value . '%';
             }
         }
     }
     $where = array_merge($where, $persistentConditions);
     $where || ($where[] = iaDb::EMPTY_CONDITION);
     $where = implode(' AND ', $where);
     $this->_iaDb->bind($where, $values);
     if (is_array($columns)) {
         $columns = array_merge(array('id', 'update' => 1, 'delete' => 1), $columns);
     }
     return array('data' => $this->_iaDb->all($columns, $where . $order, $start, $limit), 'total' => (int) $this->_iaDb->one(iaDb::STMT_COUNT_ROWS, $where));
 }

 protected function _modifyGridParams(&$conditions, &$values)
 {
     if (!empty($_GET['text'])) {
         $conditions[] = '(`title` LIKE :text OR `body` LIKE :text)';
         $values['text'] = '%' . iaSanitize::sql($_GET['text']) . '%';
     }
 }

 protected function _modifyGridParams(&$conditions, &$values)
 {
     if (isset($_GET['item']) && $_GET['item']) {
         $value = 'core' == strtolower($_GET['item']) ? '' : iaSanitize::sql($_GET['item']);
         $conditions[] = '`extras` = :extras';
         $values['extras'] = $value;
     }
 }

 protected function _gridRead($params)
 {
     $action = 1 == count($this->_iaCore->requestPath) ? $this->_iaCore->requestPath[0] : null;
     switch ($action) {
         case 'items':
             $output = array('data' => null);
             if ($items = $this->_iaCore->factory('item')->getItems(true)) {
                 foreach ($items as $key => $item) {
                     $output['data'][] = array('title' => iaLanguage::get($item), 'value' => $item);
                 }
             }
             break;
         case 'plans':
             $output = array('data' => null);
             $stmt = '';
             if (!isset($params['itemname']) || isset($params['itemname']) && iaUsers::getItemName() == $params['itemname']) {
                 $stmt = iaDb::convertIds(iaUsers::getItemName(), 'item');
                 $output['data'][] = array('title' => iaLanguage::get('funds'), 'value' => 0);
             } elseif (!empty($params['itemname'])) {
                 $stmt = iaDb::convertIds($params['itemname'], 'item');
             }
             $this->_iaCore->factory('plan');
             if ($planIds = $this->_iaDb->onefield(iaDb::ID_COLUMN_SELECTION, $stmt, null, null, iaPlan::getTable())) {
                 foreach ($planIds as $planId) {
                     $output['data'][] = array('title' => iaLanguage::get('plan_title_' . $planId), 'value' => $planId);
                 }
             }
             break;
         case 'gateways':
             $output = array('data' => null);
             if ($items = $this->getHelper()->getPaymentGateways()) {
                 foreach ($items as $name => $title) {
                     $output['data'][] = array('value' => $name, 'title' => $title);
                 }
             }
             break;
         case 'members':
             $output = array('data' => null);
             if (!empty($params['query'])) {
                 $where[] = 'CONCAT(`username`, `fullname`) LIKE :username';
                 $values['username'] = '%' . iaSanitize::sql($params['query']) . '%';
             }
             $where || ($where[] = iaDb::EMPTY_CONDITION);
             $where = implode(' AND ', $where);
             $this->_iaDb->bind($where, $values);
             if ($members = $this->_iaDb->all(array('id', 'username', 'fullname'), $where, null, null, iaUsers::getTable())) {
                 foreach ($members as $member) {
                     $output['data'][] = array('title' => $member['username'], 'value' => $member['id']);
                 }
             }
             break;
         default:
             $output = parent::_gridRead($params);
     }
     return $output;
 }

 private function _deleteFile($params)
 {
     $result = array('error' => true, 'message' => iaLanguage::get('invalid_parameters'));
     $item = isset($params['item']) ? iaSanitize::sql($params['item']) : null;
     $field = isset($params['field']) ? iaSanitize::sql($params['field']) : null;
     $path = isset($params['path']) ? iaSanitize::sql($params['path']) : null;
     $itemId = isset($params['itemid']) ? (int) $params['itemid'] : null;
     if ($itemId && $item && $field && $path) {
         $tableName = $this->_iaCore->factory('item')->getItemTable($item);
         $itemValue = $this->_iaDb->one($field, iaDb::convertIds($itemId), $tableName);
         $iaAcl = $this->_iaCore->factory('acl');
         if ($iaAcl->isAdmin() && $itemValue) {
             $pictures = $itemValue[1] == ':' ? unserialize($itemValue) : $itemValue;
             $key = null;
             if (is_array($pictures)) {
                 if ($primitive = !is_array($pictures[key($pictures)])) {
                     $pictures = array($pictures);
                 }
                 foreach ($pictures as $k => $v) {
                     if ($path == $v['path']) {
                         $key = $k;
                         break;
                     }
                 }
                 if (!is_null($key)) {
                     unset($pictures[$key]);
                 }
                 $newItemValue = $primitive ? '' : serialize($pictures);
             } else {
                 // single image
                 $newItemValue = '';
                 if ($pictures == $path) {
                     $key = true;
                 }
             }
             if (!is_null($key)) {
                 if ($this->_iaCore->factory('picture')->delete($path)) {
                     if ($this->_iaDb->update(array($field => $newItemValue), iaDb::convertIds($itemId), null, $tableName)) {
                         if (iaUsers::getItemName() == $item) {
                             // update current profile data
                             if ($itemId == iaUsers::getIdentity()->id) {
                                 iaUsers::reloadIdentity();
                             }
                         }
                     }
                     $result['error'] = false;
                     $result['message'] = iaLanguage::get('deleted');
                 } else {
                     $result['message'] = iaLanguage::get('error');
                 }
             }
         }
     }
     return $result;
 }

function lyrics_search($aQuery, $aFields, $aStart, $aLimit, &$aNumAll, $aWhere = '', $cond = 'AND')
{
    $iaCore =& iaCore::instance();
    $iaLyric = $iaCore->factoryPackage('lyric', 'lyrics');
    $ret = array();
    $match = array();
    // additional fields
    if ($aFields && is_array($aFields)) {
        foreach ($aFields as $fname => $data) {
            if ('LIKE' == $data['cond']) {
                $data['val'] = "%{$data['val']}%";
            }
            // for multiple values, like combo or checkboxes
            if (is_array($data['val'])) {
                if ('!=' == $data['cond']) {
                    $data['cond'] = count($data['val']) > 1 ? 'NOT IN' : '!=';
                } else {
                    $data['cond'] = count($data['val']) > 1 ? 'IN' : '=';
                }
                $data['val'] = count($data['val']) > 1 ? '(' . implode(',', $data['val']) . ')' : array_shift($data['val']);
            } else {
                if (preg_match('/^(\\d+)\\s*-\\s*(\\d+)$/', $data['val'], $range)) {
                    // search in range
                    $data['cond'] = sprintf('BETWEEN %d AND %d', $range[1], $range[2]);
                    $data['val'] = '';
                } else {
                    $data['val'] = "'" . iaSanitize::sql($data['val']) . "'";
                }
            }
            $match[] = "t1.`{$fname}` {$data['cond']} {$data['val']} ";
        }
    }
    $lyrics = array();
    $lyrics = $match ? $iaLyric->getSearchLyrics($aStart, $aLimit, ' AND (' . implode(' ' . $cond . ' ', $match) . ')') : array();
    $aNumAll += $iaCore->iaDb->foundRows();
    foreach ($lyrics as $lyric) {
        $iaCore->iaSmarty->assign('lyric', $lyric);
        $lyricinfo = $iaLyric->goToItem(array('item' => $lyric));
        //$ret[] = $iaCore->iaSmarty->fetch(IA_PACKAGES . 'lyrics/templates/common/brief_article.tpl');
        $ret[] = sprintf('<p><a href="%s">%s</a></p>', $lyricinfo[0], $lyric['title']);
    }
    return $ret;
}

 /**
  * Internal utility function used to generate SET stmt
  *
  * @param array $values values to be set checking by type
  * @param array $rawValues values to be set without processing
  *
  * @return string
  */
 protected function _wrapValues($values, $rawValues)
 {
     $result = '';
     // no need for further processing
     if (empty($values) && empty($rawValues)) {
         return $result;
     }
     $array = array();
     if (is_array($values)) {
         foreach ($values as $columnName => $value) {
             $pattern = "`%s` = '%s'";
             switch (true) {
                 case is_bool($value):
                     $pattern = '`%s` = %s';
                     $value = $value ? 1 : 0;
                     break;
                 case is_null($value):
                     $pattern = '`%s` = %s';
                     $value = 'NULL';
                     break;
                 case is_scalar($value):
                     $value = iaSanitize::sql($value);
                     break;
                 default:
                     // arrays, objects & resources are now actually ignored
                     continue;
             }
             $array[] = sprintf($pattern, $columnName, $value);
         }
     }
     if (is_array($rawValues) && $rawValues) {
         foreach ($rawValues as $field => $value) {
             $array[] = "`{$field}` = {$value}";
         }
     }
     $result = implode(', ', $array);
     return $result;
 }

function searchByPages($query, &$results)
{
    $iaCore = iaCore::instance();
    $iaDb =& $iaCore->iaDb;
    $iaSmarty =& $iaCore->iaView->iaSmarty;
    $iaPage = $iaCore->factory('page', iaCore::FRONT);
    $stmt = '`value` LIKE :query AND `category` = :category AND `code` = :language ORDER BY `key`';
    $iaDb->bind($stmt, array('query' => '%' . iaSanitize::sql($query) . '%', 'category' => iaLanguage::CATEGORY_PAGE, 'language' => $iaCore->iaView->language));
    $pages = array();
    if ($rows = $iaDb->all(array('key', 'value'), $stmt, null, null, iaLanguage::getTable())) {
        foreach ($rows as $row) {
            $pageName = str_replace(array('page_title_', 'page_content_'), '', $row['key']);
            $key = false === stripos($row['key'], 'page_content_') ? 'title' : 'content';
            $value = iaSanitize::tags($row['value']);
            isset($pages[$pageName]) || ($pages[$pageName] = array());
            if ('content' == $key) {
                $value = extractSnippet($value, $query);
                if (empty($pages[$pageName]['title'])) {
                    $pages[$pageName]['title'] = iaLanguage::get('page_title_' . $pageName);
                }
            }
            $pages[$pageName]['url'] = $iaPage->getUrlByName($pageName, false);
            $pages[$pageName][$key] = $value;
        }
    }
    // blocks content will be printed out as a pages content
    if ($blocks = searchThroughBlocks($query)) {
        foreach ($blocks as $pageName => $blocksData) {
            if (isset($pages[$pageName])) {
                $pages[$pageName]['extraItems'] = $blocksData;
            } else {
                $pages[$pageName] = array('url' => $iaPage->getUrlByName($pageName), 'title' => iaLanguage::get('page_title_' . $pageName), 'content' => '', 'extraItems' => $blocksData);
            }
        }
    }
    if ($pages) {
        $iaSmarty->assign('pages', $pages);
        $results['num'] += count($pages);
        $results['html']['pages'] = $iaSmarty->fetch('search-list-pages.tpl');
    }
}

//##copyright##
$iaUsers = $iaCore->factory('users');
$iaDb->setTable(iaUsers::getTable());
if (iaView::REQUEST_JSON == $iaView->getRequestType()) {
    if (isset($_GET['email'])) {
        $code = isset($_GET['code']) ? trim($_GET['code']) : false;
        $email = isset($_POST['email']) ? $_POST['email'] : (isset($_GET['email']) ? $_GET['email'] : '');
        $error = false;
        $message = array();
        if ($email) {
            if (!iaValidate::isEmail($email)) {
                $error = true;
                $message = iaLanguage::get('error_email_incorrect');
            }
            $email = iaSanitize::sql($email);
            $member = $iaDb->row_bind(iaDb::ALL_COLUMNS_SELECTION, '`email` = :email', array('email' => $email));
            if (empty($member)) {
                $error = true;
                $message = iaLanguage::get('error_no_member_email');
            }
            if (false !== $code && $member['sec_key'] != $code) {
                $error = true;
                $message = iaLanguage::get('confirmation_code_incorrect');
            }
            if (!$error && false === $code) {
                $mail = array();
                $token = $iaCore->factory('util')->generateToken();
                $confirmationUrl = IA_URL . "forgot/?email={$email}&code={$token}";
                $iaMailer = $iaCore->factory('mailer');
                $iaMailer->loadTemplate('password_restoration');

 protected function _modifyGridParams(&$conditions, &$values)
 {
     if (!empty($_GET['name'])) {
         $conditions[] = "CONCAT(`username`, `fullname`, `email`) LIKE '%" . iaSanitize::sql($_GET['name']) . "%'";
     }
 }

 public function getImageFields($pluginFilter = null)
 {
     $conditions = array("`type` IN ('image','pictures')");
     empty($pluginFilter) || ($conditions[] = "`extras` = '" . iaSanitize::sql($pluginFilter) . "'");
     $conditions = implode(' AND ', $conditions);
     return $this->iaDb->onefield('name', $conditions, null, null, self::getTable());
 }

 private function _massUpdateAlias($previous, $new, $entryId)
 {
     $previous = iaSanitize::sql($previous);
     $previous = IA_URL_DELIMITER == $previous[strlen($previous) - 1] ? substr($previous, 0, -1) : $previous;
     $new = iaSanitize::sql($new);
     $new = IA_URL_DELIMITER == $new[strlen($new) - 1] ? substr($new, 0, -1) : $new;
     $cond = iaDb::printf("`alias` LIKE ':alias%' AND `id` != :id", array('alias' => $previous, 'id' => $entryId));
     $stmt = array('alias' => "REPLACE(`alias`, '{$previous}', '{$new}')");
     $this->_iaDb->update(null, $cond, $stmt);
 }

 public function getStorageFields($itemFilter = null)
 {
     $conditions = array("`type` = 'storage'");
     empty($itemFilter) || ($conditions[] = "`item` = '" . iaSanitize::sql($itemFilter) . "'");
     $conditions = implode(' AND ', $conditions);
     return $this->iaDb->onefield('name', $conditions, null, null, self::getTable());
 }

<?php

//##copyright##
if (iaView::REQUEST_HTML == $iaView->getRequestType()) {
    if (isset($iaCore->requestPath[0])) {
        $tag = $iaCore->requestPath[0];
        $page = empty($_GET['page']) ? 0 : (int) $_GET['page'];
        $page = $page < 1 ? 1 : $page;
        $pageUrl = $iaCore->factory('page', iaCore::FRONT)->getUrlByName('tag');
        $pagination = array('start' => ($page - 1) * $iaCore->get('blog_number'), 'limit' => (int) $iaCore->get('blog_number'), 'template' => $pageUrl . '?page={page}');
        $sql = 'SELECT SQL_CALC_FOUND_ROWS ' . 'b.`id`, b.`title`, b.`date_added`, b.`body`, b.`alias`, b.`image`, m.`fullname`, bt.`title` `tag_title`' . 'FROM `:prefix:table_blog_entries` b ' . 'LEFT JOIN `:prefix:table_members` m ON (b.`member_id` = m.`id`) ' . 'LEFT JOIN `:prefix:table_blog_entries_tags` bet ON (b.`id` = bet.`blog_id`) ' . 'LEFT JOIN `:prefix:table_blog_tags` bt ON (bt.`id` = bet.`tag_id`) ' . 'WHERE bt.`alias` = \':tag\' AND bet.`tag_id` = bt.`id` ' . 'AND b.`status` = \':status\' LIMIT :start, :limit';
        $sql = iaDb::printf($sql, array('prefix' => $iaDb->prefix, 'table_blog_entries' => 'blog_entries', 'table_blog_entries_tags' => 'blog_entries_tags', 'table_blog_tags' => 'blog_tags', 'table_members' => 'members', 'tag' => iaSanitize::sql($tag), 'status' => iaCore::STATUS_ACTIVE, 'start' => $pagination['start'], 'limit' => $pagination['limit']));
        $blogEntries = $iaDb->getAll($sql);
        $pagination['total'] = $iaDb->foundRows();
        if (empty($blogEntries)) {
            return iaView::errorPage(iaView::ERROR_NOT_FOUND);
        }
        $title = '#' . $blogEntries[0]['tag_title'];
        iaBreadcrumb::toEnd($title);
        $iaView->title($title);
        $iaView->display('tag');
        $iaView->assign('pagination', $pagination);
        $iaView->assign('blog_entries', $blogEntries);
    } else {
        $page = empty($_GET['page']) ? 0 : (int) $_GET['page'];
        $page = $page < 1 ? 1 : $page;
        $pageUrl = $iaCore->factory('page', iaCore::FRONT)->getUrlByName('tag');
        $pagination = array('start' => ($page - 1) * $iaCore->get('tag_number'), 'limit' => (int) $iaCore->get('tag_number'), 'template' => $pageUrl . '?page={page}');
        $prefix = $iaDb->prefix;
        $sql = 'SELECT DISTINCT SQL_CALC_FOUND_ROWS bt.`id`, bt.`title`, bt.`alias` ' . 'FROM `:prefix:table_blog_tags` bt ' . 'LEFT JOIN `:prefix:table_blog_entries_tags` bet ON (bt.`id` = bet.`tag_id`) ' . 'LEFT JOIN `:prefix:table_blog_entries` b ON (b.`id` = bet.`blog_id`) ' . 'WHERE b.`status` = \':status\' ' . 'GROUP BY bt.`id` ' . 'ORDER BY bt.`title` ' . 'LIMIT :start, :limit';
        $sql = iaDb::printf($sql, array('prefix' => $iaDb->prefix, 'table_blog_entries' => 'blog_entries', 'table_blog_entries_tags' => 'blog_entries_tags', 'table_blog_tags' => 'blog_tags', 'status' => iaCore::STATUS_ACTIVE, 'start' => $pagination['start'], 'limit' => $pagination['limit']));

 protected function _assignValues(&$iaView, array &$entryData)
 {
     $iaUsers = $this->_iaCore->factory('users');
     $owner = empty($entryData['member_id']) ? iaUsers::getIdentity(true) : $iaUsers->getInfo($entryData['member_id']);
     $entryData['owner'] = $owner['fullname'];
     //		commented for cases when SET SESSION group_concat_max_len doesn't work
     //		$tagIds = $this->_iaDb->all('tag_id', "`blog_id` = {$this->getEntryId()}",0, null, $this->_tableBlogEntriesTags);
     //		$entryData['tags'] = '';
     //		foreach ($tagIds as $tagId)
     //		{
     //			$tags = $this->_iaDb->all('title', "`id` = {$tagId['tag_id']}",0, null, $this->_tableBlogTags);
     //			$entryData['tags'] .= $tags[0]['title'] . ',';
     //		}
     //		$entryData['tags'] = rtrim($entryData['tags'], ',');
     $this->_iaDb->query("SET SESSION group_concat_max_len = 2000");
     if ($this->getEntryId()) {
         $sql = 'SELECT GROUP_CONCAT(`title`) ' . 'FROM `:prefix:table_blog_tags` bt ' . 'WHERE `id` IN (' . 'SELECT `tag_id` ' . 'FROM `:prefix:table_blog_entries_tags` ' . 'WHERE `blog_id` = :id)';
         $sql = iaDb::printf($sql, array('prefix' => $this->_iaDb->prefix, 'table_blog_tags' => 'blog_tags', 'table_blog_entries_tags' => 'blog_entries_tags', 'id' => $this->getEntryId()));
         $entryData['tags'] = $this->_iaDb->getOne($sql);
     } else {
         if (isset($_POST['tags'])) {
             $entryData['tags'] = iaSanitize::sql($_POST['tags']);
         }
     }
 }