category name and multiple languages
If the site has multiple languages and a category is imported using any tool to import (ie. DBio or EasyPopulate) and the import does NOT contain data for all languages, category name can not be updated.
Example:
EN: Hardware
ES: Hardware-es
DE: (null)
Editing the category name in admin will not allow you to save the category name for DE language.
Simple way to reproduce the bug:
using vanilla ZC 1.5.6c, add a new language, then delete the entry from database for "Hardware" category (DELETE FROM categories_description WHERE categories_id = 1 AND language_id = 2;), then try to edit "Hardware" category and save second language name and/or description.
I understand this is not an actual Zen Cart bug because Zen Cart *will* generate the required DB entries for the new language, but the solution is stupid-simple, can save some headaches and IMHO should be the preferred way.
admin/categories.php line 153:
Code:
} elseif ($action == 'update_category') {
$db->Execute('INSERT INTO '.TABLE_CATEGORIES_DESCRIPTION .'
(categories_id, language_id, categories_name, categories_description)
VALUES ("'.(int)$categories_id.'","'.(int)$languages[$i]['id'].'","'.$sql_data_array['categories_name'].'","'.$sql_data_array['categories_description'].'")
ON DUPLICATE KEY UPDATE categories_name = "'.$sql_data_array['categories_name'].'", categories_description = "'.$sql_data_array['categories_description'].'"');
//zen_db_perform(TABLE_CATEGORIES_DESCRIPTION, $sql_data_array, 'insert on duplicate key update', "categories_id = '" . (int)$categories_id . "' and language_id = '" . (int)$languages[$i]['id'] . "'");
}
Re: category name and multiple languages
On the surface of it, this does look like a bug in that the action being 'insert on update' is not a recognized action. Further, the associated function doesn't have a default value for $query, so it should throw a notice as well when so executed.
But then, even on top of that, the on duplicate statement doesn't appear in admin/categories.php for ZC 1.5.6c or above, so where did this come from?
Re: category name and multiple languages
Argh... I pasted stupid code... :lamo:
Code:
} elseif ($action == 'update_category') {
$db->Execute('INSERT INTO '.TABLE_CATEGORIES_DESCRIPTION .'
(categories_id, language_id, categories_name, categories_description)
VALUES ("'.(int)$categories_id.'","'.(int)$languages[$i]['id'].'","'.$sql_data_array['categories_name'].'","'.$sql_data_array['categories_description'].'")
ON DUPLICATE KEY UPDATE categories_name = "'.$sql_data_array['categories_name'].'", categories_description = "'.$sql_data_array['categories_description'].'"');
//zen_db_perform(TABLE_CATEGORIES_DESCRIPTION, $sql_data_array, 'update', "categories_id = '" . (int)$categories_id . "' and language_id = '" . (int)$languages[$i]['id'] . "'");
}
In other words, replace the original zen_db_perform line with the $db->Execute line.
Re: category name and multiple languages
That certainly looks better. :) Funny thing about the situation is that I know in some coding that I purposefully omitted "making sure" that the other fields/languages were generated even with blank records. It seemed counter database like. Until this thread, I hadn't seen anyone indicate a problem such as this. Now, which way to address is right? Ie. Should the other code be updated to put that empty duplicate record, should ZC be updated to incorporate this or both? Personally, I'll be adding something to address this, but I'm curious what the design consideration is/should be.BTW, recommend adding some additional filtering/sanitization to the data fields in the above query. Normally when using zen_db_perform, the fields go through zen_db_input. Also, around the fields that are cast as integers, there do not need to be extra quotes. They are numbers and not text.
Re: category name and multiple languages
Quote:
Originally Posted by
mc12345678
That certainly looks better. :) Funny thing about the situation is that I know in some coding that I purposefully omitted "making sure" that the other fields/languages were generated even with blank records. It seemed counter database like. Until this thread, I hadn't seen anyone indicate a problem such as this. Now, which way to address is right? Ie. Should the other code be updated to put that empty duplicate record, should ZC be updated to incorporate this or both?
If it were up to me, I'd put this in core code since it's "future-proof" and "idiot-proof". Sadly, I fall into the latter group since this originally happened because of MY error while moving data from one database to another... However, it's still something that can easily happen through different scenarios.
Quote:
Personally, I'll be adding something to address this, but I'm curious what the design consideration is/should be.BTW, recommend adding some additional filtering/sanitization to the data fields in the above query. Normally when using zen_db_perform, the fields go through zen_db_input. Also, around the fields that are cast as integers, there do not need to be extra quotes. They are numbers and not text.
You're right about quotes on integers, but sanitization is already performed a few lines before that code and that's why I'm using the $sql_data_array...
Code:
// clean $categories_description when blank or just <p /> left behind
$sql_data_array = array(
'categories_name' => zen_db_prepare_input($categories_name_array[$language_id]),
'categories_description' => ($categories_description_array[$language_id] == '<p />' ? '' : zen_db_prepare_input($categories_description_array[$language_id])));
Re: category name and multiple languages
The two functions zen_db_prepare_input and zen_db_input perform different operations and one does not substitute for the other. Stated "not using the $sql_data_array" but in fact are still using data within it and it is that data that is to be safely presented to the database.
zen_db_prepare_input strips slashes from strings where zen_db_input then in order of preference performs: mysqli_real_escape_string, mysqli_escape_string or addslashes.
I recommend that you review the information about these four php functions as relates to database security, sanitization, and/or preparation.
Re: category name and multiple languages
Yup, you're right, it goes through both functions, unlike my solution.
I've added zen_db_input so it now goes through both.
Until a better solution is presented, I'll stick with the method I had suggested (insert on duplicate key update) and I do hope to see that action added to zen_db_perform someday.
Code:
} elseif ($action == 'update_category') {
$db->Execute('INSERT INTO '.TABLE_CATEGORIES_DESCRIPTION .'
(categories_id, language_id, categories_name, categories_description)
VALUES ('.(int)$categories_id.','.(int)$languages[$i]['id'].',"'.zen_db_input($sql_data_array['categories_name']).'","'.zen_db_input($sql_data_array['categories_description']).'")
ON DUPLICATE KEY UPDATE categories_name = "'.zen_db_input($sql_data_array['categories_name']).'", categories_description = "'.zen_db_input($sql_data_array['categories_description']).'"');
}
Re: category name and multiple languages
So I don't have the specific code, but am thinking that unless a complete rewrite of this area is done, when stepping through the languages and determining whether the current action is to insert or update, that prior to the action to do the update that if the primary key for the item doesn't exist that it should temporarily be identified as an insert.
I say this to potentially reduce the level of code management so that an update will update what is provided and an insert will add what is desired to be inserted and there is no "intermediate" code that would need to be touched if there is/was some change to what the record is to hold...
