How to write complex SQL and avoid sql injection?

For example, a complex SQL like

select * from table_a
    inner join table_b on table_a.id = table_b.id
    inner join table_c on table_b.id = table_c.id
    where table_c.id = $var
    group by table_a.id

how to avoid sql injection?

Hi @xaero you have to use bind parameters

Good luck

Hello, as Emilio suggested you have to use bind parameters. Here is a very simple example:

public function getId($slug)
{
    $lang = $this->getDI()->getSession()->language ?? $this->getDI()->getConfig()->site->defaultLanguage;
    $cacheFile = 'cruises-one-'. md5($slug . $lang);
    $item = $this->modelsManager->createBuilder()
        ->columns(['foreign_key AS id'])
        ->from('Models\CruisesI18n')
        ->where('is_active = 1')
        ->andWhere('lang = :lang:', ['lang' => $lang])
        ->andWhere('slug = :slug:', ['slug' => $slug])
        ->getQuery()->cache(['key' => $cacheFile])->getSingleResult();
    return $item->id ?? false;
}


9.3k
edited Aug '17

Thanks all !

In my project, I write the raw sql in models, how to write the phql with binding parameters in the static function in models?(not in controllers)

Here is a demo or my raw sql in the models:

public static function findAllTopicsBySql()
{
    $sql = "SELECT * FROM `topics`";

    $topics = new Topics();

    return new Resultset(null, $topics, $topics->getReadConnection()->query($sql));
}
edited Aug '17

See examples here, again binding parameters: https://olddocs.phalconphp.com/en/3.0.3/api/Phalcon_Db_Adapter_Pdo.html

$sql = "SELECT * FROMtopics"; Why not use Topics::find() ? No really point doing such simple raw queries really.



9.3k

Yes, but that's just an example, my raw queriers will be more complex....

And I want to know how to excute this raw querier or phql in a static function of the MODEL?

$sql = "SELECT * FROMtopics"; Why not use Topics::find() ? No really point doing such simple raw queries really.

Not possible, model::methods always use phql. Well you can always your own methods to model.