PHP sanitizeSQL函数代码示例

<?php

include "standardFormProcedures.php";
include "movieInputer.html";
// Grabbing data from POST global and attempting login
$username = grabFrom_POST("username");
$password = grabFrom_POST("password");
// Connect to database
$connection = new mysqli($host, $user, $pass, "movie_collection2");
if ($connection->connect_error) {
    die("Unable to connect!");
}
sanitizeSQL($username, $connection);
sanitizeSQL($password, $connection);
// grab reviews the user has made before
$movie_manager = new MovieManager($connection);
$result = $movie_manager->pullAllUserReviews($username);

    trigger_error("Caught Exception: " . $e->getMessage(), E_USER_ERROR);
}
# Performs the query and returns XML or JSON
try {
    $sql = "select length(line_geom), area(poly_geom) from (select ";
    $st_line_from_text = "Transform(ST_GeomFromText('LINESTRING(";
    $st_poly_from_text = "Transform(ST_GeomFromText('POLYGON((";
    $first_point = $points[0];
    $first_point_parts = explode(",", $first_point);
    $first_point = $first_point_parts[0] . " " . $first_point_parts[1];
    foreach ($points as $point) {
        $pointparts = explode(",", $point);
        $x = $pointparts[0];
        $y = $pointparts[1];
        $st_line_from_text .= $x . " " . $y . ", ";
        $st_poly_from_text .= $x . " " . $y . ", ";
    }
    $st_line_from_text = substr_replace($st_line_from_text, "", -2);
    $st_poly_from_text .= $first_point;
    $st_line_from_text .= ")'," . $in_srid . ")," . $out_srid . ") as line_geom, ";
    $st_poly_from_text .= "))'," . $in_srid . ")," . $out_srid . ") as poly_geom";
    $sql .= $st_line_from_text . $st_poly_from_text . ") as geoms";
    $sql = sanitizeSQL($sql);
    $pgconn = pgConnection();
    /*** fetch into an PDOStatement object ***/
    $recordSet = $pgconn->prepare($sql);
    $recordSet->execute();
    require_once "../inc/format.inc.php";
} catch (Exception $e) {
    trigger_error("Caught Exception: " . $e->getMessage(), E_USER_ERROR);
}

require_once "../inc/database.inc.php";
require_once "../inc/security.inc.php";
# Set arguments for error email
$err_user_name = "Tobin";
$err_email = "tobin.bradley@mecklenburgcountync.gov";
# Retrive URL arguments
try {
    $id = $_REQUEST['id'];
    $format = trim($_REQUEST['format']);
} catch (Exception $e) {
    trigger_error("Caught Exception: " . $e->getMessage(), E_USER_ERROR);
}
# Performs the query and returns XML or JSON
try {
    // if completed house number exists, find it and use it
    $sql = sanitizeSQL("select geonameid, name, latitude, longitude, elevation, population, gtopo30, modification_date from geonames where geonameid = " . $id);
    $pgconn = pgConnection();
    /*** fetch into an PDOStatement object ***/
    $recordSet = $pgconn->prepare($sql);
    $recordSet->execute();
    if ($format == 'xml') {
        require_once "../inc/xml.pdo.inc.php";
        header("Content-Type: text/xml");
        echo rs2xml($recordSet);
    } elseif ($format == 'json') {
        require_once "../inc/json.pdo.inc.php";
        header("Content-Type: application/json");
        echo rs2json($recordSet);
    } else {
        trigger_error("Caught Exception: format must be xml or json.", E_USER_ERROR);
    }

$err_user_name = "Tobin";
$err_email = "tobin.bradley@mecklenburgcountync.gov";
# Retrive URL arguments
try {
    $name = $_REQUEST['name'];
    $format = trim($_REQUEST['format']);
    if (strlen($name) < 2) {
        trigger_error("Caught exception: name parameter must include at least two characters.", E_USER_ERROR);
    }
} catch (Exception $e) {
    trigger_error("Caught Exception: " . $e->getMessage(), E_USER_ERROR);
}
# Performs the query and returns XML or JSON
try {
    // if completed house number exists, find it and use it
    $sql = sanitizeSQL("select geonameid, name from geonames where name ~* '" . strtoupper($name) . "' order by name ");
    $pgconn = pgConnection();
    /*** fetch into an PDOStatement object ***/
    $recordSet = $pgconn->prepare($sql);
    $recordSet->execute();
    if ($format == 'xml') {
        require_once "../inc/xml.pdo.inc.php";
        header("Content-Type: text/xml");
        echo rs2xml($recordSet);
    } elseif ($format == 'json') {
        require_once "../inc/json.pdo.inc.php";
        header("Content-Type: application/json");
        echo rs2json($recordSet);
    } elseif ($format == "text") {
        header("Content-Type: application/text");
        while (!$recordSet->EOF) {

require_once "../inc/error.inc.php";
require_once "../inc/database.inc.php";
require_once "../inc/security.inc.php";
# Set arguments for error email
$err_user_name = "Matt";
$err_email = "matt.crisp@mecklenburgcountync.gov";
# Retrive URL arguments
try {
    $busname = $_REQUEST['company'];
    $format = trim($_REQUEST['format']);
} catch (Exception $e) {
    trigger_error("Caught Exception: " . $e->getMessage(), E_USER_ERROR);
}
# Performs the query and returns XML or JSON
try {
    $sql = sanitizeSQL("select distinct(company) from businesswise_businesses where company ~* '" . $busname . "' ");
    $pgconn = pgConnection();
    /*** fetch into an PDOStatement object ***/
    $recordSet = $pgconn->prepare($sql);
    $recordSet->execute();
    if ($format == 'xml') {
        require_once "../inc/xml.pdo.inc.php";
        header("Content-Type: text/xml");
        echo rs2xml($recordSet);
    } elseif ($format == 'json') {
        require_once "../inc/json.pdo.inc.php";
        header("Content-Type: application/json");
        echo rs2json($recordSet);
    } elseif ($format == "text") {
        header("Content-Type: application/text");
        while ($line = $recordSet->fetch(PDO::FETCH_ASSOC)) {

# header("Content-Type: application/json");
# Performs the query and returns XML or JSON
try {
    $pgconn = pgConnection();
    if (is_numeric($query)) {
        // ignore - probably a parcel id
        echo returnEmpty($query);
    } else {
        // Process String
        // If it's an int and a space it's an address
        $query_array = explode(' ', $query);
        $pos = strpos($query, "&");
        // if the first element is numeric it's an address
        if (is_numeric($query_array[0]) and in_array("0", $searchTypes)) {
            // run full street name query
            $sql = sanitizeSQL("select '0:objectid:' || objectid as getid, 'ADDRESS: ' || full_address as name from master_address_table where txt_street_number = '" . $query_array[0] . "' and full_address like '" . strtoupper($query) . "%'  and num_x_coord > 0 and cde_status='A' order by getid, name ");
            $recordSet = $pgconn->prepare($sql);
            $recordSet->execute();
            echo rs2ubersearch($recordSet, $query);
        } else {
            if ($pos != false and in_array("8", $searchTypes)) {
                // get string before &
                $firstStreet = strtoupper(trim(substr($query, 0, $pos)));
                // get string after &
                $secondStreet = strtoupper(trim(substr($query, $pos + 1, strlen($query) - $pos)));
                if (strlen($secondStreet) > 0) {
                    $secondClause = " where streetname like '{$secondStreet}%' ";
                } else {
                    $secondClause = "";
                }
                $sql = "select distinct '8:streetname:{$firstStreet}:' || b.streetname as getid, 'INTERSECTION: {$firstStreet} & ' || b.streetname as name  from (select streetname, the_geom from roads where streetname = '{$firstStreet}') a, (select streetname,the_geom from roads {$secondClause}) b where a.the_geom && b.the_geom and intersects(a.the_geom, b.the_geom) and b.streetname <> '{$firstStreet}' ";

    $address = $_REQUEST['address'];
    $format = trim($_REQUEST['format']);
    if (strlen($address) < 3) {
        trigger_error("Caught exception: address parameter must include at least three characters.", E_USER_ERROR);
    }
} catch (Exception $e) {
    trigger_error("Caught Exception: " . $e->getMessage(), E_USER_ERROR);
}
# Performs the query and returns XML or JSON
try {
    // if completed house number exists, find it and use it
    if (strpos($address, " ")) {
        $houseno = trim(substr($address, 0, strpos($address, " ")));
        $sql = sanitizeSQL("select objectid, full_address as address from master_address_table where txt_street_number = '" . $houseno . "' and full_address like '" . strtoupper($address) . "%'  and cde_status='A' order by nme_street, txt_street_number ");
    } else {
        $sql = sanitizeSQL("select objectid, full_address as address from master_address_table where full_address like '" . strtoupper($address) . "%'  and cde_status='A' order by nme_street, txt_street_number ");
    }
    $pgconn = pgConnection();
    /*** fetch into an PDOStatement object ***/
    $recordSet = $pgconn->prepare($sql);
    $recordSet->execute();
    if ($format == 'xml') {
        require_once "../inc/xml.pdo.inc.php";
        header("Content-Type: text/xml");
        echo rs2xml($recordSet);
    } elseif ($format == 'json') {
        require_once "../inc/json.pdo.inc.php";
        header("Content-Type: application/json");
        echo rs2json($recordSet);
    } elseif ($format == "text") {
        header("Content-Type: application/text");

require_once "../inc/error.inc.php";
require_once "../inc/database.inc.php";
require_once "../inc/security.inc.php";
# Set arguments for error email
$err_user_name = "Tobin";
$err_email = "tobin.bradley@mecklenburgcountync.gov";
# Retrive URL arguments
try {
    $streetname = $_REQUEST['streetname'];
    $format = trim($_REQUEST['format']);
} catch (Exception $e) {
    trigger_error("Caught Exception: " . $e->getMessage(), E_USER_ERROR);
}
# Performs the query and returns XML or JSON
try {
    $sql = sanitizeSQL("select street_name from street_names where street_name like '" . strtoupper($streetname) . "%' ");
    $pgconn = pgConnection();
    /*** fetch into an PDOStatement object ***/
    $recordSet = $pgconn->prepare($sql);
    $recordSet->execute();
    if ($format == 'xml') {
        require_once "../inc/xml.pdo.inc.php";
        header("Content-Type: text/xml");
        echo rs2xml($recordSet);
    } elseif ($format == 'json') {
        require_once "../inc/json.pdo.inc.php";
        header("Content-Type: application/json");
        echo rs2json($recordSet);
    } elseif ($format == "text") {
        header("Content-Type: application/text");
        while ($line = $recordSet->fetch(PDO::FETCH_ASSOC)) {

     // probably a parcel id
     if (strlen($query) == 8) {
         //echo returnEmpty($query);
         // Replace with PID handler
         $sql .= sanitizeSQL("(select 'PID' as responsetype, 'master_address_table' as responsetable, 'objectid' as getfield, objectid as getid, num_parent_parcel as displaytext from master_address_table where num_parent_parcel = '" . $query . "' and num_x_coord > 0 and cde_status='A' order by getid, displaytext)");
     } else {
         //echo returnEmpty($query);
     }
 } else {
     // If it's an int and a space it's an address
     $query_array = explode(' ', $query);
     $pos = strpos($query, "&");
     // if the first element is numeric it's an address
     if (is_numeric($query_array[0]) and in_array("Address", $searchTypes)) {
         // run full street name query
         $sql .= sanitizeSQL("(select 'Address' as responsetype, 'master_address_table' as responsetable, 'objectid' as getfield, objectid as getid, full_address as displaytext from master_address_table where txt_street_number = '" . $query_array[0] . "' and full_address like '" . strtoupper($query) . "%'  and num_x_coord > 0 and cde_status='A' order by getid, displaytext)");
     } else {
         if ($pos != false and in_array("Intersection", $searchTypes)) {
             // get string before &
             $firstStreet = strtoupper(trim(substr($query, 0, $pos)));
             // get string after &
             $secondStreet = strtoupper(trim(substr($query, $pos + 1, strlen($query) - $pos)));
             if (strlen($secondStreet) > 0) {
                 $secondClause = " where streetname like '{$secondStreet}%' ";
             } else {
                 $secondClause = "";
             }
             $sql = "select distinct 'Intersection' as responsetype, 'roads' as responsetable, 'streetname' as getfield, '{$firstStreet}' || ' & ' || b.streetname as getid, '{$firstStreet}' || ' & ' || b.streetname as displaytext  from (select streetname, the_geom from roads where streetname = '{$firstStreet}') a, (select streetname,the_geom from roads {$secondClause}) b where a.the_geom && b.the_geom and intersects(a.the_geom, b.the_geom) and b.streetname <> '{$firstStreet}' ";
         } else {
             if (array_intersect($poi, $searchTypes)) {
                 // make sql array