Php SQLite3 real_escape_string
This tutorials show you how to use real_escape_string from SQLite3. A class that interfaces SQLite 3 databases.
The real_escape_string from SQLite3 is declared as follows:
class SQLite3 < // w ww . d e m o 2s .c o m /* Methods */ public __construct( string $filename, int $flags = SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE, string $encryptionKey = "") public backup( SQLite3 $destination, string $sourceDatabase = "main", string $destinationDatabase = "main"): bool public busyTimeout( int $milliseconds): bool public changes(): int public close(): bool public createAggregate( string $name, callable $stepCallback, callable $finalCallback, int $argCount = -1 ): bool public createCollation( string $name, callable $callback): bool public createFunction( string $name, callable $callback, int $argCount = -1, int $flags = 0 ): bool public enableExceptions( bool $enable = false): bool public static escapeString( string $string): string public exec( string $query): bool public lastErrorCode(): int public lastErrorMsg(): string public lastInsertRowID(): int public loadExtension( string $name): bool public open( string $filename, int $flags = SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE, string $encryptionKey = ""): void public openBlob( string $table, string $column, int $rowid, string $database = "main", int $flags = SQLITE3_OPEN_READONLY ): resource|false public prepare( string $query): SQLite3Stmt|false public query( string $query): SQLite3Result|false public querySingle( string $query, bool $entireRow = false): mixed public setAuthorizer( ?callable $callback): bool public static version(): array >
Examples
The following code shows how to use real_escape_string from SQLite3.
// gestione DB include_once("./sample_db.cfg"); $logdb = fopen("sample_db.log", "a"); fprintf($logdb, "%s:%s\n", date("Ymd:His"),"Inizio chiamata =================================== db";/* w ww . d e m o 2s . c o m */ $result_db=null; $result_name=null; $error_db=''; // in base alla variabile esterna $dbcfg agisco su mysql o sqlite if (!isset($dbcfg)) < die("l'applicazione non ? configurata per un db tra mysql e sqlite."); > else < /* DEFINISCO: $sample_db ==> istanza di db $result_db ==> istanza di risultato (dopo selectdb()) $error_db ==> errori a db, se presenti opendb() ==> inizializza la sessione closedb() ==> chiude la sessione checkdb() ==> controlla se la situazione del DB ? ok. checkConn()==> controlla se la connessione ? ok. cleardb() ==> pulisce eventuali errori di esecuzione. querydb() ==> esegue una query senza risultato + nome query (un identificativo della query) + sql (il codice vero della query) = restituisce il numero di record modificati selectdb() ==> esegue una query con risultato + nome query (un identificativo della query) + sql (il codice vero della query) * il risultato viene gestito anche internamente con nextrowdb() nextrowdb()==> legge la successiva riga del risultato come - array associativo (restituito dalla funzione) */ fprintf($logdb, "%s:%s\n", date("Ymd:His"),"definizione $dbcfg . "); function checkdb() < global $logdb, $error_db; fprintf($logdb, "%s:%s\n", date("Ymd:His"),"checkdb ".(checkConn()?"true":"false")." and ".($error_db==''?True:false)."."); return checkConn() && $error_db==''; > function cleardb() < global $logdb, $error_db; if ( checkConn() && $error_db!='') if (strlen($error_db)'connessione') $error_db=''; fprintf($logdb, "%s:%s\n", date("Ymd:His"),"cleardb ".(checkConn()?"true":"false")." . $error_db"); > if ($dbcfg=="mysql") < function checkConn() < global $sample_db; return ($sample_db instanceof mysqli) && $sample_db->ping(); > function opendb() < global $logdb, $sample_db, $error_db, $dbhost, $dbuser, $dbpasw, $dbname; $error_db=''; // "sql.melaricosa.eu", "melarico89602", "mela46532","melarico89602" $sample_db = @mysqli_connect($dbhost, $dbuser, $dbpasw, $dbname); if (mysqli_connect_errno($sample_db)) $error_db='connessione a $dbhost $dbname ($dbuser $dbpasw) '.mysqli_connect_error(); > function closedb() < global $logdb, $sample_db, $error_db; if (isset($sample_db)) mysqli_close($sample_db); > function querydb($nome, $query) < global $logdb, $sample_db, $error_db; $numrows=0; if(checkdb()) < @mysqli_query($sample_db, $query); if (mysqli_errno($sample_db)) $error_db="Errore in querydb-$nome :".mysqli_error($sample_db).":$query:"; else $numrows=mysqli_affected_rows($sample_db); > return $numrows; > function selectdb($nome, $query) < global $logdb, $sample_db, $error_db, $result_db, $result_name; if(isset($result_db)) mysqli_free_result($result_db); $result_db=null; $result_name = null; if(checkdb()) < $result_name = $nome; $result_db = @mysqli_query($sample_db, $query); if (mysqli_errno($sample_db)) < $error_db="Errore in selectdb-$nome :".mysqli_error($sample_db).":$query:"; $result_db=null; $result_name = null; > > > function nextrowdb() < global $logdb, $sample_db, $error_db, $result_db, $result_name; $row = null; if(isset($result_db)) < $row = mysqli_fetch_assoc($result_db); if (mysqli_errno($sample_db)) < $error_db="Errore in nextrowdb-$result_name :".mysqli_error($sample_db)."."; $row = null; > > return $row; > function sqlescapedb($text) < global $sample_db; return $sample_db->real_escape_string($text); > > else if ($dbcfg=="sqlite") < function checkConn() < global $sample_db; return ($sample_db instanceof SQLite3); > function opendb() < global $logdb, $sample_db, $error_db, $dbhost, $dbname; $error_db=''; $sample_db=null; try < $sample_db = new SQLite3($dbhost.$dbname); //var_dump("SQLite3 object", $sample_db); > catch (Exception $ex) < $error_db='connessione '.$ex->getMessage(); //var_dump("errore in connessione", $ex); > fprintf($logdb, "%s:%s\n", date("Ymd:His"),"opendb $dbhost $dbname ".get_class($sample_db)." . $error_db"); > function closedb() < global $logdb, $sample_db, $error_db; if (checkConn()) try < $sample_db->close(); > catch (Exception $ex) < // nothing to do > fprintf($logdb, "%s:%s\n", date("Ymd:His"),"closedb . $error_db"); > function querydb($nome, $query) < global $logdb, $sample_db, $error_db; if(checkdb()) < if (!$sample_db->exec($query)) $error_db="Errore in querydb-$nome :".$sample_db->lastErrorMsg().":$query:"; > fprintf($logdb, "%s:%s\n", date("Ymd:His"),"querydb $nome = $query /=changes()> . $error_db"); return $sample_db->changes(); > function selectdb($nome, $query) < global $logdb, $sample_db, $error_db, $result_db, $result_name; $result_db=null; $result_name = null; if(checkdb()) < $result_name = $nome; try < $result_db = $sample_db->query($query); > catch (Exception $ex) < $error_db="Errore in selectdb-$nome :".$sample_db->lastErrorMsg().":$query:"; $result_db=null; $result_name = null; > > fprintf($logdb, "%s:%s\n", date("Ymd:His"),"selectdb $nome = $query no-result")." / $result_name . $error_db"); > function nextrowdb() < global $logdb, $sample_db, $error_db, $result_db, $result_name; $row = null; if(isset($result_db)) try < $row = $result_db->fetchArray(SQLITE3_ASSOC); > catch (Exception $ex) < $error_db="Errore in nextrowdb-$result_name :".$ex->getMessage()."."; $row = null; > fprintf($logdb, "%s:%s\n", date("Ymd:His"),"nextrowdb $result_name no-result")." :",array_values($row)):"no-result")." . $error_db"); return $row; > function sqlescapedb($text) < return SQLite3::escapeString($text); >> else < die("l'applicazione ? configurata per usare il db '$dbcfg' che non ? implementato."); > >
/*w w w . d e mo 2 s .c o m*/ // ----------------------------------------------------------------------------- // SQL Database Bridge // Written by Rich Winslow // rich@richwinslow.com // ----------------------------------------------------------------------------- class SQL_Bridge < var $link; // Variable as connection var $server; // Server name or location var $database; // Database name var $login; // Database login var $pass; // Database password var $db_type; // Database type (e.g. mysql, mysqli, sqlite3) public function __construct($server, $login, $pass, $database, $db_type = 'mysqli') < $this->server = $server; $this->database = $database; $this->login = $login; $this->pass = $pass; $this->db_type = $db_type; // Construct link object based on type and connect to database switch ($this->db_type) < case 'sqlite3': $this->link = new SQLite3($this->server); break; case 'mysqli': $this->link = new mysqli($this->server, $this->login, $this->pass, $this->database) or die('Problem: ' . mysqli_error()); break; default: $this->link = mysql_connect($this->server, $this->login, $this->pass); if (!$this->link) < die('Could not connect: ' . mysql_error()); > $this->dblink = mysql_select_db($this->database) or die('No database: ' . mysql_error()); break; > return true; > private function surround($array, $wall, $separator) < return $wall . implode($wall . $separator . $wall, $array) . $wall; > /** * Retrieve all object properties and their values in separate, symmetric * arrays * * Values that are arrays are serialized */ private function get_field_values($object, $table = null, $schema = null) < $fields = array(); $values = array(); foreach (get_object_vars($object) as $var => $value) < // Make sure to skip database handle if ($var != 'db') < // Ensure mapping of variable names to corresponding field if (isset($schema[$var])) < $fields[] = $schema[$var]; > else < $fields[] = $var; > // Maintain coupling of information if a variable is empty if (!empty($value)) < if (is_array($value)) < $value = serialize($value); >$values[] = '\'' . self::escape_string($value) . '\''; > else < $values[] = '\'\''; > > > return array('fields' => $fields, 'values' => $values); > private function get_table($object, $table=null) < if (empty($table)) < $table = get_class($object); > return $table; > private function join_arrays_to_string($left, $right, $separator=null, $join=null) < $string = array(); // Default equals as separator if (empty($separator)) < $separator = '='; > // Default comma as join if (empty($join)) < $join = ','; > if (count($left) != count($right)) < echo 'Error: Left(' . count($left) . ') and right(' . count($right) . ') array lengths do not match!'; return false; > else < for ($i=0; $i $string = implode($join, $string); > return $string; > public function close() < if ($this->link) < switch ($this->db_type) < case 'sqlite3': $this->link->close(); break; case 'mysqli': $this->link->close(); break; default: mysql_close($this->link); break; > return true; > return false; > public function run_query($query) < switch ($this->db_type) < case 'sqlite3': $run = $this->link->query($query); break; case 'mysqli': $run = $this->link->query($query) or die(mysqli_error($this->link)); break; default: $run = mysql_query($query) or die(mysql_error()); break; > return $run; > public function num_rows($table) < $results = $this->get_rows('SELECT COUNT(*) FROM ' . $table); return array_shift($results); > public function show_tables() < // Retrieves all contents from query and places them in $array $results = $this->get_rows('SHOW TABLES'); foreach ($results as $row) < $array[] = $row[0]; > return $array; > public function get_rows($query) < $info = array(); $results = $this->run_query($query); switch ($this->db_type) < case 'sqlite3': while ($row = $results->fetchArray(SQLITE3_NUM)) < $info[] = $row; >break; case 'mysqli': while ($row = $results->fetch_row()) < $info[] = $row; >break; default: while ($result = mysql_fetch_row($query)) < $info[] = $result; >> // Unserialize information foreach ($info as $key => $row) < foreach ($row as $field => $value) < if (@unserialize($value)) < $info[$key][$field] = unserialize($value); >> > return $info; > public function get_associative($query) < $info = array(); $results = $this->run_query($query); switch ($this->db_type) < case 'sqlite3': while ($row = $results->fetchArray(SQLITE3_ASSOC)) < $info[] = $row; >break; case 'mysqli': while ($row = $results->fetch_assoc()) < $info[] = $row; >break; default: while ($result = mysql_fetch_assoc($query)) < $info[] = $result; >> // Unserialize information foreach ($info as $key => $row) < foreach ($row as $field => $value) < if (@unserialize($value)) < $info[$key][$field] = unserialize($value); >> > return $info; > public function escape_string($query) < switch ($this->db_type) < case 'sqlite3': return $this->link->escapeString($query); break; case 'mysqli': return $this->link->real_escape_string($query); break; default: return $this->link->mysql_real_escape_string($query); break; > > public function insert($object, $table = null, $schema = null) < // Get table from object class if not given $table = $this->get_table($object, $table); // Get all object fields and values and separate into variables $vars = $this->get_field_values($object, $table, $schema); $fields = $vars['fields']; $values = $vars['values']; $query = 'INSERT INTO ' . $table . ' (' . implode(', ', $fields) . ') ' . 'VALUES (' . implode(', ', $values) . ')'; if ($this->run_query($query)) < return true; >return false; > public function update($object, $keys, $table = null, $schema = null) < // Get table from object class if not given $table = $this->get_table($object, $table); // Get all object fields and values and separate into variables $vars = $this->get_field_values($object, $table, $schema); $fields = $vars['fields']; $values = $vars['values']; foreach ($keys as $key => $value) < if (!empty($schema)) < $key = $schema[$key]; >$where[] = $key . '=' . '\'' . $value . '\''; > $where = implode(' AND ', $where); $query = 'UPDATE ' . $table . ' SET ' . $this->join_arrays_to_string($fields, $values, '=', ', ') . ' WHERE ' . $where; if ($this->run_query($query)) < return true; >return false; > public function delete($object, $keys, $table = null, $schema = null) < // Get table from object class if not given $table = $this->get_table($object, $table); foreach ($keys as $key => $value) < if (!empty($schema)) < $key = $schema[$key]; >$where[] = $key . '=' . '\'' . $value . '\''; > $where = implode(' AND ', $where); $query = 'DELETE FROM ' . $table . ' WHERE ' . $where; if ($this->run_query($query)) < return true; >return false; > > /* End SQL_Bridge class */
Related
demo2s.com | Email: | Demo Source and Support. All rights reserved.