1--TEST--
2MySQL 5.6 / MariaDB 10.4.3 EXPIRE PASSWORD protocol change
3--EXTENSIONS--
4mysqli
5--SKIPIF--
6<?php
7require_once('connect.inc');
8
9if (!$link = @my_mysqli_connect($host, $user, $passwd, $db, $port, $socket)) {
10    die(sprintf("SKIP Cannot connect to the server using host=%s, user=%s, passwd=***, dbname=%s, port=%s, socket=%s\n",
11        $host, $user, $db, $port, $socket));
12}
13
14if ($link->server_version < 50610)
15    die(sprintf("SKIP Needs MySQL 5.6.10 or newer, found MySQL %s\n", $link->server_info));
16
17if ($link->server_version >= 100000) {
18    if ($link->server_version < 100403)
19        die(sprintf("SKIP Needs MariaDB 10.4.3 or newer, found MariaDB %s\n", $link->server_info));
20    $result = $link->query("select @@disconnect_on_expired_password");
21    if (!$result)
22        die("SKIP Failed to query MariaDB @@disconnect_on_expired_password value");
23    $row = mysqli_fetch_row($result);
24    if ($row[0] == 0)
25        die("SKIP Cannot run in MariaDB @@disconnect_on_expired_password=OFF state");
26
27}
28
29if  (!$IS_MYSQLND && (mysqli_get_client_version() < 50610)) {
30    die(sprintf("SKIP Needs libmysql 5.6.10 or newer, found  %s\n", mysqli_get_client_version()));
31}
32
33mysqli_query($link, 'DROP USER expiretest');
34mysqli_query($link, 'DROP USER expiretest@localhost');
35
36if (!mysqli_query($link, 'CREATE USER expiretest IDENTIFIED BY \'expiredpassword\'') ||
37    !mysqli_query($link, 'CREATE USER expiretest@localhost IDENTIFIED BY \'expiredpassword\'')) {
38    printf("skip Cannot create second DB user [%d] %s", mysqli_errno($link), mysqli_error($link));
39    mysqli_close($link);
40    die("skip CREATE USER failed");
41}
42
43if (!mysqli_query($link, 'ALTER USER expiretest PASSWORD EXPIRE') ||
44    !mysqli_query($link, 'ALTER USER expiretest@localhost PASSWORD EXPIRE')) {
45    printf("skip Cannot modify second DB user [%d] %s", mysqli_errno($link), mysqli_error($link));
46    mysqli_close($link);
47    die("skip ALTER USER failed");
48}
49
50if (!$link->query("DROP TABLE IF EXISTS test") ||
51    !$link->query("CREATE TABLE test (id INT)") || !$link->query("INSERT INTO test(id) VALUES (1)"))
52    die(sprintf("SKIP [%d] %s\n", $link->errno, $link->error));
53
54
55
56if (!mysqli_query($link, sprintf("GRANT SELECT ON TABLE %s.test TO expiretest@'%%'", $db)) ||
57    !mysqli_query($link, sprintf("GRANT SELECT ON TABLE %s.test TO expiretest@'localhost'", $db))) {
58    printf("skip Cannot grant SELECT to user [%d] %s", mysqli_errno($link), mysqli_error($link));
59    mysqli_close($link);
60    die("skip GRANT failed");
61}
62?>
63--FILE--
64<?php
65    require_once('connect.inc');
66    require_once('table.inc');
67
68    /* default */
69    if (!$link = my_mysqli_connect($host, 'expiretest', 'expiredpassword', $db, $port, $socket)) {
70        printf("[001] Cannot connect [%d] %s\n",
71            mysqli_connect_errno(), mysqli_connect_error());
72    } else {
73        $link->query("SELECT id FROM test WHERE id = 1");
74        printf("[002] Connect should fail, [%d] %s\n", $link->errno, $link->error);
75    }
76
77    /* explicitly requesting default */
78    $link = mysqli_init();
79    $link->options(MYSQLI_OPT_CAN_HANDLE_EXPIRED_PASSWORDS, 0);
80    if (!my_mysqli_real_connect($link, $host, 'expiretest', 'expiredpassword', $db, $port, $socket)) {
81        printf("[003] Cannot connect [%d] %s\n",
82            mysqli_connect_errno(), mysqli_connect_error());
83    } else {
84        $link->query("SELECT id FROM test WHERE id = 1");
85        printf("[004] Connect should fail, [%d] %s\n", $link->errno, $link->error);
86    }
87
88    /* allow connect */
89    $link = mysqli_init();
90    $link->options(MYSQLI_OPT_CAN_HANDLE_EXPIRED_PASSWORDS, 1);
91    if (!my_mysqli_real_connect($link, $host, 'expiretest', 'expiredpassword', $db, $port, $socket)) {
92        printf("[005] Cannot connect [%d] %s\n",
93            mysqli_connect_errno(), mysqli_connect_error());
94    } else {
95        $link->query("SELECT id FROM test WHERE id = 1");
96        printf("[006] Connect allowed, query fail, [%d] %s\n", $link->errno, $link->error);
97        $link->close();
98    }
99
100    /* allow connect, fix pw */
101    $link = mysqli_init();
102    $link->options(MYSQLI_OPT_CAN_HANDLE_EXPIRED_PASSWORDS, 1);
103    if (!my_mysqli_real_connect($link, $host, 'expiretest', 'expiredpassword', $db, $port, $socket)) {
104        printf("[007] Cannot connect [%d] %s\n",
105            mysqli_connect_errno(), mysqli_connect_error());
106    } else {
107        if (!$link->query("SET PASSWORD='expiretest'")) {
108            $link->query("SET PASSWORD=PASSWORD('expiretest')");
109        }
110        printf("[008] Connect allowed, pw set, [%d] %s\n", $link->errno, $link->error);
111        if ($res = $link->query("SELECT id FROM test WHERE id = 1"))
112            var_dump($res->fetch_assoc());
113        $link->close();
114    }
115
116
117    /* check login */
118    if (!$link = my_mysqli_connect($host, 'expiretest', "expiretest", $db, $port, $socket)) {
119        printf("[001] Cannot connect [%d] %s\n",
120            mysqli_connect_errno(), mysqli_connect_error());
121    } else {
122        $link->query("SELECT id FROM test WHERE id = 1");
123        if ($res = $link->query("SELECT id FROM test WHERE id = 1"))
124            var_dump($res->fetch_assoc());
125        $link->close();
126    }
127
128
129
130    print "done!";
131?>
132--CLEAN--
133<?php
134    require_once("clean_table.inc");
135    mysqli_query($link, 'DROP USER expiretest');
136    mysqli_query($link, 'DROP USER expiretest@localhost');
137?>
138--EXPECTF--
139Warning: mysqli%sconnect(): (HY000/%d): %s in %s on line %d
140[001] Cannot connect [%d] %s
141
142Warning: mysqli%sconnect(): (HY000/%d): %s in %s on line %d
143[003] Cannot connect [%d] %s
144[006] Connect allowed, query fail, [1820] %s
145[008] Connect allowed, pw set, [0%A
146array(1) {
147  ["id"]=>
148  string(1) "1"
149}
150array(1) {
151  ["id"]=>
152  string(1) "1"
153}
154done!
155