MySQL Summary and preprocessing

one, connect to MySQL:

$DBC = Mysqli_connect (host,user,password,databasename);
Equivalent to:
$DBC = Mysqli_connect (HOST,USER,PWD);
mysqli_select_db ($DBC, db_name);

If an error occurs, you can call: Mysqli_connect_error ()Returns an error message with no parameters.
$DBC = @mysqli_connect (host,user,pwd,db) or Die (' cannot connect to MySQL: '. Mysqli_connect_error ());

@ is an error control operator that prevents PHP errors from appearing in a Web browser. In addition, the @ can also be placed in front of Mysqli_query. The above is a preferred practice because errors will be handled by or die. Die () terminates the execution of the script.

You can place the connection file outside the path.

Set Code: Mysqli_query ("Set names gb2312");

Second, the implementation of the query:
Either select, delete, Update,insert queries are used:
$result =mysqli_query ($DBC, SQL);
For insert, queries such as Delete,update will not return results, $result will return true or FALSE, so you can use this to determine the next step:
$result = Mysqli_query ($DBC, SQL);
if ($result) {//susses}

If the query does not succeed, some kind of MySQL error must occur and may call
Mysqli_error ($DBC)Note and mysqli_connect_error () difference

Three, close the connection:
Mysqli_close ($DBC)This is not required, PHP will automatically close at the end of the script, but it's best to write it.

Four, multiple queries:
Mysqli_multi_query ()Allows multiple queries to be executed at the same time. But the syntax is a little more complicated. Especially when the results are returned.

v. Retrieve the SELECT query result:
Mysqli_fetch_array ($result [, type])is most commonly used to return one row of data in an array format at a time. It comes with the while ()To traverse the return data. With an optional parameter type that specifies the type of array to be returned: associative or indexed, or both. The parameter types are as follows:
Mysqli_assocExample: $rows [' ColumnName ']
Mysqli_numExample: $rows [0], which is a little more efficient.
Mysqli_bothExample: $rows [0] or $rows[' columnName ']

When using Mysqli_fetch_array ($result [, type]), you can take an optional step-up: Once the query results are complete, you can release this information to eliminate the system memory overhead that $result occupies. This step is optional, and PHP also automatically cleans up when it results:
Mysqli_free_result ($result)Note that the parameter is not $rows!
The process is as follows:
while ($rows =mysqli_fetch_array ($result))//or while ($rows =mysqli_fetch_array ($result, MYSQLI_ASSOC))
{//traversal
..... code do something .....
echo $rows [0]
Mysqli_free_result ($result)
}
Note:
Mysqli_fetch_array () and Mysqli_fetch_array ($result, mysqli_num) are equivalent.
Mysqli_fetch_assoc () and Mysqli_fetch_array ($result, MYSQLI_ASSOC) are equivalent.


Six, ensure SQL security, using escape functions:
Mysqli_real_escape_string ($DBC, para)
The function receives a string as a parameter that verifies the value of the variable that the user commits and combines into the SQL query statement, which escapes characters that are potentially unintentional or malicious. In the case of a single quote, the name of the foreigner may contain the symbol (e.g. o ' Toole), which is required.
Case:
$name = $_post[' name '];
$name = mysqli_real_escape_string ($dbc, $name);
$query = "Select ... From TB where name= ' $name ' "; //This will ensure that the parameters are secured when SQL is brought in.
Note: If you enable Magic quotes Magic Reference before using PHP6, you need to remove any slashes added by the Magic reference with Stripslashes (para) before using mysqli_real_escape_string, as follows:
$FN = mysqli_real_escape_string ($dbc, Trim (stripslashes ($_post[' firstName ')));

Note:

Before the PHP5.3 version, the mysqli_real_escape_string () function had a path leak problem, and a remote attacker could exploit the vulnerability to obtain the actual path of the server-side script. That is, if a warning is issued if the value of the parameter passed is an array instead of a string, the warning message contains the full path information for the current server-side run script.
Test method:
Http://localhost/cms/sqlfilter/sqlsanatizer.php?params[]=
Warning:mysqli_real_escape_string () expects parameter 1 to be string,
Array given in/var/www/vhosts/cms/sqlfilter/sqlsanatizer.php

Vii. statistics The number of records returned by select:
Use Mysqli_num_rows ($result) to count the number of result rows returned by SELECT. $num =mysqli_num_rows ($r), for the while process described above, you can change to a more rigorous notation, rather than just analyzing the success of a query, because if the database is empty, there is no error.
$sql = "SELECT * from TB where id= $id";
$r = @mysqli_query ($dbc, $sql);
$num = Mysqli_num_rows ($r);
if ($num >0) {//This is more accurate than if ($r). It is not just the analysis that runs successfully.
Do something;

Mysqli_free_result ($r)
}
Mysqli_close ($DBC); 

Eight, return insert, update,delete the number of rows affected:  
Unlike above, if the query is not a SELECT, the number of rows affected is returned with the Mysqli_affected_rows () function. Use the following:
$num = Mysqli_affected_rows ($DBC);  //attention parameter is $DBC;
such as:
$q = "Update TB set PASS=SHA1 (' $newpassword ') where id= $row [0]";
$r = @myslqi_query ($dbc, $q);
if (mysqli_affected_rows ($DBC) ==1) {
     //do something
}else{
      Echo Mysqli_error ($DBC);
     exit ();//Terminate script.
} 
Note:
1, truncate () returns 0 if the table is emptied with mysqli_affected_rows TB. Even if the query executes successfully and deletes each row.
2, if the update query, but essentially did not change the value of any column, such as the same password instead of an old password, it will also return 0.

Nine, bulk query: Preprocessing statements (12th Chapter 4th: P311)  
Version: MYSQL 4.1 Start to add preprocessing. PHP5 can be used.

Benefits of preprocessing:
1, greater security. 2, better performance. 3, batch query.

For pre-processing statements, only the query itself is sent to MySQL and is parsed only once, and the values are sent separately to MySQL.
$q = ' Insert into TB (num) VALUES (?) ';
$stmt = Mysqli_prepare ($dbc, $q);
Mysqli_stmt_bind_param ($stmt, ' I ', $n);
for ($n =1; $n <=100; $n + +)
{
Mysqli_stmt_execute ($stmt);
}
Preprocessing can be created by INSERT, UPDATE, delete, select query, step:
1, define the query:
$q = "Select Firstname,lastname from users where uid =?";(Normal is uid= $id)
2. Pass the query to MySQL preprocessing:
$stmt = Mysqli_prepare ($dbc, $q);At this point, MySQL parses the query, but does not execute.
3. Bind the variable to the query placeholder "? ", as follows:
Mysqli_stmt_bind_param ($stmt, ' I ', $id);
Where ' I ' means that the Mysql_stmt_bind_param function expects to receive a value of type int, there are several:
--------------------------------------------------------------------------------
Letter indicates the value type of the binding
D Decimal
I Integer
b Blob (binary type)
s all other types
----------------------------------------------------------------------------------
If a query statement has multiple variables, such as:
$q = "Select Uid,firstname from Users where email=? and PASS=SHA1 (?) ";Notice there's no right here. The question mark is added as a single quotation mark, even if it is a character type. This is the difference from a standard query.
Multiple variables are listed in quotation marks directly in the binding sequence. As follows:
$stmt = Mysqli_prepare ($dbc, $q);
Mysqli_stmt_bind_param ($stmt, ' SS ', $e, $p);
It is also important to note that before invoking a binding function, you may not need to define the variable first, such as the $e above, $p set below, and this will not be an error.
4, after the binding, you can assign a value to the PHP variable (if there is no value). Then execute the statement.
$id = 15;
Mysqli_stmt_execute ($stmt);
5, close the pretreatment:
Mysqli_stmt_close ($stmt);
6. Close the connection
Mysqli_close ($DBC);
When preprocessing is performed, it is called with Mysqli_stmt_error ($stmt) If there is an error.

Example:
$dbc =mysqli_connect (' localhost ', ' username ', ' pwd ', ' forum ');
$q = ' INSERT INTO messages (forumid,parentid,userid,subject,body,forumdate) VALUES (?,?,?,?,?, now ()) ';
$stmt = Mysqli_prepare ($dbc, $q);
Mysqli_stmt_bind_param ($stmt, ' Iiiss ', $forumid, $parentid, $userid, $subject, $body);
$forumid = (int) $_post[' ForumID '];
$parentid = (int) $_post[' parentid '];
$user _id = 3;
$subject = Strip_tags ($_post[' subject '); Strip_tags
$body = strip_tags ($_post[' body ');
Mysqli_stmt_execute ($stmt);
if (Mysqli_stmt_affected_rows ($stmt) ==1)
{
Do ....
}else{
echo Mysqli_stmt_error ($stmt);
}
Mysqli_stmt_close ($stmt);
Mysqli_close ($DBC);

The above illustrates a preprocessing statement in which two statements are actually preprocessed:
1, binding parameters (bound parameter): As the above example
2. Binding results (bound result): Binds the query result to a PHP variable.

10. Block SQL Injection: (12th Chapter 4th: P311)
1. Verify the data to be used in the query and, if possible, perform a type cast. Such as:
$forumid = (int) $_post[' ForumID '];
if ($forumid >0) ....//if the cast to int is finished = 0 o'clock, the data type requirement is not true.
2. Using mysqli_real_escape_string ($DBC, para)
3, using mysqli_real_escape_string ($DBC, para) Alternative: pretreatment, the above.

11. Previous PHP and MySQL connection methods:
Mysql_connect, in the wording of the letter I only the above, but the use of the same. Here's a simple example:
$conn = mysql_connect ("127.0.0.1", "Mysqltest", "123456");
mysql_select_db ("Shop"); If using $selectdb = mysql_select_db ("shop"), then $selectdb=1

mysql_query ("Set names gb2312"); mysql_query ("Set names UTF8");
$exec = "SELECT * from Product";
$result =mysql_query ($exec, $conn); Or: $result =mysql_query ($exec);

while ($rs =mysql_fetch_object ($result))
{
echo "Name: [". $rs->pname.] &nbsp;&nbsp; ";
echo "Price:".
$rs->price. "&nbsp;&nbsp;";
echo "Inbound time:" $rs->addtime. "&nbsp;&nbsp;";
echo "<br/>";
}
echo $result;

If you want the result to be judged if there is no re-output, you can use:
$conn = mysql_connect ("127.0.0.1", "Mysqltest", "123456");
mysql_select_db ("Shop");

mysql_query ("Set names gb2312"); mysql_query ("Set names UTF8");
$exec = "SELECT * from Product";
if ($result =mysql_query ($exec, $conn)) {
while ($rs =mysql_fetch_object ($result))
{
echo "Name: [". $rs->pname.] &nbsp;&nbsp; ";
echo "Price:".
$rs->price. "&nbsp;&nbsp;";
echo "Inbound time:" $rs->addtime. "&nbsp;&nbsp;";
echo "<br/>";
}
}

Report:
A. Get the last record after insert: 2 ways:
1. Using MySQL: last_insert_id () function. "INSERT INTO ...; Select last_insert_id () "
2. Use PHP mysql_insert_id () or mysqli_insert_id () to return the same value:
PHP's mysql_insert_id ([Resource $link _identifier]) function can return the ID you want. The optional parameter is a handle to the PHP connection to MySQL. Each connection has a different handle. Such as:
mysql_query ("INSERT into mytable (product) VALUES (' Kossu ')");
printf ("Last inserted record had ID%d\n", mysql_insert_id ());


B, several functions:
Trim (), LTrim (), RTrim ()
Exit (),
Strip_tags () removes any HTML and PHP tag strings contained in the string. If the HTML and PHP tags of a string are wrong, such as a sign that is greater than, the error will also be returned. This function and FGETSS () have the same function.
$text = ' <p>test paragraph.</p><!--Comment--<a href= "#fragment" >other text</a> ';
echo Strip_tags ($text); Result: Test paragraph. Other text
Licensing <p> and <a>
Echo strip_tags ($text, ' <p><a> '); Results: <p>test paragraph.</p> <a href= "#fragment" >other text</a>

MySQL Summary and preprocessing