[SOLUTION] Problem in OCI8 DB driver

[eluser]Kobus M[/eluser]
Hi,

The following problem applies to CodeIgniter 2.0.0 (and maybe also earlier versions).

We have discovered that the escape_str function of the OCI8 DB driver that does not escape single quotes. Oracle escapes a single quote with another single quote.

We have therefore changed the escape_str function in the OCI8 DB driver by adding the following just after the remove_invisible_characters function call.

$str = str_replace("'", "''", $str);

This solves the problem we've had with escaping for us, but there may be better solutions.

Thanks for the great work on CI! Keep it up!

Kobus

[eluser]Kobus M[/eluser]
The entire changed function now looks like this:

Code:

/**
     * Escape String
     *
     * @access  public
     * @param   string
     * @param    bool    whether or not the string will be used in a LIKE condition
     * @return  string
     */
    function escape_str($str, $like = FALSE)
    {
        if (is_array($str))
        {
            foreach($str as $key => $val)
            {
                $str[$key] = $this->escape_str($val, $like);
            }

            return $str;
        }

        $str = remove_invisible_characters($str);
        $str = str_replace("'", "''", $str);  

        // escape LIKE condition wildcards
        if ($like === TRUE)
        {
            $str = str_replace(    array('%', '_', $this->_like_escape_chr),
                                array($this->_like_escape_chr.'%', $this->_like_escape_chr.'_', $this->_like_escape_chr.$this->_like_escape_chr),
                                $str);
        }

        return $str;
    }

[eluser]tedroche[/eluser]
[quote author="Kobus M" date="1296759976"]

Code:

$str = remove_invisible_characters($str);
        $str = str_replace("'", "''", $str);

[/quote]

I added the same change to the ODBC driver, as I'm using it to write to SQL Server and seeing the same problem with unescaped single-quotes throwing syntax errors.

[eluser]yrachmanu[/eluser]
dear kobus,
after follow your instruction , my oracle connection problem still occur with different condition for each browser :
1. blank page , Firefox
2. Error 324 (net::ERR_EMPTY_RESPONSE): Unknown error , google chrome
3. page not respond , IE

Here my database setting :
db['default']['hostname'] = '//[ip host]:[port]/[service name]';
$db['default']['username'] = '[my user oracle]';
$db['default']['password'] = '[my user password]';
$db['default']['database'] = '';
$db['default']['dbdriver'] = 'oci8';
$db['default']['dbprefix'] = '';
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = '';
$db['default']['char_set'] = 'utf8';
$db['default']['dbcollat'] = 'utf8_general_ci';
$db['default']['swap_pre'] = '';
$db['default']['autoinit'] = TRUE;
$db['default']['stricton'] = FALSE;

is there any solution about my case ??

regards,
yudi

[eluser]Kobus M[/eluser]
[quote author="yrachmanu" date="1298281805"]dear kobus,
after follow your instruction , my oracle connection problem still occur with different condition for each browser :
1. blank page , Firefox
2. Error 324 (net::ERR_EMPTY_RESPONSE): Unknown error , google chrome
3. page not respond , IE

Here my database setting :
db['default']['hostname'] = '//[ip host]:[port]/[service name]';
$db['default']['username'] = '[my user oracle]';
$db['default']['password'] = '[my user password]';
$db['default']['database'] = '';
$db['default']['dbdriver'] = 'oci8';
$db['default']['dbprefix'] = '';
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = '';
$db['default']['char_set'] = 'utf8';
$db['default']['dbcollat'] = 'utf8_general_ci';
$db['default']['swap_pre'] = '';
$db['default']['autoinit'] = TRUE;
$db['default']['stricton'] = FALSE;

is there any solution about my case ??

regards,
yudi[/quote]

Dear Yudi,

Your problem seems to be totally unrelated to the one I had. I suggest you open a new topic with your question. One thing I see wrong in your config is that you have no database specified, but I doubt that this is the cause of your problem. If your database is provided and the password, connection, etc. is correct, I could only think that this is a coding related issue elsewhere. I'd start by setting new new homepage that simply does a database query and nothing else, and then set it back to the default in routes.php.

Hope this helps. I am no expert with CI - just accidentally figured this one out when I realized that the queries were not escaped in Oracle.

Good luck!