« CakePHP in Japan
How to extend a plugin indirectly »

Use placeholder in Model::query() of CakePHP

I’m using CakePHP1.2.3

Model::query() is very useful for writing SQL statements as follow.

<?php

$this->Model->query("SELECT `Post`.`id` FROM `posts` AS `Post` WHERE `Post`.`id` = 100", $cachequeries = false);

To avoid SQL Injection, we want to use placeholders instead of embedding user input value in the statement.
We use “?” character in the SQL statement, set array data in 2nd parameter of the query method.
If 2nd parameter is array data in query method, it executes DboMysql::value() for escape value, using the “mysql_real_escape_string” function.

<?php

$sql = "SELECT `Post`.`id` FROM `posts` AS `Post` WHERE `Post`.`id` = ? LIMIT ?";
$this->Model->query($sql, array(100,1), $cachequeries = false);

Cake constructs a SQL statement and executes as follow.

SELECT `Post`.`id` FROM `posts` AS `Post` WHERE `Post`.`id` = 100 LIMIT 1

References
http://book.cakephp.org/view/456/query
http://en.wikipedia.org/wiki/SQL_injection

This entry was posted on Monday, August 10th, 2009 at 10:25 am and is filed under cakephp, model. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

4 Responses to “Use placeholder in Model::query() of CakePHP”

  1. Jamie Says:
    August 10th, 2009 at 11:49 am

    This may be a silly question, but why are you using Model::query() instead of Model::find()?

  2. ichikaway Says:
    August 10th, 2009 at 6:27 pm

    Hi, Jamie. Thank you for your comment.
    I read your blog, it’s so nice:)

    There are few cases to use Model::query().
    I like Model::find() and use it in many cases.

    When I have to construct a complicated SQL statement, sometimes it’s easy for me to write SQL statement using Model::query() ,instead of Model::find().

  3. gothedistance Says:
    August 15th, 2009 at 12:06 am

    Hi Jamie,ichikaway

    I also using Model::query() to construct complicating sql(especially subquery and case-when statement). Coz it’s simply faster than arrange conditions for Model::find(). I realize how can I get expected datas by using sql not Model::find().

    insert or update, I almost use model::save() or updateAll().

  4. ichikaway Says:
    August 15th, 2009 at 1:17 am

    Hi, gothedistance
    Thank you for your comment.
    Maybe, you are thinking what I’m thinking about a use-case of Model::query().

    I often use updateAll(), it’s convenient.
    The updateAll() does not escape the fields, so be cautious when using it.
    http://teknoid.wordpress.com/2009/08/06/save-now-sanitize-later/

Leave a Reply