Support for "BETWEEN" condition or raw query support inside where clause

[saptarshimondal]

Hello ClanCats, first of all thank you for this wonderful library.

I was wondering how can I write "BETWEEN" condition using this library. I have tried this code - $q->where(new Expression('start_time BETWEEN "'.$_POST['start_time'].'" AND "'.$_POST['end_time'].'" '));

But it is generating the query like this - start_time BETWEEN "<start_time>" AND "<end_time>" = ?

I just want to know how can I write raw query inside of where clause ? Just like laravel "whereRaw()". If this is not possible please implement it, because it is very important feature for this library. Thanks.

[mario-deluna]

Hi there,

$h
    ->table('posts')
    ->select()
    ->where('start_time', 'between', new Expression('2010-01-01 AND 2020-01-01'))
    ->execute();

Will produce the following between query:

select * from `posts` where `start_time` between 2010-01-01 AND 2020-01-01

The problem is this way you will loose prepared statements and if you use $_POST directly you have a SQL injection vulnerability.

For now i would just use simple where conditions achieving the same result. But I will keep real between support in my mind for v2.

$h
    ->table('posts')
    ->select()
    ->where(function($q) {
      $q->where('start_time', '>', $_POST['start_time']);
      $q->where('start_time', '<', $_POST['end_time']);
    })
    ->execute();

select * from `posts` where ( `start_time` > ? and `start_time` < ? )

[mario-deluna]

Im closing this Issue now. Let me know if the issue persists.

[saptarshimondal]

Hello @mario-deluna,

Thanks for your reply.

I completely understood that it will create a SQL injection vulnerability.

I posted the sample code to let you know that you guys are missing a very important feature. which is raw query support inside where clause. Sometimes it is necessary to write complex logic inside where clause. Right now it is not possible write a logic like - WHERE SUM(price) > ?. This issue could be fixed by adding a functionality like whereRaw('SUM(price) > ?', [100]).

I hope you understood what I am trying to say. Thanks.