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