Why the restrictions for custom data field values?

[mrfelton]

Why is it not possible to add a hyphen in the value of a custom data field? ie. I can have 'ABC_123' as a value, but I can't have 'ABC-123/100'. Attempting to enter a value like this results in "Please enter a valid value for this field. You may use a - z, A - Z, 1 - 9, spaces and underline ( _ ) characters. The length of the variable string should be less than 31 characters"

But, why is there this restriction? This is a custom data field, therefore, surely I should be able to enter any custom data I want? My client wants to have a custom data field where they can collect information from users using their own internal ID's as the values. There internal IDs have values like 'ABC-100/101' but it's not possible to enter such a value therefore my client is unable to enter the values and unable to collect the data that they need to.

My chat on IRC went a little like this:
 

<mrfelton> Why is it not possible to add a hyphen in the value of a custom data field? ie. I can have 'ABC_123' as a value, but I can't have 'ABC-123'

<hershel> mrfelton: Do you mean the 'value' or the 'label' of a custom field?

<mrfelton> hershel: I mean the value
 hershel: "Please enter a valid value for this field. You may use a - z, A - Z, 1 - 9, spaces and underline ( _ ) characters. The length of the variable string should be less than 31 characters"

<mrfelton> what is wrong with '-' or '/' ? I can't understand the logic behinds this
 These values should be escaped properly wherever they are used, so why the restriction?

<kurund> mrfelton: validation is done on purpose..
 mrfelton: mostly to prevent db errors etc..

<mrfelton> kurund: I'm sure it is, but what is the purpose?
 kurund: that doesn't make sense. The values should be escaped before entering in to the db at the db layer
 ie, with something similar to the way db_query() works with placeholders in Drupal (I don't know much abouit the Civi db layer)

<kurund> mrfelton: i am pretty sure there was some valid reason for adding that rule.  Can't remember

So, anybody know what the reason for the restriction is? Can the CiviCRM database API not handle placeholders and escaping?

[lcdweb]

Are you sure you're getting that when you actually enter data into the contact's record? Or when you're defining the field name?

I actually just noticed similar restrictions in v2.2 that weren't there in v2.1 when defining a price set field label. I thought it strange that it would impact the label field. Because the label field is public showing, we need more flexibility to control how that is displayed (this particular site had some text in parens, etc. and there's no real way to reproduce what they need to communicate without at least some punctuation).

I recall some past issues related to how Civi took the label value and used it to generate the actual field name (the problem mostly had to do with the field length). I think the function that creates the field name needs to be looked at so users have as much flexibility as possible with the label, without doing harm to the field name generation.

[mrfelton]

Just to be clear - this is a restriction on the VALUE not the label.
I have an Alphanumeric Select field and the restriction is on entering a VALUE when defining each of the multiple choice options at /civicrm/admin/custom/group/field/option

[Dave Greenberg]

Based on latest IRC conversation - this is a legacy rule and probably not needed. "mrfelton" is going to run some tests w/ the rule turned off and report back.

[lcdweb]

Dave --
Re: my comment on price set fields -- it would appear this is a *new* restriction. I only noticed it because I tried to modify a field label for an install recently upgraded from 2.1 to 2.2. The 2.1 data had special characters in the field label field (parens, dashes), but when I tried to modify in 2.2 (even just edit/save with no changes), it wouldn't let me.
-Brian

[Donald Lobo]

i think the restriction for price set is still valid This is since we use the pricesets name fields in the html element name etc. For custom field / option groups etc we've switched to using ids (or so i think)

lobo

[lcdweb]

I took a closer look. It's not actually the price set field name that's the problem, it's the option values for a select/radio/checkbox. It's the value in the label field that the interface won't allow special chars in. That's not an issue for other option value records.

[mrfelton]

I commented out the offending rule at line 167 of CRM/Custom/Form/Option.php. I can now enter any value I like for my form value, and have noticed no adverse effects what so ever. I have tested by entering all kinds of potentially dangerous values like:

'"test'
/@2345E^&*@?''2"''

etc. They seem to be escaped properly and do not cause any trouble.

[Dave Greenberg]

This fix will be part of 3.0 release:
http://issues.civicrm.org/jira/browse/CRM-4770

Thx Tom!

[davej]

Hi,

I've just seen an ill effect of parentheses in an option value value: Activity Report fails to retrieve records when filtering by this option. The value in question was "Accountancy – Prep of Annual Accounts (up to 10k)". Removing the parentheses fixed the problem.

I've also seen an issue where overly long values cause problems when many options are selected, due to hitting the max length of a db field. From memory this was affecting the default values for a field: you can set the field length of the field storing the values for each entity, but the default values are stored in civicrm_custom_field default_value, which is varchar(255).

I'm unclear why it matters if option value values are restricted by length or allowed characters, given that I can have label "ABC-123/100" represented by value "1" if I like: shouldn't it always be the label that's exposed to the user? I think of the value as just for Civi internal use, being what's actually stored in the db. I advise users to keep values short & simple.

Dave J

[Dave Greenberg]

Dave - wondering whether we're missing a needed 'escape' filter on the Activity Report query?