Query Builder escape function bug #1789

Closed
keatliang2005 opened this Issue Sep 12, 2012 · 12 comments

Projects

None yet

4 participants

@keatliang2005

On MySQL and MySQLi driver

system/database/drivers/mysqli/mysqli_driver.php
system/database/drivers/mysql/mysql_driver.php

on the method escape_str

/**
     * Escape String
     *
     * @param   string
     * @param   bool    whether or not the string will be used in a LIKE condition
     * @return  string
     */
    public 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 = is_object($this->conn_id) ? $this->conn_id->real_escape_string($str) : addslashes($str);

        // escape LIKE condition wildcards
        if ($like === TRUE)
        {
            return 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;
    }

this cause single quote input into function

$this->db->like() or $this->db->escape_like_str(" ' ", TRUE);

Produce unwanted error


$this->db->like('field', "   '  ");

# suppose become
# \'

# instead
# \\'
@kutothe
kutothe commented Sep 18, 2012

I'm also having this issue, if I do:

$this->db->like('last_name', "o'brien");

I get:
``last_nameLIKE '%o\\'brien%'

@keatliang2005

how's the status of this bug ?

@keatliang2005 keatliang2005 reopened this Oct 2, 2012
@narfbg
Contributor
narfbg commented Oct 2, 2012

I'm thinking about changing the escape character via LIKE '<expr>' ESCAPE '<char>' syntax (if supported by MySQL, but I doubt that it's not). Any other ideas?

@kutothe
kutothe commented Oct 3, 2012

I was hoping it was just a bug in the escaping code for "like", but I didn't look into the underlying code yet. :)

@narfbg narfbg added a commit that referenced this issue Oct 4, 2012
@narfbg narfbg Fix issue #1789
Signed-off-by: Andrey Andreev <narf@bofh.bg>
2ea33c3
@narfbg narfbg closed this Oct 4, 2012
@kutothe
kutothe commented Oct 4, 2012

Fix appears to be working great. As always, thank you!

@keatliang2005

awesome fix !

@nonchip nonchip pushed a commit to nonchip/CodeIgniter that referenced this issue Jun 29, 2013
@narfbg narfbg Fix issue #1789
Signed-off-by: Andrey Andreev <narf@bofh.bg>
e273dc5
@QuakePhil

Whatever happened to this fix?

I'm looking in https://github.com/bcit-ci/CodeIgniter/blob/master/system/database/drivers/mysqli/mysqli_driver.php and it doesn't set _like_escape_chr to "" anywhere (like the fix does) and so the value remains "!" from DB_driver.php, generating incorrect queries

@narfbg

@narfbg
Contributor
narfbg commented Mar 7, 2016

I don't understand why you think "the fix" sets \ as the escape character - it never did - nor what you mean by incorrect queries.

@QuakePhil

@narfbg
I was mistaken. The "fix" you link above actually removes the correct values of _like_escape_chr from the various database drivers.

The end result is that CI is unable to form queries properly that use the like operand, and which contain terms with underscores.

For example, it generates this query: SELECT * FROM a WHERE b like '%first!_last%'

When I try to run this query in mysql, it breaks.

The correctly formed query follows: SELECT * FROM a WHERE b like '%first_last%' --to find entry "first_last"

@narfbg
Contributor
narfbg commented Mar 7, 2016

CI doesn't generate such queries, it puts ESCAPE '!' at the end.

86d2ec4

@QuakePhil

Ah, in that case it must have been fixed in the most recent version, as I can only confirm this issue in 3.0.1 and am currently unable to upgrade to test. Sorry for the confusion

@narfbg
Contributor
narfbg commented Mar 8, 2016

It's not ... you're confusing what CI generates (which happens via the QB like() method) and what escape_like_str() returns when used on its own.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment