codeigniter escaping queries

Question

What's up with this logic:

I have a book title called "Day and Night"

$this->db->select('b.Book_Name,Author');
$this->db->from('Books b');
$this->db->where('Book_Name',$book['Book_Name']);
$query = $this->db->get();

if($query->num_rows() > 0 ) 
    {
     $arr['by_genre'] = $query->result_array();
    }

0 results,

however if I change the name to "Day an Night" I get

  ['Book_Name'=>'Day an Night', 
     'Author'=>'Gene Night',  
     'ISBN'=> '124BA123', ..]

so my question is how do i tell my query to escape the "and" treating it as a string and not a condition.

And what is $book['Book_Name'] ? Are you sure that its correct and set — Svetoslav, Nov 25 '15 at 15:32
I'd use a parameterized query. I haven't worked with codeigniter but here's a thread, http://stackoverflow.com/questions/1615792/does-codeigniter-automatically-prevent-sql-injection. — chris85, Nov 25 '15 at 15:37
@chris85 doesn't using the active record class (as i'm doing) automatically escape my queries? — Edward, Nov 25 '15 at 15:44
I don't know as I stated I don't (and havent ever) used code igniter. I would use a parameterized query though because then you dont have to worry about escaping. — chris85, Nov 25 '15 at 15:46
@chris85 that worked but im pretty sure that is not the intended result of the active record class. — Edward, Nov 25 '15 at 15:47
Can't offer any advise on that sorry; I think that'd be a separate question, or update this one to be more specific to that. — chris85, Nov 25 '15 at 15:50

score 2 · Accepted Answer · answered Nov 25 '15 at 15:58

2

This is a bug in CodeIgniter 3.0.0 and 3.0.1. It was fixed in version 3.0.2, and the current stable version is 3.0.3 ...

All you need to do is update your CI setup.

answered Nov 25 '15 at 15:58

Narf

14,600
3
37
66

score 0 · Answer 2 · answered Nov 25 '15 at 15:50

Problem with active record class or intended use?

Below query solves the problem: (allows and in the book name)

$sql = "SELECT b.Book_Name,author
        FROM Books b WHERE b.CARD_NAME = ?";

$query = $this->db->query($sql, array($book['Book_Name']));

Vs: does not allow and in the book name.

$this->db->select('b.Book_Name,Author');
$this->db->from('Books b');
$this->db->where('Book_Name',$book['Book_Name']);
$query = $this->db->get();

It's called "Query Builder" in CI3, not active record. – Narf Nov 25 '15 at 15:59 — Narf, Nov 25 '15 at 15:59

codeigniter escaping queries

2 Answers2