PHP SQLite3::querySingle方法代码示例

/**
* Checks which files of a directory are missing in a SQLite3 database and returns a list of them.
*
* @arg dir The directory for which to check
* @arg dbfile The file containing the database
* @arg table The table name of the database
* @arg col The column containing the filenames
* @arg enckey The encryption key used for the database
* @returns A list of files missing from the database, or an empty list
*/
function missing_files_from_directory($dir, $dbfile, $table, $col, $enckey = NULL)
{
    $missing = array();
    $dirscan = scandir($dir, SCANDIR_SORT_ASCENDING);
    if ($dirscan == false) {
        // Either $dir is not a directory or scandir had no success
        return $missing;
    }
    try {
        if (is_string($enckey)) {
            $db = new SQLite3($dbfile, SQLITE3_OPEN_READONLY, $enckey);
        } else {
            $db = new SQLite3($dbfile, SQLITE3_OPEN_READONLY);
        }
    } catch (Exception $e) {
        // Database could not be opened; return empty array
        return $missing;
    }
    foreach ($dirscan as $file) {
        if (is_dir($file) || is_link($file)) {
            // Filtering out directories (. and ..) and links {
            continue;
        }
        if ($db->querySingle("SELECT EXISTS(SELECT * FROM " . $table . " WHERE " . $col . " = '" . SQLite3::escapeString($file) . "');")) {
            // if an entry exists, returns TRUE, otherwise FALSE; invalid or failing queries return FALSE
            continue;
        }
        // entry does not exist; add to array
        $missing[] = $file;
    }
    $db->close();
    sort($missing, SORT_LOCALE_STRING | SORT_FLAG_CASE);
    return $missing;
    // sort based on the locale, case-insensitive
}

function acctstart($input)
{
    require_once "settings.php";
    $input = $input;
    $delimiter1 = "The new session";
    $delimiter2 = "has been created";
    $pos1 = strpos($input, $delimiter1) + strlen($delimiter1) + 2;
    $pos2 = strpos($input, $delimiter2) - 2;
    $sstrlen = $pos2 - $pos1;
    $sessid = substr($input, $pos1, $sstrlen);
    exec($vpncmd . " " . $softetherip . " /SERVER /HUB:" . $hubname . " /PASSWORD:" . $apipass . " /CSV /CMD SessionGet " . $sessid, $SessionGet);
    if (strpos($SessionGet[0], "rror occurred") != FALSE) {
        die("Error - SessionGet resulted in error");
    }
    foreach ($SessionGet as $line) {
        list($key, $val) = explode(",", $line, 2);
        $result[$key] = $val;
    }
    $recheck = 0;
    dhcptest:
    sleep(2);
    exec($vpncmd . " " . $softetherip . " /SERVER /HUB:" . $hubname . " /PASSWORD:" . $apipass . " /CSV /CMD IpTable", $IpTable);
    $ok = 0;
    foreach ($IpTable as $line) {
        if (strpos($line, $sessid)) {
            if (strpos($line, "DHCP")) {
                list(, $key, $val) = explode(",", $line);
                list($framedip) = explode(" ", $val);
                #$result2[$key] = $val;
                $ok = 1;
            }
        }
    }
    if ($ok == 0) {
        if ($recheck == 4) {
            die("Error - could not find session in retrived IpTable data");
        }
        sleep(2);
        $recheck = $recheck + 1;
        goto dhcptest;
    }
    $db = new SQLite3($database);
    $db->exec('CREATE TABLE IF NOT EXISTS sessions (sessionid varchar(255), username varchar (255), clientip varchar (255), inputoctets varchar (255), ' . 'outputoctets varchar (255), framedip varchar (255), nasip varchar (255), nasport varchar (255), acctstarttime varchar (255), ' . 'acctsessiontime varchar (255), PRIMARY KEY(sessionid))');
    $query = $db->escapeString('INSERT OR REPLACE INTO sessions (sessionid, username, clientip, inputoctets, outputoctets, framedip, nasip, nasport, acctstarttime, acctsessiontime) VALUES ("' . $sessid . '","' . $result["User Name (Authentication)"] . '","' . $result["Client IP Address"] . '",NULL,NULL,"' . $framedip . '","' . $result["Server IP Address (Reported)"] . '","' . $result["Server Port (Reported)"] . '","' . $result["Connection Started at"] . '",NULL)');
    $db->exec($query);
    $sessid = $db->escapeString($sessid);
    $results = $db->querySingle("SELECT * FROM sessions WHERE sessionid = '" . $sessid . "'", true);
    $tmpfname = tempnam($tmpdir, "acctstarttmp_");
    $handle = fopen($tmpfname, "w");
    $packet = "Service-Type = Framed-User" . "\n" . "Framed-Protocol = PPP" . "\n" . "NAS-Port = " . $results['nasport'] . "\n" . "NAS-Port-Type = Async" . "\n" . "User-Name = '" . $results['username'] . "'" . "\n" . "Calling-Station-Id = '" . $results['clientip'] . "'" . "\n" . "Called-Station-Id = '" . $results['nasip'] . "'" . "\n" . "Acct-Session-Id = '" . $sessid . "'" . "\n" . "Framed-IP-Address = " . $results['framedip'] . "\n" . "Acct-Authentic = RADIUS" . "\n" . "Event-Timestamp = " . time() . "\n" . "Acct-Status-Type = Start" . "\n" . "NAS-Identifier = '" . $results['nasip'] . "'" . "\n" . "Acct-Delay-Time = 0" . "\n" . "NAS-IP-Address = " . $results['nasip'] . "\n";
    fwrite($handle, $packet);
    fclose($handle);
    exec("radclient " . $radiussrv . ":" . $radiusport . " acct " . $radiuspass . " -f " . $tmpfname);
    unlink($tmpfname);
    $db->close();
}

function getAPIs()
{
    $apis = array();
    $db = new SQLite3('./urls/urls.db');
    #Grab most recent VirusTotal API
    $vtresult = $db->querySingle('SELECT key FROM apis WHERE site = "vt" ORDER BY id DESC');
    $apis['vt'] = $vtresult;
    $wotresult = $db->querySingle('SELECT key FROM apis WHERE site = "wot" ORDER BY id DESC');
    $apis['wot'] = $wotresult;
    $googresult = $db->querySingle('SELECT key FROM apis WHERE site = "goog" ORDER BY id DESC');
    $apis['goog'] = $googresult;
    foreach ($apis as $id => $val) {
        if ($val == '') {
            exit("Error retrieving API key for ({$id}). Check the urls.db database.");
        }
    }
    #print_r($apis);
    return $apis;
}

function init_db()
{
    global $sqlite_database;
    $db = new SQLite3($sqlite_database);
    $test_query = "select count(*) from sqlite_master where name = 'credentials'";
    if ($db->querySingle($test_query) == 0) {
        $create_table = "create table credentials (userid text not null unique, " . "credentials text not null);";
        $db->exec($create_table);
    }
    return $db;
}

    function author_save($keyword = false, $session = false)
    {
        $db = new SQLite3('author.db') or die('Unable to open database');
        $query = <<<EOD
CREATE TABLE IF NOT EXISTS authors (
  author STRING PRIMARY KEY,
  session STRING )
EOD;
        $db->exec($query) or die('Create db failed');
        $count = $db->querySingle("SELECT count(*) as count FROM authors WHERE author='{$keyword}'");
        if ($count < 1) {
            $query = <<<EOD
INSERT INTO authors VALUES ( '{$keyword}','{$session}' )
EOD;
            $db->exec($query) or die("Unable to add author {$keyword}");
        }
        return;
    }

function acctstop($input)
{
    require_once "settings.php";
    $delimiter1 = "Session";
    $delimiter2 = ": The session has been terminated.";
    $pos1 = strpos($input, $delimiter1) + strlen($delimiter1) + 2;
    $pos2 = strpos($input, $delimiter2) - 1;
    $sstrlen = $pos2 - $pos1;
    $sessid = substr($input, $pos1, $sstrlen);
    $delimiter1 = "outgoing data size:";
    $delimiter2 = "bytes,";
    $pos1 = strpos($input, $delimiter1) + strlen($delimiter1) + 1;
    $pos2 = strpos($input, $delimiter2) - 1;
    $sstrlen = $pos2 - $pos1;
    $outdata = substr($input, $pos1, $sstrlen);
    $delimiter1 = "incoming data size:";
    $delimiter2 = "bytes.";
    $pos1 = strpos($input, $delimiter1) + strlen($delimiter1) + 1;
    $pos2 = strpos($input, $delimiter2) - 1;
    $sstrlen = $pos2 - $pos1;
    $indata = substr($input, $pos1, $sstrlen);
    $db = new SQLite3($database);
    $sessid = $db->escapeString($sessid);
    $results = $db->querySingle("SELECT * FROM sessions WHERE sessionid = '" . $sessid . "'", true);
    if ($results == FALSE) {
        die("Error - could not find sessionid");
    }
    list($time1, , $time2) = explode(" ", $results['acctstarttime']);
    $sessiontime = time() - strtotime($time1 . " " . $time2);
    $tmpfname = tempnam($tmpdir, "acctstoptmp_");
    $handle = fopen($tmpfname, "w");
    $packet = "Service-Type = Framed-User" . "\n" . "Framed-Protocol = PPP" . "\n" . "NAS-Port = " . $results['nasport'] . "\n" . "NAS-Port-Type = Async" . "\n" . "User-Name = '" . $results['username'] . "'" . "\n" . "Calling-Station-Id = '" . $results['clientip'] . "'" . "\n" . "Called-Station-Id = '" . $results['nasip'] . "'" . "\n" . "Acct-Session-Id = '" . $sessid . "'" . "\n" . "Framed-IP-Address = " . $results['framedip'] . "\n" . "Acct-Authentic = RADIUS" . "\n" . "Event-Timestamp = " . time() . "\n" . "Acct-Session-Time = " . $sessiontime . "\n" . "Acct-Input-Octets = " . $indata . "\n" . "Acct-Output-Octets = " . $outdata . "\n" . "Acct-Status-Type = Stop" . "\n" . "NAS-Identifier = '" . $results['nasip'] . "'" . "\n" . "Acct-Delay-Time = 0" . "\n" . "NAS-IP-Address = " . $results['nasip'] . "\n";
    fwrite($handle, $packet);
    fclose($handle);
    exec("radclient " . $radiussrv . ":" . $radiusport . " acct " . $radiuspass . " -f " . $tmpfname);
    unlink($tmpfname);
    $db->exec("DELETE FROM sessions WHERE sessionid = '" . $sessid . "' LIMIT 1");
    $db->close();
}

if (!isset($_GET['act'])) {
    $_GET['act'] = "calendar";
}
if ($_GET['act'] != "view" && $_GET['act'] != "games" && $_GET['act'] != "stats" && $_GET['act'] != "calendar") {
    $_GET['act'] = "calendar";
}
if (file_exists("./echl15-16.db3")) {
    $sqldb = new SQLite3("./echl15-16.db3");
} else {
    $sqldb = new SQLite3("../hockey15-16.db3");
}
$sqldb->exec("PRAGMA encoding = \"UTF-8\";");
$sqldb->exec("PRAGMA auto_vacuum = 1;");
$sqldb->exec("PRAGMA foreign_keys = 1;");
$sqlite_games_string = "";
$firstgamedate = $sqldb->querySingle("SELECT Date FROM " . $leaguename . "Games WHERE id=1");
$lastgamedate = $sqldb->querySingle("SELECT Date FROM " . $leaguename . "Games WHERE id=(SELECT MAX(id) FROM " . $leaguename . "Games)");
if ($lastgamedate < gmdate("Y") . gmdate("m") . gmdate("d")) {
    $lastgamedate = gmdate("Y") . gmdate("m") . gmdate("d");
}
if ($_GET['act'] == "calendar") {
    if (isset($_GET['date']) && strlen($_GET['date']) == 8) {
        if (!isset($_GET['month']) || !is_numeric($_GET['month'])) {
            $_GET['month'] = substr($_GET['date'], 4, 2);
        }
        if (!isset($_GET['year']) || !is_numeric($_GET['year'])) {
            $_GET['year'] = substr($_GET['date'], 0, 4);
        }
    }
    if (isset($_GET['date']) && strlen($_GET['date']) == 6) {
        if (!isset($_GET['month']) || !is_numeric($_GET['month'])) {

            $handle->exec("update ZTFCSTATIONMODEL set ZAPIKEY = 'gva', ZAPIID = '" . $stop['stopCode'] . "' where ZID = '" . $row['ZID'] . "'");
        }
        if ($distance < 250) {
            if ($row['ZNAME'] == $stop['stopName']) {
                $handle->exec("update ZTFCSTATIONMODEL set ZAPIKEY = 'gva', ZAPIID = '" . $stop['stopCode'] . "' where ZID = '" . $row['ZID'] . "'");
            } else {
                $parts = explode(",", $row['ZNAME']);
                if (isset($parts[1])) {
                    if (trim($parts[1]) == $stop['stopName']) {
                        $handle->exec("update ZTFCSTATIONMODEL set ZAPIKEY = 'gva', ZAPIID = '" . $stop['stopCode'] . "' where ZID = '" . $row['ZID'] . "'");
                        print $parts[1] . ' == ' . $stop['stopName'] . "\n";
                    }
                } else {
                    print "Distance ({$distance}) too long for " . $row['ZNAME'] . "\n";
                }
            }
        } else {
            print "Distance ({$distance}) too long for " . $row['ZNAME'] . "\n";
        }
    } else {
        print "nothing found for " . $row['ZNAME'] . "< {$url} >\n";
    }
}
$result = $handle->querySingle("select count(*) from ZTFCSTATIONMODEL where ZCOUNTY = 'Geneva' AND ZDEPARTURESURL ISNULL  ");
print $result . " are still without an URL\n";
function getFirst($url)
{
    $result = file_get_contents("http://www.timeforcoffee.ch/api/" . $url);
    $r = json_decode($result, true);
    return $r['departures'];
}

<?php

$query = "UPDATE prestiolus SET datereturn = datetime() WHERE id = '" . $_GET['id'] . "';";
//update prestiolus set datereturn = datetime() where id = '4';
$db = new SQLite3('prestiolus.db');
$db->querySingle($query);
header('Location: index.php');

<?php

session_start();
if (empty($_SESSION['user_name']) || !empty($_SESSION['role']) && $_SESSION['role'] != "admin") {
    header('Location: login.php');
}
include "config.inc";
$list_db = new SQLite3("lists.db");
$where = "";
$total_pages = $list_db->querySingle('SELECT COUNT(*) FROM lists' . $where);
$limit = 20;
$page = empty($_GET["page"]) ? 1 : $_GET["page"];
if ($page > 1) {
    $start = ($page - 1) * $limit;
} else {
    $start = 0;
}
$results = $list_db->query("SELECT * FROM lists ORDER BY evt_updatetime DESC LIMIT {$start}, {$limit}");
$arrs = array();
while ($row = $results->fetchArray()) {
    $id = $row["evt_id"];
    $outPath = dirname(__FILE__) . '/content/' . $id . "/avatar.jpg";
    //echo filesize($outPath);
    if (file_exists($outPath) && filesize($outPath) > 1024) {
        continue;
    } else {
        array_push($arrs, $id);
    }
}
?>
<div id="msg"></div>

                 $update->execute();
             }
             $res->finalize();
         }
         ++$count;
         $merge->bindValue('id', $count);
         $merge->execute();
         $res = $tdb->query("SELECT ID FROM poly WHERE type = 1");
         $diff->bindValue('outer', $count);
         while ($row = $res->fetchArray(SQLITE3_NUM)) {
             echo "Diffing " . $row[0] . "\n";
             $diff->bindValue('inner', $row[0]);
             $diff->execute();
         }
         $res->finalize();
         $endpoly = $tdb->querySingle("SELECT AsText(polygon) FROM poly WHERE id = " . (int) $count);
         echo $endpoly . "\n";
         if (!empty($insert_final) && !empty($endpoly)) {
             $insert_final->bindValue('id', $p->zone, SQLITE3_INTEGER);
             $insert_final->bindValue('name', $p->name);
             $insert_final->bindValue('region', $p->region);
             $insert_final->bindValue('realm', $p->realm);
             $insert_final->bindValue('geom', $endpoly);
             $insert_final->execute();
         }
     }
     break;
 case 'exportsql':
     if (empty($args->command->options['spatialite'])) {
         throw new Exception("Must specify spatialite DB");
     }

<?php

header("X-XSS-Protection: 0");
if (empty($_GET["id"])) {
    die("活动ID 不能为空");
}
$usert = "";
$id = $_GET["id"];
$path = dirname(__FILE__) . '/content/' . $id;
$list_db = new SQLite3("lists.db");
$r = $list_db->querySingle('SELECT * FROM lists WHERE evt_id = "' . $id . '"', true);
if (!empty($r)) {
    if (!empty($usert) && $usert != $r["evt_author"]) {
        die("<meta charset='utf-8'>权限不足，无法查看");
    }
    $evt_name = $r["evt_name"];
    $evt_desc = $r["evt_desc"];
    $tpl_file = $path . "/template.html";
    $tplFileHandle = fopen($tpl_file, 'rb') or die("can't open file");
    $tpl_html = fread($tplFileHandle, filesize($tpl_file));
    fclose($tplFileHandle);
    $pubLayoutFile = dirname(__FILE__) . "/tpl/layout.html";
    $fp = fopen($pubLayoutFile, 'rb');
    $pubLayout = fread($fp, filesize($pubLayoutFile));
    fclose($fp);
    //由于活动平台限制，转换编码为 gbk
    $evt_name = iconv("utf-8", "gbk", $evt_name);
    $evt_desc = iconv("utf-8", "gbk", $evt_desc);
    $tpl_html = iconv("utf-8", "gbk", $tpl_html);
    $pubLayout = str_replace("[TITLE]", $evt_name, $pubLayout);
    $pubLayout = str_replace("[CONTENT]", $tpl_html, $pubLayout);

<?php

error_reporting(E_ALL);
ini_set('display_errors', 1);
$email = $_POST["email"];
$password = $_POST["password"];
$hashAndSalt = password_hash($password, PASSWORD_BCRYPT);
$db = new SQLite3('../user.db');
$db->query('create table if not exists user(email varchar(255), password varchar(255), primary key(email))');
$queryResult = $db->querySingle("select * from user where email='{$email}';");
if ($queryResult) {
    printf("failure-registered");
} else {
    $db->query("insert into user(email, password) values('{$email}', '{$hashAndSalt}');");
    printf("success", $email);
}
$db->close();
?>

/* Retrieving Champion Ladder Information */
$sort_field = $_GET["sort_field"];
$sort_type = $_GET["sort_type"];
$ladderscount = $_GET["count"];
$db = new SQLite3("../pokemon");
$xml = new DomDocument();
$xml->load("../tiers.xml");
$tiers = $xml->getElementsByTagName("tier");
$ladderchamps = array();
$standings = "";
$rowcount = 0;
foreach ($tiers as $tier) {
    $tiername = $tier->getAttribute("name");
    $tablename = $tier->getAttribute("tableName");
    $result = $db->querySingle("SELECT COUNT(*) FROM " . $tablename);
    if ($result) {
        $results = $db->query("SELECT * FROM " . $tablename . " WHERE displayed_rating = (SELECT MAX(displayed_rating) FROM " . $tablename . ")");
        while ($row = $results->fetchArray()) {
            if (isset($ladderchamps[$row['name']]) == false) {
                $ladderchamps[$row['name']] = array();
                $ladderchamps[$row['name']]['ladders'] = array();
                $ladderchamps[$row['name']]['count'] = 0;
                $ladderchamps[$row['name']]['displayed_rating'] = 0;
                $ladderchamps[$row['name']]['rating'] = 0;
            }
            $ladderchamps[$row['name']]['count']++;
            array_push($ladderchamps[$row['name']]['ladders'], $tiername);
            $ladderchamps[$row['name']]['displayed_rating'] += $row['displayed_rating'];
            $ladderchamps[$row['name']]['rating'] += $row['rating'];
        }

<?php

$dbfile = getenv("DATA") . "library.db";
$db = new SQLite3($dbfile);
$root = $db->querySingle("SELECT value FROM config WHERE key = 'root'");
if (array_key_exists("id", $_GET)) {
    $id = $_GET["id"];
    $stmt = $db->prepare("SELECT EXISTS(SELECT 1 FROM songs WHERE id = ?)");
    $stmt->bindValue(1, $id, SQLITE3_INTEGER);
    if (!$stmt->execute()->fetchArray()[0]) {
        http_response_code(404);
        die;
    }
    $path = $root . "/" . $db->querySingle("SELECT path FROM songs WHERE id = {$id}");
    if (!is_readable($path)) {
        http_response_code(403);
        die;
    }
    $fhandle = fopen($path, "rb");
    if (!$fhandle) {
        http_response_code(403);
        die;
    }
    $finfo = finfo_open();
    $expires = 60 * 60 * 24 * 365;
    header("Pragma: public");
    header("Cache-Control: maxage=" . $expires);
    header("Expires: " . gmdate("D, d M Y H:i:s", time() + $expires) . " UTC");
    header("Content-Type: " . finfo_file($finfo, $path, FILEINFO_MIME));
    finfo_close($finfo);
    while (!feof($fhandle)) {