Overview
Comment: | Allow unprotected SQL queries, but only if created by someone who has access to main config |
---|---|
Downloads: | Tarball | ZIP archive | SQL archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
820a6f443d4bc85d2a46731f1b7536a1 |
User & Date: | bohwaz on 2020-12-08 17:03:13 |
Other Links: | manifest | tags |
Context
2020-12-08
| ||
17:08 | Allow to just subscribe a user to a service, without adding a payment check-in: 481cf0b000 user: bohwaz tags: trunk, stable | |
17:03 | Allow unprotected SQL queries, but only if created by someone who has access to main config check-in: 820a6f443d user: bohwaz tags: trunk | |
16:02 | Fix saved search edit form check-in: 6bf2abf9df user: bohwaz tags: trunk | |
Changes
Modified src/include/lib/Garradin/Recherche.php from [5f2af851fc] to [1d10925eeb].
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | <?php namespace Garradin; use Garradin\Entities\Accounting\Transaction; class Recherche { const TYPE_JSON = 'json'; const TYPE_SQL = 'sql'; const TARGETS = [ 'membres', 'compta', ]; protected function _checkFields($data) | > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | <?php namespace Garradin; use Garradin\Entities\Accounting\Transaction; class Recherche { const TYPE_JSON = 'json'; const TYPE_SQL = 'sql'; const TYPE_SQL_UNPROTECTED = 'sql_unprotected'; const TARGETS = [ 'membres', 'compta', ]; protected function _checkFields($data) |
︙ | ︙ | |||
29 30 31 32 33 34 35 | } if (array_key_exists('id_membre', $data) && null !== $data['id_membre'] && !$db->test('membres', 'id = ?', $data['id_membre'])) { throw new \InvalidArgumentException('Numéro d\'utilisateur inconnu.'); } | > > | | 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 | } if (array_key_exists('id_membre', $data) && null !== $data['id_membre'] && !$db->test('membres', 'id = ?', $data['id_membre'])) { throw new \InvalidArgumentException('Numéro d\'utilisateur inconnu.'); } static $types = [self::TYPE_SQL, self::TYPE_JSON, self::TYPE_SQL_UNPROTECTED]; if (array_key_exists('type', $data) && !in_array($data['type'], $types)) { throw new \InvalidArgumentException('Type de recherche inconnu.'); } if (array_key_exists('cible', $data) && !in_array($data['cible'], self::TARGETS, true)) { throw new \InvalidArgumentException('Cible de recherche invalide.'); |
︙ | ︙ | |||
163 164 165 166 167 168 169 170 171 | return []; } $out = []; $columns = $this->getColumns($target); foreach (reset($result) as $key => $v) { foreach ($columns as $ckey => $config) { if ($ckey == $key) { | > > > > > > | > > | > > > > > > > | 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 | return []; } $out = []; $columns = $this->getColumns($target); foreach (reset($result) as $key => $v) { if (substr($key, 0, 1) == '_') { continue; } $label = null; foreach ($columns as $ckey => $config) { if ($ckey == $key) { $label = $config->label; break; } elseif (isset($config->alias) && $config->alias == $key) { $key = $config->alias; $label = $config->label; break; } } if (!$label) { $label = $key; } $out[$key] = $label; } return $out; } /** * Renvoie la liste des colonnes d'une cible |
︙ | ︙ | |||
241 242 243 244 245 246 247 | } elseif ($target === 'compta') { $columns['t.id'] = (object) [ 'textMatch'=> false, 'label' => 'Numéro écriture', 'type' => 'integer', 'null' => false, | | | 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 | } elseif ($target === 'compta') { $columns['t.id'] = (object) [ 'textMatch'=> false, 'label' => 'Numéro écriture', 'type' => 'integer', 'null' => false, 'alias' => 'transaction_id', ]; $columns['t.date'] = (object) [ 'textMatch'=> false, 'label' => 'Date', 'type' => 'date', 'null' => false, |
︙ | ︙ | |||
498 499 500 501 502 503 504 | { throw new UserException('Aucune clause trouvée dans la recherche.'); } // Ajout du champ identité si pas présent if ($target == 'membres') { | | | 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 | { throw new UserException('Aucune clause trouvée dans la recherche.'); } // Ajout du champ identité si pas présent if ($target == 'membres') { $query_columns = array_merge([$config->get('champ_identite')], $query_columns); } // Ajout de champs compta si pas présents elseif ($target == 'compta') { $query_columns = array_merge(['t.id', 't.date', 't.label', 'l.debit', 'l.credit', 'a.code'], $query_columns); } |
︙ | ︙ | |||
541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 | INNER JOIN acc_transactions_lines AS l ON l.id_transaction = t.id INNER JOIN acc_accounts AS a ON l.id_account = a.id LEFT JOIN acc_accounts AS a2 ON l.id_analytical = a2.id WHERE %s GROUP BY t.id ORDER BY %s %s LIMIT %d;', $query_columns, $query_groups, $order, $desc, (int) $limit); $sql_query = preg_replace('/"(a|a2|l|t)\./', '"$1"."', $sql_query); } else { $sql_query = sprintf('SELECT id, %s FROM %s WHERE %s ORDER BY %s %s LIMIT %d;', $query_columns, $target, $query_groups, $order, $desc, (int) $limit); } return $sql_query; } /** * Lancer une recherche SQL */ | > > > > | | | > > > > > > > | | 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 | INNER JOIN acc_transactions_lines AS l ON l.id_transaction = t.id INNER JOIN acc_accounts AS a ON l.id_account = a.id LEFT JOIN acc_accounts AS a2 ON l.id_analytical = a2.id WHERE %s GROUP BY t.id ORDER BY %s %s LIMIT %d;', $query_columns, $query_groups, $order, $desc, (int) $limit); $sql_query = preg_replace('/"(a|a2|l|t)\./', '"$1"."', $sql_query); } else if ('membres' === $target) { $sql_query = sprintf('SELECT id AS _user_id, %s FROM %s WHERE %s ORDER BY %s %s LIMIT %d;', $query_columns, $target, $query_groups, $order, $desc, (int) $limit); } else { $sql_query = sprintf('SELECT id, %s FROM %s WHERE %s ORDER BY %s %s LIMIT %d;', $query_columns, $target, $query_groups, $order, $desc, (int) $limit); } return $sql_query; } /** * Lancer une recherche SQL */ public function searchSQL(string $target, $query, array $force_select = null, bool $no_limit = false, bool $unprotected = false) { if (!in_array($target, self::TARGETS, true)) { throw new \InvalidArgumentException('Cible inconnue : ' . $target); } if (null !== $force_select) { $query = preg_replace('/^\s*SELECT.*FROM\s+/Ui', 'SELECT ' . implode(', ', $force_select) . ' FROM ', $query); } if (!$no_limit && !preg_match('/LIMIT\s+\d+/i', $query)) { $query = preg_replace('/;?\s*$/', '', $query); $query .= ' LIMIT 100'; } try { $db = DB::getInstance(); static $allowed = [ 'compta' => ['acc_transactions' => null, 'acc_transactions_lines' => null, 'acc_accounts' => null, 'acc_charts' => null, 'acc_years' => null, 'acc_transactions_users' => null], 'membres' => ['membres' => null, 'membres_categories' => null], ]; if ($unprotected) { $allowed_tables = null; } else { $allowed_tables = $allowed[$target]; } $db->protectSelect($allowed_tables, $query); return $db->get($query); } catch (\Exception $e) { $message = 'Erreur dans la requête : ' . $e->getMessage(); if (null !== $force_select) { |
︙ | ︙ |
Modified src/templates/acc/search.tpl from [b1c54ffaff] to [1184e1ecfe].
︙ | ︙ | |||
26 27 28 29 30 31 32 | </tr> </thead> <tbody> {foreach from=$result item="row"} <tr> {*if $session->canAccess('membres', Membres::DROIT_ADMIN)}<td class="check"><input type="checkbox" name="selected[]" value="{$row.id}" /></td>{/if*} {foreach from=$row key="key" item="value"} | | > | > | 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 | </tr> </thead> <tbody> {foreach from=$result item="row"} <tr> {*if $session->canAccess('membres', Membres::DROIT_ADMIN)}<td class="check"><input type="checkbox" name="selected[]" value="{$row.id}" /></td>{/if*} {foreach from=$row key="key" item="value"} {if $key == 'transaction_id'} <td class="num"> <a href="{$admin_url}acc/transactions/details.php?id={$value}">{$value}</a> </td> {else} <td> {if $key == 'credit' || $key == 'debit'} {$value|raw|html_money:false} {elseif null == $value} <em>(nul)</em> {else} {$value} {/if} </td> {/if} {/foreach} <td class="actions"> {if $row.transaction_id} {linkbutton shape="search" label="Détails" href="!acc/transactions/details.php?id=%d"|args:$row.transaction_id} {/if} </td> </tr> {/foreach} </tbody> {*if $session->canAccess('membres', Membres::DROIT_ADMIN)} {include file="admin/membres/_list_actions.tpl" colspan=count($result_header)+1} {/if*} |
︙ | ︙ |
Modified src/templates/admin/membres/recherche.tpl from [9258528c92] to [58ecc642cf].
︙ | ︙ | |||
10 11 12 13 14 15 16 | {/if} <p class="help">{$result|count} membres trouvés pour cette recherche.</p> <table class="list search"> <thead> <tr> {if $session->canAccess('membres', Membres::DROIT_ADMIN)}<td class="check"><input type="checkbox" value="Tout cocher / décocher" id="f_all" /><label for="f_all"></label></td>{/if} | | | | | | | | | | | | > > > | | | > | | 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 | {/if} <p class="help">{$result|count} membres trouvés pour cette recherche.</p> <table class="list search"> <thead> <tr> {if $session->canAccess('membres', Membres::DROIT_ADMIN)}<td class="check"><input type="checkbox" value="Tout cocher / décocher" id="f_all" /><label for="f_all"></label></td>{/if} {foreach from=$result_header item="label"} <td>{$label}</td> {/foreach} <td></td> </tr> </thead> <tbody> {foreach from=$result item="row"} <tr> {if $session->canAccess('membres', Membres::DROIT_ADMIN)}<td class="check">{if $row._user_id}{input type="checkbox" name="selected[]" value=$row._user_id}{/if}</td>{/if} {foreach from=$row key="key" item="value"} <?php $link = false; ?> {if isset($result_header[$key])} <td> {if !$link && $row._user_id} <a href="{$admin_url}membres/fiche.php?id={$row._user_id}"> {/if} {$value|raw|display_champ_membre:$key} {if !$link} <?php $link = true; ?> </a> {/if} </td> {elseif substr($key, 0, 1) != '_'} <td>{$value}</td> {/if} {/foreach} <td class="actions"> {if $row._user_id} {linkbutton shape="user" label="Fiche membre" href="!membres/fiche.php?id=%d"|args:$row.id} {if $session->canAccess('membres', Membres::DROIT_ECRITURE)} {linkbutton shape="edit" label="Modifier" href="!membres/modifier.php?id=%d"|args:$row.id} {/if} {/if} </td> </tr> {/foreach} </tbody> {if $session->canAccess('membres', Membres::DROIT_ADMIN) && $row._user_id} {include file="admin/membres/_list_actions.tpl" colspan=count($result_header)+1} {/if} </table> {if $session->canAccess('membres', Membres::DROIT_ECRITURE)} </form> {/if} |
︙ | ︙ |
Modified src/templates/common/search/advanced.tpl from [793be9f367] to [731c9414d6].
1 2 3 4 5 | <?php assert(isset($columns)); assert(isset($action_url)); assert(isset($query)); assert(isset($is_admin)); | | | | > > > > | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | <?php assert(isset($columns)); assert(isset($action_url)); assert(isset($query)); assert(isset($is_admin)); $sql_disabled = !$is_admin || (!$session->canAccess('config', Membres::DROIT_ADMIN) && $is_unprotected); ?> {form_errors} <form method="post" action="{$action_url}" id="queryBuilderForm"> <fieldset> {if $sql_query && !$sql_disabled} <legend>Schéma des tables SQL</legend> <pre class="sql_schema">{foreach from=$schema item="table"}{$table}<br />{/foreach}</pre> <dl> {input type="textarea" name="sql_query" cols="100" rows="7" required=1 label="Requête SQL" help="Si aucune limite n'est précisée, une limite de 100 résultats sera appliquée." default=$sql_query} {if $session->canAccess('config', Membres::DROIT_ADMIN)} {input type="checkbox" name="unprotected" value=1 label="Autoriser l'accès à toutes les tables de la base de données"|args:$target default=$is_unprotected} <dd class="help">Attention : en cochant cette case vous autorisez la requête à lire toutes les données de toutes les tables de la base de données !</dd> {/if} </dl> <p class="submit"> {button type="submit" name="run" label="Exécuter" shape="search" class="main"} <input type="hidden" name="id" value="{$search.id}" /> {if $search.id} {button name="save" value=1 type="submit" label="Enregistrer : %s"|args:$search.intitule|truncate:40:"…":true shape="upload"} {else} {button name="save" value=1 type="submit" label="Enregistrer cette recherche" shape="upload"} {/if} </p> {elseif !$sql_disabled} <legend>Rechercher</legend> <div class="queryBuilder" id="queryBuilder"></div> <p class="actions"> <label>Trier par <select name="order"> {foreach from=$columns key="column" item="properties"} <option value="{$column}"{if $query.order == $column} selected="selected"{/if}>{$properties.label}</option> |
︙ | ︙ | |||
48 49 50 51 52 53 54 55 56 57 58 59 60 61 | {else} {button name="save" value=1 type="submit" label="Enregistrer cette recherche" shape="upload"} {/if} {if $is_admin} {button name="to_sql" value=1 type="submit" label="Recherche SQL" shape="edit"} {/if} </p> {/if} </fieldset> </form> <script type="text/javascript"> var columns = {$columns|escape:'json'}; | > > > | 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 | {else} {button name="save" value=1 type="submit" label="Enregistrer cette recherche" shape="upload"} {/if} {if $is_admin} {button name="to_sql" value=1 type="submit" label="Recherche SQL" shape="edit"} {/if} </p> {else} <legend>Recherche enregistrée</legend> <h3>{$search.intitule}</h3> {/if} </fieldset> </form> <script type="text/javascript"> var columns = {$columns|escape:'json'}; |
︙ | ︙ |
Modified src/templates/common/search/saved_searches.tpl from [0f315688a3] to [4b50bfbcd9].
︙ | ︙ | |||
20 21 22 23 24 25 26 | <dl> {input type="text" name="intitule" label="Intitulé" required=1 source=$recherche} <dt>Statut</dt> <?php $checked = (int)(bool)$recherche->id_membre; ?> {input type="radio" name="prive" value="1" default=$checked label="Recherche privée" help="Visible seulement par moi-même"} {input type="radio" name="prive" value="0" default=$checked label="Recherche publique" help="Visible et exécutable par tous les membres ayant accès à la gestion %s"|args:$target} <dt>Type</dt> | > | > > > > > > | 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 | <dl> {input type="text" name="intitule" label="Intitulé" required=1 source=$recherche} <dt>Statut</dt> <?php $checked = (int)(bool)$recherche->id_membre; ?> {input type="radio" name="prive" value="1" default=$checked label="Recherche privée" help="Visible seulement par moi-même"} {input type="radio" name="prive" value="0" default=$checked label="Recherche publique" help="Visible et exécutable par tous les membres ayant accès à la gestion %s"|args:$target} <dt>Type</dt> <dd> {if $recherche.type == Recherche::TYPE_JSON} Avancée {elseif $recherche.type == Recherche::TYPE_SQL_UNPROTECTED} SQL non protégée {else} SQL {/if}</dd> <dt>Cible</dt> <dd>{$recherche.cible}</dd> </dl> </fieldset> <p class="submit"> {csrf_field key="edit_recherche_%s"|args:$recherche.id} |
︙ | ︙ |
Modified src/www/admin/common/search.php from [c07c074828] to [58a2469528].
︙ | ︙ | |||
20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | $text_query = trim(qg('qt')); $result = null; $sql_query = null; $search = null; $id = f('id') ?: qg('id'); // Recherche simple if ($text_query !== '' && $target === 'membres' && empty($query->query)) { $query = $recherche->buildSimpleMemberQuery($text_query); } // Recherche existante elseif ($id && empty($query->query)) { $search = $recherche->get($id); if (!$search) { throw new UserException('Recherche inconnue ou invalide'); } | > > > | > > > > > > > > > > | > | > > > > > > > | 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 | $text_query = trim(qg('qt')); $result = null; $sql_query = null; $search = null; $id = f('id') ?: qg('id'); $is_unprotected = false; // Recherche simple if ($text_query !== '' && $target === 'membres' && empty($query->query)) { $query = $recherche->buildSimpleMemberQuery($text_query); } // Recherche existante elseif ($id && empty($query->query)) { $search = $recherche->get($id); if (!$search) { throw new UserException('Recherche inconnue ou invalide'); } if ($search->type != Recherche::TYPE_JSON) { if ($search->type == Recherche::TYPE_SQL_UNPROTECTED) { $is_unprotected = true; } $sql_query = $search->contenu; } else { $query = $search->query; $query->limit = (int) f('limit') ?: $query->limit; } } // Recherche SQL if (f('sql_query')) { // Only admins can run custom queries, others can only run saved queries $session->requireAccess($target, Membres::DROIT_ADMIN); $sql_query = f('sql_query'); if ($session->canAccess('config', Membres::DROIT_ADMIN)) { $is_unprotected = (bool) f('unprotected'); } else { $is_unprotected = false; } } // Execute search if ($query->query || $sql_query) { try { if ($sql_query) { $sql = $sql_query; } else { $sql = $recherche->buildQuery($target, $query->query, $query->order, $query->desc, $query->limit); } $result = $recherche->searchSQL($target, $sql, null, false, $is_unprotected); } catch (UserException $e) { $form->addError($e->getMessage()); } if (f('to_sql')) { $sql_query = $sql; } } if (null !== $result) { if (count($result) == 1 && $text_query !== '' && $target === 'membres') { Utils::redirect(ADMIN_URL . 'membres/fiche.php?id=' . (int)$result[0]->id); } if (f('save') && !$form->hasErrors()) { if (!$sql_query) { $type = Recherche::TYPE_JSON; } elseif ($is_unprotected) { $type = Recherche::TYPE_SQL_UNPROTECTED; } else { $type = Recherche::TYPE_SQL; } if ($id) { $recherche->edit($id, [ 'type' => $type, 'contenu' => $sql_query ?: $query, ]); } |
︙ | ︙ | |||
162 163 164 165 166 167 168 | $result = null; } $columns = $recherche->getColumns($target); $is_admin = $session->canAccess($target, Membres::DROIT_ADMIN); $schema = $recherche->schema($target); | | | 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 | $result = null; } $columns = $recherche->getColumns($target); $is_admin = $session->canAccess($target, Membres::DROIT_ADMIN); $schema = $recherche->schema($target); $tpl->assign(compact('query', 'sql_query', 'result', 'columns', 'is_admin', 'schema', 'search', 'target', 'is_unprotected')); if ($target == 'compta') { $tpl->display('acc/search.tpl'); } else { $tpl->display('admin/membres/recherche.tpl'); } |