<?php
defined('IN_IA') or exit('Access Denied');
define('PDO_DEBUG', true);
class DB {
protected $pdo;
protected $cfg;
protected $tablepre;
protected $result;
protected $statement;
protected $errors = array();
protected $link = array();
public function getPDO() {
return $this->pdo;
}
public function __construct($name = 'master') {
global $_W;
$this->cfg = $_W['config']['db'];
unset($_W['config']['db']);
$_W['config']['db']['tablepre'] = $this->cfg['tablepre'];
$_W['config']['db']['slave_status'] = $this->cfg['slave_status'];
$this->connect($name);
}
public function connect($name = 'master') {
if(is_array($name)) {
$cfg = $name;
} else {
$cfg = $this->cfg[$name];
}
$this->tablepre = $cfg['tablepre'];
if(empty($cfg)) {
exit("The master database is not found, Please checking 'data/config.php'");
}
$dsn = "mysql:dbname={$cfg['database']};host={$cfg['host']};port={$cfg['port']};charset={$cfg['charset']}";
$dbclass = '';
$options = array();
if (class_exists('PDO')) {
if (extension_loaded("pdo_mysql") && in_array('mysql', PDO::getAvailableDrivers())) {
$dbclass = 'PDO';
$options = array(PDO::ATTR_PERSISTENT => $cfg['pconnect']);
} else {
if(!class_exists('_PDO')) {
load()->library('pdo');
}
$dbclass = '_PDO';
}
} else {
load()->library('pdo');
$dbclass = 'PDO';
}
$pdo = new $dbclass($dsn, $cfg['username'], $cfg['password'], $options);
$this->pdo = $pdo;
$sql = "SET NAMES '{$cfg['charset']}';";
$this->pdo->exec($sql);
$this->pdo->exec("SET sql_mode='';");
if ($cfg['username'] == 'root') {
$this->pdo->exec("SET GLOBAL max_allowed_packet = 2*1024*1024*10;");
}
if(is_string($name)) {
$this->link[$name] = $this->pdo;
}
$this->logging($sql);
}
public function prepare($sql) {
$sqlsafe = SqlPaser::checkquery($sql);
if (is_error($sqlsafe)) {
trigger_error($sqlsafe['message'], E_USER_ERROR);
return false;
}
if ($GLOBALS['_W']['config']['setting']['development'] == 3) {
if ($GLOBALS['error_handler'] instanceof Raven_ErrorHandler) {
$GLOBALS['error_handler']->handleError(E_USER_ERROR, $sql);
}
}
$statement = $this->pdo->prepare($sql);
return $statement;
}
public function query($sql, $params = array()) {
$sqlsafe = SqlPaser::checkquery($sql);
if (is_error($sqlsafe)) {
trigger_error($sqlsafe['message'], E_USER_ERROR);
return false;
}
$starttime = microtime(true);
if (empty($params)) {
$result = $this->pdo->exec($sql);
$this->logging($sql, array(), $this->pdo->errorInfo());
return $result;
}
$statement = $this->prepare($sql);
$result = $statement->execute($params);
$this->logging($sql, $params, $statement->errorInfo());
$endtime = microtime(true);
$this->performance($sql, $endtime - $starttime);
if (!$result) {
return false;
} else {
return $statement->rowCount();
}
}
public function fetchcolumn($sql, $params = array(), $column = 0) {
$starttime = microtime();
$statement = $this->prepare($sql);
$result = $statement->execute($params);
$this->logging($sql, $params, $statement->errorInfo());
$endtime = microtime();
$this->performance($sql, $endtime - $starttime);
if (!$result) {
return false;
} else {
$data = $statement->fetchColumn($column);
return $data;
}
}
public function fetch($sql, $params = array()) {
$starttime = microtime();
$statement = $this->prepare($sql);
$result = $statement->execute($params);
$this->logging($sql, $params, $statement->errorInfo());
$endtime = microtime();
$this->performance($sql, $endtime - $starttime);
if (!$result) {
return false;
} else {
$data = $statement->fetch(pdo::FETCH_ASSOC);
return $data;
}
}
public function fetchall($sql, $params = array(), $keyfield = '') {
$starttime = microtime();
$statement = $this->prepare($sql);
$result = $statement->execute($params);
$this->logging($sql, $params, $statement->errorInfo());
$endtime = microtime();
$this->performance($sql, $endtime - $starttime);
if (!$result) {
return false;
} else {
if (empty($keyfield)) {
$result = $statement->fetchAll(pdo::FETCH_ASSOC);
} else {
$temp = $statement->fetchAll(pdo::FETCH_ASSOC);
$result = array();
if (!empty($temp)) {
foreach ($temp as $key => &$row) {
if (isset($row[$keyfield])) {
$result[$row[$keyfield]] = $row;
} else {
$result[] = $row;
}
}
}
}
return $result;
}
}
public function get($tablename, $params = array(), $fields = array(), $orderby = array()) {
$select = SqlPaser::parseSelect($fields);
$condition = SqlPaser::parseParameter($params, 'AND');
$orderbysql = SqlPaser::parseOrderby($orderby);
$sql = "{$select} FROM " . $this->tablename($tablename) . (!empty($condition['fields']) ? " WHERE {$condition['fields']}" : '') . " $orderbysql LIMIT 1";
return $this->fetch($sql, $condition['params']);
}
public function getall($tablename, $params = array(), $fields = array(), $keyfield = '', $orderby = array(), $limit = array()) {
$select = SqlPaser::parseSelect($fields);
$condition = SqlPaser::parseParameter($params, 'AND');
$limitsql = SqlPaser::parseLimit($limit);
$orderbysql = SqlPaser::parseOrderby($orderby);
$sql = "{$select} FROM " .$this->tablename($tablename) . (!empty($condition['fields']) ? " WHERE {$condition['fields']}" : '') . $orderbysql . $limitsql;
return $this->fetchall($sql, $condition['params'], $keyfield);
}
public function getslice($tablename, $params = array(), $limit = array(), &$total = null, $fields = array(), $keyfield = '', $orderby = array()) {
$select = SqlPaser::parseSelect($fields);
$condition = SqlPaser::parseParameter($params, 'AND');
$limitsql = SqlPaser::parseLimit($limit);
if (!empty($orderby)) {
if (is_array($orderby)) {
$orderbysql = implode(',', $orderby);
} else {
$orderbysql = $orderby;
}
}
$sql = "{$select} FROM " . $this->tablename($tablename) . (!empty($condition['fields']) ? " WHERE {$condition['fields']}" : '') . (!empty($orderbysql) ? " ORDER BY $orderbysql " : '') . $limitsql;
$total = pdo_fetchcolumn("SELECT COUNT(*) FROM " . tablename($tablename) . (!empty($condition['fields']) ? " WHERE {$condition['fields']}" : ''), $condition['params']);
return $this->fetchall($sql, $condition['params'], $keyfield);
}
public function getcolumn($tablename, $params = array(), $field = '') {
$result = $this->get($tablename, $params, $field);
if (!empty($result)) {
if (strexists($field, '(')) {
return array_shift($result);
} else {
return $result[$field];
}
} else {
return false;
}
}
public function update($table, $data = array(), $params = array(), $glue = 'AND') {
$fields = SqlPaser::parseParameter($data, ',');
$condition = SqlPaser::parseParameter($params, $glue);
$params = array_merge($fields['params'], $condition['params']);
$sql = "UPDATE " . $this->tablename($table) . " SET {$fields['fields']}";
$sql .= $condition['fields'] ? ' WHERE '.$condition['fields'] : '';
return $this->query($sql, $params);
}
public function insert($table, $data = array(), $replace = FALSE) {
$cmd = $replace ? 'REPLACE INTO' : 'INSERT INTO';
$condition = SqlPaser::parseParameter($data, ',');
return $this->query("$cmd " . $this->tablename($table) . " SET {$condition['fields']}", $condition['params']);
}
public function insertid() {
return $this->pdo->lastInsertId();
}
public function delete($table, $params = array(), $glue = 'AND') {
$condition = SqlPaser::parseParameter($params, $glue);
$sql = "DELETE FROM " . $this->tablename($table);
$sql .= $condition['fields'] ? ' WHERE '.$condition['fields'] : '';
return $this->query($sql, $condition['params']);
}
public function exists($tablename, $params = array()) {
$row = $this->get($tablename, $params);
if (empty($row) || !is_array($row) || count($row) == 0) {
return false;
} else {
return true;
}
}
public function count($tablename, $params = array(), $cachetime = 30) {
$total = pdo_getcolumn($tablename, $params, 'count(*)');
return intval($total);
}
public function begin() {
$this->pdo->beginTransaction();
}
public function commit() {
$this->pdo->commit();
}
public function rollback() {
$this->pdo->rollBack();
}
public function run($sql, $stuff = 'ims_') {
if(!isset($sql) || empty($sql)) return;
$sql = str_replace("\r", "\n", str_replace(' ' . $stuff, ' ' . $this->tablepre, $sql));
$sql = str_replace("\r", "\n", str_replace(' `' . $stuff, ' `' . $this->tablepre, $sql));
$ret = array();
$num = 0;
$sql = preg_replace("/\;[ \f\t\v]+/", ';', $sql);
foreach(explode(";\n", trim($sql)) as $query) {
$ret[$num] = '';
$queries = explode("\n", trim($query));
foreach($queries as $query) {
$ret[$num] .= (isset($query[0]) && $query[0] == '#') || (isset($query[1]) && isset($query[1]) && $query[0].$query[1] == '--') ? '' : $query;
}
$num++;
}
unset($sql);
foreach($ret as $query) {
$query = trim($query);
if($query) {
$this->query($query, array());
}
}
return true;
}
public function fieldexists($tablename, $fieldname) {
$isexists = $this->fetch("DESCRIBE " . $this->tablename($tablename) . " `{$fieldname}`", array());
return !empty($isexists) ? true : false;
}
public function fieldmatch($tablename, $fieldname, $datatype = '', $length = '') {
$datatype = strtolower($datatype);
$field_info = $this->fetch("DESCRIBE " . $this->tablename($tablename) . " `{$fieldname}`", array());
if (empty($field_info)) {
return false;
}
if (!empty($datatype)) {
$find = strexists($field_info['Type'], '(');
if (empty($find)) {
$length = '';
}
if (!empty($length)) {
$datatype .= ("({$length})");
}
return strpos($field_info['Type'], $datatype) === 0 ? true : -1;
}
return true;
}
public function indexexists($tablename, $indexname) {
if (!empty($indexname)) {
$indexs = $this->fetchall("SHOW INDEX FROM " . $this->tablename($tablename), array(), '');
if (!empty($indexs) && is_array($indexs)) {
foreach ($indexs as $row) {
if ($row['Key_name'] == $indexname) {
return true;
}
}
}
}
return false;
}
public function tablename($table) {
return (strpos($table, $this->tablepre) === 0 || strpos($table, 'ims_') === 0) ? $table : "`{$this->tablepre}{$table}`";
}
public function debug($output = true, $append = array()) {
if(!empty($append)) {
$output = false;
array_push($this->errors, $append);
}
if($output) {
print_r($this->errors);
} else {
if (!empty($append['error'][1])) {
$traces = debug_backtrace();
$ts = '';
foreach($traces as $trace) {
$trace['file'] = str_replace('\\', '/', $trace['file']);
$trace['file'] = str_replace(IA_ROOT, '', $trace['file']);
$ts .= "file: {$trace['file']}; line: {$trace['line']}; <br />";
}
$params = var_export($append['params'], true);
trigger_error("SQL: <br/>{$append['sql']}<hr/>Params: <br/>{$params}<hr/>SQL Error: <br/>{$append['error'][2]}<hr/>Traces: <br/>{$ts}", E_USER_WARNING);
}
}
return $this->errors;
}
private function logging($sql, $params = array(), $message = '') {
if(PDO_DEBUG) {
$info = array();
$info['sql'] = $sql;
$info['params'] = $params;
$info['error'] = empty($message) ? $this->pdo->errorInfo() : $message;
$this->debug(false, $info);
}
return true;
}
public function tableexists($table) {
if(!empty($table)) {
$data = $this->fetch("SHOW TABLES LIKE '{$this->tablepre}{$table}'", array());
if(!empty($data)) {
$data = array_values($data);
$tablename = $this->tablepre . $table;
if(in_array($tablename, $data)) {
return true;
} else {
return false;
}
} else {
return false;
}
} else {
return false;
}
}
private function performance($sql, $runtime = 0) {
global $_W;
if ($runtime == 0) {
return false;
}
if (strexists($sql, 'core_performance')) {
return false;
}
if (empty($_W['config']['setting']['maxtimesql'])) {
$_W['config']['setting']['maxtimesql'] = 5;
}
if ($runtime > $_W['config']['setting']['maxtimesql']) {
$sqldata = array(
'type' => '2',
'runtime' => $runtime,
'runurl' => 'http://'.$_SERVER['HTTP_HOST'].$_SERVER['REQUEST_URI'],
'runsql' => $sql,
'createtime' => time()
);
$this->insert('core_performance', $sqldata);
}
return true;
}
}
class SqlPaser {
private static $checkcmd = array('SELECT', 'UPDATE', 'INSERT', 'REPLAC', 'DELETE');
private static $disable = array(
'function' => array('load_file', 'floor', 'hex', 'substring', 'if', 'ord', 'char', 'benchmark', 'reverse', 'strcmp', 'datadir', 'updatexml', 'extractvalue', 'name_const', 'multipoint', 'database', 'user'),
'action' => array('@', 'intooutfile', 'intodumpfile', 'unionselect', 'uniondistinct', 'information_schema', 'current_user', 'current_date'),
'note' => array('','#','--'),
);
public static function checkquery($sql) {
$cmd = strtoupper(substr(trim($sql), 0, 6));
if (in_array($cmd, self::$checkcmd)) {
$mark = $clean = '';
$sql = str_replace(array('\\\\', '\\\'', '\\"', '\'\''), '', $sql);
if (strpos($sql, '/') === false && strpos($sql, '#') === false && strpos($sql, '-- ') === false && strpos($sql, '@') === false && strpos($sql, '`') === false) {
$cleansql = preg_replace("/'(.+?)'/s", '', $sql);
} else {
$cleansql = self::stripSafeChar($sql);
}
$cleansql = preg_replace("/[^a-z0-9_\-\(\)#\*\/\"]+/is", "", strtolower($cleansql));
if (is_array(self::$disable['function'])) {
foreach (self::$disable['function'] as $fun) {
if (strpos($cleansql, $fun . '(') !== false) {
return error(1, 'SQL中包含禁用函数 - ' . $fun);
}
}
}
if (is_array(self::$disable['action'])) {
foreach (self::$disable['action'] as $action) {
if (strpos($cleansql, $action) !== false) {
return error(2, 'SQL中包含禁用操作符 - ' . $action);
}
}
}
if (is_array(self::$disable['note'])) {
foreach (self::$disable['note'] as $note) {
if (strpos($cleansql, $note) !== false) {
return error(3, 'SQL中包含注释信息');
}
}
}
} elseif (substr($cmd, 0, 2) === '/*') {
return error(3, 'SQL中包含注释信息');
}
}
private static function stripSafeChar($sql) {
$len = strlen($sql);
$mark = $clean = '';
for ($i = 0; $i < $len; $i++) {
$str = $sql[$i];
switch ($str) {
case '\'':
if (!$mark) {
$mark = '\'';
$clean .= $str;
} elseif ($mark == '\'') {
$mark = '';
}
break;
case '/':
if (empty($mark) && $sql[$i + 1] == '*') {
$mark = '/*';
$clean .= $mark;
$i++;
} elseif ($mark == '/*' && $sql[$i - 1] == '*') {
$mark = '';
$clean .= '*';
}
break;
case '#':
if (empty($mark)) {
$mark = $str;
$clean .= $str;
}
break;
case "\n":
if ($mark == '#' || $mark == '--') {
$mark = '';
}
break;
case '-':
if (empty($mark) && substr($sql, $i, 3) == '-- ') {
$mark = '-- ';
$clean .= $mark;
}
break;
default:
break;
}
$clean .= $mark ? '' : $str;
}
return $clean;
}
public static function parseParameter($params, $glue = ',', $alias = '') {
$result = array('fields' => ' 1 ', 'params' => array());
$split = '';
$suffix = '';
$allow_operator = array('>', '<', '<>', '!=', '>=', '<=', '+=', '-=', 'LIKE', 'like');
if (in_array(strtolower($glue), array('and', 'or'))) {
$suffix = '__';
}
if (!is_array($params)) {
$result['fields'] = $params;
return $result;
}
if (is_array($params)) {
$result['fields'] = '';
foreach ($params as $fields => $value) {
if ($glue == ',') {
$value = $value === null ? '' : $value;
}
$operator = '';
if (strpos($fields, ' ') !== FALSE) {
list($fields, $operator) = explode(' ', $fields, 2);
if (!in_array($operator, $allow_operator)) {
$operator = '';
}
}
if (empty($operator)) {
$fields = trim($fields);
if (is_array($value) && !empty($value)) {
$operator = 'IN';
} elseif ($value === 'NULL') {
$operator = 'IS';
} else {
$operator = '=';
}
} elseif ($operator == '+=') {
$operator = " = `$fields` + ";
} elseif ($operator == '-=') {
$operator = " = `$fields` - ";
} elseif ($operator == '!=' || $operator == '<>') {
if (is_array($value) && !empty($value)) {
$operator = 'NOT IN';
} elseif ($value === 'NULL') {
$operator = 'IS NOT';
}
}
$select_fields = self::parseFieldAlias($fields, $alias);
if (is_array($value) && !empty($value)) {
$insql = array();
$value = array_values($value);
foreach ($value as $v) {
$placeholder = self::parsePlaceholder($fields, $suffix);
$insql[] = $placeholder;
$result['params'][$placeholder] = is_null($v) ? '' : $v;
}
$result['fields'] .= $split . "$select_fields {$operator} (".implode(",", $insql).")";
$split = ' ' . $glue . ' ';
} else {
$placeholder = self::parsePlaceholder($fields, $suffix);
$result['fields'] .= $split . "$select_fields {$operator} " . ($value === 'NULL' ? 'NULL' : $placeholder);
$split = ' ' . $glue . ' ';
if ($value !== 'NULL') {
$result['params'][$placeholder] = is_array($value) ? '' : $value;
}
}
}
}
return $result;
}
private static function parsePlaceholder($field, $suffix = '') {
static $params_index = 0;
$params_index++;
$illegal_str = array('(', ')', '.', '*');
$placeholder = ":{$suffix}" . str_replace($illegal_str, '_', $field) . "_{$params_index}";
return $placeholder;
}
private static function parseFieldAlias($field, $alias = '') {
if (strexists($field, '.') || strexists($field, '*')) {
return $field;
}
if (strexists($field, '(')) {
$select_fields = str_replace(array('(', ')'), array('(' . (!empty($alias) ? "`{$alias}`." : '') .'`', '`)'), $field);
} else {
$select_fields = (!empty($alias) ? "`{$alias}`." : '') . "`$field`";
}
return $select_fields;
}
public static function parseSelect($field = array(), $alias = '') {
if (empty($field) || $field == '*') {
return ' SELECT *';
}
if (!is_array($field)) {
$field = array($field);
}
$select = array();
$index = 0;
foreach ($field as $field_row) {
if (strexists($field_row, '*')) {
if (!strexists(strtolower($field_row), 'as')) {
}
} elseif (strexists(strtolower($field_row), 'select')) {
if ($field_row[0] != '(') {
$field_row = "($field_row) AS '{$index}'";
}
} elseif (strexists($field_row, '(')) {
$field_row = str_replace(array('(', ')'), array('(' . (!empty($alias) ? "`{$alias}`." : '') . '`', '`)'), $field_row);
if (!strexists(strtolower($field_row), 'as')) {
$field_row .= " AS '{$index}'";
}
} else {
$field_row = self::parseFieldAlias($field_row, $alias);
}
$select[] = $field_row;
$index++;
}
return " SELECT " . implode(',', $select);
}
public static function parseLimit($limit, $inpage = true) {
$limitsql = '';
if (empty($limit)) {
return $limitsql;
}
if (is_array($limit)) {
if (empty($limit[0]) && !empty($limit[1])) {
$limitsql = " LIMIT 0, " . $limit[1];
} else {
$limit[0] = max(intval($limit[0]), 1);
!empty($limit[1]) && $limit[1] = max(intval($limit[1]), 1);
if (empty($limit[0]) && empty($limit[1])) {
$limitsql = '';
} elseif (!empty($limit[0]) && empty($limit[1])) {
$limitsql = " LIMIT " . $limit[0];
} else {
$limitsql = " LIMIT " . ($inpage ? ($limit[0] - 1) * $limit[1] : $limit[0]) . ', ' . $limit[1];
}
}
} else {
$limit = trim($limit);
if (preg_match('/^(?:limit)?[\s,0-9]+$/i', $limit)) {
$limitsql = strexists(strtoupper($limit), 'LIMIT') ? " $limit " : " LIMIT $limit";
}
}
return $limitsql;
}
public static function parseOrderby($orderby, $alias = '') {
$orderbysql = '';
if (empty($orderby)) {
return $orderbysql;
}
if (!is_array($orderby)) {
$orderby = explode(',', $orderby);
}
foreach ($orderby as $i => &$row) {
$row = strtolower($row);
list($field, $orderbyrule) = explode(' ', $row);
if ($orderbyrule != 'asc' && $orderbyrule != 'desc') {
unset($orderby[$i]);
}
$field = self::parseFieldAlias($field, $alias);
$row = "{$field} {$orderbyrule}";
}
$orderbysql = implode(',', $orderby);
return !empty($orderbysql) ? " ORDER BY $orderbysql " : '';
}
public static function parseGroupby($statement, $alias = '') {
if (empty($statement)) {
return $statement;
}
if (!is_array($statement)) {
$statement = explode(',', $statement);
}
foreach ($statement as $i => &$row) {
$row = self::parseFieldAlias($row, $alias);
if (strexists($row, ' ')) {
unset($statement[$i]);
}
}
$statementsql = implode(', ', $statement);
return !empty($statementsql) ? " GROUP BY $statementsql " : '';
}
}