Search

I would like to research and discuss the use of PDO and prepared statements in Symphony, mainly for security reasons.

PDO was already mentioned in a couple of discussion in the past, but as far as I remember, those revolved more around the aspect of database abstraction.

From my experience, PDO has far more to offer, especially regarding security, performance and convenience.

Is the forum the right place to discuss something like this? If not, feel free to point me into the right direction.

I would still go for the database abstraction: create an interface for connecting to the database and allow extension developers to create their own implementation which will increase the number of supported database engines and optimized data access.

I'm currently still working on an SQLite extension in which I address the current issue of having a hardcoded reference to the connection provider class (class.mysql.php) in the installer. I'm still trying to figure out how to make this work properly.

Maybe I shouldn't, and only allow for a switch after the installation is completed, but my goal is to allow for a standalone installation which only requires *AP instead of *AMP.

Anyway, I would strongly encourage further development on connectivity, and I would suggest to go for the database abstraction so that we make more people happy!

Interesting discussion guys, we've been over this a few times, and it was never a priority to do as we saw a few possibilities for breaking backwards compatibility.

Remie, if you can do it, then hats of to you! You would go down in history here ;o)

One of the big problems is SQL styles, as all of the extensions use MySQL syntax, and SQLite doesn't...

Allowing different databases would be nice and PDO definitely makes this easier and more consistent, but if that's not possible due to backwards compatibility and extension support, it still shouldn't hinder us from taking advantage of PDO's enhanced security.

As mentioned above, database abstraction is not the reason why I'm bringing this up again. We could still rely on MySQL as a requirement, so can anybody think of other reasons why Symphony shouldn't make the switch to PDO (long term)?

For further reading:

http://stackoverflow.com/questions/13569/mysqli-or-pdo-what-are-the-pros-and-cons

http://jnrbsn.com/2010/06/mysqli-vs-pdo-benchmarks

Right, the database abstraction discussion is something completely separate from this. We're not talking about swapping MySQL for SQLite or Oracle; we're talking about how we currently interface with MySQL using PHP.

I must admit I've never actually used PDO, but from my .NET days I remember prepared statements being quite useful in avoiding spaghetti SQL-string building. That's not to say that SQL-string building was avoided, because that was almost impossible.

Symphony currently uses parametised query building of sorts. When you use the insert(), update() or delete() methods of the MySQL class you pass the table name and an array of values. The class escapes these for you. The problem we have is that quite often the SQL needs to be built dynamically and then passed to the more generic query() (no return) or fetch() (returns rows) methods.

Our present parameter building is done with sprintf, which look similar to the PDO syntax. sprintf is often more secure than concatenating strings because you can enforce a data type (%s for string, %d for integer), but it's still down to the developer to escape the string values. The MySQL class does have a cleanValue method for this purpose, but evidently not everyone uses it all of the time.

What are the tangible benefits other than a few ms speed boost when running many queries? I can see that the queries executed in core classes could be rewritten to use this (the straight select/insert/update queries), but given that many SQL statements in Symphony are built dynamically, I don't know how this might work with a PDO implementation.

Just wanted to bump this up again. Here's a related discussion on GitHub.

What are the tangible benefits other than a few ms speed boost when running many queries?

@nickdunn Increased out-of-the-box security, more convenient to use, de-facto-standard, peace of mind

I would love for PDO to be added to Symphony

I've used PDO in a project a few weeks ago, and I must say: I am impressed! Because of the prepared statements, you really don't have to be worried for SQL injection flaws, or other nasty things you haven't thought about.

Even though I realize this would be a major rewrite, I do see the added value of this. Thanks for bringing this up Jens.

Because of the prepared statements, you really don't have to be worried for SQL injection flaws, or other nasty things you haven't thought about.

Yeah, that's exactly my point. Turns out you can have prepared statements without PDO as well, but I think PDO already is and will even more become THE way to go in php development.

Here's an article on PDO at nettuts: Why you Should be using PHP’s PDO for Database Access (Though several of the comments correct mistakes in it.)

I know your idea is not for enabling use of other databases other than MySQL as such, but if SQLite support comes with use of PDO (I don't know if it's that simple or not) that would make it possible to include a development Symphony database as part of the repo if it was desired - good for quickly creating new development sites and developing on the go when out and about.

I believe Brendan and I think positively of PDO. I think the main issue is to work out a way to implement it given our repository of existing extensions. We'll need one or two developers to back this up and work with us.

We'll need one or two developers to back this up and work with us.

If only I had more time...

If only I had more time...

Same here... :(

@Allen I'm in.

Create an account or sign in to comment.

Symphony • Open Source XSLT CMS

Server Requirements

  • PHP 5.3-5.6 or 7.0-7.3
  • PHP's LibXML module, with the XSLT extension enabled (--with-xsl)
  • MySQL 5.5 or above
  • An Apache or Litespeed webserver
  • Apache's mod_rewrite module or equivalent

Compatible Hosts

Sign in

Login details