CakePHP: add variables to query in controller

Question

I have a controller action that uses a sql query:

    $tag = $this->params['tag'];

    $this->set('projects', $this->Project->query('SELECT * FROM projects INNER JOIN projects_tags ON projects.id = projects_tags.project_id INNER JOIN tags on projects_tags.tag_id = tags.id WHERE tags.tag LIKE $tag'));

As you can see at the end I want to use a where clause with the $tag variable but I'm not sure how the syntax would go. As I'm getting the error

Unknown column '$tag' in 'where clause'

Can someone steer me in the right direction?

Ta,

Jonesy

deceze · Accepted Answer · 2010-10-22 09:40:42Z

4

I would strongly advise you to use the Cake ORM instead of raw queries, especially if you're going to plug URL parameters into it. Conditions on HABTM tables can be tricky, but you can build your joins using Cake's ORM syntax as well!

Read the manual, section 3.7.6.9 Joining tables.

answered Oct 22, 2010 at 9:40

deceze♦

525k89 gold badges807 silver badges954 bronze badges

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

2 Comments

iamjonesy Over a year ago

you make a good point. I am going to set aside some time to learn Cake ORM. What are the main advantages though? why are they soo much better than a raw sql query?

Daniel Wright Over a year ago

The main benefits are in-built protection against SQL injection, loose coupling to data-sources, better portability, generally improved readability and maintainability, support for model callbacks and query-caching. And that's just for read operations. Cake's ORM is not the greatest in the world, but it does offer marked improvements over raw SQL.

Daniel Wright · Accepted Answer · 2010-10-22 14:58:46Z

Should you want to use Cake's ORM, the following code should provide results equivalent to your raw SQL query:

$this->loadModel('ProjectsTag'); // Load the joining table as pseudo-model

// Define temporary belongsTo relationships between the pseudo-model and the two real models
$this->ProjectsTag->bindModel(array(
    'belongsTo' => array('Project','Tag')
));

// Retrieve all the join-table records with matching Tag.tag values
$result_set = $this->ProjectsTag->find('all',array(
    'conditions' => array('Tag.tag LIKE' => "%{$tag}%")
));

// Extract the associated Project records from the result-set
$projects = Set::extract('/Project', $result_set);

// Make the set of Project records available to the view
$this->set(compact('projects'));

pleasedontbelong · Accepted Answer · 2010-10-22 09:36:51Z

1

in php there's a difference between single and double quotes... basically, single quotes dont evaluate the variables... use double quotes instead And i think that LIKE will need also single quotes.. i'm not really sure

"SELECT * FROM projects INNER JOIN projects_tags ON projects.id = projects_tags.project_id INNER JOIN tags on projects_tags.tag_id = tags.id WHERE tags.tag LIKE '$tag'"

i know.. i know.. people will start talkin' about sql injection.. and the need to scape the caracters... that's another question =)

good luck!

answered Oct 22, 2010 at 9:36

pleasedontbelong

20.1k12 gold badges55 silver badges79 bronze badges

Comments

Sam D · Accepted Answer · 2010-10-22 14:11:15Z

1

I would at least consider using the cakephp sanitize functions on your tag strings if they are user sourced. See http://book.cakephp.org/view/1183/Data-Sanitization or if using mysql as the db at least consider using http://www.php.net/manual/en/function.mysql-escape-string.php or do something to filter your user input. But the best thing is to make use of the CakePHP orm stuff.

answered Oct 22, 2010 at 14:11

Sam D

4704 silver badges6 bronze badges

Comments

fabrik · Accepted Answer · 2010-10-22 09:36:18Z

-4

Modify your query as:

$this->set('projects',
$this->Project->query("SELECT * FROM projects
                       INNER JOIN projects_tags
                       ON projects.id = projects_tags.project_id
                       INNER JOIN tags ON projects_tags.tag_id = tags.id
                       WHERE tags.tag LIKE '" . $tag . "'") //problem was here
                     );

and it will work.

answered Oct 22, 2010 at 9:36

fabrik

14.4k8 gold badges58 silver badges71 bronze badges

4 Comments

fabrik Over a year ago

Evading SQL injection is out of question currently.

deceze Over a year ago

You should at least note it though, the OP doesn't seem too concerned about it. Using your code as-is would be bad.

fabrik Over a year ago

I don't need to note it. You did it yet. (btw not 'my code' but a corrected one of OP's)

iamjonesy Over a year ago

thanks, I'm going to protect against sql injections though thanks for bringing it up

Collectives™ on Stack Overflow

CakePHP: add variables to query in controller

5 Answers 5

2 Comments

Comments

Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related