CodeIgniter Forums
query binding error - 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: query binding error (/showthread.php?tid=2399)

query binding error - El Forum - 08-02-2007

[eluser]EugeneS[/eluser]
it seems to me a bug

when i send through the form this data for example 435345\' i receive wrong SQL query.

my model query:

Code:
$sql_item = '
                    UPDATE Item
                    SET Articul = ?,
                        Name = ?,
                        Price = ?,
                        isNew = ?,
                        isLeader = ?,
                        Description = ?,
                        whenAdded = ?,
                        whenNewFinish = ?,
                        isVisible = ?,
                        Image = ?
                    WHERE ItemID = ?
        ';
        $this->db->query($sql_item, array(
                                              $data['Articul'],
                                            $data['Name'],
                                            $data['Price'],
                                              $data['isNew'],
                                            $data['isLeader'],
                                            $data['Description'],
                                              $data['whenAdded'],
                                            $data['whenNewFinish'],
                                            $data['isVisible'],
                                            $data['Image'],
                                            $data['ItemID'],
                                    )
        );


wrong final query:

Code:
UPDATE Item SET Articul = '435435', Name = '435345\\'', Price = '435435', isNew = '1', isLeader = 0, Description = '345435435', whenAdded = 1186074782, whenNewFinish = 1182286800, isVisible = '1', Image = '' WHERE ItemID = '3'


query binding error - El Forum - 08-02-2007

[eluser]smith[/eluser]
Have you tried this:
Code:
$this->db->query($sql_item, array(
                                             $this->db->escape($data['Articul']),
                                            $this->db->escape($data['Name']),
                                            $this->db->escape($data['Price']),
                                            $this->db->escape($data['isNew']),
                                            $this->db->escape($data['isLeader']),
                                            $this->db->escape($data['Description']),
                                            $this->db->escape($data['whenAdded']),
                                            $this->db->escape($data['whenNewFinish']),
                                            $this->db->escape($data['isVisible']),
                                            $this->db->escape($data['Image']),
                                            $this->db->escape($data['ItemID']),
                                    )
        );

As far as i can see you are sending data from the form without sanitizing? Maybe you are getting wrong results because of that. Sorry, i don't have more data to tell you anything else...

query binding error - El Forum - 08-03-2007

[eluser]EugeneS[/eluser]
you kidding ? sure i can do that .... BUT
extraction from the doc:

The secondary benefit of using binds is that the values are automatically escaped, producing safer queries. You don't have to remember to manually escape data; the engine does it automatically for you.

query binding error - El Forum - 08-03-2007

[eluser]smith[/eluser]
Yes, my fault Sad Sorry

But, can you print all the values of $data before executing the query.

Code:
print "<pre>";
print_r ($data);
print "</pre>";

Then you can compare the query with actual values, and we can do that too.

query binding error - El Forum - 08-03-2007

[eluser]EugeneS[/eluser]
[quote author="smith" date="1186162705"]Yes, my fault Sad Sorry

But, can you print all the values of $data before executing the query.

Code:
print "<pre>";
print_r ($data);
print "</pre>";

Then you can compare the query with actual values, and we can do that too.[/quote]

you can see this problem by adding in the text field \'

query binding error - El Forum - 08-03-2007

[eluser]Rick Jolly[/eluser]
This is the expected behaviour.

Input:
\'
Escaped data in database:
\\'
Select query result:
\'

I don't understand why you would expect something different.

query binding error - El Forum - 08-03-2007

[eluser]EugeneS[/eluser]
[quote author="Rick Jolly" date="1186201175"]This is the expected behaviour.

Input:
\'
Escaped data in database:
\\'
Select query result:
\'

I don't understand why you would expect something different.[/quote]


ohhhh really ? Big Grin so if i enter in the form slash and ' i should see sql query error ? or i should disable these simbols ? may be i must "pre encode" them ?


in my opinion if CI framework and it is supposed to quote data it should quote this in a right way .... if framework cant do this i can do this manually without special framework functions which are useless.

query binding error - El Forum - 08-03-2007

[eluser]Rick Jolly[/eluser]
[quote author="EugeneS" date="1186203010"]
so if i enter in the form slash and ' i should see sql query error?
[/quote]
There shouldn't be an error. What error are you get or expect?

[quote author="EugeneS" date="1186203010"]
in my opinion if CI framework and it is supposed to quote data it should quote this in a right way...[/quote]
CI does quote it in the correct way - it simply uses the database specific escape function.

You haven't shown any error or explained the behavior you expect.

query binding error - El Forum - 08-04-2007

[eluser]EugeneS[/eluser]
what i expect is \' to be correctly inserted into the database by meaning of CI binds .... thats all i want. in other case CI binds are useless if i should handle all the non standard situations by myself .... or these "benefits" should be removed from the documentation if CI cant quote this in a correct way ....
then CI binds will have only one purpose "code lookness" but not

The secondary benefit of using binds is that the values are automatically escaped, producing safer queries. You don’t have to remember to manually escape data; the engine does it automatically for you.


i do not see SAFER QUERIES i see ERROR and i have to remember TO MANUALLY ESCAPE AND PREPARE DATA TO THE DATABASE

query binding error - El Forum - 08-24-2007

[eluser]EugeneS[/eluser]
so another thing is if from the form script receive ? (when binding enabled) script return sql error.

so at the moment (until this be fixed in official release) i've made kind of patch for myself.

add in the system/database/db_driver.php
Code:
function htmlschars($var)
    {
        if (is_array($var))
            foreach($var as $k => $v)
                $var[$k] = $this->htmlschars($v);
        else
        {
            $var = htmlspecialchars($var, ENT_QUOTES);
            $var = str_replace(array('\\', $this->bind_marker), array('\', ord($this->bind_marker)), $var);
        }

        return $var;
    }

and change the compile_binds method by adding $binds = $this->htmlschars($binds);

Code:
function compile_binds($sql, $binds)
    {    
        if (FALSE === strpos($sql, $this->bind_marker))
        {
            return $sql;
        }
        
        if ( ! is_array($binds))
        {
            $binds = array($binds);
        }

        $binds = $this->htmlschars($binds); // prepare special characters

        foreach ($binds as $val)
        {
            $val = $this->escape($val);
                    
            // Just in case the replacement string contains the bind
            // character we'll temporarily replace it with a marker
            $val = str_replace($this->bind_marker, '{&#xb;i;nd_marker%}', $val);
            $sql = preg_replace("#".preg_quote($this->bind_marker, '#')."#", str_replace('$', '\$', $val), $sql, 1);
        }

        return str_replace('{&#xb;i;nd_marker%}', $this->bind_marker, $sql);        
    }