Pre-processing of PHP mysqli extensions

In the previous article mysqli basic knowledge of mysqli installation and basic operations (mainly a single SQL statement query operations), today is a very important part of mysqli: preprocessing.

Its three main classes are often involved in mysqli operations: The Mysqli class, the Mysql_stmt class, and the Mysqli_result class. Preprocessing is mainly done by using the Mysql_stmt class.

Preprocessing is an important means to prevent SQL injection, which is of great significance to improve the security of the website.

In this case, the database name is test, the data table name is test, the field has ID, title two, id from the growth of the primary key.

　

To perform an insert operation using MYSQLI preprocessing:

<?PHPDefine("HOST", "localhost");Define("USER", ' Root ');Define("PWD", ");Define("DB", ' Test ');$mysqli=NewMysqli (Host,user,pwd,DB);if($mysqli-Connect_errno) {    "Connect Error:".$mysqli-Connect_error;}$mysqli->set_charset (' UTF8 ');$id= ' ';$title= ' Title4 ';//use? Substitution variables$sql= "INSERT Test VALUES (?,?)";//To get $mysqli_stmt object, we must remember to pass $sql, preprocessing is the preprocessing of SQL statement. $mysqli _stmt=$mysqli->prepare ($sql);//The first parameter indicates the variable type, with I (int), d (Double), S (string), B (BLOB)$mysqli _stmt->bind_param (' is ',$id,$title);//executing a preprocessing statementif($mysqli _stmt-Execute ()) {    Echo $mysqli _stmt-insert_id;}Else{    Echo $mysqli _stmt-error;}$mysqli->close ();

Prevent SQL injection with mysqli preprocessing:

$id= ' 4 ';$title= ' Title4 ';$sql= "SELECT * FROM Test WHERE id=?" and title=? ";$mysqli _stmt=$mysqli->prepare ($sql);$mysqli _stmt->bind_param (' is ',$id,$title);if($mysqli _stmt-Execute ()) {    $mysqli _stmt-Store_result (); if($mysqli _stmt->num_rows () >0){        Echo"Validation succeeded"; }Else{        Echo"Validation Failed"; }}    $mysqli _stmt-Free_result (); $mysqli _stmt->close ();

To execute a query statement using MYSQLI preprocessing:

$sql= "Select Id,title from Test WHERE id>=?";$mysqli _stmt=$mysqli->prepare ($sql);$id=1;$mysqli _stmt->bind_param (' I ',$id);if($mysqli _stmt-Execute ()) {    $mysqli _stmt-Store_result ();
Binds a variable to a prepared statement for the result store$mysqli _stmt->bind_result ($id,$title);  while($mysqli _stmt-Fetch ()) {        Echo $id.‘ :‘.$title.‘ <br/> '; }}

For more mysqli technology, please refer to the official PHP manual, which is the best way to learn.

Pre-processing of PHP mysqli extensions