Class GxDBConnect

Copyright: 2012, 2016 Leandro Silva (http://grafluxe.com)
License: MIT
Author: Leandro Silva
Example:

This sample includes tight security.

Use value binding, a whitelist, and checker methods if you plan to construct your SQL statements with values coming from a form (or other user inputted method). Using these featured will help to prevent SQL attacks.

include "./GxDBConnect.class.php";
  include "./DSNHelper.class.php";

  try {
    $conn = new GxDBConnect(DSNHelper::mysql("my_database"), "my_username", "my_pass");

    $conn->col_whitelist = array("first", "last");
    $conn->tbl_whitelist = array("names_table");

    $f_name = $_GET["first_name"];
    $l_name = $_GET["last_name"];
    $table = $_GET["table_name"];

    $data = $conn->query("
      SELECT {$conn->col_check($f_name)}
      FROM {$conn->tbl_check($table)}
      WHERE {$conn->col_check($l_name)} = :ln
      ",
      array(
        $conn->bind_value(":ln", "Doe")
      ),
      PDO::FETCH_NUM
    );

    print_r($data);
  } catch(GxDBException $e) {
    exit($e);
  }

This sample includes a more simple use case.

include "./GxDBConnect.class.php";

  try {
    $conn = new GxDBConnect("mysql:host=localhost;dbname=my_database", "my_username", "my_pass");

    $data = $conn->query("
      SELECT first
      FROM names_table
      WHERE last = 'Doe'
    ");

    print_r($data);
  } catch(GxDBException $e) {
    exit($e);
  }

Classdesc:

Securely execute commands on a database using PHP Data Objects — many security features added. Note that methods prepended with 'run_' execute specific statements; use the 'query' method for custom queries.

Located at GxDBConnect.class.php

Methods summary

`public object`	# `__construct( string $dsn, string $usr = "root", string $pw = "root", array $opts = null )` Constructor. By default, the PDO attribute ATTR_EMULATE_PREPARES is set to false and ATTR_ERRMODE is set to ERRMODE_EXCEPTION. Constructor. By default, the PDO attribute ATTR_EMULATE_PREPARES is set to false and ATTR_ERRMODE is set to ERRMODE_EXCEPTION. Parameters `$dsn` The DSN string. You can use the GxDBConnectHelper class to help setup this param. `$usr` $usr="root" The username. `$pw` $pw="root" The password. `$opts` $opts=null Connection options. Returns object The PDO object. Throws `GxDBException`
`public`	# `blacklist_add( string $str )` Adds a value to your blacklist filter. Before any query is run, your statement will be checked for any blacklisted strings. If a blacklisted string is found, the query will not be executed and a GxDBException exception will be thrown. By default, the blacklist filter contains the following: ["DROP", "DELETE", "--", "/", "xp_", ";"] Adds a value to your blacklist filter. Before any query is run, your statement will be checked for any blacklisted strings. If a blacklisted string is found, the query will not be executed and a GxDBException exception will be thrown. By default, the blacklist filter contains the following: ["DROP", "DELETE", "--", "/", "xp_", ";"] Parameters `$str` A string to blacklist. Letter case does not matter.
`public`	# `blacklist_remove( string $str )` Removes a value from your blacklist filter. Removes a value from your blacklist filter. Parameters `$str` The word to remove from the your blacklist. Letter case does not matter.
`public array`	# `blacklist_list( )` Returns your blacklist filters. Returns your blacklist filters. Returns array The current blacklisted strings.
`public`	# `select_db( string $db )` Selects a database. Selects a database. Parameters `$db` The database name.
`public string`	# `col_check( string $col )` Checks if a column in allowed to be used (via the column whitelist). Checks if a column in allowed to be used (via the column whitelist). Parameters `$col` The column name. Returns string The column name. Throws `GxDBException`
`public string`	# `tbl_check( string $tbl )` Checks if a table in allowed to be used (via the table whitelist). Checks if a table in allowed to be used (via the table whitelist). Parameters `$tbl` The table name. Returns string The table name. Throws `GxDBException`
`public array`	# `bind_value( mixed $parameter, mixed $value, integer $data_type = null )` To be used as the bind argument in the 'query' method. Works like PDO's 'bindValue' method. To be used as the bind argument in the 'query' method. Works like PDO's 'bindValue' method. Parameters `$parameter` The parameter identifier. `$value` The value to bind. `$data_type` $data_type=null The data type. Returns array The bind data.
`public array`	# `query( string $stmt, array $bind = null, integer $fetch_how = PDO::FETCH_ASSOC )` Runs an SQL query. This is the primary method used to run queries. Runs an SQL query. This is the primary method used to run queries. Parameters `$stmt` Your query statement (use 'col_check' and 'tbl_check' with the whitelists for added security against SQL injecion) `$bind` $bind=null An array filled with the 'bind_value' methods. `$fetch_how` $fetch_how=PDO::FETCH_ASSOC How to return the results. Returns array Query results. Throws `GxDBException`
`public`	# `close( )` Closes the database connection. Closes the database connection.
`public boolean`	# `run_tbl_exists( string $tbl )` Returns a boolean determining whether a table exists. Returns a boolean determining whether a table exists. Parameters `$tbl` The table to query. Returns boolean Whether a table exists.
`public integer`	# `run_col_count( string $tbl )` Returns the total column count. Returns the total column count. Parameters `$tbl` The table to query. Returns integer The number of columns.
`public array`	# `run_col_info( string $tbl )` Returns an array of associative arrays with column info. Returns an array of associative arrays with column info. Parameters `$tbl` The table to query. Returns array Column info.
`public array`	# `run_col_data( string $col, string $tbl )` Returns all of a columns data. Returns all of a columns data. Parameters `$col` The column name. `$tbl` The table to query. Returns array Column data.
`public boolean`	# `run_col_exists( string $col, string $tbl )` Returns a boolean determining whether a column exists. Returns a boolean determining whether a column exists. Parameters `$col` The column name. `$tbl` The table to query. Returns boolean Whether a column exists.
`public integer`	# `run_row_total( string $tbl )` Returns the total row count. Returns the total row count. Parameters `$tbl` The table to query. Returns integer The row count.
`public array\|null`	# `run_row_data( integer $row, string $tbl )` Returns data in the specified row. Returns data in the specified row. Parameters `$row` The row number. `$tbl` The table to query. Returns array\|null The row data. Returns null if the specified row is greater than the total number of rows.
`public boolean`	# `run_export( string $tbl, boolean $pretty_print = false, string $relative_dir = "" )` Exports your table as a JSON formatted file. Exports your table as a JSON formatted file. Parameters `$tbl` The table to export. `$pretty_print` $pretty_print=false Whether to pretty-print output (only valid on PHP versions >=5.4.0). `$relative_dir` $relative_dir="" A save path relative to this file. Returns boolean Whether the output succeeded.
`public`	# `run_tbl_to_html( string $stmt, integer $paginate_at = 0, string $pg_query_name = "pg", boolean $use_default_styles = true )` Echos an HTML table with your data. Echos an HTML table with your data. Parameters `$stmt` Your SQL query statement. `$paginate_at` $paginate_at=0 Paginate after N rows of data. Works with the $pg_query_name param. `$pg_query_name` $pg_query_name="pg" The paginate HTML query string name. `$use_default_styles` $use_default_styles=true Assigns default inline styles. Throws `GxDBException`

Properties summary

`public object`	`$conn` The PDO connection object. The PDO connection object.	#
`public array`	`$col_whitelist` A whitelist of columns that can be queried. Use in concert with the 'col_check' method. A whitelist of columns that can be queried. Use in concert with the 'col_check' method.	#
`public array`	`$tbl_whitelist` A whitelist of tables that can be queried. Use in concert with the 'tbl_check' method. A whitelist of tables that can be queried. Use in concert with the 'tbl_check' method.	#
`public string`	`$get_last_stmt` The statement you last queried. The statement you last queried.	#
`public static string`	`$version` The release version. The release version.	# `"3.0.0"`
`public static string`	`$echo_uncaught_errors` Set to true to output uncaught errors. Defaults to false for better security. Set to true to output uncaught errors. Defaults to false for better security.	# `false`

Classes

Exceptions

Class GxDBConnect

Methods summary

Parameters

Returns

Throws

Parameters

Parameters

Returns

Parameters

Parameters

Returns

Throws

Parameters

Returns

Throws

Parameters

Returns

Parameters

Returns

Throws

Parameters

Returns

Parameters

Returns

Parameters

Returns

Parameters

Returns

Parameters

Returns

Parameters

Returns

Parameters

Returns

Parameters

Returns

Parameters

Throws

Properties summary