Namespaces

  • Latte
    • Loaders
    • Macros
    • Runtime
  • Nette
    • Application
      • Responses
      • Routers
      • UI
    • Bridges
      • ApplicationDI
      • ApplicationLatte
      • ApplicationTracy
      • CacheDI
      • CacheLatte
      • DatabaseDI
      • DatabaseTracy
      • DITracy
      • FormsDI
      • FormsLatte
      • Framework
      • HttpDI
      • HttpTracy
      • MailDI
      • ReflectionDI
      • SecurityDI
      • SecurityTracy
    • Caching
      • Storages
    • ComponentModel
    • Database
      • Conventions
      • Drivers
      • Table
    • DI
      • Config
        • Adapters
      • Extensions
    • Forms
      • Controls
      • Rendering
    • Http
    • Iterators
    • Loaders
    • Localization
    • Mail
    • Neon
    • PhpGenerator
      • Traits
    • Reflection
    • Security
    • Tokenizer
    • Utils
  • Tracy
    • Bridges
      • Nette
  • none

Classes

  • Connection
  • Context
  • Helpers
  • ResultSet
  • Row
  • SqlLiteral
  • SqlPreprocessor
  • Structure

Interfaces

  • IConventions
  • IRow
  • IRowContainer
  • IStructure
  • ISupplementalDriver

Exceptions

  • ConnectionException
  • ConstraintViolationException
  • DriverException
  • ForeignKeyConstraintViolationException
  • NotNullConstraintViolationException
  • UniqueConstraintViolationException
  • Overview
  • Namespace
  • Class
  • Tree
  • Deprecated
  1: <?php
  2: 
  3: /**
  4:  * This file is part of the Nette Framework (https://nette.org)
  5:  * Copyright (c) 2004 David Grudl (https://davidgrudl.com)
  6:  */
  7: 
  8: namespace Nette\Database;
  9: 
 10: use Nette;
 11: 
 12: 
 13: /**
 14:  * SQL preprocessor.
 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:     /** @var Connection */
 41:     private $connection;
 42: 
 43:     /** @var ISupplementalDriver */
 44:     private $driver;
 45: 
 46:     /** @var array of input parameters */
 47:     private $params;
 48: 
 49:     /** @var array of parameters to be processed by PDO */
 50:     private $remaining;
 51: 
 52:     /** @var int */
 53:     private $counter;
 54: 
 55:     /** @var bool */
 56:     private $useParams;
 57: 
 58:     /** @var string values|set|and|order */
 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:      * @param  array
 71:      * @return array of [sql, params]
 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:     /** @internal */
108:     public function callback($m)
109:     {
110:         $m = $m[0];
111:         if ($m[0] === '?') { // placeholder
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] === '-') { // string or comment
118:             return $m;
119: 
120:         } else { // command
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') { // (key, key, ...) VALUES (value, value, ...)
195:                 if (array_key_exists(0, $value)) { // multi-insert
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)) { // value, value, ... OR (1, 2), (3, 4)
220:                         $vx[] = is_array($v) ? '(' . $this->formatValue($v) . ')' : $this->formatValue($v);
221:                     } elseif (substr($k, -1) === '=') { // key+=value, key-=value, ...
222:                         $k2 = $this->delimite(substr($k, 0, -2));
223:                         $vx[] = $k2 . '=' . $k2 . ' ' . substr($k, -2, 1) . ' ' . $this->formatValue($v);
224:                     } else { // key=value, key=value, ...
225:                         $vx[] = $this->delimite($k) . '=' . $this->formatValue($v);
226:                     }
227:                 }
228:                 return implode(', ', $vx);
229: 
230:             } elseif ($mode === 'and' || $mode === 'or') { // (key [operator] value) AND ...
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') { // key, key DESC, ...
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: 
Nette 2.4-20191120 API API documentation generated by ApiGen 2.8.0