PHP db_prep_query函数代码示例

function auth_user($name, $pw)
{
    $setEncPw = false;
    $sql = "SELECT * FROM mb_user WHERE mb_user_name = \$1 AND mb_user_password = \$2";
    $v = array($name, md5($pw));
    $t = array('s', 's');
    $res = db_prep_query($sql, $v, $t);
    if ($row = db_fetch_array($res)) {
        return $row;
    } else {
        if (SYS_DBTYPE == 'pgsql' && $setEncPw == true) {
            // 	unencrypted pw in postgres without md5-support?
            $sql = "SELECT * FROM mb_user WHERE mb_user_name = \$1 AND mb_user_password = \$2";
            $v = array($name, $pw);
            $t = array('s', 's');
            $resn = db_prep_query($sql, $v, $t);
            if ($rown = db_fetch_array($resn)) {
                $sqlu = "UPDATE mb_user SET mb_user_password = \$1 WHERE mb_user_id = \$2";
                $vu = array(md5($pw), $rown["mb_user_id"]);
                $tu = array('s', 'i');
                $rowu = db_prep_query($sqlu, $vu, $tu);
                return $rown;
            }
        }
    }
}

/**
 * If access to the WFS conf is restricted, modify the filter.
 * TODO: this function is also in mod_wfs_result!! Maybe merge someday.
 */
function checkAccessConstraint($filter, $wfs_conf_id)
{
    /* wfs_conf_element */
    $sql = "SELECT * FROM wfs_conf_element ";
    $sql .= "JOIN wfs_element ON wfs_conf_element.f_id = wfs_element.element_id ";
    $sql .= "WHERE wfs_conf_element.fkey_wfs_conf_id = \$1 ";
    $sql .= "ORDER BY wfs_conf_element.f_respos";
    $v = array($wfs_conf_id);
    $t = array('i');
    $res = db_prep_query($sql, $v, $t);
    while ($row = db_fetch_array($res)) {
        if (!empty($row["f_auth_varname"])) {
            $auth_varname = $row["f_auth_varname"];
            $element_name = $row["element_name"];
        }
    }
    if (!empty($auth_varname)) {
        if (isValidVarName($auth_varname)) {
            $user = eval("return " . $auth_varname . ";");
            if ($user) {
                $pattern = "(<ogc:Filter[^>]*>)(.*)(</ogc:Filter>)";
                $replacement = "\\1<And>\\2<ogc:PropertyIsEqualTo><ogc:PropertyName>" . $element_name . "</ogc:PropertyName><ogc:Literal>" . $user . "</ogc:Literal></ogc:PropertyIsEqualTo></And>\\3";
                $filter = mb_eregi_replace($pattern, $replacement, $filter);
            } else {
                $e = new mb_exception("mod_wfsGazetteerEditor_server: checkAccessConstraint: invalid value of variable containing user information!");
            }
        } else {
            $e = new mb_exception("mod_wfsGazetteerEditor_server: checkAccessConstraint: var name is not valid! (" . $auth_varname . ")");
        }
    }
    return $filter;
}

function forgotten_password()
{
    if (!isset($_REQUEST["Benutzername"]) || !isset($_REQUEST["EMail"]) || $_REQUEST["Benutzername"] == 'guest' || empty($_REQUEST["Benutzername"]) || empty($_REQUEST["EMail"]) || !(bool) trim($_REQUEST["Benutzername"]) || !(bool) trim($_REQUEST["EMail"])) {
        return -1;
    }
    if (!USE_PHP_MAILING) {
        return -4;
    }
    $administration = new administration();
    define("USER_NAME", trim($_REQUEST["Benutzername"]));
    define("USER_EMAIL", trim($_REQUEST["EMail"]));
    if (!$administration->getUserIdByUserName(USER_NAME) || USER_EMAIL != $administration->getEmailByUserId($administration->getUserIdByUserName(USER_NAME))) {
        return -2;
    }
    $new_password = $administration->getRandomPassword();
    $sql_update = "UPDATE mb_user SET mb_user_password = \$1, mb_user_digest = \$3 WHERE mb_user_id = \$2";
    $v = array(md5($new_password), $administration->getUserIdByUserName(USER_NAME), md5(USER_NAME . ";" . USER_EMAIL . ":" . REALM . ":" . $new_password));
    $t = array("s", "i");
    if (!db_prep_query($sql_update, $v, $t)) {
        return -3;
    }
    $email_subject = "New GeoPortal.rlp Password";
    $email_body = sprintf("Your new GeoPortal.rlp password is: %s", $new_password);
    if (!$administration->sendEmail(NULL, NULL, USER_EMAIL, USER_NAME, $email_subject, $email_body, $error_msg)) {
        return -4;
    }
    return 1;
}

 /**
  * increments the load count in table "layer_load_count" for
  * each layer in the WMC document by one. 
  */
 function increment($layer_id)
 {
     if (!is_numeric($layer_id)) {
         return false;
     }
     //check if an entry exists for the current layer id
     $sql = "SELECT COUNT(layer_id) AS i FROM layer WHERE layer_id = \$1";
     $v = array($layer_id);
     $t = array('i');
     $res = db_prep_query($sql, $v, $t);
     $row = db_fetch_array($res);
     if (intval($row["i"]) === 0) {
         return false;
     }
     //check if an entry exists for the current layer id
     $sql = "SELECT load_count FROM layer_load_count WHERE fkey_layer_id = \$1";
     $v = array($layer_id);
     $t = array('i');
     $res = db_prep_query($sql, $v, $t);
     $row = db_fetch_array($res);
     //if yes, increment the load counter
     if ($row) {
         $currentCount = $row["load_count"];
         $sql = "UPDATE layer_load_count SET load_count = \$1 WHERE fkey_layer_id = \$2";
         $v = array(intval($currentCount + 1), $layer_id);
         $t = array('i', 'i');
         $res = db_prep_query($sql, $v, $t);
     } else {
         $sql = "INSERT INTO layer_load_count (fkey_layer_id, load_count) VALUES (\$1, 1)";
         $v = array($layer_id);
         $t = array('i');
         $res = db_prep_query($sql, $v, $t);
     }
 }

/**
 * This function saves the data into the database
 * @param string $data the content of the sld-document to be stored inside the database
 */
function saveSld($data)
{
    $con = db_connect($DBSERVER, $OWNER, $PW);
    db_select_db($DB, $con);
    $sql = "UPDATE sld_user_layer SET sld_xml=\$1 WHERE fkey_gui_id=\$2 AND fkey_layer_id=\$3 AND fkey_mb_user_id=\$4";
    $v = array($data, $_SESSION["sld_gui_id"], $_SESSION["sld_layer_id"], $_SESSION["mb_user_id"]);
    $t = array('s', 's', 'i', 'i');
    $res = db_prep_query($sql, $v, $t);
}

 public static function getWfsIdByFeaturetypeId($id)
 {
     $sql = "SELECT DISTINCT fkey_wfs_id FROM wfs_featuretype WHERE featuretype_id = \$1";
     $res = db_prep_query($sql, array($id), array("i"));
     $row = db_fetch_assoc($res);
     if ($row) {
         return $row["fkey_wfs_id"];
     }
     return null;
 }

 private function getVersionByWmsId($id)
 {
     $sql = "SELECT wms_version FROM wms WHERE wms_id = \$1";
     $v = array($id);
     $t = array("i");
     $res = db_prep_query($sql, $v, $t);
     $row = db_fetch_array($res);
     if ($row) {
         return $row["wms_version"];
     }
     return null;
 }

function getEpsgByLayerId($layer_id)
{
    // from merge_layer.php
    $epsg_list = "";
    $sql = "SELECT DISTINCT epsg FROM layer_epsg WHERE fkey_layer_id = \$1";
    $v = array($layer_id);
    $t = array('i');
    $res = db_prep_query($sql, $v, $t);
    while ($row = db_fetch_array($res)) {
        $epsg_list .= $row['epsg'] . " ";
    }
    return trim($epsg_list);
}

function saveLayerWfsConnection($myWfsConf, $myGui, $myLayer)
{
    $sql = "UPDATE gui_layer SET gui_layer_wfs_featuretype = \$1 ";
    $sql .= "WHERE fkey_gui_id = \$2 AND fkey_layer_id = \$3";
    $v = array($myWfsConf, $myGui, $myLayer);
    $t = array('s', 's', 'i');
    $res = db_prep_query($sql, $v, $t);
    if ($res) {
        return true;
    } else {
        return false;
    }
}

function mb_getGUIs($mb_user_id)
{
    $arrayGuis = array();
    if (isset($mb_user_id)) {
        $sql_groups = "SELECT fkey_mb_group_id FROM mb_user_mb_group WHERE fkey_mb_user_id = \$1 ";
        $v = array($mb_user_id);
        $t = array('i');
        $res_groups = db_prep_query($sql_groups, $v, $t);
        $cnt_groups = 0;
        while (db_fetch_row($res_groups)) {
            $mb_user_groups[$cnt_groups] = db_result($res_groups, $cnt_groups, "fkey_mb_group_id");
            $cnt_groups++;
        }
        $count_g = 0;
        if ($cnt_groups > 0) {
            $v = array();
            $t = array();
            $sql_g = "SELECT DISTINCT gui.gui_id FROM gui JOIN gui_mb_group ";
            $sql_g .= " ON gui.gui_id = gui_mb_group.fkey_gui_id WHERE( gui_mb_group.fkey_mb_group_id IN (";
            for ($i = 0; $i < count($mb_user_groups); $i++) {
                if ($i > 0) {
                    $sql_g .= ",";
                }
                $sql_g .= "\$" . ($i + 1);
                array_push($v, $mb_user_groups[$i]);
                array_push($t, 'i');
            }
            $sql_g .= "))";
            $res_g = db_prep_query($sql_g, $v, $t);
            while (db_fetch_row($res_g)) {
                $arrayGuis[$count_g] = db_result($res_g, $count_g, "gui_id");
                $count_g++;
            }
        }
        $sql_guis = "SELECT DISTINCT gui.gui_id FROM gui JOIN gui_mb_user ";
        $sql_guis .= "ON gui.gui_id = gui_mb_user.fkey_gui_id WHERE (gui_mb_user.fkey_mb_user_id = \$1) ";
        $sql_guis .= " AND gui.gui_public = 1";
        $v = array($mb_user_id);
        $t = array('i');
        $res_guis = db_prep_query($sql_guis, $v, $t);
        $count_guis = 0;
        while (db_fetch_row($res_guis)) {
            if (!in_array(db_result($res_guis, $count_guis, "gui_id"), $arrayGuis)) {
                $arrayGuis[$count_g] = db_result($res_guis, $count_guis, "gui_id");
                $count_g++;
            }
            $count_guis++;
        }
    }
    return $arrayGuis;
}

 function log($module, $req, $time_client, $type = "")
 {
     $this->url = $req;
     if ($type == "") {
         $type = $this->logtype;
     }
     if ($type == "file") {
         if (is_dir($this->dir)) {
             $logfile = $this->dir . "mb_access_" . date("Y_m_d") . ".log";
             if (!($h = @fopen($logfile, "a"))) {
                 #exit;
             } else {
                 for ($i = 0; $i < count($this->url); $i++) {
                     $content = strtotime("now") . " ";
                     $content .= "[" . date("d/M/Y:H:i:s O") . "]";
                     $content .= " " . Mapbender::session()->get("mb_user_ip");
                     $content .= ' "';
                     if ($this->log_username == true) {
                         $content .= Mapbender::session()->get("mb_user_name");
                     }
                     $content .= '"';
                     $content .= " " . Mapbender::session()->get("mb_user_id");
                     $content .= " " . $module;
                     $content .= ' "' . $this->url[$i] . '"';
                     $content .= chr(13) . chr(10);
                     if (!fwrite($h, $content)) {
                         #exit;
                     }
                 }
                 fclose($h);
             }
         }
     } else {
         if ($type == 'db') {
             for ($i = 0; $i < count($this->url); $i++) {
                 $sql = "INSERT INTO mb_log (";
                 $sql .= "time_client, time_server, time_readable, mb_session, ";
                 $sql .= "gui, module, ip, username, userid, request";
                 $sql .= ") VALUES (\$1, \$2, \$3, \$4, \$5, \$6, \$7, \$8, \$9, \$10)";
                 $v = array($time_client, strtotime("now"), "[" . date("d/M/Y:H:i:s O") . "]", SID, Mapbender::session()->get("mb_user_gui"), $module, Mapbender::session()->get("mb_user_ip"), Mapbender::session()->get("mb_user_name"), Mapbender::session()->get("mb_user_id"), $this->url[$i]);
                 $t = array("s", "s", "s", "s", "s", "s", "s", "s", "s", "s");
                 $res = db_prep_query($sql, $v, $t) or die(db_error());
                 if (!$res) {
                     include_once dirname(__FILE__) . "/class_mb_exception.php";
                     $e = new mb_exception("class_log: Writing table mb_log failed.");
                 }
             }
         }
     }
 }

function authenticate($name, $pw)
{
    $con = db_connect(DBSERVER, OWNER, PW);
    db_select_db(DB, $con);
    $sql = "SELECT * FROM mb_user WHERE mb_user_name = \$1 AND mb_user_password = \$2";
    $v = array($name, md5($pw));
    // is md5 used really?
    $t = array('s', 's');
    $res = db_prep_query($sql, $v, $t);
    if ($row = db_fetch_array($res)) {
        $e = new mb_exception('row mb_user_name: ' . $row['mb_user_name']);
        return $row;
    } else {
        return false;
    }
}

function createIsoTopicCategoryString($prefix, $languageCode)
{
    $str = "";
    $htmlrows = "";
    $sql = "SELECT md_topic_category_id, md_topic_category_code_" . $languageCode . " FROM md_topic_category";
    $v = array();
    $t = array();
    $res = db_prep_query($sql, $v, $t);
    if (db_error()) {
        return "Could not get Categories from db";
    }
    $i = 0;
    while ($row = db_fetch_array($res)) {
        $i++;
        $str .= "<label for=\"{$prefix}_wmcIsoTopicCategory_{$row[0]}\">" . "<input class=\"wmcIsoTopicCategory\" id=\"{$prefix}_wmcIsoTopicCategory_{$row[0]}\" " . "type=\"checkbox\" />{$row[1]}</label>";
    }
    return $str;
}

 public function select($id, $applicationId)
 {
     $sql = "SELECT fkey_gui_id, e_id, e_pos, e_public, e_comment, e_public, " . "gettext(\$1, e_title) as e_title, e_element, e_src, e_attributes, " . "e_left, e_top, e_width, e_height, e_z_index, e_more_styles, " . "e_content, e_closetag, e_js_file, e_mb_mod, e_target, " . "e_requires, e_url FROM gui_element WHERE e_id = \$2 AND " . "fkey_gui_id = \$3 LIMIT 1";
     $v = array(Mapbender::session()->get("mb_lang"), $id, $applicationId);
     $t = array("s", "s", "s");
     $res = db_prep_query($sql, $v, $t);
     $row = db_fetch_array($res);
     if ($row) {
         $this->guiId = $applicationId;
         $this->id = $row["e_id"];
         $this->pos = $row["e_pos"];
         $this->isPublic = $row["e_public"];
         $this->comment = $row["e_comment"];
         $this->title = $row["e_title"];
         $this->element = $row["e_element"];
         $this->src = $row["e_src"];
         $this->attributes = $row["e_attributes"];
         $this->left = $row["e_left"];
         $this->top = $row["e_top"];
         $this->width = $row["e_width"];
         $this->height = $row["e_height"];
         $this->zIndex = $row["e_z_index"];
         $this->moreStyles = $row["e_more_styles"];
         $this->content = $row["e_content"];
         $this->closeTag = $row["e_closetag"];
         $this->jsFile = $row["e_js_file"];
         $this->mbMod = $row["e_mb_mod"];
         $this->target = $row["e_target"];
         $this->requires = $row["e_requires"];
         $this->helpUrl = $row["e_url"];
         $sql = "SELECT var_name FROM gui_element_vars WHERE fkey_gui_id = \$1 AND fkey_e_id = \$2;";
         $v = array($applicationId, $id);
         $t = array("s", "s");
         $res = db_prep_query($sql, $v, $t);
         while ($row = db_fetch_assoc($res)) {
             $name = $row["var_name"];
             $this->elementVars[] = new ElementVar($applicationId, $id, $name);
         }
         return true;
     }
     return false;
 }

/**
 * If access to the WFS conf is restricted, modify the filter.
 * TODO: this function is also in mod_wfs_result!! Maybe merge someday.
 */
function checkAccessConstraint($filter, $wfs_conf_id)
{
    /* wfs_conf_element */
    $sql = "SELECT f.featuretype_name AS name FROM " . "wfs_featuretype AS f, wfs_conf AS c " . "WHERE c.wfs_conf_id = \$1 AND " . "c.fkey_featuretype_id = f.featuretype_id";
    $v = array($wfs_conf_id);
    $t = array('i');
    $res = db_prep_query($sql, $v, $t);
    $row = db_fetch_array($res);
    if ($row) {
        $ns = substr($row["name"], 0, strpos($row["name"], ":")) . ":";
    } else {
        $ns = "";
    }
    unset($sql);
    unset($v);
    unset($t);
    unset($res);
    $sql = "SELECT * FROM wfs_conf_element ";
    $sql .= "JOIN wfs_element ON wfs_conf_element.f_id = wfs_element.element_id ";
    $sql .= "WHERE wfs_conf_element.fkey_wfs_conf_id = \$1 ";
    $sql .= "ORDER BY wfs_conf_element.f_respos";
    $v = array($wfs_conf_id);
    $t = array('i');
    $res = db_prep_query($sql, $v, $t);
    while ($row = db_fetch_array($res)) {
        if (!empty($row["f_auth_varname"])) {
            $auth_varname = $row["f_auth_varname"];
            $element_name = $row["element_name"];
        }
    }
    $e = new mb_exception($auth_varname . " " . $element_name);
    if (!empty($auth_varname)) {
        if (isValidVarName($auth_varname)) {
            $user = eval("return " . $auth_varname . ";");
            $pattern = "(<ogc:Filter[^>]*>)(.*)(</ogc:Filter>)";
            $replacement = "\\1<And>\\2<ogc:PropertyIsEqualTo><ogc:PropertyName>" . $ns . $element_name . "</ogc:PropertyName><ogc:Literal>" . $user . "</ogc:Literal></ogc:PropertyIsEqualTo></And>\\3";
            $filter = eregi_replace($pattern, $replacement, $filter);
        }
    }
    return $filter;
}