CodeIgniter Forums
Gas Orm SQL Query - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: Gas Orm SQL Query (/thread-50640.html)



Gas Orm SQL Query - El Forum - 04-02-2012

[eluser]Unknown[/eluser]
I have a query that is a little too complex to write using the query builder.

Is it possible to run a raw SQL query, and still have it A) protect against SQL injection and B) map properly to the Model?

This is an example of what I'm trying to do:

Code:
<?php namespace Model;

use \Gas\Core;
use \Gas\ORM;

class Title extends ORM {

    public $table = 'movie';

    public $primary_key = 'id';

    function _init()
    {
        // Relationship definition

        // Field definition
        self::$fields = array(
            'id' => ORM::field('auto'),
            'name' => ORM::field('char[255]'),
            'year' => ORM::field('char[4]'),
            'created' => ORM::field('int[11]'),
            'modified' => ORM::field('int[11]'),
        );
    }

    public function search($command) {
        return self::query("select * from movie where title like '%".$command['term']."%' limit 10");
    }
}

Thanks! Smile


Gas Orm SQL Query - El Forum - 04-07-2012

[eluser]toopay[/eluser]
@richardhoppes,

Sorry for late reply, did not notice this post since everyone ask about this ORM in this thread (that will notice me via email when someone post in that thread).

[quote author="richardhoppes" date="1333422874"]I have a query that is a little too complex to write using the query builder.

Is it possible to run a raw SQL query, and still have it A) protect against SQL injection and B) map properly to the Model?[/quote]

Generally, if you use CI query builder it automatically escaping your query string, but you also need to sanitize the input (by enable xss protection, etc) to ensure your data not vulnerable against other attack method.

You can map your query result into Gas record container, like :
Code:
public function search($keyword)
{
   $result = array();
   $query = $this->query("SELECT * FROM $this->table WHERE title LIKE '%$keyword%' LIMIT 10");

   foreach ($query->result_array() as $item)
   {
      $result[] = new static($item);
   }

   return $result;
}
Then from your controller you can use it :
Code:
$search = Model\Movie::make()->search('tron');
echo 'Total match movies :'.count($search);

foreach ($search as $movie)
{
   echo $movie->title . "\n";
}