I have a big drive-PDO (2), and I have used the-pdo_PHP tutorial.

I have a big wipe-PDO (2), and I have wiped-pdo. I had a big drive-PDO (II), and I had a 213 million bid yesterday. although my roommate had an objective influence on sleeping at three o'clock, I didn't want to learn things last night. Start today. I plan to take 3 or 4 days for a big-wipe-PDO (2), wipe-pdo

Hi

I went to bed 213 again yesterday. although my roommate had an objective influence on having to go to bed more than three o'clock, I didn't want to learn things last night. Start today. I plan to finish learning PDO and AJAX in three or four days. I hope you will not be able to scold me, and I will be lazy.

1. PDO

II. Use of PDO objects (II)

2.2 error message

ErrorCode () -- Error Code;

ErrorInfo () -- Error message;

Example

/*
* 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').'", "shit@shit.com ")';
$ Result1 = $ pdo-> exec ($ insert );
Var_dump ($ result1 );

If ($ result1 = false){
Echo "error ";
Echo $ pdo-> errorCode ();
Print_r ($ pdo-> errorInfo ());
}

Check the error message.

Array ([0] => 23000 [1] => 1062 [2] => Duplicata du champ 'knga 'pour la clef 'Username ')

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

2.3 query () for query

Execute a statement, Returns a PDOstatement object.

-- Example:

/*
* PDOquery
*/

$ Pdo = new PDO ('MySQL: host = localhost; dbname = imooc ', 'root ','');

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

$ Insert = 'select * from user ';
$ Result1 = $ pdo-> query ($ insert );
Var_dump ($ result1); // view the statement object
Foreach ($ result1 as $ row) {// view the output result (based on the returned information)
Print_r ($ row );
}
If ($ result1 = false ){
Echo "error ";
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 query content does not exist, the statement object is correct and the output is null.

Of course it looks better:

Foreach ($ result1 as $ row) {// view the output result (based on the returned information)
// Print_r ($ row); echo"
";
Echo 'No.:'. $ row ['id']; echo"
";
Echo 'user name: '. $ row ['username']; echo"
";
Echo 'password: '. $ row ['password']; echo"
";
Echo 'email: '. $ row ['email']; echo"
";
Echo"

";
}

Of course, it is okay to add, delete, and modify a query.

2.4 perform queries using the prepare () and execute () methods

The recommended query method can implement conditional query.

Prepare () -- prepare the SQL statement to be executed and return the PDOstatement object;

Execute () -- execute a preprocessing statement,Returns true or false;

So the above is a pair.

-- Example

/*
* 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 );

$ Result1 = $ result-> execute (); // The execution is a preprocessing statement.
Var_dump ($ result1 );

Print_r ($ result-> fetchAll (); // The result output can be obtained only for the statement object.

If ($ result1 = false ){
Echo "error ";
Echo $ pdo-> errorCode ();
Print_r ($ pdo-> errorInfo ());
}

In this case, we need to be careful with preprocessing to identify who the object is.

-- Select the output format

There are two different parameters and methods to associate array output or all or index arrays.

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 needs to associate array output
// Print_r ($ row );
// Echo'

';
//}
//}
// $ Rows = $ stmt-> fetchAll (PDO: FETCH_ASSOC );
// Print_r ($ rows );
Echo' ';
$ Stmt-> setFetchMode (PDO: FETCH_ASSOC); // you can use this method to 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 the array.

2.5 set Database Connection properties

SetAttribute () -- set the database connection attribute;

GetAttribute () -- Get the database connection attribute;

-- Example

$ Pdo = new PDO ('MySQL: host = localhost; dbname = imooc ', 'root ','');
Echo "automatic submission ".$ Pdo-> getAttribute (PDO: ATTR_AUTOCOMMIT); Echo"

";
// Remember that pdo is an object, so you get the attributes, you know. Then there are a lot of set attribute values in it, which is the premise for us to get the attribute.
Echo "default error handling mode:". $ pdo-> getAttribute (PDO: ATTR_ERRMODE); echo" ";
$ Pdo-> setAttribute (PDO: ATTR_AUTOCOMMIT, 0 );
Echo "automatic submission". $ pdo-> getAttribute (PDO: ATTR_AUTOCOMMIT); echo" ";

Then try to get a large 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 "))."
";
}

Some of them do not exist. there is an error message. it doesn't matter.

3. Use of PDOstatement object

3.1 quote () method prevents SQL injection

-- SQL injection

First, give an example to illustrate this simple SQL injection (in fact, I am not very familiar with -- Baidu http://baike.baidu.com/link? Url = jiMtgmTeePlWAqdAntWbk-wB8XKP8xS3ZOViJE9IVSToLP_iT2anuUaPdMEM0b-VDknjolQ8BdxN8ycNLohup _)

SQL injection is to insert SQL commands into Web forms to submit or enter query strings for domain names or page requests, and finally fool the server to execute malicious SQL commands.

Therefore, you need to have a form, and then query data with the database. then, you can obtain a large amount of data through malicious use of the vulnerabilities in the rules, rather than the expected data on the page. The chestnuts are as follows:

In this example, the user name and password are required for logon and must be compared with the information in the database;

FirstLogon page

$ Stmt = $ pdo-> query ($ SQL );
Echo $ stmt-> rowCount (); // display 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 username and password in the database, and click login. 1 is displayed;

If an error message is entered, the result is usually 0;

Note,If you enter a username such as 'or 1 = 1 #, the password is random.You can easily obtain all the data in the database. This is caused by the rules of the SQL statement itself.

ThereforeFilter user input information and do not trust all user operations.

-- Solution

Echo $ pdo-> quote ($ username );

Write such a sentence, and then use the above cheating code, the output will be more single quotes, and automatically add \:

'\' Or 1 = 1 #'

In this case, $ username is automatically quoted, so the following SQL statement must be changed:

$ Username = $ pdo-> quote ($ username );
$ Pdo-> exec ('use imooc_pdo ');
$ SQL = "select * from user where username = {$ username} and password = '{$ password }'";

Simply putSet the user nameIn the case of a database, it seems that this should be prevented.

However, this method is not recommended --We recommend that you use prepare + execute preprocessing..

3.2 Use of placeholders in pre-processing statements

It can prevent injection, and only one compilation can be performed multiple times to reduce system overhead;

-- Placeholder: (name parameter) (recommended)

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 userWhere username =: username and password =: $ password";
$ Stmt = $ pdo-> prepare ($ SQL );
$ Stmt-> execute (array (": username" => $ username, ": password" => $ password ));
// $ Stmt = $ pdo-> query ($ SQL );
Echo $ stmt-> rowCount (); // display the number of rows in the result set statement object

} Catch (PDOException $ e ){
Echo $ e-> getMessage ();
}

The corresponding SQL statement, corresponding execution, and parameters to be passed must also correspond.

-- Placeholder?　

$ SQL = "select * from user where username =? And password =? ";
$ Stmt = $ pdo-> prepare ($ SQL );
$ Stmt-> execute (array ($ username, $ password ));

Feeling? To simplify the method, enter the placeholder + preprocessing + execution in the SQL statement (use array to transmit multiple data ).

3.3 bindParam () method binding parameters

Bind a parameter to the variable name.

/*
* Bind 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,: password,: email )";
$ Stmt = $ pdo-> prepare ($ SQL );
$ Username = "Wid"; $ password = "123"; $ email = "324@qq.com"; // define parameters
$ Stmt-> bindParam (": username", $ username, PDO: PARAM_STR );
$ Stmt-> bindParam (": password", $ password );
$ Stmt-> bindParam (": email", $ email );
$ Stmt-> execute ();
$ Res = $ pdo-> query ("select * from user ");
Foreach ($ res as $ row) {// view the output result (based on the returned information)
// Print_r ($ row); echo"
";
Echo 'No.:'. $ row ['id']; echo"
";
Echo 'user name: '. $ row ['username']; echo"
";
Echo 'password: '. $ row ['password']; echo"
";
Echo 'email: '. $ row ['email']; echo"
";
Echo"

";
}

} Catch (PDOException $ e ){
Echo $ e-> getMessage ();
}

The reason is that you do not need to change the SQL statement each time to execute a slightly repeated operation.

You can also change the placeholder.

// $ SQL = "insert user (username, password, email) values (?,?,?) ";

// $ Stmt-> bindParam (1, $ username );

So, in short,Actually: the placeholder will be clear ,? Will be confused.

3.4 bindValue () bind parameters

Bind the value to the parameter.

/*
* Bind 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,: password,: email )";
// $ SQL = "insert user (username, password, email) values (?,?,?) ";
$ Stmt = $ pdo-> prepare ($ SQL );

// Assume that the email parameter remains unchanged.
$ Stmt-> bindValue (": email", 'shit @ shit.com ');
$ Username = "Wade"; $ password = "123 ";
$ Stmt-> bindParam (": username", $ username, PDO: PARAM_STR );
$ Stmt-> bindParam (": password", $ password );
$ Stmt-> execute ();
$ Res = $ pdo-> query ("select * from user ");
Foreach ($ res as $ row) {// view the output result (based on the returned information)
// Print_r ($ row); echo"
";
Echo 'No.:'. $ row ['id']; echo"
";
Echo 'user name: '. $ row ['username']; echo"
";
Echo 'password: '. $ row ['password']; echo"
";
Echo 'email: '. $ row ['email']; echo"
";
Echo"

";
}

} Catch (PDOException $ e ){
Echo $ e-> getMessage ();
}

The application scenario is to fix the parameter values of a variable when the value is fixed.

3.5 bindColumn () method binding parameters

Bind a column to a 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 usage here isControl the output result to facilitate the control of the output format.

Of course, you can check whether there are several columns in the result set and what each column is:

Echo 'number of columns in the result set: '. $ stmt-> columnCount ().'

';
Print_r ($ stmt-> getColumnMeta (2 ));

3.6 fetchColumn () fetch a column from the result set

The above getColumnMeta () method is actually an experimental function in PHP, and may disappear in future versions.

$ Stmt-> execute ();

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

Note that this method is very painful because it will be executed every time and the pointer goes down one bit. Therefore, you only need to specify the column number, but do not know which row it is in.

3.7 debugDumpParams () prints a preprocessing statement

Test this method in bindParam:

$ Stmt-> debugDumpParams ();

The result is a lot of data:

SQL: [71] insert user (username, password, email) values (: username,: password,: email) Params: 3 Key: Name: [9]: username paramno =-1 name = [9] ": username" is_param = 1 param_type = 2 Key: Name: [9]: password paramno =-1 name = [9] ": password "is_param = 1 param_type = 2 Key: Name: [6]: email paramno =-1 name = [6]": email "is_param = 1 param_type = 2

That is to say, the detailed information of preprocessing is given.

It is obviously a method created for debugging.

3.8 nextRowset () method to retrieve all result sets

For example, the mysql stored procedure (which can be found in my previous mysql blog post) extracts many result sets and then operates on the set.

Actually, the pointer moves down step by step.

For example, I'm lazy and don't want to think about it anymore ....

Although I haven't written a lot, that's it.

I want to check my feet in two days. although it is still painful, I don't know whether I dare to promote blood circulation ....

Today (II), I wiped-pdo hi again 213 yesterday. although my roommate had an objective influence on sleeping at three o'clock, I didn't want to learn things last night. Start today. 3 or 4 days...