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 SQL injection

    Despite this kind of attack is very old, it is still the primary source of database and website attacks so it really deserves a little speak here and everywhere. In an ideal world, maybe this will not worth a cent, but unfortunatelly we are not in an ideal world.

    Many people say that this is due to malicious people and/or hackers fault, but the guilt comes from both sides: bad programmers and bad people.

    I'll explain this kind of attack with an example (in PHP, but it affects SQL independently of language used).

    Suppose we have a login validation this way:

$sentence="SELECT COUNT(*) FROM Users WHERE username='".$_GET['user']."' AND pass='".$_GET['password']."'";

    (And later in your code check if result>0 in order to check if login was successfull)

    The problem here comes from not checking user inputs, in a good case, sentence will not be a special one, for example: (user input in bold) 

SELECT COUNT(*) FROM Users WHERE username='bob' AND pass='foo';

    But there is a problem, if input is not sanitized, this SQL sentence could be transformed and executed into many SQL sentences allowing a user, for example this can happen because of not sanitizing _GET array:

(1) SELECT COUNT(*) FROM Users WHERE username='bob' OR 1=1;--' AND pass='foo';
or
(2) SELECT COUNT(*) FROM Users WHERE username='bob'; DROP TABLE Users;--' AND PASS='foo';
(3) SELECT COUNT(*) FROM Users WHERE username='bob'; SELECT creditcardnumber FROM Users;--' AND pass='foo';

    Those two sentences with user input marked as bold are self explanatory, but you can see how 1 sentence can be modified to allow you to login with an account which is not yours(1), to destroy data(2) or to gather other kind of sensitive information(3).

    Maybe you are thinking that if this problem is old, it does not worth to speak of it: ERROR!
From time to time, a huge company(Sony) or government agency (CIA) are attacked using this method. This leads me to think: How poor their hired programmers work, and how little do companies value our personal and sensitive data!

    So as this problem has been proved to be relevant and important, let's investigate some solutions.

• Prepared Statements and parametrized queries:

    This seems the best solution: A SQL sentence is no longer crafted by join two or more strings, and SQL keywords are splitted from data. (and also, real sentence is not sent over the network every time it is executed). An example of prepared statement could be: (in postgres)

SELECT COUNT(*) FROM Users WHERE username=$1 AND pass=$2

    This time, postgres already know what type are $1 and $2 and they are parsed accordingly, but the more important fact, $1 and $2 parameters will be treated as a complete string, eliminating the posibility of crafting another sentence from this one. So in this case a malicious input will be treated like:

SELECT COUNT(*) FROM Users WHERE username='bob''; DROP TABLE Users;--' AND pass='foo';

    In this case, as all username will be treated as a whole parameter, this sentence will likelly return 0 rows as a result instead of harming data.
Try to AVOID SQL crafted strings in your source code and use prepared statements!

• Sanitize inputs: While this approach is good enough, I recomment to apply in conjunction with previous one. It consists on checking and escaping any non standard character and convert them to avoid SQL sentence breakage. (In case you can't avoid crafting sentence)
  

$input=pg_escape_string($_GET['username']); and apply it to SQL crafted string, this way, any ' or " character that could break your SQL sentence will be quoted disallowing it to harm your sentence.

    To sum up: Even governments and big companies do not care enough their databases to spend a little time to check source code to avoid this kind of problems.