Laravel escape character in query builder

Question

I'm coding a query :

$result = \DB::table('order_items as oi')
        ->selectRaw(
            'order_item_type as item_type,
            order_item_id as item_id,
            if(order_item_type = "App\\\Models\\\Book", oi.price, invoices.price) as price
              )

just note to if statement, I'm having to use two escape character or query doesnt match App\Models\Book. when i inspect output query by laravel debugger its :

select order_item_type as item_type,
order_item_id as item_id,
if(order_item_type = "App\\Models\\Book", oi.price, invoices.price) as price,...

whats happen here? does laravel query builder remove one slash and then mysql engine remove the second slash in run time ?

EDIT :
But somewhere else in the same query i have a where clause that i used one escape character and it works fine:

 ->leftjoin('books', function ($q) {
            $q->on('q3.order_item_id', '=', 'books.id')
                ->where('q3.order_item_type', '=', 'App\\Models\\Book');
        })

and the output query part by laravel debugger:

left join `books` on `q3`.`order_item_id` = `books`.`id` and 
`q3`.`order_item_type` = 'App\Models\Book'

Can anyone explain why in if i must use two escape character but in join only one escape character is needed?
in fact there would be no problem if i even don't use any escape character in where query builder method and write the code as:

  ->leftjoin('books', function ($q) {
            $q->on('q3.order_item_id', '=', 'books.id')
                ->where('q3.order_item_type', '=', 'App\Models\Book');
        })

Have you tried to use either 4 or 2 backslashes? 3 backslashes look incorrect for me. — Ivan Yarych
– Ivan Yarych, Commented Jun 28, 2016 at 7:42
@IvanYarych this behavior is not the same all the time ,take a look at the EDIT. — alex
– alex, Commented Jun 28, 2016 at 8:03

Paulo Freitas · Accepted Answer · 2016-06-28 08:45:30Z

3

Both PHP¹ and MySQL² escapes slashes. MySQL on other hand requires them escaped unless NO_BACKSLASH_ESCAPES³ SQL mode is enabled - it's disabled by default. Within MySQL the escape sequence is either interpreted or ignored - \n will be translated into a new line, \m is ignored and translated into m.

So first PHP will convert \\\ into \\ - two get escaped into one. Then MySQL will also escape \\ into \, understanding that you wanted a literal slash. :)

Regarding your edit, selectRaw() sends raw queries to MySQL, so you've to escape the data yourself. All other non-raw methods do this internally within Laravel Query Builder, so you don't have to bother with these things.

PHP, unlike MySQL, does not ignore slashes. Using single-quoted strings any non-ending slash will be recognized as literal anyway, so you are not required to escape them - if your string ends with a slash then you are required. With double-quoted strings there are escape sequences that gets translated - your example would still work with them, but there are cases that can go wrong.

edited Jun 28, 2016 at 8:45

answered Jun 28, 2016 at 7:32

Paulo Freitas

13.7k15 gold badges78 silver badges98 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

alex Over a year ago

thanks, i added an edit. would you please check the question again

Paulo Freitas Over a year ago

I updated my answer to also answer your new question. ;)

alex Over a year ago

I've not used any escape character in internal where query builder method,and it works fine. does it mean where method will assume all user(developer) written characters as literal?

Paulo Freitas Over a year ago

PHP, unlike MySQL, does not ignore slashes. Using single-quoted strings any non-ending slash will be recognized as literal anyway, so you are not required to escape them - if your string ends with a slash then you are required. With double-quoted strings there are escape sequences that gets translated - your example would still work with them, but there are cases that can go wrong. ;)

Collectives™ on Stack Overflow

Laravel escape character in query builder

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related