· 8 years ago · Mar 17, 2018, 08:10 PM
1<?php
2
3
4// declare a config variable that can be accessed by including this file
5$config = require("config.php");
6
7class Connection {
8
9 // establish connection details from config file
10 function __construct() {
11 $config = require("config.php");
12 $this->db_host = $config["masterDB"]["host"];
13 $this->db_user = $config["masterDB"]["user"];
14 $this->db_pass = $config["masterDB"]["password"];
15 $this->db_name = $config["masterDB"]["name"];
16 }
17
18 // make the connection
19 public function connect() {
20 $this->connect_db = new mysqli($this->db_host, $this->db_user, $this->db_pass, $this->db_name);
21 if (mysqli_connect_errno()) {
22 die("Connection failed: %s\n".mysqli_connect_error());
23 exit();
24 }
25 if (!$this->connect_db->set_charset("utf8")) {
26 die("Could not set character set to UTF8: %s\n".$this->connect_db->error);
27 exit();
28 }
29 return true;
30 }
31
32 // return the current connection
33 public function get_connection() {
34 return $this->connect_db;
35 }
36}
37
38
39
40/*
41 class container for art request actions
42*/
43class RequestTemplate {
44
45 /**
46 * @var object Mysqli instance used for database operations
47 */
48 var $mysqli = null;
49
50 /**
51 * @var integer ID of user performing operations
52 */
53 var $currentUSER = null;
54
55 /**
56 * @var integer ID of current request being worked with
57 */
58 var $currentID = null;
59
60 /**
61 * @var boolean If false will not automatically commit changes to database (requires external mysqli be declared)
62 */
63 var $autoCommit = true;
64
65 /**
66 * @var boolean If true will throw an exception upon error rather than the default behavior
67 */
68 var $useException = false;
69
70 /**
71 * @var array An array of field names and their datatypes fpr constructing insert & update statements
72 */
73 protected $fieldTypes = array();
74
75 /**
76 * @var string The current table to perform insert & update operations on
77 */
78 private $currentTable = null;
79
80 /**
81 * @var array A white-list of tables eligible for insert & update operations
82 */
83 private $tablesWhitelist = array();
84
85 /**
86 * @var array A white-list of functions that can be used in insert & update operations
87 */
88 var $myslqFuncWhiteList = array(null, "null", "NULL", "NOW()");
89
90 /**
91 * Class constructor
92 *
93 * @param mixed[false|object] $mysqli An existing mysqli instance or false to initialize one
94 * @param mixed[boolean|integer] $exists The ID of the request currently being operated on, or false if unknown (or a new request)
95 * @param boolean $autocommit If false will not automatically commit mysql transactions (which requires an external instance of mysqli)
96 */
97 function __construct($mysqli = false, $exists = false, $autocommit = true) {
98 if ($mysqli == false) {
99 if ($autocommit == false) {
100 $this->handleErr("autocommit can not be used without an existing mysqli connection");
101 }
102 $this->checkDBConn();
103 } else {
104 $this->mysqli = $mysqli;
105 }
106 if ($exists != false && is_int( intval($exists) )) {
107 $this->currentID = $exists;
108 }
109
110 $this->autoCommit = $autocommit;
111 $this->useException = false;
112 if ($this->autoCommit == false) {
113 $this->useException = true;
114 }
115 }
116
117 /**
118 * Handels errors
119 *
120 * Will throw an exception if this->useException is true (and return false)
121 * Or else dies, printing the argument
122 *
123 * @param string $arg Description of the encountered error
124 * @return bool false
125 */
126 public function handleErr($arg) {
127 if ($this->useException == true) {
128 throw new Exception($arg);
129 return false;
130 } else {
131 die($arg);
132 }
133 }
134
135
136 /**
137 * Sets the current operating table - checks against whitelist of tables (throws error if no match)
138 *
139 * @param string $table Name of the white-listed table
140 * @return bool true
141 */
142 public function setCurrentTable($table = null) {
143 if (!in_array($table, $this->tablesWhitelist)) {
144 $this->handleErr("RequestTemplate->setCurrentTable invalid table used: $table");
145 }
146 $this->currentTable = $table;
147 return true;
148 }
149
150
151
152 /* create request entries
153 arguments:
154 $dataArray (string) - user notes to use on event
155 keyName -> ['s', 'value'],
156 $initialInsert (boolean) - will use the ID from the insert statement for subsequent requests
157 $insertIgnore (boolean) - will ignore duplicate unique entries instead of throwing an error
158 */
159 public function insertFields($dataArray, $initialInsert = false, $insertIgnore = false) {
160 if (!isset($this->currentUSER)) {
161 $this->handleErr("No currentUSER defined");
162 }
163
164 if (is_null($this->currentTable)) {
165 $this->handleErr("RequestTemplate->insertFields no valid table defined");
166 } else if ($this->checkTableField() == false) {
167 $this->handleErr("RequestTemplate->insertFields invalid table used: {$this->currentTable}");
168 }
169
170 $setStringArry0 = array();
171 $setStringArry1 = array();
172 $arParams = array();
173 $typeString = "";
174 $return = false;
175
176 // might want to enforce the existance of some fields here
177 foreach ($dataArray as $key => $value) {
178 if ($this->checkTableField($key) == false) {
179 $this->handleErr("RequestTemplate->insertFields Invalid field passed: $key");
180 }
181
182 if (strtolower($dataArray[$key][1]) == 'null') {
183 $dataArray[$key][1] = null;
184 }
185
186 $setStringArry0[] = "`$key`";
187 if ($dataArray[$key][0] == "f" && in_array($dataArray[$key][1], $this->myslqFuncWhiteList)) {
188 if (is_null($dataArray[$key][1])) {
189 $setStringArry1[] = 'NULL';
190 } else {
191 $setStringArry1[] = $dataArray[$key][1];
192 }
193 } else {
194 $typeString .= $dataArray[$key][0];
195 $setStringArry1[] = "?";
196 $arParams[] = &$dataArray[$key][1];
197 }
198 }
199
200 array_unshift($arParams, $typeString);
201 $qryPart0 = implode(", ", $setStringArry0);
202 $qryPart1 = implode(", ", $setStringArry1);
203
204 $insert = "INSERT";
205 if ($insertIgnore === true) {
206 $insert = "INSERT IGNORE";
207 }
208
209 $sql = "INSERT INTO `{$this->currentTable}` ($qryPart0) VALUES ($qryPart1)";
210
211 if ($insertIgnore === true) {
212 $sql .= " ON DUPLICATE KEY UPDATE `id` = LAST_INSERT_ID(id)";
213 }
214
215 $stmt = $this->mysqli->prepare($sql);
216
217 if ($stmt) {
218 call_user_func_array(array($stmt, "bind_param"), $arParams);
219 $rc = $stmt->execute();
220 if ($rc === false) {
221 $this->handleErr("RequestTemplate->insertFields->stmt->execute() failed: " . htmlspecialchars($stmt->error));
222 }
223 $return = $stmt->insert_id;
224 if ($initialInsert == true) {
225 $this->currentID = $return;
226 }
227 if ($this->autoCommit == true) {
228 $stmt->close();
229 }
230 } else {
231 $this->handleErr("RequestTemplate->insertFields->stmt->prepare() failed: " . htmlspecialchars($this->mysqli->error));
232 }
233
234 return $return;
235 }
236
237 /* update fields on the request
238 arguments:
239 $dataArray (string) - user notes to use on event
240 keyName -> ['s', 'value'],
241 */
242 public function updateFields($dataArray) {
243 $this->checkRequired();
244
245 if (is_null($this->currentTable)) {
246 $this->handleErr("RequestTemplate->updateFields no valid table defined");
247 } else if ($this->checkTableField() == false) {
248 $this->handleErr("RequestTemplate->updateFields invalid table used: {$this->currentTable}");
249 }
250
251 $setStringArry = array();
252 $arParams = array();
253 $typeString = "";
254
255 foreach($dataArray as $key => $value) {
256 if ($this->checkTableField($key) == false) {
257 $this->handleErr("RequestTemplate->updateFields Invalid field passed: $key");
258 }
259
260 if (strtolower($dataArray[$key][1]) == 'null') {
261 $dataArray[$key][1] = null;
262 }
263
264 if ($dataArray[$key][0] == "f" && in_array($dataArray[$key][1], $this->myslqFuncWhiteList)) {
265 if (is_null($dataArray[$key][1])) {
266 $setStringArry[] = "`$key` = NULL";
267 } else {
268 $setStringArry[] = "`$key` = {$dataArray[$key][1]}";
269 }
270 } else {
271 $setStringArry[] = "`$key` = ?";
272 $arParams[] = &$dataArray[$key][1];
273 $typeString .= $dataArray[$key][0];
274 }
275 }
276
277 // add extra integer for id
278 $typeString .= "i";
279 $arParams[] = &$this->currentID;
280
281 array_unshift($arParams, $typeString);
282 $qryPart = implode(", ", $setStringArry);
283
284 $sql = "UPDATE `{$this->currentTable}` SET $qryPart WHERE `id` = ?";
285 $stmt = $this->mysqli->prepare($sql);
286
287 if ($stmt) {
288 call_user_func_array( array($stmt, "bind_param"), $arParams);
289 $rc = $stmt->execute();
290 if( $rc === false ) $this->handleErr("RequestTemplate->updateFields->stmt->execute() failed: " . htmlspecialchars($stmt->error));
291 if($this->autoCommit == true) {
292 $stmt->close();
293 }
294 } else {
295 $this->handleErr("RequestTemplate->updateFields->stmt->prepare() failed: " . htmlspecialchars($this->mysqli->error));
296 }
297
298 return true;
299 }
300
301 /**
302 * Checks if a field exists within the current table
303 *
304 * @param string $field name of the field to check for
305 * @return bool true | false (whether the field was found or not)
306 */
307 private function checkTableField($field = false) {
308 $rows = 0;
309 $qry = "SELECT * FROM information_schema.TABLES WHERE TABLE_NAME = ?";
310 if ($field != false) {
311 $qry = "SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = ? AND COLUMN_NAME = ?";
312 }
313 if ($stmt = $this->mysqli->prepare($qry)) {
314 if($field != false) {
315 $rc = $stmt->bind_param("ss", $this->currentTable, $field);
316 } else {
317 $rc = $stmt->bind_param("s", $this->currentTable);
318 }
319 if ( $rc === false ) $this->handleErr("RequestTemplate->checkTableField->stmt->bind_param() failed: " . htmlspecialchars($stmt->error));
320 $rc = $stmt->execute();
321 if ( $rc === false ) $this->handleErr("RequestTemplate->checkTableField->stmt->execute() failed: " . htmlspecialchars($stmt->error));
322
323 $stmt->store_result();
324 $rows = $stmt->num_rows;
325 $stmt->free_result();
326
327 if($this->autoCommit == true) {
328 $stmt->close();
329 }
330 } else {
331 $this->handleErr("RequestTemplate->checkTableField->stmt->prepare() failed: " . htmlspecialchars($this->mysqli->error));
332 }
333
334 if ($rows == 1) return true;
335
336 return false;
337 }
338
339 /**
340 * Build input object for inserting and updating database fields
341 *
342 * @param array $dataArray array of key names and values to parse
343 * @return array $output array of database column names and their respective inputs
344 */
345 public function buildTableInputs($dataArray) {
346 $output = array();
347
348 foreach ($dataArray as $key => $value) {
349 if (isset($this->fieldTypes[$key])) {
350 if (isset($this->fieldTypes[$key]["formatting"])) {
351 $formatted = call_user_func_array(array($this, $this->fieldTypes[$key]["formatting"]), array($dataArray[$key]));
352
353 if (is_null($formatted)) {
354 if (isset($this->fieldTypes[$key]["allow_null_format"]) && $this->fieldTypes[$key]["allow_null_format"] === true) {
355 $dataArray[$key] = $formatted;
356 }
357 } else {
358 $dataArray[$key] = $formatted;
359 }
360 }
361
362 $output[$this->fieldTypes[$key]["db_field"]] = array($this->fieldTypes[$key]["db_type"], $dataArray[$key]);
363 }
364 }
365
366 return $output;
367 }
368
369 /**
370 * Checks for a database connection, creates one if it does not exist
371 *
372 * @return object A mysqli connection instance
373 */
374 public function checkDBConn() {
375 if (is_null($this->mysqli)) {
376 $db = new Connection();
377 $db->connect();
378 $this->mysqli = $db->get_connection();
379 }
380
381 return $this->mysqli;
382 }
383}
384?>