Programming with Database: Using Prepared Statements in whole program

    I have blogged several times about the benefits of programming with prepared statements when using database connections (About prepared statements, MySQL's prepared statements made easy, About SQL Injection).

    But this time, I will focus on development time, and another advantages that using prepared statements give to programmers rather than its security or performance (for that you can read the other blog posts stated above).

    As I stated in other posts, I recommend everyone who uses a database, to make use of Prepared Statements everywhere, not only in certain parts where you are interested.

    That way, combined with Model View Controller design (MVC), can save you a lot of time and problems when database scheme changes over time (and make sure it will despite you may not think).

    I am talking of common cleaning that a database may suffer some time after it has been used for some time: For example, field removal on a table, table rename, table removal, etc...

    To reflect the time you can save when that happens with Prepared Statements, let's start with a very simple example and watch how it could be solved from 2 different points of view: Using prepared statements, and not using them.

    Suppose we have a table called Customers with this schema (using PostgreSQL):

Column Name	Column Type
customerID	INTEGER
id	INTEGER
name	VARCHAR(50)
email	TEXT
credit_card_no	TEXT

    Having this table amongst others, and in a running company with plenty of data inside, now, some time after the initial database schema, we decide to switch to paypal payments processing (just an example).

In this case, having credit card info of our clients is not longer relevant, and should be deleted due to security issues.
Then now we need something like:

ALTER TABLE Customers DEL COLUMN credit_card_no;

    Unfortunatelly, we are not done yet, as we need to change some parts.

As part of this example, we will see what would happen in 2 cases: 

Using MVC and Prepared Statements

    Now that we have changed database scheme, the changes that are need to be made in source code are very easy to find. If we used something like the database class I blogged here, we just need to open the browser and wait for the errors it will come out, since all SQL sentences are prepared and loaded with database initialization.

It will tell us exactly which prepared statement failed, and just a seach will show in which function member we are using it, allowing us to change it.

Once changed, you just have to be sure to change the model class which receives that old data to remove it.

Not using MVC and not using Prepared Statements

    In this scenario, the changes that needs to be done are not easy to find as we don't have any mechanism to look at every SQL sentence we have in the whole program at once.

So we should to do a seach on every project's file, and do as much as replacements we need in order to achieve the changes, and thus, more time programming which may mean more money.

    So to sum up, I will render a table containing most important things about each others to reflect why is so important to use Prepared Statements rather than SQL statements as strings defined at runtime.

	MVC and Prepared Statements	SQL statements on the fly
Changes find time	Immediate	Search every project's file
Number of files/classes to modify	2 (Database and Model classes)	Undefined (as it may be in use from several files)
Possibility of runtime errors	No (as SQL sentence is not loaded with errors)	Yes (If, for example, you forgot to change a SQL sentence which is based on strings concatenation)

    So now you know that programming with prepared statements does not only protects us from SQL Injection, give us more performance, prevents from having dangerous runtime errors that may harm our public image to our customers and makes us save tons of time when things changes, why are you still not using them in your whole project?

Dropping MySQL, and adopting PostgreSQL

    Finally I had the time to dig a bit this issue, and finally, decided to drop all MySQL support in favor of PostgreSQL.

    Despite PostgreSQL is not as known nor as used as MySQL, it has MANY advantages:

• Referential Integrity: MySQL's default engine (MyIsam) does NOT support any kind of referential integrity, which could lead to corruption and inconsistencies in DB.
• On the other hand, the other MySQL's engine which does support referential integrity (InnoDB) is very much slow. In my case, only to create a table with a referenced FOREIGN KEY to another table takes ~1 second! And cascade deleting/checking/updating also takes much more time then postgre takes.
• Extended Data Types: With PostgreSQL you don't have only a few data types like you have in MySQL. With PostgreSQL you have, cidr (for IP addresses), money, and other data types, as well as user defined data types.
• Prepared Statements: As stated in my previous post, MySQL had support for prepare statements, but you couldn't not give a name to them (unless you use my class, or code it by yourself), but with PostgreSQL, you can even give natively a string name! (even inside client).
• Performance: In my case, with my databases, and googling a bit, you can see that PostgreSQL has much better overall performance than MySQL.

    To sum up, I though that since Oracle bought MySQL, it would be improved a lot, but it seems only a commercial action rather than a really interest in MySQL from Oracle.

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!