CODEWITHSUNDEEP
mysqllaravelquery-builder
alex
alex

Reputation: 7905

Laravel escape character in query builder

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');
        })

Upvotes: 0

Views: 9181

Answers (1)

Paulo Freitas
Paulo Freitas

Reputation: 13649

Both PHP1 and MySQL2 escapes slashes. MySQL on other hand requires them escaped unless NO_BACKSLASH_ESCAPES3 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.

Upvotes: 3

Related Questions