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?

Count regexp matches on a table: Sorting result sets by relevance in PostgreSQL

    The idea of counting the number of matches in a field that a regexp has, is a very intesting method to sort sets, for example, a web search result set, when you need the most relevant sources first (for example, containing the searched word more times).

    I researched a bit and unfortunatelly, MySQL is far behind PostgreSQL, despite the fact that Oracle bought it (yes, at first I though it could improve a lot with Oracle's experience, but reality is that Oracle is not going to put another free competitor against its own), so I will write this howto for PostgreSQL with an example.

    PostgreSQL has a very clever function regexp_matches that will show all substrings which matches a pattern and we will use that to count matches and sort result.

    For example, suppose we have this table in database:

 

DATABASE TABLE AND DATA
TABLE NAME: CONTENT id INTEGER htmltext TEXT	TABLE DATA id html 1 key 2 key key key 3 key key 4 not matching pattern

    If we try to search by keyword key, we could want to appear in relevance order:

	Expected result	Actual result
id	2	1
id	3	2
id	1	3

    To achieve this sorting, we need to use regexp_matches correctly along with our result set, like this way:

SQL Example Sorting Sentence
SELECT id, COUNT(*) AS patternmatch    FROM (       SELECT id,regexp_matches(htmltext, 'key', 'g')          FROM CONTENT    )    AS alias    GROUP BY id ORDER BY patternmatch DESC;

    That will result in a set like Expected result in previous table.

    But, that was only an example!! How about a real usage?

    With a little modification of previous sentence, it could be applied to sort a real search query, just substitute bold text with your pattern and your subquery/data set.

    The only requeriment to do that is: Your subquery must have id and htmltext between its results! (and of course, replace 'key' with

    So assuming we have a function called our_expensive_lookup (with the lookup parameter) that do a very expensive lookup by some keyword, we could apply this sorting to our returned results by:

SQL Real Usage Sorting Sentence
SELECT id, COUNT(*) AS patternmatch    FROM (       SELECT id,regexp_matches(htmltext, 'key', 'g')          FROM (             SELECT id, htmltext                FROM our_expensive_lookup('key')          ) AS lookup    ) AS alias    GROUP BY id ORDER BY patternmatch DESC;

    Of course, it can be combined with prepared statements, stored procedures, and so on! The hardest part is done, so just use your imagination!

PostgreSQL Inhertance and Constraints problem solved!

Warning: Using stored procedures and triggers in PostgreSQL is addictive!

    I've been playing around with stored procedures using PLpgSQL language, just to try performance improvements and they are just addictive!

    For example, PostgreSQL has the ability to inherit all data from one or more tables to other table, but unfortunately, any constraint will be inherited as of 9.0.4, and thus, you can not make a FOREIGN KEY constraint agains parent table.

    Fortunately, you can workaround this issue with a little performance impact by using stored procedures, triggers and a separate table for that constraint.

    For example, imagine this scenario:

CREATE TABLE account (
    id SERIAL,
    username VARCHAR(50) UNIQUE NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE employee (
    salary MONEY(6,2) NOT NULL
) INHERITS (account);

CREATE TABLE problematic (
    id SERIAL,
    account_id INTEGER NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (account_id) REFERENCES account(id)
);

    Suppose we have some data:

INSERT INTO employee(username,salary) VALUES ('foo','1280.90'),('bar','3460.75');

    We will have account filled with those usernames as well as employee filed with the rest of values but the problem will come with a:

INSERT INTO problematic(account_id) VALUES(1);

    Since inheritance does not cover constraints, this last INSERT will fail because it will not find corresponding id on account table, despite it is really there.

    But there is NOT all lost! Fortunately we can place triggers on employee to fill and maintain other intermediary table values for simulating that constraint.

    Solution will be to create that table:

CREATE TABLE account_constraint (
    id INTEGER NOT NULL,
    PRIMARY KEY (id)
);

    And to change employee's FOREIGN KEY constraint this way:

FOREIGN KEY (account_id) REFERENCES account_constraint(id);

    And now... how to keep account_constraint table up to date always and in a safe way? The solution rather than code it by yourself in all clients is to use a procedure and place triggers which will fire BEFORE INSERT, UPDATE and DELETE.

    This could be an example of this procedure:

CREATE OR REPLACE FUNCTION account_id_constraint_handler() RETURNS trigger
AS $$
    BEGIN
       IF (TG_OP = 'INSERT') THEN
          INSERT INTO account_constraint(id) VALUES (NEW.id);
       ELSE
          IF (TG_OP = 'UPDATE') THEN
             UPDATE account_constraint SET id=NEW.id WHERE id=OLD.id;
          ELSE
             DELETE FROM account_constraint WHERE id=OLD.id;
             RETURN OLD;
          END IF;
       END IF;
       RETURN NULL;
    END;
$$
Language 'plpgsql';

    And the last thing to do is to set triggers this way:

CREATE TRIGGER insert_accountID
AFTER INSERT ON user_data
FOR EACH ROW
EXECUTE PROCEDURE account_id_constraint_handler();
CREATE TRIGGER update_accountID
AFTER UPDATE ON user_data
FOR EACH ROW
EXECUTE PROCEDURE account_id_constraint_handler();
CREATE TRIGGER delete_accountID
AFTER DELETE ON user_data
FOR EACH ROW
EXECUTE PROCEDURE account_id_constraint_handler();

    You can now alter any data safely maintaining FOREIGN KEY CONSTRAINT!
And the best: database will do all work needed with only a little impact, since PostgreSQL stores procedures in a precompiled state to improve performance a bit.

    Remember that, once you start using stores procedures (or functions) you will not stop as stated in the warning above!

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.

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!