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: 820a6f443d4bc85d2a46731f1b7536a152e256fd
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


36
37
38
39
40
41
42
43
		}

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



		if (array_key_exists('type', $data) && $data['type'] !== self::TYPE_SQL && $data['type'] !== self::TYPE_JSON)
		{
			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.');







>
>
|







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
172

173
174

175

176
177






178
179
180
181
182
183
184
			return [];
		}

		$out = [];
		$columns = $this->getColumns($target);

		foreach (reset($result) as $key => $v) {






			foreach ($columns as $ckey => $config) {
				if ($ckey == $key) {
					$out[$key] = $config->label;

				}
				elseif (isset($config->alias) && $config->alias == $key) {

					$out[$config->alias] = $config->label;

				}
			}






		}

		return $out;
	}

	/**
	 * Renvoie la liste des colonnes d'une cible







>
>
>
>
>
>


|
>


>
|
>


>
>
>
>
>
>







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
248
249
250
251
252
253
254
255
		}
		elseif ($target === 'compta') {
			$columns['t.id'] = (object) [
				'textMatch'=> false,
				'label'    => 'Numéro écriture',
				'type'     => 'integer',
				'null'     => false,
				'alias'    => 'id',
			];

			$columns['t.date'] = (object) [
				'textMatch'=> false,
				'label'    => 'Date',
				'type'     => 'date',
				'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
505
506
507
508
509
510
511
512
		{
			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(['id', $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);
		}








|







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
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
				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
	 */
	public function searchSQL($target, $query, array $force_select = null, $no_limit = 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],
				'membres' => ['membres' => null],
			];








			$db->protectSelect($allowed[$target], $query);
			return $db->get($query);
		}
		catch (\Exception $e) {
			$message = 'Erreur dans la requête : ' . $e->getMessage();

			if (null !== $force_select)
			{







>
>
>
>











|




















|
|


>
>
>
>
>
>
>
|







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
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
			</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 == '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">

						{linkbutton shape="search" label="Détails" href="!acc/transactions/details.php?id=%d"|args:$row.id}

					</td>
				</tr>
			{/foreach}
		</tbody>
	{*if $session->canAccess('membres', Membres::DROIT_ADMIN)}
		{include file="admin/membres/_list_actions.tpl" colspan=count($result_header)+1}
	{/if*}







|
















>
|
>







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
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
	{/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 key="c" item="cfg"}
					<td>{$cfg.title}</td>
				{/foreach}
				<td></td>
			</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"}
						<?php $link = false; ?>
						{if isset($result_header[$key])}
							<td>
								{if !$link}
									<a href="{$admin_url}membres/fiche.php?id={$row.id}">
								{/if}

								{$value|raw|display_champ_membre:$key}

								{if !$link}
									<?php $link = true; ?>
									</a>
								{/if}
							</td>


						{/if}
					{/foreach}
					<td class="actions">

						{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}
					</td>
				</tr>
			{/foreach}
		</tbody>
	{if $session->canAccess('membres', Membres::DROIT_ADMIN)}
		{include file="admin/membres/_list_actions.tpl" colspan=count($result_header)+1}
	{/if}
	</table>

	{if $session->canAccess('membres', Membres::DROIT_ECRITURE)}
		</form>
	{/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
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
<?php
assert(isset($columns));
assert(isset($action_url));
assert(isset($query));
assert(isset($is_admin));
$sql_disabled = !$is_admin;
?>

{form_errors}

<form method="post" action="{$action_url}" id="queryBuilderForm">
	<fieldset>
	{if $sql_query}
		<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 disabled=$sql_disabled}




		</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>
	{else}
		<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>





|






|



|
>
>
>
>










|







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&nbsp;!</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

27






28
29
30
31
32
33
34
			<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{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}







>
|
>
>
>
>
>
>







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

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

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


	if ($search->type === Recherche::TYPE_SQL) {



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







}

// 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);
	}
	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())
	{

		$type = $sql_query ? Recherche::TYPE_SQL : Recherche::TYPE_JSON;








		if ($id) {
			$recherche->edit($id, [
				'type'    => $type,
				'contenu' => $sql_query ?: $query,
			]);
		}







>
>














>
|
>
>
>













>
>
>
>
>
>
>












|


















>
|
>
>
>
>
>
>
>







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
169
170
171
172
173
174
175
176
	$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'));

if ($target == 'compta') {
	$tpl->display('acc/search.tpl');
}
else {
	$tpl->display('admin/membres/recherche.tpl');
}







|







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