为什么PDO中的INSERT INTO过于复杂？ [关闭]

Question

So I have to change all the mysql_ commands to PDO becuase they are officially depreciated and PDO is the most universal. Why the INSERT ones are so complex and what is benefit of this?

For example in my old code I do this:

mysql_connect("$host", "$username", "$db_password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");
mysql_query("INSERT INTO $tbl_name(this, that, him, her) VALUES('$this', '$that', '$him', '$her')")or die(mysql_error()); 

And with PDO

$conn = new PDO('mysql:host=HST_NAME;dbname=DB_NAME;charset=utf8', 'USER', 'PASSWORD');
$sql = "INSERT INTO books (this, that, him, her) VALUES (:this,:that,:him,:her)";
$q = $conn->prepare($sql);
$q->execute(array(':this'=>$this,
                  ':that'=>$that,
                  ':him'=>$him,
                  ':her'=>$her ));

When I have to input lots of data at once the PDO will get huge. What is the benefit of this?

Looking for a why to do answer and not a what to do

Answer 1

You should not be using variables directly in SQL statements; this leads to all sorts of security vulnerabilities.

As you say, the mysql_ functions are getting deprecated. I know you may be reluctant to move to PDO if you’ve been using the mysql_ functions for a long time, but there’s various reasons why PDO is better than the old mysql_ functions:

• It protects against SQL injection vulnerabilities out of the box
• It’s faster than the mysql_ functions
• It also has the advantage of it supports database engines other than MySQL

PDO also separates your database query from parameters. Consider the following:

$sql = "INSERT INTO users (first_name, last_name, email)
        VALUES (:first_name, :last_name, :email)";

$smt->prepare($sql);
$smt->bindParam(':first_name', $first_name);
$smt->bindParam(':last_name', $last_name);
$smt->bindParam(':email', $email);
$smt->execute();

Or the less “bloated” syntax:

$sql = "INSERT INTO users (first_name, last_name, email)
        VALUES (:first_name, :last_name, :email)";

$smt->prepare($sql);
$smt->execute(array(
    ':first_name' => $first_name,
    ':last_name' => $last_name,
    ':email' => $email
));

As you can see, the parameters are separated from the statement itself. It’s cleaner than interpolating variables into your statements, which look ugly and as I say, lead to injection vulnerabilities.