• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
MSSQL

#1
[eluser]polaris1927[/eluser]
i have noted that in version 1.6.1 the mssql_driver module substitues single quote marks with double quotes after the FROM statement. For example:

Code:
$query = $this->db->get('business_names');


will generate a query string like this:
Code:
SELECT * FROM business_names
--- vesion 1.6.0

However the same code will generate the following:

Code:
SELECT * FROM "business_names"
--- version 1.6.1

I know what the issue is but cannot track it down inside the relevant modules.

Has anyone noticed this error and found a workaround?

Thanks.
JG

#2
[eluser]polaris1927[/eluser]
Discovered the cause.

Version 1.6.0, Module: mssql_driver
Code:
function _protect_identifiers($item, $affect_spaces = TRUE, $first_word_only = FALSE)
{
    // MSSQL doesn't use backticks
    return $item;
}

Was replaced in Version 1.6.1 with:

Code:
function _protect_identifiers($item, $first_word_only = FALSE)
{
    
    if (is_array($item))
    {    
        $escaped_array = array();

        foreach($item as $k=>$v)
        {
            $escaped_array[$this->_protect_identifiers($k)] = $this->_protect_identifiers($v, $first_word_only);
        }

        return $escaped_array;
    }    

    // This function may get "item1 item2" as a string, and so
    // we may need ""item1" "item2"" and not ""item1 item2""
    if (ctype_alnum($item) === FALSE)
    {
        if (strpos($item, '.') !== FALSE)
        {
            $aliased_tables = implode(".",$this->ar_aliased_tables).'.';
            $table_name =  substr($item, 0, strpos($item, '.')+1);
            $item = (strpos($aliased_tables, $table_name) !== FALSE) ? $item = $item : $this->dbprefix.$item;
        }

        // This function may get "field >= 1", and need it to return ""field" >= 1"
        $lbound = ($first_word_only === TRUE) ? '' : '|\s|\(';

        $item;// = preg_replace('/(^'.$lbound.')([\w\d\-\_]+?)(\s|\)|$)/iS', '$1"$2"$3', $item);
    }
    else
    {
        
        return "\"{$item}\"";
    }

    $exceptions = array('AS', '/', '-', '%', '+', '*');
        
    foreach ($exceptions as $exception)
    {
        
        if (stristr($item, " \"{$exception}\" ") !== FALSE)
        {
            $item = preg_replace('/ "('.preg_quote($exception).')" /i', ' $1 ', $item);
        }
    }
    return $item;
}

For a quick fix i reverted to the 1.6.0 code.

JC

#3
[eluser]mr.zeno[/eluser]
Hi to all, I am new to the CodeIgniter community (actually doing my first php project since *ages*, been doing a lot of java, c# and other stuff).



I had the same problem, the 'fix' polaris posted works with me. I do wonder however how this might affect other procedures in the mssql_driver..

Anyone who can tell me more about that?

#4
[eluser]Pygon[/eluser]
only one line is the cause -- a search of this forum or the bug report forum or the bug system would have given you the answer.

#5
[eluser]mr.zeno[/eluser]
A search in this forum brought me to this thread Wink

I'll checkout the bug-system then..

#6
[eluser]mr.zeno[/eluser]
Jup! it was in there:
http://codeigniter.com/bug_tracker/bug/3472/

the fix is posted here (confirmed by administrator):
http://ellislab.com/forums/viewthread/71558/#353513

#7
[eluser]polaris1927[/eluser]
[quote author="mr.zeno" date="1205456000"]Jup! it was in there:
http://codeigniter.com/bug_tracker/bug/3472/

the fix is posted here (confirmed by administrator):
http://ellislab.com/forums/viewthread/71558/#353513[/quote]

This fix did not work for me.

#8
[eluser]polaris1927[/eluser]
With the posted fix I have two generated queries having different syntax:

The following ws generated from the profiler:

Code:
0.0011   SELECT * FROM business_numbers WHERE name_id = '2'
0.0160   SELECT * FROM business_names WHERE "name" = '2'

The PHP code that was used is shown below:

Code:
$data['phonenumbers'] = $this->db->get_where('business_numbers', array('name_id' => $contact_id) );
            
$data['name'] = $this->db->get_where('business_names', array('id' => $contact_id) );

Not much difference, but I still get the "" (double quotes) shown for "id", which seems strange.

Can anyone verify what I have found?

#9
[eluser]polaris1927[/eluser]
In module mssql_driver.php, where you see the line (near line 467)

Code:
return "\"{$item}\"";

replace with

Code:
return $item;

#10
[eluser]mr.zeno[/eluser]
Too bad this 'bug' still exists as of version 1.6.3 Sad


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.