Tuesday 18 January 2011 11:34:05 am

Hi Henrik,
First, thanks for the contribution, you're right it's a quite common need. But your solution needs some fixes at least for security and performances.
Security issue :
Your eZ JS Core server function is vulnerable to SQL injection because you don't escape parameters. In the eZ Publish API, it should be done with eZDB::escapeString() :

<?php
 
class completeCityFunction extends ezjscServerFunctions
{
    public static function searchCities($args)
    {
        $query = '';
        $db = eZDB::instance(); // & is useless in PHP5
 
        $http = eZHTTPTool::instance();
        $query="select distinct(comune) from comuni
                       where comune like '" . $db->escapeString( trim( $http->getVariable( 'q' ) ) ) . "%'
                       and pid ='" . $db->escapeString( $http->getVariable( 'province' ) ) . "'";
 
        $result = $db->arrayQuery($query);
 
        return $result;
        // var_dump($result);
    }
}

Performances

Your tables miss some indexes. At least, the table comuni misses an index on the fields provincia and pid that could be created with the following SQL query :

CREATE INDEX comuni_provincia_pid ON comuni (pid, comune)

Hope that helps. Cheers

Damien
Planet eZ Publish.fr : http://www.planet-ezpublish.fr
Certification : http://auth.ez.no/certification/verify/372448
Publications about eZ Publish : http://pwet.fr/tags/keywords/weblog/ez_publish

Wednesday 19 January 2011 1:01:57 am

Excellent insight on integration of external tables !

Thanks for this contribution Henrik !

--
Nicolas Pastorino
Director Community - eZ
Member of the Community Project Board

eZ Publish Community on twitter: http://twitter.com/ezcommunity

t : http://twitter.com/jeanvoye
G+ : http://plus.tl/jeanvoye

Wednesday 16 February 2011 7:14:42 am

Henrik, I believe that using a class that extends eZPersistentObject instead of calling a raw sql query, would be a better and more "eZ like" implementation.

But this could also be material for a complete new tutorial...

--
Nothing is impossible. Not if you can imagine it!

Hubert Farnsworth

Monday 21 February 2011 9:57:26 am

Thiago wrote a good intro to eZPersistentObject here:

http://share.ez.no/learn/ez-publish/a-quick-and-friendly-introduction-to-ezpersistentobject

http://www.mugo.ca
Mugo Web, eZ Partner in Vancouver, Canada