I got a big rub-pdo (ii)

Hi

Yesterday, 213, although there are roommates more than 3 points to sleep the objective impact, but didn't want to learn things last night is the essential reason. Get up today. Plan to 3, 4 days, learn PDO and Ajax two, but also hope that everyone is not to scold me, exempt me and lazy.

1. PDO

Second, the use of the PDO object (ii)

2.2 Error message

ErrorCode ()--error number;

ErrorInfo ()--error message;

Give me a chestnut.

<?php
/*
* PDO error message
*/

$pdo =new PDO (' Mysql:host=localhost;dbname=imooc ', ' root ', ');

$pdo->exec (' use imooc_pdo ');
$resultd = $pdo->exec (' Delete from user where id=13 ');
Var_dump ($RESULTD);
$insert = ' Insert User (Username,password,email) VALUES ("Knga", "'. MD5 (' King '). '", "[email protected]") ';
$result 1= $pdo->exec ($insert);
Var_dump ($result 1);

if ($result 1==false) {
echo "went wrong";
echo $pdo->errorcode ();
Print_r ($pdo->errorinfo ());
}

Take a look at the error message

Array ([0] = 23000 [1] = 1062 [2] = duplicata du champ ' knga ' pour la clef ' username ')

0 is the error type, 1062 is the code, and 2 is the error message (this is because the username is set to a unique key, but the ID number is actually growing).

2.3 Query () implementation

executes a statement that returns a Pdostatement object.

--Give me a chestnut.

<?php

/*
* Pdoquery
*/

$pdo =new PDO (' Mysql:host=localhost;dbname=imooc ', ' root ', ');

$pdo->exec (' use imooc_pdo ');

$insert = ' SELECT * from user ';
$result 1= $pdo->query ($insert);
Var_dump ($result 1); View Statement Objects
foreach ($result 1 as $row) {//View output results (depending on return)
Print_r ($row);
}
if ($result 1==false) {
echo "went wrong";
echo $pdo->errorcode ();
Print_r ($pdo->errorinfo ());
}

If there is a problem with the SQL statement, the statement object is false, and the subsequent output is also an error message;

If the SQL statement is correct, but the content of the query does not exist, then the statement object is not a problem and the output is empty.

Of course this will look good:

foreach ($result 1 as $row) {//View output results (depending on return)
Print_r ($row); echo "<br/>";
Echo ' number: '. $row [' id '];echo ' <br/> ';
Echo ' username: '. $row [' username '];echo ' <br/> ';
echo ' Password: '. $row [' Password '];echo ' <br/> ';
Echo ' Email: ' $row [' email '];echo <br/> ';
echo "}

Of course, the query to perform additions and deletions are no problem.

2.4 Prepare () and execute () methods to implement the query

The recommended query method can be used to implement conditional queries.

Prepare ()--Prepares the SQL statement to execute and returns the Pdostatement object;

Execute ()-executes a preprocessing statement that returns TRUE or false;

So the above is a pair.

--an example

<?php
/*
* Pdoprepare&execute method
*/

$pdo =new PDO (' Mysql:host=localhost;dbname=imooc ', ' root ', ');

$pdo->exec (' use imooc_pdo ');

$insert = ' select * ' from user where username= ' king ';
$result = $pdo->prepare ($insert);
Var_dump ($result);

$result 1= $result->execute ();//execution is for preprocessing statements
Var_dump ($result 1);

Print_r ($result->fetchall ());//For statement objects to have a result output

if ($result 1==false) {
echo "went wrong";
echo $pdo->errorcode ();
Print_r ($pdo->errorinfo ());
}

This is a special case to be carefully preprocessed, and it is good to know who the object is.

--Select the Output form

To correlate an array output or all or an indexed array, there are two different methods of parameters and methods.

<?php
Header (' Content-type:text/html;charset=utf-8 ');
try{
$pdo =new PDO (' Mysql:host=localhost;dbname=imooc ', ' root ', ' root ');
$sql = ' SELECT * from user ';
$stmt = $pdo->prepare ($sql);
$res = $stmt->execute ();
if ($res) {
while ($row = $stmt->fetch (PDO::FETCH_ASSOC)) {//only need associative array output
Print_r ($row);
Echo ' //}
//}
$rows = $stmt->fetchall (PDO::FETCH_ASSOC);
Print_r ($rows);
Echo ' $stmt->setfetchmode (PDO::FETCH_ASSOC);//The same implementation effect, with this method can also set the default mode
Var_dump ($stmt);
$rows = $stmt->fetchall ();
Print_r ($rows);
}catch (Pdoexception $e) {
echo $e->getmessage ();
}

In general, we all want to index arrays.

2.5 Setting Database Connection properties

SetAttribute ()--Set database connection properties;

GetAttribute ()--Get database connection properties;

--an example

$pdo =new PDO (' Mysql:host=localhost;dbname=imooc ', ' root ', ');
echo "Auto Commit". $pdo->getattribute (pdo::attr_autocommit); echo "Remember that PDO is an object, so get the attributes, you know. And then it has a lot of set-up property values inside, which is the premise that we get the attributes.
echo "Default error Handling Mode:". $pdo->getattribute (Pdo::attr_errmode); echo "$pdo->setattribute (pdo::attr_autocommit, 0);
echo "Autocommit". $pdo->getattribute (pdo::attr_autocommit); echo "

Then try to get a big wave of attribute information:

$ATTRARR =array (
' Autocommit ', ' errmode ', ' case ', ' persistent ', ' server_info ', ' server_version '
);
foreach ($attrArr as $attr) {
echo "Pdo::attr_$attr:";
echo $pdo->getattribute (Constant ("pdo::attr_$attr")). " <br/> ";
}

Some are not, there will be error messages, no matter.

Third, the use of Pdostatement objects

3.1 Quote () method to prevent SQL injection

--sql Injection

Let's start with an example of this simple SQL injection (in fact, I do not really understand-Baidu a bit http://baike.baidu.com/link?url= JIMTGMTEEPLWAQDANTWBK-WB8XKP8XS3ZOVIJE9IVSTOLP_IT2ANUUAPDMEM0B-VDKNJOLQ8BDXN8YCNLOHUP_)

SQL injection, by inserting a SQL command into a Web form to submit or entering a query string for a domain name or page request, eventually achieves a malicious SQL command that deceives the server.

So that is to have a form, and then need to query data with the database and so on, and then through the malicious use of the rules on the loopholes, to get a lot of, rather than the data that the page wants. Chestnuts are as follows:

Example is logged in the case-login needs to have a user name password, etc., need to compare with the information in the database;

The first is the login page

<! DOCTYPE HTML PUBLIC "-//w3c//dtd HTML 4.01 transitional//en" >
<meta http-equiv= "Content-type" content= "text/html>
<title> Login </title>
<body>
<form action= ' doaction.php ' method= ' post ' >
User name: <input type= ' text ' name= ' username '/><br/>
Password: <input type= ' password ' name= ' password '/><br/>
<input type= ' submit ' value= ' login '/>
</form>
</body>

Notice that the form appears here. then the php file:

<?php
Header (' Content-type:text/html;charset=utf-8 ');
$username =$_post[' username '];
$password =$_post[' password '];
try {
$pdo =new PDO (' Mysql:host=localhost;dbname=imooc ', ' root ', ');
$pdo->exec (' use imooc_pdo ');
$sql = "SELECT * from user where username= ' {$username} ' and password= ' {$password} '";
$stmt = $pdo->query ($sql);
echo $stmt->rowcount ();//Displays the number of rows in the result Set statement object

} catch (Pdoexception $e) {
echo $e->getmessage ();
}

Then open login.html in the browser, enter the database of some username and password, click Login, will get 1;

If you enter the wrong information, you will generally get 0;

Note that if you enter a user name of ' or 1=1#, you can easily get all the data from the database. This is caused by the rules of the SQL statement itself.

So you need to filter the user input information, do not trust all the user's actions.

--Coping methods

echo $pdo->quote ($username);

Write such a sentence, and then use the above cheat code, the output will be more than single quotation marks, and automatically add the \:

' \ ' or 1=1# '

But in doing so, the $username call is automatically quoted, so the following SQL statement follows the change:

$username = $pdo->quote ($username);
$pdo->exec (' use imooc_pdo ');
$sql = "SELECT * from user where username={$username} and password= ' {$password} '";

Simply put the user name of the last set , for the case of a database, it seems to be to prevent this.

However, it is not recommended to use this method-it is recommended to use the Prepare+execute pretreatment method .

3.2 Use of placeholders in pre-processing statements

Good to prevent injection, and once compiled, multiple executions, reduce the cost of the system;

--placeholder: (named parameter) (recommended)

<?php
Header (' Content-type:text/html;charset=utf-8 ');
$username =$_post[' username '];
$password =$_post[' password '];
try {
$pdo =new PDO (' Mysql:host=localhost;dbname=imooc ', ' root ', ');
$pdo->exec (' use imooc_pdo ');
$sql = "SELECT * from user where Username=:username and password=: $password";
$stmt = $pdo->prepare ($sql);
$stmt->execute (": username" + $username, ":p assword" = = $password));
$stmt = $pdo->query ($sql);
echo $stmt->rowcount ();//Displays the number of rows in the result Set statement object

} catch (Pdoexception $e) {
echo $e->getmessage ();
}

corresponding SQL statements, corresponding to the execution, the parameters need to be passed on the corresponding.

--placeholder?　

$sql = "SELECT * from user where username=? and password=? ";
$stmt = $pdo->prepare ($sql);
$stmt->execute (Array ($username, $password));

Feel? To make it a little simpler, enter placeholder + preprocessing + Execute (pass multiple data with array) in the three-point--sql statement.

3.3 Bindparam () method binding parameters

Binds a parameter to the variable name.

<?php
/*
* Binding Parameters
*/

Header (' Content-type:text/html;charset=utf-8 ');
try {
$pdo =new PDO (' Mysql:host=localhost;dbname=imooc ', ' root ', ');
$pdo->exec (' use imooc_pdo ');
$sql = "Insert User (Username,password,email) VALUES (: Username,:p assword,:email)";
$stmt = $pdo->prepare ($sql);
$username = "Wid"; $password = "123"; $email = "[email protected]";//define Parameters
$stmt->bindparam (": Username ", $username, PDO::P aram_str);
$stmt->bindparam (":p assword", $password);
$stmt->bindparam (": Email", $email);
$stmt->execute ();
$res = $pdo->query ("SELECT * from user");
foreach ($res as $row) {//view output (based on return)
//print_r ($row); echo "<br/>";
Echo ' number: '. $row [' id '];echo ' <br/> ';
Echo ' username: '. $row [' username '];echo ' <br/> ';
Echo ' Password: '. $row [' Password '];echo ' <br/> ';
Echo ' mailbox: '. $row [' email '];echo <br/> ';
echo "}

} catch (Pdoexception $e) {
echo $e->getmessage ();
}

The fact is that you do not have to change the SQL statement every time to perform a slightly repetitive operation.

And, of course, you can change the placeholder.

$sql = "Insert User (Username,password,email) VALUES (?,?,?)";

$stmt->bindparam (1, $username);

So, in short, actually: will the placeholder be clearer? Be confused.

3.4 Bindvalue () implements binding parameters

Binds the value to the parameter.

<?php

/*
* Binding Parameters
*/

Header (' Content-type:text/html;charset=utf-8 ');
try {
$pdo =new PDO (' Mysql:host=localhost;dbname=imooc ', ' root ', ');
$pdo->exec (' use imooc_pdo ');
$sql = "Insert User (Username,password,email) VALUES (: Username,:p assword,:email)";
//$sql = "Insert User (Username,password,email) VALUES (?,?,?)";
$stmt = $pdo->prepare ($sql);

//Assume that the email parameter is not changed
$stmt->bindvalue (": Email", ' [email protected] ');
$username = "Wade"; $password = "123";
$stmt->bindparam (": Username", $username, PDO::P aram_str);
$stmt->bindparam (":p assword", $password);
$stmt->execute ();
$res = $pdo->query ("SELECT * from user");
foreach ($res as $row) {//view output (based on return)
//print_r ($row); echo "<br/>";
Echo ' number: '. $row [' id '];echo ' <br/> ';
Echo ' username: '. $row [' username '];echo ' <br/> ';
Echo ' Password: '. $row [' Password '];echo ' <br/> ';
Echo ' mailbox: '. $row [' email '];echo <br/> ';
echo "}

} catch (Pdoexception $e) {
echo $e->getmessage ();
}

The application scenario is that when a value is fixed, the parameter value of the variable can be fixed.

3.5 Bindcolumn () method binding parameters

Binds a column to the PHP object.

$pdo =new PDO (' Mysql:host=localhost;dbname=imooc ', ' root ', ');
$pdo->exec (' use imooc_pdo ');
$sql = "SELECT * from user";
$stmt = $pdo->prepare ($sql);
$stmt->execute ();
Control output
$stmt->bindcolumn (2, $username);
$stmt->bindcolumn (3, $password);
$stmt->bindcolumn (4, $email);
while ($stmt->fetch (pdo::fetch_bound)) {
Echo ' User name: '. $username. ' -Password: '. $password. ' -Email: '. $email. ' }

The use of this is to control the output results, which is conducive to the output format regulation.

Of course, you can see how many columns there are in the result set, and then what each column is:

Echo ' Number of columns in result set: '. $stmt->columncount (). ' Print_r ($stmt->getcolumnmeta (2));

3.6 Fetchcolumn () take a column from the result set

The Getcolumnmeta () method described above is actually an experimental function in this version of PHP and may disappear in a future release.

$stmt->execute ();

Print_r ($stmt->fetchcolumn (3));

It is important to note that this method is very painful because it is performed every time, the pointer is down one bit, so you only need to specify the number of columns, but do not know which row.

3.7 debugdumpparams () prints a preprocessing statement

Test this method in Bindparam:

$stmt->debugdumpparams ();

The result is a whole bunch of:

SQL: [[+] Insert User (Username,password,email) VALUES (: Username,:p assword,:email) params:3 key:name: [9]: username para Mno=-1 name=[9] ": username" is_param=1 param_type=2 key:name: [9]:p assword paramno=-1 name=[9] ":p assword" Is_param=1 PA ram_type=2 key:name: [6]: Email paramno=-1 name=[6] ": Email" is_param=1 param_type=2

In other words, the details of the preprocessing are given.

Obviously it's the way to debug.

3.8 Nextrowset () method to remove all result sets

For example, the stored procedure for MySQL (see my previous MySQL blog post), take out a lot of result sets, and then work on the set.

Actually, the pointer moves down a step.

Example I am lazy, do not want to knock ....

Although not a lot of writing, so it.

After two days want to go to review the foot, although still in pain, do not know also dare not to huoxue ....

I got a big rub-pdo (ii)