SQL translate error, Alone quote v unionAll()

libik

Zdravim,

Mam problem s dotazem, do ktereho vkladam string s jednoduchou uvozovkou (jednou).

Dibi Fluent:

<?php
$result = $this->db->select('count(*) as count')->from($this->db->select('count(*) as count')->from($this->pages)->
                                leftJoin($this->pages_menu)->on($this->pages_menu . '.page_id = ' . $this->pages . '.id')->
                                leftJoin($this->menu)->on($this->menu . '.id = ' . $this->pages_menu . '.menu_id')->
                                where('(1.3*(MATCH(' . $this->pages . '.name) AGAINST (%s', $string, ' IN BOOLEAN MODE)) + 0.6*(MATCH(' . $this->pages . '.text) AGAINST (%s', $string, ' IN BOOLEAN MODE)))')
                ->where($this->pages . '.lang = %s', $this->lang)
                ->where($this->pages . '.searchable = %b', 1)
                ->where($this->menu . '.active = %b', 1)
                ->groupBy($this->pages . '. id')
                ->unionAll($this->db->select('count(*) as count')->from($this->tabs)->
                        leftJoin($this->songs)->on($this->songs.'.id = '. $this->tabs.'.song_id')
                ->leftJoin($this->tab_types)->on($this->tab_types.'.id = '. $this->tabs.'.tab_type_id')
                ->where('100*(MATCH(' . $this->songs . '.name) AGAINST (%s', $string, ' IN BOOLEAN MODE))')
                ->groupBy($this->tabs . '.id'))
                ->unionAll($this->db->select('count(*) as count')->from($this->sheets)
                        ->leftJoin($this->songs)->on($this->songs.'.id = '. $this->sheets.'.song_id')
                ->where('100*(MATCH(' . $this->songs . '.name) AGAINST (%s', $string, ' IN BOOLEAN MODE))')
                        ->groupBy($this->sheets . '.id')
                        ))->as('temp')->fetchSingle();
?>

Vysledny dotaz:

<?php
SELECT count(*) as count
FROM `pages`
LEFT JOIN `pages_menu` ON pages_menu.page_id = pages.id
LEFT JOIN `menu` ON menu.id = pages_menu.menu_id
WHERE (1.3*(MATCH(pages.name) AGAINST ('you\'re my best friend' IN BOOLEAN MODE)) +
0.6*(MATCH(pages.text) AGAINST ('you\'re my best friend' IN BOOLEAN MODE))) AND pages.lang = 'en'
AND pages.searchable = 1 AND menu.active = 1
GROUP BY pages. id
UNION ALL (
SELECT count(*) as count
FROM `tabs`
LEFT JOIN `songs` ON songs.id = tabs.song_id
LEFT JOIN `tab_types` ON tab_types.id = tabs.tab_type_id
WHERE 100*(MATCH(songs.name) AGAINST ('you\\'re my best friend**Alone quote** IN BOOLEAN MODE))
GROUP BY `tabs`.`id`)
UNION ALL (
SELECT count(*) as count
FROM `sheets`
LEFT JOIN `songs` ON songs.id = sheets.song_id
WHERE 100*(MATCH(songs.name) AGAINST ('you\\'re my best friend**Alone quote** IN BOOLEAN MODE))
GROUP BY `sheets`.`id`)
?>

V unionAll je string znova escapovany 2×, cimz, se projevi uvozovka. Jak to vyresit?

Milo

Tam kde předáváš fluent do fluentu, použij modifikátor %SQL. Fluent převedený na string je už korektně oescapované SQL. Je to trochu nepříjemné, asi by se to mohlo změnit. Například:

$db->select('*')->from('%SQL', $db->select(...))->unionAll('%SQL', $db->select(...));

Mimo to, vkládat názvy tabulek proměnnou přímo do řetězce je nebezpečné. Bezpečnější je modifikátor %n:

$result = $this->db->select('COUNT(*) AS count')->from('%SQL',
    $this->db->select('COUNT(*) AS count')
        ->from($this->pages)
        ->leftJoin($this->pages_menu)->on('%n.page_id = %n.id', $this->pages_menu, $this->pages)
        ->leftJoin($this->menu)->on('%n.id = %n.menu_id', $this->menu, $this->pages_menu)
        ->where('(1.3 * (MATCH(%n.name)', $this->pages, 'AGAINST (%s IN BOOLEAN MODE)', $string, ') + 0.6*(MATCH(%n.text)', $this->pages, 'AGAINST (%s IN BOOLEAN MODE)', $string, '))')
        ...

Anebo, dibi má pro tyto účely substituce (moc je nepoužívám, tak to snad nespletu):

$db = new DibiConnection([
    'host' => '....',
    'substitutes' => [
        'pages' => 'pages',
        'menu' => 'menu',
        'pagesMenu' => 'pages_menu',
    ],
);

$result = $this->db->select('COUNT(*) AS count')->from('%SQL',
    $this->db->select('COUNT(*) AS count')
        ->from($this->pages)
        ->leftJoin($this->pages_menu)->on('[:pagesMenu:].page_id = [:pages:].id')
        ->leftJoin($this->menu)->on('[:menu:].id = [:pagesMenu:].menu_id')
...

Oznámení

SQL translate error, Alone quote v unionAll()

Komentáře

Oznámení

SQL translate error, **Alone quote** v unionAll()

Komentáře

SQL translate error, Alone quote v unionAll()