Using Prepared Statements With Kohana 3

I just discovered this tonight. (It wasn’t hard, I just had to RTFM).
In Kohana 3 you can query using prepared statements very easily. Prepared statements are important to protect against SQL injection attacks.

Below is a simple example:

$q = 'SELECT * FROM myTable WHERE id = :someId';

$results = DB::query(Database::SELECT,$q,TRUE)
                       ->param(':someId',$MyId)
                       ->execute();
  1. The first step here is to set a string in the query that is a place holder for the value that will be passed in seperately. In this case the placeholder is “:someId”
  2. Next, when calling DB::query, the first argument must be the object that Kohana will use to hold the results. In this case that object is Database::SELECT
  3. Then you pass in the information to the query using “->param(‘:someId’,$MyId)” This says that the value for ‘:someId’ is $MyId.
  4. Finally you can execute the query.

For those of you who are familiar with Coldfusion this is exactly how cfqueryparam works behind the scenes; it creates placeholders and then passes the data in seperately. Cfquery then returns a cfquery object just like how Kohana is returning a DB::SELECT object.