Magento customer collection loading: how to fetch names attributes values

I know, I’m late publishing new topics: one per week is difficult when your evenings are busy…

But today, I’m leaving (temporary) my others stuffs to share with you something I’ve discover today: loading customer collection in Magento can be redundant in some cases if you use the addNameToSelect method. I’ll explain:

Case study: loading a collection

I’ll found this topic by doing a SQL query like this one in a Magento 1.6:

$customerCollection = Mage::getModel('customer/customer')->getCollection();
/* @var $customerCollection Mage_Customer_Model_Entity_Customer_Collection */
$customerCollection->addAttributeToSelect(array(
    'dob', 'firstname', 'lastname', 'email'
));
foreach ($customerCollection as $customer) {
    /* @var $customer Mage_Customer_Model_Customer */
    echo $customer->getFirstame() . ' ' . $customer->getLastname() . PHP_EOL;
} 

Query made by fetching a customer collection

Query is made with a first query which fetch matching entities:

SELECT `e`.* FROM `customer_entity` AS `e` WHERE (e.entity_type_id = '1');

customer data are hydrated by a dedicated query to fetch attributes:

SELECT 
     `customer_entity_datetime`.`entity_id`, 
     `customer_entity_datetime`.`attribute_id`, 
     `customer_entity_datetime`.`value` 
FROM `customer_entity_datetime` 
WHERE (entity_type_id=1) 
AND (entity_id in (324, 325, 326, 327, 328, 329, 330, 331, 332, 333, 334, 335, 337, 339, 340, 341, 342, 343, 344, 345)) AND (attribute_id in ('11')) 

UNION 

SELECT 
`customer_entity_varchar`.`entity_id`, 
`customer_entity_varchar`.`attribute_id`, 
`customer_entity_varchar`.`value` 
FROM `customer_entity_varchar` 
WHERE (entity_type_id=1) 
AND (entity_id in (324, 325, 326, 327, 328, 329, 330, 331, 332, 333, 334, 335, 337, 339, 340, 341, 342, 343, 344, 345)) AND (attribute_id in ('5', '7'))

Customer collection loaded with addNameToSelect method

It seems that addNameToSelect method change how is builded the query. We can test the expected query with the following test code:

$customerCollection = Mage::getModel('customer/customer')->getCollection();
/* @var $customerCollection Mage_Customer_Model_Entity_Customer_Collection */
$customerCollection->addNameToSelect();
$customerCollection->addAttributeToSelect(array(
    'dob', 'firstname', 'lastname', 'email'
));
foreach ($customerCollection as $customer) {
    /* @var $customer Mage_Customer_Model_Customer */
    echo $customer->getFirstame() . ' ' . $customer->getLastname() . PHP_EOL;
} 

With the same script, we have the following queries mades:

SELECT `e`.*, 
`_table_prefix`.`value` AS `prefix`, 
`_table_firstname`.`value` AS `firstname`, 
`_table_middlename`.`value` AS `middlename`, 
`_table_lastname`.`value` AS `lastname`, 
`_table_suffix`.`value` AS `suffix`, 
CONCAT(IF(_table_prefix.value IS NOT NULL AND _table_prefix.value != "", CONCAT(TRIM(_table_prefix.value)," "), ""),TRIM(_table_firstname.value),IF(_table_middlename.value IS NOT NULL AND _table_middlename.value != "", CONCAT(" ",TRIM(_table_middlename.value)), "")," ",TRIM(_table_lastname.value),IF(_table_suffix.value IS NOT NULL AND _table_suffix.value != "", CONCAT(" ",TRIM(_table_suffix.value)), "")) AS `name` 

FROM `customer_entity` AS `e`
 LEFT JOIN `customer_entity_varchar` AS `_table_prefix` ON (`_table_prefix`.`entity_id` = `e`.`entity_id`) AND (`_table_prefix`.`attribute_id` = '4')
 LEFT JOIN `customer_entity_varchar` AS `_table_firstname` ON (`_table_firstname`.`entity_id` = `e`.`entity_id`) AND (`_table_firstname`.`attribute_id` = '5')
 LEFT JOIN `customer_entity_varchar` AS `_table_middlename` ON (`_table_middlename`.`entity_id` = `e`.`entity_id`) AND (`_table_middlename`.`attribute_id` = '6')
 LEFT JOIN `customer_entity_varchar` AS `_table_lastname` ON (`_table_lastname`.`entity_id` = `e`.`entity_id`) AND (`_table_lastname`.`attribute_id` = '7')
 LEFT JOIN `customer_entity_varchar` AS `_table_suffix` ON (`_table_suffix`.`entity_id` = `e`.`entity_id`) AND (`_table_suffix`.`attribute_id` = '8') WHERE (e.entity_type_id = '1');

SELECT `customer_entity_datetime`.`entity_id`, 
`customer_entity_datetime`.`attribute_id`, 
`customer_entity_datetime`.`value` 
FROM `customer_entity_datetime` 
WHERE (entity_type_id=1) AND (entity_id in (324, 325, 326, 327, 328, 329, 330, 331, 332, 333, 334, 335, 337, 339, 340, 341, 342, 343, 344, 345)) AND (attribute_id in ('11')) 

UNION 

SELECT `customer_entity_varchar`.`entity_id`, 
`customer_entity_varchar`.`attribute_id`, 
`customer_entity_varchar`.`value` 
FROM `customer_entity_varchar` 

WHERE (entity_type_id=1) AND (entity_id in (324, 325, 326, 327, 328, 329, 330, 331, 332, 333, 334, 335, 337, 339, 340, 341, 342, 343, 344, 345)) AND (attribute_id in ('5', '7'));	

In this case, customer name values are fetched two times: one time with a join method, a second time with the attribute query, so we do job two times… 🙁

Conclusion on addNameToSelect method

addNameToSelect should be used only if you do not fetch any data from varchar backend storage. if not, you should not use these method for fetching names attributes because you’ll do the job two times: Perhaps in this case it could be more interesting to build “name” value, based upon on the customer_entity_varchar attribute scan query

This test has been made on 1.6

how to solve Magento message “Your web server is configured incorrectly. As a result, configuration files with sensitive information are accessible from the outside. Please contact your hosting provider.”

If you have this message when you are logged in your back-office, it’s because there is a misconfiguration in your Magento host. There are multiple reasons to have this message, and multiple ways to fix it

Why do I have this message displayed?

Since 1.4.2 version, back-office check from back-office if file app/etc/local.xml can be read through a browser (If the response code is a Apache 200 response code; This control is made by a CURL request on your unsecure_base_url/app/etc/local.xml URI) It’s very important not to be able to read this file from a webbrowser, because it contains your database parameters, cache and session configuration.

All files and folders in app folder are protected by a .htaccess file which denies access from everyone since it browses your Magento directory tree starting from the app folder:

Order deny,allow
Deny from all

this directive is applied starting from .htaccess path and covers also subfolders, and so, local.xml file

Origins of the problem

.htaccess usage can be disturbed for three major reasons:

htaccess file doesn’t exist

For sure, if htaccess file does not exist, there is no restriction on who can read the local.xml file, and so, the security warning is displayed

htaccess files are not the access filename

By default in Apache configuration, the AccessFilename is set to .htaccess. It can be updated with the AccessFilename directive in webserver or virtual host configurations files.

So if value has been updated, .htaccess will not be read, and so ACL is not used

But if this is your study case, you should have more complex problems with your Magento 🙂

Cannot override the directories acccess control list

Apache configuration allows also to define which rules can be overrided in AccessFilename with AllowOverride directive

If you are a hosting provider, perhaps you don’t want that your clients can update in their AccessFilename some security directives. It can be done with the AllowOverride directive

Have a look at the official documentation to see the possible values for this directive

In this case, if AllowOverride directive is not well set, the .htaccess file is read, but the ACL defined is not used, and so, we can have access to your local.xml file

Because this directive is applied only to <Directory;> instruction, you must be able to edit the vhost configuration file to fix this issue. Ask your hosting provider if you cannot

Specifics rewriting rules

For one client, we encounter this case: we have updated some elements of the Magento directory tree and define some specific rewriting rules. local.xml file was not available, but CURL test receive a 200 error code because of a rewriting rule.

In this case, this is the test which is involved, not your security policy

Conclusion

Configuration is checked on the app/etc/local.xml file, but other sensibles informations can be fetch from your app directory

It’s a shame that everybody doesn’t take care of this security issue. Take a look at the google results, you’ll be surprised

If your configuration is well set, when you request the app/etc/local.xml file on your Magento, you should have the following error displayed

Magento2 source code available on github

Last December 3, Yoav, CTO at Magento posted this tweet
“Magento 2 source code now available on Github https://github.com/magento/magento2”

yes, now you can download the source code of Magento2 on github.

This is a development release, but we can start with these source code to see (and imagine) what we can found in.

first impress:

  • Seems faster
  • Bootstrap seems updated
  • Some web folders has been moved
  • Greetings to the commons embedded translations
  • A new interface to update our local copy
  • Seems always locked to default channels
  • ….

Wait and see, and enjoy download 🙂