CRM-18510 Getting "One of parameters (value: `Home-street_address`) is not of the type MysqlColumnName" error when Contact Search is used.

Mattias Michaux This seems to be a regression of https://github.com/civicrm/civicrm-core/commit/19b8532d55bb35d3d471bbe859e5ca080e85324f#diff-bf74cd94828120769953f34dab31bf46R124

Could you please take a look?

Steps to replicate:

Search Builder >
Contact >> Street Address >> Home >> LIKE >> Cadell St

One of parameters (value: Home-street_address) is not of the type MysqlColumnName

We're experiencing the same problem, but a different field:

One of parameters (value: `1-email`) is not of the type MysqlColumnName

I've updated the severity since it causes a fatal error post upgrade.

Is this because it's an alias not a column name?

The check is failing because the CRM_Utils_Rule::mysqlColumnName method is called, which uses regex to validate the name. Hyphens are not part of the allowed regex syntax:
https://github.com/civicrm/civicrm-core/blob/8d2206cbca44da02a52ea5f4d7c7ca121f69babe/CRM/Utils/Rule.php#L107

The regex was indeed to strict, as hyphens are used for location types. I created a PR to fix this, see https://github.com/civicrm/civicrm-core/pull/8317

Haven't been able to replicate the error in 4.6.16 and this version doesn't have the CRM_Utils_Type::validate() calls in CRM_Utils_Sort __construct() or in orderBy() . Mattias Michaux Is that correct, 4.6.16 not affected?

Dave Jenkins That's correct, the changes were only backported in afterwards (to limit the impact on 4.6 release), see https://github.com/civicrm/civicrm-core/pull/8290
So 4.6 needs a backport for these fixes too, as otherwise it will have the same regression in 4.6.17
I created a PR, see https://github.com/civicrm/civicrm-core/pull/8326

Replicated the issue on 4.7.7 in Search Builder, as described by Yashodha Chaku above.

Tested https://github.com/civicrm/civicrm-core/pull/8326 - this fixed the issue as tested above.

Couldn't replicate in the way Aaron Cooper describes, from upper left contact search box. Aaron, do you have a default search results profile set up?

I had to patch 4.6.16, so it is certainly affected. I may have been wrong about this...not sure how I ended up subscribed to this thread, I thought this was the bug that I subscribed to, sry.

My installation has no customizations. I added the changes in rule.php and am still getting errors. I am willing to add any recommended changes to test. I assume the typetest.php file is a test scenario file. I am in in Joomla 3.5.1 and PHP 5.5

So I was looking at the link at the top that Yashodha Chaku inserted (https://github.com/civicrm/civicrm-core/commit/19b8532d55bb35d3d471bbe859e5ca080e85324f#diff-bf74cd94828120769953f34dab31bf46R124). It shows three changes in Sort.php. In looking at my Sort.php, it shows only one of the lines matching

'name' => CRM_Utils_Type::validate($value['sort'], 'MysqlColumnName'),

but the second two changes have not been made.

here are the two lines in my Sort.php for 4.7.7.
$this->vars[$this->_currentSortID]['name'] = str_replace(' ', '', $this->_vars[$this->_currentSortID]['name']);
return $this->_vars[$this->_currentSortID]['name'] . ' asc';
}
else {
$this->vars[$this->_currentSortID]['name'] = str_replace(' ', '', $this->_vars[$this->_currentSortID]['name']);
return $this->_vars[$this->_currentSortID]['name'] . ' desc';

Is it possible that the second two changes did not get committed?

The link that Yashodha Chaku added was only one of the commits of that PR, in an following commit the escaping was removed.
There is only 1 line added in that commited PR to the CRM_Utils_Sort, see https://github.com/civicrm/civicrm-core/pull/8265/files#diff-bf74cd94828120769953f34dab31bf46

Ok, thanks, just trying to help out. As I stated above I made the two line changes in Rule.php and it did not correct my search error.

I think I have found a related bug that is quite critical. Search Profiles that include contact fields will FAIL. I have tested with email and phone fields.

I have a profile created that has the following fields:

• First Name (Individual)
• Last Name (Individual)
• Email (Primary/Contact)
  • Exposed Publicly and for Listings
  • Searchable: Yes
  • Results Column (In Selector): Yes

When doing an advanced search and selecting the profile under "Views for Display Contacts", it fails with this error:
One of parameters (value: `1-email`) is not of the type MysqlColumnName

Lee Gooding Are you using 4.7.7? Is it possible to apply the patch (https://patch-diff.githubusercontent.com/raw/civicrm/civicrm-core/pull/8317.patch ) and try again to see if the problem is fixed?

Yes, I'm using 4.7.7.

I applied the changes to Rule.php and that does not fix the issue.

Hm, yes I can reproduce this, the alias name is already escaped. with the patch in this gist (https://gist.github.com/mollux/d1fd488f56d13ded699c5620e603eb13 ) you should be able to search again, but of course it removes the check.

Not sure if this the best approach, but on the onder hand it will be quite hard to fix all the potential already escaped alias en column names.

Yep, this allows us to search again. Thanks for the temp fix for now.

That makes mine work as well. My thanks also for the temp fix.

We experience a similar problem but with a different field. This is a profile that we use:

Name of Service Provider (Organization)
City(Contact)
Zip Code (Contact)
Phone (Contact)
Website (Contact)

Sorry but we are not able to provide this at the moment. One of parameters (value: `1-city`) is not of the type MysqlColumnName

With assistance from davejenx, he provided the following link:

https://patch-diff.githubusercontent.com/raw/civicrm/civicrm-core/pull/8317.patch

We successfully applied that to CiviCRM 4.7.7 running on Drupal 6, and that resolved the same type of MySQL error when filling out new member join forms. (CiviMember + CiviContribute + Profile)

We skipped promoting file: tests/phpunit/CRM/Utils/TypeTest.php however applied from the patch above the other four files.

Please get this in CiviCRM 4.7.8 for sure.

I am thankful,
Michael

We discovered a few issues in #8317 and reopened as #8446.

Both https://github.com/civicrm/civicrm-core/pull/8446 (4.7.8-rc) and
https://github.com/civicrm/civicrm-core/pull/8326 (4.6) are merged now.