Friday, February 11, 2011

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!