1: <?php
2:
3: 4: 5: 6:
7:
8: namespace Nette\Database;
9:
10: use Nette;
11:
12:
13: 14: 15:
16: class SqlPreprocessor
17: {
18: use Nette\SmartObject;
19:
20:
21: private $connection;
22:
23:
24: private $driver;
25:
26:
27: private $params;
28:
29:
30: private $remaining;
31:
32:
33: private $counter;
34:
35:
36: private $arrayMode;
37:
38:
39: public function __construct(Connection $connection)
40: {
41: $this->connection = $connection;
42: $this->driver = $connection->getSupplementalDriver();
43: }
44:
45:
46: 47: 48: 49:
50: public function process($params)
51: {
52: $this->params = $params;
53: $this->counter = 0;
54: $prev = -1;
55: $this->remaining = [];
56: $this->arrayMode = null;
57: $res = [];
58:
59: while ($this->counter < count($params)) {
60: $param = $params[$this->counter++];
61:
62: if (($this->counter === 2 && count($params) === 2) || !is_scalar($param)) {
63: $res[] = $this->formatValue($param, 'auto');
64: $this->arrayMode = null;
65:
66: } elseif (is_string($param) && $this->counter > $prev + 1) {
67: $prev = $this->counter;
68: $this->arrayMode = null;
69: $res[] = Nette\Utils\Strings::replace(
70: $param,
71: '~\'[^\']*+\'|"[^"]*+"|\?[a-z]*|^\s*+(?:INSERT|REPLACE)\b|\b(?:SET|WHERE|HAVING|ORDER BY|GROUP BY|KEY UPDATE)(?=\s*\z|\s*\?)|/\*.*?\*/|--[^\n]*~si',
72: [$this, 'callback']
73: );
74: } else {
75: throw new Nette\InvalidArgumentException('There are more parameters than placeholders.');
76: }
77: }
78:
79: return [implode(' ', $res), $this->remaining];
80: }
81:
82:
83:
84: public function callback($m)
85: {
86: $m = $m[0];
87: if ($m[0] === '?') {
88: if ($this->counter >= count($this->params)) {
89: throw new Nette\InvalidArgumentException('There are more placeholders than passed parameters.');
90: }
91: return $this->formatValue($this->params[$this->counter++], substr($m, 1) ?: 'auto');
92:
93: } elseif ($m[0] === "'" || $m[0] === '"' || $m[0] === '/' || $m[0] === '-') {
94: return $m;
95:
96: } else {
97: static $modes = [
98: 'INSERT' => 'values',
99: 'REPLACE' => 'values',
100: 'KEY UPDATE' => 'set',
101: 'SET' => 'set',
102: 'WHERE' => 'and',
103: 'HAVING' => 'and',
104: 'ORDER BY' => 'order',
105: 'GROUP BY' => 'order',
106: ];
107: $this->arrayMode = $modes[ltrim(strtoupper($m))];
108: return $m;
109: }
110: }
111:
112:
113: private function formatValue($value, $mode = null)
114: {
115: if (!$mode || $mode === 'auto') {
116: if (is_string($value)) {
117: if (strlen($value) > 20 || strpos($value, '\\') !== false) {
118: $this->remaining[] = $value;
119: return '?';
120:
121: } else {
122: return $this->connection->quote($value);
123: }
124:
125: } elseif (is_int($value)) {
126: return (string) $value;
127:
128: } elseif (is_float($value)) {
129: return rtrim(rtrim(number_format($value, 10, '.', ''), '0'), '.');
130:
131: } elseif (is_bool($value)) {
132: return $this->driver->formatBool($value);
133:
134: } elseif ($value === null) {
135: return 'NULL';
136:
137: } elseif ($value instanceof Table\IRow) {
138: return $this->formatValue($value->getPrimary());
139:
140: } elseif ($value instanceof SqlLiteral) {
141: $prep = clone $this;
142: list($res, $params) = $prep->process(array_merge([$value->__toString()], $value->getParameters()));
143: $this->remaining = array_merge($this->remaining, $params);
144: return $res;
145:
146: } elseif ($value instanceof \DateTimeInterface) {
147: return $this->driver->formatDateTime($value);
148:
149: } elseif ($value instanceof \DateInterval) {
150: return $this->driver->formatDateInterval($value);
151:
152: } elseif (is_object($value) && method_exists($value, '__toString')) {
153: return $this->formatValue((string) $value);
154:
155: } elseif (is_resource($value)) {
156: $this->remaining[] = $value;
157: return '?';
158: }
159:
160: } elseif ($mode === 'name') {
161: if (!is_string($value)) {
162: $type = gettype($value);
163: throw new Nette\InvalidArgumentException("Placeholder ?$mode expects string, $type given.");
164: }
165: return $this->delimite($value);
166: }
167:
168: if ($value instanceof \Traversable && !$value instanceof Table\IRow) {
169: $value = iterator_to_array($value);
170: }
171:
172: if (is_array($value)) {
173: $vx = $kx = [];
174: if ($mode === 'auto') {
175: $mode = $this->arrayMode;
176: }
177:
178: if ($mode === 'values') {
179: if (array_key_exists(0, $value)) {
180: foreach ($value[0] as $k => $v) {
181: $kx[] = $this->delimite($k);
182: }
183: foreach ($value as $val) {
184: $vx2 = [];
185: foreach ($val as $v) {
186: $vx2[] = $this->formatValue($v);
187: }
188: $vx[] = implode(', ', $vx2);
189: }
190: $select = $this->driver->isSupported(ISupplementalDriver::SUPPORT_MULTI_INSERT_AS_SELECT);
191: return '(' . implode(', ', $kx) . ($select ? ') SELECT ' : ') VALUES (')
192: . implode($select ? ' UNION ALL SELECT ' : '), (', $vx) . ($select ? '' : ')');
193: }
194:
195: foreach ($value as $k => $v) {
196: $kx[] = $this->delimite($k);
197: $vx[] = $this->formatValue($v);
198: }
199: return '(' . implode(', ', $kx) . ') VALUES (' . implode(', ', $vx) . ')';
200:
201: } elseif (!$mode || $mode === 'set') {
202: foreach ($value as $k => $v) {
203: if (is_int($k)) {
204: $vx[] = is_array($v) ? '(' . $this->formatValue($v) . ')' : $this->formatValue($v);
205: } elseif (substr($k, -1) === '=') {
206: $k2 = $this->delimite(substr($k, 0, -2));
207: $vx[] = $k2 . '=' . $k2 . ' ' . substr($k, -2, 1) . ' ' . $this->formatValue($v);
208: } else {
209: $vx[] = $this->delimite($k) . '=' . $this->formatValue($v);
210: }
211: }
212: return implode(', ', $vx);
213:
214: } elseif ($mode === 'and' || $mode === 'or') {
215: foreach ($value as $k => $v) {
216: if (is_int($k)) {
217: $vx[] = $this->formatValue($v);
218: continue;
219: }
220: list($k, $operator) = explode(' ', $k . ' ');
221: $k = $this->delimite($k);
222: if (is_array($v)) {
223: if ($v) {
224: $vx[] = $k . ' ' . ($operator ? $operator . ' ' : '') . 'IN (' . $this->formatValue(array_values($v)) . ')';
225: } elseif ($operator === 'NOT') {
226: } else {
227: $vx[] = '1=0';
228: }
229: } else {
230: $v = $this->formatValue($v);
231: $vx[] = $k . ' ' . ($operator ?: ($v === 'NULL' ? 'IS' : '=')) . ' ' . $v;
232: }
233: }
234: return $value ? '(' . implode(') ' . strtoupper($mode) . ' (', $vx) . ')' : '1=1';
235:
236: } elseif ($mode === 'order') {
237: foreach ($value as $k => $v) {
238: $vx[] = $this->delimite($k) . ($v > 0 ? '' : ' DESC');
239: }
240: return implode(', ', $vx);
241:
242: } else {
243: throw new Nette\InvalidArgumentException("Unknown placeholder ?$mode.");
244: }
245:
246: } elseif (in_array($mode, ['and', 'or', 'set', 'values', 'order'], true)) {
247: $type = gettype($value);
248: throw new Nette\InvalidArgumentException("Placeholder ?$mode expects array or Traversable object, $type given.");
249:
250: } elseif ($mode && $mode !== 'auto') {
251: throw new Nette\InvalidArgumentException("Unknown placeholder ?$mode.");
252:
253: } else {
254: throw new Nette\InvalidArgumentException('Unexpected type of parameter: ' . (is_object($value) ? get_class($value) : gettype($value)));
255: }
256: }
257:
258:
259: private function delimite($name)
260: {
261: return implode('.', array_map([$this->driver, 'delimite'], explode('.', $name)));
262: }
263: }
264: