|
[php] Фильтрация переменных в классе-обертке для БД
#37793787
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
|
|
|
|
Все классы
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
<?php
class MySQL extends DB {
public function open($database = null) {
if (!$this->link) {
@$this->link = mysql_connect($this->conf['server'], $this->conf['username'], $this->conf['password']);
if (!$this->link) {
throw new Exception('No connection');
}
}
if ($database) {
$this->conf['database'] = $database;
}
if (!mysql_select_db($this->conf['database'], $this->link)) {
throw new Exception('Database does not exist');
}
}
public function close() {
mysql_close($this->link);
$this->link = null;
}
public function query($sql) {
$result = mysql_query($sql, $this->link);
if (is_resource($result)) {
return new MySQL_Result($result);
}
if ($result === false) {
throw new Exception('MySQL Error: ' . mysql_error($this->link) . '. Query: ' . $sql);
}
}
public function escape($string) {
return mysql_real_escape_string($string, $this->link);
}
public function affectedRows() {
return mysql_affected_rows($this->link);
}
public function numRows() {
return $this->affectedRows();
}
public function insertId() {
return mysql_insert_id($this->link);
}
}
?>
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
<?php
abstract class DB {
protected $conf = array(
'database' => null,
'prefix' => null
),
$link,
$temp;
abstract public function open($database = null);
abstract public function close();
abstract public function query($sql);
abstract public function escape($string);
abstract public function insertId();
abstract public function affectedRows();
abstract public function numRows();
public function __construct(array $conf) {
$conf = array_change_key_case($conf);
$this->conf = array_merge($this->conf, $conf);
$this->open();
}
public function __destruct() {
$this->close();
}
public function getConf() {
return $this->conf;
}
public function table($name) {
return $this->conf['prefix'] . $name;
}
public function setCharset($charset) {
$this->query("SET NAMES $charset;");
}
public function quote($string) {
return "'" . $this->escape($string) . "'";
}
public function raw($string) {
return new DB_Expression($string);
}
public function filterArray($array) {
foreach ($array as &$value) {
if (is_null($value)) {
$value = 'NULL';
}
else if (is_bool($value)) {
$value = $value ? 'TRUE' : 'FALSE';
}
else if (is_string($value)) {
$value = $this->quote($value);
}
}
return $array;
}
public function valueList($values) {
$values = $this->filterArray($values);
$str = implode(', ', $values);
return $str;
}
public function fieldList($fields) {
$array = $this->filterArray($fields);
$str = '';
foreach ($fields as $key => $value) {
$str .= $key . ' = ' . $value . ', ';
}
$str = substr($str, 0, -2);
return $str;
}
protected function replaceHolders($matches) {
if ($matches[1]) {
return $this->table($matches[1]);
}
else {
$value = $matches[2] ? $this->temp[$matches[2]] : next($this->temp);
switch ($matches[3]) {
case '':
case 's':
return $this->quote($value);
case 'i':
case 'd':
return intval($value);
case 'j':
return implode(', ', $value);
case 'v':
return $this->valueList($value);
case 'f':
return $this->fieldList($value);
default:
throw new Exception("Unknown placeholder ?{$matches[3]}");
}
}
}
/**
* Производит поиск и замену в SQL-строке плейсхолдеров, возвращает объект DB_Statement.
*
* $login = 'tester';
* $password = 'test';
* $stmt = $db->prepare('SELECT * FROM :users WHERE login = 2? AND password = 1?s;', $password, $login);
* {номер_аргумента}?{флаг}
* : -> добавит преффикс к таблице
* ?, ?s -> экранированная строка в кавычках
* ?i, ?d -> число
* ?j -> значения через запятую без фильтрации
* ?v -> значения через запятую c фильтрацие
* ?f -> поле = значение, ... с фильтрацией
*/
public function prepare() {
$this->temp = func_get_args();
$pattern = '/:(\w+)|(\d*)\?([a-z]?)/s';
$callback = array($this, 'replaceHolders');
$sql = preg_replace_callback($pattern, $callback, $this->temp[0]);
$stmt = new DB_Statement($this, $sql);
return $stmt;
}
public function find($table, $id, $columns = '*') {
$table = $this->table($table);
$id = (int) $id;
$result = $db->query("SELECT $columns FROM $table WHERE id = $id;");
}
public function insert($table, $row, $return_insert_id = true) {
$table = $this->table($table);
$columns = implode(', ', array_keys($row));
$values = $this->valueList($row);
$this->query("INSERT INTO $table ($columns) VALUES ($values);");
if ($return_insert_id) {
return $this->insertId();
}
}
public function update($table, $fields, $id) {
$table = $this->table($table);
$set = $this->fieldList($fields);
$id = (int) $id;
$this->query("UPDATE $table SET $set WHERE id = $id;");
}
public function delete($table, $id) {
$table = $this->table($table);
$id = (int) $id;
$this->query("DELETE FROM $table WHERE id = $id;");
}
public function count($table, $extra = 1) {
$table = $this->table($table);
$result = $this->query("SELECT COUNT(*) FROM $table WHERE $extra;");
$num = (int) $result->fetchSingle();
return $num;
}
}
?>
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
<?php
class DB_Expression {
private $value;
public function __construct($value) {
$this->value = $value;
}
public function __toString() {
return $this->value;
}
}
?>
|
|
|
