Custom field - select box not searchble

[radu81]

hello,

I've just added a custom field to my registration and I noticed that text fields are searchble, but select box (single option) are not searchble. Is that a bug or there is a reason that this kind of fields are not searchble?

Why do I need such a filed? to add to my users the "region" field. there are about 20 in Italy

thank you in advance

[Illori]

sounds like there really is nothing to search as the user is not inputting anything to search on.

[Kindred]

Exactly...  The contents of the select are not stored...   Only the index. (E.g. The number of the option) so there is nothing that can be searched.

[margarett]

Looking at the code for the members search, in the custom fields part:

Code Select Expand

AND (field_type = {string:field_type_text} OR field_type = {string:field_type_textarea})',
So it's clear that only custom profile fields of "text" type are "searchable"

[Kindred]

Exactly...  The contents of the select are not stored...   Only the index. (E.g. The number of the option) so there is nothing that can be searched.

[Arantor]

Exactly...  The contents of the select are not stored...   Only the index. (E.g. The number of the option) so there is nothing that can be searched.

The actual value selected is what is stored, or it was last time I looked.

EDIT: Checked, yes, the actual value in smf_themes for a selectbox is the actual content selected, not the index.

[Kindred]

Really? Hmmm... I could swear that only the numbers were stored last time I checked...

Although, the code margarett posted clearly indicates that only text type fields are searched...  I was under the impression that was because the others could not be searched. Hmmmmmmmm mm....

[Arantor]

Deliberate design choice, but I have no idea why that would be the case.

[Kindred]

Hmmm...   If so, this is definitely something to be reported as a desired change for fix after the initial beta of 2.1

[Arantor]

Same deal with radio buttons too, IIRC (didn't check)

Fairly sure there's some technical reason for it but nothing comes to mind.

[radu81]

so is this a bug? is there a way to make a select box searchable in my user list?

My users asked this feature because they usually make some small meetings and they want to contact the people near them

[Kindred]

no it is not a bug. This is completely by design. However until I have the devs look closer at the code I cannot imagine why it was designed that way...    however, again... it will not be changed in 2.0.x.

[radu81]

  however, again... it will not be changed in 2.0.x.

that's not a great news

[Kindred]

Well, 2.0.x is only receiving security updates and a few bug fuxes. Since this is neither  a bug nor a security issue..........

I also can not promise it would be changed  in 2.1, since there  may be an actual reason it was not done, originally.

[radu81]

Thanks for answering.

I'll try to "solve" this in a different way, by displaying custom fields on userlist. I found this mod http://custom.simplemachines.org/mods/index.php?mod=1520

[margarett]

There has to be something more "deep" for this desing choice. It's easy to get the select box to be searchable. As Arantor says, it's the selected value that gets stored in the custom field value.
But when I select it to be searched I get a "Hacking attempt" and I have no idea why

edit: in error log I get this:

Code Select Expand



http://localhost/smf2/index.php?action=mlist;sa=search
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 tcust_select ON (tcust_select.variable = 'cust_select' AND tcust_select.id_theme = 1 AND tcust_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
Apply Filter: Only show the errors from this file
File: C:/xampp/htdocs/smf2/Sources/Memberlist.php
Line: 541

[Arantor]

Hacking attempt on a query means it doesn't like bare ' in the query.

[margarett]

No idea where that comes from I didn't put it there
I just added this part

Code Select Expand

OR IFNULL(tcust_select.value, '') LIKE '%asdasd%'

[Arantor]

Yes, you have a bare ' in the query

You need to inject the empty string and the other string by way of {string:...} parameters.

[margarett]

That's how the query is built itself, it wasn't me

I did this:

Code Select Expand

// Can they search custom fields?
$request = $smcFunc['db_query']('', '
(...)
AND (field_type = {string:field_type_text} OR field_type = {string:field_type_textarea})',
array(
(...)
'field_type_text' => 'text',
'field_type_textarea' => 'textarea',
)
);
Changed to:

Code Select Expand

// Can they search custom fields?
$request = $smcFunc['db_query']('', '
SELECT col_name, field_name, field_desc
(...)
AND (field_type = {string:field_type_text} OR field_type = {string:field_type_textarea} OR field_type = {string:field_type_select})',
array(
(...)
'field_type_text' => 'text',
'field_type_textarea' => 'textarea',
'field_type_select' => 'select',
)
);
The query itself is built some lines below

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;
}
}


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
);
And I didn't really touched this part, so...

[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