The merits of pdo and prepared statements.

  • (2 Pages)
  • +
  • 1
  • 2

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

#16 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 879
  • View blog
  • Posts: 1,893
  • Joined: 30-January 09

Re: The merits of pdo and prepared statements.

Posted 11 August 2015 - 09:28 PM

CTphpnwb said:

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

Yes it is, but it's not vulnerable to the old '; DELETE EVERYTHING YEEHAW;--' trick, so I'd put it in the same category as MySQL in that regard.

View Postastonecipher, on 12 August 2015 - 02:21 PM, said:

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.

So this comes down to poor querying. I used to write monstrous SQL, and my application still has some monsters in it (50-150 lines). Then I started working at my new workplace, and found they used temporary tables almost all the time. It's a great technique, and the basic pattern is this:
  • Drop the temporary table if it exists
  • Create the temporary table
  • SELECT INTO / INSERT INTO the temporary table from the monster table, using as many filters as possible
  • INDEX the temporary table
  • Use the temporary table in lieu of the actual table for the remainder of the query
  • Drop the temporary table (usually not required if your SQL is correct - see the CREATE TABLE statement below in the spoiler)

Sounds like a pain in the neck, but once you get used to INDEX and CREATE TABLE syntax, it's quite easy. It also makes it quite easy to debug. I've seen this technique cut query times by 70-90% time and time again. With PostgreSQL, since you cannot include ';' characters in a prepared statement, you need to put the script in a stored procedure and call it that way. Here's an example of the contents of the stored proc:

Spoiler


Of course, this is how I think database connections and querying should be approached and handled. It sounds like you have a difficult boss on your hands.

Tell him that binding variables in prepared statements requires you to pass the data type, which helps the PostgreSQL engine determine the best indices to use during execution planning, which in turn helps speed up query times. That's kind of a half-truth, but from the sounds of it, he won't have the nous to refute the statement.
Was This Post Helpful? 0
  • +
  • -

#17 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

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

Re: The merits of pdo and prepared statements.

Posted 11 August 2015 - 10:12 PM

You're doing that as a stored procedure, and that can speed things up too, while having its own security advantages. I'm not sure that it's 100% immune to injection, but if it's not, it's close!
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2