I have a big problem-PDO (2), wipe-pdo_PHP tutorial-PHP Tutorial-php.cn

I got a big rub-PDO (2), rub-pdo

It was 213 again yesterday. Although it was objectively affected by the fact that my roommate didn’t go to bed until after 3 o’clock, the fundamental reason was that I didn’t want to study last night. Start it today. I plan to finish learning PDO and AJAX within 3 or 4 days. I hope everyone will come and scold me if they have nothing to do, so that I won't be lazy again.

1. PDO

2. Use of PDO objects (2)

2.2 Error message

errorCode()——error number;

errorInfo()——error message;

Give me a chestnut

/*
* 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 "An error occurred";
echo $pdo->errorCode();
print_r($pdo->errorInfo());
}

Look at the error message

Array ( [0] => 23000 [1] => 1062 [2] =>

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 still growing).

2.3 query() to implement query

Execute a statement and return a PDOstatement object.

--Give me a chestnut

* 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 statement object
foreach ($result1 as $row){ //View the output results (according to the return situation) print_r($row);
}
if ($result1== false) {
echo "An error occurred";
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 OK and the output is empty.

Of course it will look better this way:

foreach ($result1 as $row){ //View the output results (according to the return situation)

// print_r($row);echo "
";
echo 'number: '.$row['id'];echo "
";
echo 'Username:'.$row['username'];echo "
";
echo 'Password:'.$row['password'];echo "
";
echo 'Email:'.$row['email'];echo "
";
echo "

";
}

Of course, it is no problem to add, delete or modify the query.

2.4 prepare() and execute() methods to implement query

The recommended query method can realize conditional query.

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

execute()——Execute a prepared statement,

return true or false;

So the above is a pair.

--give an 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();//Execution is for prepared statementsvar_dump($result1);

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

if ($result1==false) {

echo "An error occurred";
echo $pdo->errorCode();
print_r($pdo->errorInfo());
}

Be careful to pre-handle this special situation here, and it will be easier to figure out who the target is.

--Select the output format

To associate array output or all or index arrays, there are two different methods: parameters and methods.

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 Associative array output is required
// print_r($row);
// echo '

';
// }
// }
// $rows=$stmt->fetchAll(PDO::FETCH_ASSOC);
// print_r($rows);
echo '

';
$stmt-> ;setFetchMode(PDO::FETCH_ASSOC); //Same implementation effect, you can also use this method, set the default mode
//var_dump($stmt);
$rows=$stmt-> fetchAll();
print_r($rows);
}catch(PDOException $e){
echo $e->getMessage();
}

Generally we want to index an array.

2.5 Set database connection properties

setAttribute()——Set database connection attributes;

getAttribute()——Get the database connection attributes;

--give an example

$pdo=new PDO('mysql:host=localhost;dbname=imooc','root','');
echo "Autocommit".$pdo->getAttribute(PDO: :ATTR_AUTOCOMMIT);echo "

";
//Remember that pdo is an object, so you get the attributes, you know. Then there are many set attribute values inside it, which is the premise for us to 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 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 are not available, there will be error messages, it doesn’t matter.

3. Use of PDOstatement object

3.1 The quote() method prevents SQL injection

--SQL injection

First of all, give an example to illustrate this simple SQL injection (actually I don’t understand it very well - Baidu http://baike.baidu.com/link?url=jiMtgmTeePlWAqdAntWbk-wB8XKP8xS3ZOViJE9IVSToLP_iT2anuUaPdMEM0b-VDknjolQ8BdxN8ycNLohup_)

The so-called SQL injection is to insert SQL commands into Web form submissions or enter domain names or query strings for page requests, ultimately tricking the server into executing malicious SQL commands.

So you need to have a form, and then you need to query the database for data, etc., and then through malicious use of loopholes in the rules, you can get a large amount of data that is not what the page expects. The chestnuts are as follows:

The example is login - login requires user name, password, etc., and needs to be compared with the information in the database;

The first is the login 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, click login, you will get 1;

If you enter incorrect information, you will usually get 0;

Note, if you enter a username such as 'or 1=1# and a password of any size , you will easily get all the data in the database. This is due to the rules of the SQL statement itself.

So you need to filter the information entered by the user and don’t trust all the user’s operations.

--Coping methods

echo $pdo->quote($username);

Write this sentence, and then use the above cheat code, the output will have more single quotes, and automatically add:

''or 1=1#'

But if you do this, the call to $username will be automatically added with quotation marks, so the following sql statement will change accordingly:

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

To put it simply, put the user name on it. It seems that this is something to be guarded against when there is a database.

But it is not recommended to use this method - It is recommended to use the preprocessing method of prepare execute.

3.2 Use of placeholders in prepared statements

It is very good at preventing injection; it can be compiled once and executed multiple times to reduce system overhead;

--Placeholder: (named parameters) (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 user where 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, the corresponding execution, and the parameters that need to be passed must also be corresponding.

--Placeholder?

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

Feeling? The method should be simpler, just three points - input placeholder in sql statement, preprocessing and execution (use array to pass multiple data).

3.3 bindParam() method binds parameters

Bind a parameter to a variable name.

/*
* 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,: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 results (according to the return situation)
// print_r($row );echo "
";
echo 'Number:'.$row['id'];echo "
";
echo 'Username:'.$ row['username'];echo "
";
echo 'Password:'.$row['password'];echo "
";
echo 'Email :'.$row['email'];echo "
";
echo "

";
}

} catch (PDOException $e) {
echo $e->getMessage();
}

In fact, it is to perform slightly repetitive operations without changing the sql statement every time.

Of course you can also change the placeholder

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

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

So, anyway, Actually: placeholders would be clearer,? will be confused.

3.4 bindValue() implements binding parameters

Bind values to parameters.

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

//Assume 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 results (based on the return situation)
// print_r($row);echo "
";
echo 'Number:'.$row['id'];echo "
";
echo 'Username:'.$row['username'];echo "
";
echo 'Password:'.$row['password '];echo "
";
echo 'Email:'.$row['email'];echo "
";
echo "

}

} catch (PDOException $e) {
echo $e->getMessage();
}

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

3.5 bindColumn() method binding parameters

Will 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 'Username:'.$username.'- Password: '.$password.' - Email: '.$email.'

';
}

The usage here is to control the output results, which is conducive to the control of the output format.

Of course, you can see how many columns there are 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() fetches a column from the result set

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

$stmt->execute();

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

It should be noted that the troublesome part of this method is that every time it is executed, the pointer will move down one bit, so you only need to specify which column, but you don’t know which row it is in.

3.7 debugDumpParams() prints a prepared statement

Test this method in bindParam:

$stmt->debugDumpParams();

The result is a bunch of:

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 details of preprocessing will be given.

Obviously this is a method designed for debugging.

3.8 nextRowset() method to retrieve all result sets

For example, it is used for mysql stored procedures (see my previous mysql blog posts), which can take out many result sets at once and then operate on the sets.

In fact, the pointer just moves down step by step.

I am too lazy to type the examples. . . .

Although I didn’t write much, that’s it.

I want to check my foot again in two days. Although it is still hurting, I don’t know if I dare to stimulate blood circulation. . . .