FuelPHP Forums

you

I'd like to make a SQL like following.

SELECT * FROM `a_table`

JOIN `b_table` ON (`a_table`.`user_id` = `b_table`.`id` AND (`view` = TRUE OR `lifetime` > '2017-01-31'))

Then I wrote like this.

DB::select()

->from('a_table')

->join('b_table')

->on('a_table.user_id','=','b_table.id')

->and_on('view','=',DB::expr(true))

->or_on('lifetime','>',DB::expr('2017-01-31'));

That makes following SQL.

SELECT * FROM `a_table`

JOIN `b_table` ON (`a_table`.`user_id` = `b_table`.`id` AND `view` = TRUE OR `lifetime` > '2017-01-31')

I need brackets but I can not find how to insert brackets into ON phrase.

Please tell me how to insert brackets.

Harro

This type of join is currently not supported in the Query builder.

You may wish to create an issue for this at https://github.com/fuel/core/issues.

hemor

I also had a problem using join so I used DB::query('your raw sql')->execute(); Since you already know the sql it should be straight-forward to use.

Harro

Possible, but make sure your SQL is secure if you use variables in it.

hemor

How?

Normally I use \Security::xss_clean(strip_tags('input field')); Is that secure enough?

Harro

That won't protect you against SQL injection, that only strips out stuff that could be harmful if you write that input value back to the browser. Input like " ' OR UNION ( ...) # " is perfectly valid string input, no XSS attach in it.

In general, it isn't even needed because Fuel escapes on output, so as long as you haven't disabled that, rubbish coming in is not harmful.

If you have handcoded SQL, use \DB::escape() on your variables.

you

Thank you, Harro.

I am going to fix core little to support this type of JOIN.

Harro

You don't have to, we're already working on it.

Harro

Solution committed to 1.9-dev.

        $db = \DB::select()
            ->from('a_table')
            ->join('b_table')
            ->on('a_table.user_id','=','b_table.id')
            ->on_open()
                ->on('view','=',DB::expr(true))
                ->or_on('lifetime','>',DB::expr('2017-01-31'))
            ->on_close();

generates

"SELECT * FROM `a_table` JOIN `b_table` ON (`a_table`.`user_id` = `b_table`.`id` AND (`view` = 1 OR `lifetime` > 2017-01-31))"

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.

Howdy, Stranger!

Categories

In this Discussion