codeigniter best DB query method & Injection prevention

I am not sure which is the best method to run a DB query ( or what the difference is for that matter ) between :

$this->db
 ->select('*')
 ->from('table');
$this->db->get();

and

$this->db->query($sql);

I found that i need the get() class because i need to have paginat开发者_Python百科ion and that is needed for limiters....but maybe the other one allows for pagination too?

the second part about SQL injections is this good enough? if not what is?

  $this->db
     ->select('*')
     ->from('table');
    $this->db->get();
 ->where('id >'.$this->db->escape(1));

thank you.

---

The following code implements the ActiveRecord library in CodeIgniter (though ActiveRecord usually refers to a fairly different way of getting values from a database).

$this->db
->select('*')
->from('table');
$this->db->get();

Right until the get() method, ActiveRecord will build and store an SQL query internally, and then, when you call get(), execute that query and replace it with a result object, which you can then access with result(), result_array(), row(), or row_array().

The following code will execute an SQL query directly from the $sql argument you give it

$this->db->query($sql);

From that point, if you were SELECTing data from your database, you can do this:

$result = $this->db->result_array();

to get the results of that query into an array.

For pagination, you can use both ways. This, for example, would get the records for a page 3 if you had 10 records per page:

$query = $this->db->select('*')->from('table')->limit(10, 30)->get();
if($query->num_rows())
{
    return $query->result_array();
}

return FALSE;

And, if you're using any of the $this->db methods() (where()), CodeIgniter will automatically escape the values you put into it:

$this->db->where('id', "'i am a nasty piece of SQL';DROP DATABASE 'my_db'");

That would be fine - CodeIgniter takes care of it for you. Just always be aware that just because it's done for you, doesn't mean there aren't other ways to expose security vulnerabilities in your code.

---

So most frameworks that implement a Data Access layer and/or ORM use a prepared statement. For most simple queries if you use this interface the DAL/ORM will handle escaping for you. For example:

$this->db->get_where('mytable', array('id' => $id), $limit, $offset);

Using this method it will bind the variables in the array (array('column_name' => volumn_value')) argument to the query and perform escaping on them.

Or using your example:

  $this->db
    ->select('*')
    ->from('table')
    ->where('id ', 1)->get();

check out the docs they should help explain in detail: http://codeigniter.com/user_guide/database/active_record.html