Custom field - select box not searchble

[Arantor]

Then I'm really confused how come you're getting errors.

If you add another option to that last query - array('security_override' => true) as a final parameter after $query_parameters, does it work?

[margarett]

Strange... This is the query now (so that I'm sure I didn't do anything stupid)

Code Select Expand

$request = $smcFunc['db_query']('', '
SELECT COUNT(*)
FROM {db_prefix}members AS mem
LEFT JOIN {db_prefix}membergroups AS mg ON (mg.id_group = CASE WHEN mem.id_group = {int:regular_id_group} THEN mem.id_post_group ELSE mem.id_group END)' .
(empty($customJoin) ? '' : implode('
', $customJoin)) . '
WHERE (' . implode( ' ' . $query . ' OR ', $fields) . ' ' . $query . $condition . ')
AND mem.is_activated = {int:is_activated}',
$query_parameters,
array('security_override' => true)
);

And when I press Search:

Connection Problems
Sorry, SMF was unable to connect to the database. This may be caused by the server being busy. Please try again later.

[Arantor]

Wait, what?! What did you do to it?!?!

Exaggeration for comic effect aside, I'm genuinely curious to know what you've done to it that would produce that. The only thing I can think of is that it ended up trying to recursively error for some strange reason.

Next thing... add $db_show_debug = true; to Settings.php, which will turn off certain error handling and not be quite so generous about catching errors.

[margarett]

This is a local test forum --> db_show_debug is always on

There is nothing in error log, also. Want my Memberlist.php to try it out?

[Arantor]

Please do I might not be able to look at it until tonight but I will look at it, I'm extremely curious as to what this is doing.

[margarett]

Here it is

edit: an update: my modified search function works fine if I remove the security override option, but add in the beginning of the function:

Code Select Expand

$modSettings['disableQueryCheck'] = 1;

And it produces results on selectboxes

[Arantor]

So it is related to the query checker, hmm.

[margarett]

@Arantor, did you have a chance to peek this? I'm very, *very* curious

[Arantor]

I know what it's complaining about.

I didn't get to look at it until just now but I suddenly realised what the problem is. Subs-Db-Mysql.php line 367-8:

Code Select Expand

elseif (preg_match('~\([^)]*?select~s', $clean) != 0)
$fail = true;

cust_select would certainly match that. And as such that is being detected as a subselect and thus not allowed.

You would have to insert even something like tcust_select as a parameter into the query - {raw:cust_select} will let you do so, or rename the table join as something else, e.g. AS tcust_sel_field, as long as it's not 'select'.

[margarett]

Ehehehe Wouldn't think about that in a million years

* margarett goes into testing

[Arantor]

What made me go looking was the fact that disabling security check fixed it - where the security override *didn't*.

So, go look at the code for what the thorough security check is doing

But yeah it's not the first time I've seen that particular matter and I believe the subselect barrier was removed in 2.1 (and if not, it should be)

[margarett]

Not that I need to confirm it but yes, you're right. Comment out those lines in Subs-Db allows the search to run

To mess with the query is a little bit more confuse for me... Because the word "select" cannot be removed from the query, it's the field type...
I also had a terrible aim by naming the field itself "selectbox" which translates "cust_select" but that's something that any user could do, methinks...

[Arantor]

Sure you can. {raw} is a thing.

Anything injected by parameters will not be considered by the check. Including raw parameters.

[margarett]

OK, now I see it A bit more testing coming.

Just changing the field name already fixes the issue --> cust_abcabc

Do you have any idea if the original poster is still interested?

[Arantor]

Yes but you can't rely on that.

Maybe - just not around right now

[margarett]

Sure you can. {raw} is a thing.

Anything injected by parameters will not be considered by the check. Including raw parameters.

Meh. Can't find this

The query is dynamically built:

Code Select Expand

// Any custom fields to search for - these being tricky?
foreach ($_POST['fields'] as $field)
{
$curField = substr($field, 5);
if (substr($field, 0, 5) == 'cust_' && isset($context['custom_search_fields'][$curField]))
{
$customJoin[] = 'LEFT JOIN {db_prefix}themes AS t' . $curField . ' ON (t' . $curField . '.variable = {string:t' . $curField . '} AND t' . $curField . '.id_theme = 1 AND t' . $curField . '.id_member = mem.id_member)';
$query_parameters['t' . $curField] = $curField;
$fields += array($customCount++ => 'IFNULL(t' . $curField . '.value, {string:blank_string})');
$search_fields[] = $field;
}
}
I tried to change the "variable = {string:t' . $curField . '}" to raw but it doesn't seem to have any effect.

[Arantor]

That's not the one that's the problem. It's all the OTHER ones.

You can try this one - but it's not tested.

Code Select Expand

// Any custom fields to search for - these being tricky?
foreach ($_POST['fields'] as $field)
{
$curField = substr($field, 5);
if (substr($field, 0, 5) == 'cust_' && isset($context['custom_search_fields'][$curField]))
{
$customJoin[] = 'LEFT JOIN {db_prefix}themes AS {raw:join_' . $curField . '} ON ({raw:join_' . $curField . '}.variable = {string:t' . $curField . '} AND {raw:join_' . $curField . '}.id_theme = 1 AND {raw:join_' . $curField . '}.id_member = mem.id_member)';
$query_parameters['t' . $curField] = $curField;
$query_parameters['join_' . $curField] = 'join_' . $curField];
$fields += array($customCount++ => 'IFNULL(t' . $curField . '.value, {string:blank_string})');
$search_fields[] = $field;
}
}

The trick is to ensure that any mention of 'select' is injected from the parameters - including table names and aliases.

Now I remember how I encountered this. I had a field whose name was 'selectable' which tripped it...

[radu81]

Thank you margarett and Arantor for your effort, I'm not a coder so I cannot help...

[margarett]

There is a solution already. As long as you don't name your select box select+something
Or, if you disable security before that query, it also works

I still need to test the rest for a proper fix

edit: @Arantor, still returns hacking attempt... There was an extra square bracket that I removed

Code Select Expand


Hacking attempt...

SELECT COUNT(*)
FROM smf_members AS mem
LEFT JOIN smf_membergroups AS mg ON (mg.id_group = CASE WHEN mem.id_group = 0 THEN mem.id_post_group ELSE mem.id_group END)LEFT JOIN smf_themes AS join_cust_select ON (join_cust_select.variable = 'cust_select' AND join_cust_select.id_theme = 1 AND join_cust_select.id_member = mem.id_member)
WHERE (member_name LIKE '%asdasd%' OR real_name LIKE '%asdasd%' OR email_address LIKE '%asdasd%' OR IFNULL(tcust_select.value, '') LIKE '%asdasd%')
AND mem.is_activated = 1
Function: MLSearch

edit2: Just for the fun of it, I tried the changes (my first simple changes to add field_type_select to the custom fields query) in 2.1 and the search works fine
But it triggers some "undefined variable: condition" when a search is performed. Meh
edit3: ha, it triggers the same errors with or without my custom code github time ehehehe