1 <?php
  2 /**
  3  * @author Leandro Silva
  4  * @copyright 2012, 2016 Leandro Silva (http://grafluxe.com)
  5  * @license MIT
  6  *
  7  * @classdesc Securely execute commands on a database using PHP Data Objects — many security
  8  * features added. Note that methods prepended with 'run_' execute specific statements; use
  9  * the 'query' method for custom queries.
 10  *
 11  * @example
 12  * This sample includes tight security.
 13  *
 14  * Use value binding, a whitelist, and checker methods if you plan to construct your SQL statements
 15  * with values coming from a form (or other user inputted method). Using these featured will help
 16  * to prevent SQL attacks.
 17  *
 18  * <pre>
 19  *   include "./GxDBConnect.class.php";
 20  *   include "./DSNHelper.class.php";
 21  *
 22  *   try {
 23  *     $conn = new GxDBConnect(DSNHelper::mysql("my_database"), "my_username", "my_pass");
 24  *
 25  *     $conn->col_whitelist = array("first", "last");
 26  *     $conn->tbl_whitelist = array("names_table");
 27  *
 28  *     $f_name = $_GET["first_name"];
 29  *     $l_name = $_GET["last_name"];
 30  *     $table = $_GET["table_name"];
 31  *
 32  *     $data = $conn->query("
 33  *       SELECT {$conn->col_check($f_name)}
 34  *       FROM {$conn->tbl_check($table)}
 35  *       WHERE {$conn->col_check($l_name)} = :ln
 36  *       ",
 37  *       array(
 38  *         $conn->bind_value(":ln", "Doe")
 39  *       ),
 40  *       PDO::FETCH_NUM
 41  *     );
 42  *
 43  *     print_r($data);
 44  *   } catch(GxDBException $e) {
 45  *     exit($e);
 46  *   }
 47  * </pre>
 48  *
 49  * This sample includes a more simple use case.
 50  *
 51  * <pre>
 52  *   include "./GxDBConnect.class.php";
 53  *
 54  *   try {
 55  *     $conn = new GxDBConnect("mysql:host=localhost;dbname=my_database", "my_username", "my_pass");
 56  *
 57  *     $data = $conn->query("
 58  *       SELECT first
 59  *       FROM names_table
 60  *       WHERE last = 'Doe'
 61  *     ");
 62  *
 63  *     print_r($data);
 64  *   } catch(GxDBException $e) {
 65  *     exit($e);
 66  *   }
 67  * </pre>
 68  *
 69  */
 70 
 71 class GxDBConnect {
 72   /** @var object The PDO connection object. */
 73   public $conn;
 74 
 75   /** @var array A whitelist of columns that can be queried. Use in concert with the 'col_check' method. */
 76   public $col_whitelist;
 77 
 78   /** @var array A whitelist of tables that can be queried. Use in concert with the 'tbl_check' method. */
 79   public $tbl_whitelist;
 80 
 81   /** @var string The statement you last queried. */
 82   public $get_last_stmt;
 83 
 84   /** @var string The release version. */
 85   public static $version = "3.0.0";
 86 
 87   /** @var string Set to true to output uncaught errors. Defaults to false for better security. */
 88   public static $echo_uncaught_errors = false;
 89 
 90   private $blacklist = array("DROP", "DELETE", "--", "/*", "xp_", ";");
 91 
 92  /**
 93    * Constructor. By default, the PDO attribute ATTR_EMULATE_PREPARES is set to false and ATTR_ERRMODE is set to ERRMODE_EXCEPTION.
 94    * @param string $dsn        The DSN string. You can use the GxDBConnectHelper class to help setup this param.
 95    * @param string $usr="root" The username.
 96    * @param string $pw="root"  The password.
 97    * @param array  $opts=null  Connection options.
 98    * @throws GxDBException
 99    * @return object The PDO object.
100    */
101   public function __construct($dsn, $usr = "root", $pw = "root", array $opts = null) {
102     set_exception_handler(array($this, "on_uncaught_exception"));
103     set_error_handler(array($this, "on_uncaught_error"));
104 
105     try {
106       $this->conn = new PDO($dsn, $usr, $pw, $opts);
107     } catch (PDOException $e) {
108       throw new GxDBException($e->getMessage(), 1);
109     }
110 
111     $this->conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
112     $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
113   }
114 
115   /**
116     * @ignore
117     */
118   public static function on_uncaught_exception($e) {
119     if (self::$echo_uncaught_errors) {
120       exit("[Uncaught GxDBConnect Error]: " . $e->getMessage());
121     } else {
122       exit("[Uncaught GxDBConnect Error]: To see uncaught errors, set 'GxDBConnect::\$echo_uncaught_errors' to true.");
123     }
124   }
125 
126   /**
127     * @ignore
128     */
129   public static function on_uncaught_error($err_num, $err_msg) {
130     if (self::$echo_uncaught_errors) {
131       exit("[Uncaught GxDBConnect Error]: " . $err_msg);
132     } else {
133       exit("[Uncaught GxDBConnect Error]: To see uncaught errors, set 'GxDBConnect::\$echo_uncaught_errors' to true.");
134     }
135   }
136 
137   private function blacklist_check($s) {
138     $regex = "";
139 
140     if (isset($this->blacklist) && count($this->blacklist) > 0) {
141       for ($i = 0; $i < count($this->blacklist); $i++) {
142         if ($i > 0) {
143           $regex .= "|";
144         }
145         $regex .= preg_quote($this->blacklist[$i]);
146       }
147 
148       $regex = str_replace("/", "\/", $regex);
149 
150       //remove extra space
151       $s = preg_replace("/\s+/", " ", $s);
152 
153       //remove all strings in order to test clauses
154       $s = preg_replace("/\".*?\"|'.*?'/", "", $s);
155 
156       if (preg_match("/$regex/i", $s)) {
157         throw new GxDBException("Your query statement contains a blacklisted value. See blacklist_list, index " . ($i - 1) . ".", 2);
158       }
159     }
160   }
161 
162   /**
163    * Adds a value to your blacklist filter. Before any query is run, your statement will be checked for
164    * any blacklisted strings. If a blacklisted string is found, the query will not be executed and a
165    * GxDBException exception will be thrown. By default, the blacklist filter contains the following:
166    * ["DROP", "DELETE", "--", "/*", "xp_", ";"]
167    * @param string $str A string to blacklist. Letter case does not matter.
168    */
169   public function blacklist_add($str) {
170     $str = (string) $str;
171     $at = array_search($str, $this->blacklist);
172 
173     if(!$at){
174       array_push($this->blacklist, $str);
175     }
176   }
177 
178   /**
179    * Removes a value from your blacklist filter.
180    * @param string $str The word to remove from the your blacklist. Letter case does not matter.
181    */
182   public function blacklist_remove($str) {
183     $at = array_search(strtolower((string) $str), array_map("strtolower", $this->blacklist));
184 
185     if($at !== false){
186       array_splice($this->blacklist, $at, 1);
187     }
188   }
189 
190   /**
191    * Returns your blacklist filters.
192    * @return array The current blacklisted strings.
193    */
194   public function blacklist_list() {
195     return $this->blacklist;
196   }
197 
198   /**
199    * Selects a database.
200    * @param string $db The database name.
201    */
202   public function select_db($db) {
203     $this->conn->exec("USE $db");
204   }
205 
206   /**
207    * Checks if a column in allowed to be used (via the column whitelist).
208    * @param  string $col The column name.
209    * @throws GxDBException
210    * @return string The column name.
211    */
212   public function col_check($col) {
213     if (isset($this->col_whitelist)) {
214       if (!(array_search($col, $this->col_whitelist, true) !== false)) {
215         throw new GxDBException("You do not have permission to query one of the columns in your statement.", 3);
216       }
217     }
218 
219     return $col;
220   }
221 
222   /**
223    * Checks if a table in allowed to be used (via the table whitelist).
224    * @param  string $tbl The table name.
225    * @throws GxDBException
226    * @return string The table name.
227    */
228   public function tbl_check($tbl) {
229     if (isset($this->tbl_whitelist)) {
230       if (!(array_search($tbl, $this->tbl_whitelist, true) !== false)) {
231         throw new GxDBException("You do not have permission to query one of the tables in your statement.", 4);
232       }
233     }
234 
235     return $tbl;
236   }
237 
238   /**
239    * To be used as the bind argument in the 'query' method. Works like PDO's 'bindValue' method.
240    * @param  mixed $parameter        The parameter identifier.
241    * @param  mixed $value            The value to bind.
242    * @param  integer $data_type=null The data type.
243    * @return array The bind data.
244    */
245   public function bind_value($parameter, $value, $data_type = null) {
246     return array(
247       $parameter,
248       $value,
249       $data_type
250     );
251   }
252 
253   /**
254    * Runs an SQL query. This is the primary method used to run queries.
255    * @param  string $stmt                          Your query statement (use 'col_check' and 'tbl_check' with the whitelists for added security against SQL injecion)
256    * @param  array     $bind=null                  An array filled with the 'bind_value' methods.
257    * @param  integer   $fetch_how=PDO::FETCH_ASSOC How to return the results.
258    * @throws GxDBException
259    * @return array Query results.
260    */
261   public function query($stmt, array $bind = null, $fetch_how = PDO::FETCH_ASSOC) {
262     $this->blacklist_check($stmt);
263 
264     try {
265       $q = $this->conn->prepare($stmt . ";");
266       $this->get_last_stmt = $q->queryString;
267 
268       if (isset($bind)) {
269         for ($i = 0; $i < count($bind); $i++) {
270           if ($bind[$i][2]) {
271             $q->bindValue($bind[$i][0], $bind[$i][1], $bind[$i][2]);
272           } else {
273             $q->bindValue($bind[$i][0], $bind[$i][1]);
274           }
275         }
276       }
277 
278       $q->closeCursor();
279       $q->execute();
280 
281       return $q->fetchAll($fetch_how);
282     } catch (PDOException $e) {
283       throw new GxDBException($e->getMessage(), 5);
284     }
285   }
286 
287   /**
288    * Closes the database connection.
289    */
290   public function close() {
291     $this->conn = null;
292   }
293 
294   /**
295    * Returns a boolean determining whether a table exists.
296    * @param  string $tbl The table to query.
297    * @return boolean  Whether a table exists.
298    */
299   public function run_tbl_exists($tbl) {
300     try {
301       $q = $this->conn->prepare("SELECT 1 FROM {$this->tbl_check($tbl)} LIMIT 1;");
302       $this->get_last_stmt = $q->queryString;
303       $q->closeCursor();
304       $q->execute();
305 
306       return true;
307     } catch (PDOException $e) {
308       return false;
309     }
310   }
311 
312   /**
313    * Returns the total column count.
314    * @param  string $tbl The table to query.
315    * @return integer The number of columns.
316    */
317   public function run_col_count($tbl) {
318     $q = $this->conn->prepare("SELECT * FROM {$this->tbl_check($tbl)} LIMIT 1;");
319     $this->get_last_stmt = $q->queryString;
320 
321     $q->closeCursor();
322     $q->execute();
323 
324     return $q->columnCount();
325   }
326 
327   /**
328    * Returns an array of associative arrays with column info.
329    * @param  string $tbl The table to query.
330    * @return array Column info.
331    */
332   public function run_col_info($tbl) {
333     $q = $this->conn->prepare("SHOW COLUMNS FROM {$this->tbl_check($tbl)};");
334     $this->get_last_stmt = $q->queryString;
335 
336     $q->closeCursor();
337     $q->execute();
338 
339     return $q->fetchAll(PDO::FETCH_ASSOC);
340   }
341 
342   /**
343    * Returns all of a columns data.
344    * @param  string $col The column name.
345    * @param  string $tbl The table to query.
346    * @return array Column data.
347    */
348   public function run_col_data($col, $tbl) {
349     $q = $this->conn->prepare("SELECT {$this->col_check($col)} FROM {$this->tbl_check($tbl)};");
350     $this->get_last_stmt = $q->queryString;
351 
352     $q->closeCursor();
353     $q->execute();
354 
355     $fin = array();
356     foreach ($q as $r) {
357       array_push($fin, $r[0]);
358     }
359 
360     return $fin;
361   }
362 
363   /**
364    * Returns a boolean determining whether a column exists.
365    * @param  string $col The column name.
366    * @param  string $tbl The table to query.
367    * @return boolean  Whether a column exists.
368    */
369   public function run_col_exists($col, $tbl) {
370     try {
371       $q = $this->conn->prepare("SELECT {$this->col_check($col)} FROM {$this->tbl_check($tbl)} LIMIT 1;");
372       $this->get_last_stmt = $q->queryString;
373       $q->closeCursor();
374       $q->execute();
375 
376       return true;
377     } catch (PDOException $e) {
378       return false;
379     }
380   }
381 
382   /**
383    * Returns the total row count.
384    * @param  string $tbl The table to query.
385    * @return integer The row count.
386    */
387   public function run_row_total($tbl) {
388     $q = $this->conn->prepare("SELECT COUNT(*) FROM {$this->tbl_check($tbl)};");
389     $this->get_last_stmt = $q->queryString;
390 
391     $q->closeCursor();
392     $q->execute();
393 
394     return $q->fetchColumn();
395   }
396 
397   /**
398    * Returns data in the specified row.
399    * @param  integer $row The row number.
400    * @param  string $tbl  The table to query.
401    * @return array|null The row data. Returns null if the specified row is greater than the total number of rows.
402    */
403   public function run_row_data($row, $tbl) {
404     $row = (int) $row;
405     $q = $this->conn->prepare("SELECT * FROM {$this->tbl_check($tbl)};");
406     $this->get_last_stmt = $q->queryString;
407 
408     $q->closeCursor();
409     $q->execute();
410 
411     if ($row > $this->run_row_total($tbl)) {
412       return null;
413     } else {
414       $arr = $q->fetchAll(PDO::FETCH_NUM);
415 
416       return $arr[$row];
417     }
418   }
419 
420   /**
421    * Exports your table as a JSON formatted file.
422    * @param  string   $tbl                The table to export.
423    * @param  boolean $pretty_print=false  Whether to pretty-print output (only valid on PHP versions >=5.4.0).
424    * @param  string $relative_dir=""      A save path relative to this file.
425    * @return boolean Whether the output succeeded.
426    */
427   public function run_export($tbl, $pretty_print = false, $relative_dir = "") {
428     $file_name = date("m-d-y") . "-" . $tbl . ".json";
429 
430     $obj["GxDBConnect"] = self::$version;
431     $obj["table"] = $tbl;
432     $obj["colCount"] = $this->run_col_count($tbl);
433     $obj["rowCount"] = $this->run_row_total($tbl);
434 
435     //cols
436     $desc = $this->query("DESCRIBE $tbl");
437     $keys = array_keys($desc[0]);
438 
439     for ($i = 0, $len = count($desc); $i < $len; $i++) {
440       for ($k = 0, $lenK = count($keys); $k < $lenK; $k++) {
441         $cols[$i][$keys[$k]] = isset($desc[$i][$keys[$k]]) ? utf8_encode($desc[$i][$keys[$k]]) : "";
442       }
443     }
444 
445     $obj["cols"] = $cols;
446 
447     //rows
448     $q = $this->conn->prepare("SELECT * FROM $tbl;");
449     $q->closeCursor();
450     $q->execute();
451 
452     $obj["rows"] = $q->fetchAll(PDO::FETCH_NUM);
453 
454     //
455     $json = json_encode($obj, ($pretty_print && defined(JSON_PRETTY_PRINT) ? JSON_PRETTY_PRINT : 0));
456 
457     if (json_last_error() != JSON_ERROR_NONE) {
458       die("There was a problem creating your JSON export: " . json_last_error_msg());
459     }
460 
461     if ($relative_dir) {
462       $relative_dir = trim($relative_dir, "/") . "/";
463 
464       if (!is_dir($relative_dir)) {
465         mkdir($relative_dir);
466       }
467     }
468 
469     return file_put_contents($relative_dir . $file_name, $json) > 0;
470   }
471 
472   /**
473    * Echos an HTML table with your data.
474    * @param string $stmt                     Your SQL query statement.
475    * @param integer $paginate_at=0           Paginate after N rows of data. Works with the $pg_query_name param.
476    * @param string $pg_query_name="pg"       The paginate HTML query string name.
477    * @param boolean $use_default_styles=true Assigns default inline styles.
478    * @throws GxDBException
479    */
480   public function run_tbl_to_html($stmt, $paginate_at = 0, $pg_query_name = "pg", $use_default_styles = true) {
481     $this->blacklist_check($stmt);
482 
483     $bg_color = "#CCC";
484     $col_color_odd = "#F9F9F9";
485     $col_color_even = "#F0F0F0";
486     $header_color = "#9C9C9C";
487     $alt_row = true;
488     $row_num = 1;
489 
490     //col names
491     $qNames = $this->conn->prepare($stmt . ";");
492     $qNames->closeCursor();
493     $qNames->execute();
494 
495     $names = $qNames->fetchAll(PDO::FETCH_ASSOC);
496     $col_names = array_keys($names[0]);
497 
498     //main query
499     if ($paginate_at) {
500       if (stristr($stmt, "LIMIT") || stristr($stmt, "OFFSET")) {
501         throw new GxDBException("Your 'run_tbl_to_html' statement cannot have a LIMIT or OFFSET clause.", 6);
502       }
503 
504       $totalPgs = ceil($qNames->rowCount() / $paginate_at);
505       $pgQuery  = isset($_GET[$pg_query_name]) ? $_GET[$pg_query_name] : 1;
506 
507       if ($pgQuery > $totalPgs) {
508         $pgQuery = $totalPgs;
509       }
510 
511       $stmt .= " LIMIT $paginate_at OFFSET " . $paginate_at * ($pgQuery - 1);
512     }
513 
514     $q = $this->conn->prepare($stmt . ";");
515     $q->closeCursor();
516     $q->execute();
517 
518     $len = $q->columnCount();
519 
520     if ($use_default_styles) {
521       $styles  = " style=\"width:100%; background-color:$bg_color; text-align:center\"";
522       $styles2 = " style=\"padding:3px 12px; background-color:$header_color\"";
523     }
524 
525     $echo = "<table class=\"GxDBConnectTable\"$styles>\n<tr class=\"headerRow\"$styles2>\n";
526 
527     //heads
528     for ($i = 0; $i < $len; $i++) {
529       $col_num = $i + 1;
530 
531       $echo .= "<td class=\"col$col_num\">$col_names[$i]</td>\n";
532     }
533 
534     $echo .= "</tr>\n";
535 
536     //data
537     foreach ($q as $row) {
538       for ($i = 0; $i < $len; $i++) {
539         if ($i == 0) {
540           if ($alt_row) {
541             $alt_row       = false;
542             $colColor      = $col_color_odd;
543             $alt_row_class = "oddRow";
544           } else {
545             $alt_row       = true;
546             $colColor      = $col_color_even;
547             $alt_row_class = "evenRow";
548           }
549         }
550 
551         if ($use_default_styles) {
552           $styles = " style=\"padding:3px 12px; background-color:$colColor;\"";
553         }
554 
555         if (($i % $len) == 0) {
556           $echo .= "<tr class=\"row$row_num $alt_row_class\"$styles>\n";
557         }
558 
559         $col_num = $i + 1;
560 
561         $echo .= "<td class=\"col$col_num\">$row[$i]</td>\n";
562 
563         if ($i == ($len - 1)) {
564           $echo .= "</tr>\n";
565           $row_num++;
566         }
567       }
568     }
569 
570     $echo .= "</table>";
571 
572     echo $echo;
573 
574     if ($paginate_at) {
575       if ($totalPgs > 1) {
576         $this->paginate($pgQuery, $pg_query_name, $totalPgs);
577       }
578     }
579   }
580 
581   private function paginate($pg, $pg_query_name, $totalPgs) {
582     $echo = "\n<p class=\"GxDBConnectPagination\">\n" . ($pg > 1 ? "<a href=\"" . $this->updateQueryStr($pg_query_name, $pg - 1) . "\">&lt;</a> " : "&lt; ") . "\n";
583 
584     for ($i = 1; $i <= $totalPgs; $i++) {
585       $echo .= ($pg == $i ? $i . " " : "<a href=\"" . $this->updateQueryStr($pg_query_name, $i) . "\">$i</a>") . "\n";
586     }
587 
588     $echo .= ($pg < $totalPgs ? "<a href=\"" . $this->updateQueryStr($pg_query_name, $pg + 1) . "\">&gt;</a> " : "&gt;") . "\n</p>\n";
589 
590     echo $echo;
591   }
592 
593   private function updateQueryStr($pg_query_name, $pg) {
594     if (!strstr($_SERVER['QUERY_STRING'], $pg_query_name . "=")) {
595       if (strpos($_SERVER['REQUEST_URI'], "?")) {
596         $div = "&";
597       } else {
598         $div = "?";
599       }
600 
601       return $_SERVER['REQUEST_URI'] . $div . "$pg_query_name=$pg";
602     } else {
603       return preg_replace("/" . preg_quote($pg_query_name) . "=\d*/", $pg_query_name . "=" . $pg, $_SERVER['REQUEST_URI']);
604     }
605   }
606 
607 }
608 
609 
610 
611 /**
612  * Extends the PHP Exception class to add a custom message format.
613  */
614 class GxDBException extends Exception {
615   /**
616    * Constructor.
617    * @param string $message The error message.
618    * @param integer $code   The error code.
619    */
620   public function __construct($message, $code) {
621     parent::__construct($message, $code);
622   }
623 
624   /**
625    * Override to exclude the output of potentially sensitive data.
626    * @return string
627    */
628   public function __toString() {
629     return __CLASS__ . ": [{$this->code}]: {$this->message}";
630   }
631 
632 }
633 
634 ?>
635 

API documentation generated by ApiGen