1

Having an issue with the following sql within the query module.

SELECT
    exp_channel_titles.entry_id,
    exp_channel_titles.title,
    exp_channel_titles.url_title,
    exp_channel_titles.status,
    exp_channel_titles.channel_id,
    exp_channel_data.field_id_66,
    exp_channel_data.field_id_50
FROM
    exp_channel_titles

INNER JOIN exp_channel_data ON exp_channel_data.entry_id = exp_channel_titles.entry_id

WHERE 
    exp_channel_data.field_id_50 = '{embed:member_group}'
OR
    exp_channel_titles.entry_id IN ({embed:additional_projects})
AND
    exp_channel_titles.status = 'open'
AND
    exp_channel_titles.channel_id = '14'

This issue I'm having is if the embed:additional_projects variable has no value (or contains no additional project IDs) it throws an error.

The real test for access to an entry comes with the member group I would just like to add additional entries on a per ID basis if need be. Thanks in advance for any insight or help.

1
  • Have you tested putting {embed:additional_projects} right before your query to make sure it has the value you're expecting? – Adrian Macneil Apr 27 '13 at 22:59
2

This sounds like a parse order issue. It's the sort of thing you could turn into a custom plugin really easily though, and it would avoid creating any SQL injection security issues at the same time.

Create a new file third_party/my_plugin/pi.my_plugin.php

<?php class My_plugin
{
    public function __construct()
    {
        $this->EE = get_instance();
        $this->EE->db->select('
                exp_channel_titles.entry_id,
                exp_channel_titles.title,
                exp_channel_titles.url_title,
                exp_channel_titles.status,
                exp_channel_titles.channel_id,
                exp_channel_data.field_id_66,
                exp_channel_data.field_id_50')
            ->join('exp_channel_data', 'exp_channel_data.entry_id = exp_channel_titles.entry_id')
            ->where('exp_channel_data.field_id_50', $this->EE->TMPL->fetch_param('member_group'))
            ->or_where_in('exp_channel_titles.entry_id', $this->EE->TMPL->fetch_param('additional_projects'))
            ->where('exp_channel_titles.status', 'open')
            ->where('exp_channel_titles.channel_id', '14');
        $result = $this->EE->db->result_array();

        $this->return_data = $this->EE->TMPL->parse_variables($this->EE->TMPL->tagdata, $result);
    }
}

Usage:

{exp:my_plugin member_group="{embed:member_group}" additional_projects="{embed:additional_projects}"}
    {title}
{/exp:my_plugin}
2
  • That sounds like a good idea. I was wondering how secure the query module is. Thanks for your insight – ekard9 Apr 28 '13 at 12:47
  • Re security... The query module is fine, the problem comes if you let your users POST a variable (such as member_group) which you then use directly in your query without properly escaping it, because malicious users could add whatever they like to your SQL. – Adrian Macneil Apr 28 '13 at 23:13
0

Can you try by passing parse="inward" parameter with query tag:

like:

{exp:query sql="SELECT....." parse="inward"}

---------------

{/exp:query}
1
  • Tried with no success. the embed variable does work just fine currently its when i dont have a variable to place in the sql that it doesnt show. The error is: WHERE exp_channel_data.field_id_50 = (1) OR exp_channel_titles.entry_id IN () AND exp_channel_titles.status = 'open' Its the IN () that throws the error – ekard9 Apr 26 '13 at 19:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.