Preprocessing of Operation Mysqli in PHP prepare

This article mainly introduces the operation of PHP in the mysqli preprocessing prepare, has a certain reference value, now share to everyone, the need for friends can refer to

Preprocessing of Operation Mysqli in PHP prepare

1. "PHP error" cannot pass parameter 2 by reference

This error means that a 2nd argument cannot be passed by reference.
The reason for this error is that in the Bind_param () method, in addition to the first parameter that represents the data type,
Requires a variable, not a direct amount, because the other arguments are passed by reference.

$sql = "SELECT * from tmp where myname=?" or sex =? "; $stmt = $mysqli->conn->prepare ($sql); $name = "a"; $sex = "B"; $stmt->bind_param (' SS ', $name, $sex);//must be passed in such a way, And in the mysqli, such as preprocessing parameter binding, you must specify the type of the parameter and can only bind all parameters one time, not like PDO as a binding//$stmt->bind_param (' SS ', "a", "B");//This way will be error: Fatal Error:cannot pass Parameter 2 by Reference$stmt->execute (); if ($mysqli->conn->affected_rows) {    $result = $ Stmt->get_result ();    while ($row = $result->fetch_assoc ()) {        var_dump ($row);}    }

2, PHP Anti-SQL injection do not use addslashes and mysql_real_escape_string.

Whether using addslashes or mysql_real_escape_string, you can use a coded vulnerability to enter an arbitrary password to login to the server injection Attack!!!! (The principle of attack I will not say, interested students can study the character encoding in the single-byte and multi-byte problem)

　　Mysql_real_escape_string is able to prevent injection because mysql_escape_string itself does not have the ability to judge the current encoding, you must specify both the server-side encoding and the client's code, plus the ability to prevent coding problems injected. While it is possible to prevent SQL injection to some extent, it is recommended that the following are the perfect solutions.

The perfect solution is to use PDO and mysqli with the prepared statement mechanism instead of mysql_query (note: mysql_query since PHP 5.5.0 has been discarded and will be removed in the future):

Pdo:

1. $pdo = new PDO (' Mysql:dbname=dbtest;host=127.0.0.1;charset=utf8 ', ' user ', ' pass '); $pdo->setattribute (Pdo::attr_emulate_prepares, false); $pdo->setattribute (Pdo::attr_errmode, PDO::ERRMODE_ EXCEPTION); $stmt = $pdo->prepare (' SELECT * FROM employees WHERE name =: Name '); $stmt->execute (Array (' name ' = = $n AME)); foreach ($stmt as $row) {//do something with $row

Mysqli:

1. $stmt = $dbConnection->prepare (' SELECT * FROM employees WHERE name =? '); $stmt->bind_param (' s ', $name); $stmt->execute (); $result = $stmt->get_result (), while ($row = $result->fetch_assoc ()) {//does something with $row}

Pdo:

$pdo = new PDO (' Mysql:dbname=dbtest;host=127.0.0.1;charset=utf8 ', ' user ', ' Pass '); $pdo->setattribute (pdo::attr_ Emulate_prepares, false); $pdo->setattribute (Pdo::attr_errmode, pdo::errmode_exception); $stmt = $pdo->prepare (' SELECT * FROM employees WHERE name =: Name '); $stmt->execute (Array (' name ' = $name)); foreach ($stmt as $row) {//Do something with $row}

Mysqli:

$stmt = $dbConnection->prepare (' SELECT * FROM employees WHERE name =? '); $stmt->bind_param (' s ', $name); $stmt->execute (); $result = $stmt->get_result (), while ($row = $result->fetch_assoc ()) {//does something with $row}

This error means that a 2nd argument cannot be passed by reference.
The reason for this error is that in the Bind_param () method, in addition to the first parameter that represents the data type,
Requires a variable, not a direct amount, because the other arguments are passed by reference.

The above is the whole content of this article, I hope that everyone's learning has helped, more relevant content please pay attention to topic.alibabacloud.com!