How to create custom MySQL queries in CakePHP?
Solution 1
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();
Solution 2
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`
}
}
Johnathan Au
Updated on July 09, 2022Comments
-
Johnathan Au almost 2 years
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!
-
Francisco Corrales Morales about 10 yearsdoes
query()
prevent SQL injection ? -
Francisco Corrales Morales about 10 yearsdoes
query()
prevent SQL injection ? -
Keval Domadia about 10 yearsNope. It doesn't. However, you can check the variables by using php functions like
is_int()
orctype_alnum
-
thaJeztah almost 10 years@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'));
-
Francisco Corrales Morales almost 10 yearsWell, 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 almost 10 years2.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 :)