The merits of pdo and prepared statements.

  • (2 Pages)
  • +
  • 1
  • 2

16 Replies - 1754 Views - Last Post: 11 August 2015 - 10:12 PM

#1 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2329
  • View blog
  • Posts: 9,359
  • Joined: 03-December 12

The merits of pdo and prepared statements.

Posted 06 August 2015 - 06:11 AM

I started a new position as a software engineer. My boss comes from a C background and is not new, but less experienced with all that php has to offer than others. So, I am working on a project and immediately jump to pdo.

He sees it and comments that we don't use pdo, because if he changes the server or any other credentials, he only wants to do so in one place. Then, he goes on with prepared statements are best for queries that are going to be repetitive and the value is lost for single queries.

The current standard for sanitation is adding single quotes to the input stream. I know prepared statements are best. I am working on extending the current db class to use its connection properties. Then, I want to create some test cases to show prepared statements are better than the current standard.

Has anyone else experienced this? What did you do? How did it workout?

Is This A Good Question/Topic? 0
  • +

Replies To: The merits of pdo and prepared statements.

#2 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon


Reputation: 6966
  • View blog
  • Posts: 14,572
  • Joined: 16-October 07

Re: The merits of pdo and prepared statements.

Posted 06 August 2015 - 06:39 AM

View Postastonecipher, on 06 August 2015 - 09:11 AM, said:

the value is lost for single queries.


There are kind of two parts to this. One, your boss is wrong. Two, how to deal with that.

If the "mountain of evidence" in favor of prepared statements has failed to convince him, do you believe you will have better luck? Best case, your erudite explanation of the issue will finally help them see the light. Worst case, you antagonize your boss by being smart ass. You know the person, this is for you to judge.

Sometimes, you will strongly disagree with decisions made for you. The best that you, an employee, can do is to try to make your case as clearly and professionally as possible. If this brings about change, great. If not, you're going to have to suck it up; you've done your best to make things better and now you just have to deal with how they are. At the end of the day, you are paid to do as you're told.
Was This Post Helpful? 1
  • +
  • -

#3 Atli  Icon User is offline

  • Enhance Your Calm
  • member icon

Reputation: 4238
  • View blog
  • Posts: 7,216
  • Joined: 08-June 10

Re: The merits of pdo and prepared statements.

Posted 06 August 2015 - 06:49 AM

Sounds like your boss has been teaching himself PHP from some very bad/outdated tutorials/books.

  • Using config files for things like SQL credentials is hardly difficult. In fact, it tends to be included in a lot of basic PHP/MySQL tutorials.

  • He's right about prepared statements being best for repeated queries, but that's only in regard to performance. That in itself is not the main reason why people use prepared statements, it's just a nice bonus in some circumstances.

  • Escaping user input WAS the standard 10-15 years ago. Not any more.


I don't know your boss, and how well he takes being corrected, but I would tread very carefully here. On one hand, I'd definitely want to correct the use of poor practices, but on the other hand it's rarely a good idea to turn up to a new job and start telling your boss that he doesn't know what he's talking about.

And definitely don't go against the standard set within the company. If they specifically use legacy MySQL coding, you're better of following their lead, even if you know better.
Was This Post Helpful? 0
  • +
  • -

#4 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2329
  • View blog
  • Posts: 9,359
  • Joined: 03-December 12

Re: The merits of pdo and prepared statements.

Posted 06 August 2015 - 07:28 AM

It's not even mysql, not that it makes a difference, but postgre.

One reason I was hired was my knowledge base and the many mainstream systems I have developed/ contributed to. The current plan is for me to understand their current system and sub systems and work on converting them over to the Zend framework at some point. So, he knows the value I bring and we have had a few discussions on why the company was doing it this way as opposed to that, and it usually stems from lack of knowing x feature was available or how to utilize it.


I'll work on my test case and see if I can get any tread before trying to push the issue. My be a sql injection on a test db will shed some light.
Was This Post Helpful? 0
  • +
  • -

#5 andrewsw  Icon User is online

  • lashings of ginger beer
  • member icon

Reputation: 6343
  • View blog
  • Posts: 25,601
  • Joined: 12-December 12

Re: The merits of pdo and prepared statements.

Posted 06 August 2015 - 07:54 AM

You could also prepare a convincing document. This would hopefully distract you from the day-to-day concern; you'll be prepared when the correct moment arrives, and it could possibly then be distributed to team members. But you'll still be responsible for recognising when, or if, the correct moment arrives!
Was This Post Helpful? 0
  • +
  • -

#6 ArtificialSoldier  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1790
  • View blog
  • Posts: 5,705
  • Joined: 15-January 14

Re: The merits of pdo and prepared statements.

Posted 06 August 2015 - 10:25 AM

Quote

He sees it and comments that we don't use pdo, because if he changes the server or any other credentials, he only wants to do so in one place.

How does that have anything to do with PDO? If the connection information is stored in a single config file somewhere, why does it matter which API you use to connect to the database? That sounds totally non-sequitor to me, like we don't eat apples in this office because Bob over there is allergic to cats.

Is he putting the connection information straight into php.ini or something, and then expecting you to connect without specifying a host, user, etc so that it uses the default? Because that seems terribly limited.

Quote

The current standard for sanitation is adding single quotes to the input stream.

So all input always gets escaped regardless of whether or not it's going to be used in a database query and he thinks that is more efficient or something? What if you don't use it in a query, are you supposed to un-escape it? I figure you always want to get the raw input and only sanitize it based on how you're going to use it. For example, it's kind of stupid to URL-encode everything if you're not going to put it in a URL. It's stupid to replace with HTML entities if you're not going to put something on a web page. If I'm putting some text in a PDF document I don't want it to be URL-escaped, HTML-encoded, or SQL-escaped, I just want the text they submitted. If I'm printing it on a page maybe I'll HTML-encode it, or purify it, or something similar. And, if I'm going to use it in a SQL query, then I'll sanitize it for that, myself, like a big boy. But it sounds like your boss brought back magic_quotes_gpc and decided that he solved the problem.

Would it be a big problem if you talked to your boss like a condescending ass?
Was This Post Helpful? 1
  • +
  • -

#7 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2329
  • View blog
  • Posts: 9,359
  • Joined: 03-December 12

Re: The merits of pdo and prepared statements.

Posted 06 August 2015 - 11:41 AM

Quote

Would it be a big problem if you talked to your boss like a condescending ass?


No, I'm sure he'd love that.

We have 12 databases total each is stored in its own class, the models then extend those classes.
Was This Post Helpful? 0
  • +
  • -

#8 CTphpnwb  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 3701
  • View blog
  • Posts: 13,379
  • Joined: 08-August 08

Re: The merits of pdo and prepared statements.

Posted 07 August 2015 - 06:12 PM

So what does he use if not PDO? Is he aware that the mysql extension is not only deprecated in PHP 5.x, but it's completely removed from PHP 7?
Was This Post Helpful? 0
  • +
  • -

#9 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2329
  • View blog
  • Posts: 9,359
  • Joined: 03-December 12

Re: The merits of pdo and prepared statements.

Posted 07 August 2015 - 07:57 PM

Post #4, not an issue. Our systems run PostgreSQL not mysql so it isn't an issue. He is starting to acknowledge where I am coming from and that I know a few things that he hasn't had previous knowledge of.

It turns out that the way things are done have just followed suit. The original system was written roughly ~15 years ago. Technically, the way it was written makes it doable to add PDO in with out crashing the system, but would still require a few months of integration testing before it would be able to be implemented. One of the systems is mission critical and it being down for 30 minutes costs the company $100k. Which explains why he is so hesitant for any major changes that could be broad ranging.
Was This Post Helpful? 0
  • +
  • -

#10 CTphpnwb  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 3701
  • View blog
  • Posts: 13,379
  • Joined: 08-August 08

Re: The merits of pdo and prepared statements.

Posted 08 August 2015 - 10:26 AM

I haven't used Postgre, but I believe it's also vulnerable to injection.

If the code was written properly it should be possible to change individual queries to use PDO without significantly altering the rest of the system.
Was This Post Helpful? 1
  • +
  • -

#11 Atli  Icon User is offline

  • Enhance Your Calm
  • member icon

Reputation: 4238
  • View blog
  • Posts: 7,216
  • Joined: 08-June 10

Re: The merits of pdo and prepared statements.

Posted 08 August 2015 - 10:23 PM

The Postgres specific extension does actually support prepared statements:
pg_prepare.

So if that extensions is already being used by the system, that might be a better option. That concept would also probably be easier for your boss to accept.

CTphpnwb said:

If the code was written properly...

Always the optimist :)
Was This Post Helpful? 1
  • +
  • -

#12 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2329
  • View blog
  • Posts: 9,359
  • Joined: 03-December 12

Re: The merits of pdo and prepared statements.

Posted 08 August 2015 - 10:31 PM

Found that as well. I ended up sending that docs page to the boss and the other developers to inform the group that proper code could be utilized without having to much to the existing infrastructure.

If he goes for it great. If not like baavgai said, go with the flow. It is their system that I am new to. If they are happy doing it that way, who am I to say differently. I will just keep proper practices while developing things outside of work, where I do have the option.
Was This Post Helpful? 0
  • +
  • -

#13 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2329
  • View blog
  • Posts: 9,359
  • Joined: 03-December 12

Re: The merits of pdo and prepared statements.

Posted 11 August 2015 - 11:41 AM

Found out more information today. At one point theystarted using pdo, but found that it slowed some of the applications that were using down to .75a second. No bad, but when the same app runs a sanitized query it operates at .2.

He also admits that the person that originally architected the system did so poorly. No design pattern for concurrent connections or anything else. I may try it with a singleton and see what numbers can be pushed.
Was This Post Helpful? 0
  • +
  • -

#14 CTphpnwb  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 3701
  • View blog
  • Posts: 13,379
  • Joined: 08-August 08

Re: The merits of pdo and prepared statements.

Posted 11 August 2015 - 08:16 PM

View Postastonecipher, on 11 August 2015 - 02:41 PM, said:

Found out more information today. At one point theystarted using pdo, but found that it slowed some of the applications that were using down to .75a second. No bad, but when the same app runs a sanitized query it operates at .2.

I'd love to see the queries used. I've often thought that binding parameters might take significantly longer in some situations than using execute($someArray) because of all the added parsing, but I've never bothered to test that idea. I'm wondering if that could be a cause for slower prepared statements here.
Was This Post Helpful? 0
  • +
  • -

#15 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2329
  • View blog
  • Posts: 9,359
  • Joined: 03-December 12

Re: The merits of pdo and prepared statements.

Posted 11 August 2015 - 08:21 PM

The slow downs generally come when querying our automotive databases. The kinds that have every make, model, year, engine, transmission, and part# for every vehicle made since 1950.

Some of the queries are a good 60 lines with several joins and his argument, that I see valid, is hitting the database twice with a prep statement slows it down when it may not be needed. Where as prepared statements are used when making several hits for changing values.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2