GitHub Access Token became invalid

It seems like the GitHub access token used for retrieving details about this repository from GitHub became invalid. This might prevent certain types of inspections from being run (in particular, everything related to pull requests).
Please ask an admin of your repository to re-new the access token on this website.
Completed
Push — stable-3.1 ( 28f09e...905725 )
by Benjamin
03:04
created

bureau/class/m_mysql.php (1 issue)

Upgrade to new PHP Analysis Engine

These results are based on our legacy PHP analysis, consider migrating to our new PHP analysis engine instead. Learn more

1
<?php
2
3
/*
4
  $Id: m_mysql.php,v 1.35 2005/12/18 09:51:32 benjamin Exp $
5
  ----------------------------------------------------------------------
6
  AlternC - Web Hosting System
7
  Copyright (C) 2002 by the AlternC Development Team.
8
  http://alternc.org/
9
  ----------------------------------------------------------------------
10
  Based on:
11
  Valentin Lacambre's web hosting softwares: http://altern.org/
12
  ----------------------------------------------------------------------
13
  LICENSE
14
15
  This program is free software; you can redistribute it and/or
16
  modify it under the terms of the GNU General Public License (GPL)
17
  as published by the Free Software Foundation; either version 2
18
  of the License, or (at your option) any later version.
19
20
  This program is distributed in the hope that it will be useful,
21
  but WITHOUT ANY WARRANTY; without even the implied warranty of
22
  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
23
  GNU General Public License for more details.
24
25
  To read the license please visit http://www.gnu.org/copyleft/gpl.html
26
  ----------------------------------------------------------------------
27
  Original Author of file: Benjamin Sonntag
28
  Purpose of file: Manage mysql database for users.
29
  ----------------------------------------------------------------------
30
 */
31
32
/**
33
 * MySQL user database management for AlternC.
34
 * This class manage user's databases in MySQL, and user's MySQL accounts.
35
 * 
36
 * @copyright    AlternC-Team 2002-2005 http://alternc.org/
37
 */
38
class DB_users extends DB_Sql {
39
40
    var $Host, $HumanHostname, $User, $Password, $Client;
41
42
    /**
43
     * Creator
44
     */
45
    function __construct() { // Sometimes we need to create this object with empty parameters, but by default we fill them with those of the current user's DB
46
      global $cuid, $db, $err;
47
      
48
      $db->query("select db_servers.* from db_servers, membres where membres.uid= ? and membres.db_server_id=db_servers.id;", array($cuid));
49
      if (!$db->next_record()) {
50
	$err->raise('db_user', _("There are no databases in db_servers for this user. Please contact your administrator."));
51
	die();
52
      }
53
54
      # Create the object
55
      $this->HumanHostname = $db->f('name');
56
      $this->Host = $db->f('host');
57
      $this->User = $db->f('login');
58
      $this->Password = $db->f('password');
59
      $this->Client = $db->f('client');
60
      $this->Database = "mysql"; 
0 ignored issues
show
The property Database cannot be accessed from this context as it is declared private in class DB_Sql.

This check looks for access to properties that are not accessible from the current context.

If you need to make a property accessible to another context you can either raise its visibility level or provide an accessible getter in the defining class.

Loading history...
61
      
62
      parent::__construct("mysql", $db->f('host'), $db->f('login'), $db->f('password') );
63
      
64
    }
65
66
}
67
68
class m_mysql {
69
70
    var $dbus;
71
72
    /* --------------------------------------------------------------------------- */
73
74
    /** Constructor
75
     * m_mysql([$mid]) Constructeur de la classe m_mysql, initialise le membre concerne
76
     */
77
    function m_mysql() {
78
        global $cuid;
79
        if (!empty($cuid)) {
80
            $this->dbus = new DB_users();
81
        }
82
        variable_get('sql_allow_users_backups', 1, 'Set 1 to allow users to configure backup of their databases, 0 if you want do disable this feature. Warning: it will not stop configured backup made by sqlbackup.sh');
83
    }
84
85
    function reload_dbus() {
86
        $this->dbus = new DB_users();
87
    }
88
89
    function list_db_servers() {
90
        global $db;
91
        $db->query("select d.*, IFNULL(count(m.uid),0) as nb_users from db_servers d left join membres m on  d.id = m.db_server_id group by d.id,m.db_server_id order by d.name,d.id;");
92
        $c = array();
93
        while ($db->next_record()) {
94
            $c[] = $db->Record;
95
        }
96
        return $c;
97
    }
98
99
    function hook_menu() {
100
        global $quota;
101
        $q = $quota->getquota("mysql");
102
103
        $obj = array(
104
            'title' => _("MySQL"),
105
            'ico' => 'images/mysql.png',
106
            'link' => 'toggle',
107
            'pos' => 100,
108
            'links' => array(),
109
        );
110
111
        $obj['links'][] = array(
112
            'txt' => _("Databases"),
113
            'url' => "sql_list.php",
114
        );
115
        $obj['links'][] = array(
116
            'txt' => _("MySQL Users"),
117
            'url' => "sql_users_list.php",
118
        );
119
        if ($q["u"] > 0) {
120
            $obj['links'][] = array(
121
                'txt' => _("PhpMyAdmin"),
122
                'url' => "sql_pma_sso.php",
123
                'target' => '_blank',
124
            );
125
        }
126
        return $obj;
127
    }
128
129
    /* ----------------------------------------------------------------- */
130
131
    /**
132
     * Password kind used in this class (hook for admin class)
133
     */
134
    function alternc_password_policy() {
135
        return array("mysql" => "MySQL users");
136
    }
137
138
    /* --------------------------------------------------------------------------- */
139
140
    /** Get the list of the database for the current user.
141
     * @return array returns an associative array as follow : <br>
142
     *  "db" => database name "bck" => backup mode for this db 
143
     *  "dir" => Backup folder.
144
     *  Returns an array (empty) if no databases
145
     */
146
    function get_dblist() {
147
        global $db, $err, $bro, $cuid;
148
        $err->log("mysql", "get_dblist");
149
        $db->free();
150
        $db->query("SELECT login,pass,db, bck_mode, bck_dir FROM db WHERE uid= ? ORDER BY db;", array($cuid));
151
        $c = array();
152
        while ($db->next_record()) {
153
            list($dbu, $dbn) = split_mysql_database_name($db->f("db"));
154
            $c[] = array("db" => $db->f("db"), "name" => $db->f('db'), "bck" => $db->f("bck_mode"), "dir" => $db->f("bck_dir"), "login" => $db->f("login"), "pass" => $db->f("pass"));
155
        }
156
        return $c;
157
    }
158
159
    /* --------------------------------------------------------------------------- */
160
161
    /** Get the login and password of the special user able to connect to phpmyadmin
162
     * @return array returns an associative array with login and password 
163
     *  Returns FALSE if error
164
     */
165
    function php_myadmin_connect() {
166
        global $db, $cuid, $err;
167
        $err->log("mysql", "php_myadmin_connect");
168
        $db->query("SELECT dbu.name,dbu.password, dbs.host FROM dbusers dbu, db_servers dbs, membres m WHERE dbu.uid= ? and enable='ADMIN' and dbs.id=m.db_server_id and m.uid= ? ;", array($cuid, $cuid));
169
        if (!$db->num_rows()) {
170
            $err->raise("mysql", _("Cannot connect to PhpMyAdmin"));
171
            return false;
172
        }
173
        $db->next_record();
174
        $info = array(
175
            "login" => $db->f("name"),
176
            "pass" => $db->f("password"),
177
            "host" => $db->f("host")
178
        );
179
        return $info;
180
    }
181
182
    /* --------------------------------------------------------------------------- */
183
184
    /** Returns the details of a user's database.
185
     * $dbn is the name of the database (after the _) or nothing for the database "$user"
186
     * @return string returns an associative array as follow : 
187
     *  "db" => Name of the database 
188
     *  "bck" => Current bckup mode 
189
     *  "dir" => Backup directory
190
     *  "size" => Size of the database (in bytes)
191
     *  "pass" => Password of the user
192
     *  "history" => Number of backup we keep
193
     *  "gzip" => Does we compress the dumps ?
194
     *  Returns FALSE if the user has no database of if the database does not exist.
195
     */
196
    function get_mysql_details($dbn) {
197
        global $db, $err, $cuid;
198
        $root = getuserpath();
199
        $err->log("mysql", "get_mysql_details");
200
        $pos = strpos($dbn, '_');
201
        if ($pos === false) {
202
            $dbname = $dbn;
203
        } else {
204
            $dbncomp = explode('_', $dbn);
205
            $dbname = $dbn;
206
            $dbn = $dbncomp[1];
207
        }
208
        $size = $this->get_db_size($dbname);
209
        $db->query("SELECT login,pass,db, bck_mode, bck_gzip, bck_dir, bck_history FROM db WHERE uid= ? AND db= ?;", array($cuid, $dbname));
210
        if (!$db->num_rows()) {
211
            $err->raise("mysql", _("Database %s not found"), $dbn);
212
            return array("enabled" => false);
213
        }
214
        $db->next_record();
215
        list($dbu, $dbn) = split_mysql_database_name($db->f("db"));
216
        return array("enabled" => true, "login" => $db->f("login"), "db" => $db->f("db"), "name" => $dbn, "bck" => $db->f("bck_mode"), "dir" => substr($db->f("bck_dir"), strlen($root)), "size" => $size, "pass" => $db->f("pass"), "history" => $db->f("bck_history"), "gzip" => $db->f("bck_gzip"));
217
    }
218
219
    /* --------------------------------------------------------------------------- */
220
221
    /** Create a new database for the current user.
222
     * @param $dbn string Database name ($user_$dbn is the mysql db name)
223
     * @return boolean if the database $user_$db has been successfully created, or FALSE if 
224
     * an error occured, such as over quota user.
225
     */
226
    function add_db($dbn) {
227
        global $db, $err, $quota, $cuid;
228
        $err->log("mysql", "add_db", $dbn);
229
        $password_user = "";
230
        if (!$quota->cancreate("mysql")) {
231
            $err->raise("mysql", _("Your databases quota is over. You cannot create more databases"));
232
            return false;
233
        }
234
        $pos = strpos($dbn, '_');
235
        if ($pos === false) {
236
            $dbname = $dbn;
237
        } else {
238
            $dbncomp = explode('_', $dbn);
239
            $dbname = $dbn;
240
            $dbn = $dbncomp[1];
241
            if (empty($dbn)) { // If nothing after the '_'
242
                $err->raise("mysql", _("Database can't have empty suffix"));
243
                return false;
244
            }
245
        }
246
        if (!preg_match("#^[0-9a-z]*$#", $dbn)) {
247
            $err->raise("mysql", _("Database name can contain only letters and numbers"));
248
            return false;
249
        }
250
251
	$len=variable_get("sql_max_database_length", 64);
252
        if (strlen($dbname) > $len) {
253
            $err->raise("mysql", _("Database name cannot exceed %d characters"), $len);
254
            return false;
255
        }
256
        $db->query("SELECT * FROM db WHERE db= ? ;", array($dbname));
257
        if ($db->num_rows()) {
258
            $err->raise("mysql", _("Database %s already exists"), $dbn);
259
            return false;
260
        }
261
262
        $db->query("SELECT name from dbusers where name= ? and enable='ACTIVATED' ;", array($dbname));
263
        if (!$db->num_rows()) {
264
            $password_user = create_pass(8);
265
            if (!$this->add_user($dbn, $password_user, $password_user)) {
266
                
267
            }
268
        }
269
270
        //checking for the phpmyadmin user
271
        $db->query("SELECT * FROM dbusers WHERE uid= ? AND enable='ADMIN';", array($cuid));
272
        if ($db->num_rows()) {
273
            $db->next_record();
274
            $myadm = $db->f("name");
275
            $password = $db->f("password");
276
        } else {
277
            $err->raise("mysql", _("There is a problem with the special PhpMyAdmin user. Contact the administrator"));
278
            return false;
279
        }
280
281
        //Grant the special user every rights.
282
        if ($this->dbus->exec("CREATE DATABASE $dbname;")) { // secured: dbname is checked against ^[0-9a-z]*$
283
            $err->log("mysql", "add_db_succes", $dbn);
284
            // Ok, database does not exist, quota is ok and dbname is compliant. Let's proceed
285
            $db->query("INSERT INTO db (uid,login,pass,db,bck_mode) VALUES (?, ?, ?, ? ,0)", array($cuid, $myadm, $password, $dbname));
286
            $dbuser = $dbname;
287
            $dbname = str_replace('_', '\_', $dbname);
288
            $this->grant($dbname, $myadm, "ALL PRIVILEGES", $password);
289
            if (!empty($password_user)) {
290
                $this->grant($dbname, $dbuser, "ALL PRIVILEGES", $password_user);
291
            }
292
            $this->dbus->query("FLUSH PRIVILEGES;");
293
            return true;
294
        } else {
295
            $err->log("mysql", "add_db", $dbn);
296
            $err->raise("mysql", _("An error occured. The database could not be created"));
297
            return false;
298
        }
299
    }
300
301
    /* --------------------------------------------------------------------------- */
302
303
    /** Delete a database for the current user.
304
     * @param $dbname string Name of the database to delete. The db name is $user_$dbn
305
     * @return boolean if the database $user_$db has been successfully deleted, or FALSE if 
306
     *  an error occured, such as db does not exist.
307
     */
308
    function del_db($dbname) {
309
        global $db, $err, $cuid;
310
        $err->log("mysql", "del_db", $dbname);
311
        $db->query("SELECT uid FROM db WHERE db= ?;", array($dbname));
312
        if (!$db->next_record()) {
313
            $err->raise("mysql", _("The database was not found. I can't delete it"));
314
            return false;
315
        }
316
317
        // Ok, database exists and dbname is compliant. Let's proceed
318
        $db->query("DELETE FROM size_db WHERE db ?;", array($dbname));
319
        $db->query("DELETE FROM db WHERE uid= ? AND db= ? ;", array($cuid, $dbname));
320
        $this->dbus->query("DROP DATABASE $dbname;");
321
322
        $db_esc = str_replace('_', '\_', $dbname);
323
        $this->dbus->query("DELETE FROM mysql.db WHERE Db= ? ;",    array($db_esc));
324
325
        #We test if the user created with the database is associated with more than 1 database.
326
        $this->dbus->query("select User from mysql.db where User= ? ;", array($dbname));
327
        if (($this->dbus->num_rows()) == 0) {
328
            #If not we can delete it.
329
            $this->del_user($dbname);
330
        }
331
        return true;
332
    }
333
334
    /* --------------------------------------------------------------------------- */
335
336
    /** Set the backup parameters for the database $db
337
     * @param $dbn string database name
338
     * @param $bck_mode integer Backup mode (0 = none 1 = daily 2 = weekly)
339
     * @param $bck_history integer How many backup should we keep ?
340
     * @param $bck_gzip boolean shall we compress the backup ?
341
     * @param $bck_dir string Directory relative to the user account where the backup will be stored
342
     * @return boolean true if the backup parameters has been successfully changed, false if not.
343
     */
344
    function put_mysql_backup($dbn, $bck_mode, $bck_history, $bck_gzip, $bck_dir) {
345
        global $db, $err, $bro, $cuid;
346
        $err->log("mysql", "put_mysql_backup");
347
348
        if (!variable_get('sql_allow_users_backups')) {
349
            $err->raise("mysql", _("User aren't allowed to configure their backups"));
350
            return false;
351
        }
352
353
        $pos = strpos($dbn, '_');
354
        if ($pos === false) {
355
            $dbname = $dbn;
356
        } else {
357
            $dbncomp = explode('_', $dbn);
358
            $dbname = $dbn;
359
            $dbn = $dbncomp[1];
360
        }
361
        if (!preg_match("#^[0-9a-z]*$#", $dbn)) {
362
            $err->raise("mysql", _("Database name can contain only letters and numbers"));
363
            return false;
364
        }
365
        $db->query("SELECT * FROM db WHERE uid= ? AND db= ? ;", array($cuid, $dbname));
366
        if (!$db->num_rows()) {
367
            $err->raise("mysql", _("Database %s not found"), $dbn);
368
            return false;
369
        }
370
        $db->next_record();
371
        $bck_mode = intval($bck_mode);
372
        $bck_history = intval($bck_history);
373
        if ($bck_gzip) {
374
            $bck_gzip = "1";
375
        } else {
376
            $bck_gzip = "0";
377
        }
378
        if (!$bck_mode) {
379
            $bck_mode = "0";
380
        }
381
        if (!$bck_history) {
382
            $err->raise("mysql", _("You have to choose how many backups you want to keep"));
383
            return false;
384
        }
385
        if (($bck_dir = $bro->convertabsolute($bck_dir, 0)) === false) { // return a full path or FALSE
386
            $err->raise("mysql", _("Directory does not exist"));
387
            return false;
388
        }
389
        $db->query("UPDATE db SET bck_mode= ? , bck_history= ?, bck_gzip= ?, bck_dir= ? WHERE uid= ? AND db= ? ;", array($bck_mode, $bck_history, $bck_gzip, $bck_dir, $cuid, $dbname));
390
        return true;
391
    }
392
393
    /* --------------------------------------------------------------------------- */
394
395
    /** Change the password of the user in MySQL
396
     * @param $password string new password (cleartext)
397
     * @return boolean TRUE if the password has been successfully changed, FALSE else.
398
     */
399
    function put_mysql_details($password) {
400
        global $db, $err, $cuid, $admin;
401
        $err->log("mysql", "put_mysql_details");
402
        $db->query("SELECT * FROM db WHERE uid= ?;", array($cuid));
403
        if (!$db->num_rows()) {
404
            $err->raise("mysql", _("Database not found"));
405
            return false;
406
        }
407
        $db->next_record();
408
        $login = $db->f("login");
409
410
        if (!$password) {
411
            $err->raise("mysql", _("The password is mandatory"));
412
            return false;
413
        }
414
415
	$len=variable_get("sql_max_username_length", 16);
416
        if (strlen($password) > $len) {
417
            $err->raise("mysql", _("MySQL password cannot exceed %d characters"), $len);
418
            return false;
419
        }
420
421
        // Check this password against the password policy using common API : 
422
        if (is_callable(array($admin, "checkPolicy"))) {
423
            if (!$admin->checkPolicy("mysql", $login, $password)) {
424
                return false; // The error has been raised by checkPolicy()
425
            }
426
        }
427
428
        // Update all the "pass" fields for this user : 
429
        $db->query("UPDATE db SET pass= ? WHERE uid= ?;", array($password, $cuid));
430
        $this->dbus->query("SET PASSWORD FOR " .$login . "@" . $this->dbus->Client . "  = PASSWORD(?);", array($password));
431
        return true;
432
    }
433
434
    /**
435
     * Function used to grant SQL rights to users:
436
     * @base :database 
437
     * @user : database user
438
     * @rights : rights to apply ( optional, every rights apply given if missing
439
     * @pass : user password ( optional, if not given the pass stays the same, else it takes the new value )
440
     * @table : sql tables to apply rights
441
     * */
442
    function grant($base, $user, $rights = null, $pass = null, $table = '*') {
443
        global $err, $db;
444
        $err->log("mysql", "grant", $base . "-" . $rights . "-" . $user);
445
446
        if (!preg_match("#^[0-9a-z_\\*\\\\]*$#", $base)) {
447
            $err->raise("mysql", _("Database name can contain only letters and numbers"));
448
            return false;
449
        } elseif (!$this->dbus->query("select db from db where db= ?;", array($base))) {
450
            $err->raise("mysql", _("Database not found"));
451
            return false;
452
        }
453
454
        if ($rights == null) {
455
            $rights = 'ALL PRIVILEGES';
456
        } elseif (!preg_match("#^[a-zA-Z,\s]*$#", $rights)) {
457
            $err->raise("mysql", _("Databases rights are not correct"));
458
            return false;
459
        }
460
461
        if (!preg_match("#^[0-9a-z]#", $user)) {
462
            $err->raise("mysql", _("The username can contain only letters and numbers."));
463
            return false;
464
        }
465
        $db->query("select name from dbusers where name= ? ;", array($user));
466
467
        if (!$db->num_rows()) {
468
            $err->raise("mysql", _("Database user not found"));
469
            return false;
470
        }
471
472
        $grant = "grant " . $rights . " on " . $base . "." . $table . " to " . $db->quote($user) . "@" . $db->quote($this->dbus->Client);
473
474
        if ($pass) {
475
            $grant .= " identified by " . $db->quote($pass) . ";";
476
        } else {
477
            $grant .= ";";
478
        }
479
        if (!$this->dbus->query($grant)) {
480
            $err->raise("mysql", _("Could not grant rights"));
481
            return false;
482
        }
483
        return true;
484
    }
485
486
    /* ----------------------------------------------------------------- */
487
488
    /** Restore a sql database.
489
     * @param $file string The filename, relative to the user root dir, which contains a sql dump
490
     * @param $stdout boolean shall-we dump the error to stdout ? 
491
     * @param $id integer The ID of the database to dump to.
492
     * @return boolean TRUE if the database has been restored, or FALSE if an error occurred
493
     */
494
    function restore($file, $stdout, $id) {
495
        global $err, $bro;
496
        if (empty($file)) {
497
            $err->raise("mysql", _("No file specified"));
498
            return false;
499
        }
500
        if (!$r = $this->get_mysql_details($id)) {
501
            return false;
502
        }
503
        if (!($fi = $bro->convertabsolute($file, 0))) {
504
            $err->raise("mysql", _("File not found"));
505
            return false;
506
        }
507
        if (!file_exists($fi)) {
508
            $err->raise("mysql", _("File not found"));
509
            return false;
510
        }
511
512
        if (substr($fi, -3) == ".gz") {
513
            $exe = "/bin/gzip -d -c <" . escapeshellarg($fi) . " | /usr/bin/mysql -h" . escapeshellarg($this->dbus->Host) . " -u" . escapeshellarg($r["login"]) . " -p" . escapeshellarg($r["pass"]) . " " . escapeshellarg($r["db"]);
514
        } elseif (substr($fi, -4) == ".bz2") {
515
            $exe = "/usr/bin/bunzip2 -d -c <" . escapeshellarg($fi) . " | /usr/bin/mysql -h" . escapeshellarg($this->dbus->Host) . " -u" . escapeshellarg($r["login"]) . " -p" . escapeshellarg($r["pass"]) . " " . escapeshellarg($r["db"]);
516
        } else {
517
            $exe = "/usr/bin/mysql -h" . escapeshellarg($this->dbus->Host) . " -u" . escapeshellarg($r["login"]) . " -p" . escapeshellarg($r["pass"]) . " " . escapeshellarg($r["db"]) . " <" . escapeshellarg($fi);
518
        }
519
        $exe .= " 2>&1";
520
521
        echo "<code><pre>";
522
        $ret = 0;
523
        if ($stdout) {
524
            passthru($exe, $ret);
525
        } else {
526
            exec($exe, $ret);
527
        }
528
        echo "</pre></code>";
529
        if ($ret != 0) {
530
            return false;
531
        } else {
532
            return true;
533
        }
534
    }
535
536
    /* ----------------------------------------------------------------- */
537
538
    /** Get the size of a database
539
     * @param $dbname name of the database
540
     * @return integer database size
541
     * @access private
542
     */
543
    function get_db_size($dbname) {
544
        $this->dbus->query("SHOW TABLE STATUS FROM $dbname;");
545
        $size = 0;
546
        while ($this->dbus->next_record()) {
547
            $size += $this->dbus->f('Data_length') + $this->dbus->f('Index_length');
548
            if ($this->dbus->f('Engine') != 'InnoDB') {
549
                $size += $this->dbus->f('Data_free');
550
            }
551
        }
552
        return $size;
553
    }
554
555
    /* ------------------------------------------------------------ */
556
557
    /**
558
     * Returns the list of database users of an account
559
     * */
560
    function get_userslist($all = null) {
561
        global $db, $err, $cuid;
562
        $err->log("mysql", "get_userslist");
563
        $c = array();
564
        if (!$all) {
565
            $db->query("SELECT name FROM dbusers WHERE uid= ? and enable not in ('ADMIN','HIDDEN') ORDER BY name;", array($cuid));
566
        } else {
567
            $db->query("SELECT name FROM dbusers WHERE uid= ? ORDER BY name;", array($cuid));
568
        }
569
        while ($db->next_record()) {
570
            $pos = strpos($db->f("name"), "_");
571
            if ($pos === false) {
572
                $c[] = array("name" => ($db->f("name")));
573
            } else {
574
                $c[] = array("name" => ($db->f("name")));
575
                //$c[]=array("name"=>substr($db->f("name"),strpos($db->f("name"),"_")+1));
576
            }
577
        }
578
579
        return $c;
580
    }
581
582
    function get_defaultsparam($dbn) {
583
        global $db, $err, $cuid;
584
        $err->log("mysql", "getdefaults");
585
586
        $dbu = $dbn;
587
        $r = array();
588
        $dbn = str_replace('_', '\_', $dbn);
589
        $this->dbus->query("Select * from mysql.db where Db= ? and User!= ? ;", array($dbn, $cuid."_myadm"));
590
591
        if (!$this->dbus->num_rows()) {
592
            $err->raise("mysql",_("Database not found"));
593
            return false;
594
        }
595
        while ($this->dbus->next_record()) {
596
            $variable = $this->dbus->Record;
597
            if ($variable['User'] == $dbu) {
598
                $r['Host'] = $this->dbus->f('Host');
599
600
                if ($this->dbus->f('Select_priv') !== "Y") {
601
                    return $r;
602
                }
603
                if ($this->dbus->f('Insert_priv') !== "Y") {
604
                    return $r;
605
                }
606
                if ($this->dbus->f('Update_priv') !== "Y") {
607
                    return $r;
608
                }
609
                if ($this->dbus->f('Delete_priv') !== "Y") {
610
                    return $r;
611
                }
612
                if ($this->dbus->f('Create_priv') !== "Y") {
613
                    return $r;
614
                }
615
                if ($this->dbus->f('Drop_priv') !== "Y") {
616
                    return $r;
617
                }
618
                if ($this->dbus->f('References_priv') !== "Y") {
619
                    return $r;
620
                }
621
                if ($this->dbus->f('Index_priv') !== "Y") {
622
                    return $r;
623
                }
624
                if ($this->dbus->f('Alter_priv') !== "Y") {
625
                    return $r;
626
                }
627
                if ($this->dbus->f('Create_tmp_table_priv') !== "Y") {
628
                    return $r;
629
                }
630
                if ($this->dbus->f('Lock_tables_priv') !== "Y") {
631
                    return $r;
632
                }
633
                if ($this->dbus->f('Create_view_priv') !== "Y") {
634
                    return $r;
635
                }
636
                if ($this->dbus->f('Show_view_priv') !== "Y") {
637
                    return $r;
638
                }
639
                if ($this->dbus->f('Create_routine_priv') !== "Y") {
640
                    return $r;
641
                }
642
                if ($this->dbus->f('Alter_routine_priv') !== "Y") {
643
                    return $r;
644
                }
645
                if ($this->dbus->f('Execute_priv') !== "Y") {
646
                    return $r;
647
                }
648
                if ($this->dbus->f('Event_priv') !== "Y") {
649
                    return $r;
650
                }
651
                if ($this->dbus->f('Trigger_priv') !== "Y") {
652
                    return $r;
653
                }
654
            }
655
        } //endwhile
656
        if (!count($r)) {
657
            $err->raise("mysql",_("Database not found")." (2)");
658
            return false;
659
        }
660
        if (!$db->query("SELECT name,password from dbusers where name= ? ;", array($dbu))) {
661
            $err->raise("mysql",_("Database not found")." (3)");
662
            return false;
663
        }
664
665
        if (!$db->num_rows()) {
666
            $err->raise("mysql",_("Database not found")." (4)");
667
            return false;
668
        }
669
        $db->next_record();
670
        $r['user'] = $db->f('name');
671
        $r['password'] = $db->f('password');
672
        return $r;
673
    }
674
675
    /* ------------------------------------------------------------ */
676
677
    /**
678
     * Create a new user in MySQL rights tables
679
     * @param $usern the username (we will add _[alternc-account] to it)
680
     * @param string $password The password for this username
681
     * @param string $passconf The password confirmation
682
     * @return boolean if the user has been created in MySQL or FALSE if an error occurred
683
     * */
684
    function add_user($usern, $password, $passconf) {
685
        global $db, $err, $mem, $cuid, $admin;
686
        $err->log("mysql", "add_user", $usern);
687
688
        $usern = trim($usern);
689
        $login = $mem->user["login"];
690
        if ($login != $usern) {
691
            $user = $login . "_" . $usern;
692
        } else {
693
            $user = $usern;
694
        }
695
696
        if (!$usern) {
697
            $err->raise("mysql", _("The username is mandatory"));
698
            return false;
699
        }
700
        if (!$password) {
701
            $err->raise("mysql", _("The password is mandatory"));
702
            return false;
703
        }
704
        if (!preg_match("#^[0-9a-z]#", $usern)) {
705
            $err->raise("mysql", _("The username can contain only letters and numbers"));
706
            return false;
707
        }
708
709
        // We check the length of the COMPLETE username, not only the part after _
710
        $len=variable_get("sql_max_username_length", 16);
711
        if (strlen($user) > $len) {
712
            $err->raise("mysql", _("MySQL username cannot exceed %d characters"), $len);
713
            return false;
714
        }
715
        $db->query("SELECT * FROM dbusers WHERE name= ? ;", array($user));
716
        if ($db->num_rows()) {
717
            $err->raise("mysql", _("The database user already exists"));
718
            return false;
719
        }
720
        if ($password != $passconf || !$password) {
721
            $err->raise("mysql", _("The passwords do not match"));
722
            return false;
723
        }
724
725
        // Check this password against the password policy using common API : 
726
        if (is_callable(array($admin, "checkPolicy"))) {
727
            if (!$admin->checkPolicy("mysql", $user, $password)) {
728
                return false; // The error has been raised by checkPolicy()
729
            }
730
        }
731
732
        // We add him to the user table 
733
        $db->query("INSERT INTO dbusers (uid,name,password,enable) VALUES( ?, ?, ?, 'ACTIVATED');", array($cuid, $user, $password));
734
        
735
        $this->grant("*", $user, "USAGE", $pass);
736
        return true;
737
    }
738
739
    /* ------------------------------------------------------------ */
740
741
    /**
742
     * Change a user's MySQL password
743
     * @param $usern the username 
744
     * @param $password The password for this username
745
     * @param $passconf The password confirmation
746
     * @return boolean if the password has been changed in MySQL or FALSE if an error occurred
747
     * */
748
    function change_user_password($usern, $password, $passconf) {
749
        global $db, $err, $cuid, $admin;
750
        $err->log("mysql", "change_user_pass", $usern);
751
752
        $usern = trim($usern);
753
        if ($password != $passconf || !$password) {
754
            $err->raise("mysql", _("The passwords do not match"));
755
            return false;
756
        }
757
758
        // Check this password against the password policy using common API : 
759
        if (is_callable(array($admin, "checkPolicy"))) {
760
            if (!$admin->checkPolicy("mysql", $usern, $password)) {
761
                return false; // The error has been raised by checkPolicy()
762
            }
763
        }
764
        $this->dbus->query("SET PASSWORD FOR " . $db->quote($usern) . "@" . $db->quote($this->dbus->Client) . " = PASSWORD(?);", array($password));
765
        $db->query("UPDATE dbusers set password= ? where name= ? and uid= ? ;", array($password, $usern, $cuid));
766
        return true;
767
    }
768
769
    /* ------------------------------------------------------------ */
770
771
    /**
772
     * Delete a user in MySQL rights tables
773
     * @param $user the username (we will add "[alternc-account]_" to it) to delete
774
     * @param integer $all
775
     * @return boolean if the user has been deleted in MySQL or FALSE if an error occurred
776
     * */
777
    function del_user($user, $all = false) {
778
        global $db, $err, $cuid;
779
        $err->log("mysql", "del_user", $user);
780
        if (!preg_match("#^[0-9a-z]#", $user)) {
781
            $err->raise("mysql", _("The username can contain only letters and numbers"));
782
            return false;
783
        }
784
        if (!$all) {
785
            $db->query("SELECT name FROM dbusers WHERE name= ? and enable not in ('ADMIN','HIDDEN');", array($user));
786
        } else {
787
            $db->query("SELECT name FROM dbusers WHERE uid= ? ;", array($cuid));
788
        }
789
790
        if (!$db->num_rows()) {
791
            $err->raise("mysql", _("The username was not found"));
792
            return false;
793
        }
794
        $db->next_record();
795
        $login = $db->f("name");
796
797
        // Ok, database exists and dbname is compliant. Let's proceed
798
        $this->dbus->query("REVOKE ALL PRIVILEGES ON *.* FROM " . $db->quote($user) . "@" . $db->quote($this->dbus->Client) . ";");
799
        $this->dbus->query("DELETE FROM mysql.db WHERE User= ? AND Host= ? ;", array($user, $this->dbus->Client));
800
        $this->dbus->query("DELETE FROM mysql.user WHERE User= ? AND Host= ? ;", array($user, $this->dbus->Client));
801
        $this->dbus->query("FLUSH PRIVILEGES");
802
803
        $db->query("DELETE FROM dbusers WHERE uid= ? AND name= ? ;", array($cuid, $user));
804
        return true;
805
    }
806
807
    /* ------------------------------------------------------------ */
808
809
    /**
810
     * Return the list of the database rights of user $user
811
     * @param $user the username 
812
     * @return array An array of database name and rights
813
     * */
814
    function get_user_dblist($user) {
815
        global $db, $err;
816
817
        $this->dbus->query("SELECT * FROM mysql.user WHERE User= ? AND Host= ? ;", array($user, $this->dbus->Client));
818
        if (!$this->dbus->next_record()) {
819
            $err->raise('mysql', _("This user does not exist in the MySQL/User database"));
820
            return false;
821
        }
822
823
        $r = array();
824
        $db->free();
825
        $dblist = $this->get_dblist();
826
	foreach ($dblist as $tab) {
827
            $this->dbus->query("SELECT * FROM mysql.db WHERE User= ? AND Host= ? AND Db= ? ;", array($user, $this->dbus->Client, $tab["db"]));
828
            if ($this->dbus->next_record()) {
829
                $r[] = array("db" => $tab["db"], "select" => $this->dbus->f("Select_priv"), "insert" => $this->dbus->f("Insert_priv"), "update" => $this->dbus->f("Update_priv"), "delete" => $this->dbus->f("Delete_priv"), "create" => $this->dbus->f("Create_priv"), "drop" => $this->dbus->f("Drop_priv"), "references" => $this->dbus->f("References_priv"), "index" => $this->dbus->f("Index_priv"), "alter" => $this->dbus->f("Alter_priv"), "create_tmp" => $this->dbus->f("Create_tmp_table_priv"), "lock" => $this->dbus->f("Lock_tables_priv"),
830
                    "create_view" => $this->dbus->f("Create_view_priv"),
831
                    "show_view" => $this->dbus->f("Show_view_priv"),
832
                    "create_routine" => $this->dbus->f("Create_routine_priv"),
833
                    "alter_routine" => $this->dbus->f("Alter_routine_priv"),
834
                    "execute" => $this->dbus->f("Execute_priv"),
835
                    "event" => $this->dbus->f("Event_priv"),
836
                    "trigger" => $this->dbus->f("Trigger_priv")
837
                );
838
            } else {
839
                $r[] = array("db" => $tab['db'], "select" => "N", "insert" => "N", "update" => "N", "delete" => "N", "create" => "N", "drop" => "N", "references" => "N", "index" => "N", "alter" => "N", "create_tmp" => "N", "lock" => "N", "create_view" => "N", "show_view" => "N", "create_routine" => "N", "alter_routine" => "N", "execute" => "N", "event" => "N", "trigger" => "N");
840
            }
841
        }
842
        return $r;
843
    }
844
845
    /* ------------------------------------------------------------ */
846
847
    /**
848
     * Set the access rights of user $user to database $dbn to be rights $rights
849
     * @param $user the username to give rights to
850
     * @param $dbn The database to give rights to
851
     * @param $rights The rights as an array of MySQL keywords (insert, select ...)
852
     * @return boolean TRUE if the rights has been applied or FALSE if an error occurred
853
     * 
854
     * */
855
    function set_user_rights($user, $dbn, $rights) {
856
        global $err;
857
        $err->log("mysql", "set_user_rights");
858
859
        // On genere les droits en fonction du tableau de droits
860
        $strrights = "";
861
        for ($i = 0; $i < count($rights); $i++) {
862
            switch ($rights[$i]) {
863
                case "select":
864
                    $strrights.="SELECT,";
865
                    break;
866
                case "insert":
867
                    $strrights.="INSERT,";
868
                    break;
869
                case "update":
870
                    $strrights.="UPDATE,";
871
                    break;
872
                case "delete":
873
                    $strrights.="DELETE,";
874
                    break;
875
                case "create":
876
                    $strrights.="CREATE,";
877
                    break;
878
                case "drop":
879
                    $strrights.="DROP,";
880
                    break;
881
                case "references":
882
                    $strrights.="REFERENCES,";
883
                    break;
884
                case "index":
885
                    $strrights.="INDEX,";
886
                    break;
887
                case "alter":
888
                    $strrights.="ALTER,";
889
                    break;
890
                case "create_tmp":
891
                    $strrights.="CREATE TEMPORARY TABLES,";
892
                    break;
893
                case "lock":
894
                    $strrights.="LOCK TABLES,";
895
                    break;
896
                case "create_view":
897
                    $strrights.="CREATE VIEW,";
898
                    break;
899
                case "show_view":
900
                    $strrights.="SHOW VIEW,";
901
                    break;
902
                case "create_routine":
903
                    $strrights.="CREATE ROUTINE,";
904
                    break;
905
                case "alter_routine":
906
                    $strrights.="ALTER ROUTINE,";
907
                    break;
908
                case "execute":
909
                    $strrights.="EXECUTE,";
910
                    break;
911
                case "event":
912
                    $strrights.="EVENT,";
913
                    break;
914
                case "trigger":
915
                    $strrights.="TRIGGER,";
916
                    break;
917
            }
918
        }
919
920
        // We reset all user rights on this DB : 
921
        $this->dbus->query("SELECT * FROM mysql.db WHERE User = ? AND Db = ?;", array($user, $dbn));
922
923
        if ($this->dbus->num_rows()) {
924
            $this->dbus->query("REVOKE ALL PRIVILEGES ON ".$dbn.".* FROM ".$this->dbus->quote($user)."@" . $this->dbus->quote($this->dbus->Client) . ";");
925
        }
926
        if ($strrights) {
927
            $strrights = substr($strrights, 0, strlen($strrights) - 1);
928
            $this->grant($dbn, $user, $strrights);
929
        }
930
        $this->dbus->query("FLUSH PRIVILEGES");
931
        return TRUE;
932
    }
933
934
    function available_sql_rights() {
935
        return Array('select', 'insert', 'update', 'delete', 'create', 'drop', 'references', 'index', 'alter', 'create_tmp', 'lock', 'create_view', 'show_view', 'create_routine', 'alter_routine', 'execute', 'event', 'trigger');
936
    }
937
938
    /* ----------------------------------------------------------------- */
939
940
    /** Hook function called by the lxc class to set mysql_host and port 
941
     * parameters 
942
     * @access private
943
     */
944
    function hook_lxc_params($params) {
945
        global $err;
946
        $err->log("mysql", "alternc_get_quota");
947
        $p = array();
948
        if (isset($this->dbus["Host"]) && $this->dbus["Host"] != "") {
949
            $p["mysql_host"] = $this->dbus["Host"];
950
            $p["mysql_port"] = 3306;
951
        }
952
        return $p;
953
    }
954
955
    /* ----------------------------------------------------------------- */
956
957
    /** Hook function called by the quota class to compute user used quota
958
     * Returns the used quota for the $name service for the current user.
959
     * @param $name string name of the quota
960
     * @return integer the number of service used or false if an error occured
961
     * @access private
962
     */
963
    function hook_quota_get() {
964
        global $err;
965
        $err->log("mysql", "alternc_get_quota");
966
        $q = Array("name" => "mysql", "description" => _("MySQL Databases"), "used" => 0);
967
        $c = $this->get_dblist();
968
        if (is_array($c)) {
969
            $q['used'] = count($c);
970
        }
971
        return $q;
972
    }
973
974
    /* ----------------------------------------------------------------- */
975
976
    /** Hook function called when a user is created.
977
     * AlternC's standard function that create a member
978
     * @access private
979
     */
980
    function alternc_add_member() {
981
        global $db, $err, $cuid, $mem;
982
        $err->log("mysql", "alternc_add_member");
983
        //checking for the phpmyadmin user
984
        $db->query("SELECT name,password FROM dbusers WHERE uid= ? AND Type='ADMIN';", array($cuid));
985
        if ($db->num_rows()) {
986
            $myadm = $db->f("name");
987
            $password = $db->f("password");
988
        } else {
989
            $myadm = $cuid . "_myadm";
990
            $password = create_pass(8);
991
        }
992
993
        $db->query("INSERT INTO dbusers (uid,name,password,enable) VALUES (?, ?, ?, 'ADMIN');", array($cuid, $myadm, $password));
994
995
        return true;
996
    }
997
998
    /* ----------------------------------------------------------------- */
999
1000
    /** Hook function called when a user is deleted.
1001
     * AlternC's standard function that delete a member
1002
     * @access private
1003
     */
1004
    function alternc_del_member() {
1005
        global $err;
1006
        $err->log("mysql", "alternc_del_member");
1007
        $c = $this->get_dblist();
1008
        if (is_array($c)) {
1009
            for ($i = 0; $i < count($c); $i++) {
1010
                $this->del_db($c[$i]["name"]);
1011
            }
1012
        }
1013
        $d = $this->get_userslist(1);
1014
        if (!empty($d)) {
1015
            for ($i = 0; $i < count($d); $i++) {
1016
                $this->del_user($d[$i]["name"], 1);
1017
            }
1018
        }
1019
        return true;
1020
    }
1021
1022
    /* ----------------------------------------------------------------- */
1023
1024
    /** Hook function called when a user is logged out.
1025
     * We just remove the cookie created in admin/sql_admin.php
1026
      a @access private
1027
     */
1028
    function alternc_del_session() {
1029
        $_SESSION['PMA_single_signon_user'] = '';
1030
        $_SESSION['PMA_single_signon_password'] = '';
1031
        $_SESSION['PMA_single_signon_host'] = '';
1032
    }
1033
1034
    /* ----------------------------------------------------------------- */
1035
1036
    /**
1037
     * Exporte all the mysql information of an account
1038
     * @access private
1039
     * EXPERIMENTAL 'sid' function ;) 
1040
     */
1041
    function alternc_export_conf() {
1042
        //TODO don't work with separated sql server for dbusers
1043
        global $db, $err, $cuid;
1044
        $err->log("mysql", "export");
1045
        $db->query("SELECT login, pass, db, bck_mode, bck_dir, bck_history, bck_gzip FROM db WHERE uid= ? ;", array($cuid));
1046
        $str = "";
1047
        if ($db->next_record()) {
1048
            $str.=" <sql>\n";
1049
            $str.="   <login>" . $db->Record["login"] . "</login>\n";
1050
            $str.="   <pass>" . $db->Record["pass"] . "</pass>\n";
1051
            do {
1052
                $filename = $tmpdir . "/mysql." . $db->Record["db"] . ".sql.gz"; // FIXME not used
1053
                $str.="   <database>" . ($db->Record["db"]) . "</database>\n";
1054
                $str.="   <password>" . ($db->Record["pass"]) . "</password>\n";
1055
                if ($s["bck_mode"] != 0) { // FIXME what is $s ?
1056
                    $str.="   <backup-mode>" . ($db->Record["bck_mode"]) . "</backup-mode>\n";
1057
                    $str.="   <backup-dir>" . ($db->Record["bck_dir"]) . "</backup-dir>\n";
1058
                    $str.="   <backup-history>" . ($db->Record["bck_history"]) . "</backup-history>\n";
1059
                    $str.="   <backup-gzip>" . ($db->Record["bck_gzip"]) . "</backup-gzip>\n";
1060
                }
1061
            } while ($db->next_record());
1062
            $str.=" </sql>\n";
1063
        }
1064
        return $str;
1065
    }
1066
1067
    /* ----------------------------------------------------------------- */
1068
1069
    /**
1070
     * Exporte all the mysql databases a of give account to $dir directory
1071
     * @access private
1072
     * EXPERIMENTAL 'sid' function ;) 
1073
     */
1074
    function alternc_export_data($dir) {
1075
        global $db, $err, $cuid;
1076
        $err->log("mysql", "export_data");
1077
        $db->query("SELECT db.login, db.pass, db.db, dbusers.name FROM db,dbusers WHERE db.uid= ?  AND dbusers.uid=db.uid;", array($cuid));
1078
        $dir.="sql/";
1079
        if (!is_dir($dir)) {
1080
            if (!mkdir($dir)) {
1081
                $err->raise('mysql', _("The directory could not be created"));
1082
            }
1083
        }
1084
        // on exporte toutes les bases utilisateur.
1085
        while ($db->next_record()) {
1086
            $filename = $dir . "mysql." . $db->Record["db"] . "." . date("H:i:s") . ".sql.gz";
1087
            exec("/usr/bin/mysqldump --defaults-file=/etc/alternc/my.cnf --add-drop-table --allow-keywords -Q -f -q -a -e " . escapeshellarg($db->Record["db"]) . " |/bin/gzip >" . escapeshellarg($filename));
1088
        }
1089
    }
1090
1091
    /* ----------------------------------------------------------------- */
1092
1093
    /**
1094
     * Return the size of each databases in a SQL Host given in parameter
1095
     * @param $db_name the human name of the host
1096
     * @param $db_host the host hosting the SQL databases
1097
     * @param $db_login the login to access the SQL db
1098
     * @param $db_password the password to access the SQL db
1099
     * @param $db_client the client to access the SQL db
1100
     * @return an array associating the name of the databases to their sizes : array(dbname=>size)
1101
     */
1102
    function get_dbus_size($db_name, $db_host, $db_login, $db_password, $db_client) {
1103
        global $err;
1104
        $err->log("mysql", "get_dbus_size", $db_host);
1105
1106
	$this->dbus = new DB_Sql("mysql",$db_host,$db_login,$db_password);
1107
1108
        $this->dbus->query("SHOW DATABASES;");
1109
	$alldb=array();
1110
        while ($this->dbus->next_record()) {
1111
            $alldb[] = $this->dbus->f("Database");
1112
	}
1113
1114
        $res = array();
1115
	foreach($alldb as $dbname) {
1116
            $c = $this->dbus->query("SHOW TABLE STATUS FROM $dbname;");
1117
            $size = 0;
1118
            while ($this->dbus->next_record()) {
1119
	      $size+=$this->dbus->f("Data_length") + $this->dbus->f("Index_length");
1120
            }
1121
            $res["$dbname"] = "$size";
1122
        }
1123
        return $res;
1124
    }
1125
1126
}
1127
1128
/* Class m_mysql */
1129