How to create custom MySQL queries in CakePHP?

Question

I am trying to create my own MySQL queries in Cakephp.

This is my LocationsController.php:

<?php
App::uses('Location', 'Model');
class LocationsController extends AppController
{
    public $helpers = array('Html', 'Form');
    function index()
    {
        $this->loadModel("Location");
        $this->Location->get();
    }
}

This is my LocationModel.php:

<?php
App::uses('AppModel', 'Model');
class LocationModel extends Model {

    public $name = 'Location';

    public function get()
    {
        $this->Location->query("SELECT * FROM locations;");
    }
}

As you can see, I am just trying to perform a simple query but it doesn't work. I get this error:

Error: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error 
in your SQL syntax; check the manual that corresponds to your MySQL server 
version for the right syntax to use near 'get' at line 1

When I use one of the magic methods like find("all") instead, it works...

Can you see what the problem is? I really can't and I'm only trying to do a simple task!

If you're in the Location model, wouldn't it just be $this->query('SELECT * FROM locations'); ? — SeanCannon
– SeanCannon, Commented Mar 2, 2013 at 4:53
I dont see any sane reason to use a custom query here when you already got the location model and could just do find(all)... you should always ask yourself if you really must use a custom query. then you will find out that you never really need them. — mark
– mark, Commented Mar 2, 2013 at 13:25

thaJeztah · Accepted Answer · 2013-03-02 13:10:01Z

7

The class name of your Location model should be Location, not LocationModel.

Because of this, CakePHP will generate a 'generic' model for the Locations database table and use that model instead of your own model. Because this generic model does not have a get() method, it will execute get as a SQL statement, causing the error

Also, inside the Model, you should not use $this->Location->query();, but simply $this->query();

answered Mar 2, 2013 at 13:10

thaJeztah

29.4k9 gold badges77 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Francisco Corrales Morales Over a year ago

does query() prevent SQL injection ?

thaJeztah Over a year ago

@FranciscoCorrales not if you pass it a literal query with unescaped variables in it, but it does support prepared statements (see the source here). Use it like this: $this->query('SELECT * FROM foo WHERE id=? OR somefield=?', array(123, 'foo'));

Keval Domadia · Accepted Answer · 2013-03-02 05:08:45Z

3

Location Controller should be:

<?php
App::uses('Location', 'Model'); // Y do u need this?
class LocationsController extends AppController
{
    public $helpers = array('Html', 'Form');
    function index()
    {
        $this->loadModel("Location");
        $this->LocationModel->getLocations(); // I will strongly discourage using get()
    }
}

Location Model should be:

<?php
App::uses('AppModel', 'Model');
class LocationModel extends Model {

    public $name = 'Location';

    public function getLocations() // Try to avoid naming a function as get()
    {
    /** Choose either of 2 lines below **/

        return $this->query("SELECT * FROM locations;"); // if table name is `locations`
        return $this->query("SELECT * FROM Location;"); // if table name is `Location` since your public name is `Location`
    }
}

edited Mar 2, 2013 at 5:08

answered Mar 2, 2013 at 4:56

Keval Domadia

4,6931 gold badge39 silver badges65 bronze badges

4 Comments

Francisco Corrales Morales Over a year ago

does query() prevent SQL injection ?

Keval Domadia Over a year ago

Nope. It doesn't. However, you can check the variables by using php functions like is_int() or ctype_alnum

Francisco Corrales Morales Over a year ago

Well, please take a look at this and let me know what you think: github.com/cakephp/cakephp/blob/2.4.9/lib/Cake/Model/…

Keval Domadia Over a year ago

2.4.9! prevent SQL injection , no it doesn't? Plus, what you showed will be further parsed using func_get_args.. Sanitizing will be slow on process but hasn't hurt anybody :)

Collectives™ on Stack Overflow

How to create custom MySQL queries in CakePHP?

2 Answers 2

2 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related