class Datasource_gopher extends PDO {
const
_QUOTE_TABLE = 1,
_QUOTE_COL = 2,
_QUOTE_BOTH = 3,
_QUOTE_NONE = 0;
protected $db_prefix = null;
protected $invalid_format = 'provided %1$s name "%1$2" is not a valid name. Must contain "A-Z", "a-z", "0-9", "_", "-" only, and begin with an Alpha char';
// constructor function
public function __construct() {
global $cfg_database, $cfg_host, $cfg_user, $cfg_pass, $cfg_port, $cfg_type;
if ( empty( $cfg_database ) && empty( $cfg_host ) ) {
$dns = $cfg_type;
// Get the driver that is to be used
$split_dns = explode( ':', $dns );
$driver = $split_dns[0];
} else {
// Build the DNS string needed
if ( $cfg_type == 'mysqli' ) {
$cfg_type = 'mysql';
}
$driver = $cfg_type;
$dns = sprintf( '%1$s:host=%2$s;dbname=%3$s', $cfg_type, $cfg_host, $cfg_database );
if ( !empty( $cfg_port ) ) {
$dns .= ';port='.$cfg_port;
}
}
/**
* Check the needed PDO driver is there
*/
if ( !in_array( $driver, $this->getAvailableDrivers() ) ) {
throw new SQL_InvalidDriver( 'PDO driver "'.$driver.'" is not available, ensure it is installed', 20 );
}
try {
if ( empty( $driver_options ) || !is_array( $driver_options ) ) {
$driver_options = array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION ,
);
}
if ( $driver == 'mysql' ) {
$driver_options[ PDO::MYSQL_ATTR_USE_BUFFERED_QUERY ] = true;
}
parent::__construct( $dns, $cfg_user, $cfg_pass, $driver_options );
} catch ( PDOexception $e ) {
throw new SQL_UnableToConnect( $e->getMessage(), 21 );
}
}
// empty destructor function
public function __destruct() {
}
public function get_db_prefix()
{
global $cfg_prefix;
if ( is_null( $this->db_prefix ) )
{
// Get prefix from config
$this->set_prefix($cfg_prefix);
}
return $this->db_prefix;
}
public function set_prefix( $prefix )
{
$this->db_prefix = $prefix;
}
public function check_name( $name )
{
if ( preg_match( '#[A-Z][A-Z0-9_\-]+#i', $name ) && trim( $name ) == $name ) {
return true;
} else {
return false;
}
}
public function query( $statement, $arg2='', $arg3='', $arg4='' )
{
// Replace {SQL_PREFIX} with the table prefix
$statement = str_replace( '{SQL_PREFIX}', $this->get_db_prefix(), $statement );
try {
$result = parent::query( $statement, $arg2, $arg3, $arg4 );
return $result;
} catch ( PDOException $e ) {
throw new SQL_QueryFailed( $e->getMessage(), 22 );
}
}
public function prepare( $statement, $driver_opts=array() )
{
$statement = str_replace( '{SQL_PREFIX}', $this->get_db_prefix(), $statement );
return parent::prepare( $statement, $driver_opts );
}
public function num_rows( $result )
{
return $result->rowCount();
}
public function insert_id()
{
return $this->lastInsertID();
}
public function insert( $table, $entries, $quote_ident=self::_QUOTE_BOTH )
{
$table = $this->get_db_prefix().$table;
if ( $this->check_name( $table ) ) {
if ( $quote_ident & self::_QUOTE_TABLE ) {
$table = '`'.$table.'`';
}
$query_parts = array();
foreach( $entries as $col=>$val ) {
if ( !$this->check_name( $col ) ) {
// Column has invalid name
throw new SQL_InvalidName( sprintf( $this->invalid_format, 'column', $col ), 22 );
} else if ( $quote_ident & self::_QUOTE_COL ) {
$query_parts['col'][] = '`'.$col.'`, ';
} else {
$query_parts['col'][] = $col.', ';
}
$query_parts['val'][] = '?, ';
}
// Create the column and value strings
$query_parts['col'] = trim( implode( '', $query_parts['col'] ), ', ' );
$query_parts['val'] = trim( implode( '', $query_parts['val'] ), ', ' );
$statement = 'INSERT INTO '.$table.' ( '.$query_parts['col'].' ) VALUES ( '.$query_parts['val'].' )';
// Prepare and execute query
try {
$pdo_st = parent::prepare( $statement );
$pdo_st->execute( array_values( $entries ) );
return $pdo_st;
} catch ( PDOException $e ) {
//throw new SQL_QueryFailed( $e->getMessage(), 22 );
}
} else {
// throw new SQL_InvalidName( sprintf( $this->invalid_format, 'table', $table ), 23 );
}
}
public function update( $table, $entries, $where=array(), $quote_ident=self::_QUOTE_BOTH ) {
$table = $this->get_db_prefix().$table;
if ( $this->check_name( $table ) ) {
if ( $quote_ident & self::_QUOTE_TABLE ) {
$table = '`'.$table.'`';
}
$sql = 'UPDATE '.$table.' SET ';
// Create the middle section of the query
$middle_sql = '';
foreach( $entries as $key=>$val ) {
if ( !$this->check_name( $key ) ) {
// Column has invalid name
throw new SQL_InvalidName( sprintf( $this->invalid_format, 'column', $key ), 23 );
} else if ( $quote_ident & self::_QUOTE_COL ) {
$key = '`'.$key.'`';
}
$middle_sql .= $key.' = ?, ';
}
$sql .= trim( $middle_sql, ', ' );
if ( is_array( $where ) && !empty( $where ) ) {
// Add where onto the query (Only allows for equals so far)
$where_sql = '';
foreach( $where as $key=>$val ) {
if ( !$this->check_name( $key ) ) {
// Column has invalid name
throw new SQL_InvalidName( sprintf( $this->invalid_format, 'column', $key ), 23 );
} else if ( $quote_ident & self::_QUOTE_COL ) {
$key = '`'.$key.'`';
}
$where_sql .= ' AND '.$key.' = ? ';
}
$sql .= ' WHERE '.trim( $where_sql, 'AND ' );
}
// Prepare and execute query
try {
$pdo_st = parent::prepare( $sql );
$pdo_st->execute( array_merge( array_values($entries), array_values($where) ) );
return $pdo_st;
} catch ( PDOException $e ) {
// throw new SQL_QueryFailed( $e->getMessage(), 22 );
}
} else {
// throw new SQL_InvalidName( sprintf( $this->invalid_format, 'table', $table ), 23 );
}
}
public function select( $table, $where=array(), $cols=array(), $quote_ident=self::_QUOTE_BOTH ) {
if ( !is_array( $where ) ) {
$where = array();
}
if ( !is_array( $cols ) ) {
$cols = array();
}
$table = $this->get_db_prefix().$table;
if ( $this->check_name( $table ) ) {
if ( $quote_ident & self::_QUOTE_TABLE ) {
$table = '`'.$table.'`';
}
if ( (empty( $cols ) || !is_array( $cols )) && (empty( $where ) || !is_array( $where )) ) {
// Run a straight select all on the provided table
try {
$pdo_st = parent::prepare( 'SELECT * FROM '.$table );
$pdo_st->execute();
return $pdo_st;
} catch ( PDOException $e ) {
// throw new SQL_QueryFailed( $e->getMessage(), 22 );
}
} else {
/**
* Construct the correct query string needed for use with prepared
* queries. Depending on the $quote_ident, the column names may be
* quoted correctly.
*/
$sql = 'SELECT ';
if ( is_array( $cols ) && !empty( $cols ) ) {
// Add the columns onto the query
foreach( $cols as $key=>$column ) {
if ( is_array( $column ) ) {
trigger_error( 'Sql::select() invalid column value type (array)', E_USER_WARNING );
unset( $cols[ $key ] );
} else if ( !$this->check_name( $column ) ) {
// Column has invalid name
// throw new SQL_InvalidName( sprintf( $this->invalid_format, 'column', $column ), 23 );
} else if ( $quote_ident & self::_QUOTE_COL ) {
$cols[ $key ] = '`'.$column.'`';
}
}
$sql .= trim( implode( ',', $cols ), ', ' );
} else {
$sql .= '*';
}
$sql .= ' FROM '.$table; # Add the table on
if ( is_array( $where ) && !empty( $where ) ) {
// Add Where on
$where_sql = '';
foreach( $where as $key=>$val ) {
if ( is_array( $val ) ) {
trigger_error( 'Sql::select() invalid where value type (array) for key "'.$key.'"', E_USER_WARNING );
continue;
} else if ( !$this->check_name( $key ) ) {
// Column has invalid name
throw new SQL_InvalidName( sprintf( $this->invalid_format, 'column', $key ), 23 );
} else if ( $quote_ident & self::_QUOTE_COL ) {
$key = '`'.$key.'`';
}
$where_sql .= ' AND '.$key.' = ?';
}
$sql .= ' WHERE '.trim( $where_sql, 'AND ' );
}
// Prepare and execute query
try {
$pdo_st = parent::prepare( $sql );
$pdo_st->execute( array_values( $where ) );
return $pdo_st;
} catch ( PDOException $e ) {
// throw new SQL_QueryFailed( $e->getMessage(), 22 );
}
}
} else {
// throw new SQL_InvalidName( sprintf( $this->invalid_format, 'table', $table ), 23 );
}
}
public function delete( $table, $where, $quote_ident=self::_QUOTE_BOTH ) {
$table = $this->get_db_prefix().$table;
if ( !is_array( $where ) || empty( $where ) ) {
throw new SQL_QueryFailed( 'second argument must be an array' );
} else if ( $this->check_name( $table ) ) {
if ( $quote_ident & self::_QUOTE_TABLE ) {
$table = '`'.$table.'`';
}
$sql = 'DELETE FROM '.$table;
$where_sql = '';
foreach( $where as $key=>$val ) {
if ( is_array( $val ) ) {
trigger_error( 'Sql::delete() invalid where value type (array) for key "'.$key.'"', E_USER_WARNING );
continue;
} else if ( !$this->check_name( $key ) ) {
// Column has invalid name
throw new SQL_InvalidName( sprintf( $this->invalid_format, 'column', $key ), 23 );
} else if ( $quote_ident & self::_QUOTE_COL ) {
$key = '`'.$key.'`';
}
$where_sql .= ' AND '.$key.' = ?';
}
$sql .= ' WHERE '.trim( $where_sql, 'AND ' );
// Prepare and execute query
try {
$pdo_st = parent::prepare( $sql );
$pdo_st->execute( array_values( $where ) );
return $pdo_st;
} catch ( PDOException $e ) {
// throw new SQL_QueryFailed( $e->getMessage(), 22 );
}
} else {
// throw new SQL_InvalidName( sprintf( $this->invalid_format, 'table', $table ), 23 );
}
}
public function split_sql_file( $file, $delimiter=';', $run_queries=true ) {
if ( !is_file( $file ) || !is_readable( $file ) ) {
throw new Sql_InvalidFile( $file.' does not exist or is not readable' );
}
// Remove comments from file
$file_contents = file_get_contents( $file );
$this->remove_remarks( $file_contents );
/**
* Get string into one big line, splitting on the delimiter
* and then trimming off any white space
*/
$sql = str_replace( "\r" , '', $file_contents );
$data = preg_split( '/' . preg_quote( $delimiter, '/' ) . '$/m', $file_contents );
$data = array_map( 'trim' , $data) ;
// The empty case
$end_data = end( $data );
if ( empty( $end_data ) ) {
unset( $data[ key( $data ) ] );
}
if ( $run_queries == true ) {
foreach( $data as $key=>$query ) {
if ( !trim( $query ) ) {
unset( $data[ $key ] );
} else {
$this->query( $query );
}
}
}
return $data;
}
}
, чтобы пресечь использование html'я некой группой пользователей), а те, у которых есть опыт разрешают пользователю вводить определённые html теги (или предоставляют для этого визуальный редактор).
