1<?php
2/**
3* Alias per vmsql.mysqli.php
4*
5* @package VFront
6* @subpackage DB-Libraries
7* @author Mario Marcello Verona <marcelloverona@gmail.com>
8* @copyright 2007-2010 M.Marcello Verona
9* @version 0.96 $Id: vmsql.mysqlold.php 933 2011-03-26 15:48:54Z marciuz $
10* @see vmsql.mysqli.php
11* @license http://www.gnu.org/licenses/gpl.html GNU Public License
12*/
13
14
15
16class mysql_vmsql {
17
18	public $vmsqltype='mysql';
19
20	public $link_db;
21
22	protected $connected=false;
23
24	protected $error_handler=null;
25
26	protected $last_error=null;
27
28
29
30	/**
31	 * @desc DB Connection
32	 * @param array $array_db
33	 * @return resource
34	 */
35	public function connect($array_db){
36
37		$this->link_db = @mysql_connect($array_db['host'].":".$array_db['port'],
38								  $array_db['user'],
39								  $array_db['passw']);
40
41		if(!is_resource($this->link_db)){
42
43			die("Connection error: is MySQL running? Otherwise please check your conf file");
44		}
45
46		@mysql_select_db($array_db['dbname'],$this->link_db) or die(_("Database does not exist"));
47
48		$this->connected=true;
49
50		return $this->link_db;
51
52	}
53
54
55	/**
56	 * @desc Esegue una query $sql
57	 * @param string $sql
58	 * @param bool $transazione
59	 * @return object
60	 */
61	public function query($sql,$transazione=false){
62
63		$getmicro=microtime(true);
64
65		$obj = @mysql_query($sql,$this->link_db) or $this->error($sql);
66
67		if($GLOBALS['DEBUG_SQL']){
68			$GLOBALS['DEBUG_SQL_STRING'][] = round((microtime(true) - $getmicro),4) . " --- ". $sql;
69		}
70
71		return $obj;
72
73	}
74
75
76
77	/**
78	 * Esegue una query $sql  e restisce vero|falso a seconda dell'esito
79	 * il secure_mode (di default) permette l'uso di sole query SELECT.
80	 * Se l'sql contiene errori la funzione restituisce false, ma l'esecuzione prosegue.
81	 *
82	 * @param string $sql Query SQL da testare
83	 * @param bool $secure_mode Imposta il secure mode per le query, invalidando tutte le query con comandi pericolosi
84	 * @return bool Esito della query
85	 */
86	public function query_try($sql,$secure_mode=true){
87
88		$sql=trim(str_replace(array("\n","\r")," ",$sql));
89
90		if($secure_mode){
91			// piccolo accorgimento per la sicurezza...
92			if(!preg_match("'^SELECT 'i",$sql)) return 0;
93			$sql2=preg_replace("'([\W](UPDATE)|(DELETE)|(INSERT)|(DROP)|(ALTER)|(UNION)|(TRUNCATE)|(SHOW)|(CREATE)|(INFORMATION_SCHEMA)[\W])'ui","",$sql);
94			if($sql2!=$sql){
95				return -1;
96			}
97		}
98
99		if(is_resource($this->link_db)){
100			$res = @mysql_query($sql,$this->link_db) or $this->error($sql);
101			if($res) @mysql_free_result($res);
102
103			if($GLOBALS['DEBUG_SQL']){
104				$GLOBALS['DEBUG_SQL_STRING'][] = round((microtime(true) - $getmicro),4) . " --- ". $sql;
105			}
106		}
107
108		return ($res) ? 1:0;
109	}
110
111
112
113	/**
114	 * @return array
115	 * @param resource $res
116	 * @desc Funzione di fetch_row
117	*/
118	public function fetch_row(&$res){
119
120		if(is_resource($res)){
121
122			$RS= @mysql_fetch_row($res);
123			if($RS) return $RS;
124			else return false;
125
126		}
127
128
129	}
130
131	/**
132	 * @return array
133	 * @param resource $res
134	 * @desc Funzione di fetch_assoc
135	*/
136	public function fetch_assoc(&$res){
137
138		if(is_resource($res)){
139
140			$RS= @mysql_fetch_assoc($res);
141			if($RS) return $RS;
142			else return false;
143
144		}
145
146
147	}
148
149
150	/**
151	 * @return array
152	 * @param resource $res
153	 * @desc Funzione di fetch_array
154	*/
155	public function fetch_array(&$res){
156
157		if(is_resource($res)){
158
159			$RS= @mysql_fetch_array($res);
160			if($RS) return $RS;
161			else return false;
162
163		}
164
165
166	}
167
168
169	/**
170	 * @desc Funzione di fetch_object
171	 * @return object
172	 * @param resource $res
173	*/
174	public function fetch_object(&$res,$class_name=null){
175
176		if(is_object($res)){
177
178			$RS= ($class_name==null) ? @mysql_fetch_object($res) :  @mysql_fetch_object($res, $class_name);
179
180			if($RS) return $RS;
181			else return false;
182		}
183	}
184
185
186
187	public function num_rows(&$res){
188
189		if(is_resource($res)){
190			return @mysql_num_rows($res);
191
192		}
193	}
194
195
196	/**
197	 * @return int
198	 * @param resource $res
199	 * @desc Funzione di insert ID che restituisce l'ultimo ID autoincrement inserito (MySQL)
200	*/
201	public function insert_id(){
202
203
204		if(is_resource($this->link_db)){
205			return @mysql_insert_id($this->link_db);
206
207		}
208
209	}
210
211
212
213	/**
214	 * @return int
215	 * @desc Funzione affected rows
216	*/
217	public function affected_rows(){
218
219
220		if(is_resource($this->link_db)){
221			return @mysql_affected_rows($this->link_db);
222
223		}
224
225	}
226
227
228
229	public function list_tables($dbname){
230
231		return @mysql_list_tables($dbname);
232
233	}
234
235
236
237	public function num_fields($dbname){
238
239		return @mysql_num_fields($dbname);
240
241	}
242
243
244	public function fetch_field($dbname){
245
246		return @mysql_fetch_field($dbname);
247
248	}
249
250
251	/**
252	 * @desc Funzione di free_result
253	 * @return void
254	 * @param string $result
255	*/
256	public function free_result($result){
257
258		mysql_free_result($result);
259	}
260
261
262
263
264
265
266	#########################################################################################
267	#
268	#
269	#	FUNZIONI DI ELABORAZIONE
270	#
271
272
273	/**
274	 *  Recupera informazioni dal file e dalla query ed apre la funzione openError del file design/layouts.php dove cancella il buffer e manda a video l'errore codificato
275	 *
276	 * @return void
277	 * @param string $sql
278	 * @desc Handler degli errori per le query.
279	*/
280	public function error($sql, $message=''){
281
282		if(!is_object($this->error_handler)){
283
284			$this->error_handler= new stdClass();
285
286			$this->error_handler->dbtype=$this->vmsqltype;
287			$this->error_handler->errors=array();
288		}
289
290		$trace=debug_backtrace();
291		$last=count($trace)-1;
292		$file_line=str_replace(FRONT_ROOT, '', $trace[$last]['file']).":".$trace[$last]['line'];
293
294		$ee=array('date'=>date("c"),
295				  'sql'=>$sql,
296				  'code'=>mysql_errno($this->link_db),
297				  'msg'=>mysql_error($this->link_db),
298				  'file'=>$file_line
299			);
300
301		$this->error_handler->errors[]=$ee;
302
303		$this->last_error=$ee;
304
305
306		if(isset($GLOBALS['DEBUG_SQL']) && $GLOBALS['DEBUG_SQL']){
307
308			$this->error_debug();
309		}
310		else{
311
312			if(!function_exists('openError')){
313				include_once(FRONT_REALPATH."/inc/layouts.php");
314			}
315			// richiamo la funzione openError
316			openError($this->last_error);
317			exit;
318		}
319
320
321	}
322
323
324	public function error_debug($format='string'){
325
326		if($format=='string'){
327
328			var_dump($this->last_error);
329		}
330
331	}
332
333
334
335
336	/**
337	 * @return  matrix
338	 * @param matrix $matrix
339	 * @desc restituisce una traslata della matrice partendo da indici numerici
340	*/
341	public function reverse_matrix($matrix){
342
343		if(!is_array($matrix)) return false;
344
345		$keys = array_keys($matrix[0]);
346
347		for($i=0;$i<count($matrix);$i++){
348
349			for($j=0;$j<count($keys);$j++)	$rev[$keys[$j]][$i] = $matrix[$i][$keys[$j]];
350		}
351
352		return $rev;
353	}
354
355
356
357
358	/**
359	 * @return array (matrice)
360	 * @param resource $res
361	 * @desc Funzione utility di fetch_assoc che restituisce tutta la matrice dei risultati
362	*/
363	public function fetch_assoc_all(&$res, $reverse=false){
364
365		if(is_resource($res)){
366
367			$matrice=array();
368
369			while($RS= $this->fetch_assoc($res)) $matrice[]=$RS;
370
371			$this->free_result($res);
372
373			if($reverse)
374				return $this->reverse_matrix($matrice);
375
376			else
377				return $matrice;
378
379		}
380	}
381
382
383
384
385	/**
386	 * @return resource
387	 * @param resource $res
388	 * @desc Funzione utility di fetch_row che restituisce tutta la matrice dei risultati
389	*/
390	public function fetch_row_all(&$res,$reverse=false){
391
392		if(is_resource($res)){
393
394			while($RS= $this->fetch_row($res)) $matrice[]=$RS;
395
396			$this->free_result($res);
397
398			if($reverse)
399				return $this->reverse_matrix($matrice);
400
401			else
402				return $matrice;
403
404		}
405	}
406
407
408
409
410	/**
411	 * @desc Funzione di transazione che corrisponde ad un BEGIN
412	 */
413	public function begin(){
414
415		$this->query("BEGIN");
416	}
417
418	/**
419	 * @desc Funzione di transazione di ROLLBACK
420	 */
421	public function rollback(){
422
423		$this->query("ROLLBACK");
424	}
425
426
427	/**
428	 * @desc Funzione di transazione di COMMIT
429	 */
430	public function commit(){
431
432		$this->query("COMMIT");
433	}
434
435
436	/**
437	 * Funzione di utilità
438	 * Testa l'esistenza di un $valore (di solito l'ID) nel $campo di una $tabella,
439	 * con eventuali clausole $and
440	 *
441	 * @param string $campo
442	 * @param mixed $valore_id
443	 * @param string $tabella
444	 * @param string $and
445	 * @return bool
446	 */
447	public function test_id($campo,$valore_id,$tabella,$and="",$secure_test=false){
448
449		$sql= "SELECT * FROM $tabella WHERE $campo=$valore_id $and";
450
451		if($secure_test){
452			if($this->query_try($sql)){
453				$q=$this->query($sql);
454
455				return ($this->num_rows($q)>0) ? true:false;
456			}
457			else return null;
458		}
459		else{
460
461			$q=$this->query($sql);
462			return ($this->num_rows($q)>0) ? true:false;
463		}
464	}
465
466
467
468
469
470	/**
471	 * @desc Esegue diverse query $sql (forzata per mysql e sperimentale)
472	 * @param string $sql
473	 * @return object
474	 */
475	public function multi_query($sql_multi){
476
477		$sql_array=explode(";",$sql_multi);
478
479		foreach ($sql_array as $sql){
480
481			$obj[] = @mysql_query($sql,$this->link_db) or $this->error($sql,mysql_error($this->link_db),false);
482
483			if($GLOBALS['DEBUG_SQL']){
484				$getmicro=microtime(true);
485				$GLOBALS['DEBUG_SQL_STRING'][] = round((microtime(true) - $getmicro),3) . " --- ". $sql;
486			}
487		}
488
489		return $obj;
490
491	}
492
493
494	/**
495	 * Funzione di fetch row in caso di multiple query (forzata per mysql e sperimentale)
496	 *
497	 * @param resource $res
498	 * @return array
499	 */
500	public function fetch_row_multi(&$res){
501
502
503		$output=array();
504		$res =(!is_array($res)) ? $res=array() : $res;
505
506		foreach ($res as $result){
507			/* store first result set */
508				while ($row = mysql_fetch_row($result)) {
509				   $output[]=$row;
510				}
511
512		}
513
514		return $output;
515	}
516
517	/**
518	 * Escape function
519	 *
520	 * @param string $string
521	 * @return string
522	 */
523	public function escape($string=null){
524
525		return mysql_real_escape_string(stripslashes($string),$this->link_db);
526	}
527
528
529	/**
530	 * Unescape function
531	 *
532	 * @param string $string
533	 * @return string
534	 */
535	public function unescape($string=null){
536
537		return str_replace(mysql_real_escape_string("'",$this->link_db),"'",$string);
538	}
539
540
541
542
543	/**
544	 * Recursive escape. Work on strings, numbers, array, objects
545	 *
546	 * @param mixed $mixed
547	 * @return mixed
548	 */
549	public function recursive_escape($mixed){
550
551		if(is_string($mixed)){
552
553			$escaped= $this->escape($mixed);
554		}
555		else if(is_numeric($mixed)){
556
557			$escaped= $mixed;
558		}
559		else if(is_array($mixed)){
560
561			foreach ($mixed as $k=>$val)
562				$escaped[$k]=$this->recursive_escape($val);
563		}
564		else if(is_object ($mixed)){
565
566			foreach ($mixed as $k=>$val)
567				$escaped->{$k}=$this->recursive_escape($val);
568		}
569
570		return $escaped;
571	}
572
573
574
575	/**
576	 * Concat DB sintax
577	 *
578	 * @param string $args
579	 * @param string $args
580	 * @return string
581	 */
582	public function concat($args,$as=''){
583
584		$str="CONCAT($args)";
585
586		if($as!='') $str.=" AS $as";
587
588		return $str;
589	}
590
591
592	/**
593	 * Set the LIMIT|OFFSET sintax
594	 *
595	 * @param int $limit
596	 * @param int $offset
597	 * @return string
598	 */
599	public function limit($limit,$offset=''){
600
601		if($offset!='') $str="LIMIT $offset,$limit";
602		else $str="LIMIT $limit";
603
604		return $str;
605	}
606
607
608
609	public function db_version(){
610
611		$q=$this->query("SELECT VERSION()");
612		list($db_version)=$this->fetch_row($q);
613
614		return $db_version;
615	}
616
617
618
619	public function close(){
620
621		if($this->error_handler!==null) db_error_log($this->error_handler);
622
623		return ($this->connected) ? mysql_close($this->link_db) : null;
624	}
625
626	/**
627	 *	For Oracle and MySQLi compatibility
628	 *
629	 * @param statement $stmt
630	 * @return bool
631	 */
632	public function stmt_close($stmt){
633
634		return true;
635	}
636
637	public function __destruct() {
638
639		$this->close();
640	}
641
642
643
644}
645?>