Clicking on table header don't sort by fieldname when using SignON Auth

[RS974]

Describe the bug

I authenticate with SignOn, display a table and click on one fieldname in the table header.
After displaying "loading in progress", the sort was not included in the SQL query.
This problem is found for any version higher than 4.9.2

To Reproduce

1. Start phpMyAdmin with config.inc.php

<?php

$cfg['Servers'][1]['host'] = '127.0.0.1';
$cfg['Servers'][1]['port'] = '3306';
$cfg['Servers'][1]['extension'] = 'mysqli'; // <-- deprecated

$cfg['Servers'][1]['user'] = 'user';
$cfg['Servers'][1]['password'] = 'secret';

$cfg['Servers'][1]['auth_type'] = 'signon';
$cfg['Servers'][1]['SignonSession'] = 'ss';
$cfg['Servers'][1]['SignonURL'] = 'https://www.ANYHOST.com/login';
$cfg['Servers'][1]['LogoutURL'] = 'https://www.ANYHOST.com/logout';
?>

1. Go to 'Table record list'
2. Click on 'fiedname'
3. The SQL query doesn't sort by fieldname.

Expected behavior

I should have a sort by fieldname I clicked on in the table header.

Server configuration

• Operating system: Centos 7
• Web server: Apache 2.4.6
• Database version: 5.5.64-MariaDB
• PHP version: 7.2.24
• phpMyAdmin version: 4.9.3+

Client configuration

• Browser: Chrome 80 (64-bit), Firefox 74 (64-bit)
• Operating system: Windows 10

[Dahkon]

Same problem for me.
It seems server=2 in the url prevents the query to be executed.

[RS974]

I test 2 configurations

1. My config : server1 auth_type=cookie and server2 auth_type=signon
2. Test config : server1 auth_type=signon

I have the same problem.

[williamdes]

Can you try latest 5.0 version in case it would be fixed, but anyway I will try to have a look and fix the request if 5.0 is affected
Thank you for reporting this issue

[RS974]

I try all versions : 4.9.0 to 5.0.2
4.9.0 to 4.9.2 : OK
4.9.3 to 5.0.2 : bad
so I was able to determine that as of 4.9.3 my Signon Auth configuration had this problem.

[deg026]

confirm same problem, aftrer updating from 4.9.0 to 5.0.2
receive same problems with "signon" auth type....
seems like any of GET parameters being ignored...

[williamdes]

If anyone can make a patch before me I will be very glad to approve any pull-request to QA_5_0 branch

[MauricioFauth]

Maybe related to ecb5faa.

[williamdes]

I do not understand where the issue is, I tried a multi server setup using server n°2 and a single server setup and was able to order the columns and do all the actions I wanted.
Can someone help me reproducing this issue ?
I used https://github.com/phpmyadmin/phpmyadmin/blob/master/examples/signon.php and

if (($_SERVER['HTTP_HOST'] ?? '') === 'pma.sso.local') {
    $i = 0;
    $cfg['Servers'] = [];
    $i++;
    $cfg['Servers'][$i]['verbose'] = 'MariaDB 10.4 SSO';
    $cfg['Servers'][$i]['host'] = 'mariadb104.williamdes.local';
    $cfg['Servers'][$i]['port'] = 3306;
    $cfg['Servers'][$i]['AllowNoPassword'] = true;
    $cfg['Servers'][$i]['auth_type'] = 'signon';
    $cfg['Servers'][$i]['SignonSession'] = 'SignonSession';
    $cfg['Servers'][$i]['SignonURL'] = 'http://pma.sso.local/signon-pma.php';
    $cfg['Servers'][$i]['LogoutURL'] = 'http://pma.sso.local/signon-pma.php?logout=true';
}

[Dahkon]

SSO is working correctly as we are being connected on PMA.

The problem is when using SSO (it works fine with standard login on same config and version), sql_query parameter on url sql.php is not being treated.

Parameter is displayed fine in url but not beeing used on the real query.
If I change it manually nothing happend (a loading but that's all).

On the contrary parameter table is working fine, changing it manually triggers a change.

[williamdes]

🤔 I still could not reproduce this issue, maybe because what I am testing is not the right things.
Could you screenshot the steps to get to the broken place where sql_query is not working ?

[Dahkon]

I don't think a screenshot will help, every click on columns header to order the tables isn't working.

We have 2 configurations, the first one is for connecting with regular login/password and the second one is for connecting with SSO.

We use a button in our extranet to connect to PMA directly.

Here is our code that open PMA with automatic SSO connexion maybe this can help ?

    ini_set('session.use_cookies', 'true');
    $secure_cookie = false;
    session_set_cookie_params(0, '/', '', $secure_cookie, true);
    $session_name = 'SignonSessionXXX';
    session_name($session_name);
    @session_start();

    $_SESSION['PMA_single_signon_user'] = 'xxx';
    $_SESSION['PMA_single_signon_password'] = $_POST['yyy'];
    $_SESSION['PMA_single_signon_host'] = 'localhost';
    $_SESSION['PMA_single_signon_port'] = '';
    $_SESSION['PMA_single_signon_token'] = md5(uniqid(strval(rand()), true));
	
    $id = session_id();
    @session_write_close();
    header('Location: /phpMyAdmin/index.php?server=2');

I suspect the server=2 is breaking something in the url process.

[williamdes]

🎉 I can now see the 🐛

    $i = 0;
    $cfg['Servers'] = [];
    $i++;
    $cfg['Servers'][$i]['verbose'] = 'SSO light';
    $cfg['Servers'][$i]['auth_type'] = 'signon';
    $cfg['Servers'][$i]['SignonSession'] = 'SignonSession';
    $cfg['Servers'][$i]['SignonURL'] = 'http://pma.sso.local/signon-pma.php';
    $cfg['Servers'][$i]['LogoutURL'] = 'http://pma.sso.local/signon-pma.php?logout=true';
    $i++;
    $cfg['Servers'][$i]['verbose'] = 'MariaDB 10.4 SSO';
    $cfg['Servers'][$i]['host'] = 'mariadb104.williamdes.local';
    $cfg['Servers'][$i]['port'] = 3306;
    $cfg['Servers'][$i]['AllowNoPassword'] = true;
    $cfg['Servers'][$i]['auth_type'] = 'signon';
    $cfg['Servers'][$i]['SignonSession'] = 'SignonSession';
    $cfg['Servers'][$i]['SignonURL'] = 'http://pma.sso.local/signon-pma.php';
    $cfg['Servers'][$i]['LogoutURL'] = 'http://pma.sso.local/signon-pma.php?logout=true';

The server parameter is clearly missing on all the queries.

[RS974]

I'm sorry, I forgot to mention that information.
Like Dahkon, I send the form data in $_POST from my extranet.

If I use a form in signon.php. Indeed there is no problem.

But I use Signon authentication to avoid manual entry.

[williamdes]

I found out that the issue is with the session hmac secret

diff --git a/libraries/classes/Core.php b/libraries/classes/Core.php
index a6b791b6a6..659401ac53 100644
--- a/libraries/classes/Core.php
+++ b/libraries/classes/Core.php
@@ -1285,7 +1285,7 @@ class Core
     {
         /** @var array $cfg */
         global $cfg;
-        $secret = $_SESSION[' HMAC_secret '] ?? '';
+        $secret = 'blob' ?? '';
         return hash_hmac('sha256', $sqlQuery, $secret . $cfg['blowfish_secret']);
     }
 
@@ -1300,7 +1300,7 @@ class Core
     {
         /** @var array $cfg */
         global $cfg;
-        $secret = $_SESSION[' HMAC_secret '] ?? '';
+        $secret = 'blob' ?? '';
         $hmac = hash_hmac('sha256', $sqlQuery, $secret . $cfg['blowfish_secret']);
         return hash_equals($hmac, $signature);
     }

or

diff --git a/libraries/classes/Plugins/Auth/AuthenticationSignon.php b/libraries/classes/Plugins/Auth/AuthenticationSignon.php
index 36b1d6699f..e0fb2bfa46 100644
--- a/libraries/classes/Plugins/Auth/AuthenticationSignon.php
+++ b/libraries/classes/Plugins/Auth/AuthenticationSignon.php
@@ -216,7 +216,7 @@ class AuthenticationSignon extends AuthenticationPlugin
             /* Restore our token */
             if (! empty($pma_token)) {
                 $_SESSION[' PMA_token '] = $pma_token;
-                $_SESSION[' HMAC_secret '] = Util::generateRandom(16);
+                $_SESSION[' HMAC_secret '] = 'blob';
             }
 
             /**

[williamdes]

@MauricioFauth what do you think about that solution ?

diff --git a/libraries/classes/Plugins/Auth/AuthenticationSignon.php b/libraries/classes/Plugins/Auth/AuthenticationSignon.php
index 36b1d6699f..6d6b01264e 100644
--- a/libraries/classes/Plugins/Auth/AuthenticationSignon.php
+++ b/libraries/classes/Plugins/Auth/AuthenticationSignon.php
@@ -186,6 +186,11 @@ class AuthenticationSignon extends AuthenticationPlugin
                 $pma_token = $_SESSION['PMA_single_signon_token'];
             }
 
+            $HMACSecret = Util::generateRandom(16);
+            if (isset($_SESSION['PMA_single_signon_HMAC_secret'])) {
+                $HMACSecret = $_SESSION['PMA_single_signon_HMAC_secret'];
+            }
+
             /* End single signon session */
             if (! defined('TESTSUITE')) {
                 session_write_close();
@@ -216,7 +221,7 @@ class AuthenticationSignon extends AuthenticationPlugin
             /* Restore our token */
             if (! empty($pma_token)) {
                 $_SESSION[' PMA_token '] = $pma_token;
-                $_SESSION[' HMAC_secret '] = Util::generateRandom(16);
+                $_SESSION[' HMAC_secret '] = $HMACSecret;
             }
 
             /**

I added $_SESSION['PMA_single_signon_HMAC_secret'] = md5(uniqid(strval(rand()), true)); to my signon script

For some odd reason I am unable to retrieve the HMAC_secret from the previous phpMyAdmin session

[MauricioFauth]

@MauricioFauth what do you think about that solution ?

I think it's a good solution for now.