2 if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');
4 /*********************************************************************************
5 * SugarCRM is a customer relationship management program developed by
6 * SugarCRM, Inc. Copyright (C) 2004-2011 SugarCRM Inc.
8 * This program is free software; you can redistribute it and/or modify it under
9 * the terms of the GNU Affero General Public License version 3 as published by the
10 * Free Software Foundation with the addition of the following permission added
11 * to Section 15 as permitted in Section 7(a): FOR ANY PART OF THE COVERED WORK
12 * IN WHICH THE COPYRIGHT IS OWNED BY SUGARCRM, SUGARCRM DISCLAIMS THE WARRANTY
13 * OF NON INFRINGEMENT OF THIRD PARTY RIGHTS.
15 * This program is distributed in the hope that it will be useful, but WITHOUT
16 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
17 * FOR A PARTICULAR PURPOSE. See the GNU Affero General Public License for more
20 * You should have received a copy of the GNU Affero General Public License along with
21 * this program; if not, see http://www.gnu.org/licenses or write to the Free
22 * Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
25 * You can contact SugarCRM, Inc. headquarters at 10050 North Wolfe Road,
26 * SW2-130, Cupertino, CA 95014, USA. or at email address contact@sugarcrm.com.
28 * The interactive user interfaces in modified source and object code versions
29 * of this program must display Appropriate Legal Notices, as required under
30 * Section 5 of the GNU Affero General Public License version 3.
32 * In accordance with Section 7(b) of the GNU Affero General Public License version 3,
33 * these Appropriate Legal Notices must retain the display of the "Powered by
34 * SugarCRM" logo. If the display of the logo is not reasonably feasible for
35 * technical reasons, the Appropriate Legal Notices must display the words
36 * "Powered by SugarCRM".
37 ********************************************************************************/
39 /*********************************************************************************
41 * Description: class for sanitizing field values
42 * Portions created by SugarCRM are Copyright (C) SugarCRM, Inc.
43 * All Rights Reserved.
44 ********************************************************************************/
45 require_once('modules/Import/ImportFile.php');
47 class ImportFieldSanitize
50 * properties set to handle locale formatting
55 public $currency_symbol;
56 public $default_currency_significant_digits;
59 public $default_locale_name_format;
62 * array of modules/users_last_import ids pairs that are created in this class
63 * needs to be reset after the row is imported
65 public static $createdBeans = array();
68 * Validate boolean fields
70 * @param $value string
71 * @param $vardef array
72 * @return string sanitized and validated value on success, bool false on failure
79 $bool_values = array(0=>'0',1=>'no',2=>'off',3=>'n',4=>'yes',5=>'y',6=>'on',7=>'1');
80 $bool_search = array_search($value,$bool_values);
81 if ( $bool_search === false ) {
85 //Convert all the values to a real bool.
86 $value = (int) ( $bool_search > 3 );
88 if ( isset($vardef['dbType']) && $vardef['dbType'] == 'varchar' )
89 $value = ( $value ? 'on' : 'off' );
95 * Validate currency fields
97 * @param $value string
98 * @param $vardef array
99 * @return string sanitized and validated value on success, bool false on failure
101 public function currency(
106 $value = str_replace($this->currency_symbol,"",$value);
108 return $this->float($value,$vardef);
112 * Validate datetimecombo fields
114 * @see ImportFieldSanitize::datetime()
116 * @param $value string
117 * @param $vardef array
118 * @return string sanitized and validated value on success, bool false on failure
120 public function datetimecombo(
125 return $this->datetime($value,$vardef);
129 * Validate datetime fields
131 * @param $value string
132 * @param $vardef array
133 * @return string sanitized and validated value on success, bool false on failure
135 public function datetime(
141 $value = preg_replace('/\s([pm|PM|am|AM]+)/', '\1', $value);
142 $format = $this->dateformat . ' ' . $this->timeformat;
144 if ( !$timedate->check_matching_format($value, $format) ) {
145 // see if adding a valid time at the end makes it work
146 list($dateformat,$timeformat) = explode(' ',$format);
147 $value .= ' ' . date($timeformat,0);
148 if ( !$timedate->check_matching_format($value, $format) ) {
153 if ( !$this->isValidTimeDate($value, $format) )
156 $value = $timedate->swap_formats(
157 $value, $format, $timedate->get_date_time_format());
158 $value = $timedate->handle_offset(
159 $value, $timedate->get_date_time_format(), false, $GLOBALS['current_user'], $this->timezone);
160 $value = $timedate->swap_formats(
161 $value, $timedate->get_date_time_format(), $timedate->get_db_date_time_format() );
167 * Validate date fields
169 * @param $value string
170 * @param $vardef array
171 * @return string sanitized and validated value on success, bool false on failure
173 public function date(
180 $format = $this->dateformat;
182 if ( !$timedate->check_matching_format($value, $format) )
185 if ( !$this->isValidTimeDate($value, $format) )
188 $value = $timedate->swap_formats(
189 $value, $format, $timedate->get_date_format());
195 * Validate email fields
197 * @param $value string
198 * @param $vardef array
199 * @return string sanitized and validated value on success, bool false on failure
201 public function email(
206 if ( !empty($value) && !preg_match('/^\w+(?:[\'.\-+]\w+)*@\w+(?:[.\-]\w+)*(?:[.]\w{2,})+$/',$value) ) {
214 * Validate enum fields
216 * @param $value string
217 * @param $vardef array
218 * @return string sanitized and validated value on success, bool false on failure
220 public function enum(
225 global $app_list_strings;
227 // Bug 27467 - Trim the value given
228 $value = trim($value);
230 if ( isset($app_list_strings[$vardef['options']])
231 && !isset($app_list_strings[$vardef['options']][$value]) ) {
232 // Bug 23485/23198 - Check to see if the value passed matches the display value
233 if ( in_array($value,$app_list_strings[$vardef['options']]) )
234 $value = array_search($value,$app_list_strings[$vardef['options']]);
235 // Bug 33328 - Check for a matching key in a different case
236 elseif ( in_array(strtolower($value), array_keys(array_change_key_case($app_list_strings[$vardef['options']]))) ) {
237 foreach ( $app_list_strings[$vardef['options']] as $optionkey => $optionvalue )
238 if ( strtolower($value) == strtolower($optionkey) )
241 // Bug 33328 - Check for a matching value in a different case
242 elseif ( in_array(strtolower($value), array_map('strtolower', $app_list_strings[$vardef['options']])) ) {
243 foreach ( $app_list_strings[$vardef['options']] as $optionkey => $optionvalue )
244 if ( strtolower($value) == strtolower($optionvalue) )
255 * Validate float fields
257 * @param $value string
258 * @param $vardef array
259 * @return string sanitized and validated value on success, bool false on failure
261 public function float(
266 $value = str_replace($this->num_grp_sep,"",$value);
267 $dec_sep = $this->dec_sep;
268 if ( $dec_sep != '.' ) {
269 $value = str_replace($dec_sep,".",$value);
271 if ( !is_numeric($value) ) {
279 * Split full_name field into first_name and last_name
281 * @param $value string
282 * @param $vardef array
283 * @param $focus object bean of the module we're importing into
284 * @return string sanitized and validated value on success, bool false on failure
286 public function fullname(
292 if ( property_exists($focus,'first_name') && property_exists($focus,'last_name') ) {
293 $name_arr = preg_split('/\s+/',$value);
295 if ( count($name_arr) == 1) {
296 $focus->last_name = $value;
299 // figure out what comes first, the last name or first name
300 if ( strpos($this->default_locale_name_format,'l') > strpos($this->default_locale_name_format,'f') ) {
301 $focus->first_name = array_shift($name_arr);
302 $focus->last_name = join(' ',$name_arr);
305 $focus->last_name = array_shift($name_arr);
306 $focus->first_name = join(' ',$name_arr);
315 * @param $value string
316 * @param $vardef array
317 * @return string sanitized and validated value on success, bool false on failure
324 if ( strlen($value) > 36 ) {
332 * Validate int fields
334 * @param $value string
335 * @param $vardef array
336 * @return string sanitized and validated value on success, bool false on failure
343 $value = str_replace($this->num_grp_sep,"",$value);
344 if (!is_numeric($value) || strstr($value,".")) {
352 * Validate multienum fields
354 * @param $value string
355 * @param $vardef array
356 * @return string sanitized and validated value on success, bool false on failure
358 public function multienum(
363 if(!empty($value) && is_array($value)) {
367 // If someone was using the old style multienum import technique
368 $value = str_replace("^","",$value);
370 // We will need to break it apart to put test it.
371 $enum_list = explode(",",$value);
373 // parse to see if all the values given are valid
374 foreach ( $enum_list as $key => $enum_value ) {
375 $enum_list[$key] = $enum_value = trim($enum_value);
376 if ( $this->enum($enum_value,$vardef) === false ) {
380 $value = encodeMultienumValue($enum_list);
386 * Validate name fields
388 * @param $value string
389 * @param $vardef array
390 * @return string sanitized and validated value on success, bool false on failure
392 public function name(
397 if( isset($vardef['len']) ) {
398 // check for field length
399 $value = sugar_substr($value, $vardef['len']);
406 * Validate num fields
408 * @param $value string
409 * @param $vardef array
410 * @return string sanitized and validated value on success, bool false on failure
417 return $this->int($value,$vardef);
421 * Validate parent fields
423 * @param $value string
424 * @param $vardef array
425 * @param $focus object bean of the module we're importing into
426 * @param $addRelatedBean bool true if we want to add the related bean if it is not found
427 * @return string sanitized and validated value on success, bool false on failure
429 public function parent(
433 $addRelatedBean = true
438 if ( isset($vardef['type_name']) ) {
439 $moduleName = $vardef['type_name'];
440 if ( isset($focus->$moduleName) && isset($beanList[$focus->$moduleName]) ) {
441 $vardef['module'] = $focus->$moduleName;
442 $vardef['rname'] = 'name';
443 $relatedBean = loadBean($focus->$moduleName);
444 $vardef['table'] = $relatedBean->table_name;
445 return $this->relate($value,$vardef,$focus,$addRelatedBean);
453 * Validate relate fields
455 * @param $value string
456 * @param $vardef array
457 * @param $focus object bean of the module we're importing into
458 * @param $addRelatedBean bool true if we want to add the related bean if it is not found
459 * @return string sanitized and validated value on success, bool false on failure
461 public function relate(
465 $addRelatedBean = true
468 if ( !isset($vardef['module']) )
470 $newbean = loadBean($vardef['module']);
472 // Bug 38885 - If we are relating to the Users table on user_name, there's a good chance
473 // that the related field data is the full_name, rather than the user_name. So to be sure
474 // let's try to lookup the field the relationship is expecting to use (user_name).
475 if ( $vardef['module'] == 'Users' && $vardef['rname'] == 'user_name' ) {
476 $userFocus = new User;
477 $userFocus->retrieve_by_string_fields(
478 array($userFocus->db->concat('users',array('first_name','last_name')) => $value ));
479 if ( !empty($userFocus->id) ) {
480 $value = $userFocus->user_name;
484 // Bug 32869 - Assumed related field name is 'name' if it is not specified
485 if ( !isset($vardef['rname']) )
486 $vardef['rname'] = 'name';
488 // Bug 27046 - Validate field against type as it is in the related field
489 $rvardef = $newbean->getFieldDefinition($vardef['rname']);
490 if ( isset($rvardef['type'])
491 && method_exists($this,$rvardef['type']) ) {
492 $fieldtype = $rvardef['type'];
493 $returnValue = $this->$fieldtype($value,$rvardef,$focus,$addRelatedBean);
497 $value = $returnValue;
500 if ( isset($vardef['id_name']) ) {
501 $idField = $vardef['id_name'];
503 // Bug 24075 - clear out id field value if it is invalid
504 if ( isset($focus->$idField) ) {
505 $checkfocus = loadBean($vardef['module']);
506 if ( $checkfocus && is_null($checkfocus->retrieve($focus->$idField)) )
507 $focus->$idField = '';
510 // Bug 38356 - Populate the table entry in the vardef from the bean information in case it's not provided
511 if (!isset($vardef['table'])) {
512 // Set target module table as the default table name
513 $tmpfocus = loadBean($vardef['module']);
514 $vardef['table'] = $tmpfocus->table_name;
517 // be sure that the id isn't already set for this row
518 if ( empty($focus->$idField)
519 && $idField != $vardef['name']
520 && !empty($vardef['rname'])
521 && !empty($vardef['table'])) {
522 // Bug 27562 - Check db_concat_fields first to see if the field name is a concat
523 $relatedFieldDef = $newbean->getFieldDefinition($vardef['rname']);
524 if ( isset($relatedFieldDef['db_concat_fields'])
525 && is_array($relatedFieldDef['db_concat_fields']) )
526 $fieldname = db_concat($vardef['table'],$relatedFieldDef['db_concat_fields']);
528 $fieldname = $vardef['rname'];
529 // lookup first record that matches in linked table
531 FROM {$vardef['table']}
532 WHERE {$fieldname} = '" . $focus->db->quote($value) . "'
535 $result = $focus->db->limitQuery($query,0,1,true, "Want only a single row");
537 if ( $relaterow = $focus->db->fetchByAssoc($result) )
538 $focus->$idField = $relaterow['id'];
539 elseif ( !$addRelatedBean
540 || ( $newbean->bean_implements('ACL') && !$newbean->ACLAccess('save') )
541 || ( in_array($newbean->module_dir,array('Teams','Users')) )
545 // add this as a new record in that bean, then relate
546 if ( isset($relatedFieldDef['db_concat_fields'])
547 && is_array($relatedFieldDef['db_concat_fields']) ) {
548 $relatedFieldParts = explode(' ',$value);
549 foreach ($relatedFieldDef['db_concat_fields'] as $relatedField)
550 $newbean->$relatedField = array_shift($relatedFieldParts);
553 $newbean->$vardef['rname'] = $value;
554 if ( !isset($focus->assigned_user_id) || $focus->assigned_user_id == '' )
555 $newbean->assigned_user_id = $GLOBALS['current_user']->id;
557 $newbean->assigned_user_id = $focus->assigned_user_id;
558 if ( !isset($focus->modified_user_id) || $focus->modified_user_id == '' )
559 $newbean->modified_user_id = $GLOBALS['current_user']->id;
561 $newbean->modified_user_id = $focus->modified_user_id;
563 // populate fields from the parent bean to the child bean
564 $focus->populateRelatedBean($newbean);
566 $newbean->save(false);
567 $focus->$idField = $newbean->id;
568 $this->createdBeans[] = ImportFile::writeRowToLastImport(
569 $focus->module_dir,$newbean->object_name,$newbean->id);
580 * Validate sync_to_outlook field
582 * @param $value string
583 * @param $vardef array
584 * @param $bad_names array used to return list of bad users/teams in $value
585 * @return string sanitized and validated value on success, bool false on failure
587 public function synctooutlook(
595 // cache this object since we'll be reusing it a bunch
596 if ( !($focus_user instanceof User) ) {
598 $focus_user = new User();
602 if ( !empty($value) && strtolower($value) != "all" ) {
603 $theList = explode(",",$value);
605 $bad_names = array();
606 foreach ($theList as $eachItem) {
607 if ( $focus_user->retrieve_user_id($eachItem)
608 || $focus_user->retrieve($eachItem)
614 $bad_names[] = $eachItem;
627 * Validate time fields
629 * @param $value string
630 * @param $vardef array
631 * @return string sanitized and validated value on success, bool false on failure
633 public function time(
640 $format = $this->timeformat;
642 if ( !$timedate->check_matching_format($value, $format) )
645 if ( !$this->isValidTimeDate($value, $format) )
648 $value = $timedate->swap_formats(
649 $value, $format, $timedate->get_time_format());
650 $value = $timedate->handle_offset(
651 $value, $timedate->get_time_format(), false, $GLOBALS['current_user'], $this->timezone);
652 $value = $timedate->handle_offset(
653 $value, $timedate->get_time_format(), true);
659 * Validate varchar fields
661 * @param $value string
662 * @param $vardef array
663 * @return string sanitized and validated value on success, bool false on failure
665 public function varchar(
670 return $this->name($value,$vardef);
674 * Added to handle Bug 24104, to make sure the date/time value is correct ( i.e. 20/20/2008 doesn't work )
676 * @param $value string
677 * @param $format string
678 * @return string sanitized and validated value on success, bool false on failure
680 private function isValidTimeDate(
687 $dateparts = array();
688 $reg = $timedate->get_regular_expression($format);
689 preg_match('@'.$reg['format'].'@', $value, $dateparts);
691 if ( isset($reg['positions']['a'])
692 && !in_array($dateparts[$reg['positions']['a']], array('am','pm')) )
694 if ( isset($reg['positions']['A'])
695 && !in_array($dateparts[$reg['positions']['A']], array('AM','PM')) )
697 if ( isset($reg['positions']['h']) && (
698 !is_numeric($dateparts[$reg['positions']['h']])
699 || $dateparts[$reg['positions']['h']] < 1
700 || $dateparts[$reg['positions']['h']] > 12 ) )
702 if ( isset($reg['positions']['H']) && (
703 !is_numeric($dateparts[$reg['positions']['H']])
704 || $dateparts[$reg['positions']['H']] < 0
705 || $dateparts[$reg['positions']['H']] > 23 ) )
707 if ( isset($reg['positions']['i']) && (
708 !is_numeric($dateparts[$reg['positions']['i']])
709 || $dateparts[$reg['positions']['i']] < 0
710 || $dateparts[$reg['positions']['i']] > 59 ) )
712 if ( isset($reg['positions']['s']) && (
713 !is_numeric($dateparts[$reg['positions']['s']])
714 || $dateparts[$reg['positions']['s']] < 0
715 || $dateparts[$reg['positions']['s']] > 59 ) )
717 if ( isset($reg['positions']['d']) && (
718 !is_numeric($dateparts[$reg['positions']['d']])
719 || $dateparts[$reg['positions']['d']] < 1
720 || $dateparts[$reg['positions']['d']] > 31 ) )
722 if ( isset($reg['positions']['m']) && (
723 !is_numeric($dateparts[$reg['positions']['m']])
724 || $dateparts[$reg['positions']['m']] < 1
725 || $dateparts[$reg['positions']['m']] > 12 ) )
727 if ( isset($reg['positions']['Y']) &&
728 !is_numeric($dateparts[$reg['positions']['Y']]) )