Index: branches/RC/core/kernel/db/dbitem.php =================================================================== diff -u -N -r11892 -r11927 --- branches/RC/core/kernel/db/dbitem.php (.../dbitem.php) (revision 11892) +++ branches/RC/core/kernel/db/dbitem.php (.../dbitem.php) (revision 11927) @@ -1,6 +1,6 @@ $field_value) - { - if( eregi("^[0-9]+$", $field_name) || !array_key_exists($field_name,$this->Fields) ) continue; - if ( is_array($set_fields) && !in_array($field_name, $set_fields) ) continue; + foreach($hash as $field_name => $field_value) { + if (is_numeric($field_name) || !array_key_exists($field_name, $this->Fields)) { + continue; + } + + if (is_array($set_fields) && !in_array($field_name, $set_fields)) { + continue; + } + $this->SetDirtyField($field_name, $field_value); } // formats all fields using associated formatters foreach ($hash as $field_name => $field_value) { - if( eregi("^[0-9]+$", $field_name) || !array_key_exists($field_name,$this->Fields) ) continue; - if ( is_array($set_fields) && !in_array($field_name, $set_fields) ) continue; + if (is_numeric($field_name) || !array_key_exists($field_name, $this->Fields)) { + continue; + } + + if (is_array($set_fields) && !in_array($field_name, $set_fields)) { + continue; + } + $this->SetField($field_name,$field_value); } } - function SetDBFieldsFromHash($hash, $set_fields=null) + function SetDBFieldsFromHash($hash, $set_fields = null) { - foreach ($hash as $field_name => $field_value) - { - if( eregi("^[0-9]+$", $field_name) || !array_key_exists($field_name,$this->Fields) ) continue; - if ( is_array($set_fields) && !in_array($field_name, $set_fields) ) continue; + foreach ($hash as $field_name => $field_value) { + if (is_numeric($field_name) || !array_key_exists($field_name, $this->Fields)) { + continue; + } + if (is_array($set_fields) && !in_array($field_name, $set_fields)) { + continue; + } + $this->SetDBField($field_name, $field_value); } } @@ -324,42 +339,52 @@ */ function Load($id, $id_field_name = null) { - if ( isset($id_field_name) ) $this->SetIDField( $id_field_name ); + if ( isset($id_field_name) ) { + $this->SetIDField($id_field_name); // set new IDField + } + $keys_sql = ''; - if( is_array($id) ) - { + if (is_array($id)) { $keys_sql = $this->GetKeyClause('load', $id); } - else - { + else { $this->setID($id); $keys_sql = $this->GetKeyClause('load'); } - if ( isset($id_field_name) ) $this->setIDField( $this->Application->getUnitOption($this->Prefix, 'IDField') ); + if ( isset($id_field_name) ) { + // restore original IDField from unit config + $this->setIDField( $this->Application->getUnitOption($this->Prefix, 'IDField') ); + } - if( ($id === false) || !$keys_sql ) return $this->Clear(); + if (($id === false) || !$keys_sql) { + return $this->Clear(); + } - if( !$this->raiseEvent('OnBeforeItemLoad', $id) ) return false; + if (!$this->raiseEvent('OnBeforeItemLoad', $id)) { + return false; + } - $q = $this->GetSelectSQL().' WHERE '.$keys_sql; - + $q = $this->GetSelectSQL() . ' WHERE ' . $keys_sql; $field_values = $this->Conn->GetRow($q); - if($field_values) - { + + if ($field_values) { $this->FieldValues = array_merge_recursive2($this->FieldValues, $field_values); $this->OriginalFieldValues = $this->FieldValues; } else { return $this->Clear(); } - if( is_array($id) || isset($id_field_name) ) $this->setID( $this->FieldValues[$this->IDField] ); + if (is_array($id) || isset($id_field_name)) { + $this->setID($this->FieldValues[$this->IDField]); + } $this->UpdateFormattersSubFields(); // used for updating separate virtual date/time fields from DB timestamp (for example) - $this->raiseEvent('OnAfterItemLoad', $this->GetID() ); + $this->raiseEvent('OnAfterItemLoad', $this->GetID()); $this->Loaded = true; + return true; } @@ -385,18 +410,28 @@ } } - function SkipField($field_name, $force_id=false) + /** + * Allows to skip certain fields from getting into sql queries + * + * @param string $field_name + * @param mixed $force_id + * @return bool + */ + function _skipField($field_name, $force_id = false) { $skip = false; - $skip = $skip || ( isset($this->VirtualFields[$field_name]) ); //skipping 'virtual' field - $skip = $skip || ( !getArrayValue($this->FieldValues, $field_name) && getArrayValue($this->Fields[$field_name], 'skip_empty') ); //skipping marked field with 'skip_empty' -// $skip = $skip || ($field_name == $this->IDField && !$force_id); //skipping Primary Key -// $table_name = preg_replace("/^(.*)\./", "$1", $field_name); -// $skip = $skip || ($table_name && ($table_name != $this->TableName)); //skipping field from other tables + // 1. skipping 'virtual' field + $skip = $skip || array_key_exists($field_name, $this->VirtualFields); - $skip = $skip || ( !isset($this->Fields[$field_name]) ); //skipping field not in Fields (nor virtual, nor real) + // 2. don't write empty field value to db, when "skip_empty" option is set + $field_value = array_key_exists($field_name, $this->FieldValues) ? $this->FieldValues[$field_name] : false; + $skip_empty = array_key_exists('skip_empty', $this->Fields[$field_name]) ? $this->Fields[$field_name]['skip_empty'] : false; + $skip = $skip || (!$field_value && $skip_empty); + // 3. skipping field not in Fields (nor virtual, nor real) + $skip = $skip || !array_key_exists($field_name, $this->Fields); + return $skip; } @@ -407,60 +442,75 @@ * @param int Primery Key Id to update * @return bool */ - function Update($id=null, $system_update=false) + function Update($id = null, $system_update = false) { - if( isset($id) ) $this->setID($id); + if (isset($id)) { + $this->setID($id); + } - if( !$this->raiseEvent('OnBeforeItemUpdate') ) return false; + if (!$this->raiseEvent('OnBeforeItemUpdate')) { + return false; + } - if( !isset($this->ID) ) return false; + if (!isset($this->ID)) { + // ID could be set inside OnBeforeItemUpdate event, so don't combine this check with previous one + return false; + } - // Validate before updating + // validate before updating if (!$this->Validate()) { return false; } - if( !$this->raiseEvent('OnAfterItemValidate') ) return false; - //Nothing to update - if(!$this->FieldValues) return true; + if (!$this->raiseEvent('OnAfterItemValidate')) { + return false; + } - $sql = sprintf('UPDATE %s SET ',$this->TableName); - foreach ($this->FieldValues as $field_name => $field_value) - { - if ($this->SkipField($field_name)) continue; + if (!$this->FieldValues) { + // nothing to update + return true; + } - $real_field_name = eregi_replace("^.*\.", '',$field_name); //removing table names from field names + $sql = ''; - //Adding part of SET clause for current field, escaping data with ADODB' qstr - if (is_null( $this->FieldValues[$field_name] )) { - if (isset($this->Fields[$field_name]['not_null']) && $this->Fields[$field_name]['not_null']) { - $sql .= '`'.$real_field_name.'` = '.$this->Conn->qstr($this->Fields[$field_name]['default']).', '; + foreach ($this->FieldValues as $field_name => $field_value) { + if ($this->_skipField($field_name)) { + continue; + } + + if ( is_null($field_value) ) { + if (array_key_exists('not_null', $this->Fields[$field_name]) && $this->Fields[$field_name]['not_null']) { + // "kFormatter::Parse" methods converts empty values to NULL and for + // not-null fields they are replaced with default value here + $field_value = $this->Fields[$field_name]['default']; } - else { - $sql .= '`'.$real_field_name.'` = NULL, '; - } } - else { - $sql.= sprintf('`%s`=%s, ', $real_field_name, $this->Conn->qstr($this->FieldValues[$field_name], 0)); - } + + $sql .= '`' . $field_name . '` = ' . $this->Conn->qstr($field_value) . ', '; } - $sql = ereg_replace(", $", '', $sql); //Removing last comma and space - $sql.= sprintf(' WHERE %s', $this->GetKeyClause('update')); //Adding WHERE clause with Primary Key + $sql = 'UPDATE ' . $this->TableName . ' + SET ' . substr($sql, 0, -2) . ' + WHERE ' . $this->GetKeyClause('update'); - if( $this->Conn->ChangeQuery($sql) === false ) return false; + if ($this->Conn->ChangeQuery($sql) === false) { + // there was and sql error + return false; + } $affected = $this->Conn->getAffectedRows(); - if (!$system_update && $affected == 1){ + if (!$system_update && $affected == 1) { $this->setModifiedFlag(clUPDATE); } $this->saveCustomFields(); $this->raiseEvent('OnAfterItemUpdate'); $this->Loaded = true; + if ($this->mode != 't') { $this->Application->resetCounters($this->TableName); } + return true; } @@ -751,16 +801,21 @@ * @access public * @return bool */ - function Create($force_id=false, $system_create=false) + function Create($force_id = false, $system_create = false) { - if( !$this->raiseEvent('OnBeforeItemCreate') ) return false; + if (!$this->raiseEvent('OnBeforeItemCreate')) { + return false; + } // Validating fields before attempting to create record if (!$this->Validate()) { return false; } - if( !$this->raiseEvent('OnAfterItemValidate') ) return false; + if (!$this->raiseEvent('OnAfterItemValidate')) { + return false; + } + if (is_int($force_id)) { $this->FieldValues[$this->IDField] = $force_id; } @@ -771,35 +826,41 @@ $fields_sql = ''; $values_sql = ''; foreach ($this->FieldValues as $field_name => $field_value) { - if ($this->SkipField($field_name, $force_id)) continue; + if ($this->_skipField($field_name, $force_id)) { + continue; + } - //Adding field' value to Values block of Insert statement, escaping it with qstr - if (is_null( $this->FieldValues[$field_name] )) { - if (isset($this->Fields[$field_name]['not_null']) && $this->Fields[$field_name]['not_null']) { - $values_sql .= $this->Conn->qstr($this->Fields[$field_name]['default'], 0); + if (is_null($field_value)) { + if (array_key_exists('not_null', $this->Fields[$field_name]) && $this->Fields[$field_name]['not_null']) { + // "kFormatter::Parse" methods converts empty values to NULL and for + // not-null fields they are replaced with default value here + $values_sql .= $this->Conn->qstr($this->Fields[$field_name]['default']); } else { - $values_sql .= 'NULL'; + $values_sql .= $this->Conn->qstr($field_value); } } else { - if ($field_name == $this->IDField && $this->FieldValues[$field_name] == 0) { + if (($field_name == $this->IDField) && ($field_value == 0)) { + // don't skip IDField in INSERT statement, just use DEFAULT keyword as it's value $values_sql .= 'DEFAULT'; } else { - $values_sql .= $this->Conn->qstr($this->FieldValues[$field_name], 0); + $values_sql .= $this->Conn->qstr($field_value); } } - $fields_sql .= '`'.$field_name.'`, '; //Adding field name to fields block of Insert statement + + $fields_sql .= '`' . $field_name . '`, '; //Adding field name to fields block of Insert statement $values_sql .= ', '; } - //Cutting last commas and spaces - $fields_sql = ereg_replace(", $", '', $fields_sql); - $values_sql = ereg_replace(", $", '', $values_sql); - $sql = sprintf('INSERT INTO %s (%s) VALUES (%s)', $this->TableName, $fields_sql, $values_sql); //Formatting query + $sql = 'INSERT INTO ' . $this->TableName . ' (' . substr($fields_sql, 0, -2) . ') + VALUES (' . substr($values_sql, 0, -2) . ')'; + //Executing the query and checking the result - if ($this->Conn->ChangeQuery($sql) === false) return false; + if ($this->Conn->ChangeQuery($sql) === false) { + return false; + } $insert_id = $this->Conn->getInsertID(); if ($insert_id == 0) { @@ -818,6 +879,7 @@ } $this->raiseEvent('OnAfterItemCreate'); $this->Loaded = true; + return true; } @@ -829,16 +891,21 @@ */ function Delete($id = null) { - if( isset($id) ) $this->setID($id); + if (isset($id)) { + $this->setID($id); + } - if( !$this->raiseEvent('OnBeforeItemDelete') ) return false; + if (!$this->raiseEvent('OnBeforeItemDelete')) { + return false; + } - $q = 'DELETE FROM '.$this->TableName.' WHERE '.$this->GetKeyClause('Delete'); + $sql = 'DELETE FROM ' . $this->TableName . ' + WHERE ' . $this->GetKeyClause('Delete'); - $ret = $this->Conn->ChangeQuery($q); + $ret = $this->Conn->ChangeQuery($sql); $affected_rows = $this->Conn->getAffectedRows(); - $this->setModifiedFlag(clDELETE); + $this->setModifiedFlag(clDELETE); // will change affected rows, so get it before this line if ($affected_rows > 0) { // something was actually deleted @@ -848,6 +915,7 @@ if ($this->mode != 't') { $this->Application->resetCounters($this->TableName); } + return $ret; } Index: branches/RC/core/kernel/db/db_connection.php =================================================================== diff -u -N -r11892 -r11927 --- branches/RC/core/kernel/db/db_connection.php (.../db_connection.php) (revision 11892) +++ branches/RC/core/kernel/db/db_connection.php (.../db_connection.php) (revision 11927) @@ -1,6 +1,6 @@ qstr("Don't bother",magic_quotes_runtime()); + * If it's a string, adds quotes and backslashes (only work since PHP 4.3.0) + * Otherwise returns as-is * - * @param s the string to quote - * @param [magic_quotes] if $s is GET/POST var, set to get_magic_quotes_gpc(). - * This undoes the stupidity of magic quotes for GPC. - * - * @return quoted string to be sent back to database + * @param mixed $string */ - function qstr($s,$magic_quotes=false) + function qstr($string) { - $replaceQuote = "\\'"; - if (!$magic_quotes) - { - if ($replaceQuote[0] == '\\') - { - // only since php 4.0.5 - $s = str_replace(array('\\',"\0"),array('\\\\',"\\\0"),$s); - //$s = str_replace("\0","\\\0", str_replace('\\','\\\\',$s)); - } - return "'".str_replace("'",$replaceQuote,$s)."'"; + if ( is_null($string) ) { + return 'NULL'; } - // undo magic quotes for " - $s = str_replace('\\"','"',$s); - - if($replaceQuote == "\\'") // ' already quoted, no need to change anything - { - return "'$s'"; - } - else // change \' to '' for sybase/mssql - { - $s = str_replace('\\\\','\\',$s); - return "'".str_replace("\\'",$replaceQuote,$s)."'"; - } + # This will also quote numeric values. This should be harmless, + # and protects against weird problems that occur when they really + # _are_ strings such as article titles and string->number->string + # conversion is not 1:1. + return "'" . mysql_real_escape_string($string, $this->connectionID) . "'"; } /** @@ -649,7 +629,7 @@ $values_sql = ''; foreach ($fields_hash as $field_name => $field_value) { - $values_sql .= ( is_null($field_value) ? 'NULL' : $this->qstr($field_value) ) . ','; + $values_sql .= $this->qstr($field_value) . ','; } // don't use preg here, as it may fail when string is too long @@ -677,7 +657,7 @@ $fields_sql = ''; foreach ($fields_hash as $field_name => $field_value) { - $fields_sql .= '`'.$field_name.'` = ' . ( is_null($field_value) ? 'NULL' : $this->qstr($field_value) ) . ','; + $fields_sql .= '`'.$field_name.'` = ' . $this->qstr($field_value) . ','; } // don't use preg here, as it may fail when string is too long Index: branches/RC/core/kernel/db/dblist.php =================================================================== diff -u -N -r11892 -r11927 --- branches/RC/core/kernel/db/dblist.php (.../dblist.php) (revision 11892) +++ branches/RC/core/kernel/db/dblist.php (.../dblist.php) (revision 11927) @@ -1,6 +1,6 @@