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?

About Prepared Statements

    Well, I didn't know much about the benefit of having prepared statements as of today. But thanks to trinitycore I discovered a lot.

    As well as trinitycore (which is a framework for a World of Warcraft server and they do it for learning purposes), I learnt too.

    I saw they were moving to Prepared Statements almost all their queries, and wanted to do so on my own projects (specially php ones).

     If you code in OO paradigm, simply prepare all your statements in __construct, and close them in your __destruct ($stmt->close()) function.

    The benefits were huge:

• Avoid ALL kind of SQL injection by treating parameters as strictly parameters. This way, even if a given parameter has some kind of SQL operator, it will be treated as a regular string, rather than execute what it contains.
•  If a prepared statement contains any SQL syntax error (fields not existing, misspelling of a sentence, etc), the STMT object WILL NOT be created, and thus, in destructor, a warning will be raised when you call close function. This way, you can autocheck a sentence WITHOUT actually calling it, and thus, not modifying your database only to check if syntax is OK.
• Also, you will keep SQL data as FAR as you can, and you can encapsulate even more, and more secure, since the only thing you do to query is TO BIND parameters, instead of writting the whole query. That could help you in a possible future redesign changing the less code as possible.
• Speed: That query is stored in server only ONCE, and every time you run it, you only send its parameters to server. This way, you save time, bandwidth and you can focus your code in everything but database.

    There are even more benefits, but.. try them by yourself in your projects!