I got a big scratch-PDO (2)

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().'

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 digit, 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. . . .