/**
* The primary method a driver needs to implement is the execute method, which takes an array of query options.
* The options in the array varies, but the key type will always be supplied, which will be either SELECT, UPDATE,
* INSERT, REPLACE or DELETE.
*
* @param array $options An array of options that were generated through use of the Query class.
* @return object It is expected to return an instance of an \Queryer\Driver\DatabaseDriverResult class.
* @see \Queryer\Query, \Queryer\Driver\DatabaseDriverResult
*/
public function execute(array $options)
{
$query = self::generateQuery($options);
$query = DatabaseTools::replaceVariables($query, $options['variables']);
$result = $this->sqlite->query($query);
return new Sqlite3DriverResult($result, $this->sqlite->changes(), $this->sqlite->lastInsertRowID(), $result === false ? $this->sqlite->lastErrorCode() : null, $result === false ? $this->sqlite->lastErrorMsg() : null, $query);
}
/**
* {@inheritdoc}
*/
protected function executeQuery($query, array $parameters)
{
if (isset($this->statements[$query])) {
$statement = $this->statements[$query];
} else {
// Temporary set the error reporting level to 0 to avoid any warning.
$errorReportingLevel = error_reporting(0);
$statement = $this->sqlite3->prepare($query);
// Restore the original error reporting level.
error_reporting($errorReportingLevel);
$errorCode = $this->sqlite3->lastErrorCode();
if ($errorCode !== 0) {
$exception = new SQLite3Exception($this->sqlite3->lastErrorMsg(), $errorCode);
if ($errorCode === 1) {
// SQL error cause by a missing function, this must be reported with a GeometryEngineException.
throw GeometryEngineException::operationNotSupportedByEngine($exception);
} else {
// Other SQLite3 error; we cannot trigger the original E_WARNING, so we throw this exception instead.
throw $exception;
}
} else {
$this->statements[$query] = $statement;
}
}
$index = 1;
foreach ($parameters as $parameter) {
if ($parameter instanceof Geometry) {
if ($parameter->isEmpty()) {
$statement->bindValue($index++, $parameter->asText(), SQLITE3_TEXT);
$statement->bindValue($index++, $parameter->SRID(), SQLITE3_INTEGER);
} else {
$statement->bindValue($index++, $parameter->asBinary(), SQLITE3_BLOB);
$statement->bindValue($index++, $parameter->SRID(), SQLITE3_INTEGER);
}
} else {
if ($parameter === null) {
$type = SQLITE3_NULL;
} elseif (is_int($parameter)) {
$type = SQLITE3_INTEGER;
} elseif (is_float($parameter)) {
$type = SQLITE3_FLOAT;
} else {
$type = SQLITE3_TEXT;
}
$statement->bindValue($index++, $parameter, $type);
}
}
$result = $statement->execute();
return $result->fetchArray(SQLITE3_NUM);
}
/**
* @param string $dbname database name
* @param OutputInterface $output standard verbode output
* @param boolean $renew if true delete and recreate database
*
* @throws \Exception
*/
public function __construct($dbname, OutputInterface $output, $renew = false)
{
$this->fs = new Filesystem();
$this->output = $output;
$this->dbname = $dbname;
if ($renew) {
if ($this->fs->exists($this->dbname)) {
$this->fs->remove($this->dbname);
}
}
$this->db = new \SQLite3($this->dbname);
if ($this->db->exec('PRAGMA encoding = "UTF-8";') === false) {
$this->output->writeln($this->db->lastErrorCode() . " : " . $this->db->lastErrorMsg());
throw new \Exception("cannot set encoding UTF-8");
}
}
/**
* Returns the error string.
*
* @return string
*/
public function error()
{
if (0 === $this->conn->lastErrorCode()) {
return '';
}
return $this->conn->lastErrorMsg();
}
/**
* open database, on failure send message tot syslog
* creates structure needed for this captiveportal zone
* @return SQLite3
*/
public function open()
{
// open database
$db_path = "/var/db/captiveportal{$this->zone}.db";
try {
$this->handle = new Sqlite(array("dbname" => $db_path));
$sql = array();
// create structure on new database
$sql[] = "CREATE TABLE IF NOT EXISTS captiveportal (" . "allow_time INTEGER, pipeno_in INTEGER, pipeno_out INTEGER, ip TEXT, mac TEXT, username TEXT, " . "sessionid TEXT, bpassword TEXT, session_timeout INTEGER, idle_timeout INTEGER, " . "session_terminate_time INTEGER, interim_interval INTEGER, radiusctx TEXT)";
$sql[] = "CREATE UNIQUE INDEX IF NOT EXISTS idx_active ON captiveportal (sessionid, username)";
$sql[] = "CREATE INDEX IF NOT EXISTS user ON captiveportal (username)";
$sql[] = "CREATE INDEX IF NOT EXISTS ip ON captiveportal (ip)";
$sql[] = "CREATE INDEX IF NOT EXISTS starttime ON captiveportal (allow_time)";
$sql[] = "CREATE TABLE IF NOT EXISTS captiveportal_mac (" . "mac TEXT, ip TEXT,pipeno_in INTEGER, pipeno_out INTEGER, last_checked INTEGER )";
$sql[] = "CREATE UNIQUE INDEX IF NOT EXISTS idx_mac ON captiveportal_mac (mac)";
$sql[] = "CREATE TABLE IF NOT EXISTS captiveportal_ip (" . "ip TEXT,pipeno_in INTEGER, pipeno_out INTEGER, last_checked INTEGER )";
$sql[] = "CREATE UNIQUE INDEX IF NOT EXISTS idx_ip ON captiveportal_ip (ip)";
foreach ($sql as $cmd) {
if (!$this->handle->execute($cmd)) {
$logger = new Syslog("logportalauth", array('option' => LOG_PID, 'facility' => LOG_LOCAL4));
$msg = "Error during table {$this->zone} creation. Error message: {$this->handle->lastErrorMsg()}";
$logger->error($msg);
$this->handle = null;
break;
}
}
} catch (\Exception $e) {
$logger = new Syslog("logportalauth", array('option' => LOG_PID, 'facility' => LOG_LOCAL4));
$logger->error("Error opening database for zone " . $this->zone . " : " . $e->getMessage() . " ");
$this->handle = null;
}
return $this->handle;
}
function anubisFILE($idmd5, $fileName)
{
#Execute the Python Script
#python /var/www/anubis/submit_to_anubis.py /var/www/mastiff/MD5/filename.VIR
$command = 'python /var/www/anubis/submit_to_anubis.py -u ' . $anubisUser . ' -p ' . $anubisPass . ' "/var/www/mastiff/' . $idmd5 . '/' . $fileName . '"';
$output = shell_exec($command);
$anubisRes['out'] = $output;
$pattern = '/https?\\:\\/\\/[^\\" ]+/i';
preg_match($pattern, $output, $matches);
#echo '<pre>';
# echo '$matches: ';
# var_dump($matches);
#echo '</pre>';
$anubisLink = $matches[0];
$anubisLink = strstr($anubisLink, "\n", true);
$anubisRes['link'] = $anubisLink;
#Update the Database
$db = new SQLite3('../mastiff/mastiff.db');
$result = $db->exec('UPDATE mastiff SET anubis = "' . $anubisLink . '" WHERE md5 = "' . $idmd5 . '"');
if (!$result) {
$anubisRes['db'] = $db->lastErrorMsg();
} else {
$anubisRes['db'] = $db->changes() . ' Record updated successfully.';
}
return $anubisRes;
}
function check_links()
{
//Before checking anything, check if laurentian.concat.ca is resolving.
$catalogue_link = fopen("http://laurentian.concat.ca/", "r");
if (!$catalogue_link) {
die("There may be a problem with laurentian.concat.ca so this process is going to halt. If this persists, contact Kevin Beswick");
}
fclose($catalogue_link);
//Check all links from database, if active, leave alone, if not active, delete them.
$db_session = new SQLite3('reserves.db');
$query = "SELECT bookbag_id from reserve";
$results = $db_session->query($query) or die($db_session->lastErrorMsg());
$begin_link = "http://laurentian.concat.ca/opac/extras/feed/bookbag/opac/";
$end_link = "?skin=lul";
$count = 0;
while ($row = $results->fetchArray()) {
$file = fopen($begin_link . $row["bookbag_id"] . $end_link, "r");
if (!$file) {
//remove from list
$query = "DELETE from reserve where bookbag_id = " . $row["bookbag_id"];
$db_session->exec($query) or die("not working... " . $db_session->lastErrorMsg());
$count++;
}
fclose($file);
}
echo "Done removing dead links... " . $count . " were removed.";
}
/**
* Return the last MySQL error
*/
function error()
{
if ($this->sqlite === null) {
return null;
}
return $this->sqlite->lastErrorMsg();
}
/**
* Test if a error exist, if yes than throw error
*
* @throws CHOQ_Exception
* @param string $query
*/
public function testError($query = null)
{
if ($this->sqlite->lastErrorCode()) {
$error = $this->sqlite->lastErrorMsg();
if ($query) {
$error .= "\nSQL Query: {$query}";
}
error($error);
}
}
function execute($query)
{
if (!$this->isConnected()) {
throw new NotConnectedException();
}
Connection::startMeasuring($this);
if (func_num_args() > 1) {
$query = call_user_func_array('sprintf', func_get_args());
}
$result = $this->connection->query($query);
Connection::endMeasuring($this);
if (!$result) {
throw new QueryException($this->connection->lastErrorMsg(), $this->connection->lastErrorCode());
}
if ($result instanceof \SQLite3Result) {
return new RecordSet($result);
}
return $result;
}
function db_connect()
{
global $dbname;
$conn = new SQLite3($dbname);
if (!$conn)
{
trigger_error($conn->lastErrorMsg(), E_USER_ERROR);
}
return $conn;
}
function getSettings()
{
$db = new SQLite3('/var/www/admin/admin.db');
$result = $db->query('SELECT * FROM admin WHERE id = 1');
if (isset($result)) {
while ($res = $result->fetchArray()) {
#$_SESSION['size']=$res['size'];
#$malwrRes['uuid']=$res['uuid'];
$settingRes = array();
$settingRes = $res;
/* remver
* mastiffconf
* mastiffpy
* tridloc
* malwrPlugin
* malwrAPI
* critsPlugin
* critsPage
* critsLogin
* threatanalyzerplugin
* threatapi
* threatbase
* threatpage
* threatargs
* tasubpriority
* tasubsandbox
* tasubreanalyze
* anubisuser
* anubispass
* wotapi
* vtapi
* googapi
* gcsekey
* gcsesig
* gcsecx
* gcsequery
* autopbua
* twitterapi
* twittertoken
* twitterquery
* twitterconsec
* twitteroauthsec
*/
}
}
if (!$result) {
$settingRes['db'] = $db->lastErrorMsg();
} else {
$settingRes['db'] = $db->changes() . ' Record updated successfully.';
}
$db->close();
return $settingRes;
}
/**
* Checks the last error code and decide if and what kind of exception to throw.
* @return true if nothing is wrong
* @throws CoreXEngine\Cache\AccessException if we want to write a read-only file
* @throws CoreXEngine\Cache\Exception for no special typed problem
*/
protected function checkError()
{
switch ($code = $this->database->lastErrorCode()) {
case 8:
throw new AccessException($this->database->lastErrorMsg(), $this->database->lastErrorCode());
break;
default:
if (0 < $code) {
throw new Exception($this->database->lastErrorMsg(), $this->database->lastErrorCode());
}
}
return true;
}
function do_query($query)
{
global $db_hostname, $db_user, $db_pass, $db_db, $db_type;
/* Connecting, selecting database */
if ($db_type == "mysql") {
$link = new mysqli($db_hostname, $db_user, $db_pass, $db_db) or die("Error " . mysqli_error($link));
$result = $link->query($query) or die("Error in the query: " . mysqli_error($link));
/* Closing connection */
mysqli_close($link);
} elseif ($db_type == "sqlite") {
$link = new SQLite3($db_hostname) or die("Error " . $link->lastErrorMsg());
$result = $link->query($query) or die("Error in the query: " . $link->lastErrorMsg() . " - Query: " . $query);
/* Closing connection */
$link->close;
} else {
$link = pg_connect("host={$db_hostname} dbname={$db_db} user={$db_user} password={$db_pass}") or die("Could not connect to database");
$result = pg_query($link, $query) or die("Query failed");
/* Closing connection */
pg_close($link);
}
return $result;
}
function getUserPrivilege($login)
{
$db = new SQLite3("db/db.sqlite3");
if (!$db) {
echo $db->lastErrorMsg();
return false;
}
$sql = "SELECT login,privilege FROM users WHERE login = \"{$login}\"";
$ret = $db->query($sql);
while ($row = $ret->fetchArray(SQLITE3_ASSOC)) {
$privilege = $row["privilege"];
}
return $privilege;
}
function selectAllPointsFromMeeting($selectedMeetingName)
{
$allPoints = "";
$db = new SQLite3("db/db.sqlite3");
if (!$db) {
echo $db->lastErrorMsg();
return false;
}
$sql = "SELECT points FROM userscurves WHERE meetingname='{$selectedMeetingName}'";
$ret = $db->query($sql);
while ($row = $ret->fetchArray(SQLITE3_ASSOC)) {
$allPoints = $allPoints . ":" . $row["points"];
}
return $allPoints;
}
/**
* Execute the SQL query.
*
* @param string $sql
* @throws Exception
* @return void
*/
public function query($sql)
{
if ($this->isPdo) {
$sth = $this->sqlite->prepare($sql);
if (!$sth->execute()) {
throw new Exception($sth->errorCode() . ': ' . $sth->errorInfo());
} else {
$this->result = $sth;
}
} else {
if (!($this->result = $this->sqlite->query($sql))) {
throw new Exception('Error: ' . $this->sqlite->lastErrorCode() . ': ' . $this->sqlite->lastErrorMsg() . '.');
}
}
}
function updateUserMeetingWithPoints($userlogin, $meetingname, $points)
{
$db = new SQLite3("db/db.sqlite3");
if (!$db) {
echo $db->lastErrorMsg();
return false;
}
$sql = "UPDATE userscurves SET points=\"{$points}\" WHERE userlogin='******' AND meetingname='{$meetingname}'";
$ret = $db->exec($sql);
if ($ret > 0) {
$db->close();
return true;
} else {
$db->close();
return false;
}
}
function deleteQuestionnaires($ids)
{
$db = new SQLite3("db/db.sqlite3");
if (!$db) {
echo $db->lastErrorMsg();
return false;
}
foreach ($ids as $id) {
$sql = "DELETE FROM questionnaires WHERE id={$id}";
$ret = $db->query($sql);
}
foreach ($ids as $id) {
$sql = "DELETE FROM comparisons WHERE questionnaireid={$id}";
$ret = $db->query($sql);
}
$db->close();
return $ret;
}
function dbQuery($query, $show_errors = true, $all_results = true, $show_output = true)
{
if ($show_errors) {
error_reporting(E_ALL);
} else {
error_reporting(E_PARSE);
}
// Connect to the SQLite database file
$link = new SQLite3('/var/www/sqlmap/dbs/sqlite/testdb.sqlite3');
if (!$link) {
die($sqliteerror);
}
// Print results in HTML
print "<html><body>\n";
// Print SQL query to test sqlmap '--string' command line option
//print "<b>SQL query:</b> " . $query . "<br>\n";
// Perform SQL injection affected query
$result = $link->query($query);
if (!$result) {
if ($show_errors) {
print "<b>SQL error:</b> " . $link->lastErrorMsg() . "<br>\n";
}
exit(1);
}
if (!$show_output) {
exit(1);
}
print "<b>SQL results:</b>\n";
print "<table border=\"1\">\n";
while ($line = $result->fetchArray(SQLITE3_ASSOC)) {
print "<tr>";
foreach ($line as $col_value) {
print "<td>" . $col_value . "</td>";
}
print "</tr>\n";
if (!$all_results) {
break;
}
}
print "</table>\n";
print "</body></html>";
}
/**
* Wykonanie zapytania bazy danych
*
* @param string $query
* @return \SQLite3Result
* @throws Exception
*/
public function execute($query)
{
if (!$this->connected) {
$this->connect();
}
if ($this->dbHandle == null) {
return false;
}
$this->queryCount += 1;
$tResult = $this->dbHandle->query($query);
if (!$tResult) {
throw new Exception($this->dbHandle->lastErrorMsg(), $this->dbHandle->lastErrorCode());
}
/**
* logowanie zapytań do pliku
*/
if ($this->writeToFile) {
$this->writeLog($query);
}
return $tResult;
}
/**
* @param int $id
* @param array $data
* @param callable $callback
*
* @throws \Exception
*/
public function import(&$id, array $data, callable $callback)
{
foreach ($data as $uid => $elem) {
$valuesFullText = $this->valuesFullText($elem);
$valuesFilter = $this->valuesFilter($elem);
if (sizeof($valuesFullText) > 0) {
$json = json_encode($elem);
$num = 1;
$this->stmtInsertFilter->bindValue($num++, $id, SQLITE3_INTEGER);
if ($this->useuid) {
$this->stmtInsertFilter->bindValue($num++, $uid, SQLITE3_TEXT);
}
$this->stmtInsertFilter->bindValue($num++, $json, SQLITE3_TEXT);
foreach ($valuesFilter as $valueFilter) {
$this->stmtInsertFilter->bindValue($num++, $valueFilter);
}
if (@$this->stmtInsertFilter->execute() === false) {
$lasterror = $this->db->lastErrorCode();
if ($lasterror != self::SQLITE_ERROR_CODE_CONSTRAINT) {
$this->output->writeln($lasterror . " : " . $this->db->lastErrorMsg());
throw new \Exception("cannot insert filter fields");
} else {
@$this->stmtInsertFilter->reset();
continue;
}
}
$num = 1;
$this->stmtInsertFullText->bindValue($num++, $id, SQLITE3_INTEGER);
foreach ($valuesFullText as $valueFullText) {
$this->stmtInsertFullText->bindValue($num++, $valueFullText, SQLITE3_TEXT);
}
if ($this->stmtInsertFullText->execute() === false) {
$this->output->writeln($this->db->lastErrorCode() . " : " . $this->db->lastErrorMsg());
throw new \Exception("cannot insert full text fields");
}
$id++;
}
$callback();
}
}
function check($user, $pass)
{
try {
$db = new SQLite3(':memory:');
$db->query('CREATE TABLE IF NOT EXISTS users (username TEXT, password TEXT)');
$us = array('admin', 'user', 'member', 'bob');
foreach ($us as $u) {
$db->query("INSERT INTO users (username, password) SELECT '" . $u . "', '" . generatePassword() . "' WHERE NOT EXISTS (SELECT 1 FROM users WHERE username = '******')");
}
if (strlen($user) > 32 || strlen($pass) > 32) {
res('Username or password too long!');
}
$res = $db->query("SELECT COUNT(*) FROM users WHERE username = '******' AND password = '******'");
if (!$res) {
res('SQL Error: ' . $db->lastErrorMsg());
}
$row = $res->fetchArray();
return $row[0] != 0;
} catch (Exception $e) {
res('Error: ' . $e->getMessage());
error_log($e);
}
}
function addUserToMeeting($userlogin, $meetingName)
{
global $errMsg;
$db = new SQLite3("db/db.sqlite3");
if (!$db) {
echo $db->lastErrorMsg();
return false;
}
$sql = "SELECT * FROM userscurves WHERE userlogin = \"{$userlogin}\" and meetingname=\"{$meetingName}\"";
$ret = $db->query($sql);
if ($row = $ret->fetchArray(SQLITE3_ASSOC)) {
$errMsg = "There is already a meeting with this name.";
return false;
}
$sql = "INSERT INTO userscurves VALUES (\"{$userlogin}\", \"{$meetingName}\", \"\")";
$ret = $db->exec($sql);
if ($ret > 0) {
$db->close();
return true;
} else {
$db->close();
return false;
}
}
name,
email,
ip_addr,
time)
VALUES (
:name,
:email,
:ip_addr,
:time)');
$stmt->bindValue(':name', $clean['name']);
$stmt->bindValue(':email', $clean['email']);
$stmt->bindValue(':ip_addr', $_SERVER['REMOTE_ADDR']);
$stmt->bindValue(':time', time());
$result = $stmt->execute();
if ($result === false) {
$errors['db'] = $db->lastErrorMsg();
} else {
setcookie('atlphp_mysql_contest', 1, time() + 2592000);
$isSignedUp = true;
}
}
}
}
?>
<!doctype html>
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<title>Atlanta PHP - MySQL Training Raffle</title>
<link href="css/style.css" type="text/css" rel="stylesheet" media="all" charset="utf-8" />
<script type="text/javascript">
<?php
/**
* The MIT License (MIT)
*
* Copyright (c) 2015 Prashant Shah <*****@*****.**>
*
* Permission is hereby granted, free of charge, to any person obtaining a copy
* of this software and associated documentation files (the "Software"), to deal
* in the Software without restriction, including without limitation the rights
* to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
* copies of the Software, and to permit persons to whom the Software is
* furnished to do so, subject to the following conditions:
*
* The above copyright notice and this permission notice shall be included in
* all copies or substantial portions of the Software.
*
* THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
* IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
* FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
* AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
* LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
* OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
* THE SOFTWARE.
*/
$db = new SQLite3('../database/db.sqlite');
if (!$db) {
echo $db->lastErrorMsg();
}
public function getLastError()
{
$message = $this->dbConn->lastErrorMsg();
return $message === 'not an error' ? null : $message;
}
function addColumn($settingRes,$newColName,$newColVal){
if(!isset($settingRes[$newColName])){
$db=new SQLite3('./admin/admin.db');
$altercmd = 'ALTER TABLE admin ADD COLUMN '.$newColName.' TEXT';
$result = $db->exec($altercmd);
if(!$result){
$settingRes['dbmod']=$db->lastErrorMsg();
}
else{
$settingRes['dbmod']=$db->changes().' Record updated successfully.';
}
#Add initial data
$update = $newColName."='".$newColVal."'";
$result = $db->exec('UPDATE admin SET '.$update.' WHERE id=1');
if(!$result){
$addcolRes['dbset']=$db->lastErrorMsg();
}
else{
$addcolRes['dbset']=$db->changes().' Record updated successfully.';
}
$db->close();
return $addcolRes;
}
}
$startTime = microtime(true);
include "../include/functions.php";
include "../include/release-qa.php";
// sanitize
if (!preg_match('@^[a-z0-9]{32}$@', $_GET['signature'])) {
exit('Invalid signature');
}
if (!is_valid_php_version($_GET['version'], $QA_RELEASES)) {
exit('invalid version');
}
$signature = $_GET['signature'];
$version = $_GET['version'];
$dbFile = dirname(__FILE__) . '/db/' . $version . '.sqlite';
$database = new SQLite3($dbFile, SQLITE3_OPEN_READONLY);
if (!$database) {
die("Error opening DB file: " . $database->lastErrorMsg());
}
// GET infos from DB
$query = 'SELECT reports.* FROM failed JOIN reports ON reports.id=failed.id_report WHERE signature=X\'' . $signature . '\'';
$q = $database->query($query);
$reportsArray = array();
while ($tab = $q->fetchArray(SQLITE3_ASSOC)) {
$reportsArray[$tab['id']] = $tab;
}
$tab = $database->query('SELECT test_name FROM failed WHERE signature=X\'' . $signature . '\' LIMIT 1');
list($testName) = $tab->fetchArray(SQLITE3_NUM);
// We stop everything
$database->close();
$TITLE = "Report details";
common_header(array('<meta name="robots" content="noindex">'));
?>
<?php
if (!($db = new SQLite3('../db/jquery_workshop.db'))) {
die('Unable to open the database');
}
if (!$db->exec("CREATE TABLE IF NOT EXISTS categories (id INTEGER PRIMARY KEY AUTOINCREMENT,\n name varchar(100) NOT NULL)")) {
throw new Exception($db->lastErrorMsg());
}
if (!$db->exec("ALTER TABLE movies ADD COLUMN category_id INTEGER")) {
throw new Exception($db->lastErrorMsg());
}
echo "Database succesfully updated";
$db->close();
