Why is the INSERT INTO in PDO overly complex?

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

Check: http://www.theserverside.com/news/1365244/Why-Prepared-Statements-are-important-and-how-to-use-them-properly — alxgb, Apr 21 '13 at 19:43
The benefit is that attackers won't steal your database via SQL injection. — SLaks, Apr 21 '13 at 20:14
@SLaks Hey I didn't know that, how does it help to protect? Do you have link or want to explain in an answrr? — Adam Brown, Apr 21 '13 at 20:19
Looks like this question turned to be a [famous duplicate](http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php) — Your Common Sense, Apr 21 '13 at 20:20
Excellent! thank you for the link, this finally explains WHY :) — Adam Brown, Apr 21 '13 at 20:22

score 3 · Answer 1 · edited May 23 '17 at 12:18

3

Because your mysql_query being improperly formatted.
If you care to format it properly, it will take the same amount of code as PDO

Also, for some reason you choose "long" PDO syntax. While the code could be

$sql = "INSERT INTO books (this, that, him, her) VALUES (?,?,?,?)";
$q = $conn->prepare($sql);
$q->execute(array($this,$that,$him,$her));

Nevertheless, for either of them you can use some sort of automation

To answer edited question, Why one should use prepared statements

edited May 23 '17 at 12:18

Community

1
1

answered Apr 21 '13 at 19:36

Your Common Sense

156,878
40
214
345

Actually, if you do it correctly for the `mysql_query`, it would be a LOT more code than using PDO. ^^ – Jon Apr 21 '13 at 19:40
I hate the array way of doing it but I like the answer you have with the MySQL_ part of it. +1 good reliable info here – Rixhers Ajazi Apr 21 '13 at 19:55
1

This doesn't answer the question, what is the benefit? The article on TheServerSide is good and helps – Adam Brown Apr 21 '13 at 20:03

Martin Bean · Accepted Answer · 2013-04-22T11:26:08.480

0

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.

edited Apr 22 '13 at 11:26

answered Apr 21 '13 at 20:23

Martin Bean

38,379
25
128
201

1. old mysql ext can protect you as well. 2. PDO isn't faster. 3. Different databases support won't help you in any practical way. 4. By default, PDO does the very despised interpolation as well. 5. your code being as bloated as in the initial question – Your Common Sense Apr 21 '13 at 20:25
Thank you :) this is an excellent answer and explains. I also have no idea why I'm being downvoted so much +1 – Adam Brown Apr 21 '13 at 20:26
@YourCommonSense Why did you feel the need to pick my answer apart? No one picked yours apart. Yes, origin `mysql_` has protection but it’s getting deprecated so it’s a moot point. I’m sure benchmarks have shown PDO code to be faster than code with `mysql_` functions. You _could_ do interpolated queries and pass them through PDO, yes. And lastly, who mentioned anything about code bloat? You’ve just made that one up to discredit my answer. – Martin Bean Apr 21 '13 at 20:50
1

@MartinBean Your answer was picked apart because you made claims for the advantages without providing any proof to back the claims. And the original question did mention code bloat -> "When I have to input lots of data at once the PDO will get huge". As for the speed, here's a benchmark that was done: http://jnrbsn.com/2010/06/mysqli-vs-pdo-benchmarks – Jon Apr 21 '13 at 21:05
@YourCommonSense That’s MySQLi, not MySQL. So again, you’re just changing your arguments in a way to discredit my answer. – Martin Bean Apr 22 '13 at 11:28

Why is the INSERT INTO in PDO overly complex?

2 Answers2