PDO v MySQL

Hi,

I am currently using MySQL but I have been advised a number of times that I should be using PDO.

The problem is I dont understand what PDO is and how it is different to MySQL. If you had a code run on MySQL how would you change it to PDO?

Try one of these [google]PDO tutorial[/google]s.

This will only be of any use to you after you decide to go and learn how to use SQL properly of course. This book is good: [google]Simply SQL by Rudy Limeback[/google] (and he’s a Sitepoint mentor on the sql forum here).

The short answer, PDO is very similar to MySQLi, it teaches you to use an OO approach (or at least you can use an OO approach), and more importantly teaches you to not have SQL injections by using prepared statements or binding your values when executing your SQL.

MySQLi and PDO provide helpers that automatically sanitize your values for you provided you USE those helpers and don’t just concatenate the values into your SQL query. Although you can follow countless articles found on google, I’ll reference the php manual, which shows you a prepared statement and the syntax it uses so that PDO can sanitize your input for you to remove a SQL injection attack.

I don’t think he is a “designated” mentor, but he definitely runs the SQL Forum very well :slight_smile:

thanks for the kind words

you should probably just link to sitepoint’s book page – http://sitepoint.com/books/sql1

i used to be a mentor (then an advisor, and then a team leader), but i gave all that up after several years, and now i just answer easy questions

:wink:

Duly noted.

@rudy; “just answer easy questions” – that sounds like me too :slight_smile:

fyi, that’s not me, that’s some other member

I’m not really fond of using prepared statements as a way of avoiding SQL injections but at least the code is secure that way.

And I hope I won’t open a can of worms with this statement :smiley:

What do you prefer to use then, if not prepared statements?

I too am interested in what you prefer to use. If you prefer to use the MySQLi functions and utilize mysqli_real_escape_string, I won’t complain, but I do think you are doing more work than necessary (as MySQLi even has prepared statements).

Yes, it’s as simple as that, I prefer to use the functions that were designed for this purpose: mysqli_escape_string or PDO::quote - I always use them in a convenience wrapper class so that I can type less.

I don’t think it’s more work than necessary, actually I think it’s much less work. Preparing a statement with objects and methods, binding values and then executing is more work than simply concatenating parts of string using simple $db->quote() calls. For me a php statement for concatenating parts of string preserves natural flow of logic in code (things are happening in simple succession) as opposed to segmented preparing of a query where you have the query with placeholders separately and then the referencing placeholders again with their bound parameters to match. Therefore, I consider the concatenation to be more readable even though it doesn’t look that ‘professional’.

I think that the primary reason for prepared statements is preparing queries for multiple execution with different parameters so that there is gain in performance because the query is parsed only once. Other than that I consider it a bit crazy to prepare something as simple as an SQL query to use it once and ditch it. If we extended the idea a little further we might end up preparing every string we use in php with specialised objects, which would result in at least 3 times as much code to write. This might appeal to Java programmers, though :slight_smile:

I’ve actually got more of a .NET background anymore and prepared statements appeal to me, but purely because at my old job, we forced the use of Stored Procedures for ALL queries (no ad-hoc queries allowed). Why? Because of performance, SQL Server has major performance improvements it applies to stored procedures versus ad-hoc queries. Whether they are run once an hour or 10 times in a row, you will see a benefit. Prepared Statements in my mind tries to fill that void in MySQL’s default (is it still the default engine?) MyISAM engine. INNODB allows stored procedures (I really don’t know if MyISAM allows them yet or not), but I welcome the change, granted it isn’t as powerful as the database handling it (as it only helps during the single execution (I think) and can’t be applied across all requests). So I do understand your argument all too well.

Personally so long as data is sanitized, I can careless what method is used, but I do find it easier to tell people to use ->prepare() in MySQLi or PDO as it is one less thing you have to teach them, as the sanitizing of the data is handled automatically for them (granted, I’ll still mention the method provided helps guard against SQL Injections).

AFAIK Mysql doesn’t have the performance benefit of stored procedures because they are parsed every time they are called. You also lose the benefit of the query cache when using stored procedures so I think ad-hoc queries are the fastest - Mysql still has a long way to catch up!

InnoDB is default as of Mysql 5.5. But I don’t think the db engine has anything to do with prepared statements or stored procedured as they are completely different things. Statement preparation is handled by Mysql core not by the engine so you can use then with any engine.

I was always wondering about this reasoning because whether you use ->prepare() or sanitise a value you have to do something with it, you can’t just inject it directly into the query. Even if people use prepared statements there’s nothing preventing them to insert a $var into the query directly and then the security is compromised. They have to consciously think in each case to prepare the value or to sanitise so I don’t see much difference. Or maybe it’s this simple rule - no string concatenation allowed when writing SQL in PHP?

@Lemon_Juice; Thank you for the interesting reply, I’m going to bear that in mind.

I suppose that part of my motivation for extolling the use of prepared statements is fuelled mostly by wanting to help less experienced coders a less error-prone means of protecting their databases.

For a coder new to using classes, being faced with not only learning PDO but PDOStatement too must be quite a daunting task (well it was for me, as I recall). So, maybe I’ll think again next time.

Well that blows! What was their reasoning for implementing stored procedures then? Takes all of the benefits out (I did some googling and found you were “spot on” on this).

My thinking was at one point InnoDB was the only engine to support stored procedures, since there is no benefit there (as you have proven), my statement didn’t help anything.

Yes, that would be the idea, if you teach them to use prepare, you tell them they cannot concatenate a variable to the query, otherwise, it is all for nothing.

In the end, prepare just fell a few notches from my list. Not sure which I would imply more often yet, but if MySQL fixes their stored procedure fubar then I’ll be recommending it more often again. sigh

I’d like to add a few points.

PDO is an abstraction layer, which makes it easier to change your target DBMS without having to change your code.

A prepared statement is an SQL statement written with placeholders. Upon first use, your target DBMS is asked to compile (optimize it). You client code may then call it repeatedly with different parameters.

A stored procedure is also an SQL statement written with placeholders. It is designed and exists entirely within the DBMS. It is compiled on creation, or on alteration. All clients that call it, are calling the same procedure. They can also be associated with a schema, have user and login level permissions, and other things.

Here’s a few points of interest:

  1. A prepared statement cannot be stored.
  2. You can use a PDO prepared statement to call a stored procedure, assuming your DBMS (E.G. MSSQL) supports them.

Correction, PDO is a data-access abstraction, not a Database abstraction. (Database-specific functions/methods are available with different DB drivers.)

http://www.php.net/manual/en/intro.pdo.php

PDO provides a data-access abstraction layer, which means that, regardless of which database you’re using, you use the same functions to issue queries and fetch data. PDO does not provide a database abstraction; it doesn’t rewrite SQL or emulate missing features. You should use a full-blown abstraction layer if you need that facility.

(PDO does not emulate missing features, except prepared statements.)

That is correct. I apologize if my first statement was missleading. I was in a hurry (trying to get my kids off to school). It is true that not all databases use the same SQL syntax. Some changes may be required from time to time.

FYI, in current versions of SQL server the real “win” for SProc performance is that the server can very effectively cache the execution plan because it is fixed. The same actually applies to dynamic SQL with proper variable binding but the trick is dynamic sql is, well, dynamic so it isn’t always as consistent. Still, if you have a data layer generating SQL it should be consistent enough that query performance will be approaching that of stored procedures.

The way I’d look at this question is really “Is there any reason not to use PDO and prepared statements.” I can’t think of one outside of “I’m working with an existing codebase that doesn’t use it.”

Unfortunately, for MySQL, it doesn’t do that globally, it does it per connection, which makes it meaningless (Source)

Well, to be honest, because I believe (but can’t prove it yet; haven’t found the documentation to support it yet – if you know the answer to this, I’m interested) the preparation only keeps for the given request, beyond that, it is lost, so other requests don’t get to take advantage of the user before them already running that query. This is where if SProcs worked properly in MySQL and it cached the execution plan globally and not per connection, then it would make sense to always recommend prepared statements. But the way it seems to work goes against all expectations and you end up spending time for PHP to cache/compile/optimize a query used for a single request.

Granted, that is ALL under the assumption you are using MySQL. If you are using PDO with SQL Server, you are going to do great things with PDO’s aid in terms of performance optimization.

Is it just me, or has this gone a bit off track (though interesting). The original quandry was about using Mysql via PDO (prepared statements) versus using MySql directly (and therefore real escape string) was it not? More accurately, it was more about the differences between the two, correct? We seemed to have migrated into a discussion on performance benefits of stored procs, prepared statements, and so on. Just saying.