Thursday, March 3, 2011

MySQL's Prepared Statements made really easy with PHP

I've been programming a bit with php for first time, realizing that it is true what my friends told me: it is really close to C/C++.
I managed to have an abstract class to handle and execute SQL's prepared statements, and a bit more that I want to share with world.
But first, just an example of how I made prepared statements really easy to handle with only inheriting from my MySQLDatabase class! Specially if you program a singleton class for your database connection and let __construct and __destruct do its magic!

You can really inherit from this class and see how easy is implementing prepared statements in your php project!

Example of usage

And now... the code itself!

Code for MySQLDatabase class

I hope someone find it usefull!


Adrian said...

A really bright code, as usual in StormByte

But ... what about pass custom SQL Statements on the fly?

Ya know, i'm always looking a step forward when i see some code and thinking about it's applications.

Maybe some checks for strict parameters and some protection for SQL injections will make the magic ;)

C'ya, i-can't-smoke-anywhere-man!


StormByte said...

In response to your comment:
Those are the thoughts I had in mind while coding this class: security, usability, and code reuse.

-SQL Injections: As stated in my previous post, prepared statements PREVENT all kind of SQL injection, as it separates SQL sentence from its parameters.
Every ? in the sentence will be treated ONLY as a parameter, independently what you pass to it. If you try another sub select as a parameter, or another kind of SQL injection, it won't be executed, since it will detect the whole parameter as a string, without affecting the rest of your original query and probably will produce a 0 row set result, but it will NEVER execute any SQL sentence but your original one strictly.
- About executing custom SQL queries on the fly: It is really unneeded, since you can define a new Statement on the fly, and in every moment.
So you will have the advantages of a prepared statement whenever you need it.
In my previous example, I defined all I needed in the constructor for convenience, but I can define it temporally inside a function or where I need it.
- About the strict parameter checking, it can be improved, yes, but it should be enough to cover almost all cases: it is auto detected as an integer, or will be treated as a string as a callback, so it will work anyways.

StormByte said...

I also looked a step forward in coding, and a step forward in using it.
However, this is the third version of this algorithm taking that in mind :)

Ben.F said...

Hey there,

I'm just trying to learn OOP, and every tutorial I come across seems to have rants about how the tutorial teaches bad practices, except yours!

Would you recommend anywhere in particular for learning? I used to know PHP a long time ago, before OOP became "the norm" and have let my knowledge slip.

StormByte said...

@Ben.F I know exactly how you feel, PHP is known by most people yo be a horrible programming language just because it allows nasty things to be done, but my opinion is that it is not PHP who is nasty, they are who are nasty by using nasty programming techniques! Because PHP allowing bad techniques does not mean that you should code that way!
What I suggest you is to learn OOP in a strict typed language (such as C++) prior to apply to PHP, because then, you will know how to avoid bad practices that can be done.
I can give you one big advise: When coding in an OOP language, think your code to be used by other people rather than yourself, that way you will achieve a better OOP design, and a simpler one.