Reputation: 7905
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
Reputation: 13649
Both PHP1 and MySQL2 escapes slashes. MySQL on other hand requires them escaped unless NO_BACKSLASH_ESCAPES
3 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