Database class adding graves ("`") where they don't belong

Database class adding graves ("`") where they don't belong - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Database class adding graves ("`") where they don't belong (/showthread.php?tid=13212)

Database class adding graves ("`") where they don't belong - El Forum - 11-14-2008

[eluser]mattalexx[/eluser]
I have a weird join:

Code:
// ...

$this->db->join('areas', 'IF(properties.ovr_area_id, properties.ovr_area_id, properties.mls_area_id) = areas.id');

// ...

But the Database class/Active Record turns it into this:

Code:
# ...

JOIN `areas` ON `IF`(`properties`.`ovr_area_id,` properties.ovr_area_id, properties.mls_area_id) = areas.id

# ...

No comment on the inefficiency of this SQL is necessary -- I already know. MySQL doesn't mind a join like this though and it should be allowed to work correctly.

Anyone have any ideas?

Database class adding graves ("`") where they don't belong - El Forum - 11-14-2008

[eluser]fesweb[/eluser]
You can make it stop that by passing an extra parameter (FALSE) to the db function.

Example using a different function - in this case, it's the third parameter:

Code:
$where = "LEFT(`p`.`name_last`, 1) <= 'g'";

$this->db->where($where, NULL, FALSE);

Good luck...

Database class adding graves ("`") where they don't belong - El Forum - 11-14-2008

[eluser]mattalexx[/eluser]
[quote author="fesweb" date="1226717587"]You can make it stop that by passing an extra parameter (FALSE) to the db function.

Example using a different function - in this case, it's the third parameter:

Code:
$where = "LEFT(`p`.`name_last`, 1) <= 'g'";

$this->db->where($where, NULL, FALSE);

Good luck...[/quote]

I'm trying to use the join function, not the where function.

I see that the where function has an "escape" parameter:

Code:
// ci_1.7.0/database/DB_active_rec.php, line 354

    function where($key, $value = NULL, $escape = TRUE)

    {

        return $this->_where($key, $value, 'AND ', $escape);

    }

... but the join function does not:

Code:
// ci_1.7.0/database/DB_active_rec.php, line 299

    function join($table, $cond, $type = '')

    {        

        if ($type != '')

        {

            $type = strtoupper(trim($type));

            if ( ! in_array($type, array('LEFT', 'RIGHT', 'OUTER', 'INNER', 'LEFT OUTER', 'RIGHT OUTER')))

            {

                $type = '';

            }

            else

            {

                $type .= ' ';

            }

        }

        // Extract any aliases that might exist.  We use this information

        // in the _protect_identifiers to know whether to add a table prefix 

        $this->_track_aliases($table);

        // Strip apart the condition and protect the identifiers

        if (preg_match('/([\w\.]+)([\W\s]+)(.+)/', $cond, $match))

        {

            $match[1] = $this->_protect_identifiers($match[1]);

            $match[3] = $this->_protect_identifiers($match[3]);

            $cond = $match[1].$match[2].$match[3];        

        }

        // Assemble the JOIN statement

        $join = $type.'JOIN '.$this->_protect_identifiers($table, TRUE, NULL, FALSE).' ON '.$cond;

        $this->ar_join[] = $join;

        if ($this->ar_caching === TRUE)

        {

            $this->ar_cache_join[] = $join;

            $this->ar_cache_exists[] = 'join';

        }

        return $this;

    }

Database class adding graves ("`") where they don't belong - El Forum - 11-14-2008

[eluser]mattalexx[/eluser]
Okay, I ended up extending the Database class and altering the join method a bit.

First, I extended the Loader class (app/libraries/MY_Loader.php)
This is to enable extension of the Database class. I followed this tutorial.

Code:
&lt;?php

class MY_Loader extends CI_Loader {

    function database($params = '', $return = FALSE, $active_record = FALSE)

    {

        // Do we even need to load the database class?

        if (class_exists('CI_DB') AND $return == FALSE AND $active_record == FALSE)

        {

            return FALSE;

        }

        require_once(BASEPATH.'database/DB'.EXT);

        // Load the DB class

        $db =& DB($params, $active_record);

        $my_driver = config_item('subclass_prefix').'DB_'.$db->dbdriver.'_driver';

        $my_driver_file = APPPATH.'libraries/'.$my_driver.EXT;

        if (file_exists($my_driver_file))

        {

            require_once($my_driver_file);

            $db =& new $my_driver(get_object_vars($db));

        }

        if ($return === TRUE)

        {

            return $db;

        }

        // Grab the super object

        $CI =& get_instance();

        // Initialize the db variable.  Needed to prevent

        // reference errors with some configurations

        $CI->db = '';

        $CI->db = $db;

        // Assign the DB object to any existing models

        $this->_ci_assign_to_models();

    }

}

Then, I extended the Database class (app/libraries/MY_DB_mysql_driver.php)
This is so I could add an escape parameter to the join function.

Code:
&lt;?php

class MY_DB_mysql_driver extends CI_DB_mysql_driver {

    function __construct($params){

        parent::__construct($params);

    }

    function join($table, $cond, $type = '', $escape = NULL) {

        // If the escape value was not set will will base it on the global setting

        if ( ! is_bool($escape))

        {

            $escape = $this->_protect_identifiers;

        }

        if ($type != '')

        {

            $type = strtoupper(trim($type));

            if ( ! in_array($type, array('LEFT', 'RIGHT', 'OUTER', 'INNER', 'LEFT OUTER', 'RIGHT OUTER')))

            {

                $type = '';

            }

            else

            {

                $type .= ' ';

            }

        }

        // Extract any aliases that might exist.  We use this information

        // in the _protect_identifiers to know whether to add a table prefix

        $this->_track_aliases($table);

        // Strip apart the condition and protect the identifiers

        if ($escape === false && preg_match('/([\w\.]+)([\W\s]+)(.+)/', $cond, $match))

        {

            $match[1] = $this->_protect_identifiers($match[1]);

            $match[3] = $this->_protect_identifiers($match[3]);

            $cond = $match[1].$match[2].$match[3];

        }

        // Assemble the JOIN statement

        $join = $type.'JOIN '.$this->_protect_identifiers($table, TRUE, NULL, FALSE).' ON '.$cond;

        $this->ar_join[] = $join;

        if ($this->ar_caching === TRUE)

        {

            $this->ar_cache_join[] = $join;

            $this->ar_cache_exists[] = 'join';

        }

        return $this;

    }

}

Core:

Code:
// ...

    function join($table, $cond, $type = '')

    {        

        if ($type != '')

// ...

Extended:

Code:
// ...

    function join($table, $cond, $type = '', $escape = NULL) {

        // If the escape value was not set will will base it on the global setting

        if ( ! is_bool($escape))

        {

            $escape = $this->_protect_identifiers;

        }

        if ($type != '')

// ...

Core:

Code:
// ...

        // Strip apart the condition and protect the identifiers

        if (preg_match('/([\w\.]+)([\W\s]+)(.+)/', $cond, $match))

// ...

Extended:

Code:
// ...

        // Strip apart the condition and protect the identifiers

        if ($escape === false && preg_match('/([\w\.]+)([\W\s]+)(.+)/', $cond, $match))

// ...