2

I'm using Google API to generate store locator in my module. I've stored all the store addressed in a separate table and accessing that table through direct SQL query to match locations inputted by user from frontend. Here's my query:

$resource = Mage::getSingleton('core/resource');      
$readConnection = $resource->getConnection('core_read');         
$query = sprintf("SELECT address, name, lat, lng, ( 3959 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance FROM ".$resource->getTableName('onibi_storelocator/store')." HAVING distance < '%s' ORDER BY distance LIMIT 0 , 20",
      mysql_real_escape_string($center_lat),
      mysql_real_escape_string($center_lng),
      mysql_real_escape_string($center_lat),
      mysql_real_escape_string($radius));       

$results = $readConnection->fetchAll($query);

This code works alright in my localhost but when run it on development server it generates error like this:

Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
  • 1
    Your mysql database is down :) – Fabian Blechschmidt Jul 17 '13 at 7:15
  • The rest of the store is working alright so the database is getting accessed by the store. There seems to be some specific problem with this only. – jdhaar Jul 17 '13 at 7:26
  • the rest runs through a PDO connection, how do you fire the query? – Fabian Blechschmidt Jul 17 '13 at 7:35
  • I have edited the code to include how i am firing the query. – jdhaar Jul 17 '13 at 7:46
5

mysql_real_escape_string needs a database connection opened with mysql_connect to retrieve the current MySQL charset and properly escape the input based on this charset.

Magento uses PDO, therefore you can't use mysql_real_escape_string.

Take a look on how Magento works with the Database, as a hint:

$readConnection->fetchAll('SELECT foo FROM bar WHERE a = ?', array($some_var));

PDO escapes everything correct and helps you to avoid security issues.

Please also consult the PHP manual: http://www.php.net/manual/de/pdostatement.fetchall.php

  • using the method of $readConnection->fetchAllreturns nothing from database. – jdhaar Jul 17 '13 at 9:42
  • Is the SQL-Code correct? $readConnection->fetchAll() is actually Zend_Db_Adapter_Abstract::fetchAll(). Varien_Db_Adapter_Pdo_Mysql::query() and Varien_Db_Adapter_Pdo_Mysql::_prepareQuery() take care of the bind-parameters. – thebod Jul 17 '13 at 13:24
2

I got the solution if anyone is interested. The SQL is supposed to be written in a different manner. Here is what worked for me:

// Search the rows in the markers table
$select = $readConnection->select()
    ->from(
        array('os' => $resource->getTableName('your_table')),
        array(
            'address', 'name', 'lat', 'lng',
            'distance' => new Zend_Db_Expr("( 3959 * acos( cos( radians({$center_lat}) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians({$center_lng}) ) + sin( radians({$center_lat}) ) * sin( radians( lat ) ) ) )")
        )
    )
    ->having("distance < ?", $radius)
    ->limit(20)
    ->order('distance');

$results = $readConnection->fetchAll($select);
  • 1
    You don't escape the input in from(), so this might be vulnerable to MySQL Injection... – thebod Jul 18 '13 at 8:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.