function WhiteListSender() { $sql = "SELECT mail_from,mail_to from messages WHERE zMD5=\"{$_GET["id"]}\""; $result = QUERY_SQL($sql); $ligne = @sqlite3_fetch_array(QUERY_SQL($sql)); $mail_from = $ligne["mail_from"]; if ($mail_from == null) { SinglePage(ParseLogs("ERROR\n")); exit; } $ldap = new clladp(); $upd["KasperkyASDatasAllow"] = $mail_from; $uid = $ldap->uid_from_email($ligne["mail_to"]); if ($uid == null) { SinglePage(ParseLogs("ERROR\n")); exit; } $hash = $ldap->UserDatas($uid); $dn = $hash["dn"]; if (!$ldap->Ldap_add_mod($dn, $upd)) { $error = $ldap->ldap_last_error . "\n"; } $sock = new sockets(); $error = $error . $sock->getfile('releaseallmailfrommd5:' . $_GET["id"]); SinglePage(ParseLogs($error)); }
function Graph($email) { include_once 'ressources/class.mysql.inc'; include_once 'ressources/charts.php'; $usermenus = new usersMenus(); $tpl = new templates(); $sql = "SELECT COUNT(ID) as tcount FROM messages WHERE mail_to LIKE '%{$mail}%' AND quarantine='1'"; $ligne = sqlite3_fetch_array(QUERY_SQL($sql)); $quarantine_count = $ligne["tcount"]; $sql = "SELECT COUNT(ID) as tcount FROM messages WHERE mail_to LIKE '%{$mail}%' AND quarantine='0'"; $ligne = sqlite3_fetch_array(QUERY_SQL($sql)); $safe = $ligne["tcount"]; $Graph = InsertChart('js/charts.swf', "js/charts_library", "listener.graphs.php?USER_QUARANTINE={$quarantine_count}&SAFE={$safe}", 250, 250, "FFFFFF", true, $usermenus->ChartLicence); $Graph2 = InsertChart('js/charts.swf', "js/charts_library", "listener.graphs.php?tempsQuarantine={$email}", 250, 250, "FFFFFF", true, $usermenus->ChartLicence); $html = "\n\t<table style='width:100%'>\n\t<tr>\n\t<td>\n\t<H5>{quarantines_graph}</H5>\n\t<center>\n\t{$Graph}\n\t</center>\n\t</td>\n\t<td width=50%>\n\t<H5>{quarantines_graph} ({monthly})</H5>\t\n\t{$Graph2}\n\t</td>\n\t</tr>\n\t</table>\n\t"; return $html; }
function &DBselect($query, $limit = 'NO') { global $DB; //COpt::savesqlrequest($query); $result = false; if (isset($DB['DB']) && !empty($DB['DB'])) { //SDI('SQL: '.$query); $DB['SELECT_COUNT']++; switch ($DB['TYPE']) { case 'MYSQL': if (zbx_numeric($limit)) { $query .= ' limit ' . intval($limit); } $result = mysql_query($query, $DB['DB']); if (!$result) { error('Error in query [' . $query . '] [' . mysql_error() . ']'); } break; case 'POSTGRESQL': if (zbx_numeric($limit)) { $query .= ' limit ' . intval($limit); } $result = pg_query($DB['DB'], $query); if (!$result) { error('Error in query [' . $query . '] [' . pg_last_error() . ']'); } break; case 'ORACLE': if (zbx_numeric($limit)) { $query = 'select * from (' . $query . ') where rownum<=' . intval($limit); } $result = DBexecute($query); if (!$result) { $e = ocierror($result); error('SQL error [' . $e['message'] . '] in [' . $e['sqltext'] . ']'); } break; case 'SQLITE3': if (!$DB['TRANSACTIONS']) { lock_db_access(); } if (!($result = sqlite3_query($DB['DB'], $query))) { error('Error in query [' . $query . '] [' . sqlite3_error($DB['DB']) . ']'); } else { $data = array(); while ($row = sqlite3_fetch_array($result)) { foreach ($row as $id => $name) { if (!zbx_strstr($id, '.')) { continue; } $ids = explode('.', $id); $row[array_pop($ids)] = $row[$id]; unset($row[$id]); } $data[] = $row; } sqlite3_query_close($result); $result =& $data; } if (!$DB['TRANSACTIONS']) { unlock_db_access(); } break; } if ($DB['TRANSACTIONS'] && !$result) { $DB['TRANSACTION_STATE'] &= $result; // SDI($query); // SDI($DB['TRANSACTION_STATE']); } } return $result; }
function alter_col($tablename, $colname, $type) { // Ensure we're connected to the database. if ($this->dbhandle == null) { if (!($this->dbhandle = $this->sql_database_connect())) { $this->debug('SEVERE: Unable to connect to database.', 1); return false; } } switch ($this->db) { case "mysql": return $this->sql("ALTER TABLE `{$tablename}` CHANGE `{$colname}` `{$colname}` {$type}"); case "sqlite": case "sqlite3": // As per remove_col - SQLite doesn't support ALTER TABLE properly. We have to work // around it's limitations. if ($this->db == "sqlite3") { $res = sqlite3_query($this->dbhandle, "select `tbl_name`,`sql` from `sqlite_master` where `tbl_name`='{$tablename}'"); $sqlarr = sqlite3_fetch_array($res); sqlite3_query_close($res); } else { // We're using the SQLite3 class, which works normally. $res = $this->sql("select `tbl_name`,`sql` from `sqlite_master` where `tbl_name`='{$tablename}'", "ASSOC", true); $sqlarr = $res[0]; } $sqlCreate = $sqlarr['sql']; // Extract the col types for all the cols in the $sqlCreate string preg_match_all('/\\n\\s+`(.+)`\\s(.+)[,?$]/', $sqlCreate, $arrNewTableInfo); // Which loads the col NAMES into $arr[1] and col TYPES into $arr[2] // For ease of use, we'll just make it assocative. $i = 0; foreach ($arrNewTableInfo[1] as $name) { $arrAssocTypes[$name] = $arrNewTableInfo[2][$i++]; } print_r($arrAssocTypes); // and NOW we know what the types are for each name. Lets make sure // that the col you want to change actually exists. if (defined($arrAssocTypes[$colname])) { $this->debug("SQL Error - Tried to change col {$colname}, but it doesn't exist", 2); print_r($arrAssocTypes); return false; } // Now we need to replace the type of the col in $sqlCreate with the correct one. $strOld = "`{$colname}` " . $arrAssocTypes[$colname]; $strNew = "`{$colname}` " . $type; $sqlNewCreate = str_replace($strOld, $strNew, $sqlCreate); // Right. So we've got the new table definition in $sqlNewCreate, now all we need // to do is move the old table out of the way, create the new table, and copy // everything across. $this->rename_table($tablename, "{$tablename}_temp"); $this->sql($sqlNewCreate, "NONE", true); // Create the list of cols to use on the import. $strAllCols = implode(",", $arrNewTableInfo[1]); // Copy everything from the old table to the new $sql = "INSERT INTO `{$tablename}` SELECT {$strAllCols} FROM {$tablename}_temp"; if (!$this->sql($sql, "NONE", true)) { $this->debug("SQL Command Failed: {$sql}\n" . $this->errstr . "\n"); } break; default: $this->debug("SEVERE: Database type '" . $this->db . "' NOT SUPPORTED (escape)", 0); return false; } }
function pie_month_by_blocked_type() { $sql = "SELECT count(ID) as tcount,strftime('%Y-%m',received_date),filter_action FROM messages GROUP BY \n\tfilter_action,strftime('%Y-%m',received_date) HAVING strftime('%Y-%m',received_date)='" . date('Y-m') . "' AND filter_action!='send' ORDER BY tcount DESC"; $results = QUERY_SQL($sql); $textes[] = 'title'; $donnees[] = ''; while ($ligne = sqlite3_fetch_array($results)) { $textes[] = $ligne["filter_action"]; $donnees[] = $ligne["tcount"]; } $links = array("url" => "javascript:LoadAjax('graph2','system_statistics.php?MonthCourbeDay=yes',_category_)", "target" => "javascript"); BuildPieChart(array($textes, $donnees), $links); }
print "\ttable created<br>\n"; } print "Inserting values:<br>\n"; sqlite3_exec($db, "INSERT INTO test (id,name,age) VALUES (1,'michael',32)"); sqlite3_exec($db, "INSERT INTO test (id,name,age) VALUES (2,'bob',27)"); sqlite3_exec($db, "INSERT INTO test (id,name,age) VALUES (3,'martin',12)"); /* * Create a query */ print "SQL query:<br>\n"; $query = sqlite3_query($db, "SELECT * FROM test ORDER BY age DESC"); if (!$query) { die(sqlite3_error($db)); } /* * sqlite3_fetch_array() returns an associative array * for each row in the result set. Key indexes are * the columns names. * */ while ($row = sqlite3_fetch_array($query)) { printf("\t%-20s %u<br>\n", $row['name'], $row['age']); } /* * do not forget to release all handles ! * */ print "Closing:<br>\n"; sqlite3_query_close($query); sqlite3_close($db); print "</html>\n";
/** * Parse result arrays into expected format for further operations * * SQLite does not support to return "e.entryid" within a $row['entryid'] return. * So this function manually iteratse through all result rows and rewrites 'X.yyyy' to 'yyyy'. * Yeah. This sucks. Don't tell me! * * @access private * @param ressource The row ressource handle * @param int Bitmask to tell whether to fetch numerical/associative arrays * @return array Propper array containing the ressource results */ function serendipity_db_sqlite_fetch_array($res, $type = SQLITE3_BOTH) { static $search = array('%00', '%25'); static $replace = array("", '%'); $row = sqlite3_fetch_array($res); if (!is_array($row)) { return $row; } /* strip any slashes, correct fieldname */ foreach ($row as $i => $v) { // TODO: If a query of the format 'SELECT a.id, b.text FROM table' is used, // the sqlite extension will give us key indizes 'a.id' and 'b.text' // instead of just 'id' and 'text' like in mysql/postgresql extension. // To fix that, we use a preg-regex; but that is quite performance costy. // Either we always need to use 'SELECT a.id AS id, b.text AS text' in query, // or the sqlite extension may get fixed. :-) $row[preg_replace('@^.+\\.(.*)@', '\\1', $i)] = str_replace($search, $replace, $v); } if ($type == SQLITE3_NUM) { $frow = array(); } else { $frow = $row; } if ($type != SQLITE3_ASSOC) { $i = 0; foreach ($row as $k => $v) { $frow[$i] = $v; $i++; } } return $frow; }
/** * take a resource result set and return an array of type 'ASSOC','NUM','BOTH' * @param resource $result_set * @param string $result_type in 'ASSOC','NUM','BOTH' */ function fetch_res($result_set, $result_type = 'ASSOC') { $result_type = strtoupper($result_type); if (!in_array($result_type, array('NUM', 'ASSOC', 'BOTH'))) { $result_type = 'ASSOC'; } if ($result_type === 'ASSOC') { while ($res[] = sqlite3_fetch_array($result_set)) { } unset($res[count($res) - 1]); //unset last empty row } elseif ($result_type === 'NUM') { while ($res[] = sqlite3_fetch($result_set)) { } unset($res[count($res) - 1]); //unset last empty row } else { while ($row = sqlite3_fetch_array($result_set)) { $res[] = array_merge($row, array_values($row)); } } if (empty($res)) { return $this->last_q2a_res = false; } $this->num_rows = count($res); return $this->last_q2a_res = $res; }
function fetch_array($result, $mode = "both") { switch ($this->sql) { case "sqlite": switch ($mode) { case "assoc": $mode = SQLITE_ASSOC; break; default: $mode = SQLITE_BOTH; break; } if ($result != false) { return sqlite_fetch_array($result, $mode); } else { return false; } break; case "sqlite3": if ($result != false) { return sqlite3_fetch_array($result); } else { return false; } break; case "mysql": switch ($mode) { case "assoc": $mode = MYSQL_ASSOC; break; default: $mode = MYSQL_BOTH; break; } if ($result != false) { return mysql_fetch_array($result, $mode); } else { return false; } break; default: $this->error($this->sql . " is not supported yet", "fetch_array"); return false; break; } }
} $res = sqlite3_query($db, "insert into test (a,b,c, d) VALUES (?, ?, ?, ?)"); if (!$res) { die(sqlite3_error($db)); } if (!sqlite3_bind_int($res, 1, 10)) { die(sqlite3_error($db)); } if (!sqlite3_bind_text($res, 2, "bob")) { die(sqlite3_error($db)); } if (!sqlite3_bind_double($res, 3, 3.1415)) { die(sqlite3_error($db)); } if (!sqlite3_bind_blob($res, 4, file_get_contents("/bin/sh"))) { die(sqlite3_error($db)); } if (!sqlite3_query_exec($res, TRUE)) { /* TRUE: delete the resource after the execution */ die(sqlite3_error($db)); } $res = sqlite3_query($db, "SELECT * from test"); if (!$res) { die(sqlite3_error($db)); } $a_row = sqlite3_fetch_array($res); for ($n = 0; $n < sqlite3_column_count($res); $n++) { echo "column {$n}: type " . $col_types[sqlite3_column_type($res, $n)] . "\n"; } sqlite3_query_close($res); sqlite3_close($db);
function &DBselect($query, $limit = 'NO', $offset = 0) { global $DB; $time_start = microtime(true); $result = false; if (isset($DB['DB']) && !empty($DB['DB'])) { $DB['SELECT_COUNT']++; //SDI('SQL['.$DB['SELECT_COUNT'].']: '.$query); switch ($DB['TYPE']) { case 'MYSQL': if (zbx_ctype_digit($limit)) { $query .= ' LIMIT ' . intval($limit) . ' OFFSET ' . intval($offset); } $result = mysql_query($query, $DB['DB']); if (!$result) { error('Error in query [' . $query . '] [' . mysql_error() . ']'); } break; case 'POSTGRESQL': if (zbx_ctype_digit($limit)) { $query .= ' LIMIT ' . intval($limit) . ' OFFSET ' . intval($offset); } $result = pg_query($DB['DB'], $query); if (!$result) { error('Error in query [' . $query . '] [' . pg_last_error() . ']'); } break; case 'ORACLE': if (zbx_ctype_digit($limit)) { $till = $offset + $limit; $query = 'SELECT * FROM (' . $query . ') WHERE rownum BETWEEN ' . intval($offset) . ' AND ' . intval($till); } $result = OCIParse($DB['DB'], $query); if (!$result) { $e = @ocierror(); error('SQL error [' . $e['message'] . '] in [' . $e['sqltext'] . ']'); } else { if (!@OCIExecute($result, $DB['TRANSACTIONS'] ? OCI_DEFAULT : OCI_COMMIT_ON_SUCCESS)) { $e = ocierror($result); error('SQL error [' . $e['message'] . '] in [' . $e['sqltext'] . ']'); } } break; case 'IBM_DB2': if (zbx_ctype_digit($limit)) { $till = $offset + $limit; $query = 'SELECT * FROM (' . $query . ') WHERE rownum BETWEEN ' . intval($offset) . ' AND ' . intval($till); } $options = array(); if ($DB['TRANSACTIONS']) { $options['autocommit'] = DB2_AUTOCOMMIT_OFF; } if (!($result = db2_prepare($DB['DB'], $query))) { $e = @db2_stmt_errormsg($result); error('SQL error [' . $query . '] in [' . $e . ']'); } else { if (true !== @db2_execute($result, $options)) { $e = @db2_stmt_errormsg($result); error('SQL error [' . $query . '] in [' . $e . ']'); $result = false; } } break; case 'SQLITE3': if (!$DB['TRANSACTIONS']) { lock_db_access(); } if (zbx_ctype_digit($limit)) { $query .= ' LIMIT ' . intval($limit) . ' OFFSET ' . intval($offset); } if (!($result = sqlite3_query($DB['DB'], $query))) { error('Error in query [' . $query . '] [' . sqlite3_error($DB['DB']) . ']'); } else { $data = array(); while ($row = sqlite3_fetch_array($result)) { foreach ($row as $id => $name) { if (!zbx_strstr($id, '.')) { continue; } $ids = explode('.', $id); $row[array_pop($ids)] = $row[$id]; unset($row[$id]); } $data[] = $row; } sqlite3_query_close($result); $result =& $data; } if (!$DB['TRANSACTIONS']) { unlock_db_access(); } break; } if ($DB['TRANSACTIONS'] && !$result) { $DB['TRANSACTION_STATE'] &= $result; } } COpt::savesqlrequest(microtime(true) - $time_start, $query); return $result; }
public function fetchArray($mode = SQLITE3_BOTH) { switch ($mode) { case SQLITE3_BOTH: $result = sqlite3_fetch_array($this->query); return array_merge($result, array_values($result)); case SQLITE3_ASSOC: return sqlite3_fetch_array($this->query); case SQLITE3_NUM: return sqlite3_fetch($this->query); } }