PHP MySQLi::real_escape_string方法代码示例

 /**
  * Escapes a string for use in an SQL query.
  *
  * @param  string $value
  * @param  boolean $like If true, escapes % and _ for LIKE clauses
  * @return string
  */
 public function escape($value, $like = false)
 {
     if ($like) {
         $value = str_replace(array('%', '_'), array('\\%', '\\_'), $value);
     }
     return $this->_mysqli->real_escape_string($value);
 }

 /**
  * Escapes the input with real_escape_string
  * Taken from FuelPHP and edited
  * @param  string $value  The string to escape
  * @return  string  The escaped string
  * @throws  Miaox_SphinxQl_Connection_Exception  If there was an error during the escaping
  */
 public function escape($value)
 {
     $this->connect();
     if (($value = $this->_driver->real_escape_string((string) $value)) === false) {
         throw new Miaox_SphinxQl_Connection_Exception($this->_driver->error, $this->_driver->errno);
     }
     return "'" . $value . "'";
 }

 public function escape($txt)
 {
     if (is_array($txt)) {
         throw new Error('Invalid parameter: escape cannot handle arrays.');
     }
     $this->connect();
     return $this->connection->real_escape_string($txt);
 }

 /**
  * @param Array $data
  * @return Array
  */
 private function valquote(array $data)
 {
     foreach ($data as &$value) {
         if (!is_numeric($value)) {
             $value = $this->MySQLi->real_escape_string($value);
             $value = "'{$value}'";
         }
     }
     return $data;
 }

 /**
  * Given a string, escapes it for use in a query
  *
  * @param String $value The string to escape. If an array is given, all the
  *      values in it will be escaped
  * @return String Returns the escaped string
  */
 public function escape($value)
 {
     if (is_array($value)) {
         return array_map(array($this, "escape"), $value);
     }
     $value = (string) $value;
     if (!isset($this->link)) {
         $this->connect();
     }
     return $this->link->real_escape_string($value);
 }

 public function SQLPrep($value)
 {
     if ($value == '') {
         $value = 'NULL';
     } else {
         if (!TeraWurflDatabase::isNumericSafe($value) || $value[0] == '0') {
             $value = "'" . $this->dbcon->real_escape_string($value) . "'";
         }
     }
     //Quote if not integer
     return $value;
 }

 public function toSQL(\MySQLi $con)
 {
     if (is_array($this->values) || $this->values instanceof ArrayIterator) {
         $val = "";
         $index = 0;
         foreach ($this->values as $v) {
             if ($index++ > 0) {
                 $val .= ',';
             }
             if ($v instanceof DBObject) {
                 $v = $v->id;
             }
             $val .= '"' . $con->real_escape_string($v) . '"';
         }
     } else {
         $val = $this->values->toSQL();
     }
     $field = $this->field;
     if ($field instanceof DBField) {
         $field = $field->getName();
     }
     return "({$field} {$this->operator} ({$val}))";
 }

    fclose($fr_hn);
}
//	end of prepare the multi-lingual json files for database injection
//	now we need to insert or update the database with the files
$mysqli = new MySQLi(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: ({$mysqli->connect_errno}) {$mysqli->connect_error}";
}
$json_file = scandir($OUT_DIR);
foreach ($json_file as $file) {
    if ($file == 'BCBF_en.json') {
        $content = file_get_contents($OUT_DIR . '/BCBF_en.json');
        $insert_format = " ( '%d' , '%s' )";
        $update_format = "   '%s'  ";
        $query = " INSERT INTO `" . $table_prefix . "postmeta` ( `meta_id` , `meta_value` ) VALUES ";
        $query .= sprintf($insert_format, $mysqli->real_escape_string('1457'), $mysqli->real_escape_string($content));
        $query .= ' ON DUPLICATE KEY UPDATE meta_value = ';
        $query .= sprintf($update_format, $mysqli->real_escape_string($content));
        $result = $mysqli->query($query);
        $errors = $mysqli->error;
        if (!$errors) {
            echo 'BCBF_en was updated successfully <br>';
            // If update is successful, update the post date to reflect current date.
            $update_date = $mysqli->real_escape_string(date("Y-m-d H:i:s"));
            $get_post_id = $mysqli->real_escape_string('137');
            $query_date = " UPDATE `" . $table_prefix . "posts` SET post_date='" . $update_date . "', post_date_gmt='" . $update_date . "' WHERE `ID`='" . $get_post_id . "'";
            $result_date = $mysqli->query($query_date);
            $errors_date = $mysqli->error;
            if (!$errors_date) {
                echo 'BCBF_en date was updated successfully <br>';
            } else {

 public function write2db($prices)
 {
     $mysqli = new MySQLi(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
     if ($mysqli->connect_errno) {
         echo "Failed to connect to MySQL: ({$mysqli->connect_errno}) {$mysqli->connect_error}";
     }
     $format = " ('%s', '%s', '%s', '%s', '%s', %f, %f)";
     $format_update_nav = "  '%f' ,  ";
     $format_update_nav_change = "  '%f'  ; ";
     // Go over each array item and append it to the SQL query
     foreach ($prices as $manager) {
         foreach ($manager as $class) {
             foreach ($class as $arrays) {
                 foreach ($arrays as $data) {
                     // change fund managers into english ID
                     if ($data['Manager'] == 'Brandes') {
                         $manager = 956;
                     } elseif ($data['Manager'] == 'Sionna') {
                         $manager = 960;
                     } elseif ($data['Manager'] == 'Lazard') {
                         $manager = 958;
                     } elseif ($data['Manager'] == 'Greystone') {
                         $manager = 14630;
                     }
                     // change currency into ID
                     if ($data['Currency'] == 'CAD') {
                         $currency = 0;
                     } elseif ($data['Currency'] == 'USD') {
                         $currency = 1;
                     }
                     // change Excel date into MySQL date
                     $x = $data['PriceDate'];
                     // check if the value is an array
                     if (is_array($x)) {
                         // set $x to the previous $x value
                         $x = $temp;
                     }
                     $utc = ($x - 25569) * 86400;
                     $dt = gmdate("Y-m-d", $utc);
                     $temp = $x;
                     //change "U" classes to proper Classnames
                     if ($data['Fund'] == 'BCFU') {
                         $fundcode = 'BCFB';
                     } elseif ($data['Fund'] == 'BEMU') {
                         $fundcode = 'BEMF';
                     } elseif ($data['Fund'] == 'BGBU') {
                         $fundcode = 'BGBF';
                     } elseif ($data['Fund'] == 'BGEU') {
                         $fundcode = 'BGEF';
                     } elseif ($data['Fund'] == 'BGOU') {
                         $fundcode = 'BGOF';
                     } elseif ($data['Fund'] == 'BGSU') {
                         $fundcode = 'BGSF';
                     } elseif ($data['Fund'] == 'BIEU') {
                         $fundcode = 'BIEF';
                     } elseif ($data['Fund'] == 'BUEU') {
                         $fundcode = 'BUEF';
                     } elseif ($data['Fund'] == 'BUSU') {
                         $fundcode = 'BUSF';
                     } elseif ($data['Fund'] == 'LEMU') {
                         $fundcode = 'LEMS';
                     } elseif ($data['Fund'] == 'LGEU') {
                         $fundcode = 'LGEI';
                     } elseif ($data['Fund'] == 'LGBU') {
                         $fundcode = 'LGBI';
                         /*private funds added SEP 2015*/
                     } elseif ($data['Fund'] == 'LGLU') {
                         $fundcode = 'LGLV';
                     } elseif ($data['Fund'] == 'BGCU') {
                         $fundcode = 'BGCC';
                     } else {
                         $fundcode = $data['Fund'];
                     }
                     // State Street now sends the Fund Class data that no longer requires a change
                     $datacode = $data['Class'];
                     // insert 10.00 for Brandes Canadian Money Market Fund (BMMF) Class A and F
                     if ($fundcode == 'BMMF' && $manager == 956 && $datacode == 'A') {
                         $data['NAV'] = 10;
                     }
                     if ($fundcode == 'BMMF' && $manager == 956 && $datacode == 'F') {
                         $data['NAV'] = 10;
                     }
                     $query = "INSERT INTO `bh_bh_price_data` (`price_date`, `fund_id`, `manager_id` , `currency_id`, `class_id`, `nav`, `nav_change`) VALUES ";
                     $query .= sprintf($format, $mysqli->real_escape_string($dt), $mysqli->real_escape_string($fundcode), $mysqli->real_escape_string($manager), $mysqli->real_escape_string($currency), $mysqli->real_escape_string($datacode), $mysqli->real_escape_string($data['NAV']), $mysqli->real_escape_string($data['NavChange']));
                     $query .= ' ON DUPLICATE KEY UPDATE nav = ';
                     $query .= sprintf($format_update_nav, $mysqli->real_escape_string($data['NAV']));
                     $query .= 'nav_change = ';
                     $query .= sprintf($format_update_nav_change, $mysqli->real_escape_string($data['NavChange']));
                     $result = $mysqli->query($query);
                     $errors = $mysqli->error;
                 }
             }
         }
     }
     if (!$errors) {
         echo "The database has been updated with the latest information.";
     } else {
         echo "Something went wrong. The query did not work: " . $mysqli->error;
     }
 }

 public function insertRegisters(\MySQLi &$sqli, $tableName, array $columns, array $registers)
 {
     $sql = "INSERT INTO {$tableName} SET ";
     $result = @array_combine($columns, $registers);
     if (!$result) {
         //FIX ME
         $registers = array_pad($registers, sizeof($columns), '-');
         $result = array_combine($columns, $registers);
         $this->log("WARNING! INSERTING PADDED VALUES, THIS DATA IS NOT ACCURATE!", 1, "red");
     }
     foreach ($result as $colName => $colVal) {
         $sql .= "{$colName}='" . $sqli->real_escape_string($colVal) . "',";
     }
     $sql = substr($sql, 0, -1);
     return $sqli->query($sql);
 }

 /**
  * Function used to escape parameters
  *
  * @param MySQLi $db Database connection.
  * @param array $args Arguments to escape.
  * @return array Escaped parameters.
  */
 public static function escapeParameters(MySQLi $db, $args)
 {
     $data = [];
     foreach ($args as $index => $value) {
         $data[$index] = $db->real_escape_string($value);
     }
     return $data;
 }

    exit;
}
$web_query = $mysqli->query("SELECT * from web_config WHERE web_version = '" . $web_version . "'");
$web_fetch = $web_query->fetch_assoc();
if ($web_fetch['web_version'] != $web_version) {
    echo "Website version is old, Please update.";
    exit;
}
if ($web_version == "Develop") {
    // nice setting, if you is not developer please don't change this setting
    $debug = true;
}
/*
 * Normal Connect DataBase Web Settings
 */
$servername = $mysqli->real_escape_string($web_fetch['servername']);
$pagetitle = $mysqli->real_escape_string($web_fetch['pagetitle']);
$game_version = $mysqli->real_escape_string($web_fetch['game_version']);
$web_description = $mysqli->real_escape_string($web_fetch['description']);
$web_keywords = $mysqli->real_escape_string($web_fetch['keywords']);
$client_text = $mysqli->real_escape_string($web_fetch['client_text']);
$other_text = $mysqli->real_escape_string($web_fetch['other_text']);
$tips_text = $mysqli->real_escape_string($web_fetch['tips_text']);
$home_text = $mysqli->real_escape_string($web_fetch['home_text']);
$youtu_link = $mysqli->real_escape_string($web_fetch['youtu_link']);
$reg_text = $mysqli->real_escape_string($web_fetch['reg_text']);
$reg_email_check = $mysqli->real_escape_string($web_fetch['reg_email_check']);
$admin_email = $mysqli->real_escape_string($web_fetch['admin_email']);
/*
 * Normal Web Settings
 */

/* okay - now we open a new connection and loop through the files and funds to update each fund */
$mysqli = new MySQLi(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: ({$mysqli->connect_errno}) {$mysqli->connect_error}";
}
//set up 2 counters $fn for $FUND data $fl for $json_files
//cause they might get out of sync if a json file is not generated for any fund
//but why would that be master?  cause the xml file is probably missing little butterfly.
$fn = 0;
$fl = 2;
while ($fn < count($FUNDS)) {
    if (strtoupper($FUNDS[$fn][0]) . '_' . $FUNDS[$fn][2] === substr($json_file[$fl], 0, -5)) {
        $content = file_get_contents($OUT_DIR . '/' . $json_file[$fl]);
        $sql = " INSERT INTO bh_postmeta ( meta_id , meta_value ) VALUES ( '";
        $sql .= $FUNDS[$fn][3] . "' , ";
        $sql .= "'" . sprintf('%s', $mysqli->real_escape_string($content)) . "' )";
        $sql .= " ON DUPLICATE KEY UPDATE meta_value = ";
        $sql .= "'" . sprintf('%s', $mysqli->real_escape_string($content)) . "'";
        $result = $mysqli->query($sql);
        //      echo $sql;
        $errors = $mysqli->error;
        if (!$errors) {
            echo strtoupper($FUNDS[$fn][0]) . '_' . $FUNDS[$fn][2] . ' was updated successfully <br>';
            //              If update is successful, update the post date to reflect current date.
            $update_date = $mysqli->real_escape_string(date("Y-m-d H:i:s"));
            $get_post_id = $mysqli->real_escape_string($FUNDS[$fn][1]);
            $query_date = " UPDATE bh_posts SET post_date='" . $update_date . "', post_date_gmt='" . $update_date . "' WHERE ID='" . $get_post_id . "'";
            $result_date = $mysqli->query($query_date);
            $errors_date = $mysqli->error;
            if (!$errors_date) {
                echo strtoupper($FUNDS[$fn][0]) . '_' . $FUNDS[$fn][2] . ' post date was updated successfully <br>';

 public function write2db($prices)
 {
     $mysqli = new MySQLi(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
     if ($mysqli->connect_errno) {
         echo "<br />Failed to connect to MySQL: ({$mysqli->connect_errno}) {$mysqli->connect_error}";
     }
     $format = " ('%s', '%s', '%s', '%s', '%s', %f, %f)";
     $format_update_total_factor_cad = "  '%f' , ";
     $format_update_total_factor_usd = "  '%f' ; ";
     // Go over each array item and append it to the SQL query
     foreach ($prices as $manager) {
         foreach ($manager as $class) {
             foreach ($class as $arrays) {
                 foreach ($arrays as $data) {
                     // change fund managers into ID
                     if ($data['Manager'] == 'Brandes') {
                         $manager = 956;
                     } elseif ($data['Manager'] == 'Sionna') {
                         $manager = 960;
                     } elseif ($data['Manager'] == 'Lazard') {
                         $manager = 958;
                     } elseif ($data['Manager'] == 'Greystone') {
                         $manager = 14630;
                     }
                     //change codenumber to our CodeID
                     if ($data['FundNumber'] == 'NXW7') {
                         $fund_code = 'BCEF';
                     } elseif ($data['FundNumber'] == 'NXW9') {
                         $fund_code = 'BMMF';
                     } elseif ($data['FundNumber'] == 'NXX4') {
                         $fund_code = 'BCFB';
                     } elseif ($data['FundNumber'] == 'NXW4') {
                         $fund_code = 'BEMF';
                     } elseif ($data['FundNumber'] == 'NXWK') {
                         $fund_code = 'BGBF';
                     } elseif ($data['FundNumber'] == 'NXW2') {
                         $fund_code = 'BGEF';
                     } elseif ($data['FundNumber'] == 'NXWD') {
                         $fund_code = 'BGOF';
                     } elseif ($data['FundNumber'] == 'NXW3') {
                         $fund_code = 'BGSF';
                     } elseif ($data['FundNumber'] == 'NXW1') {
                         $fund_code = 'BIEF';
                     } elseif ($data['FundNumber'] == 'NXWC') {
                         $fund_code = 'BCBF';
                     } elseif ($data['FundNumber'] == 'NXXC') {
                         $fund_code = 'BSCE';
                     } elseif ($data['FundNumber'] == 'NXX2') {
                         $fund_code = 'BSSC';
                     } elseif ($data['FundNumber'] == 'NXX3') {
                         $fund_code = 'BSDI';
                     } elseif ($data['FundNumber'] == 'NXW5') {
                         $fund_code = 'BUEF';
                     } elseif ($data['FundNumber'] == 'NXXK') {
                         $fund_code = 'BSMI';
                     } elseif ($data['FundNumber'] == 'NXW6') {
                         $fund_code = 'BUSF';
                     } elseif ($data['FundNumber'] == 'NXWE') {
                         $fund_code = 'LEMS';
                     } elseif ($data['FundNumber'] == 'NXWF') {
                         $fund_code = 'LGEI';
                     } elseif ($data['FundNumber'] == 'NXXE') {
                         $fund_code = 'LGBI';
                     } elseif ($data['FundNumber'] == 'NXXF') {
                         $fund_code = 'GCBF';
                     } elseif ($data['FundNumber'] == 'NXXO') {
                         $fund_code = 'GEIC';
                     } elseif ($data['FundNumber'] == 'NXXP') {
                         $fund_code = 'BGCC';
                     } elseif ($data['FundNumber'] == 'NXXQ') {
                         $fund_code = 'LGLV';
                     } elseif ($data['FundNumber'] == 'NXXR') {
                         $fund_code = 'SCEP';
                     }
                     $short_class = trim($data['Class'], "Class ");
                     $query = "INSERT INTO `bh_bh_monthly_distribs` (`record_date`, `payment_date`, `fund_id`, `manager_id` ,  `class_id`, `total_factor_cad` , `total_factor_usd` ) VALUES ";
                     $query .= sprintf($format, $mysqli->real_escape_string($data['RecordDate']), $mysqli->real_escape_string($data['PaymentDate']), $mysqli->real_escape_string($fund_code), $mysqli->real_escape_string($manager), $mysqli->real_escape_string($short_class), $mysqli->real_escape_string($data['TotalFactorCAD']), $mysqli->real_escape_string($data['TotalFactorUSD']));
                     $query .= ' ON DUPLICATE KEY UPDATE total_factor_cad = ';
                     $query .= sprintf($format_update_total_factor_cad, $mysqli->real_escape_string($data['TotalFactorCAD']));
                     $query .= 'total_factor_usd = ';
                     $query .= sprintf($format_update_total_factor_usd, $mysqli->real_escape_string($data['TotalFactorUSD']));
                     $result = $mysqli->query($query);
                     $errors = $mysqli->error;
                     //echo $query;
                 }
             }
         }
     }
     if (!$errors) {
         echo "<br />The database has been updated with the information from this file.";
     } else {
         echo "<br />Something went wrong, the query did not work: " . $mysqli->error;
     }
 }

 /**
  * Formats this object, escaping the values as needed.
  *
  * @param MySQLi $con the connection to use for escaping
  */
 public function toSQL(\MySQLi $con)
 {
     $oper = null;
     $val = "NULL";
     if ($this->value === null) {
         if ($this->operator == self::EQ) {
             $oper = " is ";
         } else {
             $oper = " is not ";
         }
     } else {
         $oper = $this->operator;
         if ($this->value instanceof DBObject) {
             $val = '"' . $con->real_escape_string($this->value->id) . '"';
         } elseif ($this->value instanceof \DateTime) {
             $val = '"' . $this->value->format('Y-m-d H:i:s') . '"';
         } elseif ($this->value instanceof DBField) {
             $val = $this->value->getName();
         } else {
             $val = '"' . $con->real_escape_string($this->value) . '"';
         }
     }
     $field = $this->field;
     if ($field instanceof DBField) {
         $field = $field->getName();
     }
     return '(' . $field . $oper . $val . ')';
 }