Overview
Comment:Utiliser DB::userSelect* pour les requêtes en lecture
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk | stable
Files: files | file ages | folders
SHA1: 836495093b336879428082353901ffa3b8e4d8eb
User & Date: bohwaz on 2018-12-18 13:49:18
Other Links: manifest | tags
Context
2018-12-18
15:04
Corriger bug lorsque le compte n'existe plus check-in: aae43faabd user: bohwaz tags: trunk, stable
13:49
Utiliser DB::userSelect* pour les requêtes en lecture check-in: 836495093b user: bohwaz tags: trunk, stable
2018-12-10
23:21
Correction traitement de valeurs non fournies (null) check-in: f24cfb81db user: bohwaz tags: trunk, stable
Changes

Modified src/include/lib/Garradin/Compta/Journal.php from [9b1778c3b4] to [2a385b6769].

372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
        $query .= ' ORDER BY date;';

        return $db->get($query);
    }

    public function searchSQL($query)
    {
        $db = DB::getInstance();

        if (!preg_match('/LIMIT\s+/i', $query))
        {
            $query = preg_replace('/;?\s*$/', '', $query);
            $query .= ' LIMIT 100';
        }

        if (preg_match('/;\s*(.+?)$/', $query))
        {
            throw new UserException('Une seule requête peut être envoyée en même temps.');
        }

        $st = $db->prepare($query);

        if (!$st->readOnly())
        {
            throw new UserException('Seules les requêtes en lecture sont autorisées.');
        }

        $res = $st->execute();
        $out = [];

        while ($row = $res->fetchArray(SQLITE3_ASSOC))
        {
            $out[] = $row;
        }

        return $out;
    }

    public function schemaSQL()
    {
        $db = DB::getInstance();

        $tables = [
            'journal'   =>  $db->firstColumn('SELECT sql FROM sqlite_master WHERE type = \'table\' AND name = \'compta_journal\';'),
        ];

        return $tables;
    }
}







<
<






<
<
<
<
|
<
|
<
<
<

|
<
<
|
<
<
<

<
<













372
373
374
375
376
377
378


379
380
381
382
383
384




385

386



387
388


389



390


391
392
393
394
395
396
397
398
399
400
401
402
403
        $query .= ' ORDER BY date;';

        return $db->get($query);
    }

    public function searchSQL($query)
    {


        if (!preg_match('/LIMIT\s+/i', $query))
        {
            $query = preg_replace('/;?\s*$/', '', $query);
            $query .= ' LIMIT 100';
        }





        try {

            return DB::getInstance()->userSelectGet($query);



        }
        catch (\Exception $e) {


            throw new UserException('Erreur dans la requête : ' . $e->getMessage());



        }


    }

    public function schemaSQL()
    {
        $db = DB::getInstance();

        $tables = [
            'journal'   =>  $db->firstColumn('SELECT sql FROM sqlite_master WHERE type = \'table\' AND name = \'compta_journal\';'),
        ];

        return $tables;
    }
}

Modified src/include/lib/Garradin/Plugin.php from [45828504b6] to [57a57cdb9d].

432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
				'{Membres::DROIT_ACCES}' => Membres::DROIT_ACCES,
				'{Membres::DROIT_ECRITURE}' => Membres::DROIT_ECRITURE,
				'{Membres::DROIT_ADMIN}' => Membres::DROIT_ADMIN,
			]);

			$condition = preg_replace_callback('/\{\$user\.(\w+)\}/', function ($m) use ($user) { return $user->{$m[1]}; }, $condition);
			$query = 'SELECT 1 WHERE ' . $condition . ';';
			$st = $db->prepare($query);

			if (!$st->readOnly())
			{
				throw new \LogicException('Requête plugin pour affichage dans le menu n\'est pas en lecture : ' . $query);
			}

			$res = $st->execute();

			if (!$res->fetchArray(\SQLITE3_NUM))
			{
				unset($list[$id]);
				continue;
			}







<

<
<
<
<
|







432
433
434
435
436
437
438

439




440
441
442
443
444
445
446
447
				'{Membres::DROIT_ACCES}' => Membres::DROIT_ACCES,
				'{Membres::DROIT_ECRITURE}' => Membres::DROIT_ECRITURE,
				'{Membres::DROIT_ADMIN}' => Membres::DROIT_ADMIN,
			]);

			$condition = preg_replace_callback('/\{\$user\.(\w+)\}/', function ($m) use ($user) { return $user->{$m[1]}; }, $condition);
			$query = 'SELECT 1 WHERE ' . $condition . ';';






			$st = $db->userSelectStatement($query);
			$res = $st->execute();

			if (!$res->fetchArray(\SQLITE3_NUM))
			{
				unset($list[$id]);
				continue;
			}

Modified src/include/lib/Garradin/Recherche.php from [0f45eebdb7] to [0325ea49c7].

392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
	public function searchSQL($target, $query, $force_select = null)
	{
		if (!in_array($target, self::TARGETS, true))
		{
			throw new \InvalidArgumentException('Cible inconnue : ' . $target);
		}

		$db = DB::getInstance();

		if ($force_select)
		{
			$query = preg_replace('/^\s*SELECT.*FROM\s+/Ui', 'SELECT ' . $force_select . ' FROM ', $query);
		}

		if (!preg_match('/LIMIT\s+/i', $query))
		{
			$query = preg_replace('/;?\s*$/', '', $query);
			$query .= ' LIMIT 100';
		}

		if (preg_match('/;\s*(.+?)$/', $query))
		{
			throw new UserException('Une seule requête peut être envoyée en même temps.');
		}

		$st = $db->prepare($query);

		if (!$st->readOnly())
		{
			throw new UserException('Seules les requêtes en lecture sont autorisées.');
		}

		$res = $st->execute();
		$out = [];

		while ($row = $res->fetchArray(SQLITE3_ASSOC))
		{
			$out[] = (object) $row;
		}

		return $out;
	}

	public function schema($target)
	{
		$db = DB::getInstance();

		if ($target == 'membres')







<
<





|





<
<
<
<
|
<
|
<
<
<

|
<
<
|
<
<
<

<
<







392
393
394
395
396
397
398


399
400
401
402
403
404
405
406
407
408
409




410

411



412
413


414



415


416
417
418
419
420
421
422
	public function searchSQL($target, $query, $force_select = null)
	{
		if (!in_array($target, self::TARGETS, true))
		{
			throw new \InvalidArgumentException('Cible inconnue : ' . $target);
		}



		if ($force_select)
		{
			$query = preg_replace('/^\s*SELECT.*FROM\s+/Ui', 'SELECT ' . $force_select . ' FROM ', $query);
		}

		if (!preg_match('/LIMIT\s+\d+/i', $query))
		{
			$query = preg_replace('/;?\s*$/', '', $query);
			$query .= ' LIMIT 100';
		}





		try {

			return DB::getInstance()->userSelectGet($query);



		}
		catch (\Exception $e) {


			throw new UserException('Erreur dans la requête : ' . $e->getMessage());



		}


	}

	public function schema($target)
	{
		$db = DB::getInstance();

		if ($target == 'membres')

Modified src/include/lib/Garradin/Squelette.php from [fc1698f7ac] to [8be8c4d3e2].

570
571
572
573
574
575
576
577
578
579
580
581
582
583
584

                        $where .= ' AND '.$criteria['field'].' = ?';

                        if ($criteria['field'] == 'w.id')
                        {
                            $criteria['field'] = 'id';
                        }
                        
                        $query_args[] = ['$this->getVariable(\'' . $criteria['field'] . '\')'];
                        break;
                    }
                    default:
                        break;
                }
            }







|







570
571
572
573
574
575
576
577
578
579
580
581
582
583
584

                        $where .= ' AND '.$criteria['field'].' = ?';

                        if ($criteria['field'] == 'w.id')
                        {
                            $criteria['field'] = 'id';
                        }

                        $query_args[] = ['$this->getVariable(\'' . $criteria['field'] . '\')'];
                        break;
                    }
                    default:
                        break;
                }
            }
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
                    $query .= (int) $begin;
                }
                else
                {
                    $query .= '?';
                    $query_args[] = ['\'.$this->variables[\'debut_liste\'].\''];
                }
                
                $query .= ','.(int)$limit;
            }
        }
        else
        {
            $params = [
                'loopName'  =>  $loopName,







|







604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
                    $query .= (int) $begin;
                }
                else
                {
                    $query .= '?';
                    $query_args[] = ['\'.$this->variables[\'debut_liste\'].\''];
                }

                $query .= ','.(int)$limit;
            }
        }
        else
        {
            $params = [
                'loopName'  =>  $loopName,
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
            {
                $query = 'SELECT 0 LIMIT 0;';
            }
        }

        try {
            // Sécurité anti injection, à la compilation seulement
            $statement = $db->prepare($query);
        }
        catch (\Exception $e)
        {
            throw new \KD2\MiniSkelMarkupException("Erreur SQL dans la requête : ".$e->getMessage() . "\n " . $query);
        }
        
        if (!$statement->readOnly())
        {
            throw new \KD2\MiniSkelMarkupException("Requête en écriture illégale: ".$query);
        }

        $hash = sha1(uniqid(mt_rand(), true));
        $out = new Squelette_Snippet();
        $out->append(1, '$parent_hash = $this->current[\'_self_hash\'];');
        $out->append(1, '$this->parent =& $parent_hash ? $this->_vars[$parent_hash] : null;');

        if (!empty($search))







|





<
<
<
<
<







649
650
651
652
653
654
655
656
657
658
659
660
661





662
663
664
665
666
667
668
            {
                $query = 'SELECT 0 LIMIT 0;';
            }
        }

        try {
            // Sécurité anti injection, à la compilation seulement
            $statement = $db->userSelectStatement($query);
        }
        catch (\Exception $e)
        {
            throw new \KD2\MiniSkelMarkupException("Erreur SQL dans la requête : ".$e->getMessage() . "\n " . $query);
        }






        $hash = sha1(uniqid(mt_rand(), true));
        $out = new Squelette_Snippet();
        $out->append(1, '$parent_hash = $this->current[\'_self_hash\'];');
        $out->append(1, '$this->parent =& $parent_hash ? $this->_vars[$parent_hash] : null;');

        if (!empty($search))