1: <?php
  2: /*
  3: * Copyright (C) 2013 Bryan Nielsen - All Rights Reserved
  4: *
  5: * Author: Bryan Nielsen (bnielsen1965@gmail.com)
  6: *
  7: *
  8: * This file is part of cryptUser.
  9: * cryptUser is free software: you can redistribute it and/or modify
 10: * it under the terms of the GNU General Public License as published by
 11: * the Free Software Foundation, either version 3 of the License, or
 12: * (at your option) any later version.
 13: * 
 14: * cryptUser is distributed in the hope that it will be useful,
 15: * but WITHOUT ANY WARRANTY; without even the implied warranty of
 16: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 17: * GNU General Public License for more details.
 18: * 
 19: * You should have received a copy of the GNU General Public License
 20: * along with cryptUser.  If not, see <http://www.gnu.org/licenses/>.
 21: */
 22: 
 23: namespace BSN\CryptUser;
 24: use Exception, mysqli;
 25: 
 26: require_once 'CryptDataSource.php';
 27: 
 28: /**
 29:  * A MySQL based data source object for the CryptUsers.
 30:  *
 31:  * @author Bryan Nielsen, bnielsen1965@gmail.com
 32:  * @copyright Copyright 2013, Bryan Nielsen
 33:  */
 34: class CryptMySQLSource implements CryptDataSource {
 35:     private $mysqli;
 36:     private $usersTable;
 37:     private $databaseConfig;
 38:     
 39:     
 40:     /**
 41:      * Constructor to set up the data source
 42:      * 
 43:      */
 44:     public function __construct($databaseConfig) {
 45:         $this->databaseConfig = $databaseConfig;
 46:         $this->connectToDatabase();
 47:         
 48:         $this->usersTable = (!empty($databaseConfig['usersTable']) ? $databaseConfig['usersTable'] : 'users');
 49:     }
 50:     
 51:     
 52:     /**
 53:      * Connect to the database
 54:      */
 55:     private function connectToDatabase() {
 56:         // connect if parameters provided
 57:         if (    !empty($this->databaseConfig['host']) && 
 58:                 !empty($this->databaseConfig['username']) && 
 59:                 !empty($this->databaseConfig['password']) && 
 60:                 !empty($this->databaseConfig['database'])) {
 61:             $this->mysqli = new mysqli(
 62:                     $this->databaseConfig['host'], 
 63:                     $this->databaseConfig['username'], 
 64:                     $this->databaseConfig['password'], 
 65:                     $this->databaseConfig['database']);
 66:             
 67:             if ($this->mysqli->connect_errno) {
 68:                 throw new Exception("Failed to connect to MySQL: (" . $this->mysqli->connect_errno . ") " . $this->mysqli->connect_error);
 69:             }
 70:         }
 71:     }
 72:     
 73:     
 74:     /**
 75:      * Get source type
 76:      * 
 77:      * @return string The data source type.
 78:      */
 79:     public function getSourceType() {
 80:         return 'MySQL';
 81:     }
 82:     
 83:     
 84:     /**
 85:      * Get array of users that match a given name.
 86:      * @param string $username The username to search for in the data source.
 87:      * @return array|boolean An array of arrays containing user elements to create a user
 88:      * or FALSE if not found.
 89:      */
 90:     public function getUserByName($username) {
 91:         $sql = "SELECT * FROM " . $this->usersTable . " WHERE username='" . $this->mysqli->real_escape_string($username) . "'";
 92:         $rs = $this->mysqli->query($sql);
 93:         if ($rs && $rs->num_rows) {
 94:             return $rs->fetch_assoc();
 95:         }
 96:         
 97:         // failed to find user
 98:         return FALSE;
 99:     }
100:     
101:     
102:     /**
103:      * Get a list of usernames
104:      * @return array An array of strings containing the usernames from the data source.
105:      */
106:     public function getUsernames() {
107:         $sql = "SELECT * FROM " . $this->usersTable;
108:         $rs = $this->mysqli->query($sql);
109:         if ($rs && $rs->num_rows) {
110:             $usernames = array();
111:             while ($row = $rs->fetch_assoc()) {
112:                 $usernames[] = $row['username'];
113:             }
114:             return $usernames;
115:         }
116:         
117:         // failed to find user
118:         return FALSE;
119:     }
120:     
121:     
122:     /**
123:      * Save the provided user details in the data source.
124:      * @param array $user An array of user elements to be saved.
125:      * @return boolean Returns TRUE on success and FALSE on failure.
126:      */
127:     public function saveUser($user) {
128:         if ($this->getUserByName($user['username']) !== FALSE) {
129:             // user exists, update
130:             $sql = "UPDATE " . $this->usersTable . " SET " .
131:                     "passwordHash='" . $this->mysqli->real_escape_string($user['passwordHash']) . "', " .
132:                     "sslKey='" . $this->mysqli->real_escape_string($user['sslKey']) . "', " .
133:                     "flags='" . $this->mysqli->real_escape_string($user['flags']) . "' " .
134:                     "WHERE username='" . $this->mysqli->real_escape_string($user['username']) . "'";
135:         }
136:         else {
137:             // user does not exist, insert
138:             $sql = "INSERT INTO " . $this->usersTable . "(username, passwordHash, sslKey, flags) VALUES (" .
139:                 "'" . $this->mysqli->real_escape_string($user['username']) . "', " .
140:                 "'" . $this->mysqli->real_escape_string($user['passwordHash']) . "', " .
141:                 "'" . $this->mysqli->real_escape_string($user['sslKey']) . "', " .
142:                 "'" . $this->mysqli->real_escape_string($user['flags']) . "'" .
143:                 ")";
144:         }
145:         
146:         return $this->mysqli->query($sql);
147:     }
148:     
149:     
150:     /**
151:      * Delete the specified user from the data source.
152:      * @param string $username The username of the user to delete.
153:      * @return boolean Returns TRUE on success and FALSE on failure.
154:      */
155:     public function deleteUser($username) {
156:         $sql = "DELETE FROM " . $this->usersTable . " WHERE username='" . $this->mysqli->real_escape_string($username) . "'";
157:         return $this->mysqli->query($sql);
158:     }
159:     
160:     
161:     /**
162:      * Search provided users array for the specified user.
163:      * @param array $users An array of user rows to search.
164:      * @return integer|boolean The array index for the user name or FALSE if not found.
165:      */
166:     private function searchUsersForUser($users, $username) {
167:         if ($users) {
168:             foreach ($users as $ui => $user) {
169:                 if ($user['username'] == $username) return $ui;
170:             }
171:         }
172:         
173:         return FALSE;
174:     }
175:     
176:     
177:     /**
178:      * Get SQL to create users table.
179:      * 
180:      * @return string SQL statement to create user table.
181:      */
182:     public function getCreateUserTableSQL() {
183:         return "CREATE TABLE `" . $this->usersTable . "` (" . 
184:                 "`username` VARCHAR (255), " . 
185:                 "`passwordHash` VARCHAR (255) DEFAULT '', " . 
186:                 "`sslKey` TEXT DEFAULT '', " . 
187:                 "`flags` INTEGER DEFAULT 0, " . 
188:                 "PRIMARY KEY (`username`) " . 
189:                 ") ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
190:     }
191:     
192:     
193:     /**
194:      * Create users table.
195:      * 
196:      * @param string $createSQL Optional SQL statement to create the users table.
197:      * @return boolean TRUE on success, FALSE on failure.
198:      */
199:     public function createUsersTable($createSQL = NULL) {
200:         if ($this->mysqli->ping()) {
201:             if (empty($createSQL)) $createSQL = $this->getCreateUserTableSQL();
202:             return $this->mysqli->query($createSQL);
203:         }
204:         
205:         return FALSE;
206:     }
207:     
208:     
209:     /**
210:      * Check to see if users table exists
211:      * 
212:      * @return boolean TRUE if table exists, FALSE if table does not exist.
213:      */
214:     public function usersTableExists() {
215:         if ($this->mysqli->ping()) {
216:             $result = $this->mysqli->query("SHOW TABLES LIKE '" . $this->usersTable . "'");
217:             return $result->num_rows > 0;
218:         }
219: 
220:         return FALSE;
221:     }
222:     
223:     
224:     /**
225:      * The magic __wakeup() function is used to reconnect to the database when
226:      * the object is unserialized.
227:      */
228:     public function __wakeup() {
229:         $this->connectToDatabase();
230:     }
231: }
232: 
233: