2

i'm developing a joomla component to read some data from a postgres database. Joomla is installed on mysql. In the model i have

protected function getListQuery()
{
    $option = array();                    //prevent problems
    $option['driver']   = 'postgresql';   // Database driver name
    $option['host']     = '192.168.1.0';  // Database host name:port number
    $option['port']     = '1111';
    $option['user']     = 'user';         // User for database authentication
    $option['password'] = 'password';     // Password for database authentication
    $option['database'] = 'PGDB';         // Database name
    $option['prefix']   = '';             // Database prefix (may be empty)

    $dbstock = JDatabaseDriver::getInstance( $option );

    $query = $dbstock->getQuery(true);  
    $query->select('*')->from($dbstock->quoteName('myTable'));

    return $query;
}

I get errors from the view becouse it seems that the dabase server doesn't like quotes in the table name:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near "myTable" LIMIT 20' at line 2 SQL=SELECT * FROM "myTable" LIMIT 20

What can I do?



EDIT

why the hell this works??

<?php


// No direct access to this file
defined('_JEXEC') or die('Restricted access');

class SLCatalogModelProducts extends JModelList
{
public function __construct($config = array())
 {
    parent::__construct($config);

    $option = array(); //prevent problems

    $option['driver']   = 'postgresql';            // Database driver name
            $option['host']     = '192.168.1.1';    // Database host name:port number
            $option['user']     = 'user';       // User for database authentication
            $option['password'] = 'password';   // Password for database authentication
            $option['database'] = 'PGSQL';      // Database name
            $option['prefix']   = '';    

    $db = JDatabaseDriver::getInstance( $option );
    parent::setDbo($db);
 }
/**
 * Method to build an SQL query to load the list data.
 *
 * @return      string  An SQL query
 */
protected function getListQuery()
{

            $option = array();                       //prevent problems
            $option['driver']   = 'postgresql';      // Database driver name
            $option['host']     = '192.168.1.1';    // Database host name:port number
            $option['user']     = 'user';      // User for database authentication
            $option['password'] = 'password';      // Password for database authentication
            $option['database'] = 'PGSQL';        // Database name
            $option['prefix']   = '';                // Database prefix (may be empty)

            $dbstock = JDatabaseDriver::getInstance($option);
    $query = $dbstock->getQuery(true);  
            $query->select('*')
            ->from('myTable');
    return $query;  
}
}

Really don't like this code...but it works :-(

1 Answer 1

0

Change

$query->select('*')->from($dbstock->quoteName('myTable'));

to

$query->select('*')->from('myTable');

This should prevent forcing quotes on the table name.

As stated in the docs,

quoteName
Wrap an SQL statement identifier name such as column, table or database names in quotes to prevent injection risks and reserved word conflicts.

Since your table is not a reserved keyword and you are not processing user input (so eliminating sql injection), you can safely remove that.

Sign up to request clarification or add additional context in comments.

6 Comments

Thank you but this way it seems joomla doesn't get the right database. Here's the error: SELECT command denied to user 'artsite358'@'localhost' for table 'myTable'
Is that username correct? If yes, can you verify it does indeed have the proper rights to connect to the specified database? You can easily verify that by trying to connect using the same credentials. Simplest way to find out is to replace (on your local setup) the user with root
Yes verified user/password by connecting with phpPgAdmin
... and also by writing a simple php page with a query on that db
May be something else though.. "SELECT command denied to user" is a mysql error text. Not postgresql.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.